Общая формула
(=SUM(--(FREQUENCY(IF(criteria,values),values)>0)))
Резюме
Чтобы подсчитать уникальные числовые значения в диапазоне, вы можете использовать формулу, основанную на функциях ЧАСТОТА, СУММ и ЕСЛИ. В показанном примере номера сотрудников находятся в диапазоне B5: B14. Формула в G6:
=SUM(--(FREQUENCY(IF(C5:C14="A",B5:B14),B5:B14)>0))
который возвращает 2, поскольку в здании A есть 2 уникальных идентификатора сотрудников.
Примечание: это формула массива, и ее нужно вводить с помощью Ctrl + Shift + Enter, если вы не используете Excel 365.
Объяснение
Примечание. До Excel 365 в Excel не было специальной функции для подсчета уникальных значений. Эта формула показывает один способ подсчета уникальных значений, если они являются числовыми. Если у вас есть текстовые значения или сочетание текста и чисел, вам понадобится более сложная формула.
Функция ЧАСТОТА в Excel возвращает частотное распределение, которое представляет собой сводную таблицу, содержащую частоту числовых значений, организованных в «ячейки». Мы используем его здесь как обходной способ подсчета уникальных числовых значений. Чтобы применить критерии, мы используем функцию ЕСЛИ.
Работая изнутри, мы сначала фильтруем значения с помощью функции ЕСЛИ:
IF(C5:C14="A",B5:B14) // filter on building A
Результатом этой операции является такой массив:
(905;905;905;905;773;773;FALSE;FALSE;FALSE;FALSE)
Обратите внимание, что все идентификаторы в здании B теперь FALSE. Этот массив доставляется непосредственно в функцию FREQUENCY как data_array . Для bins_array мы предоставляем сами идентификаторы:
FREQUENCY((905;905;905;905;773;773;FALSE;FALSE;FALSE;FALSE),(905;905;905;905;773;773;801;963;963;963))
В этой конфигурации FREQUENCY возвращает следующий массив:
(4;0;0;0;2;0;0;0;0;0;0)
Результат немного загадочный, но значение 905 появляется четыре раза, а 773 появляется два раза. Значения FALSE автоматически игнорируются.
FREQUENCY имеет специальную функцию, которая автоматически возвращает ноль для любых чисел, которые уже появились в массиве данных, поэтому значения равны нулю после того, как число обнаружено. Это функция, которая позволяет этому подходу работать.
Затем проверяется, что каждое из этих значений больше нуля:
(4;0;0;0;2;0;0;0;0;0;0)>0
Результатом является такой массив:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Каждое ИСТИНА в списке представляет собой уникальный номер в списке, и нам просто нужно сложить ИСТИННЫЕ значения с помощью СУММ. Однако SUM не будет складывать логические значения в массив, поэтому нам нужно сначала привести значения к 1 или нулю. Это делается с двойным отрицательным знаком (-). Результат - массив только из единиц или нулей:
(1;0;0;0;1;0;0;0;0;0;0)
Наконец, СУММ складывает эти значения и возвращает итог, который в данном случае равен 2.
Несколько критериев
Вы можете расширить формулу для обработки нескольких критериев, например:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),values),values)>0)))
