Умовне форматування з використанням формул

У Excel умовне форматування дозволяє використовувати формули в умовах замість значень. У такому випадку критерій автоматично отримує результуюче значення, яке видає формула в результаті обчислень.

Умовне форматування за формулою

Формули як критерії можна використовувати практично будь-які, які повертають значення. Але для логічних формул (що повертають значення ІСТИНА або брехня) в Excel передбачено окремий тип правил.

При використанні логічних формул як критерії слід:

  1. Вибрати інструмент: "Головна"-"Стилі"-"Умовне форматування"-"Управління правилами".
    умовне
  2. У вікні «Диспетчер правил умовного форматування» натиснути на кнопку «Створити правило».
  3. У списку опцій «Виберіть тип правила:» виберіть опцію «Використовувати формулу для визначення осередків, що форматуються».
    форматування
  4. У полі введення "Форматувати значення, для яких наступна формула є істинною" ввести логічну формулу, а натиснувши кнопку "Формат" вказати стиль оформлення осередків.


Правила використання формул в умовному форматуванні

При використанні формул як критеріїв для правил умовного форматування слід враховувати деякі обмеження:

  1. Не можна посилатися на дані в інших аркушах чи книгах. Але можна посилатися на імена діапазонів (так само в інших аркушах та книгах), що дозволяє обійти це обмеження.
  2. Істотне значення має тип посилань у аргументах формул. Слід використовувати абсолютні посилання (наприклад, =СУММ($A$1:$A$5) на комірки поза діапазоном умовного форматування. А якщо потрібно посилатися на декілька осередків безпосередньо всередині діапазону, тоді слід використовувати змішані типи посилань (наприклад, A$1).
  3. Якщоза умов формула повертає дату чи час, то її результат обчислення сприйматиметься як число. Адже дати це самі цілі числа (наприклад, 01.01.1900 – це число 1 тощо.). А час це дробові значення частини цілої доби (наприклад, 23:15 – це число 0,96875).

Іноді вбудовані умови формату осередків не задовольняють потреб користувачів. Додавання власної формули до умовного форматування забезпечує додаткову функціональність, яка не доступна у будовених функціях даного інструменту. Excel надає можливість використовувати багато критеріїв або застосовувати складні обчислення, що дає широке поле застосування складних налаштувань критеріїв для автоматично генерованого презентабельного експонування важливої ​​інформації.

Практичний приклад використання логічних функцій та формул в умовному форматуванні для порівняння двох таблиць на збіг значень.