Generička formula
=COUNT(MATCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),allowed&"",0))=LEN(A1)
Sažetak
Da biste koristili provjeru podataka kako biste omogućili popis samo određenih znakova, možete upotrijebiti prilično složenu formulu niza koja se temelji na funkcijama COUNT, MATCH i LEN. U prikazanom primjeru provjera valjanosti podataka primjenjuje se ovom formulom:
=COUNT(MATCH(MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1),allowed&"",0))=LEN(B5)
gdje je "dopušteno" imenovani raspon D5: D11.
Obrazloženje
Radeći iznutra prema van, funkcija MID koristi se za generiranje niza iz teksta unesenog u B5 s ovim isječkom:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
ovdje detaljno objašnjeno. Rezultat je niz poput ovog:
("A";"A";"A";"-";"1";"1";"1")
koja ulazi u MATCH kao vrijednost pretraživanja. Za pretraživački niz koristimo imenovani raspon "dopušteno", spojen u prazan niz (""):
allowed&""
Spajanje pretvara bilo koji broj u nizove, tako da podudaramo jabuke i jabuke. Rezultat je niz poput ovog:
("A";"B";"C";"1";"2";"3";"-")
Posljednji argument u MATCH, match_type postavljen je na nulu kako bi se forsiralo točno podudaranje. Budući da dajemo MATCH višestruke vrijednosti pretraživanja, vraćamo niz s više rezultata:
(1;1;1;7;4;4;4)
Svaki broj u ovom nizu predstavlja podudaranje. U slučaju da se za znak ne pronađe podudaranje, niz će sadržavati pogrešku # N / A.
Konačno, koristi se funkcija COUNT, broji brojeve u rezultatskom polju, što se uspoređuje s brojem svih znakova u ćeliji izračunatog s funkcijom LEN. Kada MATCH pronađe podudaranje za sve znakove, brojevi su jednaki, formula vraća TRUE i provjera podataka uspijeva. Ako MATCH ne pronađe nijedan znak, umjesto broja vraća # N / A. U tom se slučaju brojanje ne podudara i provjera valjanosti podataka ne uspijeva.
Napomena: ova se formula za obavljanje posla oslanja na grubu silu. Ako imate bolji pristup, ostavite komentar ispod.
