Как сделать двустороннюю линейчатую диаграмму

Диаграмма-торнадо в Microsoft Excel

В зарубежных источниках такой тип диаграммы назвают tornado-chart. От наших соотечественников я слышал варианты «диаграмма-горшок» и «диаграмма-бабочка» — у кого на что больше похоже 🙂 Такой тип диаграмм удобно использовать для наглядного сравнение двух наборов данных, например:

• мы и конкуренты по основным направлениям (товарам, услугам)
• количество мужчин и женщин разного возраста среди покупателей или зрителей
• этот год и прошлый год по основным KPI и т.д.

Строить такую штуку не очень сложно, но пара небольших засад на пути поджидает — давайте с ними разберемся.

За основу возьмем обычную линейчатую гистограмму с накоплением (Stacked Bar Chart) :

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

1. ввести минус 1 в любую пустую ячейку и скопировать ее
2. выделить числа в данных по одному из наборов (например, население по городу в диапазоне B5:B19)
3. нажать Ctrl+Alt+V, чтобы вызвать окно специальной вставки
4. выбрать опции Значения (Values) и Умножить (Multiply) и нажать ОК.

Теперь можно выделить всю нашу таблицу с данными (A4:C19) и строить линейчатую диаграмму с накоплением на вкладке Вставка (Insert) . Результат будет уже похож на то, что хотелось:

Для полноты картины лучше перенести вертикальную ось Y вправо до упора, чтобы она не мешалась посреди диаграммы. Для этого нужно щелкнуть правой кнопкой мыши по горизонтальной оси, выбрать команду Формат оси (Format Axis) и затем в группе Вертикальная ось пересекает вариант Максимальное значение оси (Maximum Value) .

Также может возникнуть желание спрятать на диаграмме минусы перед числами. Проще всего это сделать, если применить к ячейкам с исходными данными хитрый пользовательский формат, который не отображает знак «минус». Для этого нужно выделить исходные ячейки (B5:B19), открыть окно Формат ячейки и, выбрав вариант Все форматы (Custom) , ввести в поле Тип следующую маску:

Минусы исчезнут в ячейках и, как следствие, на диаграмме.

Ссылки по теме

• Как создавать свои пользовательские форматы в ячейках листа Excel
• Как построить диаграмму-водопад (Waterfall Chart)
• Как построить диаграмму «план-факт» в Excel

Секционные диаграммы

Представьте, что вам нужно наглядно визуализировать для отчета или презентации вот такую таблицу с данными по продажам в разных регионах-филилах компании:
Первой мыслью обычно бывает, особо не заморачиваясь, выделить столбцы с городами и числами и построить обычную гистограмму. Получается обычно что-то такое:
Результат, как легко сообразить, не блещет наглядностью. Города сливаются в единое целое, сильно затрудняя восприятие и сравнение стран между собой. Как же улучшить картину? Для начала исходную таблицу лучше немного преобразовать:
Что изменилось:

• Названия стран из колонки А копируются в заголовки столбцов
• Данные из одного общего столбца разносятся по разным — каждая страна теперь отдельно

Вроде бы, мелочи, но если теперь выделить всю таблицу (A1:G20) и построить гистограмму с накоплением на вкладке Вставка — Гистограмма (Insert — Stacked Chart) , то картина на выходе получается совсем другая:

Столбцы-города каждой страны теперь являются для диаграммы разными рядами данных и отображаются, соответственно, разными цветами. А выделение стран вместе с городами дает нам красивые сгруппированные двухуровневые подписи к оси X.

Диаграмма с горизонтально расположенными столбцами (Линейчатая или Bar Chart ) тоже в таком варианте смотрится приятно:

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

Сводная секционная диаграмма

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

. то нужно будет сначала построить сводную таблицу аналогичного вида, а уж потом по ней строить диаграмму.

Пойдем на небольшую хитрость: продублируем столбец со странами и дадим ему имя, например, Страна2 (т.к. Excel не позволяет использовать одинаковые имена столбцов при построении отчетов сводных таблиц, чтобы отличать столбцы друг от друга):

Теперь построим по нашим данным сводную таблицу на вкладке Вставка — Сводная таблица (Insert — Pivot Table) :

• Страну и Город закинем в область строк (Row Labels)
• Страну2 в область столбцов (Column Labels)
• Продажи — в область значений (Values)

Как легко догадаться, на выходе мы получаем тот же необходимый нам вид таблицы со «ступеньками» данных по каждой стране. Останется перейти на вкладку Параметры (Options) или Анализ (Analyze) и построить гистограмму с накоплением, аналогичную описанным выше:

Ссылки по теме

• Как построить гистограмму с переменной шириной столбцов
• Что такое диаграмма-торнадо и как ее построить в Excel
• Несколько вариантов построения диаграмм «план-факт»

покупка

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

Создайте двунаправленную гистограмму

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

1. Выберите весь диапазон данных, на основе которого вы создадите двунаправленную гистограмму, а затем щелкните Вставить > Вставить столбец или гистограмму > Кластерный бар.

2. Затем на рабочий лист вставляется столбчатая диаграмма с кластерами. Щелкните правой кнопкой мыши любую из полос и выберите Форматировать ряд данных из контекстного меню. Смотрите скриншот:

3. в Форматировать ряд данных панель, выберите Вторичная ось вариант под Варианты серий меню.

Теперь вы можете видеть, что на диаграмме добавлена ​​дополнительная ось, как показано на скриншоте ниже.

4. Выберите и щелкните эту дополнительную ось правой кнопкой мыши, а затем выберите Ось формата из контекстного меню.

5. в Ось формата панели необходимо настроить следующим образом.

5.1) В минимальный в поле укажите значение, противоположное максимальному значению.
Советы: Как Маximum здесь 7000, минимальное должно быть -7000.

5.2) Проверьте Значения в обратном порядке коробка. Смотрите скриншот:

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

6. Выберите и щелкните правой кнопкой мыши ось X, а затем выберите Ось формата из контекстного меню.

7. в Ось формата панель, измените Минимум и максимальная к тем же минимальным и максимальным значениям, которые мы указали для вторичной оси на шаге 5.

Теперь двунаправленная гистограмма четкая, как показано на скриншоте ниже.

8. Поскольку ось Y отображается почти посередине диаграммы, нам нужно переместить ее в левую часть диаграммы. Просто щелкните, чтобы выбрать ось Y, перейдите к Ось формата панель, а затем выберите Низкий из Этикетка Positiв раскрывающемся списке в Этикетки раздел. Смотрите скриншот:

9. Теперь вам нужно расширить планки. Выберите одну из серий на графике, перейдите к Форматировать ряд данных панели, а затем измените процент в Ширина зазора пунктом.
Повторите шаги, чтобы расширить планки с другой стороны.

Затем вы можете увидеть, что диаграмма отображается следующим образом.

10. Добавьте метки данных к столбцам. Выберите одну из серий на диаграмме, а затем нажмите «Элементы диаграммы»> «Метки данных»> «Внутри конца». Смотрите скриншот:
Повторите шаги, чтобы добавить метки данных в серию с другой стороны.

Удалите или измените заголовок диаграммы по своему усмотрению. Затем двунаправленная гистограмма готова, как показано на скриншоте ниже.

Легко создавайте двунаправленную гистограмму в Excel с помощью замечательного инструмента

Если описанный выше метод слишком нечеткий и требует много времени, здесь настоятельно рекомендуется Двунаправленная гистограмма полезности Kutools for Excel для вас. С помощью этой утилиты вы можете легко создать двунаправленную гистограмму в Excel всего за несколько кликов.

Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.

1. Нажмите Кутулс > Графики > Сравнение категорий > Двунаправленная гистограмма.

2. Во всплывающем Двунаправленная гистограмма диалоговом окне, укажите различные диапазоны в соответствующих полях и, наконец, нажмите кнопку OK кнопку.
Советы: Вы можете сначала выбрать весь диапазон данных, а затем применить эту функцию, тогда диапазоны будут распознаваться и отображаться в соответствующих полях автоматически.

Затем двунаправленная гистограмма готова, как показано на скриншоте ниже.

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

Статьи по теме:

• Создать пузырьковую диаграмму в Excel
• Сделайте две оси y в Excel

покупка

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

Создайте двунаправленную гистограмму в Excel

Предположим, вы хотите создать двунаправленную гистограмму в Excel на основе данных, как показано на скриншоте ниже, сделайте следующее.

1. Выберите весь диапазон данных, затем щелкните Вставить > Вставить столбец или гистограмму > Кластерный бар.

2. Затем создается столбчатая диаграмма с кластерами. Щелкните правой кнопкой мыши любую панель и выберите Форматировать ряд данных из меню, щелкнув правой кнопкой мыши.

3. в Форматировать ряд данных , выберите Вторичная ось опцию.

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

5. В открывшемся Ось формата панель настройте следующим образом:

5.1) Укажите новое минимальное значение в минимальный коробка;

Советы: Новое минимальное значение необходимо изменить в соответствии с фактическими данными в вашем диапазоне. В этом случае число 612 является максимальным значением в диапазоне, Excel автоматически указал число 700 в качестве максимальной оси, поэтому я указываю -700 в качестве минимальный ось.

5.2) Проверьте Наши ценности в обратном порядке коробка. Смотрите скриншот:

Теперь диаграмма отображается, как показано на скриншоте ниже.

6. Щелкните первую ось (под полосами), перейдите к Ось формата панели, а затем установите то же значение в минимальный поле как шаг 5.

Примечание: На этом этапе держите Значения в обратном порядке коробка снята.

Затем создается двунаправленная гистограмма, как показано на скриншоте ниже.

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

Изменить расположение вертикальной оси

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

Теперь вертикальная ось перенесена в левую часть полос. Смотрите скриншот:

Расширить бары

Здесь демонстрируются шаги по расширению столбцов на двунаправленной диаграмме.

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

2. Повторите этот шаг, чтобы расширить правые столбцы на диаграмме.
Теперь диаграмма показана ниже.

Добавьте метки данных к столбцам

1. Выберите левые столбцы на диаграмме, щелкните Дизайн (на Инструменты для диаграмм вкладка)> Добавить элемент диаграммы > Этикетки данных > Внутренний конец. Затем метки данных добавляются к каждой соответствующей панели, как показано на скриншоте ниже.

Советы: Чтобы изменить цвет шрифта добавленных меток данных, выберите их, а затем укажите цвет шрифта в Цвет шрифта раскрывающийся список под Главная меню.

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

Измените заголовок диаграммы или удалите его по своему усмотрению.

На этом двунаправленная диаграмма завершена.

Легко создавать двунаправленную гистограмму в Excel

Команда Двунаправленная гистограмма полезности Kutools for Excel может помочь вам быстро создать двунаправленную столбчатую диаграмму в Excel всего за несколько щелчков мышью, как показано ниже.
Скачайте и попробуйте прямо сейчас! 30-дневный бесплатный маршрут