Vispārēja formula
=COUNT(MATCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),allowed&"",0))=LEN(A1)
Kopsavilkums
Lai izmantotu datu pārbaudi, lai atļautu tikai noteiktu rakstzīmju sarakstu, varat izmantot diezgan sarežģītu masīva formulu, pamatojoties uz funkcijām COUNT, MATCH un LEN. Parādītajā piemērā datu validācija tiek lietota, izmantojot šo formulu:
=COUNT(MATCH(MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1),allowed&"",0))=LEN(B5)
kur "atļauts" ir nosauktais diapazons D5: D11.
Paskaidrojums
Strādājot no iekšpuses, MID funkcija tiek izmantota, lai ģenerētu masīvu no teksta, kas B5 ievadīts ar šo fragmentu:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
šeit sīki izskaidrots. Rezultāts ir šāds masīvs:
("A";"A";"A";"-";"1";"1";"1")
kas tiek ievadīts MATCH kā uzmeklēšanas vērtība. Uzmeklēšanas masīvam mēs izmantojam nosaukto diapazonu "atļauts", kas savienots ar tukšu virkni (""):
allowed&""
Apvienošana pārvērš visus skaitļus virknēs, lai mēs salīdzinātu ābolus ar āboliem. Rezultāts ir šāds masīvs:
("A";"B";"C";"1";"2";"3";"-")
Pēdējais arguments MATCH, match_type ir iestatīts uz nulli, lai piespiestu precīzu atbilstību. Tā kā mēs piešķiram MATCH vairākas uzmeklēšanas vērtības, mēs atgriežam masīvu ar vairākiem rezultātiem:
(1;1;1;7;4;4;4)
Katrs skaitlis šajā masīvā apzīmē spēli. Gadījumā, ja rakstzīmei nav atrasta atbilstība, masīvā būs kļūda # N / A.
Visbeidzot, tiek izmantota funkcija COUNT, skaitot skaitļus rezultātu masīvā, kas tiek salīdzināts ar visu rakstzīmju skaitu šūnā, kas aprēķināts, izmantojot funkciju LEN. Kad MATCH atrod atbilstību visām rakstzīmēm, to skaits ir vienāds, formula atgriež vērtību TRUE un datu validācija izdodas. Ja MATCH neatrod nevienu rakstzīmi, tā skaitļa vietā atgriež # N / A. Tādā gadījumā skaitļi nesakrīt un datu pārbaude neizdodas.
Piezīme: lai veiktu darbu, šī formula ir balstīta uz brutālu spēku. Ja jums ir labāka pieeja, lūdzu, atstājiet komentāru zemāk.
