Общая формула
=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")
который входит в ПОИСКПОЗ в качестве значения поиска. Для массива поиска мы используем именованный диапазон «allowed», объединенный с пустой строкой («»):
allowed&""
Конкатенация преобразует любые числа в строки, так что мы сопоставляем яблоки с яблоками. Результатом является такой массив:
("A";"B";"C";"1";"2";"3";"-")
Последний аргумент в MATCH, match_type, равен нулю, чтобы обеспечить точное совпадение. Поскольку мы передаем MATCH несколько значений поиска, мы возвращаем массив с несколькими результатами:
(1;1;1;7;4;4;4)
Каждое число в этом массиве представляет совпадение. Если совпадение для символа не найдено, массив будет содержать ошибку # N / A.
Наконец, функция COUNT используется для подсчета чисел в массиве результатов, который сравнивается с подсчетом всех символов в ячейке, вычисленным с помощью функции LEN. Когда ПОИСКПОЗ находит совпадение для всех символов, счетчики равны, формула возвращает ИСТИНА, и проверка данных завершается успешно. Если ПОИСКПОЗ не находит совпадения ни с одним символом, вместо числа возвращается # Н / Д. В этом случае счетчики не совпадают и проверка данных не выполняется.
Примечание: эта формула использует грубую силу для выполнения работы. Если у вас есть лучший подход, оставьте комментарий ниже.
