Генеричка формула
=COUNT(MATCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),allowed&"",0))=LEN(A1)
Резиме
Да бисте користили проверу података како бисте дозволили листу само одређених знакова, можете користити прилично сложену формулу низа засновану на функцијама ЦОУНТ, МАТЦХ и ЛЕН. У приказаном примеру валидација података се примењује са овом формулом:
=COUNT(MATCH(MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1),allowed&"",0))=LEN(B5)
где је „дозвољено“ именовани опсег Д5: Д11.
Објашњење
Радећи изнутра, МИД функција се користи за генерисање низа из текста унетог у Б5 са овим исечком:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
овде детаљно објашњено. Резултат је низ попут овог:
("A";"A";"A";"-";"1";"1";"1")
која улази у МАТЦХ као вредност претраживања. За претраживачки низ користимо именовани опсег „дозвољено“, спојен у празан низ („„):
allowed&""
Спајање претвара било које бројеве у низове, тако да подударамо јабуке и јабуке. Резултат је низ попут овог:
("A";"B";"C";"1";"2";"3";"-")
Последњи аргумент у МАТЦХ, матцх_типе је постављен на нулу да би се форсирало тачно подударање. Будући да МАТЦХ додељујемо више вредности претраживања, добијамо низ са више резултата:
(1;1;1;7;4;4;4)
Сваки број у овом низу представља подударање. У случају да за знак није пронађено подударање, низ ће садржати грешку # Н / А.
Коначно, користи се функција ЦОУНТ, броји бројеве у резултатском пољу, што се упоређује са бројањем свих знакова у ћелији израчунатог помоћу функције ЛЕН. Када МАТЦХ пронађе подударање за све знакове, бројање је једнако, формула враћа ТРУЕ, а валидација података успева. Ако МАТЦХ не пронађе ниједан знак, уместо броја враћа # Н / А. У том случају, бројање се не подудара и потврда података не успева.
Напомена: ова формула се ослања на грубу силу да би се посао обавио. Ако имате бољи приступ, оставите коментар испод.
