Formuła ogólna
=COUNT(MATCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),allowed&"",0))=LEN(A1)
Podsumowanie
Aby użyć sprawdzania poprawności danych, aby zezwolić na listę tylko określonych znaków, można użyć dość skomplikowanej formuły tablicowej opartej na funkcjach LICZBA, PODAJ.POZYCJĘ i DŁ. W przedstawionym przykładzie walidacja danych jest stosowana za pomocą następującego wzoru:
=COUNT(MATCH(MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1),allowed&"",0))=LEN(B5)
gdzie „dozwolone” to nazwany zakres D5: D11.
Wyjaśnienie
Działając od wewnątrz, funkcja MID służy do generowania tablicy z tekstu wprowadzonego w B5 za pomocą tego fragmentu:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
szczegółowo wyjaśniono tutaj. Rezultatem jest tablica taka:
("A";"A";"A";"-";"1";"1";"1")
która trafia do MATCH jako wartość wyszukiwania. W przypadku tablicy wyszukiwania używamy nazwanego zakresu „dozwolone”, połączonego z pustym ciągiem („”):
allowed&""
W konkatenacji wszystkie liczby są konwertowane na łańcuchy, dzięki czemu dopasowujemy jabłka do jabłek. Rezultatem jest tablica taka:
("A";"B";"C";"1";"2";"3";"-")
Ostatni argument funkcji MATCH, match_type jest ustawiany na zero, aby wymusić dokładne dopasowanie. Ponieważ podajemy MATCH wiele wartości wyszukiwania, otrzymujemy tablicę z wieloma wynikami:
(1;1;1;7;4;4;4)
Każda liczba w tej tablicy reprezentuje dopasowanie. W przypadku, gdy nie zostanie znalezione dopasowanie dla znaku, tablica będzie zawierać błąd # N / A.
Na koniec używana jest funkcja COUNT zliczająca liczby w tablicy wyników, która jest porównywana z liczbą wszystkich znaków w komórce obliczoną za pomocą funkcji LEN. Gdy funkcja MATCH znajduje dopasowanie dla wszystkich znaków, liczby są równe, formuła zwraca wartość PRAWDA, a sprawdzanie poprawności danych kończy się powodzeniem. Jeśli PODAJ.POZYCJĘ nie znajdzie dopasowania żadnego znaku, zwraca # N / A zamiast liczby. W takim przypadku liczby nie są zgodne i sprawdzanie poprawności danych kończy się niepowodzeniem.
Uwaga: ta formuła opiera się na brutalnej sile, aby wykonać zadanie. Jeśli masz lepsze podejście, zostaw komentarz poniżej.
