Как сделать календарное планирование в excel?

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

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

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

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

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

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

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

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

Отмечаем элементы шапки и клацаем по области выделения. В списке отмечаем значение «Формат ячеек…».

Перемещаемся во вкладку окна форматирования «Шрифт». В блоке настроек «Начертание» устанавливаем флажок около параметра «Полужирный». Это нужно сделать, чтобы наименования столбцов выделялись среди другой информации. Теперь жмем по кнопке «OK», чтобы сохранить введенные изменения форматирования.

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

  • Расположившись во вкладке «Главная», клацаем по треугольнику справа от пиктограммы «Границы» в блоке «Шрифт» на ленте. Открывается перечень выбора типа границ. Останавливаем свой выбор на позиции «Все границы».
  • На этом создание заготовки таблицы можно считать оконченным.

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

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

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

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

    После этого клацаем по пиктограмме «Граница» в режиме «Все границы».

    Вслед за тем, как границы очерчены, внесем даты в шкалу времени. Допустим, мы будем контролировать проект с периодом действия с 1 по 30 июня 2017 года. В этом случае наименование колонок шкалы времени нужно установить в соответствии с указанным промежутком времени. Конечно, вписывать вручную все даты довольно утомительно, поэтому воспользуемся инструментом автозаполнения, который называется «Прогрессия».

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

    Происходит активация окна «Прогрессия». В группе «Расположение» должно быть отмечено значение «По строкам», так как мы будем заполнять шапку, представленную в виде строки. В группе «Тип» должен быть отмечен параметр «Даты». В блоке «Единицы» следует поставить переключатель около позиции «День». В области «Шаг» должно находиться цифровое выражение «1». В области «Предельное значение» указываем дату 30.06.2017. Жмем на «OK».

    Массив шапки будет заполнен последовательными датами в пределе от 1 по 30 июня 2017 года. Но для сетевого графика мы имеем слишком широкие ячейки, что негативно влияет на компактность таблицы, а, значит, и на её наглядность. Поэтому проведем ряд манипуляций для оптимизации таблицы.
    Выделяем шапку шкалы времени. Клацаем по выделенному фрагменту. В списке останавливаемся на пункте «Формат ячеек».

    В открывшемся окне форматирования передвигаемся в раздел «Выравнивание». В области «Ориентация» устанавливаем значение «90 градусов», либо передвигаем курсором элемент «Надпись» вверх. Клацаем по кнопке «OK».

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

    После описанного действия наименования столбцов по высоте вписываются в границы ячеек, но по ширине ячейки не стали компактнее. Снова выделяем диапазон шапки шкалы времени и клацаем по кнопке «Формат». На этот раз в списке выбираем вариант «Автоподбор ширины столбца».

  • Теперь таблица приобрела компактность, а элементы сетки приняли квадратную форму.
  • Этап 3: заполнение данными

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

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

    После этого следует заполнить колонку «№ п/п». Если мероприятий немного, то это можно сделать, вручную вбив числа. Но если планируется выполнение многих задач, то рациональнее будет прибегнуть к автозаполнению. Для этого ставим в первый элемент столбца число «1». Курсор направляем на нижний правый край элемента, дождавшись момента, когда он преобразуется в крестик. Одномоментно зажимаем клавишу Ctrl и левую кнопку мышки, тянем крестик вниз до нижней границы таблицы.

    Весь столбец при этом будет заполнен значениями по порядку.

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

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

    Затем выделяем все ячейки нашей таблицы, кроме шапки и сетки с датами. Клацаем по иконке «Формат» на ленте, к которой мы уже ранее обращались, жмем в открывшемся списке по позиции «Автоподбор ширины столбца».

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

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

    Читать еще:  Круглая диаграмма как сделать в excel

      Отмечаем весь массив пустых ячеек на шкале времени, который представлен в виде сетки элементов квадратной формы.

    Щелкаем по значку «Условное форматирование». Он расположен в блоке «Стили» После этого откроется список. В нем следует выбрать вариант «Создать правило».

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

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

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

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

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

    Второй аргумент представлен выражением «G$1 ДА НЕТ

    Microsoft Excel

    трюки • приёмы • решения

    Четыре возможных способа использования Excel для отображения календарного плана проекта

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

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

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

    Перечень ключевых вех проекта

    Перечень ключевых вех проекта лучше всего составлять для относительно небольших проектов, продолжительность выполнения которых составляет от нескольких недель до нескольких месяцев. Такой список можно составлять и для крупных проектов, но в этом случае он может использоваться лишь руководителями организации как средство краткого их ознакомления с проектом. Ключевые вехи проекта — это основные события, которые будут происходить в процессе выполнения проекта или знаменовать собою получение одного или нескольких важнейших конечных результатов. Как правило, при составлении перечня в одном столбце отображают названия ключевых вех, а их предполагаемые даты завершения — в другом столбце. В целях отслеживания реального хода событий и составления отчетов в этом перечне можно также указывать фактические даты завершения ключевых вех проекта. На рис. 1 показан пример перечня ключевых вех проекта для некоторых конечных результатов проекта Grant St. Move.

    Рис. 1. Перечень ключевых вех проекта

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

    Календарь проекта

    Календарь проекта является еще одним инструментом, который использует ключевые вехи проекта для отображения календарного плана. В календарях обычно отображаются даты начала и завершения ключевых вех проекта — с проведением линии через дни выполнения работы. При использовании календаря можно отображать не только ключевые вехи проекта. Если вы работаете над небольшим проектом с ограниченной продолжительностью выполнения, то имеет смысл отобразить важнейшие задачи, связанные с получением соответствующего конечного результата. На рис. 2 показан календарный план (календарного типа) для конечного результата Perform Physical Move (Выполнить физическое перемещение).

    Рис. 2. Календарный план для конечного результата Выполнить физическое перемещение (Perform Physical Move) проекта Grant St.Move

    Сетевая диаграмма

    Сетевая диаграмма похожа на диаграмму предшествования, которую мы уже использовали в этой главе для определения последовательности выполнения задач. Как и в предыдущем случае, вы можете представить свою сетевую диаграмму на уровне ключевых вех проекта, конечных результатов или задач. Так, в целях составления отчетности для заинтересованных лиц единую сетевую диаграмму имеет смысл составить на уровне конечных результатов проекта, в других же случаях лучше представить отдельные сетевые диаграммы для каждого из конечных результатов. Размер проекта и количество задач в нем будут определять, с какой степенью детализации должен быть представлен календарный план. На рис. 3 показана сетевая диаграмма с конечными результата ми для нашего проекта Grant St. Move. Обратите внимание: в блоках этой диаграммы указаны даты начала и завершения.

    Рис. 3. Сетевая диаграмма

    Графики Ганта

    Когда большинство из нас думает о календарном плане проекта, то первое, что приходит в голову, это графики Ганта. Их можно строить с помощью всех программных пакетов управления проектами. Впрочем, как и с использованием Excel. На графиках Ганта можно отображать практически любой уровень детализации: конечные результаты проекта, задачи, даты начала и завершения, продолжительность выполнения задач, ресурсы и многое другое. На рис. 4 мы построили простой график Ганта, который отображает некоторые из ключевых вех проекта Grant St. Move с разбиением отдельных задач для конечного результата Prepare Offices/Rooms (Подготовить офисные помещения/комнаты).

    Рис. 4. График Ганта

    Начиная со строки 11, перечислены задачи, которые ведут к получению конечного результата Подготовить офисные помещения/комнаты (Prepare Offices/Rooms). Как альтернативный вариант первый столбец можно было бы использовать для отображения конечного результата и номеров задач из WBS или из перечня задач, если вы создали такой перечень задач отдельно от WBS. Проблема, связанная с номерами задач из WBS, заключается в том, что эти номера приводятся в обратном порядке (это объясняется иерархической природой WBS). Если вы создали перечень задач для каждого уровня пакетов работы, то можете присвоить задачам идентификационные номера, имеющие более последовательный характер.

    Как нетрудно заметить, мы указали продолжительность выполнения задач (столбец Duration), дату начала выполнения (столбец Start Date), дату завершения (столбец End Date), а также ресурс (лицо или группу лиц), ответственный за выполнение соответствующей задачи (столбец Resource). Эти группы данных отображаются так же, как и в случае обычной электронной таблицы. Единицей измерения времени в этом графике Ганта является неделя; выполнение проекта начинается с понедельника, 18 августа. Временные рамки, в которых планируется работа для получения конечных результатов, окрашены в серый цвет.

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

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

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

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

    10 шаблонов Excel, которые будут полезны в повседневной жизни

    За долгие годы своего существования Microsoft Excel стал незаменимой «рабочей лошадкой» для многих миллионов прикованных к стулу тружеников. Зачастую в головах пользователей Excel является синонимом самой работы. Давайте сломаем стереотипы и возьмём на вооружение 10 отличных шаблонов Excel на каждый день.

    Отрадно, что корпорация Microsoft всеми силами идёт нам навстречу, предоставляя абсолютно бесплатный доступ к своей офисной продукции для мобильных устройств. Поэтому вы можете вполне удобно использовать мощный инструментарий Excel на своём рослом iPhone, на iPad и на Android-гаджетах с крупной диагональю.

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

    1. Список задач

    Даже самая светлая голова при ясном уме и твёрдой памяти однажды даст сбой, и вы что-то забудете. Хорошо, если это будет покупка корма для рыбок, поздравление тёщи с Днём матери или поливка фиалки вашей второй половинки. Они побулькают, пошипят и поквакают, а ваша совесть останется чистой. Но вдруг вы не оплатите предмет первой необходимости — интернет? Вам будет стыдно смотреть на себя в зеркало. И в тот злополучный день вы сломаетесь и пообещаете составлять списки дел. А пока вы будете мучиться выбором среди электронных планировщиков, попробуйте начать с простого перечня задач.

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

    2. Бюджет путешествия

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

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

    3. Инвентарный список

    Что объединяет пожар, потоп, кражу и приезд родни погостить «на недельку»? Правильно, высокая вероятность утраты целостности вашего имущества.

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

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

    4. Список контактов

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

    Электронный лист ваших знакомств хорош как минимум по двум причинам: им легко делиться и просто сортировать силами Excel. Поэтому не лишним будет заиметь его резервный вариант даже тем, кто всецело доверяет Google Contacts.

    5. Диаграмма Ганта

    Добрая русская традиция ударить по рукам, прогулять аванс, расслабиться и выполнить работу в ночь перед днём отчётности — довольно рискованная бизнес-техника, существенно влияющая на качество. Только планирование, разбиение задач на этапы и твёрдое следование графику способно спасти вашу репутацию.

    Разумеется, мощности Excel позволяют создавать эти самые диаграммы. Их конёк — наглядность и доступность.

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

    6. Семейное древо

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

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

    7. График дежурств

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

    Впишите имена домочадцев в ячейки шаблона, распределив работы по дням недели до и после полудня. И не забудьте повесить распечатку на самое популярное место в квартире — холодильник. Теперь никто не сошлётся на свою забывчивость.

    8. Журнал техобслуживания

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

    9. Журнал пробега

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

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

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

    10. Ежедневник

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

    Шаблон диаграммы Ганта управления проектом в Excel скачать

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

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

    Переименуйте название рабочего листа Excel «Лист1» в имя «Gantt». После на этом же листе создайте таблицу с исходными данными точно такую же как показано ниже на рисунке, с таким же расположением всех ячеек на листе:

    Важно. Чтобы у Вас все адреса заполненных ячеек листа полностью совпадали с исходной представленной на рисунке.

    С помощью маркера в нижнем правом углу курсора Excel заполните диапазон ячеек G1:O1 чередующимися датами от 09.09.2022 по 17.09.2022:

    Подготовка исходных данных закончена переходим непосредственно к построению диаграммы Ганта на календаре.

    Как построить календарь планов с диаграммой Ганта по таблице Excel

    Заполните диапазон ячеек G4:O16 на листе «Gantt» одной и той же формулой:

    =$C4;G$1

    Теперь выделите диапазон ячеек G4:O16 чтобы присвоить ему условное форматирование:

    Как видно на рисунке правило форматирования применяется для ячеек с текстовым значением «Достигнута», которое возвращают формулы в диапазоне G4:O16. Для эстетики снова используем 2 цвета заливки. Важно также отметить что на вкладке «Число» используя опцию «(все форматы)» задаем свой пользовательский формат из трех точек с запетой «;;;» — это позволит скрыть текстовое содержимое в ячейках. Получаем результат:

    Перейдите на любую ячейку в диапазоне G4:O16 и откройте «Диспетчер правил условного форматирования». Для этого используйте меню «ГЛАВНАЯ»-«Условное форматирование»-«Управление правилами». Воспользовавшись кнопкой «Создать правило» создайте еще 2 правила для значений «В процессе» (желтый цвет) и «В ожидании» (красный цвет):

    Теперь создадим курсор для выделения дат на графике – это сделает анализ по графику Ганта более удобным. Снова перейдите на любую ячейку в диапазоне G4:O16 и в диспетчере правил условного форматирования создайте новое правило, но уже с формулой =G$1=$F$1 и другими настройками формата ячеек:

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

    Далее оформляем дизайн шапки таблицы в диапазоне B3:F3 используя градиентную заливку ячеек из двух цветов:

    А также следует изменить цвет шрифта на белый в заголовках столбцов таблицы.

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

    Теперь в диаграмме Ганта сделаем интерактивные элементы управления из выпадающих списков. Начнем с простого. Перейдите курсором Excel на ячейку C1 чтобы в ней сделать первый выпадающий список:

    В параметрах для поля ввода «Источник:» указываем два слова разделенных точкой с запятой «Дни;Недели», нажимаем ОК и выпадающий список из двух значений – ГОТОВ!

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

    Данное имя будет автоматически подгружать в себя все даты, находящиеся в первой строке листа начиная от ячейки G1. А теперь мы используем ссылку на это же имя (=Список_дат) в качестве источника для второго выпадающего списка в ячейке F1:

    Теперь наш курсор для выделения дат – интерактивный и его перемещение по диаграмме Ганта управляется с помощью выпадающего списка в ячейке F1.

    Пришел тот долгожданный момент, когда с помощью VBA-макроса мы будем делать магию для оживления диаграммы Ганта. Откройте редактор макросов Visual Basic нажав комбинацию клавиш ALT+F11 и создайте в нем новый модуль с кодом VBA-программы:

    Полная версия кода макроса на языке программирования VBA:

    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets( «Gantt» )

    sh.Range( «G3:XFD3» ).UnMerge
    sh.Range( «G1:XFD3» ).Clear
    sh.Range( «G1:XFD3» ).Orientation = 0

    Dim lc, lr As Integer

    For i = Application.WorksheetFunction.Min(sh.Range( «C:C» )) To Application.WorksheetFunction.Max(sh.Range( «D:D» ))
    If sh.Range( «G1» ).Value = «» Then
    sh.Range( «G1» ).Value = i

    Else
    lc = sh.Range( «XFD1» ). End (xlToLeft).Column
    sh.Cells(1, lc + 1).Value = i
    End If
    Next i

    lc = sh.Range( «XFD1» ). End (xlToLeft).Column
    lr = sh.Range( «B» & Application.Rows.Count). End (xlUp).Row

    If sh.Range( «C1» ).Value = «Äíè» Then
    sh.Range( «G3» ).Value = «=G1»
    sh.Range( «G3» , sh.Cells(3, lc)).FillRight

    sh.Range( «E3» ).Copy
    sh.Range( «G3» , sh.Cells(3, lc)).PasteSpecial xlPasteFormats
    sh.Range( «G3» , sh.Cells(3, lc)).NumberFormat = «D-MMM»
    sh.Range( «G3» , sh.Cells(3, lc)).Orientation = 90
    sh.Range( «G3» , sh.Cells(3, lc)).EntireColumn.ColumnWidth = 2.5

    For i = 7 To lc Step 7
    sh.Cells(3, i).Value = «Íåäåëÿ-» & i / 7
    sh.Range( «E3» ).Copy
    sh.Range(sh.Cells(3, i), sh.Cells(3, i + 6)).PasteSpecial xlPasteFormats
    sh.Range(sh.Cells(3, i), sh.Cells(3, i + 6)).EntireColumn.ColumnWidth = 0.8
    sh.Range(sh.Cells(3, i), sh.Cells(3, i + 6)).Merge
    sh.Range(sh.Cells(3, i), sh.Cells(3, i + 6)).HorizontalAlignment = xlCenter
    sh.Range(sh.Cells(3, i), sh.Cells(3, i + 6)).VerticalAlignment = xlCenter
    Next i
    lc = sh.Range( «XFD3» ). End (xlToLeft).Column + 6
    End If

    sh.Range( «G1:XFD1» ).NumberFormat = «D-MMM-YY»
    sh.Range( «G1:XFD1» ).Font.Color = VBA.vbWhite

    sh.Range( «H4:XFD» & Application.Rows.Count).Clear
    sh.Range( «G5:G» & Application.Rows.Count).Clear

    sh.Range( «A» & lr + 1, «A» & Application.Rows.Count).EntireRow.Clear
    sh.Range( «G1:XFD3» ).Locked = True
    sh.Range( «G1:XFD3» ).FormulaHidden = True

    sh.Range( «G4:G» & sh.Range( «B» & Application.Rows.Count). End (xlUp).Row).FillDown
    sh.Range( «G4» , sh.Cells(lr, lc)).FillRight

    With sh.Range( «B3» , sh.Cells(lr, lc))
    .Borders(xlEdgeBottom).LineStyle = xlDouble
    .Borders(xlEdgeBottom).Color = vbBlack

    .Borders(xlEdgeLeft).LineStyle = xlDouble
    .Borders(xlEdgeLeft).Color = vbBlack

    .Borders(xlEdgeRight).LineStyle = xlDouble
    .Borders(xlEdgeRight).Color = vbBlack

    .Borders(xlEdgeTop).LineStyle = xlDouble
    .Borders(xlEdgeTop).Color = vbBlack

    sh.Range( «B4» , sh.Cells(lr — 1, 6)). Select
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range( «C1» ). Select

    Чтобы воспользоваться макросом нам потребуется новый (3-тий) элемент управления диаграммой Ганта. Ним послужит простая небольшая картинка в виде кнопки с иконкой «Обновить». Копируем картинку из любого источника и вставляем прямо на лист Excel. А затем подключаем к ней наш макрос Refresh_Data:

    Нажимаем на кнопку и наслаждаемся «магией» автоматизации работы в Excel с помощью макросов:

    Как говорят французы «Ву а ля»!

    Переключение дней и неделей для диаграммы Ганта на календаре планов

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

    Код макроса для вызова с листа Excel:

    Private Sub Worksheet_Change( ByVal Target As Range)

    If Target.Row = 1 Then
    If Target.Column = 3 Then
    Call Refresh_Data
    End If
    End If
    End Sub

    Протестируем второй макрос воспользовавшись первым выпадающим списком для переключения со значения «Дни» на значение «Недели»:

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

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

    Ссылка на основную публикацию
    Adblock
    detector