Как создать комбинированную гистограмму в Excel пошагово
Рассмотрим практический пример создания и эффективного использования комбинированного чарта в Excel. Интерактивная презентация визуализации данных с помощью комбинированного чарта позволяет эффективно выполнять сравнительный анализ однотипных значений. А также дает обратную связь для пользователей дашборда, информативно визуализируя границы выборки данных из учетного периода. Лучше один раз увидеть, чем 100 раз прочитать.
Как сделать интерактивную комбинированную гистограмму в Excel
У нас имеется таблица исходных данных о ежемесячных продажах и расходах на протяжении года:
Необходимо построить комбинированный чарт с выборкой данных о продажах и функцией визуализации сравнения выбранных периодов с общей картиной в целом.
Создание сводной таблицы с исходных данных
Для начала создадим сводную таблицу. Поместите курсор Excel на любую из ячеек таблицы с исходными значениями и выберите инструмент: Insert – Tables – PivotTable.
В появившемся окне «PivotTable from table or range» в разделе настроек «Choose where you want the PivotTable to be placed» переключите на опцию «Existing Worksheet» и в поле ввода «Location:» укажите ссылку на ячейку текущего рабочего листа: Sheet1!$A$17.
В результате мы добавили на текущий лист сводную таблицу. Теперь нам необходимо настроить ее поля данных.
Добавим еще один столбец к исходной таблице и назовем его «Sales Level 2». Данный столбец следует заполнить формулой:
=IFERROR(GETPIVOTDATA("Sales",$A$17,"Name Months",B2),NA())
С помощью формулы мы заполняем столбец только теми значениями, которые содержит сводная таблица. Если нет значения возвращается #N/A. Сейчас сводная таблица не использует фильтры выборки данных и все ее значения соответствуют исходным.
Создание интерактивного элемента управления чартом
Создадим элемент управления выборкой данных с помощью фильтрации значений в сводной таблице используя инструмент Slicer. Для этого поместите курсор Excel на любую ячейку сводной таблицы и выберите инструмент: Insert – Filters – Slicer.
Теперь мы имеем возможность управлять выборкой данных в сводной таблице с помощью среза. Обратите внимание как ведет себя формула в исходной таблице в столбце «Sales Level 2».
Создание шаблона комбинированной гистограммы
Переходим к созданию шаблона комбинированного чарта в Excel. Выделите диапазон ячеек в исходной таблице B1:E13 и выберите инструмент: Insert – Charts – 2-D Column.
Теперь выберите ряд данных «Costs» на чарте и задайте ему новый тип используя инструмент: Insert – Charts – 2-D Line.
В результате получилась комбинация их двух типов чартов. Переходим к дальнейшим настройками визуализации. Выберите любую серию данных из столбчатой гистограммы («Sales» или «Sales Level 2») и нажмите комбинацию горячих клавиш CTRL+1 чтобы вызвать дополнительное окно параметров чарта «Format Data Series». В разделе «Plot Series On» заполните два поля ввода следующими значениями:
- Series Overlap: 100%.
- Gap Width: 150%.
Добавим для комбинированного чарта еще один ряд данных для еще одного типа. Выделите одним кликом левой кнопкой мышки чарт для вызова дополнительных вкладок в главном меню Excel и выберите инструмент: Chart Design – Data – Select Data.
В появившемся окне «Select Data Source» в разделе «Legend Entries (Series)» нажмите на кнопку «Add» для вызова дополнительного окна добавления ряда «Edit Series» и заполните два поля ввода:
- Series name: Decor.
- Series values: =Sheet1!$E$2:$E$13.
Теперь комбинированный чарт будет использовать 4 ряда данных и 3 типа визуализации. Выберите последний ряд данных «Decor» и задайте ему новый тип чарта выбрав инструмент: Insert – Charts – Scatter.
Добавим для последнего типа чарта несколько декоративных и функциональных настроек. Сначала выберите ряд данных «Decor» и нажмите плюс возле области чарта для вызова контекстного меню опций. После чего поставьте флажок на против опции «Data Labels - Above».
В результате добавились подписи только для выбранных данных.
Добавим последний элемент декорации. Для этого выполним целый ряд действий:
- Поставьте флажок на против «Error Bars» и выберете опцию из выпадающего меню «More Options».
- В появившемся окне «Format Error Bars» нажмите на «Error Bar Options» и из выпадающего списка выберите опцию «Series Decor Y Error Bars».
- В разделе «Error Amount» отметьте пункт «Custom» и нажмите на кнопку «Specify Value».
- В появившемся дополнительном окне параметров «Custom Error Bars» в поле ввода «Negative Error Value» введите адрес ссылки на диапазон ячеек: =Sheet1!$E$2:$E$13 и намжите ок.
В результате добавились декоративные линии, которыми можно украсить стиль оформления комбинированного чарта.
Интерактивный дизайн для визуализации данных
Далее цветами окрашиваем все элементы целого блока визуализации данных как показано выше на видео.
Скачать комбинированную гистограмму в Excel с интерактивным дизайном 
Используя настройки цветовых стилей срезов данных для сводных таблиц Excel, можно создавать красивые кнопки. Важно отметить их функционал, предназначенный для комфортной выборки значений из базы данных. Когда одновременно могут быть зажато несколько кнопок, а также работать в режиме переключателей. Это очень помогает в процессе визуального анализа данных для дашбордов, презентаций или интерактивных отчетов. Вся интерактивность автоматической фильтрации значений реализована без использования макросов.
Например, как на таком красивом дашборде:
Дашборд для управления KPI планами в Excel.
