Как связать дату в Excel с календарем

Обновлено: 16.05.2024

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

Выбор даты Excel от Microsoft

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

Во-первых, необходимо включить вкладку "Разработчик" в меню ленты в Excel. Для этого щелкните правой кнопкой мыши меню ленты и нажмите «Настроить ленту» во всплывающем меню. Выберите "Основные вкладки" в раскрывающемся меню "Настроить ленту" и установите флажок "Разработчик". Нажмите "ОК", чтобы выйти из меню.

Затем щелкните вкладку "Разработчик" в меню ленты. В разделе "Элементы управления" нажмите "Вставить", а затем нажмите "Дополнительные элементы управления" в разделе "Элементы управления ActiveX". Выберите «Microsoft Date and Time Picker Control 6.0 (SP6)» в меню и нажмите «ОК». Затем щелкните ячейку, в которую вы хотите добавить средство выбора даты. Перейдите на вкладку "Дизайн" в меню ленты и нажмите "Свойства", чтобы настроить различные параметры всплывающего окна выбора даты и ячейки, включая ее размер и цвет.

Затем, завершив размещение и настройку средства выбора даты, перейдите на вкладку "Дизайн" в меню ленты и снимите флажок "Режим конструктора". Когда кто-то щелкнет ячейку с этого момента, появится окно выбора даты, обеспечивающее вставку данных в правильном формате даты.

Сторонние средства выбора даты

Если вы используете 64-разрядные версии Excel, используете Excel Online или по какой-то причине вас не устраивает средство выбора даты Microsoft, вы всегда можете использовать сторонний подключаемый модуль для выбора даты. Вы можете найти их на рынке бизнес-приложений Microsoft AppSource. Найдите тот, который обладает нужными вам функциями, и используйте AppSource, чтобы установить его в свою электронную таблицу.

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

Использование проверки данных в Excel

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

Выберите ячейку или ячейки, в которых вы хотите применить правила проверки данных. Нажмите вкладку "Данные" в меню ленты, затем нажмите "Проверка данных". В разделе "Настройки" выберите тип данных, который вы хотите разрешить. Выберите «Дата», чтобы указать только действительную дату. В разделе "Данные" выберите любые дополнительные ограничения, например разрешить только даты в определенном диапазоне.

Выберите вкладку "Входное сообщение", чтобы настроить сообщение, которое будет отображаться при взаимодействии пользователей с рассматриваемыми ячейками. Нажмите "ОК". Если кто-то попытается ввести в ячейку недопустимые данные, он получит сообщение об ошибке.

Создать календарь в Excel

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

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

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

Office 365 профессиональный плюс переименовывается в Приложения Microsoft 365 для предприятий. Для получения дополнительной информации об этом изменении прочитайте эту запись в блоге.

Обзор

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

Разрешение

Microsoft предоставляет примеры программирования только для иллюстрации, без явных или подразумеваемых гарантий.Это включает, но не ограничивается, подразумеваемые гарантии товарного состояния или пригодности для конкретной цели. В этой статье предполагается, что вы знакомы с демонстрируемым языком программирования и инструментами, которые используются для создания и отладки процедур. Инженеры службы поддержки Майкрософт могут помочь объяснить функциональность конкретной процедуры, но они не будут изменять эти примеры, чтобы обеспечить дополнительную функциональность или создавать процедуры в соответствии с вашими конкретными требованиями.

Чтобы создать календарь, выполните следующие действия.

Майкрософт Эксель 2003

Создайте книгу.
В меню "Инструменты" выберите пункт "Макрос", а затем выберите "Редактор Visual Basic".
В меню "Вставка" выберите "Модуль".
Вставьте сценарий Visual Basic для приложений из раздела «Образец процедуры Visual Basic» на лист модуля.
В меню "Файл" выберите "Закрыть и вернуться в Microsoft Excel".
Выберите вкладку "Лист1".
В меню "Инструменты" выберите "Макрос" и выберите "Макросы".
Выберите CalendarMaker, а затем выберите "Выполнить", чтобы создать календарь.

Microsoft Excel 2007 или более поздней версии

Создайте книгу.
На ленте разработчика выберите Visual Basic.
В меню *Вставка выберите Модуль.
Вставьте сценарий Visual Basic для приложений из раздела «Образец процедуры Visual Basic» на лист модуля.
В меню "Файл" выберите "Закрыть и вернуться в Microsoft Excel".
Выберите вкладку "Лист1".
На ленте *Разработчик нажмите "Макросы".
Выберите CalendarMaker, а затем выберите *Выполнить, чтобы создать календарь.

Если лента разработчика не отображается, откройте параметры Excel, чтобы включить ее. В Excel 2007 этот параметр доступен в меню «Популярное». В Excel 2010 этот параметр доступен в меню "Настройка ленты".

Пример процедуры Visual Basic

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

Выбор даты — это элемент управления, который позволяет пользователям быстро вводить даты в форму Microsoft Office InfoPath, щелкая небольшой всплывающий календарь. Например, вы можете использовать средство выбора даты для сбора дат рождения людей в страховой форме. Вы также можете использовать средство выбора даты для отображения предустановленных дат, например текущей даты.

В этой статье

Когда использовать средство выбора даты

Используйте средство выбора даты, когда хотите:

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

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

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

На следующем рисунке средство выбора даты используется в шаблоне формы запроса на поездку для сбора даты вылета рейса.

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

Взаимодействие с пользователем

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

Вопросы совместимости

При разработке шаблона формы в InfoPath можно выбрать определенный режим совместимости для разработки шаблона формы, совместимого с браузером. Когда шаблон формы, совместимый с браузером, публикуется на сервере, на котором запущены службы InfoPath Forms Services, а затем поддерживает браузер, формы, основанные на шаблоне формы, можно просматривать в веб-браузере. При разработке шаблона формы, совместимого с браузером, некоторые элементы управления недоступны в области задач «Элементы управления», поскольку их нельзя отобразить в веб-браузере.

Несмотря на то, что средства выбора даты доступны при разработке шаблона формы, совместимого с браузером, помните о следующих ограничениях:

Выбор даты полностью поддерживается только в браузерах Windows Internet Explorer. В других веб-браузерах средство выбора даты отображается в виде текстового поля, хотя в текстовом поле поддерживаются параметры форматирования даты.

Текст-заполнитель — это текст, который отображается внутри элемента управления в шаблоне формы. Пример текста-заполнителя: «Нажмите здесь и введите текст». Хотя для шаблонов форм, совместимых с браузером, можно оставить параметры замещающего текста, этот текст не отображается в веб-браузерах.

Вставить средство выбора даты

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

На следующем рисунке показано, как выглядит средство выбора даты, когда оно выбрано в режиме конструктора.

Элементы управления могут быть привязаны или освобождены. Когда элемент управления привязан, он подключается к полю или группе в источнике данных, чтобы данные, введенные в элемент управления, сохранялись в файле базовой формы (XML). Когда элемент управления не привязан, он не связан с полем или группой, и данные, введенные в элемент управления, не сохраняются. Когда вы выбираете или наводите указатель мыши на элемент управления, текст и значок привязки появляются в правом верхнем углу элемента управления. Текст указывает на группу или поле, к которому привязан элемент управления в источнике данных. Значок указывает, правильно ли элемент управления привязан к этой группе или полю. Когда привязка выполнена правильно, появляется зеленый значок. Если с привязкой что-то не так, вместо этого вы увидите синий или красный значок.

Источник данных для шаблона формы состоит из полей и групп, которые отображаются в иерархическом представлении на панели задач "Источник данных". Элементы выбора даты всегда привязаны к полям. В следующем примере средство выбора даты отъезда в шаблоне формы привязано к полю offsetDate в области задач "Источник данных".

Вставьте средство выбора даты в новый пустой шаблон формы

При разработке нового пустого шаблона формы по умолчанию установлен флажок Автоматически создавать источник данных на панели задач Элементы управления. Это позволяет InfoPath автоматически создавать поля и группы в источнике данных при добавлении элементов управления в шаблон формы. Эти поля и группы представлены значками папок и файлов на панели задач "Источник данных".

В шаблоне формы поместите курсор туда, куда вы хотите вставить элемент управления.

Если область задач "Элементы управления" не отображается, нажмите "Дополнительные элементы управления" в меню "Вставка" или нажмите клавиши ALT+I, C.

В разделе "Вставить элементы управления" нажмите "Выбор даты".

Чтобы добавить метку к элементу управления, введите текст над или слева от элемента управления, а затем двоеточие (:).

Чтобы указать способ отображения даты в форме, дважды щелкните средство выбора даты, вставленное в шаблон формы.

Перейдите на вкладку "Данные" и нажмите "Формат".

В списке Отображать дату, как этот, в диалоговом окне Формат даты выберите нужный формат отображения.

Примечание. Формат отображения даты можно изменить, только если тип данных — текст, дата или дата и время.

Вставка средства выбора даты в шаблон формы, основанный на существующем источнике данных

В шаблоне формы поместите курсор туда, куда вы хотите вставить элемент управления.

В разделе "Вставить элементы управления" нажмите "Выбор даты".

В диалоговом окне "Привязка средства выбора даты" выберите поле, в котором вы хотите сохранить данные средства выбора даты, и нажмите "ОК".

InfoPath использует имя поля или группы в качестве метки элемента управления. При необходимости измените текст метки.

На вкладке "Данные" нажмите "Формат".

В списке Отображать дату, как показано ниже, в диалоговом окне Формат даты выберите нужный формат отображения.

Примечание. Формат отображения даты можно изменить, только если тип данных — текст, дата или дата и время.

Совет. Для вставки элементов управления также можно использовать панель задач "Источник данных". На панели задач "Источник данных" щелкните правой кнопкой мыши поле, к которому вы хотите привязать средство выбора даты, а затем выберите "Выбор даты" в контекстном меню.

Отображать текущую дату в окне выбора даты

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

Вставьте средство выбора даты в шаблон формы.

Дважды щелкните средство выбора даты, вставленное в шаблон формы.

Перейдите на вкладку "Данные".

В разделе "Значение по умолчанию" нажмите "Вставить формулу" .

В диалоговом окне "Вставить формулу" нажмите "Вставить функцию".

В списке "Категории" диалогового окна "Вставка функции" нажмите "Дата и время".

В списке «Функции» нажмите «Сегодня», а затем дважды нажмите «ОК», чтобы вернуться в диалоговое окно «Свойства средства выбора даты».

Обратите внимание, что сегодня() отображается в поле "Значение".

В разделе "Значение по умолчанию" снимите флажок Обновлять это значение при пересчете результата формулы.

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

Нажмите "ОК", чтобы вернуться к шаблону формы.

Чтобы протестировать изменения, нажмите "Просмотр" на стандартной панели инструментов или нажмите CTRL+SHIFT+B.

Убедитесь, что дата, отображаемая в окне выбора даты, выглядит так, как вы хотите. Если это не так, вы можете изменить способ отображения даты или времени, нажав кнопку «Формат» в диалоговом окне «Свойства средства выбора даты».

Отображать текущую дату и время в средстве выбора даты

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

Вставьте средство выбора даты в шаблон формы.

Дважды щелкните средство выбора даты, вставленное в шаблон формы.

Перейдите на вкладку "Данные".

В поле Тип данных выберите Дата и время (dateTime).

Нажмите "Формат".

В диалоговом окне "Формат даты и времени" в списке "Отображать время как в этом" выберите нужный параметр и нажмите кнопку "ОК".

В диалоговом окне "Свойства средства выбора даты" в разделе "Значение по умолчанию" нажмите "Вставить формулу" .

В диалоговом окне "Вставить формулу" нажмите "Вставить функцию".

В списке "Категории" диалогового окна "Вставка функции" нажмите "Дата и время".

В списке «Функции» нажмите «Сейчас», а затем дважды нажмите «ОК», чтобы вернуться в диалоговое окно «Свойства средства выбора даты».

Обратите внимание, что now() отображается в поле "Значение".

В разделе "Значение по умолчанию" снимите флажок Обновлять это значение при пересчете результата формулы.

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

Нажмите "ОК", чтобы вернуться к шаблону формы.

Чтобы протестировать изменения, нажмите "Просмотр" на стандартной панели инструментов или нажмите CTRL+SHIFT+B.

Советы по макету

Используйте следующие советы, чтобы улучшить внешний вид, размер и другие аспекты средства выбора даты:

Чтобы изменить размер сразу нескольких средств выбора даты, выберите средства выбора даты, размер которых вы хотите изменить, в меню "Формат" нажмите "Свойства", а затем на вкладке "Размер" внесите необходимые изменения.

Чтобы изменить границу или цвет средства выбора даты, выберите средство выбора даты, в меню "Формат" нажмите "Границы и заливка" и внесите необходимые изменения.

Чтобы настроить текст, отображаемый в средстве выбора даты, используйте поля «Шрифт» и «Размер шрифта» на панели инструментов «Форматирование». Чтобы одновременно изменить шрифт и размер шрифта для всех средств выбора даты в шаблоне формы, щелкните правой кнопкой мыши средство выбора даты, содержащее нужное форматирование, и выберите команду Применить шрифт ко всем элементам управления средством выбора даты в контекстном меню.

Чтобы увидеть, как будет выглядеть окно выбора даты с реальным текстом, нажмите «Образец данных» в меню «Вид». Это поможет вам понять, как будет выглядеть форма, когда ее заполнят пользователи.

Иногда текст внутри средства выбора даты и метка слева от средства выбора даты могут немного смещаться. Чтобы быстро выровнять их, дважды щелкните средство выбора даты, нажмите «Выровнять» на вкладке «Размер», а затем нажмите «Применить». Если метка и средство выбора даты находятся в разных ячейках таблицы макета, щелкните правой кнопкой мыши ячейку, содержащую метку, выберите "Свойства таблицы" в контекстном меню, а затем выберите "По центру" в разделе "Выравнивание по вертикали" на вкладке "Ячейка".

Чтобы изменить способ отображения даты, дважды щелкните средство выбора даты, перейдите на вкладку "Данные" и нажмите кнопку "Формат". Выберите формат отображения в диалоговом окне «Формат даты».

Расположение ссылки Cell имеет решающее значение для остальной части объяснения. Это влияет на формулу DATE и на макрос.

Шаг 2. Еще один раскрывающийся список за год

Создайте список лет в столбце. Это просто значение года, а не даты (посмотрите на данные в столбце AI)
Вставить новый раскрывающийся список
Связать этот новый раскрывающийся список с этим диапазоном лет.
Выберите A2 в качестве Ссылки на ячейку

Шаг 3. Создайте первую дату в зависимости от раскрывающегося списка

Год — это значение ссылки ячейки A2 + 2016 (да, первый год в списке — 2017, поэтому результатом в A2 будет 1, а не 2017)
Месяц — это просто значение A1 (простой вариант)
И день 1 (первый день месяца)

Шаг 4. Продлите дни

В C6 напишите следующую формулу
Скопируйте формулу в ячейку AF6.

Шаг 5. Измените формат даты

Шаг 6. Измените ориентацию текста

Шаг 7. Добавьте цвет и границы

Шаг 8. Добавьте динамический заголовок

="Период от "&ТЕКСТ(ДАТА(A2+2016,A1,1),"дд мммм гггг") &" до "&ТЕКСТ(ДАТА(A2+2016,A1+1,1)-1, "дд мммм гггг")

Шаг 9. Выделите выходные

Выберите все данные в первой строке календаря (диапазон B6:AF13).
Создайте правило условного форматирования (Главная>Условное форматирование>Новые правила)
Выберите Использовать формулу для определения форматируемых ячеек.
Запишите формулу =ДЕНЬНЕД(6,2 руб.)>5
Измените цвет фона (в данном примере оранжевый)

Очень, очень, ОЧЕНЬ ВАЖНО ❗❗❗ В формуле после столбца B стоит только один $

Шаг 10. Выделите праздничные дни

Выберите все свои данные (ячейки B6:AF13).
Создайте правило условного форматирования (Главная>Условное форматирование>Новые правила)
Выберите Использовать формулу для определения форматируемых ячеек.
Запишите формулу =СЧЁТЕСЛИ(Праздники!$B$2:$B$4,B$6)>0
Изменить цвет фона (красный)

Шаг 11. Скройте последние столбцы с помощью макроса

ВСЕ ПОЯСНЕНИЯ МАКРОКОДА ЗДЕСЬ

Нажмите Alt + F11, чтобы открыть редактор Visual Basic.
В меню "Вставка" выберите "Модуль".
Скопируйте и вставьте следующий код в свой модуль

Шаг 12. Свяжите макрос с раскрывающимися объектами

Шаг 13. Ваш календарь готов

Сводная таблица — Презентация

Ипотечный калькулятор в Excel

Фредерик ЛЕ ГЕН

Функция ДВССЫЛ

Пример даты, недели, промежутка в днях

Как отображать часы больше 24

78 комментариев

Спасибо за урок. Я смог создать календарь, но хотел бы, чтобы в каждой ячейке был раскрывающийся список со списком вариантов, из которых сотрудники могли выбирать. Если я выполняю проверку данных и создаю список для ячеек в календаре, он работает. Однако, если я ввожу выбор из списка на 10 февраля, он сохраняет этот выбор на 10 число каждого месяца. Как я могу сделать так, чтобы мой список в ячейках календаря не повторялся в другие месяцы? Я не эксперт в Excel и не знаю, как разобраться в этом без посторонней помощи.

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

Sub Hide_Day()
Dim Num_Col As Long

Num_Col = 365 'Количество столбцов, необходимых для всех месяцев года

For Num_Col = 2 To 365 ' Я предполагаю, что календарь будет начинаться с B. Вам понадобится столбец для имен рабочих

If Month(Cells(6, Num_Col)) = Cells(1, 1)
Columns(Num_Col).Hidden = False
Else
Columns(Num_Col).Hidden = True Конец, если
Следующий
Конец подпункта

Читайте также: