Auditportal.ru

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

Как изменить порядок рядов диаграмм в Excel?

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

Когда я изучал статистику по коронавирусу (актуальную на момент написания этой статьи), я зашел на страницу новостей Яндекса с данными о случаях заболевания и излечения и нашел там довольно интересный график:

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

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

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

Условное форматирование с помощью дополнительных столбцов.

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

На словах это звучит сложно, но позвольте мне показать вам это на примере.

Условное форматирование: расширение источника данных

Зеленым выделена наша основная таблица с данными. Справа от нее — 4 столбца, по которым распределяются исходные значения, в зависимости от попадания в определенный диапазон:

  • Значения меньше 3000. Формула в ячейке C2: =If( B2 <3000; B2 ;ND () )
  • Значения больше 3000, но меньше 5000. Формула в ячейке D2: =If(AND( B2 >=3000; B2 <5000);B2;ND () )
  • Значения больше 5000, но меньше 7000. Формула в ячейке E2: =If(AND( B2 >=5000; B2 <7000);B2;ND())
  • Значения больше 7000. Формула в ячейке F2: =If( B2 >=7000 ; B2 ;ND() )

Если значение не попадает в какой-либо диапазон, то в соответствующем столбце выводится ошибка #Н/Д. Это нужно для того, чтобы «неправильные» значения не отображались на диаграмме. Если вам мешаются ячейки с #Н/Д, то могу предложить несколько вариантов:

  1. Включить классическое условное форматирование для ячеек, которое будет изменять цвет шрифта ячеек с ошибками на белый. Таблица будет выглядеть опрятнее.
  2. После построения диаграммы, скрыть столбцы с дополнительной частью таблицы. Изначально, диаграмма не будет отображать скрытые данные, но это решается установкой галочки «Показывать данные в скрытых строках и столбцах» в настройках (при выборе источника данных для диаграммы).
Читайте так же:
Как извлечь все символы, кроме первого / последнего, из строки в Excel?

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

Условное форматирование диаграммы с помощью дополнительной таблицы

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

Условное форматирование диаграммы с помощью VBA.

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

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

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

Запустив этот макрос, мы получим следующие результаты:

Условное форматирование: результат выполнения макроса VBA

Все столбцы со значением менее 700000 были залиты красным цветом, со значением более 900000 — зеленым, а в диапазоне от 700000 до 900000 — желтым.

Если очень хочется, можно усилить условное форматирование диаграммы с помощью :

  • Добавить больше условий
  • Добавить новые цвета к новым условиям
  • Создать форму VBA, в которой можно выбирать цвета из палитры и устанавливать диапазоны условий без изменения кода

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

Читайте так же:
Как назначить значение или категорию на основе диапазона чисел в Excel?

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

Надстройка SHTEM для Excel.

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

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

Условное форматирование в надстройке для Excel

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

Хочу напомнить, что скачать дополнение может любой желающий: сначала предоставляется 30-дневный пробный период, а затем, при необходимости, вы можете получить код для разблокировки доступа.

Условное форматирование: заключение.

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

Как создать смешанную диаграмму в Excel

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

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

Рис. 136.1. Эта диаграмма будет более эффективной, если сделать ее смешанной

Рис. 136.1 Эта диаграмма была бы более эффективной, если бы она была смешанной

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

Читайте так же:
Как найти конкретное значение в объединенных ячейках в Excel?

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

  1. Щелкните по строкам, которые вы хотите изменить.
  2. Выберите Работа с диаграммами ► Конструктор ► Тип ► Изменить тип диаграммы.
  3. В диалоговом окне Изменение типа диаграммы выберите значок, который представляет тип диаграммы для выбранного ряда.
  4. Если вы хотите использовать вторую вертикальную ось для преобразованного ряда, выделите ряд и нажмите Ctrl+1, чтобы открыть диалоговое окно Формат ряда данных; перейдите в раздел Параметры ряда и установите переключатель Построить ряд в положение Вспомогательная ось.

Рис. 136.2. Такая смешанная диаграмма отображает два ряда данных, величины которых различаются

Рисунок 136.2. Этот смешанный график показывает два ряда данных с разными значениями

Как видно на рисунке 136.1, этот график представлял собой стандартную гистограмму с двумя рядами. Щелчком мыши одна из строк (в данном примере «Осадки») была преобразована в линию. Помимо добавления дополнительной оси, я промаркировал оси, чтобы было понятно, какой масштаб используется для той или иной строки. Чтобы добавить названия осей, выполните команду Работа с диаграммами ► Макет ► Названия осей.

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

Рис. 136.3. Смешанная диаграмма, использующая отдельную вертикальную ось

Рис. 136.3. составная диаграмма с отдельной вертикальной осью

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

Рис. 136.4. Смешанная диаграмма объединяет пять типов диаграмм

Рисунок 136.4: Смешанная диаграмма объединяет пять типов диаграмм

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

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

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

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

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

  • Какова общая сумма ряда?
  • Какова доля каждого сегмента в ряду?
  • Каково процентное изменение по сравнению с предыдущим периодом?

Динамические подписи данных на гистограмме

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

Как создать график с динамическими подписями данных

  • Функция ТЕКСТ
  • Функция ВЫБОР
  • Одна сводная таблица
  • Один срез
  • Одна гистограмма

Пожалуйста, используйте приведенный ниже файл в качестве примера, чтобы следовать моим объяснениям. Excel 2013 и 2016 работают с этим файлом правильно.

Шаг 1: Создайте гистограмму с итоговыми данными

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

Шаг 2: Расчет значений для подписей данных

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

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

Я создал раздел на листе для каждого измерения: Сумма, % от общего, и % изменения.
Это довольно легко, и я не буду вдаваться в детали каждого расчета.

Шаг 3: Используйте формулу ТЕКСТ для формирования подписей

Согласно основному источнику для этого графика, на нем обычно отображаются подписи данных.

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

Подписи наших данных меняются между финансовыми ($) и процентными (%), поэтому нам нужен способ изменить форматирование чисел диаграммы. В противном случае Excel будет отображать десятичное число вместо процента.

К счастью, для этого мы можем использовать функцию TEXT. Функция ТЕКСТ позволяет ввести число и указать, как оно должно отображаться.

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

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

Шаг 4: Использование функции ВЫБОР

Используйте функцию SELECT, чтобы указать, какие подписи данных должны отображаться на графике.

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

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

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

В функции SELECT мы можем выбрать индекс (1,2,3). и она вернет значение, соответствующее этому индексу.

Теперь добавим ячейку, содержащую номер индекса и метрики для каждого значения в формуле ВЫБОР.

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

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

Шаг 5: Настройка подписей данных

Следующим шагом является изменение подписей данных, чтобы они отображали значения из ячеек, сформированных нами при помощи формулы ВЫБОР.
Как я говорил ранее, мы будем использовать функцию «Значения из ячеек», которая есть в Excel 2013 или 2016, что бы сделать это проще.
Вам нужно выбрать на графике ряд подписей, затем нажать на кнопку «Выбрать диапазон» в меню Параметры подписи.

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

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

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

Повторите этот шаг для каждого ряда гистограммы.

Шаг 6: Настройка сводной таблицы и среза

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

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

Эта таблица содержит три варианта для различных подписей данных.
Она также включает в себя номер индекса, на который будет ссылаться формула ВЫБОР (см. шаг 4).
Создайте сводную таблицу. Добавьте Name, Index и Symbol в строки сводной таблицы.

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

Затем вставьте срез для поля Symbol. Для этого щелкните меню Конструктор, а затем кнопку Вставить срез после щелчка на перекрестной вкладке.

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

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

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

Последним шагом будет ссылка на значение индекса из перекрестной таблицы в ячейке C38, чтобы формула SELECT работала правильно. Когда пользователь нажимает кнопку Cut, индекс выбранного элемента сводной таблицы отображается в ячейке C38.

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

Для создания этой интерактивной гистограммы было предпринято множество шагов. Вы можете скачать пример и изменить его в соответствии со своими потребностями.

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

Спасибо за внимание. Какие другие подписи данных или виды диаграмм использовали бы Вы?
Пожалуйста, оставляйте свои комментарии ниже, а также вопросы и предложения.

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