Ringkasan
Untuk mengira baris dengan logik ATAU, anda boleh menggunakan formula berdasarkan fungsi SUMPRODUCT. Dalam contoh yang ditunjukkan, formula dalam G6 adalah:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
di mana kumpulan (B5: B15), warna1 (C5: C15), dan warna2 (D5: D15) dinamakan julat.
Penjelasan
Salah satu masalah yang lebih sukar di Excel adalah mengira baris dalam satu set data dengan "logik ATAU". Terdapat dua senario asas: (1) anda ingin mengira baris di mana nilai dalam lajur adalah "x" ATAU "y" (2) anda ingin mengira baris di mana nilai, "x", ada dalam satu lajur ATAU yang lain .
Dalam contoh ini, tujuannya adalah untuk mengira baris di mana kumpulan = "a" DAN Warna1 ATAU Warna2 adalah "merah". Ini bermakna kita sedang berusaha dengan senario 2 di atas.
Dengan MAJLIS
Anda mungkin pada mulanya mencapai fungsi COUNTIFS, yang menangani beberapa kriteria secara semula jadi. Walau bagaimanapun, fungsi COUNTIFS menggabungkan syarat dengan logik AND, jadi semua kriteria mestilah BENAR untuk disertakan dalam kiraan:
=COUNTIFS(group,"a",color1,"red",color2,"red") // returns 1
Ini menjadikan COUNTIFS tidak dapat dilaksanakan, melainkan jika kita menggunakan beberapa contoh COUNTIFS:
=COUNTIFS(group,"a",color1,"red")+COUNTIFS(group,"a",color2,"red")-COUNTIFS(group,"a",color1,"red",color2,"red")
Terjemahan: hitung baris di mana kumpulan adalah "a" dan warna1 adalah "merah" + hitung baris di mana kumpulan adalah "a" dan warna2 adalah "merah" - hitung baris di mana kumpulan adalah "a" dan warna1 adalah "merah" dan warna2 adalah " merah "(untuk mengelakkan pengiraan berganda).
Ini berfungsi, tetapi anda dapat melihat bahawa ini adalah formula yang agak rumit dan berlebihan.
Dengan logik Boolean
Penyelesaian yang lebih baik adalah menggunakan logik Boolean, dan memproses hasilnya dengan fungsi SUMPRODUCT. (Sekiranya anda memerlukan buku asas pada algebra Boolean, video ini memberikan pengenalan.) Dalam contoh yang ditunjukkan, formula dalam G6 adalah:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
di mana kumpulan (B5: B15), warna1 (C5: C15), dan warna2 (D5: D15) dinamakan julat.
Bahagian pertama masalah adalah menguji kumpulan = "a" yang kita lakukan seperti ini:
(group="a")
Kerana julat B5: B15 mengandungi 11 sel, ungkapan ini mengembalikan array dari 11 nilai BENAR dan SALAH seperti ini:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Setiap BENAR mewakili baris di mana kumpulan itu "A"
Seterusnya, kita perlu memeriksa nilai "merah" di ruang1 atau lajur2. Kami melakukan ini dengan dua ungkapan yang digabungkan dengan penambahan (+), kerana penambahan sesuai dengan logik ATAU dalam algebra Boolean:
(color1="red")+(color2="red")
Excel secara automatik menilai nilai BENAR dan SALAH sebagai 1s dan 0s semasa sebarang operasi matematik, jadi hasil dari ungkapan di atas adalah susunan seperti ini:
(2;0;0;1;1;0;1;0;0;0;1)
Nombor pertama dalam array adalah 2, kerana kedua Color1 dan Color2 "merah" di baris pertama. Atas sebab-sebab yang dijelaskan di bawah, kita perlu berhati-hati dengan keadaan ini dengan memeriksa nilai yang lebih besar daripada sifar:
((2;0;0;1;1;0;1;0;0;0;1))>0
Kini kita mempunyai pelbagai nilai BENAR dan SALAH:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Jadual di bawah meringkaskan bagaimana Excel menilai logik warna yang dijelaskan di atas:
Pada ketika ini, kami mempunyai hasil dari menguji Kumpulan = "a" dalam satu array:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Dan hasil pengujian "merah" pada Color1 atau Color2 dalam array lain:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Langkah seterusnya adalah menyatukan kedua susunan ini bersama "logik DAN". Untuk melakukan ini, kita menggunakan pendaraban (*), kerana pendaraban sesuai dengan logik AND dalam algebra Boolean.
Setelah mengalikan dua tatasusunan bersama, kami mempunyai susunan tunggal 1s dan 0s, yang dihantar terus ke fungsi SUMPRODUCT:
=SUMPRODUCT((1;0;0;0;1;0;0;0;0;0;0))
Fungsi SUMPRODUCT mengembalikan jumlah nombor, 2, sebagai hasil akhir. Ini adalah kiraan baris di mana kumpulan = "a" DAN Warna1 ATAU Warna2 adalah "merah".
Untuk mengelakkan pengiraan berganda
Kami tidak mahu mengira dua baris di mana Color1 dan Color2 berwarna "merah". Inilah sebabnya mengapa kami memeriksa hasil dari (color1 = "red") + (color2 = "red") untuk nilai yang lebih besar daripada sifar dalam kod di bawah:
((color1="red")+(color2="red"))>0
Tanpa pemeriksaan ini, 2 dari baris pertama dalam data akan muncul dalam susunan terakhir, dan menyebabkan formula tidak akan mengembalikan 3 sebagai kiraan terakhir.
Pilihan PENAPIS
Satu perkara yang baik mengenai logik Boolean ialah ia berfungsi dengan sempurna dengan fungsi terbaru Excel, seperti XLOOKUP dan FILTER. Sebagai contoh, fungsi FILTER dapat menggunakan logik yang sama seperti yang dijelaskan di atas untuk mengekstrak baris yang sepadan:
=FILTER(B5:D15,(group="a")*((color1="red")+(color2="red")>0))
Hasil dari FILTER adalah dua baris yang memenuhi kriteria, seperti yang terlihat di bawah:
Sekiranya anda ingin mengetahui lebih lanjut mengenai fungsi baru ini, kami mempunyai gambaran keseluruhan, dan latihan video.
