Genel formül
(=SUM(--(FREQUENCY(IF(criteria,MATCH(vals,vals,0)),ROW(vals)-ROW(vals.first)+1)>0)))
Özet
Ölçüt içeren bir aralıktaki benzersiz metin değerlerini saymak için, SIKLIK ve KAÇINCI işlevlerine dayalı bir dizi formülü kullanabilirsiniz. Gösterilen örnekte, G6'daki formül şöyledir:
(=SUM(--(FREQUENCY(IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0)),ROW(B5:B11)-ROW(B5)+1)>0)))
Omega projesinde üç farklı kişi çalıştığı için 3 döndürür.
Not: Bu bir dizi formülüdür ve kontrol + shift + enter ile girilmelidir.
Açıklama
Bu, MATCH işleviyle türetilen sayısal değerleri saymak için FREQUENCY kullanan karmaşık bir formüldür. İçten dışa doğru çalışarak MATCH işlevi, verilerde görünen her bir değerin konumunu elde etmek için kullanılır:
MATCH(B5:B11,B5:B11,0)
MATCH'ın sonucu şuna benzer bir dizidir:
(1;1;3;1;1;6;7)
KAÇINCI her zaman ilk eşleşmenin konumunu döndürdüğünden, verilerde birden fazla görünen değerler aynı konumu döndürür. Örneğin, "Jim" listede 4 kez göründüğü için, bu dizide 1 sayısı olarak 4 kez görünür.
KAÇINCI işlevi dışında, EĞER işlevi kriterleri uygulamak için kullanılır ve bu durumda projenin "omega" olup olmadığını (G5 hücresinden) test etmeyi içerir:
IF(C5:C11=G5 // filter on "omega"
EĞER işlevi, yalnızca "omega" ile ilişkilendirilmişlerse MATCH'tan gelen değerlerin geçmesine izin veren bir filtre görevi görür. Sonuç şuna benzer bir dizidir:
(FALSE;FALSE;FALSE;1;1;6;7) // after filtering
Filtrelenen dizi, veri_dizisi bağımsız değişkeni olarak doğrudan FREQUENCY işlevine gönderilir . Daha sonra SATIR işlevi, verilerdeki her bir değer için sıralı bir sayı listesi oluşturmak için kullanılır:
ROW(B3:B12)-ROW(B3)+1
Bu, şöyle bir dizi oluşturur:
(1;2;3;4;5;6;7;8;9;10)
bu FILTER'daki bins_array argümanı olur . Bu noktada elimizde:
FREQUENCY((FALSE;FALSE;FALSE;1;1;6;7),(1;2;3;4;5;6;7))
SIKLIK, veri dizisindeki her bir değer için bölmeye göre düzenlenmiş bir sayıyı gösteren bir sayı dizisi döndürür. Bir sayı zaten sayıldığında, FREQUENCY sıfır döndürür. FREQUENCY işlevinin sonucu şuna benzer bir dizidir:
(2;0;0;0;0;1;1;0) // result from FREQUENCY
Not: FREQUENCY her zaman, bins_array'den bir fazla öğe içeren bir dizi döndürür .
Bu noktada formülü şu şekilde yeniden yazabiliriz:
=SUM(--((2;0;0;0;0;1;1;0)>0))
Sayıları DOĞRU veya YANLIŞ'a dönüştüren sıfırdan büyük değerleri kontrol ederiz:
=SUM(--((TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE)))
Sonra mantıksal değerleri 1'ler ve 0'lara zorlamak için çift negatif kullanırız:
=SUM((1;0;0;0;0;1;1;0))
Son olarak, TOPLA işlevi nihai sonuç olarak 3 değerini döndürür.
Not: Bu bir dizi formülüdür ve Control + Shift + Enter kullanılarak girilmelidir.
Aralıktaki boş hücreleri işleme
Aralıktaki herhangi bir hücre boşsa, boş hücrelerin MATCH işlevine geçirilmesini önlemek için formülü ayarlamanız gerekir, bu da bir hata verir. Bunu, boş hücreleri kontrol etmek için başka bir iç içe geçmiş EĞER işlevi ekleyerek yapabilirsiniz:
(=SUM(--(FREQUENCY(IF(B5:B11"",IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0))),ROW(B5:B11)-ROW(B5)+1)>0)))
İki kriterle
İki kriteriniz varsa, başka bir iç içe EĞER ekleyerek formülün mantığını genişletebilirsiniz:
(=SUM(--(FREQUENCY(IF(c1,IF(c2,MATCH(vals,vals,0))),ROW(vals)-ROW(vals.1st)+1)>0)))
Burada c1 = ölçüt1 , c2 = ölçüt2 ve vals = değer aralığı.
Boole mantığı ile
Boole mantığı ile iç içe geçmiş IF'leri azaltabilirsiniz:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),MATCH(vals,vals,0)),ROW(vals)-ROW(vals.1st)+1)>0)))
Bu, ek ölçütler eklemeyi ve yönetmeyi kolaylaştırır.
