как в экселе сделать иерархию строк со скрытием

Структурирование (группировка) данных на листе

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

1. Чтобы отобразить строки для уровня, щелкните соответствующий символы структуры.

2. Уровень 1 содержит сумму продаж по всем строкам с подробными данными.

3. Уровень 2 содержит сумму продаж по каждому месяцу в каждом регионе.

4. Уровень 3 содержит подробные строки— в этом случае строки от 17 до 20.

5. Чтобы развернуть или свернуть данные в контуре, щелкните и символы структуры или нажмите клавиши ALT+SHIFT+= для развертывания и ALT+SHIFT+- для сворачивания.

Создание многоуровневой структуры из строк

1. Убедитесь, что каждый столбец данных, который требуется структурировать, имеет метку в первой строке (например, Регион), содержит аналогичные факты в каждом столбце, а диапазон, который требуется обвести, не содержит пустых строк или столбцов.
2. При необходимости сгруппированные строки сведений могут иметь соответствующую сводную строку — промежуточный итог. Чтобы создать их, выполните одно из следующих действий.
   • Вставка итоговых строк при помощи команды Промежуточные итоги Используйте команду Итоги для вставки функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ непосредственно под или над каждой группой строк с подробными данными и для автоматического создания многоуровневой структуры. Подробнее об использовании функции «Итоги» читайте в статье Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
   • Вставка собственных итоговых строк Вставьте собственные итоговые строки с формулами непосредственно под или над каждой группой строк с подробными данными. Например, под (или над) строками данных о продажах за март и апрель используйте функцию СУММ, чтобы подсчитать промежуточные итоговые данные по продажам за эти месяцы. Этот пример показан в таблице далее в этой статье.
3. По умолчанию Excel ищет сводные строки под сведениями, которые они обобщают, но их можно создать над строками сведений. Если вы создали строки сводки под подробными сведениями, перейдите к следующему шагу (шаг 4). Если вы создали строки сводки над строками сведений, на вкладке Данные в группе Структура щелкните средство запуска диалогового окна. Откроется диалоговое окно Параметры. Затем в диалоговом окне Параметры снимите флажок Сводка строк ниже и нажмите кнопку ОК.
4. Структурируйте данные. Выполните одно из перечисленных ниже действий. Автоматическое структурирование данных
   1. Выделите ячейку в диапазоне ячеек, которые нужно выделить.
   2. На вкладке Данные в группе Структура щелкните стрелку рядом с пунктом Группировать, а затем выберите пункт Создать структуру.

   Структурирование данных вручную

   Важно: Группируя уровни структуры вручную, отобразите на экране все данные во избежание ошибок группировки строк.

   1. Чтобы очертить внешнюю группу (уровень 1), выделите все строки, которые будет содержать внешняя группа (т. е. строки сведений и, если вы добавили их, их суммарные строки).
      

      1. Первая строка содержит метки и не выбрана. 2. Так как это внешняя группа, выделите все строки с промежуточными и подробными данными. 3. Не выбирайте общий итог.

   2. На вкладке Данные в группе Структура выберите Группировать. Затем в диалоговом окне Группа щелкните Строки и нажмите кнопку ОК.

   Совет: Если выбрать целые строки, а не только ячейки, Excel автоматически группирует по строкам— диалоговое окно Группа даже не открывается.

   

   Рядом с группой на экране появятся знаки структуры.
   Или структурируйте внутреннюю вложенную группу — строки с данными для определенного раздела данных.

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

   1. Можно создать несколько групп на каждом внутреннем уровне. Здесь два раздела уже группируются на уровне 2. 2. Этот раздел выбран и готов к группировке. 3. Не выбирайте строку сводки для группирования данных.

   На вкладке Данные в группе Структура выберите Группировать. Затем в диалоговом окне Группа щелкните Строки и нажмите кнопку ОК. Рядом с группой на экране появятся знаки структуры.

   Совет: Если выбрать целые строки, а не только ячейки, Excel автоматически группирует по строкам— диалоговое окно Группа даже не открывается.

   Важно: Если во время разгруппировки структуры подробные данные скрыты, строки с подробными данными могут также не отображаться. Для отображения данных перетащите указатель через номера видимых строк рядом со скрытыми строками. Затем на вкладке Главная в группе Ячейки нажмите кнопку Формат, наведите указатель мыши на пункт Скрыть & Отобразить, а затем выберите команду Отобразить строки.

   Создание многоуровневой структуры из столбцов

   1. Убедитесь в том, что в первом столбце каждой строки данных, для которых требуется создать структуру, имеется заголовок, что каждая строка содержит данные одного типа и что в диапазоне данных отсутствуют пустые строки или столбцы.
   2. Вставьте собственные итоговые столбцы с формулами непосредственно справа или слева от каждой группы строк с подробными данными. В таблице, приведенной в шаге 4, показан пример.

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

5. Если столбец сводки находится слева от столбцов сведений, на вкладке Данные в группе Структура щелкните средство запуска диалогового окна. Откроется диалоговое окно Параметры. Затем в диалоговом окне Параметры очистите столбцы Сводка справа от подробных проверка и нажмите кнопку ОК.
6. Чтобы структурировать данные, выполните одно из перечисленных ниже действий. Автоматическое структурирование данных
   1. Выделите ячейку в диапазоне.
   2. На вкладке Данные в группе Структура щелкните стрелку под пунктом Группировать, а затем выберите Создать структуру.

   Структурирование данных вручную

   Важно: Группируя уровни структуры вручную, отобразите на экране все данные во избежание ошибок при группировке столбцов.

   1. Чтобы очертить внешнюю группу (уровень 1), выберите все подчиненные сводные столбцы, а также связанные с ними подробные данные.
      

      1. Столбец А содержит метки. 2. Выберите все столбцы сведений и промежуточных итогов. Обратите внимание, что если вы не выберете целые столбцы, при нажатии кнопки Группа (на вкладке Данные в группе Структура) откроется диалоговое окно Группа с запросом на выбор строк или столбцов. 3. Не выбирайте столбец с общим итогом.

   2. На вкладке Данные в группе Структура выберите Группировать. Над группой появится символ структуры.
   3. Чтобы создать структуру внутренней вложенной группы подробных столбцов (уровень 2 или выше), выберите столбцы сведений, расположенные рядом со столбцом, содержащим сводный столбец.
      

      1. Можно создать несколько групп на каждом внутреннем уровне. Здесь два раздела уже группируются на уровне 2. 2. Эти столбцы выбраны и готовы к группировке. Обратите внимание, что если вы не выберете целые столбцы, при нажатии кнопки Группа (на вкладке Данные в группе Структура) откроется диалоговое окно Группа с запросом на выбор строк или столбцов. 3. Не выбирайте столбец сводки для группирования данных.

   4. На вкладке Данные в группе Структура выберите Группировать. Рядом с группой на экране появятся знаки структуры.
7. Продолжайте выделение и группировку внутренних столбцов до тех пор, пока не будут созданы все необходимые уровни структуры.
8. Для разгруппировки столбцов выделите необходимые столбцы, а затем на вкладке Данные в группе Структура нажмите кнопку Разгруппировать.

Вы также можете разгруппировать разделы структуры, не удаляя весь уровень. Удерживая нажатой клавишу SHIFT, щелкните или группы, а затем на вкладке Данные в группе Структура щелкните Разгруппировать.

Если во время разгруппировки структуры подробные данные скрыты, столбцы с подробными данными могут также не отображаться. Для отображения данных перетащите указатель через номера видимых столбцов рядом со скрытыми столбцами. На вкладке Главная в группе Ячейки нажмите кнопку Формат, выберите пункт Скрыть или отобразить, а затем нажмите кнопку Отображение столбцов

Отображение и скрытие структурированных данных

1. Если символы структуры не отображаются , и , перейдите в раздел Параметры >файлов>Дополнительно, а затем в разделе Параметры отображения для этого листа выберите поле Показывать символы структуры, если структура применяется проверка, а затем нажмите кнопку ОК.
2. Выполните одно или несколько из указанных ниже действий.
   • Отображение или скрытие подробных данных для группы Чтобы отобразить подробные данные в группе, нажмите кнопку для группы или нажмите клавиши ALT+SHIFT+=.
   • Чтобы скрыть подробные данные для группы, нажмите кнопку для группы или нажмите клавиши ALT+SHIFT+-.
   • Развертывание или свертывание структуры до определенного уровня В символы структуры щелкните нужный номер уровня. Подробные данные более низких уровней будут скрыты. Например, если в структуре четыре уровня, можно скрыть четвертый уровень, нажав кнопку , при этом оставшиеся три уровня будут отображаться.
   • Отображение или скрытие всех структурированных данных Чтобы отобразить все подробные данные, щелкните самый нижний уровень в символов структуры. Например, если в структуре три уровня, нажмите .
   • Чтобы скрыть все подробные данные, нажмите .

Настройка многоуровневой структуры со стилями

Для выделенных строк Microsoft Excel использует такие стили, как RowLevel_1 и RowLevel_2 . Для контурных столбцов Excel использует такие стили, как ColLevel_1 и ColLevel_2. В этих стилях используются полужирные, курсивные и другие текстовые форматы, чтобы различать строки или столбцы сводки в данных. Изменив способ определения каждого из этих стилей, можно применить различные форматы текста и ячеек для настройки внешнего вида структуры. Стиль можно применить к структуре либо при создании структуры, либо после ее создания.

Выполните одно или несколько из указанных ниже действий.

Автоматическое применение стиля к новым строкам или столбцам сводки

1. На вкладке Данные в группе Структура щелкните средство запуска диалогового окна. Откроется диалоговое окно Параметры.
2. Установите флажок Автоматические стили.

Назначение стиля существующей итоговой строке или столбцу

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

Для форматирования структурированных данных также можно применять автоформаты.

Копирование структурированных данных

1. Если символы структуры не отображаются , и , перейдите в раздел Параметры >файлов>Дополнительно, а затем в разделе Параметры отображения для этого листа выберите поле Показать символы структуры, если структура применяется проверка.
2. Используйте символы структуры , и , чтобы скрыть подробные данные, которые не нужно копировать. Подробную информацию см. в разделе Отображение и скрытие структурированных данных.
3. Выделите диапазон итоговых строк.
4. На вкладке Главная в группе Редактирование нажмите кнопку Найти и выделить, а затем выберите команду Перейти.
5. Нажмите кнопку Выделить группу ячеек.
6. Выберите пункт только видимые ячейки.
7. Нажмите кнопку ОК, а затем скопируйте данные.

Скрытие или удаление многоуровневой структуры

Примечание: При скрытии или удалении структуры удаления данных не происходит.

Скрытие многоуровневой структуры

• Перейдите в раздел Параметры файла> >Дополнительно, а затем в разделе Параметры отображения для этого листа снимите флажок Показывать символы структуры, если структура применяется проверка.

Удаление многоуровневой структуры

1. Выберите лист.
2. На вкладке Данные в группе Структура щелкните Разгруппировать, а затем выберите пункт Удалить структуру.

Важно: Если во время удаления многоуровневой структуры подробные данные скрыты, то строки или столбцы с подробными данными могут также не отображаться. Для просмотра данных выделите номера или буквы видимых соседних строк или столбцов соответственно. На вкладке Главная в группе Ячейки нажмите кнопку Формат, выберите пункт Скрыть или отобразить, а затем нажмите кнопку Отобразить строки или кнопку Отображение столбцов.

Создание итогового отчета с диаграммой

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

1. Создание сводного отчета
   1. Структурируйте данные. Подробнее читайте в разделе Создание многоуровневой структуры из строк или Создание многоуровневой структуры из столбцов.
   2. Скройте сведения, щелкнув символы структуры , и , чтобы отобразить только итоги, как показано в следующем примере структуры строки:
   3. Подробную информацию см. в разделе Отображение и скрытие структурированных данных.
2. Диаграмма сводного отчета
   1. Выделите итоговые данные, которые требуется представить в виде диаграммы. Например, для диаграммы только итоги Бьюкенен и Даволио, но не общие итоги, выделите ячейки от A1 до C19, как показано в приведенном выше примере.
   2. Щелкните Вставить >диаграммы >Рекомендуемые диаграммы, а затем откройте вкладку Все диаграммы и выберите тип диаграммы. Например, если выбран параметр Кластеризованный столбец, диаграмма будет выглядеть следующим образом: При отображении или скрытии данных в структурированном списке эти изменения отображаются и на диаграмме.

Можно группировать (или структурировать) строки и столбцы в Excel в Интернете.

Примечание: Хотя в данные можно добавлять сводные строки или столбцы (с помощью таких функций, как СУММ или ПРОМЕЖУТОЧНЫЕ ИТОГИ), нельзя применять стили или задавать позицию для строк и столбцов сводки в Excel в Интернете.

Создание структуры строк или столбцов

Структура строк в Excel Online

1. Первый столбец содержит метки.
2. Строки сведений и строки сводки группируются в структуре.
3. Общий итог не сгруппирован в контуре.

Структура столбцов в Excel Online

1. Первая строка содержит метки.
2. Столбцы сведений и столбцы сводки группируются в структуре.
3. Общий итог не сгруппирован в контуре.

1. Убедитесь, что каждый столбец (или строка) данных, которые требуется создать, имеет метку в первой строке (или столбце), содержит аналогичные факты в каждом столбце (или строке), а диапазон не содержит пустых строк или столбцов.
2. Выберите данные (включая строки или столбцы сводки).
3. На вкладке Данные в группе Структура щелкните Группировать >Строки группы или Столбцы группы.
4. При необходимости, если вы хотите создать структуру внутренней вложенной группы, выберите строки или столбцы в пределах указанного диапазона данных и повторите шаг 3.
5. Продолжайте выбирать и группировать внутренние строки или столбцы, пока не создадите все нужные уровни в структуре.

Разгруппирование строк или столбцов

• Чтобы разгруппировать, выберите строки или столбцы, а затем на вкладке Данные в группе Структура нажмите кнопку Разгруппировать и выберите Разгруппировать строки или Разгруппировать столбцы.

Отображение и скрытие структурированных данных

Выполните одно или несколько из указанных ниже действий.

Отображение или скрытие подробных данных для группы

• Чтобы отобразить подробные данные в группе, щелкните группы или нажмите клавиши ALT+SHIFT+=.
• Чтобы скрыть подробные данные для группы, щелкните группы или нажмите клавиши ALT+SHIFT+-.

Развертывание или свертывание структуры до определенного уровня

• В символы структуры щелкните нужный номер уровня. Подробные данные более низких уровней будут скрыты.
• Например, если в структуре четыре уровня, можно скрыть четвертый уровень, нажав кнопку , при этом оставшиеся три уровня будут отображаться.

Отображение или скрытие всех структурированных данных

• Чтобы отобразить все подробные данные, щелкните самый нижний уровень в символов структуры. Например, если в структуре три уровня, нажмите .
• Чтобы скрыть все подробные данные, нажмите .

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

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

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

• бюджетировании — статьи доходов/расходов группируются в блоки (cost centers) или по географическому признаку (страна-край-город)
• управлении проектами — этапы проектов разбиты обычно на более мелкие подзадачи и действия
• строительных сметах — похожим образом обычно расписываются расчеты расхода материалов и их стоимости при строительстве
• и т.д. — дальше придумайте сами.

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

Нажмите сочетание клавиш ALT+F11, чтобы открыть редактор Visual Basic. В нем выберите в меню команду Insert — Module, чтобы вставить новый модуль и скопируйте туда текст макроса:

Sub Multilevel_Group() Dim level As Single, i As Single Dim start As Single, LastRow As Single Const FIRST_ROW = 2 'первая строка списка Const FIRST_COLUMN = 1 'первый столбец списка Const NUMBER_OF_LEVELS = 3 'количество уровней Set ws = ActiveSheet ws.UsedRange.ClearOutline 'убираем все группировки на листе LastRow = WorksheetFunction.Match("Конец", ws.Columns(FIRST_COLUMN), 0) 'определяем номер последней строки 'проходим во вложенном цикле по уровням и группируем For level = 1 To NUMBER_OF_LEVELS start = 0 For i = FIRST_ROW To LastRow 'если нашли начало группы - запоминаем номер строки If ws.Cells(i, level+FIRST_COLUMN-1) <> "" And _ WorksheetFunction.CountA(ws.Cells(i + 1, FIRST_COLUMN).Resize(1, level)) = 0 Then start = i 'если нашли конец группы - группируем If WorksheetFunction.CountA(ws.Cells(i + 1, FIRST_COLUMN).Resize(1, level)) > 0 And start > 0 Then ws.Rows(start + 1 & ":" & i).Group start = 0 End If Next i Next level End Sub

При необходимости, текст можно слегка подкорректировать под ваши особенности, а именно изменить:

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

Важно! Макрос предполагает, что:

• Уровни заполняются по порядку, т.е., например, уровень 3 не может быть написан, если ему не предшествовал уровень 2.
• В первом столбце списка в последней строке должно быть слово Конец, которое необходимо, чтобы макрос понял, где заканчивается список и пора остановиться:

Чтобы запустить добавленный макрос для списка на текущем листе, нажмите сочетание клавиш ALT+F8, выберите в списке наш макрос Multilevel_Group и нажмите кнопку Выполнить (Run) .

Ссылки по теме

• Что такое макросы, как их создавать, куда копировать текст макроса на Visual Basic
• Скрытие/отображение ненужных строк и столбцов

07.10.2012 10:52:04

FIRST_ROW, FIRST_COLUMN и NUMBER_OF_LEVELS, на мой взгляд, не стоит делать константами, а внести в параметры процедуры, чтобы пользователь при её вызове сам определял количество уровней группировки и размер шапки таблицы. Тогда процедура будет универсальной.

07.10.2012 10:55:04

Ну, это вопрос вкуса

07.10.2012 10:52:37
Написала аналогичный макрос года 2 назад. Забавно, что даже переменные у меня названы также:)
07.10.2012 10:54:07

Была проблема: уровни начинались и с 5го и с 7го. А заканчивались и 7 и 15м. Никаких ключевых слов и правил. Спасся вот такой рекурсией:

Function GroopBlock(Col_ As Integer, Str_ As Integer) As Integer Dim i As Integer Dim j As Integer i = 0 While ActiveSheet.Cells(Str_ + i, Col_).Value <> "" If ActiveSheet.Cells(Str_ + i + 1, Col_).Value = "" Then For j = Col_ + 1 To 16 If ActiveSheet.Cells(Str_ + i + 1, j).Value <> "" Then i = i + GroopBlock(j, Str_ + i + 1) Exit For End If Next j End If i = i + 1 Wend Range(Cells(Str_, 1), Cells(Str_ + i - 1, 1)).EntireRow.Group GroopBlock = i End Function

Задаём только начало для блока группировки (столбец и строку) и ограничение в 16 уровней задал жестко.
Повешал на горячие клавиши.
Пользуйте, кому полезно будет..

22.04.2013 16:52:08

Господа, кто посоветует, как проанализировать готовый многоуровневый список? Т. е., в какой-то ячейке текущей строки, поставить значение уровня, к которому относится ячейка в текущей строке?

23.04.2013 18:32:55

Для примера — по первой иллюстрации в статье. Ввести в ячейку H2 формулу =ПОИСКПОЗ(«*»;A2:G2;0) и скопировать вниз до конца списка. Получите номер первой непустой ячейки с текстом в строке, т.е. уровень вложенности. Пойдет?

24.04.2013 10:37:25

Николай, спасибо за идею, однако для моего случая она не работает. В моем списке все сгруппированные по разным уровням данные находятся в первой колонке. Т.е. группировка есть, а сдвижки по колонкам, в зависимости от уровня — нет!. Вот такая бедулька .

09.02.2015 13:44:30

У меня такое сработало. Не претендую на оптимальность.

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 < rr
Cells(i, c).Value = ActiveSheet.Rows(i).OutlineLevel
i = i + 1
Wend
Application.ScreenUpdating = True
End Sub

07.05.2013 11:51:47

А как можно структурировать такого вида конструкции:
Группировка в таком случае вообще возможна? Не макросом а вообще в принципе?
06.06.2013 |ААА|111| 5600р
07.06.2013 |ААА|222| 600р
07.06.2013 |БББ|111| 120р
07.06.2013 |БББ|555| 17800р
08.06.2013 |ВВВ|777| 1200р
09.06.2013 |ВВВ|777| 100р

07.05.2013 23:08:40

Если вы имеете ввиду группировку по ААА и БББ и ВВВ, то вам, как минимум, нужно иметь пустые строки между 2 и 3, 4 и 5 строками — иначе три группировки сольются в одну.
И сгруппировать такое вполне можно стандартными средствами — с помощью Промежуточных итогов, например (Данные — Промежуточные итоги).

15.05.2013 14:44:08

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

15.05.2013 14:45:20

Все правильно. Итоги вручную считать — это прошлый век. Вы им еще сводные таблицы покажите — сойдут с ума от радости

25.07.2013 21:50:11

Здравствуйте, у меня вопрос не по макросу, но по группировке.
Есть ли способ обьединить две многоуровневые таблицы со сгруппированными данными в одну, по сути дополнить одну таблицу данными (целыми строками) из второй?

10.04.2015 13:10:01
Добрый день. Подскажите пожалуйста как изменить макрос для группировки структуры в примере ниже.

округ	Сервис 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

09.09.2015 16:48:59

Николай, добрый день!

Неужели в Экселе стоит ограничение по количеству уровней группировки =8? если нет, то где-то это настраивается?
У меня есть отчет, в котором 11 уровней группировки, но после 8 уровня Эксель не дает больше создать и все нижние перегруппировывает в более высокий

09.09.2015 17:24:01

Светлана, насколько я знаю, 8 уровней — это предел в любой версии Excel и поменять это ограничение нельзя

24.09.2015 16:39:09

Николай, а можно ли сделать группировку по цвету? У Вас в примере каждый уровень выделен определенным цветом. Тогда можно было бы группировать без смещения.

30.10.2015 16:02:43

День добрый, Николай!
Ваш макрос подходит просто идеально для моей задачи!
Признаюсь сразу: раньше этим не занималась. Теперь нужно Вчера освоила написание простых макросов. Хотела ваш просто скопировать в свой лист, убрав зеленый текст. Когда пробую применить его в работу пишет следующую ошибку: «Run-time error ‘1004’: Невозможно получить свойство Match класса WorksheetFunction» . Подскажите, в чем причина?

06.12.2018 16:45:47

Та же проблема была вместо LastRow = WorksheetFunction.Match(«Конец», ws.Columns(FIRST_COLUMN), 0) задайте LastRow = (точное количество строк) и проблема исчезнет, у меня просто был список с более 1 млн. строк, так сказать максимум возможности по строчкам excel использованы)

09.12.2015 04:53:28

Доброго времени суток, Николай!
На все вопросы есть ответы. На форуме ли, в рубрике «ПРИЕМЫ» ли. Кому как, но мне кожется, самые доскональные ответы в «Видео». Когда сам видишь как, что, и за чем все происходит.
Не задумывались сделать и на эту тему обучающий видео урок?
Спасибо

10.12.2015 11:10:27

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

19.12.2015 16:36:27

Здравствуйте!
Макрос очень помог, только почему-то не группирует последние строки группу!
Подскажите в чем причина?

06.02.2017 09:58:18

Нужно изменить настройки группировки во вкладке данные —> группировка. Или добавить это в начале кода:

With ActiveSheet.Outline
.AutomaticStyles = False
.SummaryRow = xlAbove
.SummaryColumn = xlLeft
End With
Selection.ApplyOutlineStyles

31.05.2016 01:06:05

Как сделать автоматическую группировку с помощью макроса с номерами
1
1.1
1.2
1.3
2
2.1
2.2
2.2.1
2.2.2
2.2.2.1
2.2.2.2
2.2.3
2.3
3
И тд. .

04.07.2016 12:53:15

Sub GroupByDot() On Error Resume Next firstrow = Application.InputBox("Первая строка списка", Type:=1) If firstrow = False Then Exit Sub lvlcol = Application.InputBox("Номер столбца, куда попадает уровень", Type:=1) If lvlcol = False Then Exit Sub ActiveSheet.UsedRange.ClearOutline 'макрос который определяет уровень k = firstrow Do Until Cells(k, 1) = "" strt = Cells(k, 1) l = Len(strt) l1 = 1 h = 1 Do Until l1 = l + 1 strt1 = Mid(strt, l1, 1) If strt1 = "." Then h = h + 1 End If l1 = l1 + 1 Loop Cells(k, lvlcol) = h k = k + 1 Loop'макрос который делает уровни e = firstrow Do Until Cells(e, 1) = "" If Cells(e, lvlcol).Value >= 0 Then For i = 1 To Cells(e, lvlcol) Rows(e).Group 'Cells(e, 1).InsertIndent 1 Next i If Cells(e, lvlcol).Value < Cells(e + 1, lvlcol).Value And Cells(e + 1, lvlcol) <>"" Then Range(Cells(e, 1), Cells(e, 7)).Font.Bold = True End If e = e + 1 Loop ActiveSheet.Outline.SummaryRow = xlAbove ActiveSheet.Outline.SummaryColumn = xlRight End Sub

15.09.2016 16:06:44

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

24.12.2016 03:00:13

Artyom Avakyan
Макрос работает. Но можно ли его так изменить, что бы он не проставлял уровни, в столбце числами, а смотрел их по какому-то столбцу умной таблицы, у меня автоматом уровни определяются 1.1, 1,2 (формулой). Таблица-шаблон, и уровни меняются.

24.01.2017 23:26:34

Совсем никто не может подсказать?

16.01.2021 03:42:06

Закомментируйте (жалко портить) строки кода с ‘k = firstrow по ‘k = k + 1 и номер столбца куда попадает уровень укажите свой.

10.02.2017 14:19:24

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

05.04.17 7:00 0,00187756 05.04.17 8:00 0,00190244 05.04.17 9:00 0,00192732 05.04.17 10:00 0,00195220 05.04.17 11:00 0,00197708 05.04.17 12:00 0,00000196 05.04.17 13:00 0,00002984 05.04.17 14:00 0,00005772 05.04.17 15:00 0,00008560 05.04.17 16:00 0,00011348 05.04.17 17:00 0,00014136 05.04.17 18:00 0,00016924 05.04.17 19:00 0,00019712 05.04.17 20:00 0,00022500 05.04.17 21:00 0,00025288 05.04.17 22:00 0,00028076 05.04.17 23:00 0,00030864 06.04.17 0:00 0,00033652 06.04.17 1:00 0,00036440 06.04.17 2:00 0,00039228 06.04.17 3:00 0,00042016 06.04.17 4:00 0,00044804 06.04.17 5:00 0,00047592 06.04.17 6:00 0,00050380 06.04.17 7:00 0,00053168

а то выматывает постоянно туда-сюда елозить мышкой

26.12.2018 22:48:26

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

10.09.2018 02:03:07

Здравствуйте! Давно пользуюсь, данным кодом, всё отлично работает спасибо! Так как мне пришлось изменить организацию своей таблице, код перестал работать, причина в том, что те ячейки, ранее были пустыми, в новой таблице имеют формулу (типо =»»;), код, как я понял выявлял заполненные и пустые ячейки на этом основании выводил группировку, соответственно в моём новом случае пустые ячей, но с формулой.

10.10.2018 09:45:34

ДОБРЫЙ ДЕНЬ!
Возможно сделать оглавление по листу!
в Worde это легко сделать а здесь не разобрался!
Что бы потом конвертировать в PDF с навигацией!
Навигация нужна по Группе.

31.12.2018 14:18:57

Добрый день. Всех с наступающим Новым Годом. Пытаюсь разобраться с многоуровневой группировкой строк, макрос что опубликован выше вроде подходит для решения моей задачи, но почему-то не получается, где моя ошибка?
Пример:

Контрагент. Канал збуту	Супервайзер	Торговий представник	Транспортный код
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 колонке. НО НИЧЕГО НЕ ПРОИСХОДИТ. Может кто подскажет. Спасибо заранее.

21.05.2020 16:18:35

Как изменить макрос, если нет сдвижки по уровням вправо, но есть первая колонка с указанием уровня строки.

16.11.2022 17:11:37
спасибо, разобрался, классный макрос, тема актуальна в 2022

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

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

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

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

Ваш браузер не поддерживается

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

Установить новый браузер

Google Chrome

Скачать
Яндекс Браузер

Скачать
Opera

Скачать
Firefox

Скачать
Microsoft Edge

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

8 (800) 500-78-57 support@studwork.ru

Работаем по будням с 8.00 до 20.00 по МСК

Как скрывать и группировать столбцы в Excel

Из этого руководства Вы узнаете и сможете научиться скрывать столбцы в Excel 2010-2013. Вы увидите, как работает стандартный функционал Excel для скрытия столбцов, а также научитесь группировать и разгруппировывать столбцы при помощи инструмента «Группировка».

Уметь скрывать столбцы в Excel очень полезно. Может быть множество причин не отображать на экране какую-то часть таблицы (листа):

• Необходимо сравнить два или более столбцов, но их разделяют несколько других столбцов. К примеру, Вы хотели бы сравнить столбцы A и Y, а для этого удобнее расположить их рядом. Кстати, в дополнение к этой теме, Вам может быть интересна статья Как закрепить области в Excel .
• Есть несколько вспомогательных столбцов с промежуточными расчётами или формулами, которые могут сбить с толку других пользователей.
• Вы хотели бы скрыть от посторонних глаз или защитить от редактирования некоторые важные формулы или информацию личного характера.

Читайте дальше, и вы узнаете, как Excel позволяет быстро и легко скрыть ненужные столбцы. Кроме того, из этой статьи Вы узнаете интересный способ скрыть столбцы с помощью инструмента «Группировка», который позволяет скрывать и отображать скрытые столбцы в одно действие.

• Скрываем выбранные столбцы в Excel
• Используем инструмент «Группировка», чтобы в один клик скрыть или отобразить столбцы

Скрываем выбранные столбцы в Excel

Вы хотите скрыть один или несколько столбцов в таблице? Есть простой способ сделать это:

1. Откройте лист Excel и выделите столбцы, которые необходимо скрыть.

Подсказка: Чтобы выделить несмежные столбцы, отметьте их щелчком левой кнопки мыши при нажатой клавише Ctrl.

1. Кликните правой кнопкой мыши на одном из выбранных столбцов, чтобы вызвать контекстное меню, и выберите Скрыть (Hide) из списка доступных действий.

Подсказка: Для тех, кто любит быстрые клавиши. Скрыть выделенные столбцы можно нажатием Ctrl+0.

Подсказка: Вы можете найти команду Скрыть (Hide) на Ленте меню Главная > Ячейки > Формат > Скрыть и отобразить (Home > Cells > Format > Hide & UnHide).

Вуаля! Теперь Вы с лёгкостью сможете оставить для просмотра только нужные данные, а не нужные скрыть, чтобы они не отвлекали от текущей задачи.

Используем инструмент «Группировка», чтобы в один клик скрыть или отобразить столбцы

Те, кто много работает с таблицами, часто используют возможность скрыть и отобразить столбцы. Существует ещё один инструмент, который отлично справляется с этой задачей, – Вы оцените его по достоинству! Этот инструмент – «Группировка». Бывает так, что на одном листе есть несколько несмежных групп столбцов, которые нужно иногда скрывать или отображать – и делать это снова и снова. В такой ситуации группировка значительно упрощает задачу.

Когда Вы группируете столбцы, сверху над ними появляется горизонтальная черта, показывающая, какие столбцы выбраны для группировки и могут быть скрыты. Рядом с чертой Вы увидите маленькие иконки, которые позволяют скрывать и отображать скрытые данные буквально в один клик. Увидев такие иконки на листе, Вы сразу поймёте, где находятся скрытые столбцы и какие столбцы могут быть скрыты. Как это делается:

1. Откройте лист Excel.
2. Выберите ячейки, которые надо скрыть.
3. Нажмите Shift+Alt+Стрелка вправо.
4. Появится диалоговое окно Группирование (Group). Выберите Колонны (Columns) и нажмите OK, чтобы подтвердить выбор.

Подсказка: Еще один путь к этому же диалоговому окну: Данные > Группировать > Группировать (Data > Group > Group).

1. 

Подсказка: Чтобы отменить группировку выберите диапазон, содержащий сгруппированные столбцы, и нажмите Shift+Alt+Стрелка влево.

1. Инструмент «Группировка» добавит специальные символы структуры на лист Excel, которые покажут какие именно столбцы входят в группу.
2. Теперь по одному выделяйте столбцы, которые необходимо скрыть, и для каждого нажимайте Shift+Alt+Стрелка вправо.

Замечание: Объединить в группу можно только смежные столбцы. Если требуется скрыть несмежные столбцы, то придётся создавать отдельные группы.

1. Как только Вы нажмёте сочетание клавиш Shift+Alt+Стрелка вправо, скрытые столбцы будут показаны, а возле черты над сгруппированными столбцами появится специальная иконка со знаком «—» (минус).
2. Нажатие на минус скроет столбцы, и «—» превратится в «+«. Нажатие на плюс моментально отобразит все скрытые в этой группе столбцы.
3. После выполнении группировки в верхнем левом углу появляются маленькие цифры. Их можно использовать для того, чтобы скрывать и отображать одновременно все группы одинакового уровня. Например, в таблице, показанной ниже, нажатие на цифру 1 скроет все столбцы, которые видны на этом рисунке, а нажатие на цифру 2 скроет столбцы С и Е. Это очень удобно, когда Вы создаёте иерархию и несколько уровней группировки.

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

Будьте успешны вместе с Excel!