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

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

Тут використовується Користувацька перевірка. Замість вибору типу з випадаючого списку 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 і знову введіть знижку — переконайтеся, що її прийнято.