Formula generik
=COUNTIFS(range,"*"&$item1&"*",range,"*"&item2&"*")
Ringkasan
Untuk membina jadual ringkasan dengan jumlah item berpasangan yang muncul dalam senarai kombinasi yang ada, anda boleh menggunakan kolum penolong dan formula berdasarkan fungsi COUNTIFS. Dalam contoh yang ditunjukkan formula dalam sel H5 adalah:
=IF($G5=H$4,"-",COUNTIFS(helper,"*"&$G5&"*",helper,"*"&H$4&"*"))
di mana "helper" adalah julat bernama E5: E24.
Catatan: formula ini menganggap item tidak berulang dalam kombinasi tertentu (iaitu AAB, EFE bukan gabungan yang sah).
Penjelasan
Kami ingin mengira kekerapan item dalam lajur B, C, dan D muncul bersama. Contohnya, seberapa kerap A muncul dengan C, B muncul dengan F, G muncul dengan D, dan sebagainya. Ini sepertinya penggunaan COUNTIFS yang sempurna, tetapi jika kita cuba menambahkan kriteria mencari 2 item di 3 lajur, itu tidak akan berjaya.
Penyelesaian yang mudah adalah menggabungkan semua item dalam satu sel, kemudian gunakan COUNTIFS dengan wildcard untuk mengira item. Kami melakukannya dengan lajur penolong (E) yang bergabung dengan item di lajur B, C, dan D menggunakan fungsi CONCAT seperti ini:
=CONCAT(B5:D5)
Dalam versi Excel yang lebih lama, anda boleh menggunakan formula seperti ini:
=B5&C5&D5
Kerana item berulang tidak dibenarkan dalam kombinasi, bahagian pertama formula tidak termasuk item yang sepadan. Sekiranya kedua-dua item itu sama, formula mengembalikan tanda hubung atau tanda hubung sebagai teks:
=IF($G5=H$4,"-"
Sekiranya item berbeza, fungsi COUNTIFS dijalankan:
COUNTIFS(helper,"*"&$G5&"*",helper,"*"&H$4&"*")
COUNTIFS dikonfigurasi untuk menghitung "pasang" item. Hanya apabila item di lajur G dan item yang sesuai dari baris 4 muncul bersama dalam sel barulah pasangan itu dikira. Wildcard (*) digabungkan ke kedua-dua sisi item untuk memastikan perlawanan akan dihitung di mana sahaja ia muncul di sel.
