Підручник Excel: Як перевірити умовне форматування за допомогою фіктивної формули

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

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

Ви можете думати про умовне форматування як про "накладання" невидимих формул, які розташовані поверх комірок.

Коли формула в накладенні повертає TRUE для даної комірки, застосовується форматування.

Формули, які не повертають TRUE (або еквівалент), нічого не роблять.

Проблема полягає в тому, що ви не бачите, як це відбувається, тому вам доведеться використовувати спроби та помилки, що може бути неприємним та трудомістким.

Хороший спосіб пришвидшити ситуацію - використовувати те, що я називаю "фіктивними формулами".

Фіктивні формули дозволяють візуалізувати поведінку формул перед створенням правила.

Дозвольте проілюструвати на дуже простому прикладі. Скажімо, ми хочемо виділити значення понад 100 у цьому наборі даних.

Для початку я виберу ділянку збоку, вишикувану рядами.

Далі я напишу першу формулу щодо верхньої лівої комірки даних.

У цьому випадку це В4, тож формула така

= B4> 100

Тепер скопіюю формулу поперек і вниз.

Зверніть увагу, що ми отримуємо TRUE або FALSE результат у кожній клітинці. Якщо ми перевіримо кілька посилань, ви зможете побачити, що кожна формула обчислює клітинку в даних щодо B4.

А тепер уявіть, що ці результати транспонуються безпосередньо поверх даних. Там, де ви бачите значення TRUE, буде застосовано форматування.

Там, де ви бачите FALSE, нічого не відбувається.

Ця фіктивна формула виглядає добре, тому давайте спробуємо її в умовному правилі форматування.

Спочатку я копіюю першу фіктивну формулу. Потім я вибираю дані та створюю нове правило.

У область формул я просто вставляю формулу. Потім я встановлюю формат і зберігаю правило.

Тепер усі значення понад 100 виділено, точно так, як передбачали фіктивні формули.

Спробуємо ту саму ідею з більш складною формулою. Давайте виділимо рядки в цій таблиці з пріоритетом "А".

Як і раніше, першим кроком є з’ясування, куди покласти фіктивні формули. У нас є достатньо місця праворуч, тому я почну в камері G5.

Оскільки ми хочемо виділити завдання з пріоритетом "А", спробуємо

= B5 = "A"

Коли я копіюю формули, ви бачите, що це не спрацює.

Результати TRUE показують нам лише значення у стовпці B. Ми хочемо виділити цілі рядки, тому мені потрібно скоригувати формулу, щоб заблокувати посилання на стовпець, додавши знак долара:

= $ B5 = "A"

Тепер фіктивні формули працюють. Ми отримуємо повний рядок TRUE, коли пріоритетом є "A".

Давайте спробуємо формулу в новому правилі, виконуючи той самий процес, що і раніше.

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

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

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