В Excel нет функции для подсчета уникальных записей в диапазоне, тем не менее, такая операция относительно часто встречается в составе более комплексных заданий.
Смотрите также видеоверсию статьи «Простая формула для подсчета уникальных значений».
Если вы столкнулись с проблемой подсчета уникальных записей, это сделать достаточно просто, с помощью такой вот формулы:
=СУММПРОИЗВ(1/СЧЁТЕСЛИ(данные;данные))
В английской версии Excel:
=SUMPRODUCT(1/COUNTIF(data;data))
Например, необходимо подсчитать уникальные страны (по их обозначению) в следующей таблице.
Как можно заметить, формула подсчета уникальных записей стран следующая:
=СУММПРОИЗВ(1/СЧЁТЕСЛИ(B2:B77;B2:B77))
На этом элегантная формула закончена, если интересно узнать как она работает читаем дальше.
Как формула работает
Для того, чтобы описать работу формулы, следует разобрать работу ее составляющих.
=СЧЁТЕСЛИ(B2:B77;B2:B77)
Формула выводит сколько раз в диапазоне встречается та или иная запись, результат работы формулы можно наблюдать, если ввести ее как формулу массива.
Так, страна с обозначением “CAN” (2я запись) встречается 3 раза, а FRA – всего один раз. Итак, идем дальше, следующим шагом будет деление “1” на каждое значение из диапазона.
Этим шагом мы получим долю неуникальных записей в единице, например, если какая-то запись встречается 2 раза, то доля каждой отдельной записи будет равняться 0,5.
И, наконец, суммируются все записи с помощью функции СУММПРОИЗВ. Здесь используется именно СУММПРОИЗВ с одним параметром, которая выполняет роль функции СУММ, можно использовать и СУММ, но тогда нужно вводить формулу как формулу массива, т.е. через Ctrl+Shift+Enter.
Формула очень элегантна, однако, при больших объемах входных данных могут возникнуть проблемы с производительностью, из-за того, что используется не самая быстрая функция СЧЁТЕСЛИ.
Поэтому, если у вас наблюдаются проблемы с производительностью, рекомендуем обратить внимание на формулы подсчета уникальных числовых значений и текстовых значений. Материалы по данным формулам появятся на сайте совсем скоро, не забывайте подписываться на обновления.