Обща формула
(=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)))
където "data" е наименованият диапазон B5: B14, а "exclude" е наименуваният диапазон D5: D7.
Забележка: това е формула на масив и трябва да се въведе с control + shift + enter
Предговор
Тази формула се усложнява от изискването „съдържа“. Ако просто се нуждаете от формула, за да преброите клетки, които не са * равни * на много неща, можете да използвате по-ясна формула, базирана на функцията MATCH. Също така, ако имате ограничен брой низове за изключване, можете да използвате функцията COUNTIFS по следния начин:
=COUNTIFS(data,"*pink*",data,"*orange*",data,"*black*")
При този подход обаче трябва да въведете нова двойка аргументи за обхват / критерии за всеки низ, който да изключите. За разлика от това, формулата, обяснена по-долу, може да обработва голям брой низове, за да изключи въведените директно в работния лист.
И накрая, тази формула е сложна. Кажете ми, ако имате по-проста формула, която да предложите :)
Обяснение
Ядрото на тази формула е БРОЙ и ТЪРСЕНЕ:
ISNUMBER(SEARCH(TRANSPOSE(exclude),data))
Тук ние транспонираме елементите от посочения диапазон „изключвам“, след което подаваме резултата към ТЪРСЕНЕ като „текст за намиране“, като „данните“ са „в текста“. Функцията SEARCH връща 2d масив от 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 като 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. Това е пример за логическа логика. Операцията по математика автоматично принуждава TRUE и FALSE стойности към 1s и 0s и накрая имаме масив, който да се върне към функцията SUM:
=SUM((0;0;1;1;0;0;1;1;1;0))
Функцията SUM връща краен резултат от 5.
