В материале элегантной и простой формулы для подсчета уникальных значений диапазона мы указали, что единственный недостаток – это медленная работа при больших объемах, связанная с использованием формулы массива.
В этом материале построим формулу для подсчета уникальных числовых записей диапазона. Она работает быстрее, но только с числовыми данными. Формула подсчета уникальных числовых значений построена на основе функции ЧАСТОТА.
Смотрите также видеоверсию статьи «Подсчет уникальных числовых значений».
Итак, сама формула:
=СУММ(--(ЧАСТОТА(данные;данные)>0))
В английской версии:
=SUM(--(FREQUENCY(data,data)>0))
Например, смотрим на рисунок ниже, где необходимо подсчитать спортсменов одинакового возраста (колонка Age).
Как можно заметить, формула для подсчета уникальных числовых значений следующая:
=СУММ(--(ЧАСТОТА(C2:C77;C2:C77)>0))
В принципе, уже все, если интересно как эта формула работает читаем дальше.
Как работает формула подсчета уникальных числовых значений
Итак, разберем формулу по частям, во-первых, идет подсчет частоты, с которой встречаются одинаковые записи в диапазоне.
=ЧАСТОТА(C2:C77;C2:C77)
Данную формулу следует вводить как формулу массива (сочетание Ctrl+Shift+Enter), чтобы рассчитать частоту для каждого элемента.
Особенность работы функции ЧАСТОТА заключается в том, что для неуникального числа, которое встречается первый раз записывается количество его повторений (например 2, 3 и т.д.), а, если неуникальное число встречается повторно, то для него записывается нуль. Таким образом, мы получаем численное обозначение для записей, которое являются уникальными и нули для неуникальных записей.
Следующим шагом идет преобразование результата в булево значение (истина либо ложь), для этого каждое число из диапазона проверяется больше оно нуля или нет.
=ЧАСТОТА(C2:C77;C2:C77)>0
Теперь осталось, только просуммировать количество записей “ИСТИНА”, а для этого их нужно перевести в числовые значения, это делается с помощью двойного отрицания. Можно использовать и отрицательно, но тогда конечное число получится с минусом, а так минус на минус дают плюс.
= --(ЧАСТОТА(C2:C77;C2:C77)>0)
На последнем шаге суммируются единицы, фактически это и есть количество уникальных чисел, просуммировать записи можно с помощью функции СУММ, а можно и СУММПРОИЗВ, т.к. используется один диапазон она сработает как сумма.
=СУММ(--(ЧАСТОТА(данные;данные)>0))
=СУММПРОИЗВ(--(ЧАСТОТА(данные;данные)>0))
Данная формула несколько сложнее, нежели рассмотренная ранее универсальная формула на основе функции СЧЁТЕСЛИ, но она работает быстрее.
В следующем материале рассмотрим подсчет уникальных текстовых записей.
