Обща формула
=SUMPRODUCT(1/COUNTIF(data,data))
Обобщение
За да преброите броя на уникалните стойности в диапазон от клетки, можете да използвате формула, базирана на функциите COUNTIF и SUMPRODUCT. В примерното шоу формулата във F6 е:
=SUMPRODUCT(1/COUNTIF(B5:B14,B5:B14))
Обяснение
Работейки отвътре навън, COUNTIF е конфигуриран на стойности в диапазона B5: B14, като се използват всички тези същите стойности като критерии:
COUNTIF(B5:B14,B5:B14)
Тъй като предоставяме 10 стойности за критерии, връщаме масив с 10 резултата като този:
(3;3;3;2;2;3;3;3;2;2)
Всяко число представлява брой - „Джим“ се появява 3 пъти, „Сю“ се появява 2 пъти и т.н.
Този масив е конфигуриран като делител с 1 като числител. След разделяне получаваме друг масив:
(0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5;0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5)
Всички стойности, които се появяват само веднъж в диапазона, ще се показват като 1s, но стойностите, които се появяват многократно, ще се появяват като дробни стойности, които съответстват на множеството. (т.е. стойност, която се появява 4 пъти в данни, ще генерира 4 стойности = 0,25).
И накрая, функцията SUMPRODUCT сумира всички стойности в масива и връща резултата.
Работа с празни клетки
Един от начините за обработка на празни или празни клетки е да се коригира формулата, както следва:
=SUMPRODUCT(1/COUNTIF(data,data&""))
Чрез обединяването на празен низ ("") в данните предотвратяваме завършването на нули в масива, създаден от COUNTIF, когато в данните има празни клетки. Това е важно, тъй като нула в делителя ще накара формулата да изведе грешка # DIV / 0. Работи, защото използването на празен низ ("") за критерии ще отчита празните клетки.
Въпреки това, въпреки че тази версия на формулата няма да изведе грешка # DIV / 0, когато има празни клетки, тя ще включва празни клетки в броя. Ако искате да изключите празни клетки от броя, използвайте:
=SUMPRODUCT((data"")/COUNTIF(data,data&""))
Това води до премахване на броя на празните клетки, като числителят е нула за свързаните броения.
Бавно представяне?
Това е готина и елегантна формула, но тя изчислява много по-бавно от формулите, които използват ЧЕСТОТА за преброяване на уникални стойности. За по-големи набори от данни може да искате да преминете към формула, базирана на функцията ЧЕСТОТА. Ето формула за числови стойности и една за текстови стойности.
