Загальна формула
=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. У цьому випадку підрахунок не збігається, і перевірка даних не вдається.
Примітка: ця формула покладається на грубу силу для виконання роботи. Якщо у вас кращий підхід, залиште коментар нижче.
