Загальна формула
=SUMPRODUCT(--ISNUMBER(SEARCH(list,A1)))=0
Резюме
Щоб заборонити введення, яке містить одну з багатьох речей, ви можете скористатися власним правилом перевірки даних на основі функції ПОШУК.
У наведеному прикладі перевірка даних, застосована до B5: B11, це:
=SUMPRODUCT(--ISNUMBER(SEARCH(list,B5)))=0
Пояснення
Правила перевірки даних запускаються, коли користувач додає або змінює значення комірки.
Ця формула використовує функцію ПОШУК для перевірки введення користувачем кожного значення в названому діапазоні "список". Логіка пошуку "містить" - коли знайдено значення зі "списку", SEARCH повертає позицію значення як число. Якщо не знайдено, ПОШУК повертає помилку.
Потім функція ISNUMBER перетворює числа в TRUE і помилки в FALSE, а подвійний негативний оператор змінює значення TRUE FALSE на 1s та нулі. Оскільки названий діапазон "список" містить 5 значень, ми отримуємо 5 результатів у такому масиві:
(0; 0; 0; 0; 0)
Потім SUMPRODUCT підсумовує елементи масиву і результат перевіряється на нуль. Поки всі елементи дорівнюють нулю, SUMPRODUCT повертає нуль і перевірка успішно виконується. Якщо SUMPRODUCT повертає інше число (тобто коли елемент у "списку" знайдений), формула повертає FALSE і перевірка не вдається.
Примітка: Посилання на клітинки у формулах перевірки даних відносно верхньої лівої комірки в діапазоні, вибраному, коли визначено правило перевірки, у цьому випадку B5.
