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

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

В 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.

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

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

Встречаем новый Excel, Microsoft представила динамические массивы (Dynamic Array Functions)

24 сентября 2018 года Microsoft представила одно из самых существенных изменений в Excel за последнее десятилетие - Dynamic Array...

Горизонтальная сортировка в Excel

В большинстве случаев работы с данными они организованны вертикально, поэтому когда подразумевается обработка имеется ввиду именно вертикальная обработка данных,...

Автонумерация внутри составной записи в Excel

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

Еще записей в тему?
Если честно, некоторые могут быть не свежие:)