График выполнения работ на диаграмме Ганта в Excel

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

Вид графика до автоматизации:

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

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

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

Неувязка №3: расстановка формул. Потому что формулы нужно вставлять в зависимости от уровня вложенности пт приходится их расставлять и инспектировать для всего графика вручную, при всем этом в графике могут быть 10-ки, а то и сотки строк.

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

Как мы решали задачку

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

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

Интересно почитать: Как в excel проставить нумерацию строк

А изменяя шаг графика можно избрать уровень детализации графика. Перечень вариантов шага графика: месяц, полмесяца, третья часть месяца, четверть месяца и денек. Но, при желании можно избрать полностью всякую длину шага.

Многоуровневая нумерация

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

Расстановка формул

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

К автоматом выставляемым формулам относятся:

Формулы дат. Для пт, у каких есть подуровни в столбцы «Начала работ» и «Окончания работ» вставляются формулы, которые гарантируют, что уровень иерархии будет включать все временные интервалы подуровня. К примеру, если мы на уровне 2 перенесем дату окончания работ на срок, который лежит позднее даты окончания работ верхнего уровня, то дата верхнего уровня автоматом поменяться на новейшую, чтоб включить в себя новейший временной просвет. Это касается всех уровней. Другими словами если мы изменим дату на 4 уровне, то по мере необходимости даты поменяются на 3, 2 и 1 уровнях. Это сберегает время и уменьшает количество ошибок.
Продолжительность в деньках. Рассчитывается как разность меж датами окончания и начала работ. В принципе здесь нет ничего такого особенного, но тем не наименее сберегает время и смотрится лаконичней, чем протягивание формул с припасом.
Отставание в деньках. Формула, учитывающая сроки работ, дату проверки и процент готовности. Итог отображается в деньках. При всем этом отставания имеет красноватый цвет, а опережение зеленоватый. Что комфортно при беглом зрительном анализе. Так же формула учитывает такие случаи как нулевая готовность при дате проверке до начала работ, в таком случае в отставание будет указан 0. То же самое касается и варианта 100% готовности при дате проверке опосля даты окончания работ.

Интересно почитать: Excel высота строки в мм

Комфортное зрительное представление дат проверки

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

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

Как в эксель строить графики

Задали мне здесь не так давно вопросец. Есть график, на котором имеем 3 показателя работы производства: удельный расход материала, норматив расхода и объём производства, а по горизонтальной оси временной период (недельки, к примеру).

Начальные данные смотрятся так:

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

А это наша конечная цель:

Скачать пример

ChartsIntersection.xlsx

Решение

Шаг 1

Для начала обратим внимание, что начальный тип диаграммы — График с маркерами .

Ряды этого типа описываются так:

Заметьте, что здесь нет значений координат по оси X, а есть лишь координаты по условной оси Y. Сиим предполагается, что условная ось X — общая для всех рядов, также, что при их построении не употребляются значения, хорошие от заблаговременно фиксированного ряда оси X. Вот есть у нас периоды: 1, 2, 3 и т.д., нанесенные на ось Х, и применять можно лишь их. Периода, где X=3.5 не существует для данного типа диаграммы.

Интересно почитать: Пузырьковая диаграмма в excel как построить

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

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

Шаг 2

Сейчас нам нужно нанести на диаграмму доп ряды данных:

Точки пересечения линий расхода материала с нормами расхода. Таковых точек у нас будет 3, исходя из критерий примера. Эти точки нам нужно будет расчитать, вспомнив школьный курс геометрии (см. лист Треугольники ). В файле учебного примера на листе Опосля в таблице N3:Q5 мы получили координаты подходящих точек методом нехитрых формул. Тип диаграммы Точечная в отличие от рядов с показателями работы производства.

Точки пересечения отысканных значений по оси X с объемом производства. Расчитывается в таблице N8:Q10 по близким формулам. Тип диаграммы Точечная .

Пунктирные полосы, которые мы рисуем из эстетических суждений. Таблица с их координатами находится в N12:P18 .

Добавляем ряды данных в согласовании с теми точками, которые мы расчитали. Щёлкните ПКМ по диаграмме и изберите » Избрать данные. «, дальше воспользуйтесь клавишей » Добавить «.