Общая формула
(=SUM(--(FREQUENCY(IF(criteria,MATCH(vals,vals,0)),ROW(vals)-ROW(vals.first)+1)>0)))
Резюме
Чтобы подсчитать уникальные текстовые значения в диапазоне с критериями, вы можете использовать формулу массива, основанную на функциях ЧАСТОТА и ПОИСКПОЗ. В показанном примере формула в G6:
(=SUM(--(FREQUENCY(IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0)),ROW(B5:B11)-ROW(B5)+1)>0)))
который возвращает 3, поскольку над проектом Omega работали три разных человека.
Примечание: это формула массива, и ее нужно вводить с помощью Ctrl + Shift + Enter.
Объяснение
Это сложная формула, в которой ЧАСТОТА используется для подсчета числовых значений, полученных с помощью функции ПОИСКПОЗ. Работая изнутри, функция ПОИСКПОЗ используется для получения позиции каждого значения, которое появляется в данных:
MATCH(B5:B11,B5:B11,0)
Результатом MATCH является такой массив:
(1;1;3;1;1;6;7)
Поскольку ПОИСКПОЗ всегда возвращает позицию первого совпадения, значения, которые встречаются в данных более одного раза, возвращают ту же позицию. Например, поскольку «Джим» появляется в списке 4 раза, он появляется в этом массиве 4 раза как число 1.
Вне функции ПОИСКПОЗ функция ЕСЛИ используется для применения критериев, что в данном случае включает проверку того, является ли проект "омега" (из ячейки G5):
IF(C5:C11=G5 // filter on "omega"
Функция ЕСЛИ действует как фильтр, позволяя проходить значениям из ПОИСКПОЗ, только если они связаны с «омегой». Результатом является такой массив:
(FALSE;FALSE;FALSE;1;1;6;7) // after filtering
Отфильтрованный массив доставляется непосредственно в функцию FREQUENCY как аргумент data_array . Затем функция СТРОКА используется для построения последовательного списка чисел для каждого значения в данных:
ROW(B3:B12)-ROW(B3)+1
Это создает такой массив:
(1;2;3;4;5;6;7;8;9;10)
который становится аргументом bins_array в FILTER. На данный момент у нас есть:
FREQUENCY((FALSE;FALSE;FALSE;1;1;6;7),(1;2;3;4;5;6;7))
FREQUENCY возвращает массив чисел, которые указывают количество для каждого значения в массиве данных, организованных по ячейкам. Когда число уже подсчитано, FREQUENCY вернет ноль. Результатом FREQUENCY является такой массив:
(2;0;0;0;0;1;1;0) // result from FREQUENCY
Примечание. FREQUENCY всегда возвращает массив, в котором на один элемент больше, чем в bins_array .
На этом этапе мы можем переписать формулу следующим образом:
=SUM(--((2;0;0;0;0;1;1;0)>0))
Мы проверяем значения больше нуля, что преобразует числа в ИСТИНА или ЛОЖЬ:
=SUM(--((TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE)))
Затем мы используем двойное отрицание для приведения логических значений к 1 и 0:
=SUM((1;0;0;0;0;1;1;0))
Наконец, функция СУММ возвращает 3 в качестве окончательного результата.
Примечание: это формула массива, и ее необходимо вводить с помощью Control + Shift + Enter.
Обработка пустых ячеек в диапазоне
Если какие-либо ячейки в диапазоне пусты, вам необходимо настроить формулу, чтобы предотвратить передачу пустых ячеек в функцию ПОИСКПОЗ, что приведет к ошибке. Вы можете сделать это, добавив еще одну вложенную функцию ЕСЛИ для проверки пустых ячеек:
(=SUM(--(FREQUENCY(IF(B5:B11"",IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0))),ROW(B5:B11)-ROW(B5)+1)>0)))
С двумя критериями
Если у вас есть два критерия, вы можете расширить логику формулы, добавив еще один вложенный IF:
(=SUM(--(FREQUENCY(IF(c1,IF(c2,MATCH(vals,vals,0))),ROW(vals)-ROW(vals.1st)+1)>0)))
Где c1 = критерий1, c2 = критерий2 и vals = диапазон значений.
С логической логикой
С помощью логической логики вы можете уменьшить количество вложенных IF:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),MATCH(vals,vals,0)),ROW(vals)-ROW(vals.1st)+1)>0)))
Это упрощает добавление дополнительных критериев и управление ими.
