Ringkasan
Untuk menghitung baris dengan logika OR, Anda dapat menggunakan rumus yang didasarkan pada fungsi SUMPRODUCT. Pada contoh yang ditunjukkan, rumus di G6 adalah:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
di mana grup (B5: B15), color1 (C5: C15), dan color2 (D5: D15) diberi nama rentang.
Penjelasan
Salah satu masalah yang lebih rumit di Excel adalah menghitung baris dalam satu set data dengan "logika OR". Ada dua skenario dasar: (1) Anda ingin menghitung baris dengan nilai dalam kolom adalah "x" ATAU "y" (2) Anda ingin menghitung baris yang memiliki nilai, "x", dalam satu kolom ATAU lainnya .
Dalam contoh ini, sasarannya adalah menghitung baris di mana grup = "a" DAN Warna1 ATAU Warna2 berwarna "merah". Ini berarti kami sedang mengerjakan skenario 2 di atas.
Dengan COUNTIFS
Anda mungkin pada awalnya menjangkau fungsi COUNTIFS, yang menangani beberapa kriteria secara native. Namun, fungsi COUNTIFS menggabungkan kondisi dengan logika AND, jadi semua kriteria harus BENAR untuk disertakan dalam hitungan:
=COUNTIFS(group,"a",color1,"red",color2,"red") // returns 1
Ini membuat COUNTIFS tidak bisa bekerja, kecuali 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 grupnya adalah "a" dan color1 adalah "red" + hitung baris di mana grupnya adalah "a" dan color2 adalah "red" - hitung baris di mana grupnya adalah "a" dan color1 adalah "red" dan color2 adalah " merah "(untuk menghindari penghitungan ganda).
Ini berfungsi, tetapi Anda dapat melihat ini adalah rumus yang agak rumit dan berlebihan.
Dengan logika Boolean
Solusi yang lebih baik adalah menggunakan logika Boolean, dan memproses hasilnya dengan fungsi SUMPRODUCT. (Jika Anda membutuhkan primer tentang aljabar Boolean, video ini memberikan pendahuluan.) Dalam contoh yang ditunjukkan, rumus di G6 adalah:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
di mana grup (B5: B15), color1 (C5: C15), dan color2 (D5: D15) diberi nama rentang.
Bagian pertama dari masalahnya adalah menguji group = "a" yang kita lakukan seperti ini:
(group="a")
Karena rentang B5: B15 berisi 11 sel, ekspresi ini mengembalikan larik 11 nilai TRUE dan FALSE seperti ini:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Setiap TRUE mewakili baris di mana grupnya adalah "A".
Selanjutnya, kita perlu memeriksa nilai "merah" di kolom1 atau kolom2. Kami melakukan ini dengan dua ekspresi yang digabungkan dengan penambahan (+), karena penambahan sesuai dengan logika OR dalam aljabar Boolean:
(color1="red")+(color2="red")
Excel secara otomatis mengevaluasi nilai TRUE dan FALSE sebagai 1 dan 0 selama operasi matematika apa pun, sehingga hasil dari ekspresi di atas adalah array seperti ini:
(2;0;0;1;1;0;1;0;0;0;1)
Angka pertama dalam larik adalah 2, karena Warna1 dan Warna2 berwarna "merah" di baris pertama. Untuk alasan yang dijelaskan di bawah, kita perlu waspada terhadap situasi ini dengan memeriksa nilai yang lebih besar dari nol:
((2;0;0;1;1;0;1;0;0;0;1))>0
Sekarang kita kembali memiliki array nilai TRUE dan FALSE:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Tabel di bawah ini meringkas bagaimana Excel mengevaluasi logika warna yang dijelaskan di atas:
Pada titik ini, kami memiliki hasil dari pengujian Grup = "a" dalam satu larik:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Dan hasil dari pengujian "merah" di Color1 atau Color2 dalam larik lain:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Langkah selanjutnya adalah menyatukan kedua larik ini dengan "logika AND". Untuk melakukan ini, kami menggunakan perkalian (*), karena perkalian sesuai dengan logika AND dalam aljabar Boolean.
Setelah mengalikan dua larik bersama-sama, kita memiliki larik tunggal 1 dan 0, yang dikirim langsung ke fungsi SUMPRODUCT:
=SUMPRODUCT((1;0;0;0;1;0;0;0;0;0;0))
Fungsi SUMPRODUCT mengembalikan jumlah angka, 2, sebagai hasil akhir. Ini adalah jumlah baris di mana grup = "a" DAN Warna1 ATAU Warna2 berwarna "merah".
Untuk menghindari penghitungan ganda
Kami tidak ingin menghitung dua kali baris di mana Color1 dan Color2 berwarna "merah". Inilah mengapa kami memeriksa hasil dari (color1 = "red") + (color2 = "red") untuk nilai yang lebih besar dari nol pada kode di bawah ini:
((color1="red")+(color2="red"))>0
Tanpa pemeriksaan ini, angka 2 dari baris pertama dalam data akan muncul di larik terakhir, dan menyebabkan rumus salah mengembalikan 3 sebagai hitungan akhir.
Opsi FILTER
Satu hal yang menyenangkan tentang logika Boolean adalah ia berfungsi sempurna dengan fungsi terbaru Excel, seperti XLOOKUP dan FILTER. Misalnya, fungsi FILTER dapat menggunakan logika yang sama persis seperti yang dijelaskan di atas untuk mengekstrak baris yang cocok:
=FILTER(B5:D15,(group="a")*((color1="red")+(color2="red")>0))
Hasil dari FILTER adalah dua baris yang memenuhi kriteria, seperti terlihat di bawah ini:
Jika Anda ingin mempelajari lebih lanjut tentang fungsi-fungsi baru ini, kami memiliki ikhtisar, dan pelatihan video.
