Правила перевірки між клітинками

Що таке міжклітинні правила?

Кожне правило перевірки, яке ви створювали до цього, перевіряло значення однієї клітини за фіксованою умовою — діапазон чисел, список, текстовий шаблон. Міжклітинна перевірка йде далі: вона перевіряє введене значення відносно іншої клітини в тому ж рядку.

Як Excel обробляє міжклітинні посилання

Коли ви створюєте власну формулу перевірки для клітини у рядку 2, ви можете вільно посилатися на будь-яку іншу клітину в цьому ж рядку. Excel автоматично коригує посилання, коли правило застосовується до стовпця — так само, як і звичайна формула.

Приклад 1: Кінцева дата має бути після початкової

Це найпоширеніше міжклітинне правило. Кінцева дата, введена раніше за початкову, є логічною помилкою, яку не може виявити жодне вбудоване правило — але власна формула впорається з цим.

Якщо ваша перевірка у стовпці L (End Date) посилається на стовпець K (Start Date):

=L2>K2

Excel автоматично перевіряє L3>K3, L4>K4 і так далі для кожної наступної клітини. Якщо End Date раніше або дорівнює Start Date, введення відхиляється.

Приклад 2: Знижка вимагає мінімальної кількості

Знижка має застосовуватися лише тоді, коли кількість замовлення це виправдовує. Припустимо, Quantity у стовпці H, а Discount % у стовпці J:

=IF(J2>0, H2>=3, TRUE)

Це означає: якщо вводиться знижка, кількість має бути не менше 3 — інакше завжди приймати.

TRUE наприкінці важливий — він гарантує, що рядки з нульовою знижкою проходять без перевірки кількості.

Приклад 2: Ціна за одиницю має відповідати категорії

Технічні товари ніколи не повинні коштувати менше 500. Для офісних товарів таке обмеження відсутнє. Припустимо, Category у стовпці D, а Unit Price у стовпці I:

=IF(D2="Tech", I2>=500, TRUE)

Це означає: якщо категорія — Tech, застосовується мінімальна ціна; інакше приймається будь-яка ціна.

Безпечне створення правил між клітинками

Кілька важливих моментів:

• Завжди фіксуйте правильний стовпець — використовуйте відносні посилання на рядки (H2, а не $H$2), щоб формула коректно застосовувалася до всього стовпця;
• Враховуйте порожні клітинки — якщо посилання може бути на порожню клітинку, обгорніть формулу у IF або використовуйте IFERROR, щоб уникнути неочікуваних відхилень;
• Тестуйте на граничних випадках — однакові дати, нульові кількості, порожні поля — саме тут правила між клітинками найчастіше дають збій.

Завдання

1. Застосуйте перевірку даних між клітинками для стовпця End Date:

   • Формула: =L2>K2
   • Стиль помилки: Stop
   • Повідомлення про помилку: "End Date must be after Start Date"

2. Застосуйте перевірку даних між клітинками для стовпця Discount %:

   • Формула: =IF(J2>0, H2>=3, TRUE)
   • Стиль помилки: Warning
   • Повідомлення про помилку: "Discount should only be applied for quantities of 3 or more"

3. Застосуйте перевірку даних між клітинками для стовпця Unit Price:

   • Формула: =IF(D2="Tech", I2>=500, TRUE)
   • Стиль помилки: Stop
   • Повідомлення про помилку: "Tech products must be priced at 500 or above"

4. Застосуйте перевірку даних між клітинками для стовпця Start Date:

   • Формула: =K2>=B2
   • Стиль помилки: Stop
   • Повідомлення про помилку: "Start Date cannot be before Order Date"

5. Перевірте кожне правило, ввівши навмисно конфліктні значення у різних стовпцях — переконайтеся, що всі чотири правила спрацьовують коректно;

6. Перевірте з порожніми клітинками у відповідних стовпцях — зверніть увагу на неочікувану поведінку та подумайте, як її можна врахувати.