Как сделать комбинированный график в Excel пошагово

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

Создание информативного комбо чарта в Excel

Главная задача выполнить гармоничное слияние разных элементов: столбцы, линии и точки однотипных значений в одном чарте. Приложение MS Excel предлагает нам множество инструментов для эффективного решения данной задачи. Более того имеется возможность стилизовать дизайн тонкими настройками цветов:

1. разные типы градиентных заливок;
2. прозрачность;
3. яркость;
4. позиционирование;
5. настройка тени;
6. эффекты свечения и др.

Рассмотрим все эти возможности на одном примере шаг за шагом.

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

По Техническому Заданию наш чарт должен быть интерактивным и способным сегментировать данные по нескольким условиям:

1. По годам.
2. По месяцам.
3. По департаментам.

Поэтому следует изначально правильно составить структуру таблицы с исходными данными:

Полную версию таблицы исходных данных скачайте в конце статьи.

Группировка данных на визуализации будет реализована интерактивными элементами управления – кнопки срезов фильтра из сводной таблицы. Следовательно, на основе исходных данных нам нужно еще создать сводную таблицу на листе «Control». Но сначала мы создадим именной диапазон, который будем использовать в качестве источника данных. Для этого:

1. На листе «Data» выделяем все исходные данные (просто поместите курсор Excel на любую ячейку таблицы и нажмите комбинацию горячих клавиш CTRL+A).
2. В поле NameBox введите имя для будущего именного диапазона – «mydata» и нажмите клавишу Enter для подтверждения.
3. Проверьте результат. В главном меню выберите опции: Formulas – Defined Names – Name Manager:

Теперь при создании сводной таблицы мы укажем в качестве источника именной диапазон «mydata». Таким образом нам будет легче обновлять данные в сводной таблице при необходимости.

Создание сводной таблицы для интерактивного комбо чарта

Сводную таблицу создаем на отдельном листе «Control» для организации порядка разделения данных и комфортной работы в документе Excel. Для этого поместите курсор Excel на любую ячейку таблицы и выберите инструмент: Insert – Tables – Pivot Table:

В диалоговом окне «PivotTable from table or range» заполняем два поля ввода так как показано выше на рисунке:

1. Table/Range: – указываем «mydata» – это ранее нами созданное имя для именованного диапазона ячеек A1:E193 таблицы на листе «Data» используемого в качестве источника данных.
2. Location: - указываем внешнюю ссылку на другой лист Control!$A$3 – это ссылка указывает место в документе Excel где будет находится созданная сводная таблица.

Чтобы указать место для вставки таблицы на другом листе «Control», следует предварительно отметить опцию «Existing Worksheet».

Дальше настраиваем поля сводной таблицы так как показано выше на рисунке. В поле «Rows» переносим заголовок столбца «Month», а в поле «Values» переносим заголовок столбца «Influx of New Clients». Готово!

Создание таблицы формул для динамического чарта

Заполняем данные на листе «Processing». Составляем таблицу как показано ниже на рисунке. Но столбец «Clients» должен быть заполнен формулами с внутренними и внешними ссылками на сводную таблицу. Заполняем целый диапазон столбца C3:C14 формулой.

Таким образом с помощью формулы мы ссылаемся на порядковый номер месяца (внутренняя ссылка A3) и на адрес сводной таблицы (внешняя ссылка Control!$A$3) с указанным именем соответственного заголовка столбца «Influx of New Clients». В результате получаем соответсвенные значения.

В столбце Clients будут использоваться значения ячеек C3:C14 для нижнего слоя чарта. Этот нижний слой не будет изменяться при выборке данных по месяцах. Но следующий слой будет динамически изменяемый при взаимодействии с пользователем визуализации. Его назовем «Layer 2» и подготовим исходные значения.

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

Выделяем диапазон существующей сводной таблицы A3:B16, копируем и вставляем ее копию в ячейку A20.

Затем нам нужно создать элемент управления. Не снимая выделения ячеек со второй новой созданной сводной таблицы выберите инструмент: Insert – Filters – Slicer.

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

Чтобы расположить кнопки среза горизонтально, а не вертикально нужно выбрать этот элемент и появится дополнительная вкладка меню «Slicer». В разделе «Buttons» указываем значение 12 в поле «Columns:». И соответственно изменяем ширину элемента на 6.57 в поле «Width» в разделе «Size».

Теперь копируем панель с кнопками среза и переносим его на лист «Processing». Затем заполняем следующий столбец «Layer 2» формулой по всем его ячейкам диапазона D3:D14.

Нам понадобиться еще один динамический слой данных на чарте для декоративного оформления дополнительной вспомогательной информацией и стилями. Поэтому создадим последний столбец «Decor» и заполним его диапазон E3:E14 формулой:

Создаем шаблон для чарта с абстракциями

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

Выберите инструмент: Insert – Illustrations – Shapes – Rectangle Rounded Corners.

Выберите левой кнопкой мышки сам прямоугольник и нажмите комбинацию горячих клавиш CTRL+1 чтобы вызвать дополнительную панель «Format Shape». Там задаем цвет фона черный #000000 и цвет линии светло-голубой #AADCD7.

Создаем динамический комбинированный чарт в Excel. Выделите диапазон ячеек B2:E14 на листе «Processing» и выберите инструмент: Insert – Charts - Insert Line or Area Chart – 2D Line.

Теперь выберите чарт и нажмите комбинацию горячих клавиш CTRL+1 чтобы вызвать дополнительную панель «Format Chart Area».

1. Сначала для объекта «Chart Area» удаляем фон и линию границы.
2. Нам необходимо переключится на серию данных столбца «Decor».

Настройка типов чартов для создания комбинированной композиции визуализации

Изменяем тип чарта для серии данных «Decor». Для этого, когда уже выбрана необходимая серия на чарте просто указываем на новый тип чарта в закладке меню Excel: Insert – Charts – Scatter.

Появились дополнительные оси X и Y. Это следует исправить. Выберите сам чарт чтобы вызвать дополнительную вкладку меню «Chart Design». Выберите инструмент: Chart Design – Type – Change Chart Type. Снимите флажок с опции «Secondary Axis» как показано ниже на рисунке.

В результате маркеры Scatter расположились на соответствующих им местах.

Теперь необходимо выбрать вторую серию данных «Layer 2». На дополнительной панели «Format Chart» (CTRL+1) выбираем серию данных второго столбца и указываем новый тип чарта выбрав инструмент: Insert – Charts – Clustered Column.

Пошаговое конструирование дизайна блока визуализации

Теперь у нас все типы визуализации настроены. Делаем оформление дизайна.

Выберите область чарта и с помощью кнопки с плюсом справа раскройте выпадающее меню опций чтобы снять флажки и удалить элементы: Gridlines, Legend.

Выберите первую серию данных «Clients» кликнув левой кнопкой мышки по кривой лини и нажмите CTRL+1, чтобы вызвать дополнительную панель «Format Data Series». Сделайте следующие настройки параметров как показано ниже на рисунке:

Параметры настройки цветов градиента кривой:

1. Угол 0.
2. Код первого цвета #8760DC.
3. Код второго цвета #02C582.

Теперь украшаем вторую серию данных столбца «Layer 2»:

Параметры настройки цветов градиента столбцов гистограммы:

1. Угол 90.
2. Параметры первого цвета: код #AADCD7, Position – 0%, Transparency – 78%, Brightness – 0%.
3. Параметры первого цвета: код #AADCD7, Position – 88%, Transparency – 30%, Brightness – -30%.

Теперь украшаем третью серию данных столбца «Decor»:

В результате получаем стильный дизайн для динамического комбо чарта Excel.

Скачать комбинированный график в Excel

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

Этот комбо чарт является одним лишь из десяти элементов визуализации дашборда. Его шаблон можно скачать в этой статье:

Дашборд для управления KPI планами в Excel.