Formuła ogólna
=SUMPRODUCT(--(A1=things))>0
Podsumowanie
Jeśli chcesz przetestować komórkę, aby sprawdzić, czy jest równa jednej z kilku rzeczy, możesz to zrobić za pomocą formuły korzystającej z funkcji SUMPRODUCT.
Kontekst
Załóżmy, że masz listę ciągów tekstowych z zakresu B5: B11 i chcesz przetestować każdą komórkę z inną listą elementów z zakresu E5: E9. Innymi słowy, dla każdej komórki w B5: B11 chcesz wiedzieć: czy ta komórka jest równa którejkolwiek z rzeczy w E5: E9?
Możesz zacząć budować dużą formułę na podstawie zagnieżdżonych instrukcji IF, ale formuła tablicowa oparta na SUMPRODUCT jest prostszym i czystszym podejściem.
Rozwiązanie
Rozwiązaniem jest utworzenie formuły, która będzie testować pod kątem wielu wartości i zwracać listę wartości PRAWDA / FAŁSZ. Gdy już to zrobimy, możemy przetworzyć tę listę (właściwie tablicę) za pomocą SUMPRODUCT.
Formuła, której używamy, wygląda następująco:
=SUMPRODUCT(--(B5=things))>0
Wyjaśnienie
Kluczem jest ten fragment:
--(B5=things)
który po prostu porównuje wartość w B5 z każdą wartością z nazwanego zakresu „rzeczy”. Ponieważ porównujemy B5 z tablicą (tj. Nazwanym zakresem „rzeczy”, E5: E11), wynikiem będzie tablica wartości PRAWDA / FAŁSZ w następujący sposób:
(TRUE; FALSE; FALSE; FALSE; FALSE)
Jeśli w tablicy mamy choćby jedną PRAWDA, wiemy, że B5 równa się co najmniej jednej rzeczy na liście, więc aby wymusić wartości PRAWDA / FAŁSZ na 1 i 0, używamy podwójnego ujemnego (-, zwanego także podwójnym jednoargumentowy). Po tym przymusie mamy to:
(1; 0; 0; 0; 0)
Teraz przetwarzamy wynik za pomocą SUMPRODUCT, co zsumuje elementy tablicy. Jeśli otrzymamy wynik niezerowy, mamy co najmniej jedno dopasowanie, więc używamy> 1, aby wymusić końcowy wynik PRAWDA lub FAŁSZ.
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:
--(B5=("red","blue","green"))
Radzenie sobie z dodatkowymi przestrzeniami
Jeśli testowane komórki zawierają dodatkowe spacje, nie będą pasować prawidłowo. Aby usunąć całą dodatkową przestrzeń, możesz zmodyfikować formułę, aby użyć funkcji TRIM w następujący sposób:
=SUMPRODUCT(--(TRIM(A1)=things))>0
