Цей підручник покаже, як перевірити формули перед їх застосуванням до умовного форматування в Excel.
Тестування користувацьких формул - Excel
Застосовуючи спеціальну формулу до умовного форматування в Excel, важливо переконатися, що формула дійсно повертає правильне значення TRUE або FALSE на вашому аркуші, щоб умовне форматування працювало належним чином.
Перш ніж створити правило умовного форматування, ми можемо створити формулу у "вільному просторі" у нашій книзі Excel і переконатися, що формула працює правильно.
Тестування формули для виділення клітинки аркуша
У наведеному вище прикладі ми хочемо написати формулу, щоб побачити, чи кожна клітинка на аркуші більша за 5, а якщо вона більша за 5, ми хочемо пофарбувати фон комірки.
Ми можемо почати з першої комірки в діапазоні - в даному випадку B3, а потім перевірити кожен рядок і стовпець діапазону - аж до E11.
Починаючи з простої формули, ми можемо побачити, що B3 має значення 5 і тому НЕ більше 5.
Потім ми можемо використовувати маркер, щоб перетягнути цю формулу до рядка 11, зауваживши, що частина рядка адреси комірки (тобто 3) зміниться на наступне число, коли ми будемо перетягувати формулу вниз по рядках, щоб B3 перетворився на B4, тоді В5 і так далі.
Потім ми можемо перетягнути виділений діапазон клітинок через 4 стовпці, щоб перевірити, чи мають значення клітинки у стовпцях С до Е більше 5. Коли ми перетягуємо формулу, частина стовпця адреси комірки відповідно зміниться - від стовпці C - D - E - F.
Як ми тепер бачимо, ми отримуємо правильне значення TRUE або FALSE залежно від значення у відповідній комірці.
Тепер ми знаємо, що наша формула правильна, і ми можемо використовувати цю формулу для створення правила умовного форматування. Як і у нашій формулі тесту, ми використовуємо першу клітинку в діапазоні, B3.
= B3> 5
Після того, як у формулі є типи, ми можемо встановити Формат для кольору фону і натиснути ОК.
Як бачите, формула = B3> 5 застосовується до діапазону B3: E11. Осередок формули завжди повинен відповідати першій комірці в діапазоні для форматування.
Натисніть Застосувати щоб застосувати форматування до свого робочого аркуша.
Перевірка формули для виділення рядка аркуша
Застосування формули для зміни кольору фону рядка замість стовпця дещо інше і дещо складніше.
Розглянемо наступний аркуш.
На цьому аркуші ми хочемо виділити весь рядок, якщо проект прострочений - тому, якщо у стовпці E є комірка, яка повертає значення "Прострочений" замість "Вчасно“, Тоді ми хочемо виділити весь рядок, у якому знаходиться ця клітинка.
Формула для цього проста:
= IF (E4 = "Прострочено", TRUE, FALSE)
Однак, якщо ми застосуємо це до нашого умовного форматування, це буде те, що буде повернуто.
Буде відформатовано лише перший стовпець діапазону.
Давайте спробуємо цю формулу на нашому робочому аркуші Excel.
Ми отримуємо бажаний результат, ІСТИНА, коли Проект прострочений у стовпці Е. Однак, якщо ми скопіюємо цю формулу для 5 стовпців у діапазоні (наприклад: Стовпець В - Стовпець Е), результат буде повернутись НЕВІРНО .
Формула зміниться - отже, стовпець E зміниться на стовпець F - і, звичайно, у стовпці F немає нічого, тому формула щоразу повертатиме значення FALSE.
Нам потрібно переконатися, що формула ТІЛЬКИ дивиться на стовпець E - але це на правильний рядок - тому, коли ми копіюємо формулу поперек, стовпець E залишається незмінним. Для цього нам потрібно використовувати змішане посилання, яке блокує стовпець на місці.
= $ E4 = "Надлишок"
Тепер, коли ми скопіюємо його через 4 стовпці, стовпець у формулі залишиться незмінним, але рядок зміниться.
Оскільки наша формула зараз працює на аркуші, ми можемо створити власну формулу в умовному форматуванні.
Коли ми натискаємо в порядку, і Застосувати, наші рядки, які мають стовпець E із простроченням, будуть виділені.
