Как в кабинете

Вставить календарь в ячейку Excel

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

Я отыскал этот пример на веб-сайте Chandoo.org и делюсь им с вами.

Интерактивный календарь в Excel

На выходе у нас обязано получиться что-то вроде этого:

Создаем таблицу с событиями

На листе Расчеты создаем таблицу со всеми событиями

Настраиваем календарь

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

Задаем имя спектру дат в календаре

Это просто, выделяем весь спектр дат нашего календаря и в поле Имя задаем «Календарь»

Определяем ячейку с выделенной датой

На листе Расчеты избираем пустую ячейку и задаем ей имя «ВыделеннаяЯчейка». Мы будем применять ее для определения даты, которую избрал юзер. В нашем случае, это ячейка G3.

Добавляем макрос на событие Worksheet_selectionchange()

Описанный ниже код поможет идентифицировать, когда юзер избрал ячейку в спектре «Календарь». Добавьте этот код на лист с календарем. Для этого открываем редактор VBA, нажатием Alt+F11. Копируем код ниже и вставляем его Лист1.

Настраиваем формулы для отображения деталей при выбирании даты

Изменение даты на календаре ведет за собой изменение 4-х характеристик отображения в анонсе: заглавие, дата, пространство и описание. Зная, что дата находится в ячейке «ВыделеннаяЯчейка», воспользуемся формулами ВПР, ЕСЛИ и ЕСЛИОШИБКА для определения этих характеристик. Логика формул последующая: если на избранную дату существует событие, возвращает данные этого действия, по другому возвращает пустую ячейку. Формулы с определением характеристик действия находятся на листе Расчеты, в ячейках G10:G13.

Добавление анонса

В конце концов добавляем в лист Календарь 4 элемента Надпись ипривязываем их к данным, находящихся в ячейках G10:G13 листа Расчеты.

Совет: для того, чтоб привязать значение ячейки к элементу Надпись, просто выделите элемент, в строке формул наберите G10 и щелкните Enter.

Настраиваем условное форматирование для выделенной даты

В конце концов, добавьте условное форматирование, чтоб выделить даты с событиями в календаре.

Изберите спектр дат в календаре

Перебегаем на вкладке Основная в группу Стили —> Условное форматирование —> Сделать правило

В диалоговом окне Создание правила форматирования, изберите тип правила Применять формулу для определения форматируемых ячеек.

Задаем правила выделения как на рисунке

Форматируем

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

Календарь в Excel – как сделать с примерами

Microsoft Office Excel можно применять не только лишь как калькулятор либо хранилище баз данных, да и как планировщик заданий и календарь. Благодаря разным способностям форматирования таблицы можно сделать блок с цветными ячейками и автоматическим вычислением данных формул. Сейчас сделаем календарь в excel, используя обычные функции и инструменты форматирования.

Обычный календарь

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

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

На заметку! Чтоб резвее заполнить ячейки, используйте маркер автозаполнения.

Заполняете деньки, начиная с первого января. В итоге выходит таковая таблица:

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

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

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

Производственный календарь

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

1. Создаете название с помощью сочетания функций ГОД и СЕГОДНЯ, которые возвращают текущий год.

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

1. Форматируете ячейку так, чтоб отображалось лишь заглавие месяца.

1. Используйте еще одну формулу ДАТАМЕС, которая добавляет обозначенное число месяцев к текущей дате. Подобные деяния производите для других заготовок.

1. Для резвого прибавления чисел вовнутрь таблицы выделяете спектр в границах границ и в строчку формул вставляете последующее:

Принципиально! Для внедрения формулы нажимаете Ctrl+Shift+Enter. В этом случае программка автоматом посчитает значения для выделенного спектра.

1. Итог работы функции с применением формата ячейки в виде 1-го числа.

1. Копируете спектр в другие заготовки.

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

1. В поле Применяется к выделяете спектры всякого месяца, удерживая кнопку Ctrl.

1. Чтоб обозначить текущую дату, задаете очередное правило форматирования с применением функции СЕГОДНЯ для всего рабочего листа.

1. Для того, чтоб учитывать торжественные деньки, нужно сделать для их отдельную умную таблицу и создать на нее ссылку.

1. Создаете правило форматирования и применяете ко всем месяцам.

На заметку! При изменении таблицы с праздничками таблица с числами автоматом отформатируется.

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

Платежный календарь

Таковой вид календаря употребляется в бухгалтерии и отражает приходно-расходные операции по счету с выводом остатков на начало и конец определенного периода времени. Сделаем собственный платежный календарь на недельку с помощью интегрированных функций редактора.

1. Делаете заготовку, заполняя строчку заголовков и большой столбец.

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

1. Для расчета остатка на конец периода нужно к остатку на начало прибавить приход и отнять расход по каждой фирме.

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

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

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

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

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

При выборке данных из таблицы Excel по условию с указанием начала и конца учетного периода времени нередко не хватает визуализации на интерактивном календаре. Обычно на дашбордах юзеру предоставляется только 2 поля ввода где необходимо указать исходную и конечную дату подборки данных из таблицы для следующего анализа. Но сейчас разглядим увлекательный пример для комфортной работы при зрительном анализе данных подборки с датами учетных периодов.

Пример дашборда с интерактивным календарем в Excel

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

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

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

Важней момент! В аргументах формул массива находятся ссылки на именные спектры Excel с формулами и некие из их в собственных формулах ссылаются на остальные имена. Чтоб поглядеть весь перечень применяемых имен изберите инструмент: «ФОРМУЛЫ»-«Определенные имена»-«Диспетчер имен» (CTRL+F3):

Сам интерактивный календарь управляется при помощи частей управления формами Excel. Они все доступны из инвентаря: «РАЗРАБОТЧИК»-«Элементы управления»-«Вставить».

Разглядим 3 элемента управления интерактивным календарем на дашборде:

1. 1-ый элемент «поле со перечнем» дозволяет указывать на календаре число месяца на начало учетного периода, от которого будет начинаться подборка. В этом случае это седьмое мая, соответственно с этого числа будет начинаться учетный период времени для подборки данных из таблицы на листе «Data»:
2. 2-ой отран управления – «Счетчик». Его мы используем для указания длительности учетного периода. К примеру, 11 дней. При использовании счетчика автоматом задействуется условное форматирование для подсветки избранного спектра промежутка времени. В этом случае – это с 7-го по 17-ое число мая месяца. Таковая визуализация увеличивает уровень юзабильности (удобства использования) календаря.
3. В качестве третьего элемента управления также употребляется счетчик, но уже с целью выбора календарного месяца.

Чтоб на примере показать как применять интерактивный календарь в действии на схематический дашборд лупили добавлены еще 2 графика и 1-а диаграмма.

Интерактивная визуализация данных в Excel без макросов

1-ый график заполняется автоматом данными о доходах и расходах в зависимости от подборки под управлением спектра чисел на интерактивном календаре:

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

В нижнем левом углу размещена диаграмма распределения толикой остатков продуктов в 3-х магазинах и одном складе компании:

Все значения зависят от подборки данных из таблицы по условию дат учетных периодов, которые задаются при помощи календаря.

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

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

Скачать шаблон примера с интерактивным календарем в Excel

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

Используя данный шаблон интерактивного календаря, вы сможете добавлять его на свои дашборды просто выделив на листе DASHBOARD спектр ячеек L2:T9, потом скопировать CTRL+C и вставить CTRL+V в собственный шаблон дашборда. Настроить будет нужно лишь ссылки на ячейки в формуле массива и в свойствах частей управления. А именные спектры скопируются вкупе со своими прописанными внутренними формулами – автоматом (для проверки наличия имен, нажимайте CTRL+F3). Календарь будет всеполноценно работать на вашем дашборде.