Фил написал сегодня утром с просьбой о создании диаграммы в Excel.
Есть ли способ взять два столбца с датами начала и окончания для отдельных событий и создать диаграмму типа Ганта, не выходя из Excel?
Эта тема была рассмотрена в совете по созданию временной шкалы. В этом совете от лета 2001 г. упоминалось, что вы также можете создать диаграмму типа Ганта на листе, используя условное форматирование. Этот тип диаграммы поможет решить вопрос Фила.
Я полагаю, что данные Фила выглядят примерно как таблица слева. Есть событие, затем даты начала в столбце B и даты окончания в столбце C. В моем примере я использую годы, но вы можете легко использовать обычные даты Excel.
Следующий шаг можно было бы легко включить в макрос, но реальная цель этого метода - настройка условного форматирования. Я просмотрел свои данные и заметил, что даты находятся в диапазоне от 1901 до 1919. Начиная со столбца D, я ввел первый год 1901. В E1 я ввел 1902. Затем вы можете выбрать D1: E1, щелкнуть маркер заполнения в в нижнем правом углу выделения с помощью мыши и перетащите в столбец W, чтобы заполнить все годы с 1901 по 1920 год.
Чтобы годы занимали меньше места, выберите D1: W1, затем, используя Формат - Ячейки - Выравнивание, выберите вариант вертикального текста. Затем выберите Формат - Столбец - Автоматическая ширина, и вы сможете увидеть все 23 столбца на экране.
Выберите верхнюю левую ячейку области диаграммы Ганта, или в этом примере D2. В меню выберите Формат - Условное форматирование. Диалоговое окно изначально имеет раскрывающийся список с левой стороны, который по умолчанию имеет значение «Значение ячейки». Измените раскрывающийся список на «Formula Is», и правая часть диалогового окна изменится на большое текстовое поле для ввода формулы.
Цель состоит в том, чтобы ввести формулу, которая проверяет, попадает ли год в строке 1 над этой ячейкой в диапазоны лет в столбцах B и C этой строки. Важно использовать правильную комбинацию относительных и абсолютных адресов, чтобы формулу, которую мы вводим в D2, можно было скопировать во все ячейки диапазона.
Необходимо проверить два условия, и оба они должны быть верными. Это означает, что мы собираемся начать с =AND()функции.
Первое условие проверяет, больше ли год в строке 1, чем год в столбце B. Поскольку я всегда хочу, чтобы эта формула ссылалась на строку 1, первая часть формулы - D $ 1> = $ B2. . Обратите внимание, что знак доллара перед 1 в D $ 1 гарантирует, что наша формула всегда указывает на строку 1, а знак доллара перед B в $ B2 гарантирует, что она всегда будет сравниваться со столбцом B.
Второе условие проверяет, является ли год в строке 1 меньше или равен дате в столбце C. Нам все еще нужно использовать ту же относительную и абсолютную адресацию, так что это будет D $ 1 <= $ C2
Нам нужно объединить оба этих условия, используя функцию AND (). Это было бы=AND(D$1>=$B2,D$1<=$C2)
В поле формулы диалогового окна «Условное форматирование» введите эту формулу. Обязательно начинайте со знака равенства, иначе условное форматирование не сработает.
Затем выберите яркий цвет, который будет использоваться всякий раз, когда условие выполняется. Нажмите кнопку «Форматировать…». На вкладке "Узоры" выберите цвет. Нажмите OK, чтобы закрыть диалоговое окно «Формат ячеек», и у вас должно остаться диалоговое окно «Условное форматирование», которое выглядит примерно так.
Нажмите ОК, чтобы закрыть поле «Условное форматирование». Если ваша верхняя левая ячейка в D2 упадет через год, эта ячейка станет желтой.
Независимо от того, стала ли ячейка желтой или нет, нажмите D2 и используйте Ctrl + C или Edit - Copy, чтобы скопировать эту ячейку.
Выделите D2: W6 и в меню выберите Edit - PasteSpecial - Formats - OK. Условный формат будет скопирован на весь диапазон диаграммы Ганта, и вы получите диаграмму, похожую на эту.
Условное форматирование - отличный инструмент, который позволяет легко создавать диаграммы типа Ганта прямо на рабочем листе. Имейте в виду, что вы ограничены только тремя условиями для любой ячейки. Вы можете экспериментировать с различными комбинациями условий. Чтобы создать границы вокруг каждого столбца на диаграмме Ганта, я использовал три условия, как показано ниже, и разные границы для каждого условия.
