Группирование строк данных (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 (Результат)
Как скопировать группировку строк в excel
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Структурирование (группировка) данных на листе
Если у вас есть список данных, которые нужно группировать и суммировать, можно создать структуру до восьми уровней. Каждый внутренний уровень, представленный более высоким числом в символы структуры, отображает детали для предыдущего внешнего уровня, представленного меньшим числом в символах структуры. Используйте структуру для быстрого отображения строк или столбцов сводки или отображения подробных данных для каждой группы. Можно создать структуру строк (как показано в примере ниже), структуру столбцов или структуру строк и столбцов.
1. Чтобы отобразить строки для уровня, выберите соответствующие
- Уровень 1 содержит общий объем продаж для всех строк сведений.
- Уровень 2 содержит общий объем продаж за каждый месяц в каждом регионе.
- Уровень 3 содержит подробные строки— в этом случае строки от 17 до 20.
2. Чтобы развернуть или свернуть данные в структуре, щелкните
символы структуры или нажмите клавиши ALT+SHIFT+= для развертывания и ALT+SHIFT+- для сворачивания.
Создание многоуровневой структуры из строк
- Убедитесь, что каждый столбец данных, который требуется структурировать, имеет метку в первой строке (например, Регион), содержит аналогичные факты в каждом столбце, а диапазон, который требуется обвести, не содержит пустых строк или столбцов.
- При необходимости сгруппированные строки сведений могут иметь соответствующую сводную строку — промежуточный итог. Чтобы создать их, выполните одно из следующих действий.
- Вставка итоговых строк при помощи команды Промежуточные итоги Используйте команду Итоги для вставки функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ непосредственно под или над каждой группой строк с подробными данными и для автоматического создания многоуровневой структуры. Подробнее об использовании функции «Итоги» читайте в статье Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
- Вставка собственных итоговых строк Вставьте собственные итоговые строки с формулами непосредственно под или над каждой группой строк с подробными данными. Например, под (или над) строками данных о продажах за март и апрель используйте функцию СУММ, чтобы подсчитать промежуточные итоговые данные по продажам за эти месяцы. Этот пример показан в таблице далее в этой статье.
- По умолчанию Excel ищет сводные строки под сведениями, которые они обобщают, но их можно создать над строками сведений. Если вы создали строки сводки под подробными сведениями, перейдите к следующему шагу (шаг 4). Если вы создали строки сводки над строками сведений, перейдите в раздел Данные >Структура, а затем выберите средство запуска диалогового окна.
Откроется диалоговое окно Параметры.
В диалоговом окне Параметры снимите флажок Сводка строк ниже и нажмите кнопку ОК.
- Выделите ячейку в диапазоне ячеек, которые нужно выделить.
- Перейдите в раздел Структура >>Группа данных > структура, а затем выберите Автоматические структуры.
Структурирование данных вручную
Важно: При группировке уровней структуры вручную лучше отобразить все данные , чтобы избежать неправильной группировки строк.
- Чтобы очертить внешнюю группу (уровень 1), выделите все строки, которые содержит внешняя группа (т. е. строки сведений и, если вы добавили их, их суммарные строки).
Совет: Если выбрать целые строки, а не только ячейки, Excel автоматически группирует по строкам— диалоговое окно Группа даже не открывается.
Примечание: Если вам не нужно создавать внутренние группы, перейдите к шагу f ниже.
Для каждой внутренней вложенной группы выберите строки с подробными данными, соответствующие итоговой строке.
Перейдите в раздел> группа > >данных.
В диалоговом окне Группа выберите Строки, а затем нажмите кнопку ОК. Рядом с группой на экране появятся знаки структуры.
Совет: Если выбрать целые строки, а не только ячейки, Excel автоматически группирует по строкам— диалоговое окно Группа даже не открывается.
для группы, а затем перейдите в раздел > структура >Разгруппировать > Разгруппировать.
Важно: Если вы разгруппируете структуру, пока подробные данные скрыты, строки сведений могут оставаться скрытыми. Для отображения данных перетащите указатель через номера видимых строк рядом со скрытыми строками. Затем перейдите в раздел Главная > Ячейки > Формат, наведите указатель мыши на пункт Скрыть & Отобразить, а затем выберите Отобразить строки.
Создание многоуровневой структуры из столбцов
- Убедитесь в том, что в первом столбце каждой строки данных, для которых требуется создать структуру, имеется заголовок, что каждая строка содержит данные одного типа и что в диапазоне данных отсутствуют пустые строки или столбцы.
- Вставьте собственные итоговые столбцы с формулами непосредственно справа или слева от каждой группы строк с подробными данными. В таблице, приведенной в шаге 4, показан пример.
Примечание: Для структурирования данных по столбцам обязательно наличие итоговых столбцов с формулами, которые ссылаются на ячейки в каждом из столбцов с подробными данными этой группы.
Если столбец сводки находится слева от столбцов сведений, перейдите в раздел Данные >Структура, а затем выберите средство запуска диалогового окна.
Откроется диалоговое окно Параметры.
- Выделите ячейку в диапазоне.
- Перейдите в раздел > структура >группа >группа, а затем выберите Пункт Автоматические структуры.
Структурирование данных вручную
Важно: Группируя уровни структуры вручную, отобразите на экране все данные во избежание ошибок при группировке столбцов.
- Чтобы очертить внешнюю группу (уровень 1), выберите все подчиненные сводные столбцы, а также связанные с ними подробные данные.
Перейдите в раздел >>>группа данных.
Перейдите в раздел Группа >структуры > данных.
Вы также можете разгруппировать разделы структуры, не удаляя весь уровень. Удерживая нажатой клавишу SHIFT, выберите
группы, а затем перейдите в раздел > структура >Разгруппировать.
Если разгруппировать структуру, пока подробные данные скрыты, столбцы сведений могут оставаться скрытыми. Для отображения данных перетащите указатель через номера видимых столбцов рядом со скрытыми столбцами. Перейдите в раздел Главная > Ячейки > формат, наведите указатель мыши на пункт Скрыть & Отобразить, а затем выберите Отобразить столбцы.
Отображение и скрытие структурированных данных
- Если символы структуры не отображаются
-
Отображение или скрытие подробных данных для группы Чтобы отобразить подробные данные в группе, нажмите кнопку
символов структуры выберите нужный номер уровня. Подробные данные более низких уровней будут скрыты. Например, если структура содержит четыре уровня, можно скрыть четвертый уровень при отображении остальных уровней, выбрав
символов структуры. Например, если есть три уровня, выберите
Настройка многоуровневой структуры со стилями
Для выделенных строк Microsoft Excel использует такие стили, как RowLevel_1 и RowLevel_2 . Для контурных столбцов Excel использует такие стили, как ColLevel_1 и ColLevel_2. В этих стилях используются полужирные, курсивные и другие текстовые форматы, чтобы различать строки или столбцы сводки в данных. Изменив способ определения каждого из этих стилей, можно применить различные форматы текста и ячеек для настройки внешнего вида структуры. Стиль можно применить к структуре либо при создании структуры, либо после ее создания.
Выполните одно или несколько из указанных ниже действий.
Автоматическое применение стиля к новым строкам или столбцам сводки
-
Перейдите в раздел Данные >Структура, а затем выберите средство запуска диалогового окна.
Откроется диалоговое окно Параметры.
Назначение стиля существующей итоговой строке или столбцу
- Выделите ячейки, к которым требуется применить стиль.
- Перейдите в раздел Данные >Структура, а затем выберите средство запуска диалогового окна.
Откроется диалоговое окно Параметры.
Выберите проверка автоматические стили, а затем выберите Применить стили.
Для форматирования структурированных данных также можно применять автоформаты.
Определение уровня группировки строки
Программисты 1С любят создавать отчеты Excel, которые содержат группировку строк. С одной стороны это достаточно удобно, однако появляется ряд проблем:
- Сложно скопировать строки определенного уровня, так как при этом отображаются уровни выше.
- При копировании строк определенного уровня копируются также и скрытые уровни.
- При протягивании формул они распространяются и на скрытые уровни.
Как помогает функция
С помощью функции можно заполнить отдельный столбец, который содержит нумерацию группировки строк. А далее установить фильтр по данному столбцу. При этом описанных выше проблем не будет.
Функция =УРОВЕНЬСТРОКИ(ЯЧЕЙКА) вернет уровень группировки строки (от 1 до 8) в указанной ячейке.
Пример
Код на VBA
Function УРОВЕНЬСТРОКИ(ЯЧЕЙКА As Range) As Long УРОВЕНЬСТРОКИ = ЯЧЕЙКА.Rows(1).OutlineLevel End Function
Надстройка
VBA-Excel
Надстройка для Excel содержит большой набор полезных функций, с помощью которых вы значительно сократите время и увеличите скорость работы с программой.