Диаграмма Ганта в Excel

Опубликовано 27 Янв 2015
Рубрика: Справочник Excel | Один комментарий



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

...для управления проектами (MS Project, Primavera, Gantt Project), а можно сделать в MS Excel или OOo Calc, причем практически любой степени сложности.

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

Диаграмма Ганта плана выполнения заказа.

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

Принятые в статье сокращения:

КБ – конструкторское бюро

ПДО – планово-диспетчерский отдел

ОС – отдел снабжения

ПУ – производственный участок

УПиО – участок покраски и отгрузки

Завод по производству строительных металлоконструкций 12.01.2015 готов подписать договор о поставке 63 тонн балок и стоек компании ООО «ЮГ». Начаться отгрузка готовой продукции должна 04.02.2015. Окончание отгрузки должно произойти не позднее 20.02.2015.

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

Конечный результат, который ждет нас в конце работы, представлен ниже на рисунке, где показаны таблица исходных данных и диаграмма Ганта в Excel в момент времени, когда заказ уже находится в стадии выполнения. (В частности, КБ должно через 1 день — 23.01.15 — завершить разработку всех чертежей по этому заказу.)

Диаграмма Ганта в Excel плана выполнения заказа

Ставим мысленно себя на место начальника ПДО и, запустив программу MS Excel, начинаем движение к представленному результату.

1. Формальные общие данные:

1. Номер заказа пишем

в объединенную ячейку C2D2E2: 5001

2. Наименование заказчика —

в C3D3E3: ООО «ЮГ»

3. Тип металлоконструкций —

в C4D4E4: балки, стойки

4. Массу металлоконструкций в заказе в тоннах заносим

в C5D5E5: 63,000

5. Формулу, выводящую текущую дату записываем

в C6D6E6: =СЕГОДНЯ() = 22.01.15

/Числовой формат ячейки – «Дата», тип – «14.03.01»/

2. Исходные данные:

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

1. День начала разработки чертежей в КБ вводим

в ячейку D8: 15.01.15

/Числовой формат ячейки – «Дата», тип – «14.03.01»/

Планируем, что КБ приступит к работе по этому заказу 15.01.2015.

2. Продолжительность разработки всех чертежей в КБ по этому заказу вписываем

в D9: 8

Планируем, что разработка полного комплекта чертежей займет 8 суток.

3. Количество суток от даты начала разработки чертежей в КБ до дня выдачи первых чертежей из КБ на ПУ прописываем

в D10: 5

Полагаем, что конструкторы КБ разработают первые чертежи, а сотрудники ПДО их размножат и выдадут на ПУ через 5 дней после начала работы над заказом.

4. Интервал времени от даты завершения разработки всего заказа в КБ до дня окончания выдачи всех чертежей на ПУ вводим

в D11: 1

Выделяем 1 день на печать и комплектацию копий чертежей.

5. Время от дня старта разработки чертежей в КБ до даты поступления первых материалов на завод пишем

в D12: 5

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

6. Продолжительность поставки всех материалов (металлопрокат, метизы, комплектующие) записываем

в D13: 8

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

7. Промежуток времени от дня выдачи первых чертежей на ПУ до даты начала изготовления заготовок заносим

в D14: 4

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

8. Общую продолжительность изготовления всех изделий (металлоконструкций) по заказу на ПУ вводим

в D15: 23

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



9. Интервал времени от даты старта изготовления заготовок на ПУ до даты сдачи первых изделий на УПиО прописываем

в D16: 5

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

10. Количество суток от даты сдачи первых изделий на УПиО до дня начала отгрузки заказа вписываем

в D17: 6

Полагаем, что через 6 дней после начала поступления изделий с ПУ на УПиО будет покрашена, промаркирована, скомплектована, упакована, погружена на транспорт и отправлена заказчику первая партия заказа.

11. Продолжительность отгрузки металлоконструкций всего заказа в соответствии с условиями договора пишем

в D18: 16

Планируем, что между первой и последней отгрузками у нас есть 16 суток.

3. Расчет значений в таблице для диаграммы Ганта:

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

Таблица для диаграммы Ганта

1. В ячейках G3-G7 выделены названия пяти основных этапов выполнения заказа.

2. В H3-H7 вычисляются даты старта каждого этапа:

в H3: =D8 =15.01.15

в H4: =H3+D10 =20.01.15

в H5: =H3+D12 =20.01.15

в H6: =H4+D14+D16 =29.01.15

в H7: =H6+D17 =04.02.15

/Числовой формат ячеек H3-H7 – «Дата», тип – «14.03.01»/

3. В I3-I7 высчитывается — сколько времени в сутках прошло от начала каждого этапа (значения ограничиваются продолжительностью этапов):

в I3: =ЕСЛИ(($C$6-H3)>=D9;D9;$C$6-H3) =7

в I4: =ЕСЛИ(($C$6-H4)>=(D9-D10+D11);(D9-D10+D11);$C$6-H4) =2

в I5: =ЕСЛИ(($C$6-H5)>=D13;D13;$C$6-H5) =2

в I6: =ЕСЛИ(($C$6-H6)>=D15-D16;D15-D16;$C$6-H6) =-7

в I7: =ЕСЛИ(($C$6-H7)>=D18;D18;$C$6-H7) =-13

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

4. В J3-J7 рассчитывается — сколько времени в сутках осталось до конца каждого этапа (значения также ограничиваются продолжительностью этапов):

в J3: =ЕСЛИ(I3<0;D9;D9-I3) =1

в J4: =ЕСЛИ(I4<0;(D9-D10+D11);(D9-D10+D11) -I4) =2

в J5: =ЕСЛИ(I5<0;D13;D13-I5) =6

в J6: =ЕСЛИ(I6<0;D15-D16;D15-D16-I6) =18

в J7: =ЕСЛИ(I7<0;D18;D18-I7) =16

4. Создание и форматирование диаграммы Ганта:

1. Выделяем только что созданную таблицу — область G2-J7 — и нажимаем на пиктограмму «Мастер диаграмм» на панели инструментов  Excel «Стандартная» — запускаем мастера.

2. В выпавшем окне на вкладке «Стандартные» выбираем тип диаграммы «Линейчатая», вид – «Линейчатая диаграмма с накоплением…»

Мастер диаграмм шаг1-20s

3. Нажимаем на кнопку «Далее» — на вкладке «Диапазон данных» все должно быть так, как на скриншоте внизу.

Мастер диаграмм шаг2-20s

4. Еще раз жмем на кнопку «Далее» и настраиваем вкладку «Линии сетки». На остальных вкладках этого окна пока ничего не меняем.

Мастер диаграмм шаг3-20s

5. Переходим к четвертому последнему шагу, нажимая на ту же кнопку «Далее». Помещаем диаграмму на этом же листе и нажимаем кнопку «Готово.

Мастер диаграмм шаг4-20s

Заготовка для диаграммы Ганта создана.

Заготовка диаграммы Ганта в Excel-20s

6. Удалим с поля диаграммы «Легенду» (цветные квадратики и надписи «Прошло суток» и «Осталось суток») – щелкаем правой кнопкой мыши на поле, где размещена «Легенда» и в выпавшем окошке выбираем «Очистить».

7. Настроим горизонтальную ось. Для этого делаем двойной щелчок левой кнопкой мыши на «Оси значений». В выпавшем окне «Формат оси» настраиваем вкладки «Шкала», «Шрифт», «Число» и «Выравнивание» так, как показано на скриншотах.

x-format-osi-shkala-20s

Числа, ограничивающие шкалу времени слева и справа — это порядковые номера дней, отсчитанные от 01.01.1900.

Чтобы узнать порядковый номер любой даты, необходимо ввести ее в ячейку листа MS Excel, затем применить к этой ячейке числовой формат «Общий» и считать ответ.

x-format-osi-shrift-20s

x-format-osi-chislo-20s

x-format-osi-vyravnivaniye-20s

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

iskhodnyye-dannyye-ryad-20s

9. Настроим вертикальную ось. Щелкаем дважды левой кнопкой мыши на «Оси категорий». В окне «Формат оси» настраиваем вкладку «Шкала» в соответствии со снимком помещенными далее.

y-format-osi-shkala-20s

Вкладку «Шрифт» настраиваем точно так же, как сделали для горизонтальной оси.

10. Двойным щелчком левой кнопки мыши на любой из светло-желтых полосок ряда «Дата начала этапа» вызываем выпадающее окно «Формат ряда данных». На вкладке «Порядок рядов» перемещаем ряд «Дата начала этапа» на самый верх списка.

format-ryada-dannykh-poryadok-ryadov-20s

Теперь мы видим полоски всех рядов данных диаграммы.

11. Сделаем невидимыми полоски ряда «Дата начала этапа».

format-ryada-dannykh-vid-1-20s

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

Диаграмма Ганта в Excel до форматирования-20s

12. Наводим мышь на «Область диаграммы» и дважды щелкаем левой кнопкой. В выпавшем окне «Формат области диаграммы» нажимаем на вкладке «Вид» на кнопку «Способы заливки». В выпавшем окошке «Способы заливки» выбираем вкладку «Текстура» и на ней выбираем «Пергамент».

sposoby-zalivki-tekstura-20s

Закрываем окна нажатием на кнопки «ОК». Область диаграммы залита текстурой «Пергамент».

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

sposoby-zalivki-gradiyentnaya-20s

Закрываем окна кнопкой «ОК». Область построения диаграммы залита созданным только что градиентом.

14. Изменяем цвета полосок на более яркие цвета – зеленый и желтый. Подводим указатель мыши на полоску ряда «Прошло суток» и дважды щелкаем мышкой. Настраиваем вкладку «Вид» выпавшего окна «Формат ряда данных» как на скриншоте.

format-ryada-dannykh-vid-2-20s

Аналогично меняем цвет на желтый для полоски ряда «Осталось суток».

15. В завершении выделим недели на оси времени. Наводим курсор на «Основные линии сетки оси значений», двойной щелчок и в окне «Формат линии сетки» установим самую большую толщину линии.

 format-liniy-setki-vid-20s

Форматирование завершено – результат на картинке в самом верху статьи и ниже этого текста.

Диаграмма Ганта в Excel-20s

Заключение.

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

Ежедневно и автоматически линия, определяющая текущие сутки (граница между зеленым и желтым цветом), перемещается по шкале времени, показывая вам в какой стадии выполнения должны находиться этапы процесса согласно составленному плану. На картинке вверху текущая дата — 22.01.15.

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

Ссылка на скачивание файла программы: diagramma-ganta-plana-vypolneniya-zakaza (xls 64KB).

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

Другие статьи автора блога

На главную


Введите Ваш e-mail:

Статьи с близкой тематикой

Отзывы

Один комментарий на «Диаграмма Ганта в Excel»

  1. Галина 28 Янв 2015 00:18

    Спасибо! Очень интересно — не подозревала, что такое можно выполнять в Exel, обязательно завтра попробую сделать что-нибудь. У Вас действительно полезная информация, спасибо за то, что Вы делитесь ею.

Ваш отзыв







  • Посетители: 656 202

  • Подписчики: 3 414