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

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

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

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

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

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

4.  Уровень 3 содержит строки с подробными данными (в данном случае строки с 11 по 13).

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

В этой статье

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

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

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

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

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

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

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

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

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

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

    Вставка итоговых строк при помощи команды Итоги    

    • Используйте команду Итоги для вставки функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ непосредственно под или над каждой группой строк с подробными данными и для автоматического создания многоуровневой структуры. Подробнее об использовании функции "Итоги" читайте в статье Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

      Вставка собственных итоговых строк    

    • Вставьте собственные итоговые строки с формулами непосредственно под или над каждой группой строк с подробными данными. Например, под (или над) строками данных о продажах за март и апрель используйте функцию СУММ, чтобы подсчитать промежуточные итоговые данные по продажам за эти месяцы. Этот пример показан в таблице далее в этой статье.

  3. Укажите расположение строк итогов: над или под строками с данными. На вкладке Данные в группе Структура нажмите кнопку вызова диалогового окна Структура.
    В группе "Структура" нажмите кнопку вызова диалогового окна.

  4. Если строки итогов расположены над строками с данными, снимите флажок итоги в строках под данными. В ином случае установите только этот флажок.

  5. Для структурирования данных выполните одно из следующих действий.

    Автоматическое структурирование данных

    1. При необходимости выберите ячейку в диапазоне.

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

      Щелкните стрелку под кнопкой "Группировать" и выберите команду "Создать структуру".

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

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

    1. Структурируйте внешнюю группу.

      Структурирование внешней группы

      1. Выделите все подчиненные итоговые строки и соответствующие им строки с данными.

        В приведенном ниже примере строка 6 содержит промежуточные итоговые данные для строк со 2-й по 5-ю, строка 10 содержит промежуточные итоговые данные для строк с 7-й по 9-ю, а строка 11 содержит общий итог. Для группировки всех подробных данных для строки 11 выделите строки со 2-й по 10-ю.

         

        A

        B

        C

        1

        Регион

        Месяц

        Продажи

        2

        Восточный

        Март

        9 647 ₽

        3

        Восточный

        Март

        4 101 ₽

        4

        Восточный

        Март

        7 115 ₽

        5

        Восточный

        Март

        2 957 ₽

        6

        Восточный

        Итого за март

        23 820 ₽

        7

        Восточный

        Апрель

        4 257 ₽

        8

        Восточный

        Апрель

        1 829 ₽

        9

        Восточный

        Апрель

        6 550 ₽

        10

        Восточный

        Итого за апрель

        12 636 ₽

        11

        Итого по Восточному

        36 456 ₽

        Важно : При выделении столбцов не включайте в диапазон данных итоговую строку.

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

        Выберите пункт "Строки", а затем нажмите кнопку ОК.

        Рядом с группой на экране появятся знаки структуры.

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

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

      1. Для каждой внутренней вложенной группы выберите строки с подробными данными, соответствующие итоговой строке.

        В приведенном ниже примере для группировки строк со 2-й по 5-ю, у которых имеется итоговая строка 6, выберите строки со 2-й по 5-ю. Для группировки строк с 7-й по 9-ю, у которых имеется итоговая строка 10, выделите строки с 7-й по 9-ю.

         

        A

        B

        C

        1

        Регион

        Месяц

        Продажи

        2

        Восточный

        Март

        9 647 ₽

        3

        Восточный

        Март

        4 101 ₽

        4

        Восточный

        Март

        7 115 ₽

        5

        Восточный

        Март

        2 957 ₽

        6

        Восточный

        Итого за март

        23 820 ₽

        7

        Восточный

        Апрель

        4 257 ₽

        8

        Восточный

        Апрель

        1 829 ₽

        9

        Восточный

        Апрель

        6 550 ₽

        10

        Восточный

        Итого за апрель

        12 636 ₽

        11

        Итого по Восточному

        36 456 ₽

        Важно : При выделении строк не включайте в диапазон данных итоговую строку.

      2. На вкладке Данные в группе Структура выберите Группировать.

        На вкладке "Данные" нажмите кнопку "Группировать".

        Рядом с группой на экране появятся знаки структуры.

      3. Продолжайте выделение и группировку внутренних строк до тех пор, пока не будут созданы все необходимые уровни структуры.

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

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

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

К началу страницы

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

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

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

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

  3. Укажите место расположения итогового столбца справа или слева от столбца с подробными данными. На вкладке Данные в группе Структура нажмите кнопку вызова диалогового окна Структура.

    Указание расположения итогового столбца

    1. На вкладке Данные в группе Структура нажмите кнопку вызова диалогового окна Структура.

      В группе "Структура" нажмите кнопку вызова диалогового окна.

    2. Для определения места расположения итогового столбца слева от столбца с подробными данными снимите флажок Итоги в столбцах справа от данных. Для задания места расположения итогового столбца справа от столбца с подробными данными установите флажок Итоги в столбцах справа от данных.

    3. Нажмите кнопку ОК.

  4. Чтобы структурировать данные, выполните одно из перечисленных ниже действий.

    Автоматическое структурирование данных

    1. При необходимости выберите ячейку в диапазоне.

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

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

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

    1. Структурируйте внешнюю группу.

      Структурирование внешней группы (всех строк, кроме общего итога)

      1. Выделите все подчиненные итоговые столбцы и соответствующие им столбцы с подробными данными.

        В приведенном ниже примере столбец E содержит промежуточные итоговые данные для столбцов с B по D, столбец I содержит промежуточные итоговые данные для столбцов с F по H, а столбец J содержит общий итог. Для группировки всех исходных данных для столбца J выделите столбцы с B по I.

         

        A

        B

        C

        D

        E

        F

        G

        H

        I

        J

        1

        Регион

        Январь

        Февраль

        Март

        Квартал 1

        Апрель

        Май

        Июнь

        Квартал 2

        Полугодие 1

        2

        Восточный

        371

        504

        880

        1 755

        186

        653

        229

        1 068

        2 823

        3

        Западный

        192

        185

        143

        520

        773

        419

        365

        1 557

        2 077

        4

        Северный

        447

        469

        429

        1 345

        579

        180

        367

        1 126

        2 471

        5

        Южный

        281

        511

        410

        1 202

        124

        750

        200

        1 074

        2 276

        Важно : При выборе не включайте итоговую строку J (общий итог).

      2. На вкладке Данные в группе Структура выберите Группировать.

        На вкладке "Данные" нажмите кнопку "Группировать".

        Рядом с группой на экране появятся символы структуры.

    2. При необходимости структурируйте внутреннюю вложенную группу (отдельные группы столбцов с данными).

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

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

        В приведенном ниже примере для группировки столбов с B по D, у которых имеется итоговый столбец E, выделите столбцы с B по D. Для группировки столбов с F по H, у которых имеется итоговый столбец I, выделите столбцы с F по H.

         

        A

        B

        C

        D

        E

        F

        G

        H

        I

        J

        1

        Регион

        Январь

        Февраль

        Март

        Квартал 1

        Апрель

        Май

        Июнь

        Квартал 2

        Полугодие 1

        2

        Восточный

        371

        504

        880

        1 755

        186

        653

        229

        1 068

        2 823

        3

        Западный

        192

        185

        143

        520

        773

        419

        365

        1 557

        2 077

        4

        Северный

        447

        469

        429

        1 345

        579

        180

        367

        1 126

        2 471

        5

        Южный

        281

        511

        410

        1 202

        124

        750

        200

        1 074

        2 276

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

      2. На вкладке Данные в группе Структура выберите Группировать.

        На вкладке "Данные" нажмите кнопку "Группировать".

        Рядом с группой на экране появятся знаки структуры.

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

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

На вкладке "Данные" нажмите "Разгруппировать".

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

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

К началу страницы

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

  1. Если символы структуры документа ( один два три , плюс и минус ) не отображаются, нажмите кнопку Microsoft Office  Изображение кнопки Office , а затем — Параметры Excel (в Excel 2007). В других версиях откройте вкладку Файл и выберите Параметры. Затем выберите категорию Дополнительно и в разделе Показать параметры для следующего листа выберите лист и установите флажок Показывать символы структуры (при наличии структуры).

  2. Нажмите кнопку ОК.

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

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

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

    • Чтобы скрыть подробные данные группы, нажмите соответствующую этой группе кнопку минус .

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

    • Среди символов структуры один два три нажмите кнопку с номером необходимого уровня. Подробные данные более низких уровней будут скрыты.

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

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

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

    • Чтобы скрыть все подробные данные, нажмите один .

К началу страницы

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

Для представления структурированных строк в Microsoft Excel используются такие стили, как "УровеньСтрок_1" и "УровеньСтрок_2", а для представления структурированных столбцов — "УровеньСтолб_1" и "УровеньСтолб_2". В этих стилях для выделения итоговых строк или столбцов используется полужирное, курсивное и другое форматирование текста. Изменяя способы форматирования, определенные для каждого из стилей, можно применять различные способы оформления текста и ячеек многоуровневой структуры. Применить стиль можно как в процессе создания многоуровневой структуры, так и после него.

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

Автоматическое назначение стиля итоговой строке или столбцу    

  1. На вкладке Данные в группе Структура нажмите кнопку вызова диалогового окна Структура.

    В группе "Структура" нажмите кнопку вызова диалогового окна.

  2. Установите флажок Автоматические стили.

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

  1. Выделите ячейки, к которым необходимо применить стили.

  2. На вкладке Данные в группе Структура нажмите кнопку вызова диалогового окна Структура.

    В группе "Структура" нажмите кнопку вызова диалогового окна.

  3. Установите флажок Автоматические стили.

  4. Нажмите кнопку Применить стили.

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

К началу страницы

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

  1. Если символы структуры документа ( один два три , плюс и минус ) не отображаются, нажмите кнопку Microsoft Office  Изображение кнопки Office , а затем — Параметры Excel (в Excel 2007). В других версиях откройте вкладку Файл и выберите Параметры. Затем выберите категорию Дополнительно и в разделе Показать параметры для следующего листа выберите лист и установите флажок Показывать символы структуры (при наличии структуры).

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

    Подробную информацию см. в разделе Отображение и скрытие структурированных данных.

  3. Выделите диапазон итоговых строк.

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

    Нажмите кнопку "Найти и выделить" и выберите команду "Перейти".

  5. Нажмите кнопку Выделить группу ячеек.

  6. Выберите пункт только видимые ячейки.

  7. Нажмите кнопку ОК, а затем скопируйте данные.

К началу страницы

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

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

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

  • В Excel 2007 нажмите кнопку Microsoft Office  Изображение кнопки Office , а затем — Параметры Excel. В других версиях откройте вкладку Файл и выберите Параметры. Затем выберите категорию Дополнительно и в разделе Показать параметры для следующего листа выберите лист, содержащий структуру, которую нужно скрыть, и снимите флажок Показывать символы структуры (при наличии структуры).

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

  1. Выберите лист.

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

    Нажмите кнопку "Разгруппировать" и выберите пункт "Удалить структуру".

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

К началу страницы

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

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

  1. Создание итогового отчета.

    1. Структурируйте данные.

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

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

      Структурированный список с отображением только итоговых строк

    3. Подробную информацию см. в разделе Отображение и скрытие структурированных данных.

  2. Создание диаграммы для итогового отчета

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

      Например, для построения диаграммы только по итоговым данным Батурина и Белова, а не по общим итогам, выделите ячейки с A1 по C11, как показано в примере выше.

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

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

      Диаграмма на основе промежуточных итогов

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

К началу страницы

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

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×