Cara Membandingkan Dua Tabel Data Menggunakan Formula Array Excel

oleh | 5 Apr 2024 | Belajar Excel, Excel Advanced, Excel Intermediate, Tips Excel | 0 Komentar

Dalam dunia bisnis, seringkali kita perlu membandingkan dua tabel data dari dua sumber yang berbeda untuk memastikan keakuratan informasi yang kita miliki. Salah satu cara efektif untuk melakukan ini adalah dengan menggunakan Microsoft Excel dan teknik pembandingan data yang canggih. Pada artikel ini, kita akan belajar bagaimana cara membandingkan dua tabel data antara dua tabel yang berbeda dan menampilkan data yang luput sekaligus mewarnai cellnya menggunakan conditional formatting.

Dalam dunia kerja modern yang dipenuhi dengan analisis data dan pemrosesan informasi, penggunaan formula Excel yang kompleks dapat menjadi kunci untuk efisiensi dan akurasi dalam pekerjaan sehari-hari. Salah satu contoh rumus yang kompleks namun sangat berguna adalah formula array yang digunakan untuk membandingkan dua tabel data dan menampilkan data yang hilang antara dua tabel. Mari kita jelaskan penggunaannya secara detail dalam konteks kerja.

Konteks Pekerjaan

Bayangkan Anda bekerja di departemen penjualan pada sebuah perusahaan. Anda memiliki dua sumber data yang berbeda, misalnya data dari divisi pengiriman dan data dari divisi penjualan. Tugas Anda adalah membandingkan dua tabel data dari kedua sumber data ini untuk memastikan keakuratan penjualan.

Pertama-tama, persiapkan data dari dua tabel yang akan kita bandingkan. Misalnya, kita memiliki tabel penjualan yang diinput oleh Divisi Pengiriman dan tabel yang diinput oleh Divisi Kasir. Kedua tabel ini harus memiliki kolom-kolom yang berisi data yang akan dibandingkan, seperti tanggal transaksi dan jumlah omsetnya.

Sebagai contoh, kita gunakan tabel sederhana seperti berikut untuk membandingkan dua tabel data:

membandingkan dua tabel data

Langkah 1: Menandai Data dengan Conditional Formatting

Setelah kita memiliki data dari dua divisi di tabel 1 dan tabel 2, sebelum menguraikannya di tabel ke-3, pertama-tama yang bisa kita lakukan adalah menandai baris sel untuk data yang ada di tabel 1 tapi tidak muncul di tabel 2. Cara menandai data unik secara otomatis ini kita bisa gunakan conditional formatting sebagai berikut:

  1. Pilih seluruh area data pada tabel 1 (range B7:C21).
  2. Pergi ke tab “Home” di Excel, kemudian pilih “Conditional Formatting” > “New Rule”.
  3. Pilih “Use a formula to determine which cells to format”.
  4. Masukkan rumus berikut di kotak “Format values where this formula is true”:
   =NOT(OR(B7=G$7:G$21))
  1. Klik “Format” untuk memilih warna yang ingin Anda gunakan untuk mewarnai cell yang sesuai dengan rumus.
  2. Klik “OK” untuk menerapkan conditional formatting.
Baca Juga  Cara Membuat Rekap Omset Tahunan dari Data Transaksi Bulanan di Excel
contoh conditional formatting untuk perbandingan

Jika dilakukan dengan benar maka sel dengan data yang tidak muncul di tabel 2 akan terwarnai dengan warna seperti pada gambar di atas.

Langkah 2: Menulis Formula SUMPRODUCT

Langkah berikutnya untuk membandingkan dua tabel data adalah membuat data bantu di sel L5 agar rumus yang akan kita tulis untuk menguraikan data yang ada di tabel 1 tapi luput di tabel 2 lebih pendek. Dan di sini kita akan menggunakan fungsi SUMPRODUCT untuk menghitung data unik di tabel 1.

Dan berikut adalah rumusnya:

=SUMPRODUCT(--ISNA(MATCH($B$7:$B$21&$C$7:$C$21;$G$7:$G$21&$H$7:$H$21;0)))

Dan jika benar, maka hasilnya adalah 3, sama seperti jumlah baris sel yang terwarnai. Berikut penampakannya:

contoh penggunaan rumus sumproduct

Penjelasan Rumus:

Rumus SUMPRODUCT di sini adalah rumus yang cukup kompleks tetapi sangat berguna untuk analisis data yang mendalam seperti membandingkan dua tabel data di atas. Mari kita jelaskan bagaimana rumus ini bekerja:

  1. $B$7:$B$21 dan $C$7:$C$21 adalah kisaran data di dua kolom yang akan dibandingkan dengan data di kolom G dan H.
  2. $G$7:$G$21 dan $H$7:$H$21 adalah kisaran data di kolom G dan H yang akan dibandingkan dengan data di kolom B dan C.
  3. MATCH($B$7:$B$21&$C$7:$C$21;$G$7:$G$21&$H$7:$H$21;0) adalah fungsi pencocokan yang menggabungkan nilai di kolom B dan C untuk dibandingkan dengan nilai yang sama yang digabungkan di kolom G dan H. Fungsi ini akan mengembalikan posisi pertama kecocokan yang ditemukan atau #N/A jika tidak ada kecocokan.
  4. ISNA(...) digunakan untuk mengecek apakah hasil pencocokan adalah #N/A, yang berarti tidak ada kecocokan yang ditemukan. Fungsi ini akan mengembalikan TRUE untuk setiap kecocokan yang tidak ditemukan dan FALSE untuk kecocokan yang ditemukan.
  5. --ISNA(...) digunakan untuk mengubah hasil dari TRUE/FALSE menjadi 1/0, di mana 1 menunjukkan kecocokan yang tidak ditemukan dan 0 menunjukkan kecocokan yang ditemukan.
  6. SUMPRODUCT(...) digunakan untuk menjumlahkan hasil dari langkah sebelumnya, yang pada dasarnya menghitung berapa banyak kecocokan yang tidak ditemukan antara dua kisaran data.
Baca Juga  Membuat Aplikasi Koreksi Pilihan Ganda di Excel Menggunakan Rumus Sederhana

Jadi, secara keseluruhan, rumus ini menghitung berapa banyak kecocokan yang tidak ditemukan antara data di kolom B dan C dengan data di kolom G dan H, dan mengembalikan jumlah kecocokan yang tidak ditemukan tersebut. Rumus ini sangat berguna dalam analisis data untuk mengetahui seberapa sering data dalam dua kisaran tidak cocok satu sama lain.

Langkah 3: Menulis Formula Array

Setelah diketahui jumlah data uniknya maka tugas kita berikutnya adalah menguraikan tanggal-tanggal transaksi yang muncul di tabel 1 tapi tidak muncul di tabel 2. Caranya adalah dengan menggunakan formula array berikut di sel L7:

=IF(ROWS(L$8:L8)<=L$5;INDEX(B$7:B$21;SMALL(IF(ISNA(MATCH($B$7:$B$21&$C$7:$C$21;$G$7:$G$21&$H$7:$H$21;0));ROW($C$7:$C$21)-ROW($C$7)+1);ROWS(L$8:L8)));"")

Jika sudah, akhiri penulisan rumusnya dengan menekan Ctrl + Shift dan Enter (jangan hanya Enter). Jika benar, maka akan ada simbol kurung kurawal ‘{}’ yang mengapit rumus di atas. Kemudian copy dan paste formula array tersebut ke sel-sel yang ada di bawah dan juga sampingnya.

contoh formula array untuk perbandingan data

Penjelasan Rumus:

Rumus ini menggunakan fungsi seperti IF, INDEX, SMALL, IF, ISNA, dan MATCH untuk mencocokkan dan menampilkan data yang tidak tampil di tabel 2. Berikut penjelasan lengkapnya:

  1. ROWS(L$8:L8) digunakan untuk menghitung jumlah baris yang telah diproses dalam tabel hasil. Ini digunakan dalam kombinasi dengan $L$5 yang mungkin berisi batas jumlah baris yang ingin ditampilkan dalam hasil.
  2. INDEX(B$7:B$21;SMALL(...)) digunakan untuk mengambil nilai dari kolom B yang sesuai dengan indeks yang dihasilkan oleh fungsi SMALL.
  3. IF(ISNA(MATCH(...))) digunakan untuk mengecek apakah ada kecocokan antara kolom B dan C di tabel sumber dengan kolom G dan H di tabel pembanding.
  4. ROW($C$7:$C$21)-ROW($C$7)+1 digunakan untuk menghasilkan array baris dari tabel sumber yang sesuai dengan kondisi dalam fungsi IF.
  5. ROWS(L$8:L8) dan ROWS(L$8:L8) digunakan untuk menghasilkan indeks dalam fungsi SMALL, yang secara bertahap mengambil nilai yang cocok sesuai dengan urutan kecil yang ditentukan.

Dengan menggunakan rumus tersebut, Anda dapat membandingkan dua tabel data dan menampilkan data yang hilang antara dua tabel dengan sangat efisien. Jika Anda memiliki pertanyaan lebih lanjut atau membutuhkan bantuan tambahan, jangan ragu untuk bertanya!

Baca Juga  Contoh Penggunaan Rumus IF AND Excel dan Juga IF OR Beserta Penjelasannya

Langkah 4: Format Data

Karena untuk kolom “Omzet” datanya hasil paste dari rumus di kolom “Tanggal” maka jika format di kolom tanggal berupa “DATE” maka format tersebut akan mengikuti juga di kolom “Omset”. Untuk itu, ganti format di kolom “Omzet” menjadi Accounting. Dan berikut hasilnya:

perbandingan data dua tabel

Langkah 5: Hasil Akhir

Dengan langkah-langkah di atas, Anda telah berhasil membandingkan dua tabel data antara dua tabel yang berbeda menggunakan Excel. Data yang hilang dari tabel Divisi Pengiriman namun tidak ada di tabel Divisi Kasir telah ditampilkan dalam tabel baru dan diwarnai agar lebih mudah dikenali. Proses ini dapat membantu Anda dalam mengidentifikasi kesalahan atau perbedaan data antara dua sumber dengan lebih efisien.

Implementasi dalam Kerja Sehari-hari

Dalam konteks kerja sehari-hari, rumus ini akan membantu Anda dengan cara berikut:

  • Efisiensi Analisis Data: Dengan menggunakan rumus ini, Anda dapat dengan cepat membandingkan data dari dua sumber yang berbeda tanpa harus melakukannya secara manual, menghemat waktu dan usaha.
  • Akurasi dan Ketepatan: Rumus ini memastikan bahwa data yang hilang atau tidak cocok antara kedua sumber data teridentifikasi dengan tepat, meminimalkan risiko kesalahan dalam pengelolaan inventaris.
  • Laporan yang Tepat Waktu: Dengan memiliki data yang akurat dan terperinci, Anda dapat menghasilkan laporan inventaris yang tepat waktu dan akurat untuk kebutuhan manajemen dan audit perusahaan.

Kesimpulan Membandingkan dua Tabel data

Penggunaan rumus Excel yang kompleks seperti formula array untuk membandingkan dua tabel data dan menampilkan data yang hilang antara dua tabel dapat memberikan nilai tambah yang signifikan dalam dunia kerja. Dengan memahami dan mengimplementasikan rumus ini secara efektif, Anda dapat meningkatkan efisiensi, akurasi, dan ketepatan dalam analisis data dan pengambilan keputusan. Jangan ragu untuk mencoba dan mengadaptasi rumus ini sesuai dengan kebutuhan kerja Anda!

Dan terakhir, bagi yang membutuhkan file Cara Membandingkan Dua Tabel Data Menggunakan Formula Array Excel ini, silahkan download file contoh membandingkan dua tabel data dari dua tabel berbeda melalui tautan di bawah ini:

0 Komentar

Kirim Komentar

Alamat email Anda tidak akan dipublikasikan. Ruas yang wajib ditandai *

Join Our Newsletter