Универсальный календарь формулой
Если вам нужен календарь на листе Microsoft Excel, то у вас есть много разных способов - от кропотливого вбивания дат вручную до подключения всплывающих календарей из различных надстроек и макросов. Еще один вариант - реализовать универсальный календарь на любую дату с помощью всего одной (правда, весьма страшной с непривычки) формулы массива.
Чтобы ее использовать создайте на листе заготовку вот такого вида:
Дата в ячейке B2 может быть любой, тут важен только месяц и год. В ячейках диапазона B3:H3 могут быть названия дней недели в любом подходящем формате.
Теперь выделите диапазон B4:H9 и введите туда вот такую формулу:
=ЕСЛИ(МЕСЯЦ(ДАТА(ГОД(B2);МЕСЯЦ(B2);1)) <>МЕСЯЦ(ДАТА(ГОД(B2);МЕСЯЦ(B2);1)- (ДЕНЬНЕД(ДАТА(ГОД(B2);МЕСЯЦ(B2);1);2)-1) +{0:1:2:3:4:5}*7+{1;2;3;4;5;6;7}-1);" "; ДАТА(ГОД(B2);МЕСЯЦ(B2);1)- (ДЕНЬНЕД(ДАТА(ГОД(B2);МЕСЯЦ(B2);1);2)-1) +{0:1:2:3:4:5}*7+{1;2;3;4;5;6;7}-1)
В английском варианте это будет:
=IF(MONTH(DATE(YEAR(B2),MONTH(B2),1)) <>MONTH(DATE(YEAR(B2),MONTH(B2),1)- (WEEKDAY(DATE(YEAR(B2),MONTH(B2),1))-1) +{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),””, DATE(YEAR(B2),MONTH(B2),1)- (WEEKDAY(DATE(YEAR(B2),MONTH(B2),1))-1) +{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)
Затем нажмите сочетание Ctrl+Shift+Enter, чтобы ввести эту формулу как формулу массива. Все выделенные ячейки должны заполнится датами заданного в B2 месяца:
Останется только навести внешний блеск, добавив форматирование и скрыв день в заголовке B2 и месяц и год в остальных ячейках с помощью окна Формат ячеек (Ctrl+1):
Теперь, меняя дату в ячейке B2, мы будем получать по нашей формуле корректный календарь на любой выбранный месяц любого года. Практически, вечный календарь ;)
Ссылки по теме
- Как подключить всплывающий календарь к листу Excel
- Быстрый ввод даты и времени с помощью надстройки PLEX
- Как Excel работает с датами и временем
- Быстрый ввод даты и времени без разделителей
чуть короче, но по тому же принципу:
=ЕСЛИ(МЕСЯЦ(B2-ДЕНЬ(B2)-ОСТАТ(B2-ДЕНЬ(B2)-1;7)+{0:1:2:3:4:5}*7+{1;2;3;4;5;6;7})<>МЕСЯЦ(B2);"";B2-ДЕНЬ(B2)-ОСТАТ(B2-ДЕНЬ(B2)-1;7)+{0:1:2:3:4:5}*7+{1;2;3;4;5;6;7})или так
=ЕСЛИ(МЕСЯЦ(ОТБР((B2-ДЕНЬ(B2)-1)/7+{0:1:2:3:4:5})*7+{1;2;3;4;5;6;7}+1)<>МЕСЯЦ(B2);"";ОТБР((B2-ДЕНЬ(B2)-1)/7+{0:1:2:3:4:5})*7+{1;2;3;4;5;6;7}+1)"Он придет и молча исправит все..." (с) БГ
=ЕСЛИ(И(ЕСЛИОШИБКА(ЕСЛИ(ДЕНЬНЕД(ДАТАЗНАЧ(ТЕКСТ(ЕСЛИОШИБКА(ЗНАЧЕН(СМЕЩ($A$7;ЕСЛИ(СТОЛБЕЦ()-СТОЛБЕЦ($B$7)=0;СТРОКА()-СТРОКА($B$7)-1;СТРОКА()-СТРОКА($B$7));ЕСЛИ(СТОЛБЕЦ()-СТОЛБЕЦ($B$7)=0;7;СТОЛБЕЦ()-СТОЛБЕЦ($B$7))));0)+1;0)&"."&ТЕКСТ(МЕСЯЦ(ЛЕВСИМВ(ТЕКСТ($B$5;0);3)&"."&ТЕКСТ($B$1;0));0)&"."&ТЕКСТ($B$1;0));2)=СТОЛБЕЦ()-СТОЛБЕЦ($B$7)+1;ДЕНЬ(ДАТАЗНАЧ(ТЕКСТ(ЕСЛИ(СМЕЩ($A$7;ЕСЛИ(СТОЛБЕЦ()-СТОЛБЕЦ($B$7)=0;СТРОКА()-СТРОКА($B$7)-1;СТРОКА()-СТРОКА($B$7));ЕСЛИ(СТОЛБЕЦ()-СТОЛБЕЦ($B$7)=0;7;СТОЛБЕЦ()-СТОЛБЕЦ($B$7)))="";0;СМЕЩ($A$7;ЕСЛИ(СТОЛБЕЦ()-СТОЛБЕЦ($B$7)=0;СТРОКА()-СТРОКА($B$7)-1;СТРОКА()-СТРОКА($B$7));ЕСЛИ(СТОЛБЕЦ()-СТОЛБЕЦ($B$7)=0;7;СТОЛБЕЦ()-СТОЛБЕЦ($B$7))))+1;0)&"."&ТЕКСТ(МЕСЯЦ(ЛЕВСИМВ(ТЕКСТ($B$5;0);3)&"."&ТЕКСТ($B$1;0));0)&"."&ТЕКСТ($B$1;0)));"";);"";)<8-ДЕНЬНЕД(ДЕНЬНЕД(ДАТАЗНАЧ("01."&ТЕКСТ(МЕСЯЦ(ЛЕВСИМВ(ТЕКСТ($B$5;0);3)&"."&ТЕКСТ($B$1;0));0)&"."&ТЕКСТ($B$1;0));2);2);СТРОКА()-СТРОКА($B$7)>0);"";ЕСЛИОШИБКА(ЕСЛИ(ДЕНЬНЕД(ДАТАЗНАЧ(ТЕКСТ(ЕСЛИОШИБКА(ЗНАЧЕН(СМЕЩ($A$7;ЕСЛИ(СТОЛБЕЦ()-СТОЛБЕЦ($B$7)=0;СТРОКА()-СТРОКА($B$7)-1;СТРОКА()-СТРОКА($B$7));ЕСЛИ(СТОЛБЕЦ()-СТОЛБЕЦ($B$7)=0;7;СТОЛБЕЦ()-СТОЛБЕЦ($B$7))));0)+1;0)&"."&ТЕКСТ(МЕСЯЦ(ЛЕВСИМВ(ТЕКСТ($B$5;0);3)&"."&ТЕКСТ($B$1;0));0)&"."&ТЕКСТ($B$1;0));2)=СТОЛБЕЦ()-1;ДЕНЬ(ДАТАЗНАЧ(ТЕКСТ(ЕСЛИ(СМЕЩ($A$7;ЕСЛИ(СТОЛБЕЦ()-СТОЛБЕЦ($B$7)=0;СТРОКА()-СТРОКА($B$7)-1;СТРОКА()-СТРОКА($B$7));ЕСЛИ(СТОЛБЕЦ()-СТОЛБЕЦ($B$7)=0;7;СТОЛБЕЦ()-СТОЛБЕЦ($B$7)))="";0;СМЕЩ($A$7;ЕСЛИ(СТОЛБЕЦ()-СТОЛБЕЦ($B$7)=0;СТРОКА()-СТРОКА($B$7)-1;СТРОКА()-СТРОКА($B$7));ЕСЛИ(СТОЛБЕЦ()-СТОЛБЕЦ($B$7)=0;7;СТОЛБЕЦ()-СТОЛБЕЦ($B$7))))+1;0)&"."&ТЕКСТ(МЕСЯЦ(ЛЕВСИМВ(ТЕКСТ($B$5;0);3)&"."&ТЕКСТ($B$1;0));0)&"."&ТЕКСТ($B$1;0)));"";);"";))
И вопрос: можно ли создать календарь где в каждой ячейке с датой находилась бы информация т.е таблица excel с проделанной работой или шаблоны с этой таблицей? Для удобства дальнейшего их архивирования и для востоновления сценариев проделанной работы по мере необходимости. Думаю было бы очень удобно в разворачиваемом окне календаря иметь необходимую для работы таблицу.Зарание спасибо!
где А1 - адрес первой выделенной ячейки (без $)
Потом нажать на кнопку Формат и выбрать цвет заливки.
Как сделать подсветку сегодняшней даты? Условное форматирование по =день(сегодня()) и =сегодня() не работает(((
Разобрался! Условное форматирование - правила выделения ячеек - дата
Как сделать календарный график, но так чтобы один день занимал 2 ячейки -до 14.00, после 14.00 (обеденный)..и соответственно не в ручную числа проставлять на месяц. числа на месяц должны идти в строку.В дальнейшем это будет диаграмма ганта с помощью условного форматирования (хотя может я хочу невозможного..) спасибо если кто-нибудь откликнется
Спасибо за ваш труд и помощь в изучении этой программы.
У меня вопрос по условному форматированию этого календаря.
Столбец таблицы содержит набор дат.
02.12.15
03.06.15
и т.д. в любой последовательности.
Каким должно быть условие/формула, чтобы при совпадении даты из столбца с датой в календаре, выполнялось условное форматирование?
С уважением Виктор.
...{0:1:2:3:4:5}*7+{1;2;3;4;5;6;7}-1...Данная формула предназначена только для диапазона, где 6 строк и 7 колонок. Для диапазона с любым другим количеством строк и колонок формулу необходимо переделать. Например если вы хотите, чтобы дни шли не по горизонтали, а по вертикали.
Фрагмент
{0:1:2:3:4:5}{0:1:2:3:4:5}*7Разделитель ":", как вы поняли, размещает соответствующее число в соответствующую строку независимо от столбца, а разделитель ";" размещает соответствующее число в соответствующие колонки независимо от строки.
Результат массива
{1;2;3;4;5;6;7}{0:1:2:3:4:5}*7+{1;2;3;4;5;6;7}В конце из каждой ячейки полученного массива вычитается 1. Этой единичной можно оперировать, если требуется например начать неделю не с Понедельника, а с Воскресенья.
Таким образом, если вам требуется формула для вертикального календаря с 7-мью строками и 6-тью колонками, то фрагмент формулы с загадочными числами будет следующий:
{0:1:2:3:4:5:6}+{0;1;2;3;4;5;6}*7Копирую формулу, выделяю диапазон, нажимаю комбинацию клавиш "Ctrl+Shift+Enter, чтобы ввести эту формулу как формулу массива" - ячейки не заполняются, выделение остаётся. Что не так сделано?
При попытке просто вставить в диапазон формулу, говорит, что данные в буфере не совпадают с выделенной областью
Или может, есть решение этой задачи?
Есть список мероприятий различной длительности (до 5 дней). когда человек выбирает дату начала мероприятия, закрашиваются все 5 дней в календаре - первый (выбранный) и 4 после. Проблема возникает, когда дата начала мероприятия - в конце месяца (правая картинка):
1 случай - всё ок, то есть форматирование с учетом переноса на след месяц отображается нормально
2 случай - не ок, то есть когда в первом месяце есть пустая строка.
Спасибо!!!
но при нажатии Ctrl+Shift+Enter выдает ошибку
использовал формулу =if(month(trunc(B2-Day(B2)-1)/7+{0:1:2:3:4:5})*7+1)<>Month(B2);"";trunc((B2-day(B2)-1)/7+{0:1:2:3:4:5})*7+{1:2:3:4:5:6:7}+1)
[img][/IMG]
Помогите реализовать такой календарик, чтобы можно было после каждого числа вписывать данные
исходная табл
колонка_1 прогрессия по дням
колонка_2 день()
колонка_3 деннед()
колонка_4 номнед()
колонка_5 год()
и построил св табл:
в столбцы поместил поле "ден нед" - получил 7 колонок
в строки 3 поля "год <- номер_недели <- мес"
в поле значений 2-ую колнку "День()" из исходной таблицы
колонку потом "B" можно скрыть
но такой календарь кажется бесполезен
Например вот так:
мне нужен календарь в котором неделя начинается с воскресенья.
как будет выглядеть формула?