Динамический график small-multiples в Excel скачать шаблон

График small-multiples в Excel представляет собой серию похожих мини-диаграмм, имеющих общую шкалу и оси. Эти диаграммы были популяризированы Эдвардом Туфте, который видел в них большой потенциал для представления данных. Small-multiples – маленькие кратные значения хороши, среди прочего, когда мы зависим от показа большого количества рядов данных, которые на одном графике выглядят неразборчивыми.

Преимущество графика small-multiples в Excel

Благодаря расположению панелей с небольшими коэффициентами пользователь диаграммы small-multiples может быстро сравнить представленные переменные, находя потенциальные закономерности между ними. Давайте проверим, как выполнить такую диаграмму в Excel!

Для создания small-multiples графика мы будем использовать исходные данные о изменении объема продаж по 5-ти магазинах, разбитых на периоды времени. Таблица с исходными данными статистики продаж в пяти магазинах за период с 2019-го по 2023 год:

Нам необходимо определить какие магазины фавориты и показывают рост продаж, а какие наоборот снижаются в продажах при сравнении за выбранный период времени по годам (например, с 2019г. по 2020г. или 2021-2023).

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

А представьте теперь как бы выглядел запутанно этот график для сети из 10-ти магазинов. Главная цель визуализации данных в Excel – это не просто представлять числовые данные в графическом виде, а правильно визуализировать информацию с большим объемом данных для быстрого принятия правильных решений с помощью визуального анализа. Поэтому для таких целей будем использовать тип графика small-multiples в Excel с информативными подписями данных.

Как сделать график small-multiples в Excel

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

Построение динамического графика small-multiples в Excel.
Создание информативных меток подписей данных.

Первая глобальная задача. Для решения первой задачи нам необходимо сначала транспонировать таблицу. Для этого выделяем диапазон ячеек A1:F6, копируем его и на втором листе вставляем в ячейку A1 через инструмент: «ГЛАВНАЯ»-«Буфер обмена»-«Специальная вставка» (Ctrl+Alt+V), где должна быть отмечена опция «транспонировать»:

В ячейках H9:H10 у нас будут находится входящие данные для управления графиком small-multiples с динамически изменяемым его содержимым. Для начала пусть будут это года за период 2019-2020. Делаем табличку будущего интерфейса управления графиком как показано ниже на рисунке:

Далее необходимо построить в диапазоне B9:F18 динамическую матрицу, которая позволит красиво распределить данные на графике. Чтобы быстро составить динамически изменяемую матрицу нам потребуется формула, благодаря которой показатели продаж из исходной таблицы будут распределятся по диагонали «квадратной матрицы». Но для корректной работы формулы потребуется 2 дополнительных числовых значения в ячейках A7:A8. Это могут быть любые числа, например, единицы:

Они послужат нам точкой отправления для образования распределения значений из исходной таблицы по диагонали в матрице. Для этого используем формулу по целому диапазону B9:F18:

Обратите внимание! В формуле используются смешанные ссылки на диапазоны B$2:B$6. Благодаря этому мы просто вставляем формулу в первую ячейку матрицы B9 и копируем ее протягивая в остальные ячейки диапазона B9:F18.

Как видно на рисунке, благодаря первым двум единицам A7:A8 и формуле в диапазоне матрицы B9:F18 все значения из исходной таблицы были распределены строго по диагонали.

В поставленной задаче нам необходимо визуально отобразить фаворитов магазинов и аутсайдеров. На графике мы отобразим их разными цветами: зелеными и красными – соответственно. Например, если в Магазине-2 в 2020-му году показатели продаж выше чем в 2019-ом – значит был рост в продажа за этот период времени и присваиваем ему зеленый цвет фаворита. В противном же случае – красный, как например в Магазин-1, ведь он аутсайдер в этот период продаж. Для автоматического определения и присваивания цвета сначала создадим формулу, которая будет определять какому магазину какой цвет на основе исходных данных таблицы. Формула прописана во всех ячейках под данными магазинов в диапазоне B7:F7:

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

Обратите внимание! В формулах дополнительных матриц снова используются смешанные ссылки на ячейки B$7.

Таким образом мы распределили данные из матрицы прототипа по цветам и по дополнительным матрицам для построения графиков.

Теперь все исходные данные подготовлены и можно приступать непосредственно к построению графика. Выделите диапазон дополнительной «зеленой» матрицы B20:F29 и выберите инструмент: «ВСТАВКА»-«Диаграммы»-«С областями»:

Чтобы настроить вид графика на свой лад активируйте его щелкнув по нему левой кнопкой мышки для вызова дополнительного меню где следует выбрать инструмент: «РАБОТА С ДИАГРАММАМИ»-«КОНСТРУКТОР»-«Выбрать данные»:

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

Теперь зададим свойственный первому графику зеленый цвет. Для этого следует снова вызвать дополнительное меню и выбрать на этот раз инструмент: «РАБОТА С ДИАГРАММАМИ»-«ФОРМАТ»-«Текущий фрагмент»-«Ряд1». За тем жмем на кнопку «Формат выделенного», которая находится в этом же разделе инструментов. И в появившемся диалоговом окне «Формат ряда данных» указываем в опциях «ПАРАМЕТРЫ РЯДА»-«ЗАЛИВКА»-«Цвет»-«Зеленый». Все эти действия повторяем для Ряд2, Ряд3, 4 и 5:

В результате первый зеленый график магазинов фаворитов готов! Теперь добавляем к нему второй красный график аутсайдеров. Для этого:

Выделяем «красную» дополнительную матрицу.
Копируем все ее содержимое.
Выделяем график и выбираем инструмент: «ГЛАВНАЯ»-«Буфер обмена»-«Вставить»-«Специальная вставка» (Ctrl+Alt+V):

В появившемся специфическом окне специальной вставки данных для новых рядов графика сделайте настройки так как показано выше на рисунке: «Добавить значения как»-«новые ряды» и «Значения (Y)»-«в столбцах». После чего меняем цвета всех добавленных столбцов (Ряд6-Ряд10) на красный и получаем готовый результат:

Информативные подписи с показателями изменений в процентах

Вторая глобальная задача. Теперь необходимо сделать информативные подписи для всех рядов графика, а легенду можно смело удалить. Сначала подготовим данные для подписей. Нам потребуется обратно транспонировать некоторые строки таблицы в столбцы. Более того этот процесс следует автоматизировать, а строки должны иметь возможность автоматически быть выбранными по условию пользователя. Для транспонирования строк в столбцы по условию используем формулу в диапазоне ячеек H2:H6:

А в диапазоне I2:I6 немного изменена формула:

Первая формула ссылается на начало выбранного периода (например, 2019г.) а вторая – на конец (например, 2020г.) во всем остальном эти две формулы идентичны.

Выбрав попарно показатели продаж на начало и конец указанного периода для каждого магазина мы теперь имеем возможность посчитать изменения в процентном соотношении. А с помощью функции ТЕКСТ эти проценты красиво оформим. Для этого в диапазоне ячеек J2:J6 используем формулу:

Также еще понадобятся координаты для размещения подписей в нужном нам месте на графике, а не там, где предложит Excel. Для X координат в диапазоне ячеек K2:K6 используем дробные числа: 1,5; 3,5; 5,5; 7,5 и 9,5. А для оси Y – формула в ячейках L2:L6:

Данные подписей подготовлены осталось добавить метки на график. Активировав кликом small-multiples график выберите инструмент: «РАБОТА С ДИАГРАММАМИ»-«КОНСТРУКТОР»-«Выбрать данные»

В окне «Выбор источника данных» нажмите на кнопку «Добавить» чтобы еще добавить 11-ый ряд данных из диапазона ячеек L2:L6:

Поле «Имя ряда:» мы оставляем пустым, а в полю «Значения:» указываем ссылку на диапазон ячеек $L$2:$L$6.

Далее изменяем тип диаграммы для Ряд11. Правой кнопкой мышки кликаем по ряду 11-му и вызываем опцию из контекстного мену: «Изменить тип диаграммы для ряда»:

В окне «Изменение типа диаграммы» для Ряд11 изменяем «Тип» на «Точечная»:

Снова открываем конструктор: «РАБОТА С ДИАГРАММАМИ»-«КОНСТРУКТОР»-«Выбрать данные» и вносим значения оси X для подписей то есть для Ряд11 как показано ниже на рисунке:

Благодаря точечной диаграмме в настройках 11-го ряда появилось дополнительное поле «Значения X». В нем мы указываем ссылку на соответствующий диапазон ячеек подписей: $K$2:$L$6.

Теперь добавим подписи на график, активируем его кликом и выбираем инструмент: «РАБОТА С ДИАГРАММАМИ»-«КОНСТРУКТОР»-«Макеты диаграмм»-«Добавить элемент диаграммы»-«Подписи данных»-«По центру». Затем выбираем другой инструмент для их настройки формата отображения: «РАБОТА С ДИАГРАММАМИ»-«ФОРМАТ»-«Текущий фрагмент»-«Ряд11 Подписи данных».

В дополнительном окне «Формат подписей данных» сначала отмечаем галочку «значения из ячеек» и с помощью кнопки «Выбрать диапазон» указываем на $J$2:$J$6 (где были заранее подготовленные данные для меток подписей). А чтобы скрыть точки точечной диаграммы выбираем Ряд11 и в его дополнительном окне настроек «Формат ряда данных» выбираем опцию в инструменте «параметры РЯДА»-«ПАРАМЕТРЫ МАРКЕРА»-«Нет»:

В завершающем этапе второй глобальной задачи на вкладке «ГЛАВНАЯ» задаем размер шрифта и белый цвет для текста подписей, не забыв их выделить предварительно одним кликом мышки. Также удаляем легенду выделив ее + клавиша «Delete» на клавиатуре и таким же образом убираем значения на оси X. Плюс ко всему убираем основные линии сетки чтобы получить в итоге стильный дизайн small-multiples графика:

Умный интерфейс управления графиком small-multiples в Excel

Для управления динамически изменяемого графика будем использовать выпадающие списки в ячейка ввода переменных значений, а именно H9 и H10. Мы будем использовать инструмент Excel проверку данных с выпадающим списком. Это даст нам возможность сделать защиту от неправильного ввода значений. Например, чтобы старший год не был в начале периода, а младший в конце. То есть начало: 2020, а конец: 2019 – это ошибка!!! Мы будем использовать имена диапазонов с формулами, которые не дадут возможность пользователю выбирать ошибочные значения для входных параметров визуального анализа продаж по магазинам.

Сначала создаем имя для возможности выбора опций при указании начального периода. Выберите инструмент: «Формулы»-«Определенные имена»-«Диспетчер имен». После чего нажмите на кнопку создать и заполните в окне «Создание имени»:

поле «Имя:» - года_начало;
выпадающий список «Область:» - опция «Книга»;
в поле «Диапазон:» вводим формулу:

таким же образом создаем и второе имя «года_конец» с уже другой формулой:

Теперь создаем выпадающие списки с использованием этих имен. Сначала переходить в ячейку H9 и выбираем инструмент: «ДАННЫЕ»-«Работа с данными»-«Проверка данных». И в появившемся диалоговом окне «Проверка вводимых значений» на вкладке «Параметры» указываем «Тип данных:»-«Список»:

А в поле ввода «Источник» используем прямую ссылку на имя =года_начало. Аналогичным образом создаем второй выпадающий список в ячейке H10 и там уже используем имя =года_конец. Результат готов:

Скачать график small-multiples в Excel

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