Обща формула
=COUNT(MATCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),allowed&"",0))=LEN(A1)
Обобщение
За да използвате проверка на данни, за да разрешите само списък от конкретни символи, можете да използвате доста сложна формула на масив, базирана на функциите COUNT, MATCH и LEN. В показания пример валидирането на данни се прилага с тази формула:
=COUNT(MATCH(MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1),allowed&"",0))=LEN(B5)
където "позволено" е наименованият диапазон D5: D11.
Обяснение
Работейки отвътре навън, функцията MID се използва за генериране на масив от текст, въведен в B5 с този фрагмент:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
обяснено подробно тук. Резултатът е масив като този:
("A";"A";"A";"-";"1";"1";"1")
което влиза в MATCH като справочна стойност. За справочен масив използваме наименувания диапазон "разрешен", конкатениран в празен низ (""):
allowed&""
Конкатенацията преобразува всякакви числа в низове, така че да съпоставяме ябълки към ябълки. Резултатът е масив като този:
("A";"B";"C";"1";"2";"3";"-")
Последният аргумент в MATCH, match_type е зададен на нула, за да принуди точно съвпадение. Тъй като даваме MATCH множество справочни стойности, ние връщаме масив с множество резултати:
(1;1;1;7;4;4;4)
Всяко число в този масив представлява съвпадение. В случай, че съвпадение не е намерено за даден знак, масивът ще съдържа грешка # N / A.
Накрая се използва функцията COUNT, брои числата в масива с резултати, което се сравнява с броя на всички знаци в клетката, изчислено с функцията LEN. Когато MATCH намери съвпадение за всички знаци, броят е равен, формулата връща TRUE и проверката на данните е успешна. Ако MATCH не намери съвпадение с нито един знак, той връща # N / A вместо число. В този случай броят не съвпада и проверката на данните е неуспешна.
Забележка: тази формула разчита на груба сила, за да свърши работата. Ако имате по-добър подход, моля, оставете коментар по-долу.
