Общая формула
=SUM(--(LEN(UNIQUE(FILTER(range,criteria,"")))>0))
Резюме
Чтобы подсчитать уникальные значения с одним или несколькими условиями, вы можете использовать формулу, основанную на УНИКАЛЬНОСТИ и ФИЛЬТРЕ. В показанном примере формула H7 имеет следующий вид:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,C6:C15=H6,"")))>0))
который возвращает 3, поскольку в B6 есть три уникальных имени: B15, связанных с проектом Omega.
Примечание: для этой формулы требуются формулы динамического массива, доступные только в Excel 365. В более старой версии Excel вы можете использовать более сложные альтернативные формулы.
Объяснение
По сути, эта формула использует функцию UNIQUE для извлечения уникальных значений, а функция FILTER применяет критерии.
Работая изнутри, функция ФИЛЬТР используется для применения критериев и извлечения только тех имен, которые связаны с проектом «Омега»:
FILTER(B6:B15,C6:C15=H6) // Omega names only
Результатом FILTER является такой массив:
("Jim";"Jim";"Carl";"Sue";"Carl")
Далее функция UNIQUE используется для удаления дубликатов:
UNIQUE(("Jim";"Jim";"Carl";"Sue";"Carl"))
что приводит к новому массиву, подобному этому:
("Jim";"Carl";"Sue") // after UNIQUE
На данный момент у нас есть уникальный список имен, связанных с Omega, и нам просто нужно их посчитать. По причинам, объясненным ниже, мы делаем это с помощью функций LEN и SUM. Чтобы прояснить ситуацию, мы сначала перепишем формулу, включив в нее уникальный список:
=SUM(--(LEN(("Jim";"Carl";"Sue"))>0))
Функция LEN получает длину каждого элемента в списке и возвращает массив длин:
LEN(("Jim";"Carl";"Sue")) // returns (3;4;3)
Затем мы проверяем, больше ли длины нуля:
LEN((3;4;3)>0 // returns (TRUE;TRUE;TRUE)
И используйте двойное отрицательное значение, чтобы привести значения ИСТИНА и ЛОЖЬ к 1 и 0:
--((TRUE;TRUE;TRUE)) // returns (1;1;1)
Наконец, мы складываем результаты с помощью функции СУММ:
=SUM((1;1;1)) // returns 3
Этот массив доставляется непосредственно в функцию COUNTA, которая возвращает окончательный счет:
=COUNTA(("Jim";"Carl";"Sue")) // returns 3
Обратите внимание: поскольку мы проверяем длину каждого элемента, возвращаемого UNIQUE, пустые или пустые ячейки, соответствующие критериям, игнорируются. Эта формула является динамической и будет немедленно пересчитана при изменении исходных данных.
Считайте уникальными по нескольким критериям
Для подсчета уникальных значений на основе нескольких критериев можно расширить логику «включения» внутри ФИЛЬТРА. Например, чтобы подсчитать уникальные имена для проекта Omega только в июне, используйте:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,(C6:C15=H6)*(D6:D15="june"))))>0))
Это пример использования логической логики для применения более чем одного условия. Подход более подробно объясняется здесь.
Для получения дополнительной информации см. Это обучающее видео: Как фильтровать по нескольким критериям.
COUNTA
Можно написать более простую формулу, которая отвечает на функцию COUNTA. Однако важное предостережение заключается в том, что COUNTA вернет 1, если нет совпадающих значений. Это связано с тем, что функция ФИЛЬТР возвращает ошибку, когда данные не соответствуют критериям, и эта ошибка в конечном итоге подсчитывается функцией СЧЁТ. Основная формула COUNTA выглядит так:
=COUNTA(UNIQUE(FILTER(B6:B15,C6:C15=H6)))
Опять же, эта формула вернет 1, если нет совпадающих данных. Он также будет включать пустые ячейки, соответствующие критериям. Формула, основанная на LEN и SUM, - лучший вариант.
Нет динамических массивов
Если вы используете старую версию Excel без поддержки динамических массивов, вы можете использовать более сложную формулу. Для более общего обсуждения альтернатив динамических массивов см .: Альтернативы формулам динамических массивов.
