Общая формула
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
Резюме
Для подсчета ячеек, которые не содержат много разных строк, вы можете использовать довольно сложную формулу, основанную на функции MMULT. В показанном примере формула в F5:
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
где «данные» - это именованный диапазон B5: B14, а «exclude» - именованный диапазон D5: D7.
Примечание: это формула массива, и ее нужно вводить с помощью Ctrl + Shift + Enter.
Предисловие
Эта формула усложняется требованием "содержит". Если вам просто нужна формула для подсчета ячеек, которые не * равны * многим значениям, вы можете использовать более простую формулу, основанную на функции ПОИСКПОЗ. Кроме того, если у вас есть ограниченное количество строк для исключения, вы можете использовать функцию СЧЁТЕСЛИМН, как это:
=COUNTIFS(data,"*pink*",data,"*orange*",data,"*black*")
Однако при таком подходе вы должны ввести новую пару аргументов диапазона / критерия для каждой исключаемой строки. Напротив, формула, описанная ниже, может обрабатывать большое количество строк, чтобы исключить их, введенные непосредственно на листе.
Наконец, это сложная формула. Дайте мне знать, если у вас есть возможность предложить более простую формулу :)
Объяснение
Суть этой формулы - ЕЧИСЛО и ПОИСК:
ISNUMBER(SEARCH(TRANSPOSE(exclude),data))
Здесь мы транспонируем элементы в именованном диапазоне «exclude», затем передаем результат в SEARCH как «найти текст», а «data» как «в тексте». Функция ПОИСК возвращает двумерный массив значений ИСТИНА и ЛОЖЬ, 10 строк по 3 столбца, например:
(3,#VALUE!,12;#VALUE!,4,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,3;14,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;3,#VALUE!,12)
Для каждого значения в «данных» у нас есть 3 результата (по одному на строку поиска), которые являются либо ошибками #VALUE, либо числами. Числа представляют положение найденной текстовой строки, а ошибки представляют собой текстовые строки, которые не найдены. Между прочим, функция TRANSPOSE нужна для генерации массива полных результатов 10 x 3.
Этот массив передается в ISNUMBER для получения значений TRUE FALSE, которые мы преобразуем в единицы и нули с помощью оператора двойного отрицательного (-). Результатом является такой массив:
(1,0,1;0,1,0;0,0,0;0,0,0;0,0,1;1,0,0;0,0,0;0,0,0;0,0,0;1,0,1)
который входит в функцию MMULT как array1. Согласно правилам умножения матриц, количество столбцов в array1 должно равняться количеству строк в array2. Чтобы сгенерировать array2 , мы используем функцию ROW следующим образом:
ROW(exclude)^0
Это дает массив из единиц, 3 строки на 1 столбец:
(1;1;1)
который входит в MMULT как array2 . После умножения массива у нас есть массив, размер которого соответствует исходным данным:
(2;1;0;0;1;1;0;0;0;2)
В этом массиве любое ненулевое число представляет значение, в котором была найдена хотя бы одна из исключенных строк. Нули указывают, что исключенных строк не найдено. Чтобы привести все ненулевые значения к 1, мы используем больше нуля:
(2;1;0;0;1;1;0;0;0;2)>0
который создает еще один массив или значения TRUE и FALSE:
(TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)
Наша конечная цель - подсчитать только текстовые значения, в которых не было обнаружено исключенных строк, поэтому нам нужно отменить эти значения. Мы делаем это, вычитая массив из 1. Это пример логической логики. Математическая операция автоматически переводит значения ИСТИНА и ЛОЖЬ в единицы и нули, и, наконец, у нас есть массив для возврата к функции СУММ:
=SUM((0;0;1;1;0;0;1;1;1;0))
Функция СУММ возвращает окончательный результат 5.
