Microsoft Excel:
  Таблицы и VBA. Справочник.
  Вопросы и Ответы. Советы. Примеры.
Меню Заметки | XL : Календарь с использованием формулы

Rambler's Top100


Counter CO.KZ
Вариантов создания собственного календаря с помощью формул, тоже может быть много, но в этой заметке Вы увидите только несколько способов(формул) : Скачать пример

  • Откройте рабочую книгу, в которой Вы предполагаете создать календарь
  • Активируйте нужный рабочий лист
  • Выберите ячейку, в которой в дальнейшем будет выбираться месяц, например, J2
  • В меню Данные выберите команду Проверка и в появившемся стандартном диалоговом окне Проверка вводимых значений, в поле со списком Тип данных: выберите Целое число, после чего, в поле со списком Значение: выберите Между и в текстовом поле Минимум: введите 1, а в поле поле Максимум: введите 12



  • Выберите ячейку, в которой в дальнейшем будет выбираться год, например, K2
  • В меню Данные выберите команду Проверка и в появившемся стандартном диалоговом окне Проверка вводимых значений, в поле со списком Тип данных: выберите Целое число, затем, в поле со списком Значение: выберите Между и в текстовом поле Минимум: введите 1900 (или 1904, если Вы используете систему дат 1904 - более подробно в меню Сервис - Параметры - Вычисления - Система дат 1904), а в поле Максимум: введите 9999 (на самом деле, при необходимости, Вы можете сократить разрыв между мин. и макс., более того, Вы можете вообще отказаться от проверки, если уверены, что никогда не совершите ошибку при вводе номера месяца и года, или же, выбор будет осуществляться с помощью элементов управления, например, Поле со списком и/или даже Полоса прокрутки)



  • В меню Вставка выберите пункт Имя и команду Присводить. В появившемся стандартном диалоговом окне Присвоение имени, в текстовом поле Имя: введите ДЕНЬ, а в текстовом поле Формула: введите, а лучше, вставьте предварительно скопированный нижеприведённый текст

    ={1;2;3;4;5;6;7:8;9;10;11;12;13;14:15;16;17;18;19;20;21:22; 23;24;25;26;27;28:29;30;31;32;33;34;35:36;37;38;39;40;41;42}



  • Выделите диапазон ячеек, состоящий из 42 ячеек = 6 строк X 7 столбцов, например, B3:H8 (важно, чтобы Вы выделили именно этот диапазон, причём, начиная именно с ячейки B3, в противном случае, в некоторых формулах придётся менять адрес этой ячейки)
  • Введите одну из нижеопубликованных формул массива и обязательно нажмите клавиши CTRL + SHIFT + ENTER (последняя формула предполагает предварительное присвоение ячейкам K2, J2 имён ГОД и МЕСЯЦ см.пример)

    =ДАТА(K2;J2;ДЕНЬ)-ДЕНЬНЕД(ДАТА(K2;J2;1);3)

    =ДАТА(K2;J2;ДЕНЬ-ДЕНЬНЕД(ДАТА(K2;J2;1);3))

    =ДАТА(ГОД;МЕСЯЦ;ДЕНЬ-ДЕНЬНЕД(ДАТА(ГОД;МЕСЯЦ;1);3))

  • Теперь измените параметры форматирования (порядок действий может быть другой, в т.ч. и до создания формулы) :
    1. В меню Формат выберите команду Ячейки, и в появившемся диалоговом окне в списке Числовые форматы: выберите (все форматы), а в текстовом поле Тип: введите ДД (если дни с 1 по 9 должны отображаться с лидирующим нулём, т.е. 01, 02 и т.д. или Д (если лидирующий нуль не нужен)



    2. В меню Формат выберите команду Условное форматирование и в диалоговом окне в списке Условие 1 выберите Формула, а в текстовом поле введите следующую формулу :

      =МЕСЯЦ(B3)<>$J$2

      Теперь, кликните кнопку Формат и установите цвет шрифта и/или заливки так, чтобы даты смежных месяцев не отображались, для этого, например, установите цвет шрифта = изначальному цвету заливки или выберите белый цвет (оба варианта можно найти в примере)
    3. Если Вы хотите, чтобы текущий день выделялся на Вашем календаре, то кликните кнопку А также >> и добавьте нижеприведённую формулу и, разумеется, измените Формат так, чтобы Сегодня не терялось на фоне всех остальных дней.

      =B3=СЕГОДНЯ()



    4. Если Вы что-то забыли ... самое время довести до совершенства свой календарь, например, выделить выходные дни, т.е. субботу и воскресенье красным цветом.
    Теперь, календарь на выбранный месяц готов, и он может выглядить, например, вот так :



    Если же Вас "пугает" формула массива, которая, кстати, была выбрана потому, что позволяет избежать случайного удаления/изменения в отдельно взятой ячейке, причём, без защиты ячейки и рабочего листа, то второй вариант заключается в создании самой первой даты и постепенному увеличению этой даты на один день. Т.е., если в ячейке B3 Вы введёте :

    =ДАТА($K$2;$J$2;1)-ДЕНЬНЕД(ДАТА($K$2;$J$2;1);3)
    =ДАТА(ГОД;МЕСЯЦ;1)-ДЕНЬНЕД(ДАТА(ГОД;МЕСЯЦ;1);3)

    или

    =("1."&J2&"."&K2)-ДЕНЬНЕД("1."&J2&"."&K2;3)
    =("1."&МЕСЯЦ&"."&ГОД)-ДЕНЬНЕД("1."&МЕСЯЦ&"."&ГОД;3)

    то в ячейке C3 достаточно уже будет ввести =B3+1 и т.д.


    Автор : Климов Павел Юрьевич
    • © 2004-2016 Климов П.Ю. Все права защищены. WebDesign & Error's Klimoff