Bendroji formulė
=COUNT(MATCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),allowed&"",0))=LEN(A1)
Santrauka
Norėdami naudoti duomenų tikrinimą, kad leistumėte tik konkrečių simbolių sąrašą, galite naudoti gana sudėtingą masyvo formulę, pagrįstą funkcijomis COUNT, MATCH ir LEN. Pateiktame pavyzdyje duomenų patvirtinimas taikomas pagal šią formulę:
=COUNT(MATCH(MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1),allowed&"",0))=LEN(B5)
kur „leidžiama“ yra pavadintas diapazonas D5: D11.
Paaiškinimas
Dirbant iš vidaus, MID funkcija naudojama norint sukurti masyvą iš teksto, įvesto B5 su šiuo fragmentu:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
čia išsamiai paaiškinta. Rezultatas yra toks masyvas:
("A";"A";"A";"-";"1";"1";"1")
kuris eina į MATCH kaip paieškos reikšmę. Paieškos masyvui naudojame pavadintą diapazoną „leidžiama“, sujungtą į tuščią eilutę („“):
allowed&""
Sujungimas paverčia bet kokius skaičius eilutėmis, kad mes derintume obuolius su obuoliais. Rezultatas yra toks masyvas:
("A";"B";"C";"1";"2";"3";"-")
Paskutinis argumentas „MATCH, match_type“ yra nustatytas į nulį, kad priverstų tikslią atitiktį. Kadangi MATCH suteikiame kelias paieškos reikšmes, mes gauname masyvą su keliais rezultatais:
(1;1;1;7;4;4;4)
Kiekvienas šio masyvo skaičius reiškia atitikmenį. Jei nerandama simbolio atitiktis, masyve bus klaida # N / A.
Galiausiai funkcija COUNT naudojama skaičiuojant rezultatų masyvo skaičius, kuris lyginamas su visų simbolių skaičiumi ląstelėje, apskaičiuotu naudojant funkciją LEN. Kai MATCH randa atitikimą visiems simboliams, skaičiai yra vienodi, formulė grąžina TRUE ir duomenų patvirtinimas pavyksta. Jei MATCH neranda jokio simbolio, jis grąžina # N / A vietoj skaičiaus. Tokiu atveju skaičiai nesutampa ir duomenų patvirtinti nepavyksta.
Pastaba: norint atlikti darbą, ši formulė priklauso nuo grubios jėgos. Jei turite geresnį požiūrį, palikite komentarą žemiau.
