Metode Berbeda untuk Mencocokkan Data di Excel
Ada berbagai cara untuk mencocokkan data di excel, jika kita ingin mencocokkan data di kolom yang sama katakanlah kita ingin memeriksa duplikat kita dapat menggunakan format bersyarat dari tab beranda atau jika kita ingin mencocokkan data menjadi dua atau lebih banyak kolom yang berbeda kita dapat menggunakan fungsi bersyarat seperti fungsi if.
- Metode # 1 - Menggunakan Fungsi Vlookup
- Metode # 2 - Menggunakan Indeks + Fungsi Pencocokan
- Metode # 3 - Buat Nilai Pencarian Anda Sendiri
Sekarang mari kita bahas masing-masing metode secara rinci
Anda dapat mendownload Template Excel Data Pertandingan ini di sini - Template Excel Data Pertandingan# 1 - Cocokkan Data Menggunakan Fungsi VLOOKUP
VLOOKUP tidak hanya digunakan untuk mendapatkan informasi yang diperlukan dari tabel data tetapi juga dapat digunakan sebagai alat rekonsiliasi. Ketika datang ke rekonsiliasi atau mencocokkan data rumus VLOOKUP memimpin tabel.
Untuk 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 mendamaikan apakah data dalam dua tabel cocok atau tidak. Cara pertama mencocokkan data adalah fungsi SUM di excel ke dua tabel untuk mendapatkan total penjualan.
Data 1 - Tabel
Data 2 - Tabel
Saya telah menerapkan fungsi SUM untuk kedua kolom Jumlah Penjualan pada tabel. Pada langkah awal itu sendiri, kami mendapatkan perbedaan nilai. Tabel Data 1 menunjukkan total penjualan 2,16,214 dan tabel Data 2 menunjukkan total penjualan 2,10,214 .
Sekarang kita perlu memeriksa ini secara rinci. Jadi, mari terapkan fungsi VLOOKUP untuk setiap tanggal.
Pilih larik tabel sebagai rentang Data 1 .
Kami membutuhkan data dari kolom kedua dan rentang pencarian adalah SALAH yaitu Pencocokan Tepat.
Outputnya diberikan di bawah ini:
Di sel berikutnya kurangi nilai asli dengan nilai tiba.
Setelah dikurangi kita mendapatkan hasilnya sebagai nol.
Sekarang salin dan tempel rumus ke semua sel untuk mendapatkan nilai varians.
Di sel G6 dan G12 kami mendapatkan perbedaan.
Di Data 1 kami memiliki 12104 untuk Tanggal 04-Mar-2019 dan di Data 2 kami memiliki 15104 untuk tanggal yang sama, jadi ada selisih 3000.
Demikian pula untuk tanggal 18-Mar-2019 di Data 1 ada 19351 dan di Data 2 ada 10351, jadi selisih 9000.
# 2 - Cocokkan Data Menggunakan Fungsi INDEX + MATCH
Untuk data yang sama, kita bisa menggunakan fungsi INDEX + MATCH. Kita dapat menggunakan ini sebagai alternatif dari fungsi VLOOKUP.
Fungsi INDEX digunakan untuk mendapatkan nilai dari kolom yang dipilih berdasarkan nomor baris yang disediakan. Untuk memberikan nomor baris kita perlu menggunakan fungsi MATCH berdasarkan nilai LOOKUP.
Buka fungsi INDEX di sel F3.
Pilih larik sebagai rentang kolom hasil yaitu B2 hingga B14.
Untuk mendapatkan nomor baris, buka fungsi MATCH sekarang sebagai argumen berikutnya.
Pilih nilai pencarian sebagai sel D3.
Selanjutnya pilih lookup array sebagai kolom Sales Date pada Data 1.
Di jenis pencocokan, pilih "0 - Pencocokan Tepat".
Tutup dua tanda kurung dan tekan tombol enter untuk mendapatkan hasilnya.
Ini juga memberikan hasil yang sama seperti VLOOKUP saja. Karena kami telah menggunakan data yang sama, kami mendapatkan angkanya sebagaimana adanya
# 3 - Buat Nilai Pencarian Anda Sendiri
Sekarang kita telah melihat bagaimana mencocokkan data menggunakan fungsi excel. Sekarang kita akan melihat skenario waktu nyata yang berbeda. Untuk contoh ini lihat data di bawah ini.
Pada data di atas, kami memiliki data penjualan berdasarkan Zona dan Tanggal seperti yang ditunjukkan di atas. Kami perlu melakukan proses pencocokan data lagi. Mari terapkan fungsi VLOOKUP seperti contoh sebelumnya.
Kami punya banyak varian. Mari kita periksa setiap kasus per kasus.
Di sel I5 kita mendapatkan varian 8300. Mari kita lihat tabel utama.
Padahal pada tabel utama nilainya 12104 kita mendapatkan nilai 20404 dari fungsi VLOOKUP. Alasannya adalah VLOOKUP dapat mengembalikan nilai dari nilai pencarian pertama yang ditemukan.
Dalam kasus ini, nilai pencarian kami adalah tanggal yaitu 20-Mar-2019. Di sel di atas untuk zona Utara untuk tanggal yang sama kami memiliki nilai 20404, jadi VLOOKUP telah mengembalikan nilai ini untuk zona Timur juga.
Untuk mengatasi masalah ini kita perlu membuat nilai pencarian yang unik. Gabungkan Zona, Tanggal & Jumlah Penjualan di Data 1 & Data 2.
Data 1 - Tabel
Data 2 - Tabel
Sekarang kami telah membuat nilai unik untuk setiap zona dengan nilai gabungan dari Zona, Tanggal Penjualan, dan Jumlah Penjualan.
Menggunakan nilai unik ini memungkinkan menerapkan fungsi VLOOKUP.
Terapkan rumus ke semua sel, kita akan mendapatkan varians nol di semua sel.
Seperti ini, dengan menggunakan fungsi excel kita dapat mencocokkan data dan menemukan variansnya. Sebelum menerapkan rumus, kita perlu melihat duplikat dalam nilai pencarian untuk rekonsiliasi yang akurat. Contoh di atas adalah ilustrasi terbaik dari nilai duplikat dalam nilai pencarian. Dalam skenario seperti itu, kita perlu membuat nilai pencarian unik kita sendiri dan mendapatkan hasilnya.
