Bagaimana Mencocokkan Data di Excel?

Berbagai Metode untuk Mencocokkan Data di Excel

Ada berbagai metode untuk mencocokkan data di Excel. Misalnya, kita ingin membandingkan data dalam kolom yang sama, misalnya memeriksa duplikasi. Dalam hal ini, kita dapat menggunakan “Pemformatan Bersyarat” dari tab “Beranda”. Sebaliknya, jika kita ingin mencocokkan data dalam dua atau lebih kolom yang berbeda, kita dapat menggunakan fungsi kondisional seperti fungsi IF.

Metode #1 – Menggunakan Fungsi Vlookup
Metode #2 – Menggunakan Fungsi Index + Match
Metode #3 – Buat Nilai Pencarian Anda Sendiri

Sekarang, mari kita bahas masing-masing metode secara rinci.

#1 – Mencocokkan Data Menggunakan Fungsi VLOOKUP

Fungsi VLOOKUP tidak hanya digunakan untuk mendapatkan informasi yang dibutuhkan dari tabel data. Ini juga dapat digunakan sebagai alat rekonsiliasi. Saat merekonsiliasi atau mencocokkan data, rumus VLOOKUP memimpin tabel.

Sebagai contoh, lihat tabel di bawah ini.

Kami memiliki dua tabel data di sini. Yang pertama adalah “Data 1.” Yang kedua adalah “Data 2.”

Sekarang, kita perlu melakukan rekonsiliasi apakah data di kedua tabel cocok. Cara pertama membandingkan data adalah fungsi SUM di excel ke dua tabel untuk mendapatkan total penjualan.

Data 1 – Tabel

Data 2 – Tabel

Kami telah menerapkan fungsi SUM untuk kolom “Jumlah Penjualan” pada tabel. Pada awalnya, kami memiliki perbedaan nilai. Tabel Data 1 menunjukkan total penjualan 2,16,214, dan tabel Data 2 menunjukkan 2,10,214.

Sekarang, kita perlu memeriksa ini secara rinci. Jadi, mari kita terapkan fungsi VLOOKUP untuk setiap tanggal.

Pilih larik tabel sebagai rentang Data 1.

Kami membutuhkan data dari kolom kedua, dan rentang LOOKUP adalah “FALSE”, yaitu pencocokan persis.

Outputnya diberikan di bawah ini:

Pertama, kita harus mengurangi nilai asli dari nilai kedatangan sel berikutnya.

Setelah dikurangi, kami mendapatkan hasilnya sebagai nol.

Kita harus menyalin dan menempelkan rumus ke semua sel untuk mendapatkan nilai varians.

Di sel G6 dan G12, kami mendapatkan perbedaannya.

Di Data 1, kami memiliki 12.104 untuk 04-Mar-2019. Namun, di Data 2, kami memiliki 15.104 untuk tanggal yang sama, jadi ada selisih 3.000.

Demikian pula, untuk 18-Mar-2019 di Data 1, kami memiliki 19.351. Di Data 2, kita punya 10.351, jadi selisihnya 9.000.

#2 – Mencocokkan Data Menggunakan Fungsi INDEX + MATCH

Untuk data yang sama, kita bisa menggunakan fungsi INDEX + MATCH. Kita dapat menggunakan ini sebagai alternatif untuk fungsi VLOOKUPAlternatif Untuk Fungsi VLOOKUPUntuk mereferensikan data dalam kolom dari kanan ke kiri, kita dapat menggabungkan fungsi indeks dan pencocokan, yang merupakan salah satu alternatif Excel terbaik untuk Vlookup.baca lebih lanjut.

Fungsi INDEX digunakan untuk mendapatkan nilai dari kolom yang dipilih berdasarkan nomor baris yang disediakan. Kita perlu menggunakan fungsi MATCHGunakan Fungsi MATCHFungsi MATCH mencari nilai tertentu dan mengembalikan posisi relatifnya dalam rentang sel tertentu. Outputnya adalah posisi pertama yang ditemukan untuk nilai yang diberikan. Menjadi fungsi pencarian dan referensi, ini berfungsi untuk pencocokan tepat dan perkiraan. Misalnya, jika rentang A11:A15 terdiri dari angka 2, 9, 8, 14, 32, rumus ‘MATCH(8,A11:A15,0)’ mengembalikan 3. Hal ini karena angka 8 berada pada urutan ketiga posisi. baca lebih lanjut berdasarkan nilai LOOKUP untuk memberikan nomor baris.

Kita harus Buka fungsi INDEXBuka Fungsi INDEXFungsi INDEX di Excel membantu mengekstrak nilai sel, yang berada dalam larik (rentang) tertentu dan, di persimpangan nomor baris dan kolom yang disebutkan.baca lebih lanjut di sel F3.

Kemudian, pilih larik sebagai rentang kolom hasil, B2 hingga B14.

Buka fungsi MATCH sebagai argumen berikutnya untuk mendapatkan nomor baris.

Pilih nilai LOOKUP sebagai sel D3.

Selanjutnya, pilih larik pencarian sebagai kolom “Tanggal Penjualan” di Data 1.

Di jenis pencocokan, pilih “0 – Pencocokan Tepat.”

Sekarang, tutup dua tanda kurung dan tekan tombol “Enter” untuk mendapatkan hasilnya.

Ini juga memberikan hasil yang sama dengan VLOOKUP. Karena kami telah menggunakan data yang sama, kami mendapatkan angka apa adanya.

#3 – Buat Nilai Pencarian Anda

Sekarang, kita telah melihat bagaimana mencocokkan data menggunakan fungsi Excel. Sekarang, kita akan melihat berbagai skenario secara real-time. Untuk contoh ini, lihat data di bawah ini.

Seperti yang ditunjukkan di atas, kami memiliki data penjualan berdasarkan zona dan berdasarkan tanggal dalam data di atas. Oleh karena itu, kita perlu melakukan proses pencocokan data kembali. Tapi, pertama-tama, mari kita terapkan fungsi VLOOKUP seperti contoh sebelumnya.

Kami punya banyak varian. Mari kita periksa setiap kasus per kasus.

Di sel I5, kami mendapat varian 8.300. Mari kita lihat tabel utama.

Walaupun nilai tabel utama adalah 12.104, kita mendapatkan nilai 20.404 dari fungsi VLOOKUP. Itu karena VLOOKUP dapat mengembalikan nilai LOOKUP yang ditemukan pertama kali.

Dalam hal ini, nilai LOOKUP kami adalah tanggal 20-Mar-2019. Di sel di atas untuk zona “Utara” untuk tanggal yang sama, kami memiliki nilai 20.404, jadi VLOOKUP mengembalikan nilai ini untuk zona “Timur”.

Untuk mengatasi masalah ini, kita perlu membuat nilai LOOKUP yang unik. Misalnya, gabungkan Zona, Tanggal, dan Jumlah Penjualan di Data 1 dan Data 2.

Data 1 – Tabel

Data 2 – Tabel

Kami telah membuat nilai unik untuk setiap zona dengan nilai gabungan “Zona”, “Tanggal Penjualan”, dan “Jumlah Penjualan”.

Dengan menggunakan nilai unik ini, mari kita terapkan fungsi VLOOKUP.

Terapkan rumus ke semua sel. Kami akan mendapatkan varian nol di semua sel.

Seperti ini, dengan menggunakan fungsi Excel, kita bisa mencocokkan data dan menemukan variannya. Namun, pertama-tama, kita perlu melihat duplikat dalam nilai LOOKUP untuk rekonsiliasi yang akurat Rekonsiliasi Rekonsiliasi adalah proses membandingkan saldo akun untuk mengidentifikasi ketidakkonsistenan keuangan, perbedaan, penghilangan, atau bahkan penipuan. Pada akhir setiap periode akuntansi, rekonsiliasi melibatkan pencocokan saldo dan memastikan bahwa debit (kredit) dari satu akun untuk satu transaksi sama dengan kredit (debit) ke akun lain untuk transaksi yang sama. Baca lebih lanjut sebelum menerapkan rumus. Contoh di atas adalah ilustrasi terbaik dari nilai duplikat dalam nilai LOOKUP. Kita perlu membuat nilai LOOKUP unik kita dalam skenario seperti itu dan sampai pada hasilnya.

Artikel yang Direkomendasikan

Artikel ini adalah panduan untuk Mencocokkan Data di Excel. Kami membahas pencocokan data di Excel menggunakan fungsi VLOOKUP, fungsi INDEX + MATCH, dan nilai LOOKUP kami dengan templat Excel yang dapat diunduh. Anda dapat mempelajari lebih lanjut tentang Excel dari artikel berikut: –