Подсчет уникальных числовых значений

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

В этом материале построим формулу для подсчета уникальных числовых записей диапазона. Она работает быстрее, но только с числовыми данными. Формула подсчета уникальных числовых значений построена на основе функции ЧАСТОТА.

Итак, сама формула:

=СУММ(--(ЧАСТОТА(данные;данные)>0))

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

=SUM(--(FREQUENCY(data,data)>0))

Например, смотрим на рисунок ниже, где необходимо подсчитать спортсменов одинакового возраста (колонка Age).

image

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

=СУММ(--(ЧАСТОТА(C2:C77;C2:C77)>0))

В принципе, уже все, если интересно как эта формула работает читаем дальше.

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

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

=ЧАСТОТА(C2:C77;C2:C77)

image

Данную формулу следует вводить как формулу массива (сочетание Ctrl+Shift+Enter), чтобы рассчитать частоту для каждого элемента.

Особенность работы функции ЧАСТОТА заключается в том, что для неуникального числа, которое встречается первый раз записывается количество его повторений (например 2, 3 и т.д.), а, если неуникальное число встречается повторно, то для него записывается нуль. Таким образом, мы получаем численное обозначение для записей, которое являются уникальными и нули для неуникальных записей.

Следующим шагом идет преобразование результата в булево значение (истина либо ложь), для этого каждое число из диапазона проверяется больше оно нуля или нет.

=ЧАСТОТА(C2:C77;C2:C77)>0

image

Теперь осталось, только просуммировать количество записей “ИСТИНА”, а для этого их нужно перевести в числовые значения, это делается с помощью двойного отрицания. Можно использовать и отрицательно, но тогда конечное число получится с минусом, а так минус на минус дают плюс.

= --(ЧАСТОТА(C2:C77;C2:C77)>0)

image

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

=СУММ(--(ЧАСТОТА(данные;данные)>0))

=СУММПРОИЗВ(--(ЧАСТОТА(данные;данные)>0))

Данная формула несколько сложнее, нежели рассмотренная ранее универсальная формула на основе функции СЧЁТЕСЛИ, но она работает быстрее.

В следующем материале рассмотрим подсчет уникальных текстовых записей.