Обща формула
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))=COUNTA(things)
Обобщение
Ако искате да тествате клетка, за да видите дали тя съдържа всички елементи в списък, можете да го направите с формула, която използва функцията SEARCH, с помощта на функциите ISNUMBER, SUMPRODUCT и COUNTA.
Контекст
Да приемем, че имате списък с текстови низове в диапазона B5: B8 и искате да разберете дали тези клетки съдържат всички думи в друг диапазон, E5: E7.
Можете да създадете формула, която използва вложени IF изрази, за да проверява за всеки елемент, но това няма да се мащабира добре, ако имате много неща, които да търсите. Всеки път, когато добавяте дума, която да търсите, ще трябва да добавите още една вложена IF и да коригирате скоби.
Решение
Решението е да се създаде формула, която отчита всички съвпадения наведнъж. След като имаме това, ние просто сравняваме този брой с броя на елементите, които търсим. Ако те съвпадат, знаем, че клетка съдържа всички елементи.
В показания пример формулата, която използваме, е:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))=COUNTA(things)
Обяснение
Ключът е този фрагмент:
ISNUMBER(SEARCH(things,B5)
Това се основава на друга формула (обяснена подробно тук), която просто проверява клетката за единичен подниз. Ако клетката съдържа подниза, формулата връща TRUE. Ако не, формулата връща FALSE.
Ако обаче дадем на същата формула списък с неща (в този случай използваме именуван диапазон, наречен „неща“, E5: E7), ще ни върне списък с TRUE / FALSE стойности, по един за всеки елемент в . Резултатът е масив, който изглежда така:
(TRUE; TRUE; TRUE)
Където всяко TRUE представлява намерен елемент, а всяко FALSE представлява елемент, който не е намерен.
Можем да принудим стойностите TRUE / FALSE на 1s и 0s с двойно отрицателно (-, наричано още двойно унарно):
--ISNUMBER(SEARCH(things,B5))
което дава масив като този:
(1; 1; 1)
След това обработваме този масив със SUMPRODUCT, което ще ни даде обща сума. Ако тази сума е равна на броя на елементите в посочения диапазон "неща", знаем, че сме намерили всички неща и можем да върнем TRUE. Начинът, по който правим това, е да сравняваме двете числа директно. Получаваме брой непразни клетки в "неща", използвайки COUNTA:
=COUNTA(things)
С твърдо кодиран списък
Няма изискване да използвате диапазон за вашия списък с неща. Ако търсите само малък брой неща, можете да използвате списък във формат на масив, който се нарича константа на масива. Например, ако просто търсите цветовете червено, синьо и зелено, можете да използвате ("червено", "синьо", "зелено") по следния начин:
=SUMPRODUCT(--ISNUMBER(SEARCH(("yellow","green","dog"),B5)))=COUNTA(things)
