Простая формула для подсчета уникальных значений

В Excel нет функции для подсчета уникальных записей в диапазоне, тем не менее, такая операция относительно часто встречается в составе более комплексных заданий.

Если вы столкнулись с проблемой подсчета уникальных записей, это сделать достаточно просто, с помощью такой вот формулы:

=СУММПРОИЗВ(1/СЧЁТЕСЛИ(данные;данные))


В английской версии Excel:

=SUMPRODUCT(1/COUNTIF(data;data))

Например, необходимо подсчитать уникальные страны (по их обозначению) в следующей таблице.

image

Как можно заметить, формула подсчета уникальных записей стран следующая:

=СУММПРОИЗВ(1/СЧЁТЕСЛИ(B2:B77;B2:B77))

На этом элегантная формула закончена, если интересно узнать как она работает читаем дальше.

Как формула работает

Для того, чтобы описать работу формулы, следует разобрать работу ее составляющих.

=СЧЁТЕСЛИ(B2:B77;B2:B77)

Формула выводит сколько раз в диапазоне встречается та или иная запись, результат работы формулы можно наблюдать, если ввести ее как формулу массива.

image

Так, страна с обозначением “CAN” (2я запись) встречается 3 раза, а FRA – всего один раз. Итак, идем дальше, следующим шагом будет деление “1” на каждое значение из диапазона.

image

Этим шагом мы получим долю неуникальных записей в единице, например, если какая-то запись встречается 2 раза, то доля каждой отдельной записи будет равняться 0,5.

И, наконец, суммируются все записи с помощью функции СУММПРОИЗВ. Здесь используется именно СУММПРОИЗВ с одним параметром, которая выполняет роль функции СУММ, можно использовать и СУММ, но тогда нужно вводить формулу как формулу массива, т.е. через Ctrl+Shift+Enter.

Формула очень элегантна, однако, при больших объемах входных данных могут возникнуть проблемы с производительностью, из-за того, что используется не самая быстрая функция СЧЁТЕСЛИ.

Поэтому, если у вас наблюдаются проблемы с производительностью, рекомендуем обратить внимание на формулы подсчета уникальных числовых значений и текстовых значений. Материалы по данным формулам появятся на сайте совсем скоро, не забывайте подписываться на обновления.