✅ Пребройте уникалните стойности в Excel, като използвате функцията COUNTIF

Съдържание

Как да броите уникални стойности в Excel

В този урок ще научите как да броите уникални стойности в Excel с помощта на формули (функции COUNTIF и SUMPRODUCT).

Как да броите уникални стойности в Excel

Да кажем, че имаме набор от данни, както е показано по -долу:

За целите на този урок ще кръстя диапазона A2: A10 като ИМЕНА. Занапред ще използваме този именен диапазон във формулите.

Вижте също: Как да създадете именовани диапазони в Excel.

В този набор от данни има повторение в диапазона NAMES. За да получим броя уникални имена от този набор от данни (A2: A10), можем да използваме комбинация от функции COUNTIF и SUMPRODUCT, както е показано по -долу:

= SUMPRODUCT (1/COUNTIF (ИМЕНА, ИМЕНА))

Как действа тази формула?

Нека разбием тази формула, за да разберем по -добре:

COUNTIF (ИМЕНА, ИМЕНА)
- Тази част от формулата връща масив. В горния пример ще бъде {2; 2; 3; 1; 3; 1; 2; 3; 2}. Числата тук показват колко пъти се среща стойност в дадения диапазон от клетки.
  Например името е Боб, което се среща два пъти в списъка, следователно ще върне числото 2 за Боб. По същия начин Стив се среща три пъти и следователно 3 се връща за Стив.
1/COUNTIF (ИМЕНА, ИМЕНА)
- Тази част от формулата ще върне масив - {0,5; 0,5; 0,333333333333333; 1; 0,333333333333333; 1; 0,5; 0,333333333333333; 0,5}
  Тъй като сме разделили 1 на масива, той връща този масив.
  Например, първият елемент от масива, върнат по -горе, беше 2. Когато 1 се раздели на 2, той връща .5.
СУМПРОДУКТ (1/БРОЙ (ИМЕНА, ИМЕНА))
- SUMPRODUCT просто добавя всички тези числа. Обърнете внимание, че ако Боб се появи два пъти в списъка, горният масив връща .5, където и да се появи името на Боб в списъка. По същия начин, тъй като Стив се появява три пъти в списъка, масивът връща .3333333 всеки път, когато се появи името на Стив. Когато добавяме числата за всяко име, то винаги ще връща 1. И ако добавим всички числа, това ще върне общия брой уникални имена в списъка.

Тази формула работи добре, докато нямате празни клетки в диапазона. Но ако имате празни клетки, тя ще върне #DIV/0! грешка.

Как да боравим с ПРАЗНИ клетки?

Нека първо разберем защо връща грешка, когато има празна клетка в диапазона. Да предположим, че имаме набора от данни, както е показано по -долу (с клетка A3 е празна):

Ако използваме същата формула, която използвахме по -горе, частта COUNTIF от формулата връща масив {2; 0; 3; 1; 3; 1; 2; 3; 1}. Тъй като в клетка A3 няма текст, броят му се връща като 0.

И тъй като делим 1 на целия този масив, той връща #DIV/0! грешка.

За да се справите с тази грешка при разделяне в случай на празни клетки, използвайте формулата по -долу:

= SUMPRODUCT ((1/COUNTIF (ИМЕНА, ИМЕНА & ””)))

Една промяна, която направихме в тази формула, е частта от критериите на функцията COUNTIF. Използвахме NAMES & ”” вместо NAMES. По този начин формулата ще върне броя на празни клетки (по -рано тя върна 0, където имаше празна клетка).

ЗАБЕЛЕЖКА: Тази формула ще брои празни клетки като уникална стойност и ще я върне в резултата.

В горния пример резултатът трябва да бъде 5, но връща 6, тъй като празната клетка се брои като една от уникалните стойности.

Ето формулата, която се грижи за празните клетки и не я брои в крайния резултат:

= SUMPRODUCT ((NAMES ””)/COUNTIF (NAMES, NAMES & ””))

В тази формула вместо 1 като числител сме използвали ИМЕНА ””. Това връща масив от TRUEs и FALSEs. Връща FALSE винаги, когато има празна клетка. Тъй като TRUE е равно на 1, а FALSE е равно на 0 в изчисленията, празни клетки не се броят, тъй като числителят е 0 (FALSE).

Сега, когато имаме готовия основен скелет на формулата, можем да отидем още една крачка напред и да преброим различни типове данни.

Как да броите уникални стойности в Excel, които са текст

Ще използваме същата концепция, обсъдена по -горе, за да създадем формулата, която ще брои само уникални текстови стойности.

Ето формулата, която ще брои уникални текстови стойности в Excel:

= SUMPRODUCT ((ISTEXT (NAMES)/COUNTIF (NAMES, NAMES & ””))))

Всичко, което направихме, е да използваме формулата ISTEXT (NAMES) като числител. Връща TRUE, когато клетката съдържа текст, и FALSE, ако не. Той няма да брои празни клетки, но ще брои клетки, които имат празен низ („”).