Скачать файл, используемый в видеоуроке:

  Группировка в сводных таблицах (831,4 КиБ, 1 870 скачиваний)


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

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


 

ГРУППИРОВКА ДАТЫ И ВРЕМЕНИ
Если необходимо просмотреть суммарную стоимость предложений по кварталам, то пригодиться группировка по датам.

  1. Выделить любую ячейку нужного поля из области строк или столбцов и щелкнуть правой кнопкой мыши;
  2. Выбрать из контекстного меню пункт Группировать (Group);
  3. При необходимости задать свою начальную дату в поле Начиная с(Starting at). По умолчанию сводная самостоятельно определяет самую первую дату в наборе данных и именно её использует в качестве начальной. Если указать другую дату - галочка с этого поля снимется(т.к. галочка как раз дает понять сводной, что надо определить дату самостоятельно)
  4. В поле по(Ending at) ввести конечную дату для группы. Опять же - при необходимости. По умолчанию здесь будет записана самая последняя дата из всех данных поля
  5. В поле с шагом(By)  выбрать диапазон группировки: секунды, минуты, часы, дни, месяцы, кварталы, годы (seconds, minutes, hours, days, months, quarters, years);
  6. Нажать OK

Начиная с 2016 версии Excel группировка полей с датой и временем создается автоматически сразу при добавлении поля в область сводной таблицы. Чтобы отключить автогруппировку перейдите в: Файл(File) -Параметры(Options) -Данные(Data) -снять галочку с пункта Отключить автоматическое группирование столбцов даты и времени в сводных таблицах(Disable automating grouping of Date|Time columns in PivotTables).

Так же можно группировать и по неделям. Для этого делаем все так же: выделяем ячейку с датой -Группировать(Group). В появившемся окне указываем только Дни(Days). Кол-во дней - 7(т.к. это кол-во дней в одной неделе). И вот здесь очень важный момент: по умолчанию сводная таблица возьмет минимальную дату во всем наборе и "разложит" её на куски по 7 дней. Фактически, это конечно, недели. Но в жизни первая дата во всей таблице далеко не всегда будет понедельником :) Поэтому, чтобы недели были "разложены" правильно, необходимо посмотреть на начальную дату, которую определила сводная таблица(поле Начиная с(Starting at)) и найти в календаре понедельник той недели, в которую наша дата входит. Иначе говоря - первый понедельник, предшествующий нашей дате в календаре. Дату этого понедельника мы и должны указать в качестве стартовой даты.
Если за какие-то недели данных нет совсем в наборе данных - такие недели будут пропущены. Чтобы они появились, необходимо в исходные данные сводной таблицы дописать строки дат для каждой недели, пусть даже в этих строках суммы будут нулевыми.


 

ГРУППИРОВКА ЧИСЛОВЫХ ПОЛЕЙ
Может пригодиться для группировки по занятым местам или по ценам предложений. Например, можно отобрать все предложения от 110 000р до 130 000р с шагом 10 000р. В данном случае получим таблицу, в которой будут интересующие предложения из указанного диапазона, разбитые с нужным шагом. Если какие значения превышают указанную сумму(130 000р), то будет отдельная группа: >130000, если меньше: <110000.

  1. Выделить любую ячейку нужного поля из области строк или столбцов и щелкнуть правой кнопкой мыши
  2. Выбрать из контекстного меню пункт Группировать(Group)
  3. В поле Начиная с(Starting at) оставить автовыбор или ввести свое начальное значение (115000). В случае с автоматическим выбором, сводная сама определит минимальное число. Как правило в подобных группировках этого делать не рекомендуется - число может быть не "ровным". Т.е. не 110000, а 109548. Тогда вся разбивка по 10 000 будет начинаться именно с этого "неровного" числа. Иначе говоря - число в этом поле лучше всегда проверять.
  4. В поле по(Ending at) ввести максимальное число, после которого разбивка уже не нужна (130000)
  5. В поле с шагом(By) указать шаг для детализации в каждой группе (10000), если выбранный автоматически шаг не подходит.

 

ГРУППИРОВКА ТЕКСТОВЫХ ПОЛЕЙ ИЛИ ОТДЕЛЬНЫХ ЭЛЕМЕНТОВ

  1. Выделить ячейку из области строк или столбцов с одним из элементов поля для группировки
  2. Удерживая CTRL или SHIFT выделить другие элементы (ячейки) этого поля;
  3. Щелкнуть правой кнопкой по любой выделенной ячейке и выбрать из контекстного меню пункт Группировать(Group) или на вкладке Параметры(Options) в группе Группировать(Group) нажать кнопку Группа по выделенному(Group Selection)

  4. При необходимости задать свое имя группе

В полях с уровнями можно группировать только элементы, имеющие одинаковые подуровни. Например, если в поле есть два уровня «Страна» и «Город», нельзя сгруппировать города из разных стран.


 

ПЕРЕИМЕНОВАНИЕ ГРУППЫ ПО УМОЛЧАНИЮ
При группировке элементов Excel всегда задает имена групп по умолчанию. Например Группа1(Group1) для выбранных элементов или Кв-л1(Qtr1) для квартала 1(если работаем с датами). Изменить это поведение невозможно, т.к. этот механизм "зашит" в дебри Excel и какой-либо настройки для этого нет. Можно только вручную переименовать имя каждой группы. Это может быть скучно и не оптимально - но совсем не сложно:

    1. Выделить имя группы
    2. Нажать клавишу F2 или перейти в строку формул
    3. Ввести новое имя группы


 

РАЗГРУППИРОВКА ЭЛЕМЕНТОВ

  1. Выделить группу элементов, которые требуется разгруппировать
  2. На вкладке Параметры(Options) в группе Группировать(Group) нажать кнопку Разгруппировать(Ungroup) (или щелкнуть правой кнопкой мыши и выбрать из контекстного меню пункт Разгруппировать(Ungroup)>)

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

Примечания:

  1. Для источников данных OLAP (Online Analytical Processing), не поддерживающих инструкцию CREATE SESSION CUBE, группировка элементов невозможна.
  2. При наличии одного или нескольких сгруппированных элементов использовать команду Преобразование в формулы (Параметры- Сервис- Средства OLAP) невозможно. Перед использованием этой команды необходимо сначала удалить сгруппированные элементы.
  3. Для быстрой работы c группами данных надо выделить ячейки в области названий строк или столбцов сводной таблицы, щелкнуть правой кнопкой мыши на любой из выделенных ячеек и выбрать Развернуть/Cвернуть (Expand/Collapse)

 

Что делать, если данные не группируются
При группировке дат и числовых полей есть небольшой нюанс: иногда, при наличии дат или чисел в ячейках и вызове группировки можно получить сообщение "Выделенные объекты нельзя объединить в группу":
Выделенные объекты нельзя объединить в группу
И вроде бы в ячейках даты/числа - но группироваться отказываются. В данном случае следует проверить - а действительно ли числа это числа, а даты - даты? Как бы странно это не звучало - случается подобное не так уж редко. Т.е. выглядят в ячейках данные как числа или даты, а на деле это просто текст. В большинстве случаев Excel подсвечивает такие ячейки зелеными треугольничками в левом верхнем углу:
Число как текст
В этом случае все просто: находим самую первую ячейку с таким треугольничком и выделяем все нижестоящие ячейки(до конца таблицы(Ctrl+Shift+стрелка вниз)). После чего прокручиваем лист обратно к этой ячейке, нажимаем на значок с воскл.знаком левее ячейки и в раскрывшемся меню выбираем Преобразовать в число(дату).
Преобразовать в число
После этого обязательно необходимо перейти в сводную таблицу и обновить её(выделить любую ячейку сводной таблицы →Правая кнопка мыши →Обновить(Refresh) или вкладка Данные(Data) →Обновить все(Refresh all) →Обновить(Refresh)). Это действие придется повторить два раза(для дат обязательно обновить именно два раза, иначе сводная так и будет считать даты текстом).

Но может быть и так, что зеленых треугольников нет, а группировка не работает. Для начала следует убедиться, что в столбце нет ячеек с явным текстом, а так же ячеек с пробелами. По хорошему - пустые ячейки тоже лучше убрать или заменить нолями. Как правило это можно определить через фильтр. Опять обновить пару раз сводную.
Если после этого группировка все равно отказывается работать - значит где-то еще есть числа/даты, записанные как текст и они могут быть не подсвечены зеленым треугольником. Такое поведение часто наблюдается в файлах, выгруженных из 1С или иных программ. Выявить такие ячейки "глазами" будет проблематично и я в таких случаях рекомендую в отдельном столбце записать формулу:
=ЕЧИСЛО(A2)
=ISNUMBER(A2)

A2 - ссылка на первую ячейку столбца с датами или числами(да, даты тоже числа - подробнее в статье: ).
Протянуть формулу на весь столбец данных. Там, где будет результатом ЛОЖЬ(FALSE) - не число и не дата. Внимательно изучаем эти данные и убеждаемся, что по сути значение в ячейке полностью соответствует нашему понимаю даты. Чтобы быстро преобразовать такие ячейки с "датами/числами", записанными как текст в реальные даты/число необходимо:

  • назначить всем ячейкам с нашими "датами" формат даты или общий (желательно до действий ниже)
  • скопировать любую пустую ячейку на листе
  • выделить все ячейки с датами/числами(или столбец полностью)
  • правая кнопка мыши -Специальная вставка (Paste Special) -в окне выбрать Значения (Values), операция - Сложить (Multiply)
  • ОК

Excel автоматом преобразует даты и числа в нормальные данные. Возможно, придется заново задать формат датам - но это уже совершенно не сложно: правая кнопка мыши -Формат ячеек (Format Cells) -Дата (Date).
Про другие возможности Специальной вставки можно прочитать в этой статье: Как быстро умножить/разделить/сложить/вычесть из множества ячеек одно и то же число?
Часто я видел как подобную проблему решают очень упорным трудом: выделяют ячейку, жмут F2(чтобы войти в режим редактирования ячейки) и Enter. Т.к. происходит повторный ввод данных в ячейку - Excel пытается определить тип данных и преобразует дату/число в настоящие дату/число, если это возможно сделать.

Так же см.:
Общие сведения о сводных таблицах
Сводная таблица из нескольких листов
Сводная из базы данных Access
Автообновляемая сводная таблица
Как перейти к редактированию исходных данных прямо из сводной таблицы?

Один комментарий

  1. Можно ли добавить дополнительные "Промежуточные итоги" для сводной таблицы, содержащей большую структуру - столбцов.
    Так, чтобы эти дополнительные итоги - показывали итоги по каждой структуре.
    Например, есть сводная таблица по месяцам продаж (строки) по Магазинам, Маркам, Цветам товара (столбцы).

    Хотелось бы увидеть в столбцах: Общие итоги (+), Итоги по Магазину (+), Итоги по Марке (- не дает, только внутри каждого магазина), Итоги по Цвету (+), Итоги по Магазину-Марке(+), Итоги по Марке-Цвету(- не дает), Итоги по Магазину -Цвету (не дает).....

    Итого 7 итогов: 4 могу сделать, а 3 не получается ( в одной таблице)...... Приходится делать надстройку поверх Сводной....

Добавить комментарий

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.