Общая формула
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
Резюме
Для подсчета ячеек, не равных многим параметрам, можно использовать формулу, основанную на функциях ПОИСКПОЗ, ISNA и СУММПРОИЗВ. В показанном примере формула в ячейке F5:
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
где «данные» - именованный диапазон B5: B16, а «exclude» - именованный диапазон D5: D7.
Объяснение
Во-первых, немного контекста. Обычно, если у вас есть всего пара вещей, которые вы не хотите считать, вы можете использовать СЧЁТЕСЛИМН, как это:
=COUNTIFS(range,"apple",range,"orange")
Но это не очень хорошо масштабируется, если у вас есть список из многих вещей, потому что вам придется добавить дополнительную пару диапазон / критерий для каждой вещи, которую вы не хотите считать. Было бы намного проще создать список и передать ссылку на этот список как часть критериев. Именно это и делает формула на этой странице.
По сути, эта формула использует функцию ПОИСКПОЗ для поиска ячеек, не равных «a», «b» или «c», с помощью следующего выражения:
MATCH(data,exclude,0)
Обратите внимание, что поисковое значение и поисковый массив «перевернуты» по сравнению с нормальной конфигурацией - мы предоставляем все значения из именованного диапазона «data» в качестве значений поиска и даем все значения, которые мы хотим исключить, в именованном диапазоне «exclude». Поскольку мы передаем MATCH несколько значений поиска, мы получаем более одного результата в таком массиве:
(1;2;3;#N/A;#N/A;#N/A;1;2;3;#N/A;1)
По сути, ПОИСКПОЗ дает нам позицию совпадающих значений в виде числа и возвращает # N / A для всех остальных значений.
Нас интересуют результаты # N / A, поскольку они представляют значения, не равные «a», «b» или «c». Соответственно, мы используем ISNA, чтобы заставить эти значения иметь значение ИСТИНА, а числа - ЛОЖЬ:
ISNA(MATCH(data,exclude,0)
Затем мы используем двойное отрицание, чтобы привести ИСТИНА к 1 и ЛОЖЬ к нулю. Результирующий массив внутри SUMPRODUCT выглядит так:
=SUMPRODUCT((0;0;0;1;1;1;0;0;0;1;0))
Имея только один массив для обработки, SUMPRODUCT суммирует и возвращает окончательный результат 4.
Примечание. Использование СУММПРОИЗВ вместо СУММ позволяет избежать использования клавиш Ctrl + Shift + Enter.
Считать минус совпадение
Другой способ подсчета ячеек, не равных какому-либо из нескольких значений, - это подсчет всех значений и вычитание совпадений. Вы можете сделать это с помощью такой формулы:
=COUNTA(range)-SUMPRODUCT(COUNTIF(range,exclude))
Здесь COUNTA возвращает количество всех непустых ячеек. Функция СЧЁТЕСЛИ, учитывая именованный диапазон «exclude», вернет три числа, по одному для каждого элемента в списке. СУММПРОИЗВ суммирует итог, и это число вычитается из количества всех непустых ячеек. Конечный результат - количество ячеек, которые не равны значениям в «исключить».
Литерал содержит логику типов
Формула на этой странице рассчитывается по логике «равно». Если вам нужно подсчитать ячейки, которые не содержат много строк, где содержит означает, что строка может появиться в любом месте ячейки, вам понадобится более сложная формула.
