Formuła ogólna
=SUMPRODUCT(--ISNUMBER(SEARCH(list,A1)))=0
Podsumowanie
Aby zabronić wprowadzania danych, które zawierają jedną z wielu rzeczy, można użyć niestandardowej reguły sprawdzania poprawności danych opartej na funkcji SEARCH.
W przedstawionym przykładzie walidacja danych zastosowana do B5: B11 to:
=SUMPRODUCT(--ISNUMBER(SEARCH(list,B5)))=0
Wyjaśnienie
Reguły sprawdzania poprawności danych są uruchamiane, gdy użytkownik dodaje lub zmienia wartość komórki.
Ta formuła używa funkcji SZUKAJ do testowania danych wejściowych użytkownika dla każdej wartości z nazwanego zakresu „lista”. Logika wyszukiwania to „zawiera” - po znalezieniu wartości z „listy” funkcja SEARCH zwraca pozycję wartości jako liczbę. Jeśli nie zostanie znaleziony, funkcja SEARCH zwraca błąd.
Następnie funkcja ISNUMBER konwertuje liczby na PRAWDA, a błędy na FAŁSZ, a podwójny operator ujemny zmienia wartości PRAWDA FAŁSZ na 1 i zera. Ponieważ nazwany zakres „lista” zawiera 5 wartości, otrzymujemy 5 wyników w tablicy takiej jak ta:
(0; 0; 0; 0; 0)
SUMPRODUCT następnie sumuje pozycje w tablicy, a wynik jest testowany względem zera. Dopóki wszystkie elementy są równe zero, SUMPRODUCT zwraca zero i walidacja kończy się pomyślnie. Jeśli SUMPRODUCT zwraca inną liczbę (tj. Gdy pozycja na „liście” została znaleziona), formuła zwraca FALSE i walidacja kończy się niepowodzeniem.
Uwaga: Odwołania do komórek w formułach sprawdzania poprawności danych dotyczą lewej górnej komórki w zakresie wybranym podczas definiowania reguły sprawdzania poprawności, w tym przypadku B5.
