Özet
VEYA mantığıyla satırları saymak için SUMPRODUCT işlevine dayalı bir formül kullanabilirsiniz. Gösterilen örnekte, G6'daki formül şöyledir:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
burada grup (B5: B15), renk1 (C5: C15) ve renk2 (D5: D15) adlandırılmış aralıklardır.
Açıklama
Excel'deki daha yanıltıcı sorunlardan biri, bir veri kümesindeki satırları "VEYA mantığı" ile saymaktır. İki temel senaryo vardır: (1) bir sütundaki değerin "x" VEYA "y" olduğu satırları saymak istiyorsunuz (2) "x" değerinin bir sütunda VEYA başka bir sütunda mevcut olduğu satırları saymak istiyorsunuz .
Bu örnekte amaç, grup = "a" VE Renk1 VEYA Renk2'nin "kırmızı" olduğu satırları saymaktır. Bu, yukarıdaki senaryo 2 ile çalıştığımız anlamına gelir.
COUNTIFS ile
İlk başta, yerel olarak birden çok ölçütü işleyen COUNTIFS işlevine erişebilirsiniz. Ancak, EĞERSAY işlevi, VE mantığıyla koşulları birleştirir, bu nedenle sayıma dahil edilmesi için tüm ölçütlerin DOĞRU olması gerekir:
=COUNTIFS(group,"a",color1,"red",color2,"red") // returns 1
Bu, COUNTIFS'in birden çok örneğini kullanmadığımız sürece COUNTIFS'i çalışmaz hale getirir:
=COUNTIFS(group,"a",color1,"red")+COUNTIFS(group,"a",color2,"red")-COUNTIFS(group,"a",color1,"red",color2,"red")
Çeviri: grubun "a" ve renk1'in "kırmızı" olduğu satırları sayın + grubun "a" ve renk2'nin "kırmızı" olduğu satırları sayın - grubun "a" ve renk1'in "kırmızı" ve renk2'nin "olduğu satırları sayın red "(iki kez saymayı önlemek için).
Bu işe yarıyor, ancak bunun biraz karmaşık ve gereksiz bir formül olduğunu görebilirsiniz.
Boole mantığı ile
Daha iyi bir çözüm, Boole mantığını kullanmak ve sonucu SUMPRODUCT işlevi ile işlemektir. (Boole cebri üzerine bir primere ihtiyacınız varsa, bu video bir giriş sağlar.) Gösterilen örnekte, G6'daki formül:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
burada grup (B5: B15), renk1 (C5: C15) ve renk2 (D5: D15) adlandırılmış aralıklardır.
Problemin ilk kısmı, şu şekilde yaptığımız grup = "a" için test etmektir:
(group="a")
B5: B15 aralığı 11 hücre içerdiğinden, bu ifade aşağıdaki gibi 11 TRUE ve FALSE değerlerinden oluşan bir dizi döndürür:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Her DOĞRU, grubun "A" olduğu bir satırı temsil eder.
Daha sonra, sütun1 veya sütun2'deki "kırmızı" değerini kontrol etmemiz gerekir. Bunu, Boole cebirinde toplama OR mantığına karşılık geldiğinden, toplama (+) ile birleştirilen iki ifade ile yapıyoruz:
(color1="red")+(color2="red")
Excel, herhangi bir matematik işlemi sırasında DOĞRU ve YANLIŞ değerleri otomatik olarak 1'ler ve 0'lar olarak değerlendirir, bu nedenle yukarıdaki ifadenin sonucu şu şekilde bir dizidir:
(2;0;0;1;1;0;1;0;0;0;1)
Dizideki ilk sayı 2'dir, çünkü hem Color1 hem de Color2 ilk satırda "kırmızı" dır. Aşağıda açıklanan nedenlerden dolayı, sıfırdan büyük değerleri kontrol ederek bu duruma karşı önlem almamız gerekir:
((2;0;0;1;1;0;1;0;0;0;1))>0
Şimdi yine bir dizi DOĞRU ve YANLIŞ değerler var:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Aşağıdaki tablo, Excel'in yukarıda açıklanan renk mantığını nasıl değerlendirdiğini özetlemektedir:
Bu noktada, Grup = "a" nın tek bir dizide test edilmesinin sonuçlarına sahibiz:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Ve başka bir dizide Color1 veya Color2'de "kırmızı" testinin sonuçları:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Bir sonraki adım, bu iki diziyi "VE mantığı" ile bir araya getirmektir. Bunu yapmak için, çarpma (*) kullanıyoruz, çünkü çarpma, Boole cebirindeki AND mantığına karşılık gelir.
İki diziyi birlikte çarptıktan sonra, doğrudan SUMPRODUCT işlevine gönderilen tek bir 1 ve 0 dizimiz var:
=SUMPRODUCT((1;0;0;0;1;0;0;0;0;0;0))
SUMPRODUCT işlevi, nihai sonuç olarak 2 sayılarının toplamını döndürür. Bu, grup = "a" VE Renk1 VEYA Renk2'nin "kırmızı" olduğu satırların sayısıdır.
Çift sayımı önlemek için
Hem Color1 hem de Color2'nin "kırmızı" olduğu satırları iki kez saymak istemiyoruz. Bu nedenle aşağıdaki kodda sıfırdan büyük değerler için (color1 = "red") + (color2 = "red") sonuçlarına bakıyoruz:
((color1="red")+(color2="red"))>0
Bu kontrol olmadan, verilerdeki ilk satırdan 2, son dizide görünür ve formülün hatalı bir şekilde son sayı olarak 3'ü döndürmesine neden olur.
FİLTRE seçeneği
Boole mantığıyla ilgili güzel bir şey, XLOOKUP ve FILTER gibi Excel'in en yeni işlevleriyle mükemmel çalışmasıdır. Örneğin, FİLTRE işlevi, eşleşen satırları çıkarmak için yukarıda açıklanan mantığın aynısını kullanabilir:
=FILTER(B5:D15,(group="a")*((color1="red")+(color2="red")>0))
FILTER'ın sonucu, aşağıda görüldüğü gibi kriterleri karşılayan iki satırdır:
Bu yeni işlevler hakkında daha fazla bilgi edinmek isterseniz, bir genel bakışımız ve video eğitimimiz var.
