Общая формула
=SUM(--(FREQUENCY(data,data)>0))
Резюме
Чтобы подсчитать уникальные числовые значения в диапазоне, вы можете использовать формулу, основанную на функциях ЧАСТОТА и СУММ. В показанном примере номера сотрудников находятся в диапазоне B5: B14. Формула в F5:
=SUM(--(FREQUENCY(B5:B14,B5:B14)>0))
который возвращает 4, поскольку в списке 4 уникальных идентификатора сотрудников.
Объяснение
Примечание. До Excel 365 в Excel не было специальной функции для подсчета уникальных значений. Эта формула показывает один способ подсчета уникальных значений, если они являются числовыми. Если у вас есть текстовые значения или сочетание текста и чисел, вам понадобится более сложная формула.
Функция ЧАСТОТА Excel возвращает частотное распределение, которое представляет собой сводную таблицу, которая показывает частоту числовых значений, организованных в «ячейки». Мы используем его здесь как обходной способ подсчета уникальных числовых значений.
Работая наизнанку, мы предоставляем FREQUENCY один и тот же набор чисел как для массива данных, так и для массива бинов:
FREQUENCY(B5:B14,B5:B14)
FREQUENCY возвращает массив со счетчиком каждого числового значения в диапазоне:
(4;0;0;0;2;0;1;3;0;0;0)
Результат немного загадочен, но значение 905 появляется четыре раза, 773 появляется два раза, 801 появляется один раз и 963 появляется три раза.
FREQUENCY имеет специальную функцию, которая автоматически возвращает ноль для любых чисел, которые уже появились в массиве данных, поэтому значения равны нулю после того, как число обнаружено.
Затем проверяется, что каждое из этих значений больше нуля:
(4;0;0;0;2;0;1;3;0;0;0)>0
Результатом является такой массив:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE)
Каждое ИСТИНА представляет собой уникальный номер в списке. По умолчанию SUM игнорирует логические значения, поэтому мы приводим значения TRUE и FALSE к 1 и 0 с двойным отрицательным знаком (-), что дает:
=SUM((1;0;0;0;1;0;1;1;0;0;0))
Наконец, СУММ складывает эти значения и возвращает итог, который в данном случае равен 4.
Примечание: вы также можете использовать SUMPRODUCT для суммирования элементов в массиве.
Использование COUNTIF вместо FREQUENCY для подсчета уникальных значений
Другой способ подсчета уникальных числовых значений - использовать COUNTIF вместо FREQUENCY. Это более простая формула, но имейте в виду, что использование COUNTIF на больших наборах данных для подсчета уникальных значений может вызвать проблемы с производительностью. Формула ЧАСТОТА, хотя и более сложная, вычисляется намного быстрее.
