Üldine valem
=COUNT(MATCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),allowed&"",0))=LEN(A1)
Kokkuvõte
Andmete valideerimise kasutamiseks ainult konkreetsete märkide loendi lubamiseks võite kasutada funktsioone COUNT, MATCH ja LEN põhinevat üsna keerukat massiivivalemit. Näidatud näites rakendatakse andmete valideerimist selle valemiga:
=COUNT(MATCH(MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1),allowed&"",0))=LEN(B5)
kus "lubatud" on nimega vahemik D5: D11.
Selgitus
Seestpoolt töötades kasutatakse funktsiooni MID massiivi genereerimiseks selle koodilõiguga B5 sisestatud tekstist:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
siin üksikasjalikult selgitatud. Tulemuseks on selline massiiv:
("A";"A";"A";"-";"1";"1";"1")
mis läheb otsingu väärtusena mängu MATCH. Otsingumassiivi jaoks kasutame nimega vahemikku "lubatud", mis on liidetud tühjaks stringiks (""):
allowed&""
Liitmine muudab kõik numbrid stringideks, nii et me sobitame õunad õuntega. Tulemuseks on selline massiiv:
("A";"B";"C";"1";"2";"3";"-")
MATCHi viimane argument match_type on nullitud, et sundida täpset vastet. Kuna anname MATCH-le mitu otsinguväärtust, saame tagasi mitme tulemusega massiivi:
(1;1;1;7;4;4;4)
Selle massiivi iga number tähistab vastet. Kui tähemärgile ei leitud vastet, sisaldab massiiv viga # N / A.
Lõpuks kasutatakse funktsiooni COUNT, et lugeda tulemuste massiivi numbreid, mida võrreldakse LEN-funktsiooniga arvutatud kõigi lahtris olevate märkide arvuga. Kui MATCH leiab vaste kõigile tähemärkidele, on arvud võrdsed, valem tagastab TÕENE ja andmete valideerimine õnnestub. Kui MATCH ei leia ühtegi märki, tagastab see numbri asemel # N / A. Sellisel juhul ei lähe arvud kokku ja andmete valideerimine ebaõnnestub.
Märkus: see valem põhineb töö tegemisel toorel jõul. Parema lähenemisviisi korral jätke kommentaar allpool.
