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

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

Рассмотрим, как применять формулы и условное форматирование в диаграммах Excel.

Примеры формул в диаграммах

Построим на основе ряда данных простой график с маркерами:

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

Аргументы функции РЯД:

  1. Имя (название ряда данных, отображается в легенде; не обязательный аргумент);
  2. Подписи категорий (метки, появляющиеся на оси категорий; не обязательный аргумент);
  3. Значения (которые применяются для построения графика; обязательный параметр);
  4. Порядок (порядок значений в ряду данных; обязательный параметр).

Аргументы функции РЯД можно найти и изменить в диалоговом окне «Выбрать данные»:

Выделим элемент легенды «y» и щелкнем по кнопке изменить. В поле «Имя ряда» содержится аргумент функции «Имя»:

Название ряда данных – «y». Его можно менять.

В поле «Значения» – аргумент значений ряда данных.

Подписи горизонтальной оси – это аргумент функции РЯД «Подписи категорий»:

Так как наш график построен на основе одного ряда данных, то порядок равняется единице. Данный аргумент отражается в списке «Элементы легенды».

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

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

Создание динамических диаграмм

Для имеющейся исходной таблицы с данными создадим именованные диапазоны: для первого столбца – категорий – «х»; для второго – точек данных – «у».

Открываем вкладку «Формулы» – нажимаем кнопку «Диспетчер имен».

В диалоговом окне жмем «Создать». Откроется окно «Создание имени». В поле «Имя» вводим имя диапазона. В поле «Диапазон» – формулу для ссылки на данные в первом столбце (=СМЕЩ(Лист1!$A$1;1;0;СЧЁТЗ(Лист1!$A$1:$A$20)-1;1)).

Читайте также:  Чем открыть файл ozf2 на компьютере

Чтобы заголовок ряда данных не включался в именованный диапазон, за аргументами функции СЧЕТЗ ставим «-1». В качестве диапазона можно указывать весь столбец А – Excel быстро определяет пустые ячейки. В примере мы поставили лишь первые 20 ячеек.

Создаем именованный диапазон для второго столбца. По такому же принципу.

Теперь поменяем ссылки на ряд данных в графике именами динамических диапазонов. Вызываем диалоговое окно «Выбор источника данных». Выделяем элемент легенды и нажимаем «Изменить». Меняем ссылки в поле «Значения» на имя диапазона.

Далее жмем «Изменить подписи горизонтальной оси». Задаем для диапазона назначенной имя.

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

При работе с огромным массивом данных иногда нужно создать диаграмму только на основе некоторого количества последних значений в ряду. Чтобы формула выбирала только их, при формировании динамического именованного диапазона прописываем следующее: =СМЕЩ(Лист1!$A$1;СЧЁТЗ(Лист1!$A$1:$A$1000)-40;0;40;1). По такому же принципу – для столбца В.

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

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

Данный инструмент достаточно просто используется для обычных данных. Для диаграмм в Excel применить встроенное условное форматирование невозможно. Нужно идти другим путем.

Зачем это? Для улучшения восприятия информации. При изменении значений в исходных ячейках автоматически будет меняться цветовое исполнение диаграммы.

Выполнить условное форматирование в диаграммах можно с помощью макросов и формул. Рассмотрим второй способ.

На основании тех же исходных данных составим гистограмму:

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

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

Для каждого условия создадим отдельный ряд данных. Значения в исходной таблице находятся в диапазоне от 0,06 до 5,7. Создадим ряд для периодов 0-0,6; 0,6-1,6; 1,6-3; 3-4,6; 4,6-6.

Читайте также:  Фокус с вилкой гнется секрет

Сформируем данные для гистограммы с условным форматированием. Диапазон условий внесем в строки 1 и 2. Заголовки – в строку 3. Формулы для заголовков:

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

Источник данных для гистограммы – столбцы А и В. Нужно исключить колонку В и добавить вновь созданный диапазон С:F.

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

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

Идея состоит в использовании условного форматирование, чтобы заставить Excel заливать ячейку заданным цветом, если она по дате попадает между началом и концом этапа. Для этого выделите весь диапазон, где должна быть диаграмма (в нашем примере – начиная с ячейки D3 и до конца таблицы) и затем:

    в Excel 2003 и старше: выберите в меню Формат – Условное форматирование – Формула (Format – Conditional Formatting – Formula) и введите в появившуюся строку следующую формулу:

    в Excel 2007 или новее: жмем на вкладке Главная (Home) кнопку Условное форматирование – Создать правило (Conditional Formatting – New Rule) , выбираем последний тип Использовать формулу для определения форматируемых ячеек (Use a formula to determine which cells to format) и вводим аналогичную формулу:

По сути, эта формула делает простую вещь – функция И (AND) проверяет обязательное выполнение двух условий, чтобы дата для текущей ячейки была позже, чем дата начала этапа и раньше даты окончания. Если оба эти условия выполняются, то ячейка находится внутри этапа, т.е. должна быть залита. Нажав на кнопку Формат (Format) можно выбрать необходимый цвет.

Просто и красиво, не правда ли?

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

Читайте также:  Что делать если visual studio не устанавливается

Основа здесь все та же – условное форматирование. Плюс добавлены:

  • задержка перед началом этапа (может быть положительно или отрицательной или =0)
  • проценты выполнения по каждому этапу с их подсветкой на диаграмме
  • функция РАБДЕНЬ (WORKDAY) для расчета не календарных, а рабочих дней
  • подсветка праздников и выходных все тем же условным форматированием с помощью функции ДЕНЬНЕД (WEEKDAY)
  • координатная подсветка текущей строки и столбца с помощью специального макроса

Microsoft Project нам так, конечно, не переплюнуть, но приблизиться к нему в визуальной части вполне можно :)

Проблемы с отображением видео:

Диаграмма с помощью условного форматирования.

Когда-то давно "нарисовал" диаграмму используя условное форматирование. Недавно показал на тренинге – народу понравилось. Может быть и вам пригодится.

Файлы для скачивания:
Файл Описание Размер файла: Скачивания
Пример 7 Кб 1115

Итак, собственно говоря из-за чего весь сыр бор:

Чтобы увидеть видео включите в браузере GIF-Анимацию.

Давайте приступим. Для начала рисуем такую таблицу:

Далее выделяем диапазон ячеек "F4:M8" в главном меню переходим во вкладку "Главная", в разделе "Стили" нажимаем кнопку "Условное форматирование", в выпавшем списке выбираем пункт "Создать правило":

В открывшемся окне, в списке "Выберите тип правила" выбираем пункт "Использовать формулу для определения форматируемых ячеек". В разделе "Измените описание правила" в строку формул вбиваем такую формулу "=$E4>=F$3". Устанавливаем формат. Я установил градиентную заливку от "белого" к "красному". Нажимаем кнопку "ОК":

Повторяем операцию для диапазона "N4:O8" и "P4:Q8" только назначаем им другие цвета. В итоге всех манипуляций у вас должно получиться три правила:

После того как правила созданы вводим значения выполнения плана в диапазон "D4:D7" и наблюдаем как MS Excel закрашивает ячейки.

Оцените статью
Добавить комментарий

Adblock detector