Excel formula: Samo specifični znakovi za provjeru valjanosti podataka -

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.

Zanimljivi članci...