Календарно-сетевой график в месяцах в MS EXCEL
Создадим в MS EXCEL простейший календарно-сетевой график с шагом равным 1 месяц, затем сделаем связи между отдельными работами (тип связи Конец-Начало), потом введем запаздывание, отобразим % выполнения и наконец — прогноз завершения работы в случае ее отклонения от плана.
Под «календарно-сетевым графиком» будем понимать график работ проекта, состоящий из 2-х частей:
- календарный план работ проекта (перечень работ с датами начала, окончания и связями между работами)
- соответствующая плану диаграмма Ганта.
Совет: В данной статье диаграмма выполнена с помощью Условного форматирования (далее УФ). Для тех, кто мало знаком с этим инструментом MS EXCEL рекомендуется прочитать вот эту статью про УФ.
Примечание: в другой статье про диаграмму Ганта данная диаграмма построена с помощью инструмента Диаграмма.
Простейший график
Построим график именно в месяцах, т.к. в EXCEL он получается очень компактным и проще соотнести длительность работы (в месяцах) с ее началом и концом (не требуется дополнительных вычислений, как например для недель, когда в разных месяцах укладывается разное количество недель). Конечно, такая детализация не очень удобна для коротких проектов, но, чтобы укрупненно продемонстрировать прогресс проектов (с длительностью от 1 года), такой подход вполне будет приемлемым.
Для построения простейшего графика, изображенного выше, нам потребуются следующие исходные данные:
- Длительность каждой работы
- Начало каждой работы (связи сделаем позже)
Месяц окончания каждой работы будем вычислять с помощью формулы =C14+D14-1 (к началу прибавим длительность) и скорректируем на 1, т.к. в начальный месяц работа также выполняется.
Чтобы чуть украсить график, можно для разных работ задать разные цвета. Для этого потребуется создать столько правил УФ, сколько разных цветов у нас будет на графике. Формула также чуть усложнится, также добавится столбец с кодом цвета.
Текущий месяц также можно отразить на графике с помощью УФ. На картинке выше текущий месяц выделен штриховкой — это июль 2022 года.
График со связями работ
Простейший график строить легко, но он не очень полезен. Чаще всего работы связаны между собой. Смоделируем связь Конец-Начало.
Добавим столбец с кодами работ и столбец для указания предшественника. Также в столбце, где указано начало работы вставим формулу:
Первая часть формулы просматривает столбец Предшественник («предшеств.», столбец С) и если находит пустую ячейку (нет предшественника, т.е. первая работа проекта), то устанавливает начало этой работы равной месяцу начала проекта (задается вручную в отдельной ячейке С6). Таких начальных работ может быть несколько.
Вторая часть формулы ИНДЕКС($F$14:$F$19;ПОИСКПОЗ(C14;$A$14:$A$19;0))+1 ищет в столбце А код предшественника, указанный в столбце С. После нахождения нужной строки, берет срок конца работы-предшественника (столбец F) и подставляет его в начало работы (столбец Е).
Примечание: Срок конца работы-предшественника (столбец F) вычисляется с помощью опять же столбца Е (также добавлен 1 месяц, чтобы работа-последователь начиналась без пересечения, т.е. с последующего месяца, как показано на диаграмме выше).
Обратите внимание, что циклической ссылки не возникает, хотя формально мы используем значения столбца Е (начало работы), чтобы вычислять опять же начало, но уже другой работы. Все дело в том, что это разные ячейки. Например, для вычисления начала работы с кодом 20 (ячейка Е15) мы использовали значение начала работы с кодом 10, которое находится в ячейке Е14. Главное — не делать перекрестных ссылок, например, когда предшественником работы 20 является работа 30, а у работы 30 предшественником является работа 20 (даже опосредованно, через другие работы).
График со связями работ и запаздыванием
Сделаем следующий шаг — введем запаздывание, т.е. работа-последователь теперь сможет начаться не сразу после окончания предшественника, а с некой заданной нами задержкой.
На рисунке выше показано как выглядит задержка 2 месяца работы 20, относительно работы 10. Формула, с помощью которой это реализовано, почти полностью совпадает с предыдущей формулой, которую мы использовали для вычисления начала работы-последователя
Теперь в столбце G у нас введена величина запаздывания, которая может быть отрицательной величиной. В этом случае работа-последователь начнет выполняться еще до завершения работы-предшественника.
График со связями работ, запаздыванием и прогрессом выполнения работ
Планирование работ — это половина дела, необходимо еще и отслеживать выполнение планов. Прогресс отслеживания завершения работы не сложно реализовать в MS EXCEL с помощью Условного форматирования.
Сначала, чтобы не перегружать количеством правил УФ, избавимся от кодов цветов — сделаем представление планов работ одним (голубым) цветом. Выполненные работы будем отмечать темно-синим цветом. Всего у нас будет 2 правила УФ: один для плана, другой для факта выполненных работ.
Нам особо ничего не придется переделывать на листе — столбец с кодом цвета теперь будет использоваться для хранения прогресса выполненных работ в месяцах. Фактически мы будем указывать сколько месяцев из общей длительности работы нужно закрасить другим цветом.
Формула в правиле УФ очевидна = И(I$11>=$E14;I$11<=$E14+$H14-1)
Закрашиваться будут только те ячейки (месяцы), которые уже начались (>=E14) у данной работы И выполнены (<=E14+H14-1).
Примечание: Конечно, выполнение работы корректнее отслеживать с помощью метода освоенного объема, но и данный упрощенный подход позволяет оценить прогресс выполнения работы.
Если значение выполнения работы больше чем ее длительность, то это означает, что имело место увеличение плановой длительности.
Процент выполнения работ
Иногда указывать прогресс выполнения задачи удобнее в процентах от общего объема работ (в нашем случае — от ее общей длительности). Этот вариант также реализован в файле примера на другом листе.
Если раньше на диаграмме ганта у нас были пустые ячейки, то теперь у нас введена довольно длинная формула:
Она может вернуть 4 разных значения:
- -1: ячейка (соответствующий месяц) лежит вне периода выполнения работы, закрашивается белым;
- 0: работа в этом месяце еще не выполнена, ячейка закрашивается салатовым цветом;
- от 1 до 99: работа в этом месяце выполнена частично, ячейка закрашивается гистограммой пропорционально значению (50 соответствует половине закрашенной ячейки);
- 100: работа в этом месяце выполнена полностью, ячейка полностью синим цветом.
Для выделения текущей даты используется вертикальная линия, ее нужно двигать вручную.
График со связями работ, запаздыванием, прогрессом выполнения работ и прогнозом окончания
Работы не всегда выполняются четко по плану, чаще фактическая длительность больше, чем плановая. По мере выполнения работы зачастую становится понятно насколько ее окончание сдвинется. Смоделируем эту ситуацию в нашем графике, добавив еще один столбец — Прогноз. В этом столбце будем указывать сколько еще месяцев дополнительно к плановой длительности нам потребуется, чтобы завершить весь объем работы.
Пусть изначально плановый график выглядел так. Предполагалось, что длительность первой работы (самая верхняя строка) скорее составит 7 месяцев, а последующая работа начнется сразу после ее окончания.
После 8 месяцев выполнения проекта стало понятно, что в плановый срок первую работу выполнить не удалось. Необходимо обновить планы, но при этом нужно сохранить изначальную информацию о длительности работы.
Как видно из рисунка, длительность первой работы (самая верхняя строка) скорее составит теперь не 7 месяцев как планировалось, а 9 (+2 месяца указано в столбце «прогн», т.е. прогноз). Эти дополнительные 2 месяца выделены темно-оранжевым цветом. После добавления этих двух месяцев изменятся сроки начала связанных работ. Теперь последующая работа должна начаться с 11 месяца, а не с 9.
Прогноз реализован с помощью еще одного правила УФ:
График можно усложнять и дальше: добавлять новые типы связей, учитывать ресурсы/ финансы, использовать более точный расчет процента выполнения и др. Если такая потребность появляется на практике, то это как раз то самое время, когда необходимо воспользоваться специализированной программой.
А мы далее рассмотрим создание графика с детализацией по дням, он больше подходит для более коротких проектов.
Как в экселе сделать график работы на месяц
Я уже делился с уважаемыми читателями своими наработками по поводу табеля рабочего времени. А сегодня я предлагаю вашему вниманию график планирования посменной работы отдела. Он будет полезен для структур, которые работают в режиме 24 x 7 или около того, и вынуждены работать сменами. Это всяческие службы поддержки, кол-центры и т.п.
Ко мне обратилась моя бывшая коллега с просьбой помочь усовершенствовать тот график планирования посменной работы, который достался ей в наследство. Он не предусматривал никакой автоматизации, всё считалось чуть ли не пальцем по экрану, а процесс планирования занимал массу времени. Я согласился помочь, а поскольку шаблон вышел вроде бы удачным, то решил его выложить на всеобщее обозрение.
Шаблон сделан на основе уже упоминаемого табеля рабочего времени, унаследовав все его положительные свойства. Он является хорошей иллюстрацией к более-менее зрелому использованию условного форматирования, и, хотя я не собираюсь подробно описывать, что и как там устроено, но готов ответить на все ваши вопросы по его устройству, если таковые появятся.
Файл для скачивания
Версия 1.01 (описываемая в статье)
Версия 1.03 (незначительно усовершенствованная)
Функционал
Встроенный универсальный календарь.
Вам не придётся думать сколько дней в феврале 2016 года, уточнять с какого дня недели начинается месяц и тому подобное. Всё, что вы должны сделать, чтобы получить правильную заготовку нужного вам месяца, это указать год и месяц. Субботы и воскресенья выделяются серым, что позволяет вам легко ориентироваться в днях недели. 
Выделение праздников.
Шаблон отметит красным фоном все праздники, которые вы перечислите на листе Настройки. 
Выбор смены из справочника.
Вид смены выбирается строго из списка, что избавляет вас от ошибок ввода. 
Смены описаны в Настройках в специальной таблице. Можно удалить лишние и добавить свои. 
Смены автоматически выделяются цветом.
Поскольку всё форматирование реализовано через условное форматирование, то есть оно не хранится непосредственно в ячейках, то это очень удобно, так как ячейки можно копировать и удалять, на заботясь, что остатнутся следы от предыдущей смены.
Подсчёт смен и часов по человеку.
Автоматически подсчитывается по каждому человеку отработанное количество смен и количество рабочих часов.
Учёт рабочего времени за день.
За каждый день подсчитывается количество отработанных смен, часов, количество больных сотрудников, сотрудников в отпусках и сотрудников, находящихся во внеочередных выходных (если сотрудник работает 2 дня по 11 часов, то вы ему обязаны дать 2 выходных дня непосредственно после этих рабочих смен).
Подсчёт количества сотрудников по часам суток.
Тем, кто обеспечивает работу в режиме 24 х 7 важно знать сколько сотрудников будет работать в каждый конкретный час, чтобы балансировать нагрузку на отдел и уровень сервиса. Известно, например, что максимальное количество звонков поступит с 10 утра до 17 вечера и надо, например, иметь 5 специалистов, чтобы справиться с этой нагрузкой, а с 19:00 до 7:00 утра хватит одного дежурного специалиста. Для этого предназначена панель подсчёта сверху шаблона. Она отображает ситуацию того дня, в столбце которого вы стоите. Кстати, нулевые значения выделяются, чтобы бросались в глаза.
Два внеочередных выходных.
Как мы уже упоминали, если сотрудник отработал 2 смены подряд по 11 часов, то вы по закону должны предоставить ему 2 выходных подряд сразу после этих двух смен. В шаблон встроен автоматический механизм контроля этой ситуации. Если вы не предоставили сотруднику 2 выходных после таких смен, то фамилия сотрудника подсвечивается красным. Единственная проблема возникает на границе месяца, но я с ней ничего не могу поделать.
Как построить график в Excel по данным таблицы
PDF шпаргалка с ТОП горячих клавиш в Excel Получить
При работе в Excel табличных данных часто не достаточно для наглядности информации. Для повышения информативности ваших данных советуем использовать графики и диаграммы в Эксель. В этой статье рассмотрим пример того, как построить график в Excel по данным таблицы.
Отправьте файл с с примером графика, построенного по данным таблицы себе на почту ��
Какой-то текст ошибки
Какой-то текст ошибки
Спасибо, файл успешно отправлен на указанный адрес почты. Проверьте папки «Рассылки», «Реклама» или «Спам».
Одно или несколько полей содержат ошибку. Пожалуйста проверьте и попробуйте снова.
При отправке запроса произошла ошибка. Пожалуйста, попробуйте позже.
Видеоурок
Как построить простой график в Excel
Представим, что у нас есть таблица с ежемесячными данными среднего курса Доллара в течении года:
На основе этих данных нам нужно нарисовать график. Для этого нам потребуется:
- Выделить данные таблицы, включая даты и курсы валют левой клавишей мыши:
- На панели инструментов перейти во вкладку «Вставка» и в разделе «Диаграммы» выбрать «График»:
- Во всплывающем окне выбрать подходящий стиль графика. В нашем случае, мы выбираем график с маркерами:
- Система построила нам график:
Как построить график в Excel на основе данных таблицы с двумя осями
Представим, что у нас есть данные не только курса Доллара, но и Евро, которые мы хотим уместить на одном графике:
Для добавления данных курса Евро на наш график необходимо сделать следующее:
- Выделить созданный нами график в Excel левой клавишей мыши и перейти на вкладку «Конструктор» на панели инструментов и нажать «Выбрать данные»:
- Изменить диапазон данных для созданного графика. Вы можете поменять значения в ручную или выделить область ячеек зажав левую клавишу мыши:
- Готово. График для курсов валют Евро и Доллара построен:
Если вы хотите отразить данные графика в разных форматах по двум осям X и Y, то для этого нужно:
- Перейти в раздел «Конструктор» на панели инструментов и выбрать пункт «Изменить тип диаграммы»:
- Перейти в раздел «Комбинированная» и для каждой оси в разделе «Тип диаграммы» выбрать подходящий тип отображения данных:
- Нажать «ОК»
Ниже мы рассмотрим как улучшить информативность полученных графиков.
Как добавить название в график Эксель
На примерах выше мы строили графики курсов Доллара и Евро, без заголовка сложно понять про что он и к чему относится. Чтобы решить эту проблему нам нужно:
- Нажать на графике левой клавишей мыши;
- Нажать на «зеленый крестик» в правом верхнем углу графика;
- Во всплывающем окне поставить галочку напротив пункта «Название диаграммы»:
- Над графиком появится поле с названием графика. Кликните по нему левой клавишей мыши и внесите свое название:
Как подписать оси в графике Excel
Для лучше информативности нашего графика в Excel есть возможность подписать оси. Для этого:
- Щелкните левой клавишей мыши по графику. В правом верхнем углу графика появится «зеленый крестик», нажав на который раскроются настройки элементов диаграммы:
- Щелкните левой клавишей мыши на пункте «Названия осей». На графике под каждой осью появятся заголовки, в которые вы можете внести свой текст:
Как добавить подписи данных на графике Excel
Ваш график может стать еще более информативным с помощью подписи отображаемых данных.
На примере курсов валют мы хотим отобразить на графике стоимость курса Доллара и Евро помесячно. Для этого нам потребуется:
- Щелкнуть правой кнопкой мыши по линии графика, на которую хотим добавить данные. В раскрывающемся меню выбрать пункт «Добавить подписи данных»:
Система отобразила курс Доллара на линии графика, но наглядность данных это не улучшило, так как значения сливаются с графиком. Для настройки отображения подписи данных потребуется сделать следующие шаги:
- Кликните правой кнопкой мыши на любом значении линии графика. В всплывающем окне выберите пункт «Формат подписей данных»:
В этом меню вы сможете настроить положение подписи, а также из чего будет состоять подпись данных: из имени ряда, категории, значения и.т.д.
Помимо настроек расположения, в том же меню вы сможете настроить размер подписей, эффекты, заливку и.т.д:
Настроив все параметры у нас получился такой график курса валют:
Как в экселе сделать график работы на месяц
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |