Свайпніть щоб показати меню

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

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

Як це працює

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

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

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

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

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

Як це працює:

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

Примітка

Залежно від версії Excel та регіональних налаштувань, у формулах можуть використовуватися як коми ,, так і крапки з комою ; як роздільники аргументів.

Наприклад:

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

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

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

Завдання

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

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

Все було зрозуміло?

Дякуємо за ваш відгук!

Секція 1. Розділ 9

Запитати АІ

Запитайте про що завгодно або спробуйте одне із запропонованих запитань, щоб почати наш чат

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