Загальна формула
=COUNT(FIND(xlist,A1))=0
Резюме
Щоб використовувати перевірку даних для обмеження пунктуації, ви можете використовувати іменований діапазон та формулу на основі функцій FIND та COUNT. У наведеному прикладі перевірка даних, застосована до C5: C10:
=COUNT(FIND(xlist,B5))=0
де xlist - іменований діапазон D5: D11.
Пояснення
Правила перевірки даних запускаються, коли користувач додає або змінює значення комірки. Коли спеціальна формула повертає значення TRUE, перевірка проходить, і введення приймається. Коли формула повертає FALSE, перевірка не вдається, і введення відхиляється зі спливаючим повідомленням.
У цьому випадку ми раніше визначили названий діапазон "xlist" як D5: D11. Цей діапазон містить символи, які заборонені.
Формула, яку ми використовуємо для перевірки даних, така:
=COUNT(FIND(xlist,B5))=0
Функція FIND, що працює зсередини, налаштована на xlist для "пошуку тексту" та комірки B5 як тексту для пошуку. Оскільки ми надаємо FIND масив з кількома значеннями, FIND повертає масив результату, по одному для кожного символу в названому діапазоні "xlist". Для комірки B5 результат з FIND виглядає так:
(#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!)
Кожна помилка #VALUE представляє один символ, який не знайдено. Якщо ми спробуємо ввести, скажімо, "demolition @", що включає обмежений символ, FIND повертає:
(#VALUE!;11;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!)
Зверніть увагу, друге значення в масиві тепер 11.
Далі функція COUNT повертає кількість усіх чисел у масиві. Коли масив не містить чисел (тобто жодних обмежених символів), COUNT повертає нуль, вираз повертає TRUE, і перевірка даних успішна. Однак, коли масив не містить чисел (тобто знайдено принаймні один обмежений символ), COUNT повертає число, вираз повертає FALSE, а перевірка даних не вдається.
Символи, які відображаються в названому діапазоні xlist, можуть бути налаштовані на відповідність вимогам.
Примітка: Посилання на клітинки у формулах перевірки даних відносно верхньої лівої комірки в діапазоні, вибраному, коли визначено правило перевірки, у цьому випадку B5.
