Lumpics lumpics.ru

Создание сетевого графика в Microsoft Excel

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

Читайте также: Как сделать диаграмму Ганта в Экселе

Процедура построения сетевого графика

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

Этап 1: построение структуры таблицы

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

  1. Итак, вписываем наименования столбцов в будущую шапку таблицы. В нашем примере названия колонок будут следующими:
    • № п/п;
    • Название мероприятия;
    • Ответственное лицо;
    • Дата начала;
    • Продолжительность в днях;
    • Примечание.

    Если названия не вместятся в ячейку, то раздвигаем её границы.

  2. Наименования колонок в шапке таблицы в Microsoft Excel
  3. Отмечаем элементы шапки и клацаем по области выделения. В списке отмечаем значение «Формат ячеек…».
  4. Переход в формат ячеек в Microsoft Excel
  5. В новом окне передвигаемся в раздел «Выравнивание». В области «По горизонтали» ставим переключатель в положение «По центру». В группе «Отображение» ставим галочку около пункта «Переносить по словам». Это нам пригодится позже, когда мы будет оптимизировать таблицу в целях экономии места на листе, сдвигая границы его элементов.
  6. Вкладка выранивание в окне форматирования ячеек в Microsoft Excel
  7. Перемещаемся во вкладку окна форматирования «Шрифт». В блоке настроек «Начертание» устанавливаем флажок около параметра «Полужирный». Это нужно сделать, чтобы наименования столбцов выделялись среди другой информации. Теперь жмем по кнопке «OK», чтобы сохранить введенные изменения форматирования.
  8. Вкладка шрифт в окне форматирования ячеек в Microsoft Excel
  9. Следующим шагом станет обозначение границ таблицы. Выделяем ячейки с наименованием столбцов, а также то количество строк ниже их, которое будет равно приблизительному числу запланированных мероприятий в границах данного проекта.
  10. Выделение строк будущей таблицы в Microsoft Excel
  11. Расположившись во вкладке «Главная», клацаем по треугольнику справа от пиктограммы «Границы» в блоке «Шрифт» на ленте. Открывается перечень выбора типа границ. Останавливаем свой выбор на позиции «Все границы».
Установка границ в Microsoft Excel

На этом создание заготовки таблицы можно считать оконченным.

Заготовка таблицы готова в Microsoft Excel

Урок: Форматирование таблиц в Экселе

Этап 2: создание шкалы времени

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

В нашем примере используем вариант, когда один период равен одному дню. Сделаем шкалу времени на 30 дней.

  1. Переходим к правой границе заготовки нашей таблицы. Начиная от этой границы, выделяем диапазон, насчитывающий 30 столбцов, а количество строк будет равняться числу строчек в заготовке, которую мы создали ранее.
  2. Выделение диапазона для шкалы времени в Microsoft Excel
  3. После этого клацаем по пиктограмме «Граница» в режиме «Все границы».
  4. Установка границ в диапазоне для шкалы времени в Microsoft Excel
  5. Вслед за тем, как границы очерчены, внесем даты в шкалу времени. Допустим, мы будем контролировать проект с периодом действия с 1 по 30 июня 2017 года. В этом случае наименование колонок шкалы времени нужно установить в соответствии с указанным промежутком времени. Конечно, вписывать вручную все даты довольно утомительно, поэтому воспользуемся инструментом автозаполнения, который называется «Прогрессия».

    В первый объект шапки шакалы времени вставляем дату «01.06.2017». Передвигаемся во вкладку «Главная» и клацаем по значку «Заполнить». Открывается дополнительное меню, где нужно выбрать пункт «Прогрессия…».

  6. Переход в окно прогрессии в Microsoft Excel
  7. Происходит активация окна «Прогрессия». В группе «Расположение» должно быть отмечено значение «По строкам», так как мы будем заполнять шапку, представленную в виде строки. В группе «Тип» должен быть отмечен параметр «Даты». В блоке «Единицы» следует поставить переключатель около позиции «День». В области «Шаг» должно находиться цифровое выражение «1». В области «Предельное значение» указываем дату 30.06.2017. Жмем на «OK».
  8. Окно Прогрессия в Microsoft Excel
  9. Массив шапки будет заполнен последовательными датами в пределе от 1 по 30 июня 2017 года. Но для сетевого графика мы имеем слишком широкие ячейки, что негативно влияет на компактность таблицы, а, значит, и на её наглядность. Поэтому проведем ряд манипуляций для оптимизации таблицы.
    Выделяем шапку шкалы времени. Клацаем по выделенному фрагменту. В списке останавливаемся на пункте «Формат ячеек».
  10. Переход в окно форматирования ячеек в Microsoft Excel
  11. В открывшемся окне форматирования передвигаемся в раздел «Выравнивание». В области «Ориентация» устанавливаем значение «90 градусов», либо передвигаем курсором элемент «Надпись» вверх. Клацаем по кнопке «OK».
  12. Изменение ориентации надписи в окне форматирования в Microsoft Excel
  13. После этого наименования столбцов в виде дат изменили свою ориентацию с горизонтальной на вертикальную. Но из-за того, что ячейки свой размер не поменяли, названия стали нечитаемыми, так как по вертикали не вписываются в обозначенные элементы листа. Чтобы изменить это положение вещей, опять выделяем содержимое шапки. Клацаем по пиктограмме «Формат», находящейся в блоке «Ячейки». В перечне останавливаемся на варианте «Автоподбор высоты строки».
  14. Переход к автоподбору высоты строки в Microsoft Excel
  15. После описанного действия наименования столбцов по высоте вписываются в границы ячеек, но по ширине ячейки не стали компактнее. Снова выделяем диапазон шапки шкалы времени и клацаем по кнопке «Формат». На этот раз в списке выбираем вариант «Автоподбор ширины столбца».
  16. Переход к автоподбору ширины столбца в Microsoft Excel
  17. Теперь таблица приобрела компактность, а элементы сетки приняли квадратную форму.
Квадратная форма элементов сетки в Microsoft Excel

Этап 3: заполнение данными

Далее нужно заполнить таблицу данными.

  1. Возвращаемся к началу таблицы и заполняем колонку «Название мероприятия» наименованиями задач, которые планируется выполнить в ходе реализации проекта. А в следующей колонке вносим фамилии ответственных лиц, которые будут отвечать за выполнение работы по конкретному мероприятию.
  2. Мероприятия и ответсвенные лица в таблице в Microsoft Excel
  3. После этого следует заполнить колонку «№ п/п». Если мероприятий немного, то это можно сделать, вручную вбив числа. Но если планируется выполнение многих задач, то рациональнее будет прибегнуть к автозаполнению. Для этого ставим в первый элемент столбца число «1». Курсор направляем на нижний правый край элемента, дождавшись момента, когда он преобразуется в крестик. Одномоментно зажимаем клавишу Ctrl и левую кнопку мышки, тянем крестик вниз до нижней границы таблицы.
  4. Маркер заполнения в Microsoft Excel
  5. Весь столбец при этом будет заполнен значениями по порядку.
  6. Диапазон заполнен нумерацией по порядку в Microsoft Excel
  7. Далее переходим к столбцу «Дата начала». Тут следует указать дату начала каждого конкретного мероприятия. Делаем это. В столбце «Продолжительность в днях» указываем количество дней, которое придется потратить для решения указанной задачи.
  8. Даты начала и продолжительность в днях конретных мероприятий в Microsoft Excel
  9. В колонке «Примечания» можно заполнять данные по мере необходимости, указывая особенности конкретного задания. Внесение информации в этот столбец не является обязательным для всех мероприятий.
  10. Примечание в таблице в Microsoft Excel
  11. Затем выделяем все ячейки нашей таблицы, кроме шапки и сетки с датами. Клацаем по иконке «Формат» на ленте, к которой мы уже ранее обращались, жмем в открывшемся списке по позиции «Автоподбор ширины столбца».
  12. Переход к автоподбору ширины столбца в программе Microsoft Excel
  13. После этого ширина столбцов выделенных элементов сужается до размеров ячейки, в которой длина данных больше всего в сравнении с остальными элементами колонки. Таким образом, экономится место на листе. При этом в шапке таблицы производится перенос наименований по словам в тех элементах листа, в которых они не умещаются в ширину. Это получилось сделать благодаря тому, что мы ранее в формате ячеек шапки поставили галочку около параметра «Переносить по словам».
Таблица стала компактной в Microsoft Excel

Этап 4: Условное форматирование

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

  1. Отмечаем весь массив пустых ячеек на шкале времени, который представлен в виде сетки элементов квадратной формы.
  2. Выделение диапазона ячеек на шкале времени в Microsoft Excel
  3. Щелкаем по значку «Условное форматирование». Он расположен в блоке «Стили» После этого откроется список. В нем следует выбрать вариант «Создать правило».
  4. Переход к созданию правила условного форматирования в Microsoft Excel
  5. Происходит запуск окна, в котором требуется сформировать правило. В области выбора типа правила отмечаем пункт, который подразумевает использование формулы для обозначения форматируемых элементов. В поле «Форматировать значения» нам требуется задать правило выделения, представленное в виде формулы. Для конкретно нашего случая она будет иметь следующий вид:

    =И(G$1>=$D2;G$1<=($D2+$E2-1))

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

    «И» — это встроенная функция Excel, которая проверяет, все ли значения, внесенные как её аргументы, являются истиной. Синтаксис таков:

    =И(логическое_значение1;логическое_значение2;…)

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

    Первый аргумент записан в виде выражения «G$1>=$D2». Он проверяет, чтобы значение в шкале времени было больше или равно соответствующему значению даты начала определенного мероприятия. Соответственно первая ссылка в данном выражении ссылается на первую ячейку строки на шкале времени, а вторая — на первый элемент столбца даты начала мероприятия. Знак доллара ($) установлен специально, чтобы координаты формулы, у которых стоит данный символ, не изменялись, а оставались абсолютными. И вы для своего случая должны расставить значки доллара в соответствующих местах.

    Второй аргумент представлен выражением «G$1<=($D2+$E2-1)». Он проверяет, чтобы показатель на шкале времени (G$1) был меньше или равен дате завершения проекта ($D2+$E2-1). Показатель на шкале времени рассчитывается, как и в предыдущем выражении, а дата завершения проекта вычисляется путем сложения даты начала проекта ($D2) и продолжительности его в днях ($E2). Для того, чтобы в количество дней был включен и первый день проекта, от данной суммы отнимается единица. Знак доллара играет ту же роль, что и в предыдущем выражении.

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

    Чтобы выбрать определенный цвет заливки, клацаем по кнопке «Формат…».

  6. Переход к выбору формата условного форматирования в приложении Microsoft Excel
  7. В новом окне передвигаемся в раздел «Заливка». В группе «Цвета фона» представлены различные варианты закраски. Отмечаем тот цвет, которым желаем, чтобы выделялись ячейки дней, соответствующих периоду выполнения конкретной задачи. Например, выберем зеленый цвет. После того, как оттенок отразился в поле «Образец», клацаем по «OK».
  8. Выбор цвета заливки в окне Формат ячеек в Microsoft Excel
  9. После возвращения в окно создания правила тоже клацаем по кнопке «OK».
  10. Закрытие окна создания правила условного форматирования в приложении Microsoft Excel
  11. После выполнения последнего действия, массивы сетки сетевого графика, соответствующие периоду выполнения конкретного мероприятия, были окрашены в зеленый цвет.
Сетевой график готов в программе Microsoft Excel

На этом создание сетевого графика можно считать оконченным.

Урок: Условное форматирование в Майкрософт Эксель

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

Обсудить в TelegramНаш Telegram каналТолько полезная информация
Автор статьи Вы на сайте: Статья обновлена: . Автор: Максим Тютюшев

Вам помогли мои советы?

Получить ответ на Email
Уведомить о

20 ответов
По рейтингу
Новые Старые
Межтекстовые Отзывы
Посмотреть все комментарии
Аноним
18 декабря 2017 13:29

Причем здесь сетевой график?

Коля
21 февраля 2019 15:27
Ответить на  Максим Тютюшев

Это линейный график или график Ганта. Сетевой график выглядит несколько по-другому.

Аноним
11 ноября 2022 06:45
Ответить на  Коля

согласен. сетевой график другой

Юрий
27 июля 2017 11:59

В формуле надо вычесть 1 во втором значении, чтобы день начала включался в количество дней

Женя
30 августа 2017 08:11

А как перестроить график на недели и только рабочие дни?

Женя
30 августа 2017 15:31
Ответить на  Максим Тютюшев

А если вместо дат использовать номера недель? И рабочие дни, в принципе знает, помню была там функция, нужно только указать выходные (номнедели?). С праздниками хуже, но там уже плюс-минус день…

Женя
7 сентября 2017 15:13
Ответить на  Максим Тютюшев

По неделям перестроить график оказалось проще простого…. Я прономеровал недели, от 1 до 52, а потом, вместо разбивки по дням, по формуле в нижней ячейке вычислил первый день недели, дальше все по вашему.
Но, есть одно но, даты из разных месяцев могут быть в одной неделе. Например у меня 31 неделя июля получилась — 24.07.2017, и 31 неделя августа у меня получается 24.07.2017, а должно бы быть 1.08.2017. Вот как доработать формулу?
=ДАТА(2017;1;1)+7*номер_недели-ДЕНЬНЕД(ДАТА(2017;1;1);2)-7+1
И да, праздники, мать их. Но, меня +-3 дня не очень смущают.

Алексей
31 августа 2017 08:40

Интересный урок! Но не понял всех нюансов, поясните пожалуйста:
1. Для чего в формуле задан массив =$A:$A ?

2. Для в формуле на рисунке
/wp-content/uploads/2017/05/Perehod-k-vyiboru-formata-v-okne-sozdaniya-pravila-uslovnogo-formatirrovaniya-v-programme-Microsoft-Excel.png

В конце стоит вертикальная линия?

Алексей
1 сентября 2017 08:23
Ответить на  Максим Тютюшев

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

Ололоша
23 января 2018 16:55

А как сделать, чтобы считало помесячно? Прогрессия заполняет строку помесячно, но просто прибавляя месяц к первоначальной дате, т.е. если начало 15.01.2017, то и каждый месяц будет начинаться с 15 числа. Следовательно, при разработке графика клетка не будет закрашиваться если даты не пересекают 15-е число, если дата начала 05.02.2017, а дата окончания 14.02.2017, то клетка февраля 2017 не закрашивается, аналогично, если даты 16.02.2017-25.03.2017, то закрашивается только март. Путём долгих мучений, я понял, что необходимо использовать функции месяц(начало реализации) и год(начало реализации), но правильно встроить в вашу функцию =И(G$1>=$D2;G$1<=($D2+$E2-1)) я так и не смог.

Марина
13 июля 2018 01:23

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

Валерий
8 октября 2018 03:01

Сделал все в точности как тут написано. Но график не отобразился

comment_image_reloaded_75739689
Аноним
21 февраля 2019 12:20

а как набирать значки «больше» или «меньше», ведь вставка не работает при форматировании значения

Аноним
9 ноября 2022 20:46

Если учитывать, что мероприятие 7 по примечанию, как я понял, завершается 16.06 и продолжительность 11 дней, тогда формула именно для этого мероприятия неправильна. Потому что оно должно начинаться 5.06 и именно завершаться 16.06, а не начинаться.

Задать вопрос