Как сделать группировку по числам
Группировка в сводной таблице Excel – полезные рекомендации.
Возможность группировки данных является одной из самых мощных и полезных функций в сводной таблице. Это позволит обнаружить ранее скрытые взаимосвязи, сделать интересные выводы.
При работе со сводными таблицами в Microsoft Excel вы можете обнаружить, что в очень часто в них имеется много информации, которую следовало бы обобщить еще больше. Это можно сделать, сгруппировав отдельные показатели. И в этом материале я объясню, как сделать это, какие возможности для анализа мы здесь получаем.
Используем как источник информацию о реализации шоколада, часть которой вы видите на рисунке ниже.
Согласитесь, что простое перечисление продаж не дает нам достаточно полезной информации, чтобы сделать какие-то выводы и предложения.
Группировка по числовому значению.
Давайте попробуем подсчитать, с какой скидкой у нас происходит больше всего покупок.
Создадим новый макет сводной таблицы. Как это сделать – читайте в нашем руководстве по созданию сводных таблиц. Перетащим скидку в область строк, а сумму – в область значений. Предлагаю сразу же изменить присвоенное по умолчанию наименование показателя на «Сумма покупки». Так будет гораздо информативнее.
В результате получаем длинный список покупок с процентом скидки по каждой из них.
И на какой диапазон скидок нам лучше всего ориентироваться? Вот здесь нам и пригодится группировка.
Устанавливаем курсор на то поле, по которому хотим сгруппировать числа. Во вкладке «Анализ» находим «Группа» (1) и далее действуем так, как показано на рисунке чуть ниже. Программа автоматически определяет максимальное и минимальное значение (3) и по умолчанию предлагает шаг группировки равный единице, то есть 1%. Думаю, есть смысл увеличить его до 5%. (4)
К сожалению, процентный формат чисел здесь не учитывается, поэтому в уме умножайте всё на 100.
Получаем сжатый и достаточно понятный результат. Для наглядности можно добавить сюда еще и диаграмму. Для этого на вкладке «Анализ» нажмите «Сводная диаграмма» и выберите наиболее подходящий вам вид. И далее ее можно корректировать точно так же, как и обычную диаграмму Excel.
Больше всего покупок – со скидкой от 6,6% до 11,6% (на диаграмме это интервал 0,066 – 0,116).
Группировка по датам.
Теперь давайте посмотрим, в какие периоды времени у нас были максимальные продажи. Для этого создадим еще одну сводную таблицу. В качестве строк на этот раз используем даты.
Итак, первоначально перед нами группировка дат с суммами покупок за каждый день. Нам снова будет предложено выбрать интервал группировки. Выберите месяц и квартал.
Как и ранее, добавим для наглядности сводную диаграмму.
Итак, мы видим итоги по кварталам, общий итог, а также продажи, сгруппированные по месяцам.
Произвольная группировка.
Мы можем научиться как сделать более сложную группировку. Продолжаем работать с последним вариантом нашей сводной таблицы.
Перетаскиваем поле «Товар» в область условных обозначений столбцов, как это показано на рисунке ниже.
Сразу же меняется и сама таблица, перестраивается график. Теперь мы видим продажи по месяцам в разрезе отдельных товаров. Причем, если таких наименований будет слишком много, мы можем оставить на диаграмме только самые важные из них. Для этого нажмите на выпадающий список товаров, который находится справа от диаграммы. И там выберите только нужные товары, чтобы не загромождать график лишней информацией.
И, наконец, мы можем добавить к нашему анализу еще одно изменение. Как вы помните, у нас еще есть сведения, кому именно был продан товар, то есть код покупателя.
Используем такую интересную новинку, появившуюся в Office 2013, как срезы.
На вкладке «Анализ» нажмите кнопку «Вставить срез» и в появившемся окне выберите «Код покупателя».
Рядом появилось небольшое окошко с кодами покупателей. Это своего рода фильтр, при помощи которого можно выбрать для отображения одного, нескольких либо всех покупателей.
В результате мы можем сгруппировать сведения о покупках по времени, по товарам и по покупателю (одному или нескольким).
Согласитесь, это достаточно мощный инструмент анализа.
Кроме того, группировать можно выборочно: не по всем значениям какого-то показателя, а только по некоторым из них. К примеру, давайте создадим группу черного шоколада, отделив его от всех товаров.
Выделим ячейки с нужным товаром. Затем, как это показано на скриншоте, нажмём на ленте «Группировка по выделенному». Результат вы видите ниже.
Выбранные нами наименования товара сейчас объединены в группу. И по ней мы можем делать все необходимые расчеты.
Работа с группировками.
Вы можете свернуть и развернуть группу сводной таблицы, нажав на символ плюс / минус (+/-) слева от ее названия.
Одно из преимуществ группировки чисел в сводной таблице заключается в том, что затем можно извлечь подмножество сгруппированных данных в новый рабочий лист. То есть, получить своего рода расшифровку по любому из итогов.
Для этого выберите интересующую вас группу и дважды щелкните ячейку «Итого». Данные, образовавшие этот итог, будут извлечены на новый лист в вашей рабочей книге.
Предположим, мы хотим получить детальную информацию о том, как образовалась сумма продаж равная 32116 по покупателю с кодом «Голубой» за 1 квартал. Вы ее видите на предыдущем скриншоте чуть выше.
Щелкаем по этой цифре, и получаем на новом листе вот что:
Единственное неудобство здесь, быть может, в том, что ко всем числам применен формат «Общий», который используется в Excel по умолчанию.
Итак, двойной щелчок по любому значению сводной таблицы создает новый рабочий лист, содержащий все данные, которые повлияли на это значение.
Устранение ошибок
Иногда, когда вы пытаетесь сгруппировать числа или даты, вы получаете сообщение об ошибке, в котором говорится, что вы не можете выполнить это действие.
Это может произойти по ряду причин.
Во-первых, если вы пытаетесь сгруппировать какие-то показатели в свою собственную пользовательскую группу, вы должны выбрать как минимум две записи для ее создания. Вы не можете создать группу только из одной записи.
Если в поле, содержащем даты или числа, есть пустые ячейки или ячейки с текстом, то из-за этого вы получите сообщение об ошибке.
Я думаю, вы понимаете, что Excel не может группировать числа и текст, ведь нельзя сложить букву и число.
В каждом случае проверьте исходные данные и устраните проблему, а затем выполните обновление сводной таблицы перед повторной попыткой.
Структурирование (группировка) данных на листе
Если у вас есть список данных, которые вы хотите сгруппировать и обобщить, можно создать структуру из восьми уровней. Каждый внутренний уровень, представленный более высоким числом в символы структуры, отображает детали для предыдущего внешнего уровня, представленный более низким числом в символах контура. Используйте структуру для быстрого отображения итогов в строках или столбцах либо для отображения подробных данных для каждой группы. Вы можете создать структуру строк (как показано в примере ниже), структуру столбцов или структуру как строк, так и столбцов.
1. Чтобы отобразить строки уровня, щелкните соответствующий 
структуру.
2. Уровень 1 содержит сумму продаж по всем строкам с подробными данными.
3. Уровень 2 содержит сумму продаж по каждому месяцу в каждом регионе.
4. Уровень 3 содержит строки с подробными сведениями — в данном случае строки с 17 по 20.
5. Чтобы развернуть или свернуть данные в структуре, щелкните символы 
и 
или нажмите ALT+SHIFT+= для расширения и ALT+SHIFT+- для свернуть.
Убедитесь, что в первой строке каждого столбца данных, которые вы хотите структурировать, есть заметка (например, «Регион»), в каждом столбце содержатся одинаковые факты и что в диапазоне, который вы хотите структурировать, нет пустых строк или столбцов.
При этом строки сгруппными данными могут иметь соответствующую итоговую строку — подытог. Чтобы создать их, сделайте следующее:
Вставка итоговых строк при помощи команды Промежуточные итоги
Используйте команду Итоги для вставки функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ непосредственно под или над каждой группой строк с подробными данными и для автоматического создания многоуровневой структуры. Подробнее об использовании функции «Итоги» читайте в статье Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
Вставка собственных итоговых строк
Вставьте собственные итоговые строки с формулами непосредственно под или над каждой группой строк с подробными данными. Например, под (или над) строками данных о продажах за март и апрель используйте функцию СУММ, чтобы подсчитать промежуточные итоговые данные по продажам за эти месяцы. Этот пример показан в таблице далее в этой статье.
По умолчанию Excel ищет итоги в строках под итогами, но их можно создать над строками с подробными данными. Если вы создали итоги в строках под сведениями, переперейти к следующему шагу (шагу 4). Если вы создали итоговые строки над строками с данными, на вкладке «Данные» в группе «Структуру» нажмите кнопку запуска диалогового окна.
Откроется диалоговое окно «Параметры».
Затем в диалоговом окне «Параметры» сберегайте строки итогов под подробными данными и нажмите кнопку «ОК».
Структурируйте данные. Выполните одно из перечисленных ниже действий.
Автоматическое структурирование данных
Выберем ячейку в диапазоне ячеек, которые вы хотите контурить.
На вкладке Данные в группе Структура щелкните стрелку рядом с пунктом Группировать, а затем выберите пункт Создать структуру.
Структурирование данных вручную
Важно: Группируя уровни структуры вручную, отобразите на экране все данные во избежание ошибок группировки строк.
Чтобы обозначить внешнюю группу (уровень 1), выберем все строки, которые она будет содержать (то есть строки с подробными данными и если вы их добавили, их итоги).
1. Первая строка содержит подписи, но не выбрана.
2. Поскольку это внешняя группа, выберем все строки с подытоговами и подробными сведениями.
3. Не выбирайте общий итог.
На вкладке Данные в группе Структура выберите Группировать. В диалоговом окне «Группа» щелкните «Строки»и нажмите кнопку «ОК».
Совет: Если выбрать строки целиком, а не только ячейки, Excel автоматически группируется по строкам— диалоговое окно «Группа» даже не открывается.
Рядом с группой на экране появятся знаки структуры.
Или структурируйте внутреннюю вложенную группу — строки с данными для определенного раздела данных.
Примечание: Если вам не нужно создавать какие-либо внутренние группы, перешагнуть к шагу f ниже.
Для каждой внутренней вложенной группы выберите строки с подробными данными, соответствующие итоговой строке.
1. Можно создать несколько групп на каждом внутреннем уровне. Здесь два раздела уже сгруппировали на уровне 2.
2. Этот раздел выбран и готов к группировке.
3. Не выбирайте итоговую строку для групп данных.
На вкладке Данные в группе Структура выберите Группировать.
В диалоговом окне «Группа» щелкните «Строки»и нажмите кнопку «ОК». Рядом с группой на экране появятся знаки структуры.
Совет: Если выбрать строки целиком, а не только ячейки, Excel автоматически группируется по строкам— диалоговое окно «Группа» даже не открывается.
Продолжайте выделение и группировку внутренних строк до тех пор, пока не будут созданы все необходимые уровни структуры.
Чтобы разгруппировать строки, выделите их, а затем на вкладке Данные в группе Структура выберите команду Разгруппировать.
Вы также можете разгруппировать разделы в структуре, не удаляя весь уровень. Удерживая нажатой кнопку shift, щелкните 
или 
для группы, а затем на вкладке «Данные» в группе «Структуру» нажмите кнопку «Разгруппировать».
Важно: Если во время разгруппировки структуры подробные данные скрыты, строки с подробными данными могут также не отображаться. Для отображения данных перетащите указатель через номера видимых строк рядом со скрытыми строками. Затем на вкладке «Главная» в группе «Ячейки» нажмите кнопку «Формат», выберите пункт «Скрыть& открыть», а затем нажмите кнопку «Открыть строки».
Убедитесь в том, что в первом столбце каждой строки данных, для которых требуется создать структуру, имеется заголовок, что каждая строка содержит данные одного типа и что в диапазоне данных отсутствуют пустые строки или столбцы.
Вставьте собственные итоговые столбцы с формулами непосредственно справа или слева от каждой группы строк с подробными данными. В таблице, приведенной в шаге 4, показан пример.
Примечание: Для структурирования данных по столбцам обязательно наличие итоговых столбцов с формулами, которые ссылаются на ячейки в каждом из столбцов с подробными данными этой группы.
Если итоговой столбец находится слева от столбцов с подробными данными, на вкладке «Данные» в группе «Структуру» нажмите кнопку запуска диалогового окна.
Откроется диалоговое окно «Параметры».
Затем в диалоговом окне «Параметры» разберите столбцы итогов справа от подробных полей и нажмите кнопку «ОК».
Чтобы структурировать данные, выполните одно из перечисленных ниже действий.
Автоматическое структурирование данных
Выделите ячейку в диапазоне.
На вкладке Данные в группе Структура щелкните стрелку под пунктом Группировать, а затем выберите Создать структуру.
Структурирование данных вручную
Важно: Группируя уровни структуры вручную, отобразите на экране все данные во избежание ошибок при группировке столбцов.
Чтобы обозначить внешнюю группу (уровень 1), выберем все подчиненные итоги столбцов, а также связанные с ними подробные данные.
1. Столбец A содержит метки.
2. Выберем все столбцы с подробными и подытогами. Обратите внимание, что если не выбрать столбцы целиком, при нажатии кнопки «Группировать» (на вкладке «Данные» в группе «Структуру») откроется диалоговое окно «Группа» с запросом на выбор строк или столбцов.
3. Не выбирайте столбец общего итога.
На вкладке Данные в группе Структура выберите Группировать.
Над группой появится символ контура.
Чтобы обозначить внутреннюю вложенную группу столбцов с подробными данными (уровень 2 или более высокий), выберите столбцы с подробными данными, смежные со столбцом, который содержит итоговую группу.
1. Можно создать несколько групп на каждом внутреннем уровне. Здесь два раздела уже сгруппировали на уровне 2.
2. Эти столбцы будут выбраны и готовы к группировке. Обратите внимание, что если не выбрать столбцы целиком, при нажатии кнопки «Группировать» (на вкладке «Данные» в группе «Структуру») откроется диалоговое окно «Группа» с запросом на выбор строк или столбцов.
3. Не выбирайте итоговую группу данных.
На вкладке Данные в группе Структура выберите Группировать.
Рядом с группой на экране появятся знаки структуры.
Продолжайте выделение и группировку внутренних столбцов до тех пор, пока не будут созданы все необходимые уровни структуры.
Для разгруппировки столбцов выделите необходимые столбцы, а затем на вкладке Данные в группе Структура нажмите кнопку Разгруппировать.
Вы также можете разгруппировать разделы в структуре, не удаляя весь уровень. Удерживая нажатой кнопку shift, щелкните или для группы, а затем на вкладке «Данные» в группе «Структуру» нажмите кнопку «Разгруппировать».
Если во время разгруппировки структуры подробные данные скрыты, столбцы с подробными данными могут также не отображаться. Для отображения данных перетащите указатель через номера видимых столбцов рядом со скрытыми столбцами. На вкладке Главная в группе Ячейки нажмите кнопку Формат, выберите пункт Скрыть или отобразить, а затем нажмите кнопку Отображение столбцов
Если вы не видите символов контура , и , перейдите в меню «Файл» > Параметры > Дополнительные параметры, а затем в разделе «Параметры отображения» для этого раздела выберите параметр «Отображать символы контура», если она применена, и нажмите кнопку ОК.
Выполните одно или несколько из указанных ниже действий.
Отображение или скрытие подробных данных для группы
Чтобы отобразить подробные данные в группе, нажмите кнопку или клавиши ALT+SHIFT+=.
Чтобы скрыть подробные данные для группы, нажмите кнопку или клавиши ALT+SHIFT+-.
Развертывание или свертывание структуры до определенного уровня
В щелкните номер нужного уровня. Подробные данные более низких уровней будут скрыты.
Например, если в структуре четыре уровня, можно скрыть четвертый уровень, нажав кнопку 
, при этом оставшиеся три уровня будут отображаться.
Отображение или скрытие всех структурированных данных
Чтобы показать все подробные данные, щелкните нижний уровень в структуре. Например, если в структуре три уровня, нажмите .
Чтобы скрыть все подробные данные, нажмите 
.
Для строк со структурой в Microsoft Excel используются такие стили, как RowLevel_1 и RowLevel_2. Для столбцов со структурой в Excel используются такие стили, как ColLevel_1 и ColLevel_2. В этих стилях используются полужирный шрифт, выделить строки или столбцы итогов, а также выделить их в тексте. Изменяя способ определения каждого из этих стилей, можно применять различные форматы текста и ячеок для настройки внешнего вида контура. Стиль можно применить к контуру как при ее создании, так и после ее создания.
Выполните одно или несколько из указанных ниже действий.
Автоматическое применение стиля к новым суммарным строкам или столбцам
На вкладке «Данные» в группе «Структуру» нажмите кнопку запуска диалогового окна.
Откроется диалоговое окно «Параметры».
Установите флажок Автоматические стили.
Назначение стиля существующей итоговой строке или столбцу
Вы выберите ячейки, к которым вы хотите применить стиль.
На вкладке «Данные» в группе «Структуру» нажмите кнопку запуска диалогового окна.
Откроется диалоговое окно «Параметры».
Включив автоматические стили, нажмите кнопку «Применить стили».
Для форматирования структурированных данных также можно применять автоформаты.
Если вы не видите символов контура , и , перейдите в раздел «Параметры файла >> Дополнительные параметры», а затем в разделе «Параметры отображения» для этого раздела выберите параметр «Показать символы контура», если она применена.
Используйте символы , и , чтобы скрыть подробные данные, которые не нужно копировать.
Выделите диапазон итоговых строк.
На вкладке Главная в группе Редактирование нажмите кнопку Найти и выделить, а затем выберите команду Перейти.
Нажмите кнопку Выделить группу ячеек.
Выберите пункт только видимые ячейки.
Нажмите кнопку ОК, а затем скопируйте данные.