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

Старт:14.12.2024

Срок обучения:9 недель

Excel: базовый курс

Обучение Excel в Нетологии с нуля до профессионала. Освойте сводные таблицы, формулы и функции. Практический курс с сертификацией и примерами из реальной жизни. Запишитесь уже сегодня!

11 250 ₽22 500 ₽

937₽/мес рассрочка

Подробнее

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


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

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

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

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

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

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

Настройка динамических меток данных не так уж и сложна, но требует некоторой работы. Для этой диаграммы нам потребуются следующие компоненты:

• ТЕКСТ функция
• Функция ВЫБРАТЬ
• Сводная таблица
• Ломтик
• Гистограмма

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

Загрузка файла

Загрузите образец файла, чтобы следовать моим объяснениям. Примечание: этот файл отлично работает в Excel 2013 или 2016.
 

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

Первый шаг - создать обычную гистограмму с общими итогами над столбцами. Думаю, ты знаешь, как это сделать ;)

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

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

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

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

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

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

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

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

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

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

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

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

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

Функция SELECT позволяет нам указать индекс (1,2,3,...) и вернет значение, соответствующее этому индексу.

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


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

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

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

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


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

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

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

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

 Затем введите раздел для поля «Символ». Для этого щелкните сводную таблицу, перейдите в меню «Конструктор» и нажмите там кнопку «Вставить срез.


Раздел можно отформатировать в соответствии с внешним видом диаграммы. Я изменил количество столбцов на 3, чтобы отобразить кнопки горизонтального раздела.

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

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

Итог

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

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

Спасибо за внимание. Какие еще метки данных или типы диаграмм вы бы использовали?