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