Как сделать гистограмму с группировкой в excel

Как сделать гистограмму с группировкой в excel

Есть исходные данные для построения диаграммы в обычном табличном виде:

Необходимо выполнить сравнение данных по каждому кварталу – значений Центра (Москва) с накопительным итогом значений Филиалов (Филиал А, Филиал Б, Филиал В):

Гистограмма с группировкой и гистограмма с накоплением вместе на одной диаграмме – возможно ли это?
Казалось бы какие сложности? Однако, даже при использовании плоских диаграмм так просто нет возможности создать комбинированную диаграмму с этими двумя видами гистограмм, т.к. это немного противоречит смыслу гистограммы с накоплением – ведь данные одного ряда должны строиться с суммарным итогом.
Для построения такой диаграммы необходимо подготовить особым образом исходную таблицу – разнести два сравниваемых ряда (Центр и Филиалы):

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

Оставьте комментарий!

На сообщение “Гистограмма с группировкой и гистограмма с накоплением: могут ли быть вместе?” комментариев 29

  1. TeoDor :
    17.05.2014 (22:10)

ещё раз убедился, что нужно хорошо понимать по каким данным какие диаграммы строятся

Хороший и главное простой способ построения данной диаграммы

Просто и элегантно. 😉 Спасибо.

была необходимость строить подобный график, но не смог догадаться о таком простом варианте

Супер. Мне помогло при построении диаграммы двух видов данных: Рынок (разбивка по видам продукта суммируются данные в одном столбике) — Компания (то же самое) и по годам. Спасибо огромное!

Cпасибо, мне очень пригодилось!

Да, присоединяюсь ко всем, кто выразил автору статьи свои благодарности!
Реально когда требуется сделать такое, то натыкаешься, что это внутренним функционалом экселя не предусмотрено…
Это Ваша идея или переводная статья с забугорных сайтов? В любом случае, спасибо за еще одно решение этой задачи (те решения, которые были мне известны чуть похуже)

Пожалуйста, Excel-Ok! Решение находила сама, чтобы помочь слушателям одной нефтяной компании 🙂

Увидев вашу статью, по-другому посмотрел на диаграммы. Теперь быстро строю подобные и не мучаюсь.

Теперь в метро я провожу время с пользой — читая Ваши статьи. И время пролетает быстро и с пользой! А Диаграммы — это мой хлеб 🙂

Вау! Какую нужную для меня вещь я откопала! 🙂

Была такая задача — решить не смог. Оказывается, не смог догадаться.

Интересный вариант, хотя сразу и не очевиден.

Интересное решение, жаль, что узнал поздно

а как сделать так чтобы столбцы первого квартала были без промежутка? (далее промежуток и 2 квартал и т д).

Это боковой зазор — он между всеми столбцами ряда одинаковый

Очень здорово! И ведь ничего сложного нет! Спасибо!

Лучше один раз увидеть (прочесть), чем долго думать самой. Спасибо!

Добрый день! Гистограмма что надо! При ее построении, столкнулась с проблемой: там где у вас кварталы, нет возможности объединения двух ячеек в одну. Подскажите, как вы объединили?

Обратите внимание на расположение исходных данных!

Интересное сочетание несочетаемого. Сам не догадался

И ведь не сразу догадаешься о таком простом решении. Воспользовалась и теперь счастлива!

В новом офисе есть другое решение или нету?

В новых офисах ничего подобного не появилось

Оказывается, как легко можно построить, если продумать расположение исходных данных. Прекрасно!

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

Читайте также:  Как обновить клавиатуру самсунг

Построение гистограммы

Гистограмму в Экселе можно создать тремя способами:

    • С помощью инструмента, который входит в группу «Диаграммы»;
    • С использованием условного форматирования;
    • При помощи надстройки Пакет анализа.

Она может быть оформлена, как отдельным объектом, так и при использовании условного форматирования, являясь частью ячейки.

Способ 1: создание простой гистограммы в блоке диаграмм

Обычную гистограмму проще всего сделать, воспользовавшись функцией в блоке инструментов «Диаграммы».

    Строим таблицу, в которой содержатся данные, отображаемые в будущей диаграмме. Выделяем мышкой те столбцы таблицы, которые будут отображены на осях гистограммы.

Все простые диаграммы расположены с левой части списка.

После того, как выбор сделан, на листе Excel формируется гистограмма.

С помощью инструментов, расположенных в группе вкладок «Работа с диаграммами» можно редактировать полученный объект:

  • Изменять стили столбцов;
  • Подписывать наименование диаграммы в целом, и отдельных её осей;
  • Изменять название и удалять легенду, и т.д.

Способ 2: построение гистограммы с накоплением

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

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

Выделяем таблицу, на основании которой будет строиться гистограмма. Во вкладке «Вставка» кликаем по кнопке «Гистограмма». В появившемся списке диаграмм выбираем тот тип гистограммы с накоплением, который нам требуется. Все они расположены в правой части списка.

  • После этих действий гистограмма появится на листе. Её можно будет отредактировать с помощью тех же инструментов, о которых шёл разговор при описании первого способа построения.
  • Способ 3: построение с использованием «Пакета анализа»

    Для того, чтобы воспользоваться способом формирования гистограммы с помощью пакета анализа, нужно этот пакет активировать.

      Переходим во вкладку «Файл».

    Кликаем по наименованию раздела «Параметры».

    Переходим в подраздел «Надстройки».

    В блоке «Управление» переставляем переключатель в позицию «Надстройки Excel».

    В открывшемся окне около пункта «Пакет анализа» устанавливаем галочку и кликаем по кнопке «OK».

    Перемещаемся во вкладку «Данные». Жмем на кнопку, расположенную на ленте «Анализ данных».

    В открывшемся небольшом окне выбираем пункт «Гистограммы». Жмем на кнопку «OK».

  • Открывается окно настройки гистограммы. В поле «Входной интервал» вводим адрес диапазона ячеек, гистограмму которого хотим отобразить. Обязательно внизу ставим галочку около пункта «Вывод графика». В параметрах ввода можно указать, где будет выводиться гистограмма. По умолчанию — на новом листе. Можно указать, что вывод будет осуществляться на данном листе в определенных ячейках или в новой книге. После того, как все настройки введены, жмем кнопку «OK».
  • Как видим, гистограмма сформирована в указанном вами месте.

    Способ 4: Гистограммы при условном форматировании

    Гистограммы также можно выводить при условном форматировании ячеек.

    1. Выделяем ячейки с данными, которые хотим отформатировать в виде гистограммы.
    2. Во вкладке «Главная» на ленте жмем на кнопку «Условное форматирование». В выпавшем меню кликаем по пункту «Гистограмма». В появившемся перечне гистограмм со сплошной и градиентной заливкой выбираем ту, которую считаем более уместной в каждом конкретном случае.
    Читайте также:  Как взломать любой пароль на телефоне

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

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

    Отблагодарите автора, поделитесь статьей в социальных сетях.

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

    Гистограмма с группировкой и с отображением разницы:

    Линейчатая диаграмма с группировкой и с отображением разницы:

    Обзор

    Гистограмма с группировкой или линейчатая диаграмма с группировкой оказываются отличным решением, когда нужно сравнить два ряда данных по множеству категорий. В примере, приведённом выше, мы сравниваем План (Budget) и Факт (Actual) по множеству регионов. Стандартная диаграмма с группировкой показывает итоги в каждом ряду по категориям, но не даёт информации о разнице. Пользователь вынужден рассчитывать разницу самостоятельно.

    Однако, используя некоторые продвинутые приёмы создания диаграмм, разницу можно легко отобразить на графике.

    Требования к данным

    Для любой диаграммы очень важным является создать правильную структуру данных прежде, чем приступать к построению графика. На рисунке ниже показано, как данные должны быть организованы на рабочем листе. Это форма простого отчёта, в котором один столбец содержит имена категорий (Region) и два столбца отведено для рядов данных (Budget и Actual).

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

    Требования к диаграмме

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

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

    Как это делается

    Вычисление данных

    Первым делом добавляем в таблицу с данными три столбца с расчётами.

      Точка отсчётаразницы (Base Variance) – точка отсчёта для построения разницы рассчитывается, как минимальное значение из двух наборов данных в каждой строке таблицы.

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


    Положительная разница (Positive Var) – рассчитывается, как разница между рядом 1 и рядом 2 (факт и бюджет), и изображается на графике, как положительный результат.

    Условие ЕСЛИ (IF) возвращает пустое значение, если разница отрицательна. Пустое значение и подпись для него не будут показаны на графике.


    Отрицательная разница (Negative Var) – такое же простое вычисление, как и в случае с положительной разницей, но для того, чтобы получить положительное значение при отрицательной разнице, мы использовали функцию вычисления модуля числа ABS.

    Отрицательная разница должна быть построена на графике, как положительная величина, чтобы заполнить разрыв между двумя рядами данных. Вычисление её в отдельной колонке позволит нам задать для отрицательных значений другой цвет заливки, чтобы пользователь мог легко отличить их от положительных значений разницы.

    Читайте также:  Как изменить инженерное меню

    Как создать диаграмму

    Создание диаграмм с накоплением и с группировкой происходит одинаково. Построить диаграмму достаточно просто, и на её примере можно изучить несколько интересных приёмов.

    1. Первым делом создаём гистограмму с накоплением и строим в ней пять рядов данных.
    2. Ряд 1 (Actual) и Ряд 2 (Budget) должны быть построены по вспомогательной оси. Кликните правой кнопкой мыши по столбцу ряда данных Actual на графике и нажмите Формат ряда данных (Format Data Series).В разделе настроек Параметры ряда (Series Options) поставьте флажок Построить ряд (Plot Series On) в положение По вспомогательной оси (Secondary Axis).

    Повторите эту операцию для ряда данных Budget.
    Для рядов данных 1 и 2 измените тип диаграммы на Гистограмма с группировкой (Clustered Column). Выделите ряд данных Actual на графике или в выпадающем списке Элементы диаграммы (Chart Elements), который находится на вкладке Макет (Layout) в разделе Текущий фрагмент (Current selection). Диаграмма должна быть выделена, чтобы группа вкладок Работа с диаграммами (Chart Tools) появилась на Ленте.На вкладке Конструктор (Design) нажмите кнопку Изменить тип диаграммы (Change Chart Type) и выберите тип Гистограмма с группировкой (Clustered Column).

    С этого момента можно видеть, как наша диаграмма начинает приобретать нужную форму. Ряды данных Actual (Факт) и Buget (План) теперь показаны в виде столбцов, расположенных рядом для удобства сравнения. Ряд данных, отображающий разницу, виден позади, как столбец с накоплением.

  • В диалоговом окне Формат ряда данных (Format Data Series) в разделе Параметры ряда (Series Options) настройте Боковой зазор (Gap Width) для обоих графиков. Этот параметр регулирует зазор между столбцами. Уменьшите его значение, и ширина столбцов увеличится, а зазор между категориями уменьшится.
  • Настраиваем формат диаграммы. Настройки форматирования, заданные по умолчанию, выглядят не очень привлекательно. Мы можем кое-что исправить, чтобы сделать нашу диаграмму более презентабельной:
    • Переместим легенду в верхнюю часть области построения диаграммы и удалим из нее 3 позиции с разницей.
    • Добавим название диаграммы.
    • Удалим подписи осей.
    • Настроим цвета границы и заливки для столбцов.
    • Удалим горизонтальные линии сетки.

    • Добавляем подписи данных. Столбцы с разницей в таблице данных отформатированы так, чтобы вместо нулей отображалась пустая ячейка:

      При таких настройках пустые ячейки не отображаются на графике, что придаёт ему аккуратный вид. В противном случае столбцы с разницей равной нулю имели бы подписи данных.

      Подписи данных для гистограммы с накоплением не имеют опции, которая отображала бы их над графиком, поэтому придётся вручную переместить подписи вверх и влево или вправо от столбца.

      Подведём итог

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

      Что думаете о прочитанном? Может быть Вы используете другой тип диаграммы для отображения разницы?

      Ссылка на основную публикацию
      Как связаться с инстаграм по телефону
      В этой статье расскажем о том, как работает горячая линия линия Инстаграмм, можно ли обратиться по телефону или доступен только...
      Как разблокировать телефон samsung galaxy j1 mini
      Характеристики Samsung Galaxy J1 mini Отзывы о Samsung Galaxy J1 mini Инструкция Samsung Galaxy J1 mini Прошивка Samsung Galaxy J1...
      Как разблокировать флешку от защиты записи
      Извиняюсь за заголовок, но именно так задают вопрос, когда при действиях с USB флешкой или SD картой памяти Windows сообщает...
      Как связаться с инстаграмом
      В этой статье расскажем о том, как работает горячая линия линия Инстаграмм, можно ли обратиться по телефону или доступен только...
      Adblock detector