Группировка и разгруппировка данных в Excel
При обработке большого объема данных довольно часто требуется их упорядочивание. Специально для этого в программе Excel предусмотрены различные функции, одной из которых является группировка. С ее помощью, как следует из названия, можно сгруппировать данные, а также, скрыть неактуальную информацию. Давайте разберемся, как это работает.
- Настраиваем параметры функции
- Группируем данные по строкам
- Группируем столбцы
- Создаем многоуровневую группировку
- Разгруппировываем данные
- Заключение
Настраиваем параметры функции
Чтобы в конечном счете получить желаемый результат, для начала следует выполнить настройки самой функции. Для этого выполняем следующие шаги:
- Переключившись во вкладку “Данные” щелкаем по кнопке “Структура” и в открывшемся перечне команд – по небольшому значку в виде стрелки, направленной по диагонали вниз.
На экране отобразится небольшое окошко с параметрами функции. Здесь мы можем настроить отображение итогов. Ставим галочки напротив нужных опций (в т.ч. автоматические стили) и жмем кнопку OK.Примечание: расположение итоговых данных в строках под данными многим кажется неудобным, поэтому данный параметр можно выключить.
Группируем данные по строкам
Для начала давайте рассмотрим, как можно сгруппировать строки:
- Вставляем новую строку над или под строками, которые хотим сгруппировать (зависит от того, какой вид расположения итогов по строкам мы выбрали). Как это сделать, читайте в нашей статье – “Как добавить новую строку в Excel“.
- В самой левой ячейке добавленной строки пишем название, которое хотим присвоить группе.
- Любым удобным способом, например, с помощью зажатой левой кнопки мыши производим выделение ячеек строк (кроме итоговой), которые требуется сгруппировать. Во вкладке “Данные” щелкаем по кнопке “Структура” и в открывшемся списке выбираем функцию “Группировать”. Щелкнуть нужно именно по значку команды, а не по ее названию.
Если же нажать на последнее (со стрелкой вниз), откроется еще одно подменю, в котором следует нажать на одноименную кнопку.
- В появившемся окошке отмечаем пункт “строки” (должен быть выбран по умолчанию) и подтверждаем действие нажатием OK.
Примечание: Если вместо ячеек выделить все строки целиком на вертикальной панели координат, а затем применить группировку, то промежуточного окна с выбором строки или столбца не будет, так как программа сразу понимает, что именно ей необходимо сделать.
Группируем столбцы
Чтобы сгруппировать столбцы, придерживаемся примерно такого же алгоритма действий, описанного выше:
- Вставляем столбец справа или слева от группируемых – зависит от выбранного параметра в настройках функции. Подробнее о том, как это сделать, читайте в нашей статье – “Как вставить столбец в таблицу Эксель“.
Примечание: как и в случае с группировкой строк, при выделении столбцов целиком на горизонтальной панели координат, группировка данных будет выполнена сразу, минуя промежуточное окно с выбором элементов.
Создаем многоуровневую группировку
Возможности программы позволяют выполнять как одноуровневые, так и многоуровневые группировки. Вот как это делается:
- В раскрытом состоянии главной группы, внутри которой планируется создать еще одну, выполняем действия, рассмотренные в разделах выше в зависимости от того, с чем мы работаем – со строками или столбцами.
Разгруппировываем данные
Когда ранее выполненная группировка столбцов или строк больше не нужна или требуется выполнить ее иначе, можно воспользоваться обратной функцией – “Разгруппировать”:
- Производим выделение сгруппированных элементов, после чего все в той же вкладке “Данные” в группе инструментов “Структура” выбираем команду “Разгруппировать”. Жмем именно по значку, а не по названию.
- В открывшемся окне ставим отметку напротив требуемого пункта (в нашем случае – “строки”) и нажимаем OK.
Примечание: в случае многоуровневой группировки или наличия нескольких групп данных, каждую из них необходимо расформировать отдельно. - Вот и все, что требовалось сделать.
Заключение
Группировка данных выполняется в несколько кликов и не требует особых навыков в работе с программой, однако, данный прием позволяет существенно сэкономить время, когда приходится иметь дело с большим объемом информации. Это делает функцию одной из самых полезных и незаменимых в Excel.
Многоуровневая группировка строк
Если у вас имеется большая и сложная таблица на небольшом мониторе (например, ноутбука), то в процессе работы с ней часто возникает желание скрыть часть ненужных в текущий момент данных (строк или столбцов). Основные способы для решения этой проблемы — скрытие строк и группировка. Вот о последнем способе и пойдет речь далее.
Предположим, что мы работаем вот с такой сложной многоуровневой таблицей с данными:
Обратите внимание на то, что в таблице ясно и четко прослеживаются вложенные друг в друга уровни данных. Такую структуру часто можно увидеть в:
- бюджетировании — статьи доходов/расходов группируются в блоки (cost centers) или по географическому признаку (страна-край-город)
- управлении проектами — этапы проектов разбиты обычно на более мелкие подзадачи и действия
- строительных сметах — похожим образом обычно расписываются расчеты расхода материалов и их стоимости при строительстве
- и т.д. — дальше придумайте сами.
Делать такую группировку вручную весьма мучительно, вследствие неудобности и ограниченности средств группировки самого Excel. Поэтому, столкнувшись как-то с подобной задачей в одном из своих проектов, я написал макрос для автоматической группировки подобных списков, которым и хочу тут поделиться. Чтобы применить его к вашему списку, проделайте следующие действия:
Нажмите сочетание клавиш ALT+F11, чтобы открыть редактор Visual Basic. В нем выберите в меню команду Insert — Module, чтобы вставить новый модуль и скопируйте туда текст макроса:
При необходимости, текст можно слегка подкорректировать под ваши особенности, а именно изменить:
- FIRST_ROW — номер первой строки списка, начиная с которой пойдет группировка. Если у вас шапка не из одной строки или над таблицей есть данные — меняйте.
- FIRST_COLUMN — номер первого столбца списка, с которого начинается анализ и группировка. Если слева от вашей таблицы есть еще колонки, то эту константу также нужно изменить.
- NUMBER_OF_LEVELS — количество уровней (столбцов) для анализа. В приведенном выше примере мы хотим проанализировать три первых столбца, поэтому значение этой константы =3
Важно! Макрос предполагает, что:
- Уровни заполняются по порядку, т.е., например, уровень 3 не может быть написан, если ему не предшествовал уровень 2.
- В первом столбце списка в последней строке должно быть слово Конец, которое необходимо, чтобы макрос понял, где заканчивается список и пора остановиться:
Чтобы запустить добавленный макрос для списка на текущем листе, нажмите сочетание клавиш ALT+F8, выберите в списке наш макрос Multilevel_Group и нажмите кнопку Выполнить (Run) .
Ссылки по теме
- Что такое макросы, как их создавать, куда копировать текст макроса на Visual Basic
- Скрытие/отображение ненужных строк и столбцов
Ну, это вопрос вкуса
Для примера — по первой иллюстрации в статье. Ввести в ячейку H2 формулу =ПОИСКПОЗ("*";A2:G2;0) и скопировать вниз до конца списка. Получите номер первой непустой ячейки с текстом в строке, т.е. уровень вложенности. Пойдет?
У меня такое сработало. Не претендую на оптимальность.
Sub ЗаполнениеУровняГруппировки()
‘ Для ускорения работы макроса обновление экрана отключается.
Application.ScreenUpdating = False
Dim rr, i, c As Long
c = ActiveCell.Column
i = CLng(InputBox("введите номер первой строки для обработки?", "введите номер первой строки для обработки?", ActiveCell.Row))
rr = ActiveCell.UsedRange.Rows.Count
While i Родитель Ссылка
Все правильно. Итоги вручную считать — это прошлый век. Вы им еще сводные таблицы покажите — сойдут с ума от радости
Добрый день. Подскажите пожалуйста как изменить макрос для группировки структуры в примере ниже.
| округ | Сервис 1 | Фио 6 | 1 |
| округ | Сервис 1 | Фио 7 | 43 |
| округ | Сервис 1 итого | 44 | |
| округ | Сервис 2 | Фио 1 | 13 |
| округ | Сервис 2 | Фио 2 | 18 |
| округ | Сервис 2 | Фио 3 | 9 |
| округ | Сервис 2 | Фио 4 | 3 |
| округ | Сервис 2 итого | Фио 5 | 3 |
| округ итого | 134 | ||
| округ 1 | Сервис 3 | Фио 1 | 13 |
| округ 1 | Сервис 3 | Фио 2 | 18 |
| округ 1 | Сервис 3 | Фио 3 | 9 |
| округ 1 | Сервис 3 | Фио 4 | 3 |
| округ 1 | Сервис 3 итого | Фио 5 | 3 |
| округ 1 итого | 46 | ||
| Общий итог | 180 |
Николай, добрый день!
Неужели в Экселе стоит ограничение по количеству уровней группировки =8? если нет, то где-то это настраивается?
У меня есть отчет, в котором 11 уровней группировки, но после 8 уровня Эксель не дает больше создать и все нижние перегруппировывает в более высокий
Светлана, насколько я знаю, 8 уровней — это предел в любой версии Excel и поменять это ограничение нельзя
День добрый, Николай!
Ваш макрос подходит просто идеально для моей задачи!
Признаюсь сразу: раньше этим не занималась. Теперь нужно Вчера освоила написание простых макросов. Хотела ваш просто скопировать в свой лист, убрав зеленый текст. Когда пробую применить его в работу пишет следующую ошибку: "Run-time error ‘1004’: Невозможно получить свойство Match класса WorksheetFunction" . Подскажите, в чем причина?
Здравствуйте. Очень понравилась статья. У меня вопрос, можно ли сделать автоматическую группировку с помощью макроса не разбивая по столбцам.
Столбец по которому хотел сделать выглядит вот так:
Нужно изменить настройки группировки во вкладке данные —> группировка. Или добавить это в начале кода:
With ActiveSheet.Outline
.AutomaticStyles = False
.SummaryRow = xlAbove
.SummaryColumn = xlLeft
End With
Selection.ApplyOutlineStyles
Artyom Avakyan
Макрос работает. Но можно ли его так изменить, что бы он не проставлял уровни, в столбце числами, а смотрел их по какому-то столбцу умной таблицы, у меня автоматом уровни определяются 1.1, 1,2 (формулой). Таблица-шаблон, и уровни меняются.
Совсем никто не может подсказать?
Добрый день, а можно ли сделать группировку по датам (суткам), таблицы такого вида:
а то выматывает постоянно туда-сюда елозить мышкой
По датам лучше всего создать сводную таблицу и там групировать хоть по дням, хоть по неделям и с любим шагом
Добрый день. Всех с наступающим Новым Годом. Пытаюсь разобраться с многоуровневой группировкой строк, макрос что опубликован выше вроде подходит для решения моей задачи, но почему-то не получается, где моя ошибка?
Пример:
| Контрагент. Канал збуту | Супервайзер | Торговий представник | Транспортный код |
| HoReCa | Верес Мария Валерьевна | Музира Людмила Григорьевна | 1405 |
| HoReCa | Верес Мария Валерьевна | Музира Людмила Григорьевна | 1405 |
| HoReCa | Верес Мария Валерьевна | Музира Людмила Григорьевна | 1405 |
| HoReCa | Верес Мария Валерьевна | Музира Людмила Григорьевна | 1405 |
| HoReCa | Верес Мария Валерьевна | Музира Людмила Григорьевна | 1405 |
| HoReCa | Верес Мария Валерьевна | Музира Людмила Григорьевна | 1405 |
| HoReCa | Верес Мария Валерьевна | Музира Людмила Григорьевна | 1405 |
| HoReCa | Верес Мария Валерьевна | Музира Людмила Григорьевна | 1405 |
| HoReCa | Верес Мария Валерьевна | Музира Людмила Григорьевна | 1405 |
| HoReCa | Верес Мария Валерьевна | Музира Людмила Григорьевна | 1405 |
| HoReCa | Верес Мария Валерьевна | Музира Людмила Григорьевна | 1405 |
| HoReCa | Верес Мария Валерьевна | Музира Людмила Григорьевна | 1405 |
| HoReCa | Верес Мария Валерьевна | Музира Людмила Григорьевна | 1405 |
| HoReCa | Верес Мария Валерьевна | Музира Людмила Григорьевна | 1405 |
| HoReCa | Верес Мария Валерьевна | Музира Людмила Григорьевна | 1405 |
| HoReCa | Верес Мария Валерьевна | Музира Людмила Григорьевна | 1405 |
| HoReCa | Верес Мария Валерьевна | Музира Людмила Григорьевна | 1405 |
| HoReCa | Верес Мария Валерьевна | Музира Людмила Григорьевна | 1405 |
| HoReCa | Верес Мария Валерьевна | Музира Людмила Григорьевна | 1405 |
| HoReCa | Верес Мария Валерьевна | Музира Людмила Григорьевна | 1405 |
| HoReCa | Верес Мария Валерьевна | Музира Людмила Григорьевна | 1405 |
| HoReCa | Верес Мария Валерьевна | Музира Людмила Григорьевна | 1405 |
| HoReCa | Верес Мария Валерьевна | Музира Людмила Григорьевна | 1405 |
| HoReCa | Верес Мария Валерьевна | Музира Людмила Григорьевна | 1405 |
| HoReCa | Краськович Маргарита Васильевна | Царенко Андрей Сергеевич | 280 |
| HoReCa | Краськович Маргарита Васильевна | Царенко Андрей Сергеевич | 280 |
| HoReCa | Краськович Маргарита Васильевна | Царенко Андрей Сергеевич | 280 |
| HoReCa | Краськович Маргарита Васильевна | Царенко Андрей Сергеевич | 280 |
| HoReCa | Краськович Маргарита Васильевна | Царенко Андрей Сергеевич | 280 |
| HoReCa | Краськович Маргарита Васильевна | Царенко Андрей Сергеевич | 280 |
| HoReCa | Краськович Маргарита Васильевна | Царенко Андрей Сергеевич | 280 |
| HoReCa | Краськович Маргарита Васильевна | Царенко Андрей Сергеевич | 280 |
| HoReCa | Краськович Маргарита Васильевна | Царенко Андрей Сергеевич | 280 |
| HoReCa | Краськович Маргарита Васильевна | Царенко Андрей Сергеевич | 280 |
| HoReCa | Краськович Маргарита Васильевна | Царенко Андрей Сергеевич | 280 |
| HoReCa | Краськович Маргарита Васильевна | Царенко Андрей Сергеевич | 280 |
| HoReCa | Краськович Маргарита Васильевна | Царенко Андрей Сергеевич | 280 |
| HoReCa | Краськович Маргарита Васильевна | Царенко Андрей Сергеевич | 280 |
| HoReCa | Краськович Маргарита Васильевна | Царенко Андрей Сергеевич | 280 |
| HoReCa | Краськович Маргарита Васильевна | Царенко Андрей Сергеевич | 280 |
| HoReCa | Краськович Маргарита Васильевна | Царенко Андрей Сергеевич | 280 |
| HoReCa | Краськович Маргарита Васильевна | Царенко Андрей Сергеевич | 280 |
| HoReCa | Краськович Маргарита Васильевна | Царенко Андрей Сергеевич | 280 |
| HoReCa | Краськович Маргарита Васильевна | Царенко Андрей Сергеевич | 280 |
| HoReCa | Краськович Маргарита Васильевна | Царенко Андрей Сергеевич | 280 |
| HoReCa | Краськович Маргарита Васильевна | Царенко Андрей Сергеевич | 280 |
Строк около 25тыс., транспортный код (это разные адреса около 700 штук) не повторяются, следующая колонка, ее не видно это дата месяца.
Const FIRST_ROW = 2 ‘первая строка списка (оставляю без изменений)
Const FIRST_COLUMN = 1 ‘первый столбец списка (меняю на 4 — колонка транспортный код)
Const NUMBER_OF_LEVELS = 3 ‘количество уровней (меняю на 1 )
Если верно понимаю, то должна произойти группировка только по 4 колонке. НО НИЧЕГО НЕ ПРОИСХОДИТ. Может кто подскажет. Спасибо заранее.
© Николай Павлов, Planetaexcel, 2006-2021
info@planetaexcel.ru
Использование любых материалов сайта
допускается строго с указанием прямой ссылки на источник, упоминанием
названия сайта, имени автора и неизменности исходного текста
и иллюстраций.
ИП Павлов Николай Владимирович
ИНН 633015842586
ОГРН 310633031600071