Резюме
Ви можете встановити динамічну сітку календаря на аркуші Excel із низкою формул, як пояснюється в цій статті. У наведеному прикладі формула в B6 має вигляд:
=start-CHOOSE(WEEKDAY(start),0,1,2,3,4,5,6)
де "старт" - названий діапазон K5, і містить дату 1 вересня 2018 року.
Пояснення
Примітка: Цей приклад передбачає, що дату початку буде вказано як перше числа місяця. Дивіться нижче формулу, яка динамічно повертатиме перший день поточного місяця.
З макетом сітки, як показано, основною проблемою є обчислення дати в першій комірці календаря (B6). Це робиться за такою формулою:
=start-CHOOSE(WEEKDAY(start),0,1,2,3,4,5,6)
Ця формула визначає неділю, що передує першому дню місяця, за допомогою функції ВИБІР, щоб "відкотити" потрібну кількість днів до попередньої неділі. CHOOSE чудово працює у цій ситуації, оскільки дозволяє довільні значення для кожного дня тижня. Ми використовуємо цю функцію для відкочування нульових днів, коли перший день місяця - неділя. Детальніше про цю проблему подано тут.
З першим днем, встановленим у B6, інші формули в сітці просто збільшують попередню дату на одиницю, починаючи з формули в C6:
=IF(B6"",B6,$H5)+1
Ця формула перевіряє значення клітинки відразу ліворуч. Якщо значення не знайдено, воно витягує значення зі стовпця H у рядку вище. Примітка. $ H5 є змішаним посиланням для блокування стовпця, оскільки формула копіюється по всій сітці. Однакова формула використовується у всіх клітинах, крім В6.
Правила умовного форматування
Календар використовує формули умовного форматування, щоб змінити форматування, щоб відтінити попередні та майбутні місяці та виділити поточний день. Обидва правила застосовуються до всієї сітки. Для попереднього та наступних місяців формула:
=MONTH(B6)MONTH(start)
Для поточного дня формула така:
=B6=TODAY()
Детальніше див .: Умовне форматування з формулами (10 прикладів)
Заголовок календаря
Заголовок календаря - місяць і рік - обчислюється за цією формулою в комірці В4:
=start
Відформатовано за спеціальним форматом чисел "мммм рррр". Для центрування заголовка над календарем діапазон B4: H4 має горизонтальне вирівнювання, встановлене на "по центру виділення". Це кращий варіант, ніж об’єднання комірок, оскільки це не змінює структуру сітки на аркуші.
Вічний календар з поточною датою
Щоб створити календар, який автоматично оновлюється на основі поточної дати, ви можете скористатися такою формулою в K5:
=EOMONTH(TODAY(),-1)+1
Ця формула отримує поточну дату за допомогою функції TODAY, а потім отримує перший день поточного місяця за допомогою функції EOMONTH. Замініть TODAY () на будь-яку дату, щоб створити календар у іншому місяці. Детальніше про те, як працює EOMONTH тут.
Кроки для створення
- Приховати лінії сітки (необов’язково)
- Додайте межу до B5: H11 (7R x 7C)
- Назвіть K5 "початок" та введіть дату, наприклад "1 вересня 2018 року"
- Формула в B4 = старт
- Формат B4 як "мммм рррр"
- Виберіть B4: H4, встановіть вирівнювання на "По центру по виділенню"
- В діапазоні B5: H5 введіть скорочення дня (SMTWTFS)
- Формула в B6 = старт-ВИБІР (ВИХОДНИЙ (початок), 0,1,2,3,4,5,6)
- Виберіть B6: H11, застосуйте спеціальний формат номера "d"
- Формула в C6 = IF (B6 "", B6, $ H5) +1
- Скопіюйте формулу в C6 до решти комірок у сітці календаря
- Додати правило попереднього / наступного умовного форматування (див. Формулу вище)
- Додати поточне правило умовного форматування (див. Формулу вище)
- Змініть дату в K5 на іншу дату "першого місяця" для тестування
- Для вічного календаря формула в K5 = EOMONTH (TODAY (), - 1) +1
