Обобщение
Можете да настроите динамична календарна мрежа на работен лист на 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, за да "върне" правилния брой дни до предишната неделя. CHOOSE работи перфектно в тази ситуация, тъй като позволява произволни стойности за всеки ден от седмицата. Използваме тази функция, за да върнем нула дни назад, когато първият ден от месеца е неделя. Повече подробности за този проблем са предоставени тук.
С първия ден, установен в B6, другите формули в мрежата просто увеличават предишната дата с една, започвайки с формулата в C6:
=IF(B6"",B6,$H5)+1
Тази формула тества клетката веднага вляво за стойност. Ако не е намерена стойност, тя извлича стойност от колона H в реда по-горе. Забележка: $ H5 е смесена препратка, за да заключите колоната, докато формулата се копира в цялата мрежа. Същата формула се използва във всички клетки с изключение на B6.
Правила за условно форматиране
Календарът използва формули за условно форматиране, променя форматирането, за да засенчи предходните и бъдещите месеци и да подчертае текущия ден. И двете правила се прилагат към цялата мрежа. За предходни и следващи месеци формулата е:
=MONTH(B6)MONTH(start)
За текущия ден формулата е:
=B6=TODAY()
За повече подробности вижте: Условно форматиране с формули (10 примера)
Заглавие на календара
Заглавието на календара - месец и година - се изчислява с тази формула в клетка B4:
=start
Форматиран с персонализиран формат на числото "mmmm yyyy". За центриране на заглавието над календара, обхватът B4: H4 има хоризонтално подравняване, зададено на "центриране по избор". Това е по-добра опция от обединяването на клетки, тъй като не променя структурата на мрежата в работния лист.
Непрекъснат календар с текуща дата
За да създадете календар, който се актуализира автоматично въз основа на текущата дата, можете да използвате формула като тази в K5:
=EOMONTH(TODAY(),-1)+1
Тази формула получава текущата дата с функцията TODAY, след това получава първия ден от текущия месец, използвайки функцията EOMONTH. Заменете ДНЕС () с дадена дата, за да изградите календар в различен месец. Повече подробности за това как работи EOMONTH тук.
Стъпки за създаване
- Скриване на мрежовите линии (по избор)
- Добавете граница към B5: H11 (7R x 7C)
- Име K5 „старт“ и въведете дата като „1 септември 2018 г.“
- Формула в B4 = начало
- Формат B4 като „мммм гггг“
- Изберете B4: H4, задайте подравняването на "Центриране по избор"
- В диапазон B5: H5, въведете съкращения за деня (SMTWTFS)
- Формула в B6 = start-CHOOSE (WEEKDAY (start), 0,1,2,3,4,5,6)
- Изберете B6: H11, приложете персонализиран номер формат "d"
- Формула в C6 = IF (B6 "", B6, $ H5) +1
- Копирайте формула в C6 в останалите клетки в решетката на календара
- Добавяне на правило за предварително / следващо форматиране (вижте формулата по-горе)
- Добавете текущо правило за условно форматиране (вижте формулата по-горе)
- Променете датата в K5 на друга „първа от месеца“ за тестване
- За вечен календар формула в K5 = EOMONTH (ДНЕС (), - 1) +1
