Загальна формула
(=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, а "виключити" - іменований діапазон D5: D7.
Примітка: це формула масиву, і її потрібно вводити за допомогою control + shift + enter
Передмова
Ця формула ускладнюється вимогою "містить". Якщо вам просто потрібна формула для підрахунку клітинок, які * не дорівнюють * багатьом речам, ви можете використовувати більш просту формулу на основі функції MATCH. Крім того, якщо у вас є обмежена кількість рядків для виключення, ви можете використовувати функцію COUNTIFS наступним чином:
=COUNTIFS(data,"*pink*",data,"*orange*",data,"*black*")
Однак при такому підході потрібно ввести нову пару аргументів діапазону / критерію для кожного рядка для виключення. На відміну від цього, пояснена нижче формула може обробляти велику кількість рядків, щоб виключити введені безпосередньо на робочому аркуші.
Нарешті, ця формула є складною. Повідомте мене, якщо у вас є простіша формула, щоб запропонувати :)
Пояснення
Основою цієї формули є ЧИСЛО та ПОШУК:
ISNUMBER(SEARCH(TRANSPOSE(exclude),data))
Тут ми транспонуємо елементи з названого діапазону "виключити", а потім передаємо результат на ПОШУК як "знайти текст", а "дані" - як "всередині тексту". Функція SEARCH повертає двовимірний масив значень TRUE і FALSE, 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 значення, які ми перетворюємо в 1s і 0s за допомогою подвійного негативного (-) оператора. Результат - такий масив:
(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. Дотримуючись правил множення матриць, кількість стовпців у масиві1 має дорівнювати кількості рядків у масиві2. Щоб сформувати масив2 , ми використовуємо функцію ROW наступним чином:
ROW(exclude)^0
Це дає масив 1s, 3 рядки на 1 стовпець:
(1;1;1)
який переходить у MMULT як масив2 . Після множення масиву ми маємо масив, розмірений відповідно до вихідних даних:
(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. Це приклад логічної логіки. Операція математики автоматично примушує значення TRUE і FALSE до 1s і 0s, і нарешті ми маємо масив для повернення до функції SUM:
=SUM((0;0;1;1;0;0;1;1;1;0))
Функція SUM повертає кінцевий результат 5.
