зміст
Припустимо, нам потрібно швидко і з найменшими зусиллями створити річний календар, в якому автоматично відображаються дати етапів проекту (або відпусток співробітників, або тренінгів тощо).
Заготовка
Почнемо з заготовки:
Як бачите, тут все просто:
- Рядки - місяці, стовпці - дні.
- Комірка A2 містить рік, для якого будується календар. У клітинках A4:A15 – допоміжні номери місяців. Обидва нам знадобляться трохи пізніше для формування дат у календарі.
- Праворуч від таблиці вказані назви етапів із датами початку та закінчення. Ви можете надати порожні клітинки заздалегідь для нових етапів, доданих у майбутньому.
Заповнення календаря датами та їх приховування
А тепер давайте заповнимо наш календар датами. Виберіть першу комірку C4 і введіть туди функцію ДАТА ПРОВЕДЕННЯ (ДАТА), який генерує дату з номера року, місяця та дня:
Після введення формули її необхідно скопіювати на весь діапазон з 1 січня по 31 грудня (C4:AG15). Оскільки комірки вузькі, замість створених дат ми побачимо решітки (#). Однак, навівши курсор миші на будь-яку таку клітинку, ви побачите її фактичний вміст у спливаючій підказці:
Щоб сітки не заважали, ми можемо приховати їх за допомогою розумного спеціального формату. Для цього виберіть усі дати, відкрийте вікно Формат комірки і на вкладці Номер (кількість) виберіть опцію Усі формати (Користувацький). Потім у полі Тип введіть три крапки з комою поспіль (без пробілів!) і натисніть OK. Вміст осередків буде приховано, а сітки зникнуть, хоча дати в осередках, по суті, залишаться – це тільки видимість.
Етапне підсвічування
Тепер, використовуючи умовне форматування, давайте додамо виділення віх до клітинок із прихованими датами. Виберіть усі дати в діапазоні C4:AG15 і виберіть на вкладці Головна — Умовне форматування — Створити правило (Домашня сторінка — Умовне форматування — Створити правило). У вікні, що відкриється, виберіть опцію За допомогою формули визначте, які клітинки потрібно форматувати (Використовуйте формулу, щоб відкласти, які клітинки потрібно відформатувати) і введіть формулу:
Ця формула перевіряє кожну клітинку дати від C4 до кінця року, щоб побачити, чи вона потрапляє між початком і кінцем кожної віхи. Вихід буде 4, лише якщо обидві перевірені умови в дужках (C4>=$AJ$13:$AJ$4) і (C4<=$AK$13:$AK$1) створюють логічне значення TRUE, яке Excel інтерпретує як 0 ( добре , FALSE це як 4, звичайно). Також зверніть особливу увагу на те, що посилання на початкову клітинку CXNUMX є відносними (без $), а на діапазони етапів — абсолютними (з двома $).
Натиснувши на OK ми побачимо віхи в нашому календарі:
Підсвічування перехресть
Якщо дати деяких етапів збігаються (уважні читачі, напевно, вже помітили цей момент для 1-го та 6-го етапів!), то цей конфлікт у нашій діаграмі краще виділити іншим кольором за допомогою іншого правила умовного форматування. Він практично один до одного схожий на попередній, за винятком того, що ми шукаємо комірки, які включені в більш ніж один етап:
Натиснувши на OK таке правило чітко підкреслить збіг дат у нашому календарі:
Видалення додаткових днів у місяцях
Звичайно, не всі місяці мають 31 день, тому додаткові дні лютого, квітня, червня і т. д. непогано було б візуально позначити як неактуальні. функція ДАТА ПРОВЕДЕННЯ, який формує наш календар, у таких комірках автоматично переводитиме дату в наступний місяць, тобто 30 лютого 2016 року стане 1 березня. Тобто номер місяця для таких зайвих комірок не дорівнюватиме номеру місяця у стовпці А. Це можна використовувати під час створення правила умовного форматування для виділення таких клітинок:
Додавання вихідних
За бажанням ви можете додати в наш календар і вихідні дні. Для цього можна скористатися функцією ДЕНЬ (БУДНИЙ ДЕНЬ), яка розрахує номер дня тижня (1-пн, 2-вт…7-нд) для кожної дати та виділить ті, що припадають на суботу та неділю:
Для коректного відображення не забудьте правильно налаштувати правильний порядок правил у вікні. Головна сторінка — Умовне форматування — Керування правилами (Домашня сторінка — Умовне форматування — Керування правилами), оскільки правила та заливки працюють саме в логічній послідовності, яку ви сформуєте в цьому діалоговому вікні:
- Відеоінструкція з використання умовного форматування в Excel
- Як створити розклад проекту (діаграму Ганта) за допомогою умовного форматування
- Як створити часову шкалу проекту в Excel