«Вечный» календарь в Excel без макросов

Материал из Календарь событий

Версия от 19:59, 17 августа 2010; Sirius (Обсуждение | вклад)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к:навигация, поиск

В этой статье мы рассмотрим процесс создания «вечного» календаря в Excel без использования макросов (на самом деле, в силу специфики Excel, он не будет действительно «вечным», но в нём можно будет построить календарь на любой год в период с 1900 по 9999 для Windows, и с 1904 по 9999 для Mac OS). С первого взгляда может показаться, что это очень сложная задача. На самом же деле всё достаточно просто, хотя необходимо будет прибегнуть к некоторым хитростям. Алгоритм создания календаря:

1. Выбираем ячейку, в которой будет указываться год и вводим туда какое-нибудь первоначальное значение (например «2009»). В нашем случае это будет ячейка A1.

2. Перед тем как приступить ко второму шагу, необходимо определиться со структурой календаря, так как это в той или иной степени может повлиять на дальнейшие шаги. Пускай у нас дни недели располагаются горизонтально, а неделя начинается с понедельника. Укажем название первого месяца (логично, конечно же, начать с января) и дней недели.

Struct cal in excel.png

Под каждый месяц необходимо выделить 7x6=42 ячейки для чисел месяца (так как один месяц может пересекать максимум 6 недель, а в одной неделе 7 дней).

3. Запишем в первой ячейке под понедельником следующую формулу:

=ДАТАЗНАЧ(СЦЕПИТЬ("01.01."; $A$1))-ДЕНЬНЕД(ДАТАЗНАЧ(СЦЕПИТЬ("01.01."; $A$1));3)

Данная формула вычисляет дату, которая приходится на понедельник (причём она может не принадлежать тому месяцу, для которого мы строим календарь). Рассмотрим формулу подробнее. Функция СЦЕПИТЬ предназначена для объединения 2 и более строк. В нашем случае, мы с помощью неё получаем строковое представление даты, соответствующей первому числу нужного месяца и указанного в нашей ячейке A1 года. Функцию СЦЕПИТЬ можно заменить эквивалентным оператором &, тогда формула станет выглядеть так:

=ДАТАЗНАЧ("01.01."& $A$1)-ДЕНЬНЕД(ДАТАЗНАЧ("01.01."&$A$1);3)

Функция ДАТАЗНАЧ преобразует строковое представление даты в числовое. Это необходимо нам для того, чтобы можно было производить арифметические операции над датами. Функция ДЕНЬНЕД возвращает день недели, на который приходится указанная дата. Аргумент 3 после точки с запятой означает, что недели будут нумероваться от 0 до 6 и начинаться с понедельника.

4. Как вы можете заметить, вместо ожидаемой даты, в ячейке появилось обычное число. Ошибки нет, просто в Excel дата представляется в виде целого числа. Для того, чтобы дата предстала перед нами в привычном виде, нужно изменить формат ячейки. Щёлкните правой кнопкой мыши на ячейке и выберите пункт меню «Формат ячеек…». Нам необходимо отображать не всю дату, а только день месяца. Так как среди стандартных форматов отображения дат, необходимого нам нет, то необходимо задать формат самим. На вкладке «Число» выберите пункт «(все форматы)». В поле «Тип» введите ДД и нажмите «OK». Как видите, простое число преобразовалось в нужный нам день месяца.

5. Чтобы получить число, которое приходится на вторник, необходимо всего лишь прибавить значение, полученное в предыдущей ячейке, на 1 (например «=A5+1»). Логично, что эту процедуру нужно повторить для всех 42 ячеек, которые мы выделили для месяца. И не забудьте у всех ячеек поменять формат отображения!

Аналогично создаются календари на остальные месяца (можно просто сделать копии первого календаря, изменив номер месяца в формуле в первой ячейке).

6. Как мы видим, каждая сетка на месяц в нашем календаре может содержать числа предыдущего и следующего месяца. К сожалению, убрать их не получится, но можно сделать их невидимыми. Для этого нужно цвет шрифта сделать совпадающим с цветом фона. В этом нам поможет условное форматирование.

Покажем для начала как сделать невидимыми все числа календарной сетки для января, относящиеся к предыдущему месяцу (декабрю). Выделим все 42 ячейки, отведённые под дни января. На вкладке «Главная» в группе «Стили» выберем пункт меню «Условное форматирование»->«Создать правило»

Uslformat menu.png

В появившемся окне установите тип правила в «Форматировать только ячейки, которые содержат». Ниже, в левом выпадающем списке выберите «Значение ячейки», а в соседнем «Меньше». В правом поле ввода напишем знакомую формулу:

=ДАТАЗНАЧ(СЦЕПИТЬ("01.01."; $A$1))

Тем самым мы сформировали условие, согласно которому под форматирование попадут все ячейки дата, в которых меньше 1 января. Осталось только указать, какое форматирование будет применено для этих ячеек. Для этого в том же окне нажимаем кнопку «Формат…». В появившемся новом окне на вкладке шрифт устанавливаем белый цвет текста. После чего нажимаем два раза «OK» и видим, что все числа, относящиеся к предыдущему месяцу «исчезли». Аналогично делаем и для чисел следующего месяца, только вместо условия «меньше» ставим «больше», а также учитываем, что количество дней в месяцах может отличаться. Например, для января формула будет выглядеть так:

=ДАТАЗНАЧ(СЦЕПИТЬ("01.01."; $A$1))+30

либо так:

=ДАТАЗНАЧ(СЦЕПИТЬ("31.01."; $A$1))

Кроме того не стоит забывать и про февраль, у которого в високосный год 29, а не 28 дней в году. Для февраля формула будет сложнее:

=ДАТАЗНАЧ(СЦЕПИТЬ("01.02.";$A$1))+ЕСЛИ(ОСТАТ($A$1;4)<>0;27;ЕСЛИ(ОСТАТ($A$1;100)<>0;28;ЕСЛИ(ОСТАТ($A$1;400)<>0;27;28)))

Думаю, что смысл второй части выражения объяснять не имеет смысла.

Примечание: Вместо условий «меньше» и «больше» можно использовать условия «вне» или «между», тем самым сократив количество создаваемых правил вдвое. При использовании условия «между» (так как в этом случае под условие будут попадать ячейки нужного месяца) необходимо заранее цвет шрифта в ячейках сделать белым, а уже в условном форматировании менять его на чёрный.

После того, как вы примените условное форматирование ко всем месяцам ваш календарь практически готов.

7. Далее останется только применить обычное форматирование к элементам нашего календаря, чтобы сделать его красивым и наглядным (например, выделить красным цветом выходные дни).

Теперь можно смело пользоваться созданным календарём. Для того чтобы получить календарь на нужный год, необходимо всего лишь изменить значение ячейки с годом.


Скачать календарь в Excel: Calendnomacros.xls

Навигация
Календари
Инструменты