Как сделать график в Экселе, чтобы в шкале времени отражались и месяцы (кварталы), и годы?

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

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

Выделите всю эту таблицу, нажмите F11, и диаграмма будет создана на отдельном листе.

Остается настроить ее вид и при необходимости перенести на имеющийся лист, например на лист с таблицей.

Как сделать еженедельный график в Excel вместе с ежедневным

Argument ‘Topic id’ is null or empty

© Николай Павлов, Planetaexcel, 2006-2021
info@planetaexcel.ru

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

За изображения спасибо Depositphotos.com

ИП Павлов Николай Владимирович
ИНН 633015842586
ОГРН 310633031600071

График с накоплением в Excel.

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

График с накоплением в Excel

Выбираем нужный нам тип диаграммы — График с накоплением.

График с накоплением в Excel

Нажимаем и получаем График с накоплением.

График с накоплением в Excel

На графике отразились две кривые линии. Оранжевая кривая, это продажи Магазина №2. Синяя кривая, это продажи магазина №1. Для наглядности добавим Метки данных. Вызовем настройки диаграммы нажав на нее. В настройках выберем Элементы диаграммы. Поставим галочку напротив пункта Метки данных. На нашем графики отразиться количество проданных стульев соответственно месяцу года и магазину.

Пример

Вертикальная ось диаграммы показывает количество проданных стульев. При этом оранжевая кривая на графике, которая соответствует продажам Магазина №2, берет свое начало не на отметке 260 (шт.). Это количество проданных стульев данным магазинов в январе. Оранжевая кривая берет сове начало на отметке 510 (шт.). Это суммарное количество проданных стульев двумя магазинами в январе (250 шт. +.260 шт.). То есть, для оранжевой кривой, при определении координаты по вертикальной оси, начало отсчета происходит не от нулевой отметки, а от соответствующей (нижележащей) точки синей кривой.

Построим на основе исходной таблицы простой График.

Обычный

Как видно, кривые продаж Магазина №1 и Магазина №2 наложились одна на одну. Проводить анализ или презентацию используя такой график не целесообразно. График с накоплением позволяет избежать данной проблемы.

10 шаблонов Excel, которые будут полезны в повседневной жизни

10 шаблонов Excel, которые будут полезны в повседневной жизни

Отрадно, что корпорация Microsoft всеми силами идёт нам навстречу, предоставляя абсолютно бесплатный доступ к своей офисной продукции для мобильных устройств. Поэтому вы можете вполне удобно использовать мощный инструментарий Excel на своём рослом iPhone, на iPad и на Android-гаджетах с крупной диагональю.

В Telegram-канале «Лайфхакер» только лучшие тексты о технологиях, отношениях, спорте, кино и многом другом. Подписывайтесь!

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

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

1. Список задач

Даже самая светлая голова при ясном уме и твёрдой памяти однажды даст сбой, и вы что-то забудете. Хорошо, если это будет покупка корма для рыбок, поздравление тёщи с Днём матери или поливка фиалки вашей второй половинки. Они побулькают, пошипят и поквакают, а ваша совесть останется чистой. Но вдруг вы не оплатите предмет первой необходимости — интернет? Вам будет стыдно смотреть на себя в зеркало. И в тот злополучный день вы сломаетесь и пообещаете составлять списки дел. А пока вы будете мучиться выбором среди электронных планировщиков, попробуйте начать с простого перечня задач.

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

2. Бюджет путешествия

Теоретически даже самое нереальное (и при этом полноценное) путешествие может обойтись вообще без финансовых вложений с вашей стороны. Для этого необходимо заручиться поддержкой на краудфандинговой площадке, найти бесплатные места для посещения и немного поработать за еду и койко-место. Такие успехи сопутствуют лишь прожжённым путешественникам с изрядной долей везения. Хотя чего таить, даже им приходится искать пару монет на звонок, чтобы отчитаться маме о своём здравии. Поэтому любое перемещение за границы места проживания сопровождается предварительным планированием и составлением сметы. И дабы вам не пришлось марать листки бумаги и без конца крутить-вертеть цифры, предлагаем обратиться за помощью к калькулятору поездки.

Как рассчитать расходы на путешествие в Excel

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

3. Инвентарный список

Что объединяет пожар, потоп, кражу и приезд родни погостить «на недельку»? Правильно, высокая вероятность утраты целостности вашего имущества.

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

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

4. Список контактов

Как бы ни старался технический прогресс, но он не в силах победить «динозавров», не желающих знать о существовании удобных инструментов организации ваших контактов. Дневники, записные книжки и просто клочки бумаги — их всё. Обычно в таких случаях говорят, что горбатого могила исправит (привет, жена!). Но не будем опускать руки и найдём компромиссный вариант — блокнот.

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

5. Диаграмма Ганта

Добрая русская традиция ударить по рукам, прогулять аванс, расслабиться и выполнить работу в ночь перед днём отчётности — довольно рискованная бизнес-техника, существенно влияющая на качество. Только планирование, разбиение задач на этапы и твёрдое следование графику способно спасти вашу репутацию.

Диаграмма Ганта — это популярный тип столбчатых диаграмм (гистограмм), который используется для иллюстрации плана, графика работ по какому-либо проекту.

Разумеется, мощности Excel позволяют создавать эти самые диаграммы. Их конёк — наглядность и доступность.

Шаблон Excel диаграммы Ганта

И даже если у вас нет собственного дела, попробуйте спланировать ремонт в квартире, подготовку к поступлению или марафону по методике Ганта. Вы оцените силу инструмента.

6. Семейное древо

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

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

7. График дежурств

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

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

8. Журнал техобслуживания

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

Читайте также:  Пример как сделать простой дашборд в Excel скачать шаблон

9. Журнал пробега

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

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

Как рассчитать расход топлива на километр пути в Excel

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

10. Ежедневник

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

Макросы. Изучаем редактор VBE. Академия Excel

Давайте разбираться где происходит написание макросов? В Excel существует встроенный редактор Visual Basic Editor (VBE), в котором собственно и происходит создание макросов с нуля, редактирование существующий, удаление ненужных.

Посмотрев урок, Вы научитесь настраивать редактор под себя, познакомитесь с новым понятием Модуль (узнаете основные операции с ними).

01:05Как попасть в редактор VBE

01:41Kак вернуться к Excel

02:32Настройка внешнего вида редактора

04:24Перемещение окон редактора

06:20Окно проекта. Знакомство с модулями.

07:26Создание модулей

08:04Удаление модулей

09:28Переименование модуля

11:27Форматирование кода макроса

Для этого в первую очередь потребуется создать каркас для будущей таблицы с осями:

  • X – дни;
  • Y – сотрудники.

Шаблон для графика работ в Excel

После заполняем смены для каждого сотрудника.

Заполненный график работы в Excel

Готово, далее требуется визуально заполнить его.

Как сделать еженедельный график в Excel вместе с ежедневным

Внешний вид (скрин-сейв) графика смен

График смен скачать

График работы сотрудников, как и образец графика работы в любом режиме (два-два, три-один, пять-два) составляется с учетом продолжительности рабочей смены, планируемых выходных, соблюдения ТК РФ, бюджета часов на всех сотрудников, утвержденного администрацией.

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

Предложенная форма-образец графика работы сотрудников разработана на основе графика работы магазина (торговой точки). Форма-образец графика автоматически строит графики работ сотрудников с учетом указанных критериев:

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

Суммарное количество рабочих часов соответствует требованиям Трудового кодекса.

Файлы содержат программу (макрос) для автоматических расчетов. При открытии файла включайте макросы!

Как быстро создавать ежедневные / еженедельные / ежемесячные отчеты в одном формате в Excel?

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

док создать еженедельный отчет 1

Создавайте ежедневные / еженедельные / ежемесячные отчеты в том же форматехорошая идея3

Создавайте ежедневные / еженедельные / ежемесячные отчеты в том же формате

В Excel, за исключением функции «Переместить» или «Копировать» для копирования листа по одному вручную, я не могу найти простой способ решить эту проблему. Но если у вас есть Kutools for Excel, Его Создать рабочие листы последовательностей Утилита, вы можете быстро создать серию листов на основе заданного формата листа.

После установки Kutools for Excel, сделайте следующее: (Бесплатная загрузка Kutools for Excel прямо сейчас!)

1. Создайте новый лист в книге и укажите нужный формат листа, затем щелкните Kutools Plus > Рабочий лист > Создать рабочие листы последовательностей. Смотрите скриншот:
док создать еженедельный отчет 2

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

3. Нажмите Ok, еженедельные отчеты созданы в новой книге, все они имеют одинаковый формат.
док создать еженедельный отчет 1

Чаевые: Если вы хотите создать ежедневный отчет, вам просто нужно нажать док добавитьчтобы добавить новый ежедневный список в настраиваемый список. Смотрите скриншот:

Как быстро и легко заполнить дату по неделям в Excel?

В Excel дата автозаполнения обычно должна использоваться в нашей повседневной работе, но в этом случае я расскажу о том, как заполнять даты по неделям. Другими словами, я определяю дату начала как 3 (вторник) и заполняю ее в ячейке A1, и мне нужно заполнить 2016/1/3 (вторник) в ячейке A8, 2016/2/2016 (вторник) в ячейке A3,… и т. д.

Дата автозаполнения еженедельно / ежемесячно / ежегодно с Kutools for Excel хорошая идея3

стрелка синий правый пузырь Дата автозаполнения еженедельно формулой

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

1. Выберите ячейку и введите дату начала. В нашем случае мы набираем 3/1/2016 в ячейке A1. Смотрите скриншот:
дата заполнения документа еженедельно 1

2. Затем в следующей ячейке A2 введите эту формулу = A1 + 7 , и нажмите Enter ключ, чтобы получить второе свидание. Смотрите скриншот:
дата заполнения документа еженедельно 2

И теперь вы можете перетащить дескриптор автозаполнения ячейки A2 вниз, чтобы заполнять даты еженедельно по мере необходимости. Смотрите скриншот:
дата заполнения документа еженедельно 3

стрелка синий правый пузырь Дата автозаполнения еженедельно / ежемесячно / ежегодно с Kutools for Excel

Если у вас есть Kutools for Excel, С его Вставить порядковый номер Утилита, вы можете не только заполнять дату еженедельно, вы также можете указать дату окончания при заполнении ячеек.

После установки Kutools for Excel, сделайте следующее: (Бесплатная загрузка Kutools for Excel прямо сейчас!)

1. Введите дату начала в ячейку, возьмите, например, A1, затем выберите эту дату и щелкните Главная вкладку и перейдите, чтобы указать эту дату как Общие форматирование из списка в Число группы на Главная таб. Смотрите скриншот:

дата заполнения документа еженедельно 4
стрелка вниз
дата заполнения документа еженедельно 5

2. Затем нажмите Kutools > Вставить > Вставить порядковый номер. Смотрите скриншот:
дата заполнения документа еженедельно 6

3. В появившемся диалоговом окне щелкните Создать кнопку, чтобы создать новую последовательность. Смотрите скриншот:
дата заполнения документа еженедельно 7

4. Затем введите дату начала (числовую серию) в поле Стартовый номер текстовое поле и введите 7 в текстовое поле Increment и 5 в Кол-во цифр текстовое окно. Смотрите скриншот:
дата заполнения документа еженедельно 8

5. Нажмите Добавить чтобы завершить создание новой последовательности, затем выберите диапазон ячеек, которые вы хотите заполнить по неделям, и нажмите Диапазон заполнения. Смотрите скриншот:
дата заполнения документа еженедельно 9

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

6. Не снимая выделения с дат, нажмите Главная > Поле числового формата > Краткая дата для форматирования число как формат даты.

дата заполнения документа еженедельно 10дата заполнения документа еженедельно 11

Наконечник:

1. Со вставкой порядкового номера, вы указываете дату окончания при вставке дат. Например, я указываю дату окончания 6/18/2016 , который может отображаться как 42539 . И когда вы вставляете даты, они заканчиваются на 6/18/2016 .

дата заполнения документа еженедельно 12

дата заполнения документа еженедельно 13

дата заполнения документа еженедельно 14

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

Как сделать еженедельный график в Excel вместе с ежедневным

Предположим, у вас есть таблица почасовых записей, как показано на скриншоте ниже, но вам нужно рассчитать среднесуточные значения из почасовых записей, как решить эту проблему в Excel? Kutools for Excel’s To Actual утилита и Advanced Combine Rows утилита также могут вам помочь.

Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now

1. Выберите ячейки времени, по которым будет выполняться усреднение, и щелкните значок Главная > Формат номера коробка> Короткое свидание чтобы изменить дату и время только на даты.

Внимание: Для расчета среднего по неделям / месяцу / годам вы можете изменить обычную дату и время на день недель / месяцев / лет только с помощью Kutools for Excel’s Применить форматирование даты функции и К фактическому функцию. Полнофункциональная бесплатная 30-дневная пробная версия!
объявление применить форматирование даты 5

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

2. Выберите диапазон данных и щелкните значок Kutools > Content > Расширенные ряды комбинирования.
в среднем документов в день 03

3. В открывшемся диалоговом окне «Объединить строки на основе столбца» выберите столбец «Дата / время» в списке столбцов и щелкните значок Основной ключ, выберите столбец Сумма и нажмите кнопку Рассчитать > Средняя, наконец, нажмите Ok кнопка. Смотрите скриншот:
в среднем документов в день 04

Затем сразу рассчитываются среднесуточные значения.

Внимание: Этот метод также позволяет рассчитывать среднемесячное / годовое среднее значение, если на первом этапе вы можете отображать даты в виде месяцев или лет. Кстати, Kutools > Формат > Применить форматирование даты может отображать дату как день недели, месяц, год или другое указанное форматирование даты, а затем изменять даты на месяцы / годы с помощью Kutools > К фактическому.

Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now

Расписания с перерывами

9. Еженедельное или раз в две недели расписание с перерывами (Excel)

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

табель учета рабочего времени часов еженедельно раз в две недели с перерывами Excel

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

10. Еженедельное расписание с перерывами на обед (Excel)

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

табель учета рабочего времени часов в неделю с перерывами на обед

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

Как создать диаграмму в excel когда есть список заявок?

Это список времени в которое были заявки на сайте. Каждая строка — 1 заявка. Строки идут в хронологическом порядке.

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

  • Вопрос задан более трёх лет назад
  • 259 просмотров
  • Facebook
  • Вконтакте
  • Twitter
  • Google

NeiroNx

Разбить день на 24 часа и считать количество в каждом часу. По простому — добавить 24 колонки с формулой если к каждому заказу, нумерацию колонок. =ЕСЛИ(И($A1>=ВРЕМЯ(B$8;0;0);$A1<=ВРЕМЯ(B$8;59;59));1;0)

в ячейке A1 время первого заказа, в ячейке B8 — номер часа — вначале формулу скопировать по горизонтали на все 24 колонки, потом повертикали на все заказы
Если время в интервале то 1 иначе 0 а потом посчитать сумму единичек для каждого часа.

  • Facebook
  • Вконтакте
  • Twitter
  • Google

Вариант 1: Ответ Александр
Вариант 2 (дает большую возможность для аналитики, например с разбивкой по числам или дням недели): сделать сводную таблицу, а по ней сводную диаграмму.
Ниже пример сводной таблицы.
3adb6cbfeee9401e91f5f57cdae503d6.JPG

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

Для создания графика такой формы я использовал встроенный в MS EXCEL конструктор диаграмм и тип диаграммы «Линейчатая с накоплением».

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

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

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

На самом деле диаграмма отражает не только периоды отпусков, но и разрывы между ними (настройками выполнено отображение отпусков зеленым цветом заливки, а разрывов — без заливки то есть прозрачными).

Первый ряд

Это отображаемый прозрачным период от начала времен до даты начала первого отпуска в году. Используется значение в столбце «Начало1».

Второй ряд

Это отображаемый зеленым первый отпуск. Используется значение в столбце «Дней 1» — продолжительность первого периода отпуска:

У меня столбец «Дней 1» рассчитывается по формуле:

Плюс один день потому, что дата окончания отпуска — это последний его день, а не первый рабочий.

Третий ряд

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

Также считается формулой, а так как данной значение не имеет пользовательской ценности — столбец в таблице максимально сужен.

Последующие ряды

Тут хочется просто сказать «ну и так далее. », в общем зеленые периоды отпусков строятся аналогично ряду 2, а прозрачные промежутки между ними — аналогично ряду 3. Для моей задачи хватило 5-ти периодов — это текущее ограничение шаблона, которое можно преодолеть, продолжая таблицу в ширину (насколько у вас хватит терпения).

Как составить скользящий график работы (образец)

Составим график сменности сторожей (образец на 4 человек, работающих «3 + 1» (3 рабочих дня — 1 выходной).

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

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

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

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

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

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

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

  • в бухгалтерских расчетах (зарплата, стаж, отпуска. )
  • в логистике — для корректного определения сроков доставки с учётом выходных и праздников (помните классическое «давай уже после праздников?»)
  • в управлении проектами — для правильной оценки сроков с учётом, опять же, рабочих-нерабочих дней
  • при любом использовании функций типа РАБДЕНЬ (WORKDAY) или ЧИСТРАБДНИ (NETWORKDAYS) , т.к. они требуют в качестве аргумента список праздников
  • при использовании функций Time Intelligence (типа TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR и т.д.) в Power Pivot и Power BI
  • . и т.д. и т.п. — примеров масса.

Тем, кто работает в корпоративных ERP-системах типа 1С или SAP — проще, в них производственный календарь встроен. Но что делать пользователям Excel?

Можно, конечно, вести такой календарь вручную. Но тогда придется как минимум раз в год (а то и чаще, как в «весёлом» 2020 году) его актуализировать, аккуратно внося туда все выходные, переносы и нерабочие дни, придуманные нашим правительством. А потом повторять эту процедуру каждый следующий год. Скукота.

А как насчёт чуть напрячься и сделать «вечный» производственный календарь в Excel? Такой, чтобы сам обновлялся, брал данные из интернета и формировал всегда актуальный список нерабочих дней для последующего использования в любых расчетах? Заманчиво?

Сделать такое, на самом деле, совсем не сложно.

Источник данных

Главный вопрос — а где брать данные? В поисках подходящего источника я перебрал несколько вариантов:

  • Оригинальные указы публикуются на сайте правительства в формате PDF (вот, один из них, например) и отпадают сразу — полезную информацию из них не вытащить.
  • Заманчивым вариантом, на первый взгляд, казался «Портал открытых данных РФ», где есть соответствующий набор данных, но, при ближайшем изучении всё оказалось печально. Для импорта в Excel сайт жутко неудобен, техподдержка не отвечает (самоизолировались?), да и сами данные там давно устарели — производственный календарь на 2020 год обновлялся последний раз в ноябре 2019 (позорище!) и, само-собой, не содержит наших «коронавирусных» и «голосовательных» выходных 2020 года, например.
Читайте также:  Быстрые изменения в прайс-листах перед печатью

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

И вот в процессе поисков случайно обнаружилось прекрасное — сайт http://xmlcalendar.ru/

Сайт xmlcalendar.ru

Без лишних «рюшечек», простой, легкий и быстрый сайт, заточенный под одну задачу — отдавать всем желающим производственный календарь за нужный год в XML-формате. Отлично!

Если, вдруг, вы не в курсе, то XML — это текстовый формат с разметкой содержимого специальными <тегами>. Легкий, удобный и читаемый большинством современных программ, включая Excel.

Я, на всякий случай, связался с авторами сайта и они подтвердили, что сайт существует уже 7 лет, данные на нем постоянно актуализируются (у них для этого даже ветка на github есть) и закрывать его они не собираются. И совершенно не против, чтобы мы с вами с него грузили данные для любых наших проектов и расчётов в Excel. Бесплатно. Как же приятно, что есть еще такие люди! Респектище!

Осталось загрузить эти данные в Excel с помощью надстройки Power Query (для версий Excel 2010-2013 её можно бесплатно скачать с сайта Microsoft, а в версиях Excel 2016 и новее — она уже встроена по умолчанию).

Логика действий будет такая:

  1. Делаем запрос для загрузки данных с сайта за один любой год
  2. Превращаем наш запрос в функцию
  3. Применяем эту функцию к списку всех имеющихся лет, начиная с 2013 и до текущего года — и получаем «вечный» производственный календарь с автоматическим обновлением. Вуаля!

Шаг 1. Импортируем календарь за один год

Сначала загрузим производственный календарь за один любой год, например, за 2020. Для этого в Excel идём на вкладку Данные (или Power Query, если вы установили её как отдельную надстройку) и выбираем Из интернета (From Web) . В открывшееся окно вставляем ссылку на соответствующий год, скопированную с сайта:

Грузим данные с сайта через Power Query

После нажатия на ОК появляется окно предварительного просмотра, в котором нужно нажать кнопку Преобразовать данные (Transform data) или Изменить данные (Edit data) и мы попадем в окно редактора запросов Power Query, где и продолжим работу с данными:

Окно Power Query

Сразу же можно смело удалить в правой панели Параметры запроса (Query settings) шаг Измененный тип (Changed Type) — он нам не нужен.

Таблица в столбце holidays содержит коды и описания нерабочих дней — увидеть её содержимое можно, дважды «провалившись» в неё щелчком мыши по зелёному слову Table :

Проваливаемся в таблицу праздников

Для возврата назад придется удалить в правой панели все появившиеся шаги обратно до Источника (Source) .

Вторая таблица, куда можно зайти аналогичным образом, содержит как раз то, что нам нужно — даты всех нерабочих дней:

Таблица выходных

Останется эту табличку обработать, а именно:

1 . Отфильтровать только даты праздников (т.е. единички) по второму столбцу Attribute:t

Удаляем лишние столбцы

2 . Удалить все столбцы, кроме первого — щелчком правой кнопкой мыши по заголовку первого столбца и выбором команды Удалить другие столбцы (Remove Other Columns) :

Удаляем лишние столбцы

3 . Разделить первый столбец по точке отдельно на месяц и день с помощью команды Разделить столбец — По разделителю на вкладке Преобразование (Transform — Split column — By delimiter) :

Делим по точке на месяц и день

4 . И, наконец, создать вычисляемый столбец с нормальными датами. Для этого на вкладке Добавление столбца жмём на кнопку Настраиваемый столбец (Add Column — Custom Column) и вводим в появившееся окно следующую формулу:

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

=#date(2020, [#»Attribute:d.1″], [#»Attribute:d.2″])

Здесь у оператора #date три аргумента: год, месяц и день, соответственно. После нажатия на ОК получаем требуемый столбец с нормальными датами выходных, а остальные столбцы удаляем как в п.2

Готовый список нерабочих дней

Шаг 2. Превращаем запрос в функцию

Следующая наша задача — преобразовать созданный на 2020 год запрос в универсальную функцию для любого года (номер года будет её аргументом). Для этого делаем следующее:

1 . Разворачиваем (если ещё не развернута) панель Запросы (Queries) слева в окне Power Query:

Панель запросов

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

Щелкнув еще раз правой по получившейся копии calendar(2) выберем команду Переименовать (Rename) и введём новое имя — пусть будет, например, fxYear:

Копируем исходный запрос

3 . Открываем исходный код запроса на внутреннем языке Power Query (он лаконично назвается «М») с помощью команды Расширенный редактор на вкладке Просмотр (View — Advanced Editor) и вносим туда небольшие правки для превращения нашего запроса в функцию на любой год.

Было

Стало

Если интересны подробности, то здесь:

  • (year as number)=> — объявляем, что в нашей функции будет один числовой аргумент — переменная year
  • Подклеиваем переменную year в веб-ссылку на шаге Источник. Поскольку Power Query не позволяет склеивать числа и текст, то на лету преобразовываем номер года в текст с помощью функции Number.ToText
  • Подставляем переменную year вместо 2020 года на предпоследнем шаге #»Добавлен пользовательский объект«, где мы формировали дату из фрагментов.

После нажатия на Готово наш запрос превращается в функцию:

Запрос превратился в функцию

Шаг 3. Импортируем календари за все года

Осталась самая малость — сделать последний главный запрос, который будет закачивать данные за все имеющиеся года и складывать все полученные даты праздников в одну таблицу. Для этого:

1 . Щёлкаем в левой панели запросов в серое пустое место правой кнопкой мыши и выбираем последовательно Новый запрос — Другие источники — Пустой запрос (New Query — From other sources — Blank query) :

Создаем пустой запрос

2 . Нам нужно сформировать список всех лет, для которых мы будем запрашивать календари, т.е. 2013, 2014 . 2020. Для этого в строке формул появившегося пустого запроса вводим команду:

Формируем список лет

. в Power Query формирует список целых чисел от А до Б. Например, выражение

. выдало бы список 1,2,3,4,5.

Ну, а чтобы не привязываться жёстко к 2020 году, мы используем функцию DateTime.LocalNow() — аналог экселевской функции СЕГОДНЯ (TODAY) в Power Query — и извлекаем из неё, в свою очередь, текущий год функцией Date.Year.

3 . Полученный набор лет хоть и выглядит вполне адекватно, но для Power Query представляет собой не таблицу, а особый объект — список (List) . Но преобразовать его в таблицу — не проблема: достаточно нажать кнопку В таблицу (To Table) в левом верхнем углу:

Преобразуем список в таблицу

4 . Финишная прямая! Применяем созданную нами ранее функцию fxYear к полученному списку лет. Для этого на вкладке Добавление столбца жмём кнопку Вызвать настраиваемую функцию (Add Column — Invoke Custom Function) и задаём её единственный аргумент — колонку Column1 с годами:

Вызываем функцию fxYear

После нажатия на ОК наша функция fxYear по очереди отработает импорт для каждого года и мы получим столбец, где в каждой ячейке будет содержаться таблица с датами нерабочих дней (содержимое таблицы хорошо видно, если щёлкнуть мышью в фон ячейки рядом со словом Table):

Вложенные таблицы с данными по каждому году

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

Разворачиваем вложенные таблицы

. и после нажатия на ОК мы получим то, что хотели — список всех праздников с 2013 и до текущего года:

Все загруженные даты праздников за все годы

Первый, ненужный уже столбец, можно удалить, а для второго задать тип данных Дата (Date) в выпадающем списке в шапке столбца:

Задаем тип данных даты

Сам запрос можно переименовать как-нибудь более осмысленно, чем Запрос1 и выгрузить затем результаты на лист в виде динамической «умной» таблицы с помощью команды Закрыть и загрузить на вкладке Главная (Home — Close & Load) :

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

Обновлять созданный календарь в будущем можно щелчком правой кнопки мыши по таблице или запросу в правой панели через команду Обновить. Или же использовать кнопку Обновить все на вкладке Данные (Date — Refresh All) или сочетание клавиш Ctrl + Alt + F5 .

Ссылка на основную публикацию