Formuła ogólna
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))=COUNTA(things)
Podsumowanie
Jeśli chcesz przetestować komórkę, aby sprawdzić, czy zawiera wszystkie elementy na liście, możesz to zrobić za pomocą formuły korzystającej z funkcji SZUKAJ, korzystając z funkcji LICZBA, SUMA i ILOŚĆ.
Kontekst
Załóżmy, że masz listę ciągów tekstowych z zakresu B5: B8 i chcesz się dowiedzieć, czy te komórki zawierają wszystkie słowa z innego zakresu, E5: E7.
Możesz zbudować formułę, która używa zagnieżdżonych instrukcji IF do sprawdzania każdego elementu, ale nie będzie to dobrze skalowane, jeśli masz wiele rzeczy do wyszukania. Za każdym razem, gdy dodajesz wyszukiwane słowo, musisz dodać kolejny zagnieżdżony IF i dostosować nawiasy.
Rozwiązanie
Rozwiązaniem jest utworzenie formuły zliczającej wszystkie dopasowania naraz. Gdy już to zrobimy, po prostu porównujemy tę liczbę z liczbą przedmiotów, których szukamy. Jeśli pasują, wiemy, że komórka zawiera wszystkie elementy.
W pokazanym przykładzie formuła, której używamy, to:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))=COUNTA(things)
Wyjaśnienie
Kluczem jest ten fragment:
ISNUMBER(SEARCH(things,B5)
Jest to oparte na innej formule (szczegółowo wyjaśnionej tutaj), która po prostu sprawdza komórkę pod kątem pojedynczego podciągu. Jeśli komórka zawiera podciąg, formuła zwraca wartość PRAWDA. Jeśli nie, formuła zwraca FALSE.
Jeśli jednak podamy tę samą formułę listę rzeczy (w tym przypadku używamy nazwanego zakresu o nazwie „rzeczy”, E5: E7), zwróci nam to listę wartości PRAWDA / FAŁSZ, po jednej dla każdego elementu w . Rezultatem jest tablica, która wygląda następująco:
(PRAWDA; PRAWDA; PRAWDA)
Gdzie każda PRAWDA reprezentuje znaleziony element, a każda FAŁSZ reprezentuje element nie znaleziony.
Możemy wymusić wartości PRAWDA / FAŁSZ na 1 i 0 za pomocą podwójnego ujemnego (-, zwanego również podwójnym jednoargumentowym):
--ISNUMBER(SEARCH(things,B5))
co daje tablicę taką jak ta:
(1; 1; 1)
Następnie przetwarzamy tę tablicę za pomocą SUMPRODUCT, co da nam całkowitą sumę. Jeśli ta suma jest równa liczbie elementów w nazwanym zakresie „rzeczy”, wiemy, że znaleźliśmy wszystkie rzeczy i możemy zwrócić wartość TRUE. Sposób, w jaki to robimy, polega na bezpośrednim porównaniu tych dwóch liczb. Otrzymujemy liczbę niepustych komórek w „rzeczach” przy użyciu funkcji COUNTA:
=COUNTA(things)
Z listą zakodowaną na stałe
Nie ma wymogu używania zakresu na liście rzeczy. Jeśli szukasz tylko niewielkiej liczby rzeczy, możesz użyć listy w formacie tablicowym, który nazywa się stałą tablicową. Na przykład, jeśli szukasz tylko kolorów czerwonego, niebieskiego i zielonego, możesz użyć („czerwony”, „niebieski”, „zielony”) w ten sposób:
=SUMPRODUCT(--ISNUMBER(SEARCH(("yellow","green","dog"),B5)))=COUNTA(things)
