Как сгруппировать строки в excel

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

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

Группировка данных

1. Щелкните значение в сводной таблице правой кнопкой мыши и выберите команду Группировать.
2. В окне Группировка установите флажки Начиная с и Заканчивая и при необходимости измените значения.
3. В разделе С шагом выберите период времени. Для числовых полей введите число, которое указывает интервал для каждой группы.
4. Нажмите кнопку ОК.

Группировка выделенных элементов

1. Удерживая нажатой клавишу CTRL, выделите несколько значений.
2. Щелкните правой кнопкой мыши и выберите команду Группировать.

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

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

Присвоение имени группе

1. Выберите группу.
2. На вкладке Анализ нажмите кнопку Параметры поля. На вкладке Сводная таблица вАктивном поле щелкните Параметры поля.
3. Измените пользовательское имя, как вам нравится, а затем нажмите кнопку ОК.

Разгруппировка сгруппированных данных

Как сгруппировать строки в excel

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071

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

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

• Группировки столбцов.
• Группировки строк.

В этом руководстве вы будете использовать следующую примерную таблицу.

Таблица со столбцами, показывающими Год (2020), Страна (США, Панама или Канада), Продукт (рубашка или шорты), Канал продаж (онлайн или торговый посредник) и Единицы (различные значения от 55 до 7500)

Где найти группу по кнопке

Группу можно найти в трех местах:

• На вкладке «Главная » в группе «Преобразование «.
• На вкладке «Преобразование» в группе таблиц.
• В контекстном меню при щелчке правой кнопкой мыши выберите столбцы.

Использование статистической функции для группировки по одному или нескольким столбцам

В этом примере цель состоит в том, чтобы суммировать общее количество проданных единиц на уровне страны и канала продаж. Для выполнения группы по операции вы будете использовать столбцы Country and Sales Channel .

1. На вкладке «Главная» выберите группу.
2. Выберите параметр «Дополнительно«, чтобы выбрать несколько столбцов для группировки.
3. Выберите столбец «Страна«.
4. Выберите » Добавить группирование«.
5. Выберите столбец «Канал продаж».
6. В поле «Создать столбец» введите «Всего единиц» в операции, выберите «Сумма» и » Столбец» выберите «Единицы«.
7. Выберите ОК

Эта операция предоставляет следующую таблицу.

Доступные операции

С помощью группы по функциям доступные операции можно классифицировать двумя способами:

• Операция уровня строк
• Операция уровня столбца

В следующей таблице описывается каждая из этих операций.

Имя операции	Категория	Description
Sum	Операция столбца	Суммирует все значения из столбца
Average	Операция столбца	Вычисляет среднее значение из столбца
Median	Операция столбца	Вычисляет медиану из столбца
Min	Операция столбца	Вычисляет минимальное значение из столбца
Max	Операция столбца	Вычисляет максимальное значение из столбца
Процентиль	Операция столбца	Вычисляет процентиль, используя входное значение от 0 до 100 из столбца.
Подсчет уникальных значений	Операция столбца	Вычисляет количество отдельных значений из столбца
Count : подсчет строк	Операция строки	Вычисляет общее количество строк из заданной группы
Подсчет отдельных строк	Операция строки	Вычисляет количество отдельных строк из заданной группы.
Все строки	Операция строки	Выводит все сгруппированные строки в табличном значении без агрегирования

В Power Query Online доступны разные значения и операции процентиля .

Выполнение операции для группировки по одному или нескольким столбцам

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

1. Используйте следующие столбцы в качестве группы по столбцам :
   • Страна/регион
   • Канал продаж
2. Создайте два новых столбца, выполнив следующие действия.
   1. Агрегирование столбца «Единицы» с помощью операции Sum . Присвойте этому столбцу общее число единиц.
   2. Добавьте новый столбец 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, который отражает точное группирование значений и их соответствующие оценки сходства. Этот столбец можно развернуть при необходимости или использовать значения из новых столбцов частоты для других типов преобразований.

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

   Как сгруппировать строки в excel

   

   В сводных таблицах EXCELесть очень удобный инструмент группировки строк по какому-либо числовому параметру.

   Это позволяет быстро создать ценовые группы и сгруппировать объекты по ценам для удобства восприятия, а также для формирования ценовых предложений клиентам.

   Рассмотрим пример в сфере торговли недвижимостью.

   На первом фото –реальная база по объектам недвижимости с несколько измененными адресами.

   

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

   

   1. Сформировать сводную таблицу из нашей базы данных. Разместить группируемые данные в строках сводной таблицы.
   2. Определить, по какому из полей будем делать группировку. Обратите внимание, что группировка по строкам на основании данных может производиться только по числовым параметрам. В примере приведена группировка по ценам и по площади.
   3. Кликнуть правой клавишей на любом элементе строк и нажать «Группировать».

   

3. Задать шаг для группировки, например 5 000 000. В нашем случае, в связи с большим разбросом цен в базе это наиболее оптимальный вариант.
   
4. В итоге получаем несколько групп, созданных EXCEL. Для удобства, на фото свернута наиболее многочисленная группа, для того чтобы показать работу группировки. Уменьшая шаг (например 700 000), можно настроить категории внутри эконом-класса и т.п.
   

   
   

На последнем фото представлен вариант группировки по площади с шагом 20 кв.м. с сохранением группировки по цене.

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