Группировка в сводных таблицах (831,4 КиБ, 1 900 скачиваний)
Основное предназначение сводной таблицы - анализ данных. И для этого она снабжена достаточным довольно обширным функционалом, включая группировку элементов. Разберемся что это такое, для чего нужно, когда и как применять.
Предположим, имеется таблица примерно такого вида:
И необходимо проанализировать данные в разрезе месяца, квартала, на основании групп артикулов или цен с определенным шагом. Все это сводная может сделать без особых усилий с нашей стороны.
- Группировка даты и времени
- Группировка числовых полей
- Группировка текстовых полей и отдельных элементов
- Переименование группы по умолчанию
- Разгруппировка элементов
- Ошибка "Выделенные объекты нельзя объединить в группу"
Если необходимо просмотреть суммарную стоимость предложений по кварталам, то пригодиться группировка по датам.
- Выделить любую ячейку нужного поля из области строк или столбцов и щелкнуть правой кнопкой мыши;
- Выбрать из контекстного меню пункт Группировать
(Group) ; - При необходимости задать свою начальную дату в поле Начиная с
(Starting at) . По умолчанию сводная самостоятельно определяет самую первую дату в наборе данных и именно её использует в качестве начальной. Если указать другую дату - галочка с этого поля снимется(т.к. галочка как раз дает понять сводной, что надо определить дату самостоятельно) - В поле по
(Ending at) ввести конечную дату для группы. Опять же - при необходимости. По умолчанию здесь будет записана самая последняя дата из всех данных поля - В поле с шагом
(By) выбрать диапазон группировки: секунды, минуты, часы, дни, месяцы, кварталы, годы(seconds, minutes, hours, days, months, quarters, years) ; - Нажать OK
Начиная с 2016 версии Excel группировка полей с датой и временем создается автоматически сразу при добавлении поля в область сводной таблицы. Чтобы отключить автогруппировку перейдите в: Файл
(File) -Параметры(Options) -Данные(Data) -снять галочку с пункта Отключить автоматическое группирование столбцов даты и времени в сводных таблицах(Disable automating grouping of Date|Time columns in PivotTables) .
Так же можно группировать и по неделям. Для этого делаем все так же: выделяем ячейку с датой -Группировать
ГРУППИРОВКА ЧИСЛОВЫХ ПОЛЕЙ
Может пригодиться для группировки по занятым местам или по ценам предложений. Например, можно отобрать все предложения от 110 000р до 130 000р с шагом 10 000р. В данном случае получим таблицу, в которой будут интересующие предложения из указанного диапазона, разбитые с нужным шагом. Если какие значения превышают указанную сумму(130 000р), то будет отдельная группа: >130000, если меньше: <110000.
- Выделить любую ячейку нужного поля из области строк или столбцов и щелкнуть правой кнопкой мыши
- Выбрать из контекстного меню пункт Группировать
(Group) - В поле Начиная с
(Starting at) оставить автовыбор или ввести свое начальное значение (115000). В случае с автоматическим выбором, сводная сама определит минимальное число. Как правило в подобных группировках этого делать не рекомендуется - число может быть не "ровным". Т.е. не 110000, а 109548. Тогда вся разбивка по 10 000 будет начинаться именно с этого "неровного" числа. Иначе говоря - число в этом поле лучше всегда проверять. - В поле по
(Ending at) ввести максимальное число, после которого разбивка уже не нужна (130000) - В поле с шагом
(By) указать шаг для детализации в каждой группе (10000), если выбранный автоматически шаг не подходит.
- Выделить ячейку из области строк или столбцов с одним из элементов поля для группировки
- Удерживая
CTRL илиSHIFT выделить другие элементы (ячейки) этого поля; - Щелкнуть правой кнопкой по любой выделенной ячейке и выбрать из контекстного меню пункт Группировать
(Group) или на вкладке Параметры(Options) в группе Группировать(Group) нажать кнопку Группа по выделенному(Group Selection)
- При необходимости задать свое имя группе
В полях с уровнями можно группировать только элементы, имеющие одинаковые подуровни. Например, если в поле есть два уровня «Страна» и «Город», нельзя сгруппировать города из разных стран.
При группировке элементов Excel всегда задает имена групп по умолчанию. Например Группа1
- Выделить имя группы
- Нажать клавишу
F2 или перейти в строку формул - Ввести новое имя группы
- Выделить группу элементов, которые требуется разгруппировать
- На вкладке Параметры
(Options) в группе Группировать(Group) нажать кнопку Разгруппировать(Ungroup) (или щелкнуть правой кнопкой мыши и выбрать из контекстного меню пункт Разгруппировать(Ungroup) >)
Для полей с числовыми группировками или полей даты-времени будут автоматически удалены все группировки, относящиеся к тому полю, которое было сгруппировано. А вот для произвольных группировок(отдельно выбранные текстовые элементы) будет удалена только та группа, в которой была выделена ячейка. При этом в списке полей сводной таблицы дополнительное поле такой группировки будет значится до тех пор, пока все произвольные группы этого поля не будут разгруппированы.
Примечания:
- Для источников данных OLAP (Online Analytical Processing), не поддерживающих инструкцию CREATE SESSION CUBE, группировка элементов невозможна.
- При наличии одного или нескольких сгруппированных элементов использовать команду Преобразование в формулы (Параметры- Сервис- Средства OLAP) невозможно. Перед использованием этой команды необходимо сначала удалить сгруппированные элементы.
- Для быстрой работы c группами данных надо выделить ячейки в области названий строк или столбцов сводной таблицы, щелкнуть правой кнопкой мыши на любой из выделенных ячеек и выбрать Развернуть/Cвернуть
(Expand/Collapse)
При группировке дат и числовых полей есть небольшой нюанс: иногда, при наличии дат или чисел в ячейках и вызове группировки можно получить сообщение "Выделенные объекты нельзя объединить в группу":
И вроде бы в ячейках даты/числа - но группироваться отказываются. В данном случае следует проверить - а действительно ли числа это числа, а даты - даты? Как бы странно это не звучало - случается подобное не так уж редко. Т.е. выглядят в ячейках данные как числа или даты, а на деле это просто текст. В большинстве случаев Excel подсвечивает такие ячейки зелеными треугольничками в левом верхнем углу:
В этом случае все просто: находим самую первую ячейку с таким треугольничком и выделяем все нижестоящие ячейки(до конца таблицы(
После этого обязательно необходимо перейти в сводную таблицу и обновить её(выделить любую ячейку сводной таблицы →Правая кнопка мыши →Обновить
Но может быть и так, что зеленых треугольников нет, а группировка не работает. Для начала следует убедиться, что в столбце нет ячеек с явным текстом, а так же ячеек с пробелами. По хорошему - пустые ячейки тоже лучше убрать или заменить нолями. Как правило это можно определить через фильтр. Опять обновить пару раз сводную.
Протянуть формулу на весь столбец данных. Там, где будет результатом
- назначить всем ячейкам с нашими "датами" формат даты или общий (желательно до действий ниже)
- скопировать любую пустую ячейку на листе
- выделить все ячейки с датами/числами(или столбец полностью)
- правая кнопка мыши -Специальная вставка
(Paste Special) -в окне выбрать Значения(Values) , операция - Сложить(Multiply) - ОК
Excel автоматом преобразует даты и числа в нормальные данные. Возможно, придется заново задать формат датам - но это уже совершенно не сложно: правая кнопка мыши -Формат ячеек
Про другие возможности Специальной вставки можно прочитать в этой статье: Как быстро умножить/разделить/сложить/вычесть из множества ячеек одно и то же число?
Так же см.:
Общие сведения о сводных таблицах
Сводная таблица из нескольких листов
Сводная из базы данных Access
Автообновляемая сводная таблица
Как перейти к редактированию исходных данных прямо из сводной таблицы?
Можно ли добавить дополнительные "Промежуточные итоги" для сводной таблицы, содержащей большую структуру - столбцов.
Так, чтобы эти дополнительные итоги - показывали итоги по каждой структуре.
Например, есть сводная таблица по месяцам продаж (строки) по Магазинам, Маркам, Цветам товара (столбцы).
Хотелось бы увидеть в столбцах: Общие итоги (+), Итоги по Магазину (+), Итоги по Марке (- не дает, только внутри каждого магазина), Итоги по Цвету (+), Итоги по Магазину-Марке(+), Итоги по Марке-Цвету(- не дает), Итоги по Магазину -Цвету (не дает).....
Итого 7 итогов: 4 могу сделать, а 3 не получается ( в одной таблице)...... Приходится делать надстройку поверх Сводной....