Formuła ogólna
=COUNT(FIND(xlist,A1))=0
Podsumowanie
Aby użyć sprawdzania poprawności danych w celu ograniczenia interpunkcji, możesz użyć nazwanego zakresu i formuły opartej na funkcjach ZNAJDŹ i LICZ. W przedstawionym przykładzie walidacja danych zastosowana do C5: C10 to:
=COUNT(FIND(xlist,B5))=0
gdzie xlist to nazwany zakres D5: D11.
Wyjaśnienie
Reguły sprawdzania poprawności danych są uruchamiane, gdy użytkownik dodaje lub zmienia wartość komórki. Gdy formuła niestandardowa zwraca wartość TRUE, walidacja przebiega pomyślnie, a dane wejściowe są akceptowane. Gdy formuła zwraca FALSE, sprawdzanie poprawności kończy się niepowodzeniem, a dane wejściowe są odrzucane za pomocą wyskakującego komunikatu.
W tym przypadku wcześniej zdefiniowaliśmy nazwany zakres „xlist” jako D5: D11. Ten zakres zawiera niedozwolone znaki.
Formuła, której używamy do walidacji danych, to:
=COUNT(FIND(xlist,B5))=0
Działająca od wewnątrz funkcja ZNAJDŹ jest skonfigurowana z xlist dla „znajdowania tekstu” i komórką B5 jako tekstem do wyszukiwania. Ponieważ podajemy ZNAJDŹ tablicę z wieloma wartościami, funkcja ZNAJDŹ zwraca tablicę wyników, po jednym dla każdego znaku z nazwanego zakresu „xlista”. W przypadku komórki B5 wynik funkcji ZNAJDŹ wygląda następująco:
(#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!)
Każdy błąd #VALUE oznacza jeden nie znaleziony znak. Jeśli spróbujemy wpisać, powiedzmy, „demolition @”, które zawiera znak zastrzeżony, funkcja ZNAJDŹ zwraca:
(#VALUE!;11;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!)
Zauważ, że druga wartość w tablicy to teraz 11.
Następnie funkcja COUNT zwraca liczbę wszystkich liczb w tablicy. Gdy tablica nie zawiera liczb (tj. Żadnych znaków zastrzeżonych) LICZBA zwraca zero, wyrażenie zwraca PRAWDA, a walidacja danych kończy się powodzeniem. Jednak gdy tablica nie zawiera liczb (tj. Znaleziono co najmniej jeden zastrzeżony znak) LICZBA zwraca liczbę, wyrażenie zwraca FALSE, a walidacja danych kończy się niepowodzeniem.
Znaki, które pojawiają się na liście xlista nazwanego zakresu, można dostosować do wymagań dopasowania.
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.
