Иногда на листе Excel бывает нужен календарь. Например, я ежемесячно формирую график работы на следующий месяц, в котором имеется календарь в виде строки. Такой календарь легко сделать вручную, записав дату начала месяца и протянув ее вправо. Можно вручную сделать и многострочный календарь. Но создание календарей вручную – не наш метод. Во-первых, неинтересно. Во-вторых, если в календаре применить формулу, то появится возможность выбора для него месяца и года из выпадающих списков. Эта статья про календари на листе Excel, сформированные формулами. Формулы массивов в этих календарях не используются. В трёх календарях применяется условное форматирование.
Общие установки для всех примеров календарей
- В ячейке B2 создан выпадающий список месяцев, ей присвоено имя выбранныйМесяц.
- В ячейке C2 создан выпадающий список годов, ей присвоено имя выбранныйГод.
- Формулы всех примеров календарей используют месяц из ячейки выбранныйМесяц (B2) и год из ячейки выбранныйГод (C2).
- Все примеры календарей размещены по адресу B6 (левая верхняя ячейка календаря), этой ячейке присвоено имя началоКалендаря.
- В ячейках календаря с датами установлен формат Д, который отображает в ячейке только день месяца: Формат ячеек… → (все форматы), в поле Тип вставить букву Д.
Календарь 1
Календарь 1 представляет собой таблицу 37х2. Почему 37? В России принято, что неделя начинается с понедельника. Месяц может начаться с любого дня недели. Для дней месяца или дней месяцев первой недели нужно предусмотреть семь ячеек. Если воскресенье – первое число месяца, то для дней месяцев, в которых 31 день, нужно еще 30 ячеек. Итого для календаря нужно 7 + 30 = 37 ячеек для дней месяцев. В верхней строке таблицы записаны дни недели, в нижней строке – формулы. Для наглядности недели залиты разными цветами, субботы и воскресенья, а также их даты, выделяются красным полужирным шрифтом, в таблице отображаются границы ячеек. Условное форматирование в этом календаре не применяется.
Как считает формула
Вычисляется порядковый номер месяца, выбранного в ячейке B2.
=ПОИСКПОЗ(выбранныйМесяц;{"январь";"февраль";"март";"апрель";"май";"июнь";"июль";"август";"сентябрь";"октябрь";"ноябрь";"декабрь"};0)
Из выбранных в ячейках B2 и C2 месяца и года формируется дата начала месяца.
=ДАТА(выбранныйГод;ПОИСКПОЗ(выбранныйМесяц;{"январь";"февраль";"март";"апрель";"май";"июнь";"июль";"август";"сентябрь";"октябрь";"ноябрь";"декабрь"};0);1)
Вычисляется день недели первого дня месяца от 1 (понедельник) до 7 (воскресенье).
=ДЕНЬНЕД(ДАТА(выбранныйГод;ПОИСКПОЗ(выбранныйМесяц;{"январь";"февраль";"март";"апрель";"май";"июнь";"июль";"август";"сентябрь";"октябрь";"ноябрь";"декабрь"};0);1);2)
Вычисляется дата последнего воскресенья предыдущего месяца – из даты начала месяца вычитается день недели начала месяца.
=ДАТА(выбранныйГод;ПОИСКПОЗ(выбранныйМесяц;{"январь";"февраль";"март";"апрель";"май";"июнь";"июль";"август";"сентябрь";"октябрь";"ноябрь";"декабрь"};0);1)-ДЕНЬНЕД(ДАТА(выбранныйГод;ПОИСКПОЗ(выбранныйМесяц;{"январь";"февраль";"март";"апрель";"май";"июнь";"июль";"август";"сентябрь";"октябрь";"ноябрь";"декабрь"};0);1);2)
Вычисляется позиция ячейки в строке с датами, которая будет прибавлена к дате последнего воскресенья предыдущего месяца, чтобы получить нужную дату выбранного месяца.
=СЧЁТЗ(началоКалендаря:B6)
Вычисляются даты в ячейках с датами. Дата = дата начала месяца - день недели первого дня месяца + позиция (от 1 до 37) в строке с днями недели.
=ДАТА(выбранныйГод;ПОИСКПОЗ(выбранныйМесяц;{"январь";"февраль";"март";"апрель";"май";"июнь";"июль";"август";"сентябрь";"октябрь";"ноябрь";"декабрь"};0);1)-ДЕНЬНЕД(ДАТА(выбранныйГод;ПОИСКПОЗ(выбранныйМесяц;{"январь";"февраль";"март";"апрель";"май";"июнь";"июль";"август";"сентябрь";"октябрь";"ноябрь";"декабрь"};0);1);2)+СЧЁТЗ(началоКалендаря:B6)
Отображаются даты только за выбранный месяц. Если номер месяца полученной даты равен номеру месяца в ячейке выбранныйМесяц (В2), то значение ячейки равно полученной дате, иначе – "".
=ЕСЛИ(МЕСЯЦ(ДАТА(выбранныйГод;ПОИСКПОЗ(выбранныйМесяц;{"январь";"февраль";"март";"апрель";"май";"июнь";"июль";"август";"сентябрь";"октябрь";"ноябрь";"декабрь"};0);1)-ДЕНЬНЕД(ДАТА(выбранныйГод;ПОИСКПОЗ(выбранныйМесяц;{"январь";"февраль";"март";"апрель";"май";"июнь";"июль";"август";"сентябрь";"октябрь";"ноябрь";"декабрь"};0);1);2)+СЧЁТЗ(началоКалендаря:B6))=ПОИСКПОЗ(выбранныйМесяц;{"январь";"февраль";"март";"апрель";"май";"июнь";"июль";"август";"сентябрь";"октябрь";"ноябрь";"декабрь"};0);ДАТА(выбранныйГод;ПОИСКПОЗ(выбранныйМесяц;{"январь";"февраль";"март";"апрель";"май";"июнь";"июль";"август";"сентябрь";"октябрь";"ноябрь";"декабрь"};0);1)-ДЕНЬНЕД(ДАТА(выбранныйГод;ПОИСКПОЗ(выбранныйМесяц;{"январь";"февраль";"март";"апрель";"май";"июнь";"июль";"август";"сентябрь";"октябрь";"ноябрь";"декабрь"};0);1);2)+СЧЁТЗ(началоКалендаря:B6);"")
Адрес B6 является единственным адресом в формуле, остальные 3 адреса заменены именами ячеек. При создании календаря на другом листе нужно формулу вставить в первую ячейку строки с датами, изменить в формуле адрес B6 на адрес начала календаря (адрес ячейки с именем началоКалендаря) на этом листе, скопировать ячейку с полученной первой датой и вставить формулы в остальные тридцать шесть ячейки строки.
Календарь 2
Календарь 2 аналогичен Календарю 1, но даты предыдущего и следующего месяцев не скрываются, а отображаются светло-серым шрифтом на сером фоне. Это делается при помощи условного форматирования. Формула при этом упрощается.
=ДАТА(выбранныйГод;ПОИСКПОЗ(выбранныйМесяц;{"январь";"февраль";"март";"апрель";"май";"июнь";"июль";"август";"сентябрь";"октябрь";"ноябрь";"декабрь"};0);1)-ДЕНЬНЕД(ДАТА(выбранныйГод;ПОИСКПОЗ(выбранныйМесяц;{"январь";"февраль";"март";"апрель";"май";"июнь";"июль";"август";"сентябрь";"октябрь";"ноябрь";"декабрь"};0);1);2)+СЧЁТЗ(началоКалендаря:B6)
Адрес B6 является единственным адресом в формуле, остальные 3 адреса заменены именами ячеек. При создании календаря на другом листе нужно изменить в формуле адрес B6 на адрес начала календаря (адрес ячейки с именем началоКалендаря) на этом листе.
Для первых шести и последних девяти ячеек применяется правило условного форматирования: если значение месяца в них не равно месяцу даты первого воскресенья выбранного месяца ($H$7), то цвет их заливки будет 166; 166; 166 (Белый, Фон 1, более темный оттенок 35%), а цвет шрифта - 242; 242; 242 (Белый, Фон 1, более темный оттенок 5%).
Формула условия МЕСЯЦ(B7)<>МЕСЯЦ($H$7) применяется к диапазонам $B$7:$G$7;$AD$7:$AL$7. При создании календаря на другом листе нужно изменить в формуле условия адреса B7 и $H$7, а также адреса диапазонов применения условного форматирования на соответствующие адреса этого листа.
Календарь 3
В этом календаре количество отображаемых ячеек соответствует количеству дней выбранного месяца. Календарь представляет собой таблицу 31х2, в которой заливка и границы ячеек, красный цвет и полужирное начертание шрифта формируются с помощью условного форматирования.
В ячейки первой строки вставлена формула, которая вычисляет день недели даты из второй строки таблицы. Если в строке с датами даты нет (“”), то формула возвращает значение пустой ячейки (“”).
=ЕСЛИ(B7<>"";ИНДЕКС({"Пн";"Вт";"Ср";"Чт";"Пт";"Сб";"Вс"};0;ДЕНЬНЕД(B7;2));"")
В ячейки второй строки вставлена формула, вычисляющая даты, начиная с первого числа выбранного месяца.
Как считает формула
Вычисляется порядковый номер месяца, выбранного в ячейке B2.
=ПОИСКПОЗ(выбранныйМесяц;{"январь";"февраль";"март";"апрель";"май";"июнь";"июль";"август";"сентябрь";"октябрь";"ноябрь";"декабрь"};0)
Из выбранных в ячейках B2 и C2 месяца и года формируется дата начала месяца.
=ДАТА(выбранныйГод;ПОИСКПОЗ(выбранныйМесяц;{"январь";"февраль";"март";"апрель";"май";"июнь";"июль";"август";"сентябрь";"октябрь";"ноябрь";"декабрь"};0);1)
Вычисляется смещение позиции по столбцам от даты начала месяца (принимает значения от 0 до 30).
= СТОЛБЕЦ(B7)-СТОЛБЕЦ(началоКалендаря)
Дата = первое число месяца + смещение позиции по столбцам от даты начала месяца (от 0 до 30).
=ЕСЛИ(МЕСЯЦ(ДАТА(выбранныйГод;ПОИСКПОЗ(выбранныйМесяц;{"январь";"февраль";"март";"апрель";"май";"июнь";"июль";"август";"сентябрь";"октябрь";"ноябрь";"декабрь"};0);1)+(СТОЛБЕЦ(B7)-СТОЛБЕЦ(началоКалендаря)))=ПОИСКПОЗ(выбранныйМесяц;{"январь";"февраль";"март";"апрель";"май";"июнь";"июль";"август";"сентябрь";"октябрь";"ноябрь";"декабрь"};0); ДАТА(выбранныйГод;ПОИСКПОЗ(выбранныйМесяц;{"январь";"февраль";"март";"апрель";"май";"июнь";"июль";"август";"сентябрь";"октябрь";"ноябрь";"декабрь"};0);1)+(СТОЛБЕЦ(B7)-СТОЛБЕЦ(началоКалендаря));"")
Отображаются даты только выбранного месяца. Если номер месяца полученной даты равен номеру месяца в ячейке выбранныйМесяц (В2), то значение ячейки равно полученной дате, иначе – "".
=ЕСЛИ(МЕСЯЦ(ДАТА(выбранныйГод;ПОИСКПОЗ(выбранныйМесяц;{"январь";"февраль";"март";"апрель";"май";"июнь";"июль";"август";"сентябрь";"октябрь";"ноябрь";"декабрь"};0);1)+(СТОЛБЕЦ(B7)-СТОЛБЕЦ(началоКалендаря)))=ПОИСКПОЗ(выбранныйМесяц;{"январь";"февраль";"март";"апрель";"май";"июнь";"июль";"август";"сентябрь";"октябрь";"ноябрь";"декабрь"};0); ДАТА(выбранныйГод;ПОИСКПОЗ(выбранныйМесяц;{"январь";"февраль";"март";"апрель";"май";"июнь";"июль";"август";"сентябрь";"октябрь";"ноябрь";"декабрь"};0);1)+(СТОЛБЕЦ(B7)-СТОЛБЕЦ(началоКалендаря));"")
Адрес B7 является единственным адресом в формуле, остальные 3 адреса заменены именами ячеек. При создании календаря на другом листе этот адрес нужно изменить на адрес первой ячейки с датами на этом листе.
Применяются правила условного форматирования:
1. Отображаются границы непустых ячеек. Условие: =B$7<>"". Применяется к диапазону: =$B$6:$AF$7. Формат: Формат → Граница → Внешние.
2. Субботы и воскресенья отображаются полужирным шрифтом красного цвета. Условие: =ИЛИ(B$6="Сб";B$6="Вс"). Применяется к диапазону: =$B$6:$AF$7. Формат: Формат → Шрифт → Красный, полужирный.
3. Недели с нечетными номерами выделяются светло-зеленым цветом. Условие: =ЕНЕЧЁТ(НОМНЕДЕЛИ(B$7;2)). Применяется к диапазону: =$B$6:$AF$7. Формат: Формат → Заливка → Светло-зеленая.
Недели с четными номерами выделяются светло-синим цветом. Условие: =ЕЧЁТН(НОМНЕДЕЛИ(B$7;2)). Применяется к диапазону: =$B$6:$AF$7. Формат: Формат → Заливка → Светло-синяя.
При создании календаря на другом листе нужно изменить адреса в формулах условий, а также адреса диапазонов применения условного форматирования на соответствующие адреса этого листа.
Календарь 4
В Календаре 4 по сравнению с Календарем 1 изменен расчет позиции ячейки, которая будет прибавлена к дате последнего воскресенья предыдущего месяца, чтобы получить нужную дату выбранного месяца. В этом календаре позиция вычисляется не в строке, а в двумерном массиве ячеек с датами. Условное форматирование в этом календаре не применяется.
Формула СЧЁТЗ(началоКалендаря:B6) заменена на формулу, которая вычисляет номер позиции в строке и прибавляет количество предшествующих строк, умноженное на 7.
=(СТОЛБЕЦ(B7)-СТОЛБЕЦ(началоКалендаря)+1)+7*(СТРОКА(B7)-СТРОКА(началоКалендаря)-1)
Формула, вычисляющая дату, аналогична формуле, примененной в Календаре 1.
=ЕСЛИ(МЕСЯЦ(ДАТА(выбранныйГод;ПОИСКПОЗ(выбранныйМесяц;{"январь";"февраль";"март";"апрель";"май";"июнь";"июль";"август";"сентябрь";"октябрь";"ноябрь";"декабрь"};0);1)-ДЕНЬНЕД(ДАТА(выбранныйГод;ПОИСКПОЗ(выбранныйМесяц;{"январь";"февраль";"март";"апрель";"май";"июнь";"июль";"август";"сентябрь";"октябрь";"ноябрь";"декабрь"};0);1);2)+(СТОЛБЕЦ(B7)-СТОЛБЕЦ(началоКалендаря)+1)+7*(СТРОКА(B7)-СТРОКА(началоКалендаря)-1))=ПОИСКПОЗ(выбранныйМесяц;{"январь";"февраль";"март";"апрель";"май";"июнь";"июль";"август";"сентябрь";"октябрь";"ноябрь";"декабрь"};0);ДАТА(выбранныйГод;ПОИСКПОЗ(выбранныйМесяц;{"январь";"февраль";"март";"апрель";"май";"июнь";"июль";"август";"сентябрь";"октябрь";"ноябрь";"декабрь"};0);1)-ДЕНЬНЕД(ДАТА(выбранныйГод;ПОИСКПОЗ(выбранныйМесяц;{"январь";"февраль";"март";"апрель";"май";"июнь";"июль";"август";"сентябрь";"октябрь";"ноябрь";"декабрь"};0);1);2)+(СТОЛБЕЦ(B7)-СТОЛБЕЦ(началоКалендаря)+1)+7*(СТРОКА(B7)-СТРОКА(началоКалендаря)-1);"")
Адрес B7 является единственным адресом в формуле, остальные 3 адреса заменены именами ячеек. При создании календаря на другом листе этот адрес нужно изменить на адрес первой ячейки с датами на этом листе.
Календарь 5
Календарь 5 аналогичен Календарю 4, но даты предыдущего и следующего месяцев не скрываются, а отображаются светло-серым шрифтом на сером фоне. Это делается при помощи условного форматирования. Формула при этом упрощается.
=ДАТА(выбранныйГод;ПОИСКПОЗ(выбранныйМесяц;{"январь";"февраль";"март";"апрель";"май";"июнь";"июль";"август";"сентябрь";"октябрь";"ноябрь";"декабрь"};0);1)-ДЕНЬНЕД(ДАТА(выбранныйГод;ПОИСКПОЗ(выбранныйМесяц;{"январь";"февраль";"март";"апрель";"май";"июнь";"июль";"август";"сентябрь";"октябрь";"ноябрь";"декабрь"};0);1);2)+(СТОЛБЕЦ(B7)-СТОЛБЕЦ(началоКалендаря)+1)+7*(СТРОКА(B7)-СТРОКА(началоКалендаря)-1)
Адрес B7 является единственным адресом в формуле, остальные 3 адреса заменены именами ячеек. При создании календаря на другом листе этот адрес нужно изменить на адрес первой ячейки с датами на этом листе.
Для первых шести и последних девяти ячеек применяется правило условного форматирования: если значение месяца в них не равно месяцу даты первого воскресенья выбранного месяца ($H$6), то цвет их заливки будет 166; 166; 166 (Белый, Фон 1, более темный оттенок 35%), а цвет шрифта - 242; 242; 242 (Белый, Фон 1, более темный оттенок 5%).
Формула условия МЕСЯЦ(B7)<>МЕСЯЦ($H$7) применяется к диапазонам $B$7:$G$7;$B$11:$H$11;$B$12:$C$12.
При создании календаря на другом листе нужно изменить адреса в формуле условий, а также адреса диапазонов применения условного форматирования на соответствующие адреса этого листа.
Календарь 6
Календарь 6 нравится мне больше других календарей. Он аналогичен Календарю 4, за исключением того, что в нем нет границ ячеек и в строке с датами отсутствует заливка.
При необходимости во все календари можно добавить номера недель.
Это все, о чем я хотел рассказать в этой статье. Примеры календарей выложены на Яндекс.Диск.
Если статья вам понравилась, то лайки и подписка – лучшая благодарность. Если у вас есть замечания и дополнения, расскажите, пожалуйста, о них в комментариях. Отвечу на все заданные в комментариях вопросы.