Auditportal.ru

Аудит Портал
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как использовать условное форматирование для создания диаграммы Ганта в Excel?

Диаграмма Ганта с условным форматированием

Сегодня утром Фил написал письмо с просьбой предоставить график в формате Excel.

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

Пример области данных.

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

Следующий шаг можно легко включить в макрос, но настоящая идея заключается в настройке условного форматирования. Просматривая свои данные, я заметил, что они охватывают период с 1901 по 1919 год. Колонка D начинается с 1901 года. В E1 я ввел 1902 год. Чтобы заполнить все годы с 1901 по 1920, выберите D1: E1, щелкните маркер заполнения в правом нижнем углу выделения и перетащите его в столбец W.

Чтобы годы занимали меньше места, выберите D1: W1, затем с помощью Формат — Ячейки — Выравнивание выберите вариант вертикального текста. Затем выберите Формат — Столбец — Автоширина, и на экране появятся все 23 столбца.

Применяется опция вертикального текста

Выберите верхнюю левую ячейку в области диаграммы Ганта, в данном примере это D2. В меню выберите команду Формат — Условное форматирование. В диалоговом окне слева есть выпадающий список, который по умолчанию имеет значение «Значение ячейки». Измените выпадающий список на «Формулы», и правая часть диалогового окна изменится на большое текстовое поле для ввода формулы.

Цель состоит в том, чтобы ввести формулу, которая проверяет, попадает ли год в строке 1 над этой ячейкой в ​​диапазоны лет в столбцах B и C этой строки. Важно использовать правильную комбинацию относительных и абсолютных адресов, чтобы формулу, которую мы вводим в D2, можно было скопировать во все ячейки диапазона.

Проверяются два условия, и оба должны быть истинными. Это означает, что мы должны начать с функции =AND().

В первом условии проверяется, больше ли год в строке 1, чем год в столбце B. Чтобы сослаться на строку 1, первая часть формулы имеет вид D $ 1> = $ B2. D $ 1 всегда равно строке 1, а B2 всегда равно столбцу B. Знак доллара перед B2 гарантирует, что он всегда берет значение из столбца B.

Второе условие проверяет, является ли год в строке 1 меньше или равен дате в столбце C. Мы должны всегда использовать одинаковую относительную и абсолютную адресацию, поэтому это будет D $ 1 <= $ C2

Это можно сделать с помощью функции AND (). Это будет =AND(D$1>=$B2,D$1<=$C2)

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

Читайте так же:
Как извлечь время или час только из даты и времени в Excel?

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

Диалоговое окно форматирования условий

Закрыв окно Условное форматирование, нажмите кнопку OK. Если левая верхняя ячейка в D2 уменьшится через год, эта ячейка станет желтой.

Независимо от того, стала ли ячейка желтой или нет, нажмите D2 и используйте Ctrl + C или Правка — Копировать, чтобы скопировать эту ячейку.

Выберите в меню Правка — ВставитьСпециальные — Форматы — ОК, когда выделите D2: W6. В результате условный формат будет скопирован на весь диапазон диаграммы Ганта, и вы получите диаграмму, подобную этой.

Условное форматирование диапазона данных приложения

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

Диалог условного форматирования для 3 условий Итоговая диаграмма Ганта

Условное форматирование в MS Excel с примерами

Условное форматирование в Excel — это инструмент, который делит вашу работу на ту, что до и после его освоения. Он основан на автоматическом форматировании ячеек при наступлении определенного условия. Например, если число превышает 100, шрифт становится красным, жирным курсивом; если до оплаты осталось 2 дня, ячейка с датой выделяется желтым цветом; если план продаж перевыполнен на 5% и более, она окрашивается в зеленый цвет, и так далее.

Вот упрощенный, но реальный пример. Есть отчет о товарных запасах.

Таблица без форматирования

Менеджер по закупкам отслеживает товары, которые необходимо пополнить. Для этого он изучает последний столбец, в котором показан запас (ТЗ) в неделях. Если ТЗ меньше, чем, скажем, 3, необходимо подготовить заказ. Когда их меньше 2, необходимо сделать срочный заказ. Если в таблице десятки позиций, то просмотр каждой строки займет довольно много времени. Та же таблица после применения условного форматирования, причем значения ниже пороговых значений выделены определенным цветом.

Таблица с условным форматированием

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

Используйте команду Условный формат на вкладке Главная, чтобы задать условный формат.

Кнопка условного форматирования на ленте

Нажмите на кнопку , чтобы открыть меню.

Меню условного форматирования

Верхние 5 команд – это готовые сценарии для быстрого условного форматирования. Чтобы ими воспользоваться достаточно выбрать нужный вариант и сделать минимальные настройки. Эти сценарии мы рассмотрим ниже.

Читайте так же:
Как зашифровать буквы в словах в Excel?

Ниже находятся еще три опции для ручного создания, удаления и управления правилами условного форматирования. Давайте поговорим и о них.

Все сценарии разделены по категориям:

— Правила распределения ячеек

— Принцип выбора первого и последнего значения

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

Правила выделения ячеек в условном форматировании

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

Больше.

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

Меньше. Ячейки со значением меньше указанного порога форматируются.

Меньше.

Между… Форматирование происходит, если содержимое ячеек попадает в заданные пределы.

Между.

Равно… если значение или текст в ячейке совпадает с условием.

Равно.

Текст содержит… Если совпадает только часть текста (слово, код, комбинация символов и т.д.).

Текст содержит…

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

Дата.

Дублирующие значения… ячейки с одинаковым содержимым выделяются. Хороший способ поиска дубликатов (повторов). В настройках можно также выбрать противоположный вариант: выделять только уникальные значения.

Повторяющиеся значения…

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

Правила отбора первых и последних значений

Первые 10 элементов. Выбираются первые 10 верхних ячеек. Можно выбрать число от топ-5 до топ-20 и т. д. ).

Первые 10 элементов…

Первые 10%… Выделяются 10% наибольших значений. Долю можно изменить.

Первые 10%.

Последние 10 пунктов… Что касается первого пункта, то форматируются только самые маленькие значения.

Последние 10 элементов…

Последние 10%… Наименьшие 10% или другая доля от всех элементов.

Последние 10%.

Выше среднего. Форматированное значение превышает среднее арифметическое.

Выше среднего…

Ниже среднего. Ниже среднего арифметического.

Ниже среднего…

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

Гистограммы

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

Гистограммы условного форматирования

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

Цветные шкалы

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

Цветные шкалы условного форматирования

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

Читайте так же:
Как изменить ориентацию листа или текста в ячейках в Excel

Набор значков

Так выглядят ячейки Excel.

Набор значков в условном форматировании

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

Управление правилами

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

Диалоговое окно управление правилами

После нажатия кнопки «Изменить правило…» открывается окно, вид которого зависит от изменяемого правила.

Редактирование правила условного форматирования

Существует множество вариантов, но мы их пока пропустим. В целом, это довольно интуитивно понятно. Вы просто должны экспериментировать. Практика делает совершенным.

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

Удаление условного форматирования

Условное форматирование — это три шага вперед на пути к профессиональному использованию Excel. Поэтому я рекомендую вам немедленно применить его на практике.

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

Неправильный формат ячеек

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

Строим диаграмму Ганта в Excel

На дворе 2017 год, на компьютерах Excel — 2016, а никто до сих пор не добавил стандартный макет для построения такой полезной и нужной диаграммы Ганта в нашей любимой электронной таблице. Однако мы располагаем всеми необходимыми инструментами для построения такого рода графиков. Мы уже приводили пример файла (здесь), но сегодня мы покажем вам, как создать его самостоятельно.

Ее можно построить двумя способами:

Модификация ленточной диаграммы за счет накопления;

2) Используйте условное форматирование (тогда отображение будет встроено прямо в ячейки).

Рассмотрим пошагово первый метод.

Способ 1. График Ганта через модификацию Ленточной диаграммы с накоплением

1. Организация данных для построения

Диаграмма Ганта — это тип визуализации данных, который используется для представления плана или графика проекта.

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

Правильная организация данных

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

2. Создание диаграммы

Затем выберите весь диапазон данных (вместе с заголовком и каждым столбцом) и создайте диаграмму правил.

Читайте так же:
Как запустить макрос VBA при открытии или закрытии книги?

Создание линейчатой диаграммы с накоплением

Если доступно несколько вариантов, выберите тот, в котором даты указаны внизу, а названия этапов — слева.

Выходные данные должны выглядеть так:

Стандартная диаграмма

После создания диаграммы перейдите к этапу настройки.

3. Настройка диаграммы

Сначала удалите легенду с диаграммы Ганта (в большинстве случаев она не нужна на диаграмме Ганта). Это можно сделать, щелкнув по ней и нажав Delete.

Далее выделите подписи оси Y (названия этапов) и нажмите Ctrl+1 или Правая кнопка мыши — Формат Оси. В открывшейся панели инструментов найдите вкладку Параметры Оси и установите галочки напротив пунктов "в максимальной категории" и "обратный порядок категорий" (это нужно, чтобы этапы начинались сверху с самого первого, а ось подписей дат была внизу).

Настройка формата Оси Y

Теперь настройте вторую ось. Выделите легенду оси X (дата) и нажмите Ctrl+1 или правой кнопкой мыши — Форматировать ось. В открывшейся панели инструментов найдите вкладку Параметры оси. Установите минимальный предел (например, дату начала первого этапа). Если ваша версия Excel не позволяет ввести дату в обычном формате, вам придется ввести ее числовой эквивалент (чтобы узнать это, вы можете временно перевести ячейку, содержащую дату, в числовой формат в Excel, перезаписать значение, а затем восстановить его). Максимальный предел обычно достаточно хорошо определяется автоматически, но при необходимости его можно установить и вручную.

В этой же панели можно установить цену делений (7 — для недели, 10 — для декады и т.д.).

Настройка формата Оси X

Далее нужно спрятать лишние ряды (которые служат для визуализации времени начала этапов, см. рисунок ниже). Выделите ненужный ряд (кликнув на нем левой кнопкой мыши), нажмите Ctrl+1 или Правая кнопка мыши — Формат ряда данных. В открывшейся панели инструментов найдите вкладку Заливка и Границы и установите галочку "Нет заливки".

Лишние ряды выделены красной рамкой

Для остальных видимых линий необходимо настроить такое же заполнение (актуально при повторении одного и того же шага), а также настроить ширину полос на графике (формат данных линии — параметры линии — перекрытие линий: 100%, боковое пространство: 30%)

Формат видимых рядов

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

Итоговая диаграмма

Рассмотрим второй метод.

Способ 2. График Ганта через Условное форматирование

1. Организация данных для построения

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

Читайте так же:
Как защитить форматирование ячеек, но разрешить ввод данных только в Excel?

Организация данных

2. Создание правила условного форматирования

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

Выделенный диапазон

На вкладке "Главная" найдите "Условное форматирование" и нажав, выберите "Создать правило. ". Выберите тип правила: "Использовать формулу. ", после чего в строку, выделенную на рисунке ниже, нужно будет ввести формулу правила условного форматирования.

Строка ввода формулы для правила УФ

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

Разберем ее подробнее. Формулу начинается с функции ИЛИ. Она нужна тогда, когда Вы будете использовать повторяющиеся этапы в Вашем проекте. Для каждого такого этапа внутри функции ИЛИ есть отдельная конструкция с функцией И (в нашем случае один из этапов повторяется 2 раза, а значит и конструкций внутри "ИЛИ" будет 2).

Первая из них — И(G$1>=$C2;G$1<$C2+$D2). Эта часть формулы отвечает за заливку цветом первого повторения каждого этапа. G1 — ссылка на ячейку с датой (первая дата в шапке), а C2 и D2 — ячейки с датой начала и длиной этапа соответственно. Обратите внимание, что для G1 закреплена строка (так как шапка всегда будет в одной и той же строке), а для других ячеек — столбцы (так как столбцы будут те же, а вот строки мы будем перебирать). Формулу можно прочитать как:Идата в шапке больше или равна дате начала этапа,Идата в шапке меньше даты начала + длительности этапа (а дата начала + длительность = следующий день после окончания этапа). Если условие истинно, то дата в шапке находится в пределах этапа и ячейку нужно закрасить.

Вторая конструкция И(G$1>=$C2+$D2+$E2;G$1<$C2+$D2+$E2+$F2)) работает аналогично, только для даты начала этапа (а это уже будет не первый, а повторный запуск этапа) приходится суммировать дату начала первого раза, продолжительность, перерыв и т.д. Разумеется, Вы можете переорганизовать данные так, как Вам удобно и избежать подобной необходимости. Главное — понять принцип построения.

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

После ввода формулы нужно нажать кнопку "Формат" и задать способ выделения ячеек (в нашем примере мы применили заливку и штриховку ячейки). Нажимаете ОК и получаете результат.

Диаграмма Ганта на основе условного форматирования

Выбирайте подходящий Вам способ и пользуйтесь. Удачи в Ваших проектах!

Вы можете задать вопросы о статье через нашего бота обратной связи в Telegram: @ExEvFeedbackBot

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector