Обща формула
=SUMPRODUCT(--(FREQUENCY(MATCH(data,data,0),ROW(data)-ROW(data.firstcell)+1)>0))
Обобщение
За да преброите уникални текстови стойности в диапазон, можете да използвате формула, която използва няколко функции: FREQUENCY, MATCH, ROW и SUMPRODUCT. В показания пример формулата във F5 е:
=SUMPRODUCT(--(FREQUENCY(MATCH(B5:B14,B5:B14,0),ROW(B5:B14)-ROW(B5)+1)>0))
което връща 4, тъй като в B5 има 4 уникални имена: B14.
Забележка: Друг начин за преброяване на уникални стойности е използването на функцията COUNTIF. Това е много по-проста формула, но може да работи бавно при големи масиви от данни. С Excel 365 можете да използвате по-проста и по-бърза формула, базирана на УНИКАЛЕН.
Обяснение
Тази формула е по-сложна от подобна формула, която използва FREQUENCY за преброяване на уникални числови стойности, защото FREQUENCY не работи с нечислови стойности. В резултат на това голяма част от формулата просто трансформира нечисловите данни в числови данни, които FREQUENCY може да обработва.
Работейки отвътре навън, функцията MATCH се използва за получаване на позицията на всеки елемент, който се появява в данните:
MATCH(B5:B14,B5:B14,0)
Резултатът от MATCH е масив като този:
(1;1;1;4;4;6;6;6;9;9)
Тъй като MATCH винаги връща позицията на първото съвпадение, стойностите, които се появяват повече от веднъж в данните, връщат една и съща позиция. Например, тъй като „Джим“ се появява 3 пъти в списъка, той се показва в този масив 3 пъти като числото 1.
Този масив се подава в FREQUENCY като аргумент data_array . Аргументът bins_array се изгражда от тази част на формулата:
ROW(B5:B14)-ROW(B5)+1)
който изгражда последователен списък с числа за всяка стойност в данните:
(1;2;3;4;5;6;7;8;9;10)
В този момент FREQUENCY е конфигуриран по следния начин:
FREQUENCY((1;1;1;4;4;6;6;6;9;9),(1;2;3;4;5;6;7;8;9;10))
FREQUENCY връща масив от числа, които указват брой за всяко число в масива от данни, организиран от bin. Когато числото вече е преброено, FREQUENCY ще върне нула. Това е ключова характеристика в работата на тази формула. Резултатът от FREQUENCY е масив като този:
(3;0;0;2;0;3;0;0;2;0;0) // output from FREQUENCY
Забележка: FREQUENCY винаги връща масив с още един елемент от bins_array .
Вече можем да пренапишем формулата по следния начин:
=SUMPRODUCT(--((3;0;0;2;0;3;0;0;2;0;0)>0))
След това проверяваме за стойности, по-големи от нула (> 0), което преобразува числата в TRUE или FALSE, след което използваме двойно отрицателно (-), за да преобразуваме TRUE и FALSE стойности в 1s и 0s. Сега имаме:
=SUMPRODUCT((1;0;0;1;0;1;0;0;1;0;0))
И накрая, SUMPRODUCT просто събира числата и връща общата сума, която в този случай е 4.
Работа с празни клетки
Празните клетки в диапазона ще накарат формулата да върне грешка # N / A. За да обработвате празни клетки, можете да използвате по-сложна формула на масив, която използва функцията IF за филтриране на празни стойности:
(=SUM(IF(FREQUENCY(IF(data"", MATCH(data,data,0)),ROW(data)-ROW(data.firstcell)+1),1)))
Забележка: Добавянето на IF превръща това във формула на масив, която изисква control-shift-enter.
За повече информация вижте тази страница.
Други начини за преброяване на уникални стойности
Ако имате Excel 365, можете да използвате функцията UNIQUE, за да преброите уникални стойности с много по-проста формула.
Осевата таблица също е отличен начин за преброяване на уникални стойности.
