Группировка данных сводной таблицы в Excel 2013

Рубрика: 7. Полезняшки Excel

Несмотря на то что большая часть операций подведения итогов и вычисления показателей выполняется с использованием стандартных команд сводных таблиц, иногда возникают ситуации, в которых требуется дополнительная группировка данных. [1] Например, исходные финансовые данные, как правило, фиксируются с указанием даты сделки (рис. 1), в отчете желательно представить их не по каждой дате отдельно (рис. 2а), а сгруппированными по месяцам (рис. 2б). Команда Группировать вкладки Анализ позволяет легко и быстро консолидировать даты сделок в группы, основанные на месяцах или кварталах. Впоследствии вы сможете суммировать данные в этих группах таким же образом, как это выполняется в полях сводной таблицы.

Рис. 1. Исходные данные содержат список сделок с указанием конкретной даты

Рис. 1. Исходные данные содержат список сделок с указанием конкретной даты

Скачать заметку в формате Word или pdf, примеры в формате Excel

Рис. 2. Отчет, сведенный по датам

Рис. 2. Отчет, сведенный по датам: (а) данные двух лет продаж занимают более 500 столбцов; (б) после группировки по месяцам отчет существенно удобнее

Группировка полей дат

Для группировки полей дат выделите заголовок поля дат или любую ячейку с датой. Например, на рис. 2а выделите одну из ячеек: В3, В4, С4, D4… На контекстной вкладке ленты Анализ в области Группировать щелкните на кнопке Группировка по полю. Если поле содержит информацию о датах, откроется диалоговое окно Группирование (рис. 3). Обратите внимание, что исходные данные (см. рис. 1) в колонке Дата заказа должны содержать только даты; даже одна текстовая или незаполненная ячейка (пустая) в исходных данных в столбце Дата заказа не позволит сделать группировку по датам. Если перед группированием вы выделили только одну ячейку, то кнопки Группировка по полю и Группировка по выделенному работают одинаково. Различие проявится только если перед группированием вы выделите несколько ячеек.

Рис. 3. Окно Группирование

Рис. 3. Окно Группирование

Можно группировать данные по секундам, минутам, часам, дням, месяцам, кварталам и годам. По умолчанию выделен вариант Месяцы. Выберите также Дни и Годы и нажмите Ok. Обратите внимание на некоторые особенности группировки данных в итоговой сводной таблице. Во-первых, поля Месяцы и Годы добавлены в список полей (рис. 4). Не позволяйте себя одурачить — ваш источник данных не изменился и никаких новых полей не содержит. Эти поля теперь являются частью кеша сводной таблицы в памяти (подробнее о кеше см. Excel 2013. Создание нескольких сводных таблиц на основе одного источника данных: один кеш или несколько?). Во-вторых, по умолчанию поля Месяцы и Годы автоматически добавляются в макет сводной таблицы. Вы можете работать с ними, как с обычными полями: перетаскивать в другие области или делать неактивными. В-третьих, включайте данные по Дням, чтобы иметь возможность добавить это поле в сводную таблицу. Если вы в окне Группирование оставите только Месяцы, то поле Даты вы будете видеть в списке, вот только оно эквивалентно месяцам, а поле Месяцы не появится. И наконец, в окне Группирование добавляйте поле Годы. Если это сделать, то вы корректно сможете разделить данные по годам (рис. 5а), если же этого не сделать, то данные двух лет наблюдений объединяться в одном столбце (рис. 5б).

Рис. 4. Добавление полей Месяцы и Годы в список полей и макет сводной таблицы

Рис. 4. Добавление полей Месяцы и Годы в список полей и макет сводной таблицы

Рис. 5. Группирование (а) по дням, месяцам и годам; (б) по дням и месяцам

Рис. 5. Группирование: (а) по дням, месяцам и годам; (б) по дням и месяцам

Группировка полей дат по неделям

Диалоговое окно Группирование предлагает настройки группировки по секундам, минутам, часам, дням, месяцам, кварталам и годам. А что делать, если нужно сгруппировать данные по одной или двум неделям или иным промежуткам времени? Это вполне реально.

Прежде всего следует свериться с календарем, чтобы решить, с какого дня должна начинаться неделя: с воскресенья, понедельника или любого на ваш выбор. Например, первый понедельник в 2014-м году – 6 января. Если вы хотите, чтобы данные за первую неделю также отражались, вам следует выбрать в качестве начала отсчета последний понедельник 2013-го года – 30 декабря. Чтобы сгруппировать даты по неделям выделите заголовок или ячейку с датой. Например, на рис. 2 ячейки В3 или В4. Перейдите на контекстную вкладку ленты Анализ и в разделе Группировать щелкните на кнопке Группировка по полю. В диалоговом окне Группирование (рис. 6) выделите только параметр Дни. В результате станет доступным счетчик количество дней. Чтобы создать недельный отчет, установите значение 7. Установить в поле начиная с требуемую дату (в нашем примере – 30.12.13). Нажмите Ok. В результате сгенерируется отчет, отображающий еженедельные объемы продаж, как показано на левой части рис. 6.

Рис. 6. Группировка дат по неделям

Рис. 6. Группировка дат по неделям

Если вы решили выполнить группировку данных по неделям, иные настройки группировки будут недоступны. Вы не сможете сгруппировать текущее или любое другое поле по месяцам или кварталам. Чтобы преодолеть это ограничение вы должны создавать сводные таблицы так, чтобы каждая была основана на своем кеше (см. Excel 2013. Создание нескольких сводных таблиц на основе одного источника данных: один кеш или несколько?). Именно так я и поступал в файле с примерами, чтобы сохранить вид примеров в первозданном виде.

Группирование двух полей дат в одной сводной таблице

При группировке поля дат по месяцам и годам программа Excel переназначает исходное поле дат для отображения месяцев и добавляет новое поле для отображения лет. Новое поле получает имя Годы. Все довольно просто, если у вас в отчете представлено только одно поле дат. Если вам нужно создать отчет с двумя полями дат (например, дата заказа и дата оплаты) и вы пытаетесь сгруппировать оба поля по месяцам и годам, программа Excel сама установит первому сгруппированному полю имя Годы, а второму — имя Годы2. Чтобы не возникло путаницы, переименуйте поле Годы, прежде чем запустить группирование по второму полу дат.

Группировка числовых полей

Диалоговое окно Группирование, применяемое для числовых полей, позволяет группировать элементы в одинаковые диапазоны. Это может быть полезно при проведении частотного анализа. Сводная таблица на рис. 7 сформирована необычным образом. Здесь в область СТРОКИ помещено поле Доход, а в область ЗНАЧЕНИЕ – поле Заказчик. Поскольку поле Заказчик является текстовым, сводная таблица автоматически подсчитывает их количество, а не сумму.

Рис. 7. Сводная таблица, «заготовка» для проведения частотного анализа

Рис. 7. Сводная таблица, «заготовка» для проведения частотного анализа

Выделите в столбце А любое число, а затем на вкладке Анализ щелкните на кнопке Группировка по полю. В диалоговом окне Группирование выберите параметры группирования (рис. 8). В рассматриваемом случае группирование начинается с 0 и завершается величиной 25 350 при шаге группирования 5000.

Рис. 8. Частотное распределение на основе группировки заказов в группы по $5000 по полю Доход

Рис. 8. Частотное распределение на основе группировки заказов в группы по $5000 по полю Доход

Разгруппировка. Создав группу, вы можете разгруппировать её с помощью кнопки Разгруппировать, находящейся на вкладке Анализ. Достаточно выделить ячейку со сгруппированными данными и щелкнуть на этой кнопке. Эта команда также доступна в контекстном меню: выделите ячейку со сгруппированными данными и щелкните правой кнопкой мыши.

Группировка текстовых полей

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

Для начала создайте отчет, отображающий доход по рынкам сбыта. Держа нажатой клавишу Ctrl, выделите рынки сбыта, на основе которых будет создана новая зона (рис. 9). Перейдите на вкладку Анализ, и щелкните на кнопке Группировка по выделенному.

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

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

Excel добавит новое поле с именем Рынок сбыта2 (рис. 10). Выделенные на первом шаге рынки сбыта объединились в Группа1. Выделите оставшиеся рынки сбыта и повторно щелкните на кнопке Группировка по выделенному.

Рис. 10. Первая зона создана

Рис. 10. Первая зона создана

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

Рис. 11. Итоговый отчет по новым зонам продаж

Рис. 11. Итоговый отчет по новым зонам продаж

 

[1] Заметка написана на основе книги Билл Джелен, Майкл Александер. Сводные таблицы в Microsoft Excel 2013. Глава 4.

Комментарии: 13 комментариев

Очень все доходчиво и понятно описано.
Есть вопрос.
То, что показано на рис. 6. Группировка дат по неделям, есть возможность задать шаг группировки по дням с указанием к-ва дней.
В вашем файле (лист "Рис. 6") все работает. Но когда я самостоятельно пытаюсь воссоздать подобную сводную, у меня шаг к-во дней не активно. Почему?
Спасибо, Артем.

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

Добрый день!
Как быть, если ни на вкладке «Анализ», ни в контекстном меню команда Группировать недоступна? Не помогает ни создание файла с 0 и копирование содержимого с применением спецвставки в режиме «Только значение». В каких случаях в сводных таблицах группировка недоступна? Речь не идет о связанных таблицах — таблица только одна с исходными данными.
С уважением
Новак

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

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

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

этим можно как то управлять?

Я не сталкивался с таким. Правда использую Excel2013. Если я первоначально группирую по месяцам в русском Excel, то при открытии в английском Excel показывает название месяцев на английском; при этом группировка сохраняется.

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

Мне не известна такая возможность. На мой взгляд, названия месяцев зашиты в региональные настройки MS Windows и не могут быть изменены внутри Excel. Если найдете, как это сделать, поделитесь…

Группировка станет доступна (активна) если при создании таблицы не добавлять данные в модель (снять флажок «Добавлять эти данные в модель данных»)

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

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

Как отключить сводную таблицу?

Анастасия, поясните, что значит «отключить»?


Прокомментировать