Как сделать многоуровневую таблицу в excel

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

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

  • Настраиваем параметры функции
  • Группируем данные по строкам
  • Группируем столбцы
  • Создаем многоуровневую группировку
  • Разгруппировываем данные
  • Заключение

Настраиваем параметры функции

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

Переход к параметрам функции Группировка в Эксель

    Переключившись во вкладку “Данные” щелкаем по кнопке “Структура” и в открывшемся перечне команд – по небольшому значку в виде стрелки, направленной по диагонали вниз.

Настройка параметров функции Группировать в Excel

На экране отобразится небольшое окошко с параметрами функции. Здесь мы можем настроить отображение итогов. Ставим галочки напротив нужных опций (в т.ч. автоматические стили) и жмем кнопку OK.Примечание: расположение итоговых данных в строках под данными многим кажется неудобным, поэтому данный параметр можно выключить.

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

Для начала давайте рассмотрим, как можно сгруппировать строки:

  1. Вставляем новую строку над или под строками, которые хотим сгруппировать (зависит от того, какой вид расположения итогов по строкам мы выбрали). Как это сделать, читайте в нашей статье – “Как добавить новую строку в Excel“.
  2. В самой левой ячейке добавленной строки пишем название, которое хотим присвоить группе.Имя итоговой ячейки для группировки данных в Эксель
  3. Любым удобным способом, например, с помощью зажатой левой кнопки мыши производим выделение ячеек строк (кроме итоговой), которые требуется сгруппировать. Во вкладке “Данные” щелкаем по кнопке “Структура” и в открывшемся списке выбираем функцию “Группировать”. Щелкнуть нужно именно по значку команды, а не по ее названию. Группировка выделенных строк в ЭксельЕсли же нажать на последнее (со стрелкой вниз), откроется еще одно подменю, в котором следует нажать на одноименную кнопку.Группировка выделенных строк в Excel
  4. В появившемся окошке отмечаем пункт “строки” (должен быть выбран по умолчанию) и подтверждаем действие нажатием OK.Группировка строк в ЭксельПримечание: Если вместо ячеек выделить все строки целиком на вертикальной панели координат, а затем применить группировку, то промежуточного окна с выбором строки или столбца не будет, так как программа сразу понимает, что именно ей необходимо сделать.Группировка выделенных строк в Эксель
  • Группа создана, о чем свидетельствуют появившаяся на панели координат полоска со знаком “минус”. Это означает, что сгруппированные данные раскрыты. Чтобы их скрыть, нажимам по минусу или кнопке с цифрой “1” (самый верхний уровень группировки).Сгруппированные строки в Эксель
  • Теперь строки скрыты. Чтобы их обратно раскрыть, нажимаем по значку “плюса”, который появился вместо “минуса” (или по кнопке “2”).Скрытые сгруппированные строки в Excel
  • Группируем столбцы

    Чтобы сгруппировать столбцы, придерживаемся примерно такого же алгоритма действий, описанного выше:

      Вставляем столбец справа или слева от группируемых – зависит от выбранного параметра в настройках функции. Подробнее о том, как это сделать, читайте в нашей статье – “Как вставить столбец в таблицу Эксель“.

  • Пишем название в самой верхней ячейке нового столбца.Имя итоговой ячейки при группировке столбцов в Эксель
  • Выделяем ячейки группируемых столбцов (за исключением добавленного) и применяем функцию группировки.Группировка выделенных столбцов в Эксель
  • Ставим отметку напротив варианта “столбцы” и кликам OK.Группировка столбцов в ЭксельПримечание: как и в случае с группировкой строк, при выделении столбцов целиком на горизонтальной панели координат, группировка данных будет выполнена сразу, минуя промежуточное окно с выбором элементов.Группировка выделенных столбцов в Excel
  • Задача успешно выполнена. Сгруппированные столбцы в Эксель
  • Создаем многоуровневую группировку

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

    Создание многоуровневой группировки в Эксель

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

    Многоуровневая группировка в Excel

  • Таким образом, мы получили многоуровневую группировку.
  • Разгруппировываем данные

    Когда ранее выполненная группировка столбцов или строк больше не нужна или требуется выполнить ее иначе, можно воспользоваться обратной функцией – “Разгруппировать”:

    1. Производим выделение сгруппированных элементов, после чего все в той же вкладке “Данные” в группе инструментов “Структура” выбираем команду “Разгруппировать”. Жмем именно по значку, а не по названию.Разгруппировка в Эксель
    2. В открывшемся окне ставим отметку напротив требуемого пункта (в нашем случае – “строки”) и нажимаем OK.Разгруппировка в Excel Примечание: в случае многоуровневой группировки или наличия нескольких групп данных, каждую из них необходимо расформировать отдельно.
    3. Вот и все, что требовалось сделать.Разгруппированная таблица Эксель

    Заключение

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

    Многоуровневая группировка строк

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

    Предположим, что мы работаем вот с такой сложной многоуровневой таблицей с данными:

    group1.png

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

    • бюджетировании — статьи доходов/расходов группируются в блоки (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

    Ссылка на основную публикацию