Диаграмма ганта эксель условное форматирование
Обновлено: 08.07.2024
Если вам приходится часто строить графики проектов, отпусков, тренингов и т.д., то есть любых долгих и сложных многоэтапных мероприятий, то этот простой, но красивый прием придется вам ко двору. Предположим, что у нас имеется вот такая таблица:
Идея состоит в использовании условного форматирование, чтобы заставить Excel заливать ячейку заданным цветом, если она по дате попадает между началом и концом этапа. Для этого выделите весь диапазон, где должна быть диаграмма (в нашем примере - начиная с ячейки D3 и до конца таблицы) и затем жмем на вкладке Главная (Home) кнопку Условное форматирование - Создать правило (Conditional Formatting - New Rule) , выбираем последний тип Использовать формулу для определения форматируемых ячеек (Use a formula to determine which cells to format) и вводим формулу:
По сути, эта формула делает простую вещь - функция И (AND) проверяет обязательное выполнение двух условий, чтобы дата для текущей ячейки была позже, чем дата начала этапа и раньше даты окончания. Если оба эти условия выполняются, то ячейка находится внутри этапа, т.е. должна быть залита. Нажав на кнопку Формат (Format) можно выбрать необходимый цвет.
Просто и красиво, не правда ли?
В более "навороченном" варианте такая диаграмма может менять шаг временной шкалы, учитывать наличие выходных и праздничных дней и подсвечивать выбранную строку/столбец:
Основа здесь все та же - условное форматирование. Плюс добавлены:
- задержка перед началом этапа (может быть положительно или отрицательной или =0)
- проценты выполнения по каждому этапу с их подсветкой на диаграмме
- функция РАБДЕНЬ (WORKDAY) для расчета не календарных, а рабочих дней
- подсветка праздников и выходных все тем же условным форматированием с помощью функции ДЕНЬНЕД (WEEKDAY)
- координатная подсветка текущей строки и столбца с помощью специального макроса
Microsoft Project нам так, конечно, не переплюнуть, но приблизиться к нему в визуальной части вполне можно :)
Видимо у вас Excel 2007 - он даты в параметрах оси не понимает. Вместо даты надо ввести ее код, который можно увидеть, если для ячейки с датой начала проекта (желтая ячейка в видео) временно установить общий или числовой формат.
Все получилось! Спасибо огромное Николай за столь оперативный ответ!
| Фамилия | 1-я часть отп. | Длительность | 2-я часть отпуска | Длительность |
| Иванов | 01.01.2013 | 15 | 01.07.2013 | 15 |
| Петров | 01.02.2013 | 30 | 01.08.2013 | 15 |
| Сидоров | 01.03.2013 | 20 | 15.08.2013 | 20 |
Александр, нужно добавить в таблицу еще три столбца (Начало, Длительность, Конец) для второй части отпуска и в условном форматировании добавить еще одно условие с помощью функции И.
Николай, столкнулась с той же проблемой, что и Александр. Но не совсем понятно, как её решить. Почему нужно добавлять в условном форматировании еще одно условие, если мы создаем линейчатую диаграмму. Совсем запуталась. Объясните, пожалуйста, чайнику)
Пробовала создавать в данных диаграммы еще 2 ряда дополнительных, то тогда ничего не выходит.
Очень хотелось бы получить ответ.Заранее спасибо!
Николай. Немного лирики - спасибо большое за сайт и видео.
Не понял, разве в меню "выбор источника данных" в диаграмме есть условное форматирование?
Прошу выложить пример построения диаграммы Ганта для графика отпусков, например для 5 сотрудников, у каждого из которых будет 2 временных периода в году. Как сделать это всё в одной диаграмме?
Заранее спасибо.
- ряд, имитирующий интервал между окончанием первого отпуска и началом второго (его потом сделать прозрачным)
- ряд, представляющий собой длительность второго отпуска
Николай, спасибо большое, всё получилось.
Как залить скриншот готовой диаграммы?
Картинку нужно выложить на любой бесплатный хостинг картинок или в облачное хранилище, а сюда вставить ссылку на него. Прикреплять файлы напрямую движок сайта в комментариях не позволяет, к сожалению.
Николай, выручайте. У меня почему то не ставится фиксировованное значение оси, те не убираются первые графики, с идущие с ноября, а мне надо с января 2014. Уже голову сломал помогайте.
У вас Excel 2007, насколько я понимаю?
Введите в качестве фиксированного минимального значения по оси времени не дату (как в видео, где Excel 2010), а код даты. Его можно увидеть, если ввести дату в любую ячейку и поменять потом ее формат на Общий или Числовой .
Николай, добрый день.
Подскажите можно ли добавить в данную диаграмму, еще и числовые показатели. (денежное выражение на каждом этапе проекта)
Можно добавить как подписи к ряду Длительность любые данные, в том числе и финансовые показатели, расходы на этап и т.д.
Это можно сделать с помощью надстройки XYChartLabeler
А как сделать тоже самое только в виде сводной диаграммы, с фильтрами и сводной таблицей для управления.
Заранее благодарен
Не сочтите за злостный PR, но я тут сегодня тоже выложил свою реализацию диаграммы Ганта. Вышло довольно неплохо.
Интересное воплощение, но мне в отображении графика Ганта не хватило указания ответственных. попробую на днях создать диаграммуГанта под свои задачи. посмотрим как получится
пытаюсь составить такую диаграмму на неделю с разбивкой не по дате а по времени - часы, минуты , даже хотя бы на день - ничего не получается
Такая же проблема как у Serge0ev. Есть необходимость в подобной диаграмме, только по времени и в разрезе одно дня. Как это реализовать?
Здравствуйте Николай!
Как можно изменить в диаграмме ось времени, чтобы было 1 месяц, 2 месяц,3 месяц. (на случай, когда дата старта проекта не принята, а требуется видеть длительность этапов). Спасибо!
График Ганта может описывать процесс работы программы, но знаете ли вы, как использовать функцию условного форматирования для создания простой диаграммы Ганта в Excel? В этой статье я расскажу, как создать диаграмму Ганта с помощью условного форматирования.
Использование условного форматирования для создания диаграммы Ганта
Предположим, ваш список данных, как показано на скриншоте ниже:
1. Выберите ячейки, которые находятся в столбцах даты, D2: Z7, и щелкните Главная > Условное форматирование > Новое правило. Смотрите скриншот:
2. Затем в Новое правило форматирования диалоговое окно, выберите Используйте формулу, чтобы определить, какие ячейки следует форматировать. в Выберите тип правила list и введите эту формулу = И (D $ 1> = $ B2, D $ 1 <= $ C2) в Формат значений, где эта формула истинна текстовое поле и щелкните Формат. Тогда в Формат ячеек диалог, включите Заполнять и выберите один цвет, который хотите использовать для выделения. Смотрите скриншот:
В формуле D1 - это первые ячейки, которые в столбцах даты, B2 - первая начальная дата в столбце Start Date, а C2 - первая конечная дата в столбце End Date.
3. Нажмите OK > OK, отображается диаграмма Ганта.
Используйте быстрый инструмент для создания стандартной диаграммы Ганта
Освободи Себя Диаграмма Ганта инструмента в Графики группы Kutools for Excel который может помочь вам быстро создать стандартную диаграмму Ганта в Excel.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Нажмите Kutools > Графики > Момент времени > Диаграмма Ганта.
2. в Диаграмма Ганта диалог, сделайте как показано ниже:
1) Выберите название задачи (проекта) в Название задачи текстовое окно;
2) Выберите ячейки, содержащие даты начала в Дата / время начала текстовое окно;
3) Выберите ячейки, содержащие даты окончания или дни продолжительности до Дата / время окончания or Длительность текстовые поля по мере необходимости. Смотрите скриншот:
3. Нажмите Ok, появится диалоговое окно, напоминающее вам, что будет создан скрытый лист для размещения данных. Нажмите Да, диаграмма Ганта создана.
Чаевые： При первом использовании вы можете щелкнуть Пример кнопку, чтобы понять, как работает этот инструмент.
Например, у вас есть таблица оценок класса, и вы хотите создать диаграмму для цветных оценок в разных диапазонах, таких как больше 90, меньше 60 и между 60 и 90, как показано на скриншоте ниже, как бы вы справились Это? В этой статье будет представлен способ создания диаграммы с условным форматированием для ее решения.
- Создать диаграмму с условным форматированием в Excel
- Создайте диаграмму с условным форматированием с помощью замечательного инструмента
- Условное форматирование существующей диаграммы
Создать диаграмму с условным форматированием в Excel
Чтобы различать оценки в разных диапазонах на диаграмме, вы можете создать диаграмму с условным форматированием в Excel.
1. Добавьте три столбца справа от исходных данных, как показано ниже:
(1) Назовите первый столбец как >90 введите формулу =IF(B2>90,B2,0) в первой пустой ячейке этого столбца, а затем перетащите маркер автозаполнения на весь столбец;
(2) Назовите второй столбец как <60 введите формулу =IF(B2<60,B2,0) , и перетащите маркер автозаполнения на весь столбец;
(3) Назовите третий столбец как 60
90 введите формулу =IF(AND(B2>=60,B2<=90),B2,0) и перетащите маркер автозаполнения на весь столбец.
Теперь вы получите новые исходные данные, как показано на скриншоте ниже:
2, Выберите Имя столбец и новые три столбца с удержанием Ctrl ключ, а затем щелкните Insert > Insert Column or Bar Chart (or Column) > Clustered Column . Смотрите скриншот:
Теперь диаграмма с условным форматированием создана. Вы увидите, что баллы больше 90 - синие, оценки меньше 60 - оранжевые, а оценки от 60 до 90 - серые. Смотрите скриншот:
Создайте диаграмму с условным форматированием с помощью замечательного инструмента
Если у вас есть Kutools for Excel установлен, вы можете использовать его Таблица группировки цветов возможность быстрого создания диаграммы с условным форматированием в Excel.
Kutools for Excel- Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
1. Выберите источник данных, на основе которого вы создадите диаграмму, и нажмите Kutools > Графики > Таблица группировки цветов для включения этой функции.
2. В таблице группировки цветов сделайте следующее:
(1) Отметьте Столбчатая диаграмма вариант;
(2) Укажите диапазон меток осей;
(3) Укажите диапазон значений серии;
(4) В группы раздел, нажмите Добавить кнопка. Затем в диалоговом окне Добавить группу укажите имя группы, диапазон данных и определенные значения диапазона, которые вам нужны, и нажмите кнопку Добавить кнопку.
Советы: Эта функция добавит правило условного форматирования по одной группе. Если вам нужно добавить несколько правил условного форматирования для диаграммы, добавьте столько групп, сколько вам нужно.
3, Нажмите Ok кнопку.
Теперь вы увидите, что создана столбчатая диаграмма, и столбцы окрашены в соответствии с указанными группами.
Заметки: При изменении значений в источнике данных цвет заливки соответствующих столбцов будет изменен автоматически в зависимости от указанных групп.
Условное форматирование существующей диаграммы
Иногда вы могли создать столбчатую диаграмму, как показано на скриншоте ниже, и теперь хотите добавить условное форматирование для этой диаграммы. Здесь я рекомендую Таблица цветов по значению особенность Kutools for Excel Для решения этой проблемы.
Kutools for Excel- Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
1. Выберите диаграмму, для которой нужно добавить условное форматирование, и нажмите Kutools > Графики > Таблица цветов по значению для включения этой функции.
2. В диалоговом окне «Цвет заливки диаграммы» выполните следующие действия:
(1) Выберите критерий диапазона из Данные раскрывающийся список;
(2) Укажите значения диапазона в Минимальное значение or Максимальное значение коробки;
(3) Выберите цвет заливки из Цвет заливки раскрывающийся список;
(4) Щелкните значок Заполнять кнопку.
Советы:
(1) Операции (1) - (4) изменят цвет заливки столбцов, значения точек данных которых попадают в указанный диапазон данных.
(2) Если вы хотите изменить цвет заливки других столбцов, вам необходимо повторить операции (1) - (4) для создания других правил, говорит, что изменить цвет заливки столбцов, значения точек данных которых находятся между 60 и 90, на серый .
3. После завершения операций нажмите кнопку Закрыть кнопку, чтобы выйти из функции.
Ноты: Этот метод полностью изменит цвет заливки столбцов на диаграмме. Если вы измените значения в исходных данных, цвета заливки соответствующих столбцов не изменятся.
Читайте также: