Formuła ogólna
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,A1)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,A1)))=0)
Podsumowanie
Aby przetestować komórkę pod kątem jednego z wielu ciągów, wykluczając inne, możesz użyć formuły opartej na funkcjach SEARCH, ISNUMBER i SUMPRODUCT. W przedstawionym przykładzie formuła w C5 to:
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,B5)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,B5)))=0)
gdzie „uwzględnij” to nazwany zakres E5: E9, a „wyklucz” to nazwany zakres G5: G6.
Wyjaśnienie
Zasadniczo ta formuła wykorzystuje funkcję SZUKAJ do wyszukiwania wielu ciągów w komórce. Po lewej stronie SUMPRODUCT funkcja SEARCH wyszukuje wszystkie ciągi z nazwanego zakresu „include”.
W prawym SUMPRODUCT, SEARCH szuka wszystkich ciągów w nazwanym zakresie „wyklucz”.
W obu częściach formuły funkcja SEARCH zwraca pozycje liczbowe, gdy znaleziono ciągi, oraz błędy, gdy nie. Funkcja ISNUMBER konwertuje liczby na PRAWDA, a błędy na FAŁSZ, a podwójna wartość ujemna konwertuje wartości PRAWDA FAŁSZ na 1 i 0.
Wynik w tym momencie wygląda następująco:
=(SUMPRODUCT((1;0;0;0;0))>0)*(SUMPRODUCT((0;0))=0)
Następnie:
=(1>0)*(0=0) =TRUE*TRUE =1
Uwaga: ta formuła zwraca 1 lub zero, które są obsługiwane jak TRUE i FALSE w formułach, formatowaniu warunkowym lub walidacji danych.
