Группирование строк данных (Power Query)
В Power Query одни и те же значения можно сгруппировать в одном или нескольких столбцах в одну сгруппированную строку. Столбец можно группировать с помощью агрегатной функции или группы по строке.
Следующие процедуры основаны на этом примере данных запроса:
Группирование столбца с помощью агрегатной функции
Вы можете группировать данные с помощью агрегатной функции, например Sum и Average. Например, необходимо свести итоговые суммы проданных единиц на уровне страны и канала продаж, сгруппированные по столбцам Страна и Канал продаж .
- Чтобы открыть запрос, найдите ранее загруженный из Редактор Power Query, выберите ячейку в данных, а затем выберите Запрос >Изменить. Дополнительные сведения см. в статье Создание, изменение и загрузка запроса в Excel.
- Выберите Главная >Группировать по.
- В диалоговом окне Группировать по выберите Дополнительно , чтобы выбрать несколько столбцов для группировки.
- Чтобы добавить другой столбец, выберите Добавить группирование.
Имя нового столбца введите «Всего единиц» для нового заголовка столбца.
Операции Выберите Сумма. Доступные агрегаты: Sum, Average, Median, Min, Max, Count Rows и Count Distinct Rows.
Result (Результат)
Группировка по строке
Операция строки не требует столбца, так как данные группируются по строке в диалоговом окне Группировать по. При создании нового столбца можно выбрать два варианта:
Счетчик строк , отображающий количество строк в каждой сгруппированной строке.
Все строки Вставляется внутреннее значение таблицы. Он содержит все строки для столбцов, по которым вы группируете. При необходимости можно развернуть столбцы позже. Дополнительные сведения см. в статье Работа со структурированным столбцом списка, записи или таблицы.
Последовательность действий
Например, чтобы сгруппировать по всем строкам, требуется общее количество проданных единиц и два других столбца, которые дают имя и единицы, проданные для наиболее производительного продукта, сводные на уровне страны и канала продаж.
- Чтобы открыть запрос, найдите ранее загруженный из Редактор Power Query, выберите ячейку в данных, а затем выберите Запрос >Изменить. Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.
- Выберите Главная >Группировать по.
- В диалоговом окне Группировать по выберите Дополнительно , чтобы выбрать несколько столбцов для группировки.
- Добавьте столбец для агрегирования, выбрав Добавить агрегат в нижней части диалогового окна.
Агрегирование агрегирования столбца Units с помощью операции Sum. Назовите этот столбец Всего единиц.
Result (Результат)
Группировка и отмена группировки данных в сводной таблице
Группировка позволяет выделить для анализа определенное подмножество данных сводной таблицы. Например, вы можете захотеть сгруппировать поля даты и времени из громоздкого списка в сводной таблице по кварталам и месяцам.
Группировка данных
- Щелкните значение в сводной таблице правой кнопкой мыши и выберите команду Группировать.
- В окне Группировка установите флажки Начиная с и Заканчивая и при необходимости измените значения.
- В разделе С шагом выберите период времени. Для числовых полей введите число, которое указывает интервал для каждой группы.
- Нажмите кнопку ОК.
Группировка выделенных элементов
- Удерживая нажатой клавишу CTRL, выделите несколько значений.
- Щелкните правой кнопкой мыши и выберите команду Группировать.
Группировка по дате и времени
Приложение автоматически обнаруживает связи между значениями соответствующих полей и группирует их, когда пользователь добавляет в сводные таблицы строки полей времени. Созданную группу можно перетащить в сводную таблицу для анализа.
Присвоение имени группе
- Выберите группу.
- На вкладке Анализ нажмите кнопку Параметры поля. На вкладке Сводная таблица вАктивном поле щелкните Параметры поля.
- Измените пользовательское имя, как вам нравится, а затем нажмите кнопку ОК.
Разгруппировка сгруппированных данных
Группировка и разгруппировка данных в Excel
При обработке большого объема данных довольно часто требуется их упорядочивание. Специально для этого в программе Excel предусмотрены различные функции, одной из которых является группировка. С ее помощью, как следует из названия, можно сгруппировать данные, а также, скрыть неактуальную информацию. Давайте разберемся, как это работает.
Содержание скрыть
- Настраиваем параметры функции
- Группируем данные по строкам
- Группируем столбцы
- Создаем многоуровневую группировку
- Разгруппировываем данные
- Заключение
Настраиваем параметры функции
Смотрите также: “Выделение ячеек в Excel: строка, столбец, диапазон и лист целиком”
Чтобы в конечном счете получить желаемый результат, для начала следует выполнить настройки самой функции. Для этого выполняем следующие шаги:
- Переключившись во вкладку “Данные” щелкаем по кнопке “Структура” и в открывшемся перечне команд – по небольшому значку в виде стрелки, направленной по диагонали вниз.
- На экране отобразится небольшое окошко с параметрами функции. Здесь мы можем настроить отображение итогов. Ставим галочки напротив нужных опций (в т.ч. автоматические стили) и жмем кнопку OK.
Примечание: расположение итоговых данных в строках под данными многим кажется неудобным, поэтому данный параметр можно выключить. - Все готово, теперь можем перейти, непосредственно, к самой группировке данных.
Группируем данные по строкам
Для начала давайте рассмотрим, как можно сгруппировать строки:
- Вставляем новую строку над или под строками, которые хотим сгруппировать (зависит от того, какой вид расположения итогов по строкам мы выбрали). Как это сделать, читайте в нашей статье – “Как добавить новую строку в Excel“.
- В самой левой ячейке добавленной строки пишем название, которое хотим присвоить группе.
- Любым удобным способом, например, с помощью зажатой левой кнопки мыши производим выделение ячеек строк (кроме итоговой), которые требуется сгруппировать. Во вкладке “Данные” щелкаем по кнопке “Структура” и в открывшемся списке выбираем функцию “Группировать”. Щелкнуть нужно именно по значку команды, а не по ее названию.
Если же нажать на последнее (со стрелкой вниз), откроется еще одно подменю, в котором следует нажать на одноименную кнопку.
- В появившемся окошке отмечаем пункт “строки” (должен быть выбран по умолчанию) и подтверждаем действие нажатием OK.
Примечание: Если вместо ячеек выделить все строки целиком на вертикальной панели координат, а затем применить группировку, то промежуточного окна с выбором строки или столбца не будет, так как программа сразу понимает, что именно ей необходимо сделать.
- Группа создана, о чем свидетельствуют появившаяся на панели координат полоска со знаком “минус”. Это означает, что сгруппированные данные раскрыты. Чтобы их скрыть, нажимам по минусу или кнопке с цифрой “1” (самый верхний уровень группировки).
- Теперь строки скрыты. Чтобы их обратно раскрыть, нажимаем по значку “плюса”, который появился вместо “минуса” (или по кнопке “2”).
Группируем столбцы
Чтобы сгруппировать столбцы, придерживаемся примерно такого же алгоритма действий, описанного выше:
- Вставляем столбец справа или слева от группируемых – зависит от выбранного параметра в настройках функции. Подробнее о том, как это сделать, читайте в нашей статье – “Как вставить столбец в таблицу Эксель“.
- Пишем название в самой верхней ячейке нового столбца.
- Выделяем ячейки группируемых столбцов (за исключением добавленного) и применяем функцию группировки.
- Ставим отметку напротив варианта “столбцы” и кликам OK.
Примечание: как и в случае с группировкой строк, при выделении столбцов целиком на горизонтальной панели координат, группировка данных будет выполнена сразу, минуя промежуточное окно с выбором элементов.
- Задача успешно выполнена.
Создаем многоуровневую группировку
Возможности программы позволяют выполнять как одноуровневые, так и многоуровневые группировки. Вот как это делается:
- В раскрытом состоянии главной группы, внутри которой планируется создать еще одну, выполняем действия, рассмотренные в разделах выше в зависимости от того, с чем мы работаем – со строками или столбцами.
- Таким образом, мы получили многоуровневую группировку.
Разгруппировываем данные
Когда ранее выполненная группировка столбцов или строк больше не нужна или требуется выполнить ее иначе, можно воспользоваться обратной функцией – “Разгруппировать”:
- Производим выделение сгруппированных элементов, после чего все в той же вкладке “Данные” в группе инструментов “Структура” выбираем команду “Разгруппировать”. Жмем именно по значку, а не по названию.
- В открывшемся окне ставим отметку напротив требуемого пункта (в нашем случае – “строки”) и нажимаем OK.
Примечание: в случае многоуровневой группировки или наличия нескольких групп данных, каждую из них необходимо расформировать отдельно. - Вот и все, что требовалось сделать.
Заключение
Группировка данных выполняется в несколько кликов и не требует особых навыков в работе с программой, однако, данный прием позволяет существенно сэкономить время, когда приходится иметь дело с большим объемом информации. Это делает функцию одной из самых полезных и незаменимых в Excel.
Группирование или сводка строк
В Power Query можно группировать значения в разных строках в одно значение, группируя строки в соответствии со значениями в одном или нескольких столбцах. Вы можете выбрать один из двух типов операций группирования:
- Группировки столбцов.
- Группировки строк.
В этом руководстве вы будете использовать следующую примерную таблицу.
Таблица со столбцами, показывающими Год (2020), Страна (США, Панама или Канада), Продукт (рубашка или шорты), Канал продаж (онлайн или торговый посредник) и Единицы (различные значения от 55 до 7500)
Где найти группу по кнопке
Группу можно найти в трех местах:
- На вкладке «Главная » в группе «Преобразование «.
- На вкладке «Преобразование» в группе таблиц.
- В контекстном меню при щелчке правой кнопкой мыши выберите столбцы.
Использование статистической функции для группировки по одному или нескольким столбцам
В этом примере цель состоит в том, чтобы суммировать общее количество проданных единиц на уровне страны и канала продаж. Для выполнения группы по операции вы будете использовать столбцы Country and Sales Channel .
- На вкладке «Главная» выберите группу.
- Выберите параметр «Дополнительно«, чтобы выбрать несколько столбцов для группировки.
- Выберите столбец «Страна«.
- Выберите » Добавить группирование«.
- Выберите столбец «Канал продаж».
- В поле «Создать столбец» введите «Всего единиц» в операции, выберите «Сумма» и » Столбец» выберите «Единицы«.
- Выберите ОК
Эта операция предоставляет следующую таблицу.
Доступные операции
С помощью группы по функциям доступные операции можно классифицировать двумя способами:
- Операция уровня строк
- Операция уровня столбца
В следующей таблице описывается каждая из этих операций.
| Имя операции | Категория | Description |
|---|---|---|
| Sum | Операция столбца | Суммирует все значения из столбца |
| Average | Операция столбца | Вычисляет среднее значение из столбца |
| Median | Операция столбца | Вычисляет медиану из столбца |
| Min | Операция столбца | Вычисляет минимальное значение из столбца |
| Max | Операция столбца | Вычисляет максимальное значение из столбца |
| Процентиль | Операция столбца | Вычисляет процентиль, используя входное значение от 0 до 100 из столбца. |
| Подсчет уникальных значений | Операция столбца | Вычисляет количество отдельных значений из столбца |
| Count : подсчет строк | Операция строки | Вычисляет общее количество строк из заданной группы |
| Подсчет отдельных строк | Операция строки | Вычисляет количество отдельных строк из заданной группы. |
| Все строки | Операция строки | Выводит все сгруппированные строки в табличном значении без агрегирования |
В Power Query Online доступны разные значения и операции процентиля .
Выполнение операции для группировки по одному или нескольким столбцам
Начиная с исходного примера, в этом примере вы создадите столбец с общими единицами и двумя другими столбцами, которые дают вам имя и единицы, проданные для наиболее производительного продукта, суммируются на уровне страны и канала продаж.
- Используйте следующие столбцы в качестве группы по столбцам :
- Страна/регион
- Канал продаж
- Создайте два новых столбца, выполнив следующие действия.
- Агрегирование столбца «Единицы» с помощью операции Sum . Присвойте этому столбцу общее число единиц.
- Добавьте новый столбец Products с помощью операции «Все строки «.
После завершения операции обратите внимание, что столбец Products имеет значения [Table] внутри каждой ячейки. Каждое значение [Таблица] содержит все строки, сгруппированные по столбцам Country и Sales Channel из исходной таблицы. Вы можете выбрать пробел в ячейке, чтобы просмотреть предварительное представление содержимого таблицы в нижней части диалогового окна.
Панель предварительного просмотра сведений может не отображать все строки, которые использовались для операции с группировкой. Вы можете выбрать значение [Таблица], чтобы просмотреть все строки, относящиеся к соответствующей операции по группе.
Затем необходимо извлечь строку, которая имеет наибольшее значение в столбце «Единицы» таблиц в новом столбце Products, и вызвать новый продукт верхнего исполнителя столбца.
Извлечение сведений о продукте верхнего исполнителя
Используя новый столбец Products со значениями [Таблица], создайте новый настраиваемый столбец, перейдя на вкладку «Добавить столбец » на ленте и выбрав настраиваемый столбец из группы «Общие «.
Присвойте новому продукту верхнего исполнителя столбца. Введите формулу Table.Max([Products], «Units» ) в формуле настраиваемого столбца.
Результат этой формулы создает новый столбец со значениями [Record]. Эти значения записей по сути являются таблицей с одной строкой. Эти записи содержат строку с максимальным значением столбца «Единицы » каждого значения [Таблица] в столбце Products .
С помощью этого нового столбца продукта верхнего исполнителя, содержащего значения [Запись], можно выбратьзначок развертывания, выбрать поля «Продукт и единицы«, а затем ОК.
После удаления столбца Products и задания типа данных для обоих вновь развернутых столбцов результат будет выглядеть следующим образом.
Нечеткое группирование
Следующая функция доступна только в Power Query Online.
Чтобы продемонстрировать, как выполнить «нечеткое группирование», рассмотрим пример таблицы, показанной на следующем рисунке.
Цель нечеткой группировки — выполнить операцию по группе, которая использует приблизительный алгоритм сопоставления для текстовых строк. Power Query использует алгоритм сходства Jac карта для измерения сходства между парами экземпляров. Затем он применяет агломеративную иерархическую кластеризация для группирования экземпляров вместе. На следующем рисунке показаны ожидаемые выходные данные, в которых таблица будет сгруппирована по столбцу Person .
Чтобы выполнить нечеткое группирование, выполните те же действия, которые ранее описаны в этой статье. Единственное различие заключается в том, что на этот раз в диалоговом окне «Группа по» выберите поле «Использовать нечеткое группирование» проверка.
Для каждой группы строк Power Query выбирает наиболее частый экземпляр в качестве канонического экземпляра. Если несколько экземпляров происходят с одной частотой, Power Query выберет первый экземпляр. После нажатия кнопки «ОК» в диалоговом окне «Группа по«, вы получите ожидаемый результат.
Однако у вас больше контроля над нечеткой операцией группировки путем расширения параметров нечетких групп.
Для нечетких группирования доступны следующие параметры:
- Порог сходства (необязательно) — этот параметр указывает, насколько похожи два значения должны быть сгруппированы. Минимальный параметр 0 приведет к группировке всех значений. Максимальное значение 1 позволяет сгруппировать только значения, соответствующие точно сгруппированы. Значение по умолчанию — 0.8.
- Игнорировать регистр. При сравнении текстовых строк регистр будет игнорироваться. Этот параметр по умолчанию включен.
- Группируйте путем объединения текстовых частей: алгоритм попытается объединить текстовые части (например, объединение Микро и обратимо в Корпорацию Майкрософт) для группирования значений.
- Отображение показателей сходства: отображение показателей сходства между входными значениями и вычисляемых репрезентативных значений после нечетких группирования. Требуется добавление операции, например «Все строки» , чтобы продемонстрировать эти сведения на уровне строк.
- Таблица преобразования (необязательно): вы можете выбрать таблицу преобразования, которая будет сопоставлять значения (например, сопоставление MSFT с Корпорацией Майкрософт) для группировки их вместе.
В этом примере таблица преобразования будет использоваться для демонстрации того, как можно сопоставить значения. Таблица преобразования содержит два столбца:
- Из: текстовая строка для поиска в таблице.
- Чтобы: текстовая строка, используемая для замены текстовой строки в столбце From .
На следующем рисунке показана таблица преобразования, используемая в этом примере.
Важно, чтобы в таблице преобразования были одинаковые столбцы и имена столбцов, как показано выше (они должны быть «From» и «To»), в противном случае Power Query не распознает эти столбцы.
Вернитесь в группу по диалоговому окне, разверните параметры нечеткой группы, измените операцию с счетчиков на все строки, включите параметр «Показать оценки сходства», а затем выберите раскрывающееся меню таблицы преобразования.
После выбора таблицы преобразования нажмите кнопку «ОК«. Результат этой операции дает следующие сведения:
В этом примере параметр «Игнорировать регистр» включен, поэтому значения в столбце From таблицы преобразования используются для поиска текстовой строки, не учитывая регистр строки. Эта операция преобразования выполняется сначала, а затем выполняется нечеткое группирование.
Оценка сходства также отображается в табличном значении рядом с столбцом person, который отражает точное группирование значений и их соответствующие оценки сходства. Этот столбец можно развернуть при необходимости или использовать значения из новых столбцов частоты для других типов преобразований.
При группировке по нескольким столбцам таблица преобразования выполняет операцию замены во всех столбцах, если при замене значения увеличивается оценка сходства.