Реклама ООО Таймвэб
Реклама ООО Таймвэб
Реклама ООО Таймвэб

Как создать динамический календарь в Google Таблицах

5 комментариев
Как создать динамический календарь в Google Таблицах

Некоторые записи в Google Таблицах обязательно сопровождаются указанием месяца, числа и дня недели. Конечно, все это можно расставлять в ячейках вручную, однако с каждым месяцем данные придется менять или создавать новую таблицу. Это неудобно, особенно если учитывать тот факт, что сами Таблицы были созданы для упрощения взаимодействия со значениями.

Поэтому в рамках данной статьи я разберу метод создания динамического календаря, числа в котором будут автоматически меняться в соответствии с текущим месяцем. К тому же мы настроим подсветку выходных дней. 

Шаг 1: Создание листа с данными

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

  1. Откройте пустую таблицу, где хотите разработать динамический календарь. Нажмите кнопку с плюсом, чтобы добавить новый лист.Создание нового листа для создания динамического календаря в Google Таблицах

  2. Задайте для него любое название, чтобы в будущем не запутаться с предназначением листа.Название для нового листа для создания динамического календаря в Google Таблицах

  3. По порядку в первом столбце самостоятельно напишите список всех двенадцати месяцев.Список месяцев для создания динамического календаря в Google Таблицах

  4. После этого выберите первую ячейку напротив «Январь», щелкните по ней правой кнопкой мыши и выберите пункт «Настроить проверку данных».Переход к проверке данных для создания динамического календаря в Google Таблицах

  5. Из списка «Правила» выберите пункт «Дата».Проверка данных по дням для создания динамического календаря в Google Таблицах

  6. После этого можно сохранить изменения.Сохранение проверки данных по дням для создания динамического календаря в Google Таблицах

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

  8. Теперь в клетке отображается первое число первого месяца текущего года.Проверка первого числа месяца для создания динамического календаря в Google Таблицах

  9. Активируйте ячейку напротив «Февраль» и введите формулу =КОНМЕСЯЦА(B1;0)+1, после чего нажмите Enter. Эта формула вычисляет последний день месяца и прибавляет к нему единицу, что позволяет в самой клетке отображать первое число нового месяца. Выполнение данного процесса может показаться непонятным, однако это самый простой метод настроить ячейку для дальнейшего растягивания, чтобы не использовать проверку данных каждый раз.Формула для первого числа месяца для создания динамического календаря в Google Таблицах

  10. Теперь, начиная с первого дня февраля, растяните формулу до последнего месяца.Растягивание формулы первого числа месяца для создания динамического календаря в Google Таблицах

  11. Вы видите, что числа остаются первыми, меняются только месяцы. Если дважды кликнуть по любой ячейке, откроется календарь и дополнительно покажется и текущий день недели.Список первых чисел месяцев для создания динамического календаря в Google Таблицах

Это был весь подготовительный этап со вспомогательным листом. Теперь можете переключиться на основной и приступать к следующему шагу, в котором описан главный процесс создания динамического календаря.

Комьюнити теперь в Телеграм
Подпишитесь и будьте в курсе последних IT-новостей
Подписаться

Шаг 2: Заполнение таблицы

Сейчас разберемся с заполнением таблицы, в которой и будет присутствовать динамический календарь. Настроим список месяцев и сделаем так, чтобы при выборе одного из них числа и дни недели в ячейках автоматически менялись на актуальные. Красивое оформление пропустим, поскольку с этим этапом вы сможете разобраться и самостоятельно.

  1. Выберите первую ячейку, на листе, которая будет служить списком с месяцами. Щелкните по ней правой кнопкой мыши и в контекстном меню нажмите кнопку «Настроить проверку данных».Создание проверки данных для месяцев для создания динамического календаря в Google Таблицах

  2. В поле «Укажите диапазон или» нажмите по значку с ячейками.Переход к выбору данных проверки для создания динамического календаря в Google Таблицах

  3. Перейдите на вспомогательный лист и выделите все клетки с месяцами, формируя тем самым список.Выбор списка месяцев для создания динамического календаря в Google Таблицах

  4. Сохраните изменения, кликнув по соответствующей кнопке.Сохранение списка с проверкой данных для создания динамического календаря в Google Таблицах

  5. Теперь, разворачивая список в клетке, вы видите перечень всех месяцев, расположенных на вспомогательном листе.Выбор месяца из списка для создания динамического календаря в Google Таблицах

  6. Активируйте первую ячейку строки, с которой должен начинаться отсчет дней. Введите в ней формулу =FILTER('НазваниеВспомогательногоЛиста'!B:B; 'НазваниеВспомогательногоЛиста'!'A:A=A1'). Таким образом, вы создаете фильтр, чтобы при выборе месяца автоматически подставлялся его первый день в данную клетку. Если список и названия на вспомогательном листе у вас находятся в столбцах под другими буквами, поменяйте значения в формулах под себя.Формула для первого дня месяца для создания динамического календаря в Google Таблицах

  7. Измените из выпадающего списка месяц, чтобы убедиться в корректной динамической дате.Проверка первого дня месяца для создания динамического календаря в Google Таблицах

  8. В клетке следующего дня введите =B1+1, где B1 – та самая клетка с функцией фильтра. Так вы добавите один день к текущему, получив следующий.Функция для следующих дней месяца для создания динамического календаря в Google Таблицах

  9. Растяните формулу до конца таблицы (на 31 день), достигнув тем самым отображения абсолютно всех чисел месяца. Соответственно, при изменении самого месяца даты тоже будут меняться на соответствующие, как это было показано ранее.Растягивание функции со днями месяца для создания динамического календаря в Google Таблицах

Последняя задача данного этапа – сделать так, чтобы формат ячеек соответствовал вашим требованиям. Разберем это на простом примере, чтобы вместо 01.01.2022 отображался формат Суббота, 1 (и так далее по всем дням).

  1. Выделите ячейку с первым днем месяца, откройте меню «Формат» и выберите пункт «Пользовательские дата и время».Переход к формату даты для создания динамического календаря в Google Таблицах

  2. Удалите текущие значения, кликнув по каждому из них левой кнопкой мыши.Удалениен текущего формата даты для создания динамического календаря в Google Таблицах

  3. При помощи вариантов снизу отыщите подходящие значения, которые бы включали день недели и число. Их можно поменять местами или редактировать, чтобы полные названия поменялись на сокращенные.Настройка формата даты для создания динамического календаря в Google Таблицах

  4. Примените изменения и вернитесь к таблице. Как видно, настройки вступили в силу и дни недели отображаются в более понятном формате.Новый формат даты для создания динамического календаря в Google Таблицах

Шаг 3: Настройка подсветки выходных

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

  1. Выберите строки ниже, где будете вводить свои данные. У меня это отчеты по дням для каждого сотрудника. Обязательно убедитесь в том, что выделение коснулось всех дней месяца. После этого сделайте клик ПКМ и выберите «Условное форматирование».Переход к условному форматированию для создания динамического календаря в Google Таблицах

  2. Из списка укажите условие форматирования «Ваша формула».Выбор типа условного форматирования для создания динамического календаря в Google Таблицах

  3. Введите =or(weekday(B$1)=7;). B$1 замените на то название клетки, в которой содержится первый день месяца.Первая часть формулы форматирования для создания динамического календаря в Google Таблицах

  4. После точки с запятой идет вторая часть формулы weekday(B$1)=1, а в целом строка должна выглядеть как =or(weekday(B$1)=7;weekday(B$1)=1). Таким образом, условное форматирование будет активно только в том случае, если день недели суббота или воскресенье.Вторая часть формулы форматирования для создания динамического календаря в Google Таблицах

  5. Цвет подсветки ячейки можете выбрать любой, после чего сохраните изменения.Выбор цвета подсветки дней для создания динамического календаря в Google Таблицах

  6. Теперь обратите внимание на то, что клетки с выходными днями начали подсвечиваться выбранным цветом. Измените месяц и проверьте динамическое форматирование, которое тоже должно работать корректно.Подсветка выходных дней для создания динамического календаря в Google Таблицах

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

Наши постоянные авторы и читатели делятся лайфхаками, основанными на личном опыте. Полная свобода самовыражения.

Комментарии

Vasya Pyatkin +1
19 сен в 2023
А как убрать, ведь в некоторых месяцах отображается всё те же 31 день. Допустим выбираю февраль и вместо 28, там 31, дополните как исправить эту ситуацию, чтобы ячейки лишний автоматом стирались, если количество дней в месяце менее 31. LET и SEQUENCE (https://infoinspired.com/google-docs/spreadsheet/populate-a-full-months-dates-based-on-a-drop-down/#How_to_Populate_a_Full_Month%E2%80%99s_Dates_Based_on_a_Drop-Down_in_Google_Sheets_)
на западном сайте применяют, но у меня по их способу выдают ошибку на этапе (DATE(A1,MONTH(A2&1),1)) где они слово месяца преобразуют в цифры, а по вашему способу норм получается. Таблица у меня тоже плоская как у них на две строки горизонтальные сверху числа календарные, а далее название дней недели коротки пн.вт. итд Еще как-то быстро менять год тоже выпадающим списком, ваш способ нужно улучшить, но сам я не соображу))
Свернуть ответы
Eli +1
25 сен в 2023
В ячейку после 28 числа (то есть где должны быть числа 29,30,31) я поставила такую формулу:
=ЕСЛИ(КОНМЕСЯЦА(AE3;0)=AE3;"";ЕСЛИ(AE3="";""; ЕСЛИ(КОНМЕСЯЦА(AE3;0)<>AE3;AE3+1)))
Вместо "АЕ3" поставьте свою ячейку. Объясню как работает:
Если в предыдущей ячейке конец месяц=концу тек. месяцу, то "пусто"; иначе если предыдущая ячейка="пусто", то "пусто"; иначе если предыдущая ячейка не равно концу тек. месяца, то значение в пред. ячейки +1 (то есть если это январь, то после 28 пойдет 29, после 29 пойдет 30 и т.д.).
Надеюсь, понятно объяснила :)
Дмитро Козак 0
19 фев в 21:12
Тогда календарь продолжает обозначать пустые ячейки календарного месяца цветом "типа выходные". Можно ли это исправить?
Inna Shatalovaip 0
03 окт в 2023
Добрый вечер! Спасибо огромное за такой классный календарь! Очень помогает в работе. Два дня мучаюсь, пытаюсь настроить вручную идентичный в Excel. Не соглашается с формулой FILTER. Нет ли у вас настроек подобного календаря в Excel? Прошу прощения за наглость)
Helen Korol' 0
12 июня в 18:58
А как сделать, чтобы данные в ячейках с отчетами по дням для каждого сотрудника менялись, в зависимости от месяца? Например, мы заполнили данные за январь, а когда переключились на февраль, ячейки с отчетаи опустели. Но чтобы данные за январь сохранились. То есть чтобы можно было переключаться между ними
С помощью соцсетей
У меня нет аккаунта Зарегистрироваться
С помощью соцсетей
У меня уже есть аккаунт Войти
Инструкции по восстановлению пароля высланы на Ваш адрес электронной почты.
Пожалуйста, укажите email вашего аккаунта
Ваш баланс 10 ТК
1 ТК = 1 ₽
О том, как заработать и потратить Таймкарму, читайте в этой статье
Чтобы потратить Таймкарму, зарегистрируйтесь на нашем сайте