Обща формула
=SUMPRODUCT(--((ISNUMBER(FIND("abc",rng)) + ISNUMBER(FIND("def",rng)))>0))
Обобщение
За да преброите клетки, които съдържат x или y, можете да използвате формула, базирана на функцията SUMPRODUCT. В показания пример формулата в клетка F5 е:
=SUMPRODUCT(--((ISNUMBER(FIND("abc",B5:B11))+ISNUMBER(FIND("def",B5:B11)))>0))
Това е разтворът с формула за единична клетка, обяснен по-долу. Също така е възможно да се използва по-проста формула, базирана на помощна колона, също обяснена по-долу.
Обяснение
Когато броите клетки с "ИЛИ логика", трябва да внимавате да не броите двойно. Например, ако броите клетки, които съдържат "abc" или "def", не можете просто да съберете две функции COUNTIF, защото можете да удвоите броя на клетките, които съдържат както "abc", така и "def".
Едноклетъчен разтвор
За една формула можете да използвате SUMPRODUCT с ISNUMBER + FIND. Формулата във F5 е:
=SUMPRODUCT(--((ISNUMBER(FIND("abc",B5:B11)) + ISNUMBER(FIND("def",B5:B11)))>0))
Тази формула се основава на формулата, обяснена тук, която локализира текста вътре в клетка:
ISNUMBER(FIND("abc",B5:B11)
Когато му бъде даден диапазон от клетки, този фрагмент ще върне масив от TRUE / FALSE стойности, по една стойност за всяка клетка в диапазона. Тъй като използваме това два пъти (веднъж за "abc" и веднъж за "def"), ще получим два масива.
След това добавяме тези масиви заедно (с +), което създава нов единичен масив от числа. Всяко число в този масив е резултат от добавяне на стойностите TRUE и FALSE в оригиналните два масива заедно. В показания пример полученият масив изглежда така:
(2;0;2;0;1;0;2)
Трябва да съберем тези числа, но не искаме да броим двойно. Затова трябва да се уверим, че всяка стойност, по-голяма от нула, се брои само веднъж. За целта принуждаваме всички стойности на TRUE или FALSE с "> 0", след това на 1/0 с двойно отрицателно (-).
И накрая, SUMPRODUCT връща сумата от всички стойности в масива.
Разтвор за помощна колона
С помощна колона за проверка на всяка клетка поотделно проблемът е по-малко сложен. Можем да използваме COUNTIF с две стойности (предоставени като „константа на масива“). Формулата в C5 е:
=--(SUM(COUNTIF(B5,("*abc*","*def*")))>0)
COUNTIF ще върне масив, който съдържа два елемента: брой за "abc" и брой за "def". За да предотвратим двойно броене, добавяме елементите и след това форсираме резултата на TRUE / FALSE с "> 0". И накрая, преобразуваме стойностите TRUE / FALSE в 1 и 0 с двойно отрицателно (-).
Крайният резултат е 1 или 0 за всяка клетка. За да получите обща сума за всички клетки в диапазона, просто сумирайте помощната колона.
