Обща формула
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
Обобщение
За да преброите клетки, които не са равни на което и да е от много неща, можете да използвате формула, базирана на функциите MATCH, ISNA и SUMPRODUCT. В показания пример формулата в клетка F5 е:
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
където "data" е наименованият диапазон B5: B16, а "exclude" е наименуваният диапазон D5: D7.
Обяснение
Първо, малко контекст. Обикновено, ако имате само няколко неща, които не искате да броите, можете да използвате COUNTIFS по този начин:
=COUNTIFS(range,"apple",range,"orange")
Но това не се мащабира много добре, ако имате списък с много неща, защото ще трябва да добавите допълнителна двойка обхват / критерии за всяко нещо, което не искате да броите. Би било много по-лесно да се изгради списък и да се предаде препратка към този списък като част от критериите. Точно това прави формулата на тази страница.
В основата си тази формула използва функцията MATCH за намиране на клетки, които не са равни на "a", "b" или "c" с този израз:
MATCH(data,exclude,0)
Обърнете внимание, че справочната стойност и справочният масив са „обърнати“ от нормалната конфигурация - ние предоставяме всички стойности от посочения диапазон „данни“ като справочни стойности и даваме всички стойности, които искаме да изключим в посочения диапазон „изключвам“. Тъй като даваме на MATCH повече от една справочна стойност, получаваме повече от един резултат в масив като този:
(1;2;3;#N/A;#N/A;#N/A;1;2;3;#N/A;1)
По същество MATCH ни дава позицията на съвпадащи стойности като число и връща # N / A за всички останали стойности.
Резултатите # N / A са тези, които ни интересуват, тъй като те представляват стойности, които не са равни на "a", "b" или "c". Съответно, ние използваме ISNA, за да принудим тези стойности да са TRUE и числата да са FALSE:
ISNA(MATCH(data,exclude,0)
След това използваме двойно отрицателно, за да принудим TRUE до 1 и FALSE до нула. Полученият масив вътре в SUMPRODUCT изглежда така:
=SUMPRODUCT((0;0;0;1;1;1;0;0;0;1;0))
Само с един масив за обработка, SUMPRODUCT сумира и връща краен резултат, 4.
Забележка: Използването на SUMPRODUCT вместо SUM избягва необходимостта от използване на control + shift + enter.
Брой минус съвпадение
Друг начин да броим клетки, които не са равни на някое от няколко неща, е да броим всички стойности и да изваждаме съвпадения. Можете да направите това с формула като тази:
=COUNTA(range)-SUMPRODUCT(COUNTIF(range,exclude))
Тук COUNTA връща броя на всички непразни клетки. Функцията COUNTIF, като се посочи диапазонът "изключване", ще върне три броя, по един за всеки елемент от списъка. SUMPRODUCT сумира сумата и това число се изважда от броя на всички непразни клетки. Крайният резултат е броят на клетките, които не са равни на стойностите в „изключване“.
Буквал съдържа логика на типа
Формулата на тази страница се брои с логика "равно на". Ако трябва да преброите клетки, които не съдържат много низове, където съдържа означава, че низ може да се появи навсякъде в клетка, ще ви трябва по-сложна формула.
