Как создать диаграмму с интерактивными подписями данных в Excel
Здравствуйте. Сегодня Вы узнаете об великолепной возможности в Excel, а именно как создать диаграмму, а точнее гистограмму, с динамическими подписями данных. Изучив этот урок Вы сможете создавать диаграммы, в которых будет возможность выбирать, какую информацию показывать на ней, без пересчета исходных данных.
Динамические подписи данных на гистограмме
Как создать график с динамическими подписями данных
Загрузите файл в качестве примера, что бы следовать за моими пояснениями. Внимание: Этот файл работает правильно в Excel 2013 или 2016.
Шаг 1: Создайте гистограмму с итоговыми данными
Первым шагом является создание обычной гистограммы с общими итогами над столбцами. Думаю, как ее создать Вы знаете 
Шаг 2: Расчет значений для подписей данных
Исходные данные для гистограммы выглядят как на скриншоте снизу. Нам нужно будет рассчитать различные значения для подписей данных.
Я создал раздел на листе для каждого измерения: Сумма, % от общего, и % изменения.
Это довольно легко, и я не буду вдаваться в детали каждого расчета.
Шаг 3: Используйте формулу ТЕКСТ для формирования подписей
Как правило, диаграмма отображает подписи данных на основе основного источника для диаграммы.
В Excel 2013 была введена новая возможность под названием «Значения из ячеек». Эта возможность позволяет указать диапазон, который мы хотим использовать для подписей данных.
Так как наши подписи данных изменяются между финансовым ($) и процентным (%) форматом, нам нужен способ, чтобы также изменить форматирование чисел на графике. В противном случае Excel будет отображать десятичное число, вместо процентов.
К счастью, мы можем использовать функцию ТЕКСТ для этого. Функция ТЕКСТ позволяет Вам взять число и указать формат, в котором Вы хотели бы, что бы оно отобразилось.
Функция ТЕКСТ на самом деле возвращает текст в ячейку, даже если он выглядит как число. Таким образом, мы можем использовать эту функцию в качестве источника наших подписей данных и гистограмма будет отображать правильное форматирование.
Шаг 4: Использование функции ВЫБОР
Используйте функцию ВЫБОР, для того что бы определить какие именно подписи данных необходимо отобразить на диаграмме.
У нас есть три раздела, которые содержат различные метрики. Далее мы создадим еще один раздел, который будет содержать информацию для подписей данных на гистограмме.
Нам нужно будет использовать функцию поиска, чтобы возвратить правильную метрику по выбору среза. Функция ВЫБОР отлично подходит для этого.
Функция ВЫБОР позволяет нам указать индекс (1,2,3. ) и она возвратит то значение, которое соответствует индексу.
Сейчас мы просто добавим ячейку, которая содержит номер индекса, и указывает на три метрики для каждого значения в формуле ВЫБОР.
Шаг 5: Настройка подписей данных
Следующим шагом является изменение подписей данных, чтобы они отображали значения из ячеек, сформированных нами при помощи формулы ВЫБОР.
Как я говорил ранее, мы будем использовать функцию «Значения из ячеек», которая есть в Excel 2013 или 2016, что бы сделать это проще.
Вам нужно выбрать на графике ряд подписей, затем нажать на кнопку «Выбрать диапазон» в меню Параметры подписи.
Затем выберите диапазон, который содержит подписи данных для этого ряда.
Повторите этот шаг для каждого ряда на гистограмме.
Шаг 6: Настройка сводной таблицы и среза
Последний шаг позволит сделать наши подписи данных интерактивными. Мы сделаем это при помощи сводной таблицы и среза. Исходные данные для сводной таблицы — это таблица на рисунке ниже.
Эта таблица содержит три варианта для различных подписей данных.
Она также включает в себя номер индекса, на который будет ссылаться формула ВЫБОР (см. шаг 4).
Создайте сводную таблицу. Добавьте Name, Index и Symbol в строки сводной таблицы.
Затем вставьте срез для поля Символ. Для этого кликните на сводную таблицу, зайдите в меню Конструктор и там нажмите кнопку Вставить срез.
Срез может быть отформатирован, чтобы соответствовать оформлению Вашего графика. Я изменил количество столбцов до 3, что бы показать кнопки среза по горизонтали.
Последний шаг состоит в том, что бы в ячейке С38 сослаться на значение индекса из сводной таблицы для корректной работы формулы ВЫБОР. Когда пользователь нажимает на кнопку среза, в ячейке С38 отразится индекс выбранного элемента сводной таблицы.
Формулы ВЫБОР автоматически отобразят подписи данных для выбранного среза.
Было проделано много шагов, что бы создать эту интерактивную гистограмму. К счастью, Вы можете скачать пример и модифицировать его для себя.
Вы можете перемещать срез в области диаграммы, а также добавить инструкции, что бы пользователь понимал, как это работает.
Спасибо за внимание. Какие другие подписи данных или виды диаграмм использовали бы Вы?
Пожалуйста, оставляйте свои комментарии ниже, а также вопросы и предложения.
Как сделать диаграмму в Excel по данным таблицы
Здравствуйте. Сегодня Вы узнаете об великолепной возможности в Excel, а именно как создать диаграмму, а точнее гистограмму, с динамическими подписями данных. Изучив этот урок Вы сможете создавать диаграммы, в которых будет возможность выбирать, какую информацию показывать на ней, без пересчета исходных данных.
