Хтось із резервів армії США запитав:
У мене є проста електронна таблиця з датами виконання. Чи є спосіб зробити так, щоб терміни виплат стали зеленими, коли вони закінчились 10 днів, а потім червоними, коли вони вийшли 5 днів?
Це легке завдання за допомогою умовного форматування. Ця функція була представлена в Excel 95. Вона дозволяє вказати до трьох умов і автоматично змінювати форматування комірки на основі цих умов. Ви можете змінити шрифт, зробити шрифт жирним, змінити колір, колір фону. Якщо у вас є комірки, які ви хочете контролювати, щоб перевірити, чи залишають вони певний допуск, умовне форматування - чудовий інструмент для використання.
Найкращий спосіб - це встановити умовний формат для першої комірки у колонці з датами, а потім скопіювати цей формат до всіх інших комірок у діапазоні.
Скажімо, ваш діапазон дат знаходиться в E2: 200. Ви вибрали б клітинку E2 і виконали такі дії: (прокрутіть трохи вниз, щоб побачити зображення під час читання …)
- У меню виберіть Формат - Умовне форматування …
- Ліворуч від діалогового вікна "Умовне форматування" є спадне меню "Значення комірки є". Клацніть на DropDown і змініть значення на "Formula Is". Це змінить праву частину діалогового вікна на одне довге поле.
- У цьому полі введіть цю формулу:
=(E2-TODAY())<6(Зверніть увагу, якщо ваша перша дата не в E2, змініть E2 на клітинку, в якій ви перебуваєте) - Натисніть кнопку Формат внизу діалогового вікна умовного форматування.
- Посередині вкладки «Шрифт» знаходиться випадаючий список кольорів. Виберіть червоний. Або - виберіть жовтий, а на вкладці візерунки виберіть Червоний. Це змусить їх справді виділитися. Натисніть OK, щоб повернутися до діалогового вікна умовного форматування.
Вищезазначені кроки дозволять зробити терміни виконання протягом 5 днів з сьогоднішнього дня червоними. Поки ви перебуваєте в діалоговому вікні умовного форматування, виконайте такі дії:
- Натисніть кнопку Додати >> внизу діалогового вікна, і з’явиться нова Умова 2.
- Змініть "Значення комірки" на "Формула є"
- Введіть формулу
=(E2-TODAY())<11 - Натисніть кнопку Формат і виберіть синій шрифт та зелений візерунок. Натисніть OK, щоб закрити діалогове вікно Форматування комірок.
- Діалогове вікно умовного форматування має виглядати так, як це. Клацніть OK.
Ми майже там. Після того, як ви пройдете всі ці кроки, щоб отримати умовний формат, налаштований для першої комірки, легко скористатися спеціальною функцією «Вставити», щоб скопіювати формати до всього діапазону дат. Коли ви вставляєте спеціальні формати, також буде скопійовано умовне форматування.
- Виділіть клітинку, до якої ви додали кондональне форматування. У цьому прикладі це E2.
- У меню виберіть Редагувати - Копіювати.
- Виділіть усі інші дати у своїх даних - E3: E200. ЯКЩО ви збираєтесь додавати більше даних, сміливо виділяйте кілька зайвих рядків.
- У меню виберіть Редагувати - Вставити спеціальне…
- У діалоговому вікні «Спеціальна вставка» виберіть 4-й запис у лівій колонці - «Формати».
- Клацніть OK.
Це призведе до копіювання умовного форматування до всіх вибраних комірок. Зверніть увагу, що він також скопіює межі та формати чисел, тому, можливо, вам доведеться відрегулювати свої межі після виконання цієї операції.
Примітки:
- Існує обмеження в 3 умовні формати, які можна застосувати до будь-якої комірки. Включаючи форматування за замовчуванням, яке застосовується до комірок, які не відповідають жодній умові, це означає, що ви можете автоматично мати до 4 форматів, що застосовуються до комірки.
- Якщо вам потрібно розширити це до більш ніж 4 форматів, вам доведеться написати макрос у VBA. Макрос буде відносно повільним, оскільки йому доведеться оновити всі комірки після внесення будь-яких змін до електронної таблиці. Ви також можете найняти для цього консультанта Excel.
- Найскладнішою частиною налаштування умовного форматування є введення формули у поле формули. Не забувайте завжди починати формулу зі знака рівності. У книзі може бути ціла глава, присвячена різним формулам, які можна ввести в це поле. Можна вводити формули, які повністю покладаються на інші клітинки.
- Якщо ви вийдете із введення формули і підете пізніше, щоб відредагувати формулу, Excel має неприємну звичку інтерпретувати символ зворотного простору як спробу вказати на клітинки. Це завжди змінить вашу формулу на неправильну річ. Натисніть Escape, щоб повернутися до початкової формули. Корисно виділити помилковий текст за допомогою миші, а потім ввести більше.
