+7(499)-938-42-58 Москва
+7(800)-333-37-98 Горячая линия

График реализации проекта в excel. Создание сетевого графика в Microsoft Excel

Содержание

Календарно-сетевое планирование, учет выполнения и анализ инвестиционно-строительных проектов с помощью MS Project и PlanBridge

График реализации проекта в excel. Создание сетевого графика в Microsoft Excel

Продолжаем серию статей от Виталия Денисова.

В предыдущей статье мы рассматривали, насколько важна информационная система управления проектами (ИСУП) для планирования, учета и анализа выполнения проектов, обсуждали проблемы оперативной актуализации календарно-сетевых графиков и основные преимущества работы MS Project и PlanBridge в сценариях коммуникаций между участниками проекта.

Теперь предлагаю рассмотреть возможные сценарии использования MS Project и PlanBridge в управлении инвестиционно-строительными проектами с участием менеджеров Заказчика и Генподрядчика.

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

  • Своевременно ли выполняются поставки материалов и оборудования?
  • Какими темпами выполняются строительно-монтажные и пусконаладочные работы?
  • Какой объем работ должен быть выполнен по плану, а какой освоен по факту?
  • Какие работы выполняются с задержкой, а какие – по графику?
  • Как влияют на сроки и бюджет всего строительства текущие задержки и изменения технологии работ?
  • Как соотносятся по факту графики финансирования и освоения средств на выполненных объемах работ?
  • Своевременно ли передаются фронты работ между смежными подрядчиками?

Для контроля над строительством объекта и получения ответов на вышеперечисленные вопросы Генподрядчик должен добросовестно выполнять следующие функции:

  • Разработка и контроль выполнения календарно-сетевого графика строительства;
  • Разработка оптимального плана финансирования строительства объекта;
  • Координация производства работ субподрядчиков и передачи фронтов работ;
  • Контроль за соблюдением технологической последовательности производства работ на объекте;
  • Организация своевременного снабжения стройки необходимыми материалами, техникой и оборудованием.

Будет нелишним выяснить, кто, и каким образом у Генподрядчика и Заказчика выполняет вышеперечисленные функции. Это может оказаться довольно непростой задачей, если у Генподрядчика нет надлежащей системы коммуникаций между планировщиком и всеми ответственными за выполнение работ.

Планирование

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

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

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

Учет выполнения

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

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

Но, к сожалению, всю эту информацию почти нереально собрать в общий оперативный план.

Для ее получения и систематизации аналитик проекта или РП должен обзвонить или собрать всех ответственных, выписать из журналов и актов огромное количество данных о выполненных объемах, изменениях, задержках. Потом эти данные еще необходимо вручную внести в оперативный план MS Project для актуализации и анализа выполнения проекта в целом.

Для получения оперативной информации и ручного внесения изменений в текущий план MS Project, как правило, требуется огромное количество времени и трудозатрат.

Рабочие сценарии информационного обмена в управлении инвестиционно-строительными проектами

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

1. Автоматическое создание и рассылка электронной почтой ответственным или исполнителям группы обменных документов Excel для планирования работ.

2. Детализация укрупненных задач календарно-сетевого графика строительства MS Project и наполнение данными о технологической последовательности, сроках, физических объемах, расходе материалов, стоимости работ путем загрузки данных из обменных документов.

  • В результате соответствующих настроек и запуска данной функции, выполняется создание специальных таблиц Excel и рассылка ответственным или назначенным исполнителям по заданным адресам электронной почты. Каждый документ содержит заданную выборку работ общего плана, которые курирует определенный ответственный или исполнитель.
  • Ответственные заполняют в обменных документах Excel перечень детализирующих работ по группам, захваткам, отметкам, определяют сроки выполнения, назначают на эти работы физические объемы, материалы, трудовые ресурсы и механизмы, стоимость. Все полученные данные с помощью PlanBridge корректно импортируются в общий план MS Project, образуя целостную модель реализации проекта.

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

Заполнение ответственными обменных документов Excel

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

Консолидация в общем плане данных от нескольких профилированных отделов: производственно-технического, строительно-монтажного, сметного, финансового, и т. д., путем совместного наполнения обменного документа Excel и последующей загрузкой плановых данных в MS Project

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

Для этого необходимо, чтобы ответственные регулярно заполняли ОД о выполненных физических объемах работ или % завершения, фактических сроках начала и окончания.

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

Получив ОД по электронной почте или через общие бесплатные хранилища документов (OneDrive, Google-диск), планировщик или РП загружает их в план MS Project. В результате все соответствующие задачи и интерактивные отчеты плана обновляются оперативными фактическими данными.

4. Рассылка ответственным выбранных работ с расчетными данными задержек и отклонений, управленческих решений и замечаний руководителя по любым задачам плана в формате обменных документов Excel.

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

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

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

Фрагмент общего графика строительства в MS Project

Обменный документ Excel, созданный с помощью PlanBridge

Новые функции PlanBridge для планирования и актуализации строительных проектов

В последней версии PlanBridge появились новые инструменты, с помощью которых в MS Project стали возможны следующие функции.

Загрузка в файл MS Project состава работ, объемов, материалов, трудозатрат рабочих и машин, стоимостей из смет формата АРПС, как основы для построения плана проекта.

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

Создание и настройка специальных ресурсных столбцов для записи и отображения плановых и фактических данных назначений (плановые и фактические объемы, трудозатраты, стоимость) непосредственно в таблице работ представления «Диаграмма Гантта».

Ресурсные столбцы PlanBridge существенно дополняют функции MS Project и предоставляют следующие возможности:

  • при использовании в представлениях Лист ресурсов, Использование ресурсов, Использование задач созданный ресурсный столбец сразу привязывается к выделенному ресурсу или назначению;
  • созданные столбцы позволяют запись и отображение планового и фактического количества назначенных на работы физических объемов, материалов, трудозатрат рабочих и механизмов (отдельно и по группам) в строках соответствующих работ;
  • автоматический расчет промежуточных итогов назначенных объемов работ и материалов в привязке к основной структуре работ и пользовательским группировкам;
  • использование нормативных коэффициентов для автоматического расчета назначенных материалов, трудозатрат рабочих и механизмов в привязке к величине физического объема;
  • использование формул и индикаторов в полях задач для контроля фактического выполнения, плановых остатков и отклонений объемов работ, назначенных материалов и трудозатрат ресурсов.

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

Например: количество планового и фактического бетона, арматуры, кирпича, раствора по захваткам, отметкам, бригадам (т.е. в привязке к выбранной структуре работ).

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

График реализации проекта в excel. Создание сетевого графика в Microsoft Excel

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

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

Что такое диаграмма Ганта?

Как сделать диаграмму Ганта в Excel

Диаграмма Ганта в Excel шаблон

Если вы уже знаете, как сделать диаграмму Ганта в Excel, и хотите только скачать шаблон диаграммы Ганта, то он доступен по следующей ссылке:

[ Диаграмма Ганта в Excel скачать шаблон ]

Что такое диаграмма Ганта?

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

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

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

Как сделать диаграмму Ганта в Excel 2016, 2013 и 2010

К сожалению, Microsoft Excel не имеет встроенного шаблона диаграммы Ганта в качестве опции. Тем не менее, вы можете быстро сделать диаграмму Ганта в Excel, используя функциональность гистограммы и добавив немного форматирования.

Далее мы по шагам рассмотрим, как сделать диаграмму Ганта за 3 минуты.

Мы будем использовать Excel 2013 для создания шаблона диаграммы Ганта, но вы можете точно так же создать диаграмму Ганта в Excel 2016 и Excel 2010.

Шаг 1. Создание таблицы проекта

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

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

Шаг 2. Создание стандартной диаграммы Excel на основе даты начала

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

  • Выберите диапазон начальных дат с заголовком столбца, в нашем случае это B1:B11. Обязательно выберите только ячейки с данными, а не весь столбец.
  • Перейдите на вкладку «ВСТАВКА» –> группа «Диаграммы» и нажмите «Вставить линейчатую диаграмму».
  • В разделе «Линейчатая» щелкните «Линейчатая с накоплением».

Диаграмма Ганта в Excel – Создание линейчатой диаграммы с накоплением

В результате на вашем листе будет добавлена следующая диаграмма:

Шаг 3. Добавление данных Длительность к диаграмме

Теперь нам нужно добавить еще один ряд в будущую диаграмму Ганта в Excel.

  1. Щелкните правой кнопкой мыши в любом месте области диаграммы и выберите «Выбрать данные» в контекстном меню.

Диаграмма Ганта в Excel – Выбрать данные

Откроется окно «Выбор источника данных».

  1. В разделе Элементы легенды (ряды) уже добавлена Дата начала. Теперь нам нужно добавить Длительность.

Нажмите кнопку «Добавить», чтобы выбрать дополнительные данные (Длительность), которые вы хотите построить в диаграмме Ганта.

Диаграмма Ганта в Excel – Выбор источника данных

  1. Откроется окно Изменение ряда. Выполните следующие пункты:
  • В поле «Имя ряда» введите «Длительность» или любое другое имя по вашему выбору. Кроме того, вы можете поместить курсор мыши в это поле и щелкнуть заголовок столбца (ячейка D1) в своей таблице, выбранный заголовок будет добавлен как имя ряда для диаграммы Ганта.
  • В поле «Значения» выберите Длительность, щелкнув по первой ячейке D2 и перетащив мышь до последней D11. Убедитесь, что вы ошибочно не включили заголовок или любую пустую ячейку.

Диаграмма Ганта в Excel – Добавление ряда данных

Нажмите кнопку ОК.

  1. Теперь вы вернулись в окно «Выбор источника данных» с указанием начальной даты и длительности в разделе «Элементы легенд» (ряды). Просто нажмите «ОК» и «Длительность» будет добавлена в диаграмму.

Диаграмма Ганта в Excel – Выбор источника данных – Добавлен новый ряд

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

Шаг 4. Добавление описания задач в диаграмме Ганта

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

  1. Щелкните правой кнопкой мыши в любом месте области графика (область с синими и красными полосами) и нажмите «Выбрать данные», чтобы снова открыть окно «Выбор источника данных».
  2. Убедитесь, что в левой панели выбрана дата начала и нажмите кнопку «Изменить» в правой панели под надписью «Подписи горизонтальной оси (категории)».

Диаграмма Ганта в Excel – Добавление описания задач в диаграмме Ганта

  1. Откроется окно Подписи оси, и вы выбираете свои задачи таким же образом, как выбирали «Длительность» на предыдущем шаге. Помните, что заголовок столбца не должен быть включен. Чтобы закрыть открытые окна, два раза нажмите OK.
  1. На этом этапе диаграмма Ганта должна содержать описание задач в левой части и выглядеть примерно так:

Шаг 5. Преобразование гистограммы в диаграмму Ганта в Excel

Теперь у нас есть сводная гистограмма. Мы должны добавить правильное форматирование, чтобы данная диаграмма больше напоминала диаграмму Ганта.

Наша цель – удалить синие полосы, чтобы отображались только оранжевые части, представляющие задачи проекта.

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

  1. Нажмите на любую синюю полосу в диаграмме Ганта, чтобы выбрать их все; щелкните правой кнопкой мыши и выберите «Формат ряда данных» в контекстном меню.

Диаграмма Ганта в Excel – Преобразование гистограммы в диаграмму Ганта

  1. Появится окно «Формат ряда данных». Во вкладке «Заливка и границы» в группе «Заливка» выберите «Нет заливки», и в группе «Граница» выберите «Нет линий».

Диаграмма Ганта в Excel – Изменение формата ряда данных в диаграмме Ганта

  1. Как вы, наверное, заметили, задачи в диаграмме Ганта перечислены в обратном порядке. И теперь мы это исправим.

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

Диаграмма Ганта в Excel – Изменение порядка задач в диаграмме Ганта

Результаты внесенных изменений:

  • Задачи упорядочены в соответствии с диаграммой Ганта.
  • Маркеры даты перемещаются снизу вверх в начало графика.

Диаграмма Excel начинает выглядеть как обычная диаграмма Ганта:

Шаг 6. Улучшение дизайна диаграммы Ганта в Excel

Добавим еще несколько штрихов для улучшения внешнего вида диаграммы Ганта.

  1. Удаление пустого пространства в левой части диаграммы Ганта.

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

  • Щелкните правой кнопкой мыши по первой начальной дате в таблице данных, выберите «Формат ячеек» –> «Общий». Запишите число, которое вы видите – это числовое представление даты, в данном случае 42826. Нажмите «Отмена» (!), потому что нам здесь не нужно вносить какие-либо изменения.

Диаграмма Ганта в Excel – Числовое представление начальной даты

  • Нажмите на любую дату над панелью задач в диаграмме Ганта. Щелкните правой кнопкой мыши в данной области и выберите «Формат оси» в контекстном меню.

Диаграмма Ганта в Excel – Удаление пустой области в правой части диаграммы Ганта

  • В разделе «Параметры оси» в поле «Минимум» введите число, записанное на предыдущем шаге. Также в этом разделе вы можете изменить основные и промежуточные деления для интервалов дат. Как правило, чем короче время вашего проекта, тем меньшее число вы используете. Вы можете увидеть какие настройки мы используем в нашем примере на нижеприведенном изображении.

Примечание.

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

Диаграмма Ганта в Excel – Изменение дат в диаграмме Ганта

  1. Удаление лишнего пустого пространства между полосами.

Уплотнение полос задач сделает диаграмму Ганта еще лучше.

  • Нажмите на любую красную полоску, чтобы выбрать их все; щелкните правой кнопкой мыши и выберите «Формат ряда данных».
  • В диалоговом окне «Формат ряда данных» установите Перекрытие рядов на 100%, а Боковой зазор – на 0% (или близкий к 0%).

Диаграмма Ганта в Excel – Удаление пустого пространства в диаграмме Ганта

И вот результат наших усилий – простая, но симпатичная диаграмма Ганта в Excel:

Диаграмма Ганта в Excel – Финальная версия диаграммы Ганта

Помните, что, хотя диаграмма Excel очень хорошо имитирует диаграмму Ганта, она по-прежнему сохраняет основные функции стандартной диаграммы Excel:

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

Диаграмма Ганта в Excel шаблон

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

Когда вы создали свой дизайн, хорошей идеей будет сохранить диаграмму Ганта в Excel как шаблон для будущего использования. Для этого щелкните правой клавишей мыши в области диаграммы, и выберите пункт «Сохранить как шаблон» в контекстном меню.

Диаграмма Ганта в Excel – Сохранить шаблон диаграммы Ганта в Excel

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

[ Диаграмма Ганта в Excel скачать шаблон ]

АКак сделать сетевой график в Excel?

График реализации проекта в excel. Создание сетевого графика в Microsoft Excel

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

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

Расчет сетевого графика в прикладном аспекте его исполнения является предметом настоящей статьи.

Перед стартом моделирования

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

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

Расчеты и моделирование будем выполнять методом «вершина-работа» и классическим табличным способом («вершина-событие») с применением МКР (метода критического пути).

Построение сетевого графика мы начнем на основе первой итерации календарного плана, выполненного в форме диаграммы Ганта. Для целей наглядности предлагаю не учитывать отношения предшествования и максимально упростить последовательность действий.

Хотя на практике такое бывает редко, представим в нашем примере, что операции выстроены в последовательность вида «окончание-начало».

Ниже вашему вниманию представляются две таблицы: выписка из списка работ проекта (фрагмент из 15-ти операций) и список параметров сетевой модели, необходимый для представления формул.

Пример фрагмента списка операций инвестиционного проекта

Список параметров сетевой модели, подлежащих расчету

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

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

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

Что нам потребуется определить в ходе построения графика?

  1. Раннее начало текущей работы, в которую входят несколько связей от предыдущих операций. Выбираем максимальное значение из всех ранних окончаний предыдущих операций.
  2. Позднее окончание текущего действия, из которого выходят несколько связей. Выбираем минимальное значение из всех поздних начал последующих действий.
  3. Последовательность работ, формирующих критический путь. У этих действий раннее и позднее начала равны, как и раннее и позднее окончание соответственно. Резерв такой операции равен 0.
  4. Полные и частные резервы.
  5. Коэффициенты напряженности работ. Логику формул резервов и коэффициента напряженности работы мы рассмотрим в специальном разделе.

Шаг первый

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

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

Схема изображения работы на сетевом графике

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

Рассчитываем раннее начало и раннее окончание с учетом формулы раннего начала текущего действия в условиях нескольких входящих связей. И так проходим до завершающей фрагмент операции. При этом, в нашем примере проекта тем же графиком Ганта не предусмотрены исходящие связи от операций 11, 12, 13 и 14.

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

Шаг второй

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

Шаг третий

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

Построение графиков в Excel по данным таблицы

График реализации проекта в excel. Создание сетевого графика в Microsoft Excel

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

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

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

Допустим, у нас есть данные по чистой прибыли предприятия за 5 лет:

ГодЧистая прибыль*
201013742
201111786
20126045
20137234
201415605

* Цифры условные, для учебных целей.

Заходим во вкладку «Вставка». Предлагается несколько типов диаграмм:

Выбираем «График». Во всплывающем окне – его вид. Когда наводишь курсор на тот или иной тип диаграммы, показывается подсказка: где лучше использовать этот график, для каких данных.

https://www.youtube.com/watch?v=1VDfnG4Q3e0

Выбрали – скопировали таблицу с данными – вставили в область диаграммы. Получается вот такой вариант:

Прямая горизонтальная (синяя) не нужна. Просто выделяем ее и удаляем. Так как у нас одна кривая – легенду (справа от графика) тоже убираем. Чтобы уточнить информацию, подписываем маркеры. На вкладке «Подписи данных» определяем местоположение цифр. В примере – справа.

Улучшим изображение – подпишем оси. «Макет» – «Название осей» – «Название основной горизонтальной (вертикальной) оси»:

Заголовок можно убрать, переместить в область графика, над ним. Изменить стиль, сделать заливку и т.д. Все манипуляции – на вкладке «Название диаграммы».

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

Можем оставить график в таком виде. А можем сделать заливку, поменять шрифт, переместить диаграмму на другой лист («Конструктор» – «Переместить диаграмму»).



Допустим, нам нужно показать не только чистую прибыль, но и стоимость активов. Данных стало больше:

Но принцип построения остался прежним. Только теперь есть смысл оставить легенду. Так как у нас 2 кривые.

Добавление второй оси

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

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

Выделяем ось, для которой хотим добавить вспомогательную. Правая кнопка мыши – «Формат ряда данных» – «Параметры ряда» – «По вспомогательной оси».

Нажимаем «Закрыть» – на графике появилась вторая ось, которая «подстроилась» под данные кривой.

Это один из способов. Есть и другой – изменение типа диаграммы.

Щелкаем правой кнопкой мыши по линии, для которой нужна дополнительная ось. Выбираем «Изменить тип диаграммы для ряда».

Определяемся с видом для второго ряда данных. В примере – линейчатая диаграмма.

Всего несколько нажатий – дополнительная ось для другого типа измерений готова.

Строим график функций в Excel

Вся работа состоит из двух этапов:

  1. Создание таблицы с данными.
  2. Построение графика.

Пример: y=x(√x – 2). Шаг – 0,3.

Составляем таблицу. Первый столбец – значения Х. Используем формулы. Значение первой ячейки – 1. Второй: = (имя первой ячейки) + 0,3. Выделяем правый нижний угол ячейки с формулой – тянем вниз столько, сколько нужно.

В столбце У прописываем формулу для расчета функции. В нашем примере: =A2*(КОРЕНЬ(A2)-2). Нажимаем «Ввод». Excel посчитал значение. «Размножаем» формулу по всему столбцу (потянув за правый нижний угол ячейки). Таблица с данными готова.

Переходим на новый лист (можно остаться и на этом – поставить курсор в свободную ячейку). «Вставка» – «Диаграмма» – «Точечная». Выбираем понравившийся тип. Щелкаем по области диаграммы правой кнопкой мыши – «Выбрать данные».

Выделяем значения Х (первый столбец). И нажимаем «Добавить». Открывается окно «Изменение ряда». Задаем имя ряда – функция. Значения Х – первый столбец таблицы с данными. Значения У – второй.

Жмем ОК и любуемся результатом.

С осью У все в порядке. На оси Х нет значений. Проставлены только номера точек. Это нужно исправить. Необходимо подписать оси графика в excel. Правая кнопка мыши – «Выбрать данные» – «Изменить подписи горизонтальной оси». И выделяем диапазон с нужными значениями (в таблице с данными). График становится таким, каким должен быть.

Наложение и комбинирование графиков

Построить два графика в Excel не представляет никакой сложности. Совместим на одном поле два графика функций в Excel. Добавим к предыдущей Z=X(√x – 3). Таблица с данными:

Выделяем данные и вставляем в поле диаграммы. Если что-то не так (не те названия рядов, неправильно отразились цифры на оси), редактируем через вкладку «Выбрать данные».

А вот наши 2 графика функций в одном поле.

Графики зависимости

Данные одного столбца (строки) зависят от данных другого столбца (строки).

Построить график зависимости одного столбца от другого в Excel можно так:

Условия: А = f (E); В = f (E); С = f (E); D = f (E).

Выбираем тип диаграммы. Точечная. С гладкими кривыми и маркерами.

Выбор данных – «Добавить». Имя ряда – А. Значения Х – значения А. Значения У – значения Е. Снова «Добавить». Имя ряда – В. Значения Х – данные в столбце В. Значения У – данные в столбце Е. И по такому принципу всю таблицу.

Скачать все примеры графиков

Готовые примеры графиков и диаграмм в Excel скачать:

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

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

Автоматическая сетевая диаграмма проекта в MS EXCEL

График реализации проекта в excel. Создание сетевого графика в Microsoft Excel

Построим сетевую диаграмму проекта на диаграмме MS EXCEL. Сетевая диаграмма будет автоматически перестраиваться при изменении связей между работами. Для этого нам потребуется автоматически определить все пути проекта (не только критические). 

В статье Метод критического пути в MS EXCEL сетевая диаграмма проекта была построена на листе MS EXCEL. 

К сожалению, при изменении связей между работами данную диаграмму необходимо перестраивать в ручную, что может быть достаточно трудоемко. Чтобы этого избежать, используем диаграмму типа Точечная (XY Scatter), на которой точками обозначим работы, а стрелками – связи между работами.

СОВЕТ: Подробнее о построении диаграмм см. статью Основы построения диаграмм в MS EXCEL.

Постановка задачи

Предположим, что нам требуется отобразить связи проекта, состоящего из 7 работ (от А до G), также заданы вехи начала (Start) и окончания проекта (Finish).

Как видно из диаграммы, связи между работами заданы так, что существует 3 пути:

  1. Start-A-D-Finish
  2. Start-B-E-G-Finish
  3. Start-C-F-Finish

Изобразим на диаграмме типа Точечная эти работы и связи между ними.

Пусть между работами D и G требуется создать связь (выделено красным на диаграмме ниже).

Это приведет к тому, что число путей проекта увеличится с 3-х до 4-х: добавится путь Start-А-D-G-Finish.

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

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

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

ВНИМАНИЕ! Построение данной сетевой диаграммы в этой статье приведено лишь с целью демонстрации технической реализуемости такого построения в MS EXCEL. Не ставилось целью сделать “удобную программу для пользователей”.

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

Такая настройка от пользователя потребует серьезных знаний MS EXCEL и времени.

Задаем связи между работами

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

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

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

Подсчет предшественников можно сделать с помощью формулы, которая состоит из 4-х частей:

=ЕСЛИОШИБКА(ИНДЕКС($A$22:$A$29;НАИБОЛЬШИЙ(

ЕСЛИ($A23=$F$22:$H$29;СТРОКА($F$22:$F$29)-СТРОКА($F$21);””);L$20));””)

Сначала, для каждой работы определим, для каких работ она является последователем. Это реализовано с помощью функции ЕСЛИ(): если работа является чьим-то последователем, то возвращается код работы-предшественника (совпадающий с номером позиции работы). В противном случае возвращается значение Пустой текст “”.

Функция НАИБОЛЬШИЙ()  сортирует массив вида {“”;””;””:””;””;””:””;””;””:””;””;””:””;5;””:6;””;””:””;””;””:””;””;””} и выводит 3 наибольших значения, т.е. сначала числа, а затем значения Пустой текст “” (если последователей менее 3).

Функция ИНДЕКС() выводит коды работ-предшественников по номеру позиции. Функция ЕСЛИОШИБКА(), которая появилась в MS EXCEL 2007, заменяет ошибки #ЧИСЛО! на значение Пустой текст “”, которое очень удобно, т.к. ячейка выглядит при этом пустой. 

При задании последователей (см. желтые ячейки) необходимо отслеживать, чтобы количество предшественников у каждой работы было не больше 3-х (см. столбец J).

Вычисление путей

Алгоритм вычисления путей следующий:

  1. Для вехи Start (код=1) определяются ее последователи, т.е. работы А, В и С, которые имеют коды соответственно 2, 3, 4 (см. шаг 0, строка 37), а также количество последователей у каждой из работ А, В и С. Кроме того, код вехи Start необходимо повторить в столбце В столько раз, сколько у нее последователей. На данном шаге количество путей равно 3, т.е. равно количеству последователей вехи Start;
  2. На следующем шаге определяются последователи работ А, В, С, т.е. работы D, E, F (коды 5, 6, 7). См. ячейку J53. В соседнем столбце справа вычисляется количество последователей этих работ. Как видно из диаграммы выше, работа D имеет 2 последователя. Это приводит к тому, что количество путей проекта увеличивается до 4-х. Поэтому нужно обновить количество вех Start до 4-х (см. ячейку G53). Это можно сделать используя идеи из статьи Восстанавливаем последовательности из списка без повторов в MS EXCEL.
  3. Аналогично на следующих шагах определяются следующие работы-последователи и обновляется количество возможных путей проекта. По результатам каждого шага производится проверка достижения вехи Finish (код=9). Если все пути завершены, то в конце каждого пути должна быть веха Finish.

В файле примера максимальная длина пути от вехи Start до вехи Finish должна быть не более 5 (включая эти вехи). Под длиной пути понимается последовательность работ, например: Start – А – D – G – Finish.

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

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

Построение диаграммы

Сначала на диаграмме построим точки, представляющие собой работы.

Чтобы каждой точке присвоить надпись с названием работы нужно написать макрос или иметь MS EXCEL 2013 или последующую версию программы (см. статью Подписи для точечной диаграммы в MS EXCEL). В файле примера также имеется макрос для присвоения надписей (также см. статью Ориентированный граф на диаграмме MS EXCEL).

Чтобы создать пути (максимум 10) нам потребуется создать 10 рядов данных. Часть из этих рядов будет содержать значения #Н/Д, т.к. число путей может быть меньше 10.

В результате получим вот такую диаграмму, в которой 4 пути:

Предположим, что при планировании проекта выяснилось, что между работами F и G имеется связь (взаимосвязь работ в файле примера только Финиш-Старт, т.е. начало следующей работы после окончания предыдущей). Добавив эту связь в ячейку D28, диаграмма автоматически обновится.

Как видно из диаграммы – также увеличилось число путей: с 4 до 5.

Поделиться:
Нет комментариев

    Добавить комментарий

    Ваш e-mail не будет опубликован. Все поля обязательны для заполнения.