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

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

Пример 1: Группировка в сводной таблице по дате

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

Если необходимо выполнить группировку данных о продажах по месяцам, то это можно создать вот так:

1. Кликните правой клавишей мыши по левому столбцу сводной таблицы (столбец с датами) и изберите команду Группировать (Group). Покажется диалоговое окно Группирование (Grouping) для дат.
2. Изберите Месяцы (Month) и нажмите ОК. Данные таблицы будут сгруппированы по месяцам, как показано в сводной таблице ниже.

Пример 2: Группировка в сводной таблице по спектру

Представим, мы сделали сводную таблицу (как на картинке ниже), которая группирует перечень из 150 малышей по возрасту. Группы делятся по возрастам от 5 до 16 лет.

Если необходимо пойти еще далее и соединить возрастные группы в группы 5-8 лет, 9-12 лет и 13-16 лет, то можно поступить вот так:

1. Кликните правой клавишей мыши по левому столбцу сводной таблицы (столбец с возрастами) и изберите команду Группировать (Group). Покажется диалоговое окно Группирование (Grouping) для чисел.Excel автоматом заполнит поля Начиная с (Starting At) и По (Ending At) наименьшим и наибольшим значениями из наших начальных данных (в нашем примере это 5 и 16).
2. Мы желаем соединить возрастные группы в группы по 4 года, как следует, в поле С шагом (By) вводим значение 4. Жмём ОК.Таковым образом возрастные группы будут объединены в группы начиная с 5-8 лет и дальше с шагом по 4 года. В итоге получится вот таковая таблица:

Как разгруппировать сводную таблицу

Чтоб разгруппировать значения в сводной таблице, необходимо:

• Кликнуть правой клавишей мыши по левому столбцу сводной таблицы (столбец, содержащий сгруппированные значения);
• В показавшемся меню надавить Разгруппировать (Ungroup).

Распространённые ошибки при группировке в сводной таблице

Ошибка при группировке в сводной таблице: Выделенные объекты недозволено соединить в группу (Cannot group that selection).

Время от времени при попытке выполнить группировку в сводной таблице оказывается, что команда Группировать (Group) в меню не активна, либо возникает окно с сообщением о ошибке Выделенные объекты недозволено соединить в группу (Cannot group that selection). Это происходит почаще всего поэтому, что в столбце данных в начальной таблице содержатся нечисловые значения либо ошибки. Чтоб поправить это, необходимо заместо нечисловых значений вставить числа либо даты.

Опосля этого кликните правой клавишей мыши по сводной таблице и нажмите Обновить (Refresh). Данные в сводной таблице будут обновлены, и сейчас группировка строк либо столбцов обязана быть доступна.

Функции сводных таблиц в DAX: GROUPBY и SUMMARIZECOLUMNS в Power BI и Power Pivot

Приветствую Вас, дорогие друзья, с Вами Будуев Антон. В данной статье мы продолжим учить DAX функции, создающие в Power BI и Power Pivot сводные таблицы, а непосредственно, это GROUPBY и SUMMARIZECOLUMNS.

Напомню, что в предшествующей статье мы уже разглядывали фаворита по формированию сводных таблиц — функцию SUMMARIZE, ну а на данный момент, как я уже написал выше, мы разглядим еще пару инструментов в данной для нас теме.

Для Вашего удобства, рекомендую скачать «Справочник DAX функций для Power BI и Power Pivot» в PDF формате.

Если же в Ваших формулах имеются какие-то ошибки, задачи, а результаты работы формул повсевременно не те, что Вы ждете и Для вас нужна помощь, то записывайтесь в бесплатный экспресс-курс «Резвый старт в языке функций и формул DAX для Power BI и Power Pivot».

Также, подписывайтесь на наши социальные сети. Поэтому что конкретно в их, Для вас будут доступны оперативно и любой денек наши животрепещущие фишки, секреты, выработки, примеры, кейсы, полезные советы, видео и статьи по темам сквозной BI аналитики (Power BI, DAX, Power Pivot, Excel…): Вконтакте, Инстаграм, Фейсбук, YouTube.

DAX функция GROUPBY в Power BI и Power Pivot

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

Также, нередко я встречаю раздельное написание данной для нас функции, как DAX GROUP BY, что некорректно…

• ‘Таблица’ — начальная существующая таблица либо табличное выражение, значения которых мы желаем сгруппировать
• [Столбец] — столбец для группировки
• «Имя столбца» — имя создаваемого столбца для значений группировки
• Выражение — вычисляемое выражение для значений группировки

Выражение непременно обязано содержать статистическую DAX функцию формата X (SUMX, MAXX, AVERAGEX…), во входных параметрах которой, в качестве таблицы подставляется служебное выражение CURRENTGROUP ().

Давайте разберем все характеристики GROUPBY, в том числе и служебное выражение CURRENTGROUP на примере формулы.

Для разбора примера сделаем в модели данных вычисляемую таблицу по формуле с ролью GROUPBY.

Потому что в Excel (Power Pivot) в модели данных создавать вычисляемые таблицы недозволено, то пример будем разглядывать на базе Power BI — в ней можно создавать физические вычисляемые таблицы. А в Excel вычисляемые таблицы создаются лишь виртуально, во время вычисления самих формул.

Итак, в Power BI Desktop имеется начальная таблица «Реализации Менеджеров»:

Сделаем во вкладке «Моделирование» вычисляемую таблицу на базе формулы с ролью DAX функции GROUPBY:

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

Во 2-м и 3-ем параметрах (2-ая строчка характеристик) мы прописали столбцы [Отдел] и [Менеджер]. Конкретно по ним и будет происходить группировка всех значений.

В четвертом параметре (3-я строчка характеристик) мы прописали заглавие новейшего столбца, в котором расположатся агрегированные значения в создаваемой сводной таблице.

В 5-ом параметре, согласно синтаксису GROUPBY, расположилась вложенная функция SUMX на базе которой, будет рассчитываться агрегированная сумма всех продаж по группам. В качестве входящей таблицы в SUMX указана служебное выражение CURRENTGROUP.

Итак, результатом выполнения формулы на базе DAX функции GROUPBY будет последующая сводная таблица:

В данной для нас сделанной таблице с помощью GROUPBY нам удалось собрать все реализации воедино по любому менеджеру всякого отдела в организации.

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

DAX функция SUMMARIZECOLUMNS в Power BI и Power Pivot

SUMMARIZECOLUMNS () — весьма схожая DAX функция на GROUPBY, а тем наиболее на SUMMARIZE. Она также делает сводную таблицу, но, в данном случае, с возможностью фильтрации группируемых столбцов.

Синтаксис функции SUMMARIZECOLUMNS весьма похож на синтаксис GROUPBY, который мы разглядывали выше, кроме только той различия, что:

• в первом параметре не надо прописывать начальную таблицу;
• в выражении мы можем применять не только лишь X функции, да и любые остальные функции агрегирования
• тут возник очередной параметр — фильтр, по которому делается фильтрация столбцов для группировки

Разглядим пример формулы на базе DAX функции SUMMARIZECOLUMNS. Как и выше, пример мы будем разглядывать в Power BI на базе все той же начальной таблицы «Реализации Менеджеров»:

Сделаем в Power BI Desktop во вкладке «Моделирование» новейшую вычисляемую таблицу и пропишем там последующую формулу с ролью функции SUMMARIZECOLUMNS:

В первой строке мы прописали столбцы [Отдел] и [Менеджер], по которым будет происходить группировка значений.

Во 2-ой строке прописали фильтр, сделанный на базе DAX функции FILTER. Данная функция фильтрует начальную таблицу «Реализации Менеджеров» по столбцу [Менеджер], где его значения не должны быть равны значению «Петров».

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

В четвертой строке само выражение агрегации на базе функции SUM, которая сложит все реализации по категориям группировки.

Итак, результатом выполнения формулы выше на базе работы DAX функции SUMMARIZECOLUMNS будет последующая сводная таблица:

Как мы лицезреем, создалась сводная таблица по продажам менеджеров, но без менеджера Петров, потому что его мы отфильтровали в самой формуле SUMMARIZECOLUMNS.

Итак, на этом, с разбором DAX функций, создающих сводные таблицы в Power BI и Power Pivot, все.

Пожалуйста, оцените статью:

1. 5
2. 4
3. 3
4. 2
5. 1

Фурроров Для вас, друзья!
С почтением, Будуев Антон.
Проект «BI — это просто»

Если у Вас возникли какие-то вопросцы по материалу данной статьи, задавайте их в комментах ниже. Я Для вас непременно отвечу. Ну и совершенно, просто оставляйте там Вашу оборотную связь, я буду весьма рад.

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

Приглянулся материал статьи?
Добавьте эту статью в закладки Вашего браузера, чтоб возвратиться к ней снова. Для этого, прямо на данный момент нажмите на клавиатуре комбинацию кнопок Ctrl+D

Что еще поглядеть / почитать?

RANKX — функция ранжирования (ранга) в DAX (Power BI и Power Pivot)