Genel formül
=SUMPRODUCT(--(FREQUENCY(MATCH(data,data,0),ROW(data)-ROW(data.firstcell)+1)>0))
Özet
Bir aralıktaki benzersiz metin değerlerini saymak için birkaç işlevi kullanan bir formül kullanabilirsiniz: FREQUENCY, MATCH, ROW ve SUMPRODUCT. Gösterilen örnekte, F5'teki formül şöyledir:
=SUMPRODUCT(--(FREQUENCY(MATCH(B5:B14,B5:B14,0),ROW(B5:B14)-ROW(B5)+1)>0))
B5: B14'te 4 benzersiz ad olduğundan 4 döndürür.
Not: Benzersiz değerleri saymanın başka bir yolu, EĞERSAY işlevini kullanmaktır. Bu çok daha basit bir formüldür, ancak büyük veri kümelerinde yavaş çalışabilir. Excel 365 ile UNIQUE'ye dayalı daha basit ve daha hızlı bir formül kullanabilirsiniz.
Açıklama
Bu formül, benzersiz sayısal değerleri saymak için FREQUENCY kullanan benzer bir formülden daha karmaşıktır, çünkü FREQUENCY sayısal olmayan değerlerle çalışmaz. Sonuç olarak, formülün büyük bir kısmı sayısal olmayan verileri FREQUENCY'in işleyebileceği sayısal verilere dönüştürür.
İçten dışa doğru çalışarak MATCH işlevi, verilerde görünen her bir öğenin konumunu almak için kullanılır:
MATCH(B5:B14,B5:B14,0)
MATCH'ın sonucu şuna benzer bir dizidir:
(1;1;1;4;4;6;6;6;9;9)
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 3 kez göründüğü için, bu dizide 1 sayısı olarak 3 kez görünür.
Bu dizi, veri_dizisi bağımsız değişkeni olarak FREQUENCY'ye beslenir . Bins_array bağımsız değişken Formül bu kısmından oluşturulur:
ROW(B5:B14)-ROW(B5)+1)
verilerdeki her bir değer için sıralı bir sayı listesi oluşturan:
(1;2;3;4;5;6;7;8;9;10)
Bu noktada, FREQUENCY şu şekilde yapılandırılır:
FREQUENCY((1;1;1;4;4;6;6;6;9;9),(1;2;3;4;5;6;7;8;9;10))
SIKLIK, veri dizisindeki her bir sayı 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. Bu, bu formülün işleyişindeki önemli bir özelliktir. FREQUENCY işlevinin sonucu şuna benzer bir dizidir:
(3;0;0;2;0;3;0;0;2;0;0) // output from FREQUENCY
Not: FREQUENCY her zaman, bins_array'den bir fazla öğe içeren bir dizi döndürür .
Şimdi formülü şu şekilde yeniden yazabiliriz:
=SUMPRODUCT(--((3;0;0;2;0;3;0;0;2;0;0)>0))
Ardından, sayıları DOĞRU veya YANLIŞ'a dönüştüren sıfırdan (> 0) büyük değerleri kontrol ederiz, ardından DOĞRU ve YANLIŞ değerlerini 1s ve 0'lara dönüştürmek için çift negatif (-) kullanırız. Şimdi elimizde:
=SUMPRODUCT((1;0;0;1;0;1;0;0;1;0;0))
Son olarak, SUMPRODUCT basitçe sayıları toplar ve toplamı döndürür, bu durumda bu 4'tür.
Boş hücreleri işleme
Aralıktaki boş hücreler formülün #YOK hatası döndürmesine neden olur. Boş hücreleri işlemek için, boş değerleri filtrelemek için EĞER işlevini kullanan daha karmaşık bir dizi formülü kullanabilirsiniz:
(=SUM(IF(FREQUENCY(IF(data"", MATCH(data,data,0)),ROW(data)-ROW(data.firstcell)+1),1)))
Not: IF eklemek, bunu kontrol-üst karakter-enter gerektiren bir dizi formülüne dönüştürür.
Daha fazla bilgi için bu sayfaya bakın.
Benzersiz değerleri saymanın diğer yolları
Excel 365'iniz varsa, benzersiz değerleri çok daha basit bir formülle saymak için BENZERSİZ işlevini kullanabilirsiniz.
Bir pivot tablo, benzersiz değerleri saymanın mükemmel bir yoludur.
