Formula generik
=SUMPRODUCT(COUNTIF(range1,range2))
Ringkasan
Untuk membandingkan dua julat, dan mengira jumlah padanan antara dua julat, anda boleh menggunakan formula yang menggabungkan fungsi COUNTIF dengan fungsi SUMPRODUCT. Dalam contoh yang ditunjukkan, formula dalam sel G7 adalah:
=SUMPRODUCT(COUNTIF(B5:B11,D5:D11))
Catatan: formula ini tidak mementingkan lokasi atau pesanan item dalam setiap julat.
Penjelasan
Katakan anda mempunyai senarai "induk" semacam itu, dan juga mempunyai senarai lain yang mengandungi beberapa item yang sama. Anda mahukan formula yang membandingkan nilai dalam senarai ke-2 untuk melihat berapa banyak yang muncul dalam senarai pertama. Anda tidak peduli dengan urutan item tersebut, anda hanya ingin tahu berapa banyak item dalam senarai 2 yang muncul dalam senarai 1.
Fungsi COUNTIF akan mengira perkara dalam julat yang memenuhi kriteria anda. Biasanya, anda akan memberi COUNTIF julat seperti A1: A10 dan kriteria sederhana seperti "> 10". COUNTIF kemudian akan mengembalikan jumlah sel di A1: A10 yang lebih besar daripada 10.
Dalam kes ini, kami memberikan COUNTIF pelbagai kriteria. Kami tidak menggunakan pengendali logik, yang bermaksud COUNTIF akan memeriksa kesetaraan (iaitu ia berkelakuan seolah-olah kami menggunakan operator sama dengan (=)).
Kerana kami memberi COUNTIF rentang (juga disebut "array") yang berisi 7 item, COUNTIF akan mengembalikan array dari 7 item sebagai hasilnya. Setiap item dalam susunan hasil mewakili kiraan. Dalam contoh ini, susunan yang dikembalikan oleh COUNTIF seperti ini:
(1; 1; 0; 1; 1; 0; 0)
Sekarang kita hanya perlu menambahkan item dalam susunan ini, yang merupakan tugas yang sempurna untuk SUMPRODUCT. Fungsi SUMPRODUCT adalah fungsi serba boleh yang mengendalikan tatasusunan secara semula jadi tanpa sintaks array khas.
Sekiranya anda memberikan SUMPRODUCT dua atau lebih tatasusunan, ia akan melipatgandakan tatasusunan bersama, menjumlahkan hasilnya, dan mengembalikan nombor itu. Dalam kes ini, kami memberikan SUMPRODUCT hanya satu array, jadi ia hanya merangkum item dalam array dan mengembalikan 4 hasilnya.
Padankan antara baris
Sekiranya anda ingin membandingkan dua julat atau lajur, dan ingin mengira padanan pada tahap baris (iaitu hanya mengira padanan apabila item yang sama muncul dalam kedudukan yang sama), anda memerlukan formula yang berbeza.
