Как сгруппировать графики в экселе

Обновлено: 04.07.2024

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

Настраиваем параметры функции
Группируем данные по строкам
Группируем столбцы
Создаем многоуровневую группировку
Разгруппировываем данные
Заключение

Настраиваем параметры функции

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

Группируем данные по строкам

Для начала давайте рассмотрим, как можно сгруппировать строки:

Вставляем новую строку над или под строками, которые хотим сгруппировать (зависит от того, какой вид расположения итогов по строкам мы выбрали). Как это сделать, читайте в нашей статье – “Как добавить новую строку в Excel“.
В самой левой ячейке добавленной строки пишем название, которое хотим присвоить группе.
Любым удобным способом, например, с помощью зажатой левой кнопки мыши производим выделение ячеек строк (кроме итоговой), которые требуется сгруппировать. Во вкладке “Данные” щелкаем по кнопке “Структура” и в открывшемся списке выбираем функцию “Группировать”. Щелкнуть нужно именно по значку команды, а не по ее названию. Если же нажать на последнее (со стрелкой вниз), откроется еще одно подменю, в котором следует нажать на одноименную кнопку.
В появившемся окошке отмечаем пункт “строки” (должен быть выбран по умолчанию) и подтверждаем действие нажатием OK. Примечание: Если вместо ячеек выделить все строки целиком на вертикальной панели координат, а затем применить группировку, то промежуточного окна с выбором строки или столбца не будет, так как программа сразу понимает, что именно ей необходимо сделать.

Группируем столбцы

Чтобы сгруппировать столбцы, придерживаемся примерно такого же алгоритма действий, описанного выше:

Создаем многоуровневую группировку

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

Разгруппировываем данные

Когда ранее выполненная группировка столбцов или строк больше не нужна или требуется выполнить ее иначе, можно воспользоваться обратной функцией – “Разгруппировать”:

Заключение

Группировка данных выполняется в несколько кликов и не требует особых навыков в работе с программой, однако, данный прием позволяет существенно сэкономить время, когда приходится иметь дело с большим объемом информации. Это делает функцию одной из самых полезных и незаменимых в Excel.

Построение диаграмм с одним рядом данных рассмотрено в статье Основы построения диаграмм в MS EXCEL . Начинающим пользователям также целесообразно перед прочтением ознакомиться со статьей Основные типы диаграмм .

ГИСТОГРАММА

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

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

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

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

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

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

Другой возможностью отображения на диаграмме двух рядов со значительно различающимися значениями, является масштабирование самих значений в таблице-источнике.

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

Теперь изменим подписи по горизонтальной оси (категории).

В окне Выбор источника данных видно, что для обоих рядов подписи горизонтальной оси одинаковы, т.к. категории одинаковы для обоих рядов (столбец Месяц).

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

При желании это также можно изменить. В окне Выбор источника данных выделите ряд Прибыль, нажмите кнопку Изменить расположенную справа, удалите ссылку на ячейки. Теперь у ряда Прибыль вместо названия категорий будут просто порядковые числа 1, 2, 3,… Однако, они не будут отображаться на диаграмме, т.к. отображается пока только Основная горизонтальная ось .

Теперь в меню Оси (вкладка Макет , группа Оси ) выберите Вспомогательная горизонтальная ось и установите ее Слева направо . В окне формата Вспомогательной вертикальной оси измените точку пересечения оси (установите Автовыбор ). Получим вот такую диаграмму.

Хотя техническая возможность отображения 2-х различных категорий существует, конечно, таких диаграмм нужно избегать, т.к. их сложно воспринимать. Категории должны быть одинаковыми для всех рядов на диаграмме. Естественно, такой трюк сработает только для двух групп рядов, т.к. имеется всего 2 типа оси: основная и вспомогательная.

ГРАФИК

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

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

Примечание . Если провести горизонтальные линии сетки к вспомогательной оси, то они, естественно, могут не совпасть с линиями к основной оси, т.к. масштабы осей (величины основных делений вертикальных осей) могут не совпадать. Это может «перегрузить» диаграмму.

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

ТОЧЕЧНАЯ

Визуально Точечная диаграмма похожа на диаграмму типа График (если конечно у Точечной диаграммы точки соединены линиями).

Различие состоит в том, что для построения Графика в качестве координаты Х всегда используется последовательность 1, 2, 3, … (с подписями - категориями), а для Точечной значения по горизонтальной оси м.б. любыми числами (см. статью График vs Точечная ).

Примечание . Если для построения Точечной диаграммы не указана ссылка на значения Х (или ссылка указывает на текстовые значения), то в качестве координат по Х будет использована та же последовательность 1, 2, 3, …, что и для Графика.

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

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

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

Кроме того, График может быть только на основной оси и поменять это нельзя.

Если для Точечной диаграммы используется координата Х и оба ряда отложены по одной (основной) оси, то получается не очень красивая диаграмма.

Это связано с тем, что при совмещении с Точечной диаграммой (на одной оси и когда у Точечной указаны значения по Х), диаграмма типа График становится как бы главной:

на горизонтальной оси отображаются подписи только для Графика;
вертикальная сетка не отображается для отрицательных значений Х (т.к. График строится только для Х=1, 2, 3, …);
у Графика невозможно изменить Ось с Основной на Вспомогательную (у Точечной можно).

Если Точечную построить на вспомогательной оси, то диаграмма изменится.

Подписи для Точечной (значения по Х) теперь отображаются сверху.

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

Теперь рассмотрим построение 2-х рядов данных, которые используют диаграмму Точечная.

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

Примечание . Фактически на диаграмме 4 ряда данных: точка центра представляет отдельный ряд.

Дважды кликнем по бордовому эллипсу и выберем построение ряда на вспомогательной оси (сделаем то же и центра эллипса).

Теперь координаты Y для бордового эллипса откладываются по правой вертикальной оси (можно ее для наглядности выделить также бордовым цветом).

Добавим Вспомогательную горизонтальную ось (в меню Оси (вкладка Макет , группа Оси ) выберите Вспомогательная горизонтальная ось и установите ее По умолчанию ).

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

КРУГОВАЯ

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

Совмещение разных типов диаграмм

Про совмещение Графика и Точечной диаграммы рассказано выше (см. раздел Точечная).

Совмещением Линейчатой и Гистограммы не стоит заниматься, т.к. выглядит это достаточно странно, почти также странно, как и совмещение Гистограммы с Круговой (см. также статью Основные типы диаграмм, раздел Круговая ).

Совет . Также можно посмотреть статью Совмещаем разные типы диаграмм .

Оригинальностью совмещения могут также похвастаться Гистограмма и Нормированная линейчатая с накоплением .

Наверное, единственными типами диаграмм, которые стоит совмещать, являются График и Гистограмма (категории должны быть одинаковыми для обоих рядов).

Для такой диаграммы постройте сначала Гистограмму с группировкой с двумя рядами (см. раздел Гистограмма в начале этой статьи). Затем выделите нужный ряд и нажмите кнопку Изменить тип диаграммы (вкладка Конструктор ). Выбрав График или График с Маркерами нажмите ОК.

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

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

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

Это хорошо, но это может быть сложно, если у вас есть несколько круговых диаграмм.

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

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

Это позволяет нам сравнивать продажи продукции в разных городах. Но есть сложности, когда мы хотим изменить их все последовательно или рассматривать их как единую фигуру.

В этой статье мы рассмотрим три разных подхода к объединению круговых диаграмм.

Консолидация данных из нескольких графиков

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

Имеет смысл показывать одну круговую диаграмму вместо трех. Это создало бы больше места в отчете и означало бы меньше «глазного тенниса» со стороны читателя.

В этом примере это принесет жертву сравнения городов.

Самый простой и быстрый способ объединить данные из трех круговых диаграмм — использовать инструмент Консолидация в Excel.

Давайте объединим данные, показанные ниже.

Щелкните ячейку на листе, где вы хотите разместить консолидированные данные. Нажмите Данные> Объединить на ленте.

Откроется окно консолидации.

Мы будем использовать функцию Sum для суммирования продаж во всех трех городах.

Далее мы должны собрать все ссылки, которые мы хотим объединить. Нажмите в поле «Ссылка», выберите первый диапазон, а затем нажмите «Добавить».

Повторите этот шаг для других ссылок.

Установите флажок «Левый столбец», поскольку название продукта находится слева от значений в наших данных. Нажмите «ОК».

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

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

Объединить круговую диаграмму в одну фигуру

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

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

Нажмите Формат> Группа> Группа.

Все круговые диаграммы теперь объединены в одну фигуру. Они будут перемещаться и изменять размер как одно изображение.

Выберите разные графики для просмотра ваших данных

Хотя эта статья посвящена объединению круговых диаграмм, другим вариантом будет выбор другого типа диаграммы. Круговые диаграммы — не единственный способ визуализации частей целого.

Хорошей альтернативой будет столбчатая диаграмма с накоплением.

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

Выберите диапазон ячеек и нажмите «Вставка»> «Столбчатая диаграмма».

Существует два типа столбцов с накоплением на выбор. Первый представит ваши данные, как показано ниже.

Это похоже на наличие трех круговых диаграмм на одном графике.

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

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

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

Это использует процент по оси.

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

Вы можете нажать кнопку «Переключить строку / столбец» на вкладке «Дизайн», чтобы переключить данные между осью и легендой.

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

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

Групповые (двухуровневые) метки осей с настройкой макета исходных данных в Excel

Этот первый метод поможет вам изменить макет исходных данных перед созданием столбчатой диаграммы в Excel. А сделать можно так:

1. Переместите столбец фруктов перед столбцом даты, вырезав столбец фруктов и вставив перед столбцом даты.

2. Выберите столбец с фруктами, кроме заголовка столбца. В нашем случае выберите диапазон A2: A17, а затем нажмите кнопку Сортировка от А до Я Кнопка на Данные меню.

3. В появившемся диалоговом окне Sort Warning оставьте Расширить выбор установлен флажок, и щелкните Сортировать кнопку.

4. В столбце фруктов выберите первую серию одинаковых ячеек, говорит A2: A6, и нажмите Главная > Слияние и центр. Затем нажмите кнопку ОК в открывшемся диалоговом окне Microsoft Excel. См. Скриншоты ниже:

Затем объединяются первые серии соседних ячеек, заполненных Apple. См. Снимок экрана ниже:

5. Повторите шаг 4 и объедините другие соседние ячейки, заполненные такими же значениями.

Совет: один щелчок, чтобы объединить все соседние ячейки, заполненные одинаковым значением в Excel
Если у вас установлен Kutools for Excel, вы можете применить его Объединить одинаковые ячейки утилита для объединения всех соседних ячеек, содержащих одинаковое значение, одним щелчком мыши. Полнофункциональная бесплатная 30-дневная пробная версия!

6. Выберите исходные данные, а затем щелкните значок Вставить столбчатую диаграмму (или Колонка)> Колонка на Вставить меню.

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

Сгруппируйте (двухуровневые) метки оси с помощью сводной диаграммы в Excel

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

1. Создайте сводную диаграмму с выбором исходных данных и:
(1) В Excel 2007 и 2010, щелкнув значок PivotTable > Сводная диаграмма в таблицы группы на Вставить Tab;
(2) В Excel 2013 щелкнув значок Сводная диаграмма > Сводная диаграмма в Графики группы на Вставить меню.

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

3. Теперь на открывшейся панели полей сводной таблицы перетащите поля Дата и Фрукты в Ряды раздел и перетащите Сумму в Ценности .

Ноты:
(1) Заполненный фрукт должен быть выше даты, указанной в Ряды .
(2) Помимо перетаскивания, вы также можете щелкнуть правой кнопкой мыши поле и затем выбрать Добавить в метки строк or Добавить к значениям в контекстном меню.

Затем метки даты автоматически группируются по фруктам в новой созданной сводной диаграмме, как показано ниже:

Читайте также: