Спеціальна перевірка за допомогою формул

Що таке перевірка на основі формули?

Тут використовується Користувацька перевірка. Замість вибору типу з випадаючого списку Allow, обирається Custom і вводиться формула безпосередньо. Excel оцінює цю формулу щоразу при введенні значення — якщо результат TRUE, запис приймається; якщо FALSE — відхиляється.

Як це працює

1. Вибір цільових клітинок;
2. Відкриття Data Validation → Settings;
3. У полі Allow вибір Custom;
4. У полі Formula введення будь-якого логічного виразу, що повертає TRUE або FALSE;
5. Натискання OK.

Формула оцінюється відносно першої клітинки у вибраному діапазоні — аналогічно до правила умовного форматування. Якщо вибрано H2:H51 і написано =H2>0, Excel автоматично підлаштовує її до H3>0, H4>0 і так далі вниз по стовпцю.

Приклад електронної пошти клієнта

Стовпець Customer Email завжди повинен містити символ @ — це мінімальна перевірка коректності формату електронної пошти. Жодне вбудоване правило цього не перевіряє, але власна формула виконує це за один рядок:

=ISNUMBER(FIND("@",G2))

Як це працює:

• FIND("@",G2) шукає @ у комірці з електронною поштою — повертає позицію, якщо знайдено, або помилку, якщо ні;
• ISNUMBER() перетворює результат на TRUE (знайдено) або FALSE (не знайдено);
• Excel приймає значення лише якщо результат — TRUE.

Що робить формулу користувача дійсною

• Має повертати TRUE або FALSE (або 1/0);
• Може посилатися на інші комірки в тому ж рядку;
• Може використовувати будь-яку функцію Excel — IF, AND, OR, ISNUMBER, LEN, FIND, COUNTIF та інші;
• Не може напряму посилатися на комірки з інших аркушів у деяких версіях Excel — використовуйте іменований діапазон як обхідний шлях.

Завдання

Знижка лише за умови виконання кількості

1. Застосуйте користувацьку перевірку до стовпця Discount %:
   • Формула: =OR(H2>=3,J2=0);
   • Стиль помилки: Попередження;
   • Повідомлення про помилку: "Discount is only allowed for quantities of 3 or more";
2. Перевірте, ввівши знижку 10 при кількості 1 — спостерігайте попередження;
3. Змініть кількість на 3 та повторно введіть знижку — переконайтеся, що вона приймається.