Как в excel сделать иерархическую таблицу

Создание иерархии

Excel для Microsoft 365 Word для Microsoft 365 Outlook для Microsoft 365 PowerPoint для Microsoft 365 Excel 2021 Word 2021 Outlook 2021 PowerPoint 2021 Excel 2019 Word 2019 Outlook 2019 PowerPoint 2019 Excel 2016 Word 2016 Outlook 2016 PowerPoint 2016 Excel 2013 Word 2013 Outlook 2013 PowerPoint 2013 Excel 2010 Word 2010 Outlook 2010 PowerPoint 2010 Excel 2007 Word 2007 Outlook 2007 PowerPoint 2007 Еще. Меньше

Если вы хотите проиллюстрировать иерархические отношения, которые прогрессируют по вертикали или по горизонтали, можно создать графический элемент SmartArt, использующий макет иерархии, например Иерархия с меткой. Иерархия представляет собой ряд упорядоченных групп людей или элементов в системе. Используя графический элемент SmartArt в Excel, Outlook, PowerPoint или Word, вы можете создать иерархию и включить ее в электронную почту, сообщение электронной почты, презентацию или документ.

Важно: Если вы хотите создать организациическую диаграмму,создайте графический элемент SmartArt с помощью макета Организацивая диаграмма.

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

Создание иерархии

1. На вкладке Вставка в группе Иллюстрации нажмите кнопку SmartArt.
2. В коллекции Выбор рисунка SmartArt щелкните Иерархияи дважды щелкните макет иерархии (например, Горизонтальная иерархия).
3. Для ввода текста выполните одно из следующих действий.
4. В области текста щелкните элемент [Текст] и введите содержимое.
5. Скопируйте текст из другого места или программы, в области текста щелкните элемент [Текст], а затем вставьте скопированное содержимое.

Примечание: Если область текста не отображается, щелкните элемент управления.

• Щелкните поле в графическом элементе SmartArt и введите свой текст.

Примечание: (ПРИМЕЧАНИЕ.) Для достижения наилучших результатов используйте этот вариант после добавления всех необходимых полей.

Добавление и удаление полей в иерархии

Добавление поля

1. Щелкните графический элемент SmartArt, в который нужно добавить поле.
2. Щелкните существующее поле, ближайшее к месту вставки нового поля.
3. В разделе Работа с рисунками SmartArt на вкладке Конструктор в группе Создать рисунок щелкните стрелку под командой Добавить фигуру. Если вкладка Работа с рисунками SmartArt или Конструктор не отображается, выделите графический элемент SmartArt.
4. Выполните одно из указанных ниже действий.
   • Чтобы вставить поле на том же уровне, что и выбранное поле, но после него, выберите команду Добавить фигуру после.
   • Чтобы вставить поле на том же уровне, что и выбранное поле, но перед ним, выберите команду Добавить фигуру перед.
   • Чтобы вставить поле на один уровень выше выбранного поля, выберите команду Добавить фигуру над.
     Новое поле займет место выбранного поля, а выбранное поле и все поля непосредственно под ним будут понижены на один уровень.
   • Чтобы вставить поле на один уровень ниже выбранного поля, выберите команду Добавить фигуру под. Новое поле будет добавлено после другого на том же уровне.

Удаление поля

Чтобы удалить поле, щелкните его границу и нажмите клавишу DELETE.

• Если вам нужно добавить поле в иерархию, поэкспериментируйте с ним до, после, сверху или под выбранным полем, чтобы получить нужное расположение.
• Несмотря на то что в макетах иерархии, таких как Горизонтальная иерархия, нельзя автоматически соединить линией два поля верхнего уровня,вы можете сымитировать это, добавив поле в графический элемент SmartArt и нарисуя линию для соединения полей.
• Чтобы добавить поле из области текста:
  1. Поместите курсор в начало текста, куда вы хотите добавить фигуру.
  2. Введите нужный текст в новой фигуре и нажмите клавишу ВВОД. Чтобы добавить отступ для фигуры, нажмите клавишу TAB, а чтобы сместить ее влево — клавиши SHIFT+TAB.

Перемещение полей в иерархии

• Чтобы переместить поле, щелкните его и перетащите на новое место.
• Чтобы фигура перемещалась с очень маленьким шагом, удерживайте нажатой клавишу CTRL и нажимайте клавиши со стрелками.

Изменение макета иерархии

1. Щелкните правой кнопкой мыши иерархию, которую вы хотите изменить, и выберите изменить макет.
2. Щелкните Иерархияи сделайте одно из следующих:
   • Чтобы показать иерархические отношения, которые выровна сверху вниз и сгруппировать по иерархии, щелкните Иерархия с меткой.
   • Чтобы показать группы данных, встроенные сверху вниз, и иерархии внутри каждой группы, щелкните Иерархия таблиц.
   • Чтобы показать иерархические отношения в группах, щелкните Иерархический список.
   • Чтобы показать иерархические отношения, которые выровна по горизонтали, выберите горизонтальную иерархию.
   • Чтобы показать иерархические отношения, которые выровна по горизонтали и помечены иерархией, щелкните Горизонтальная иерархия с подписи.

Примечание: Чтобы изменить макет SmartArt, можно также выбрать нужный параметр в разделе Работа с рисунками SmartArt на вкладке Конструктор в группе Макеты. При выборе варианта макета можно предварительно просмотреть, как будет выглядеть графический элемент SmartArt.

Изменение цветов иерархии

Чтобы быстро оформление графического элементов SmartArt выглядело и выглядело как дизайнер, вы можете изменить цвета или применить стиль SmartArt к своей иерархии. Вы также можете добавить эффекты, такие как свечение, сглаживание или объемные эффекты.

К полям в графических элементах SmartArt можно применять цветовые вариации из цвета темы.

1. Щелкните графический элемент SmartArt, цвет которого нужно изменить.
2. В разделе Работа с рисунками SmartArt на вкладке Конструктор в группе Стили SmartArt нажмите кнопку Изменить цвета. Если вкладка Работа с рисунками SmartArt или Конструктор не отображается, выделите графический элемент SmartArt.
3. Выберите нужную комбинацию цветов.

Совет: (ПРИМЕЧАНИЕ.) При наведении указателя мыши на эскиз можно просмотреть, как изменяются цвета в графическом элементе SmartArt.

Изменение цвета или стиля линии

1. В графическом элементе SmartArt щелкните правой кнопкой мыши границу линии или фигуры, которые вы хотите изменить, и выберите пункт Формат фигуры.
2. Чтобы изменить цвет границы, нажмите кнопку Цвет линии ,выберите цвет , а затем выберите нужный цвет.
3. Чтобы изменить тип границы фигуры, щелкните Тип линии и задайте нужные параметры.

Изменение цвета фона окна в иерархии

1. Щелкните правой кнопкой мыши границу фигуры и выберите команду Формат фигуры.
2. Щелкните область Заливка и выберите вариант Сплошная заливка.
3. Нажмите кнопку Цвет и выберите нужный цвет.
4. Чтобы указать степень прозрачности фонового цвета, переместите ползунок Прозрачность или введите число в поле рядом с ним. Значение прозрачности можно изменять от 0 (полная непрозрачность, значение по умолчанию) до 100 % (полная прозрачность).

Применение стиля SmartArt к иерархии

Стиль SmartArt — это сочетание различных эффектов, например стилей линий, рамок или трехмерных эффектов, которые можно применить к полям графического элемента SmartArt для придания им профессионального, неповторимого вида.

1. Щелкните графический элемент SmartArt, стиль SmartArt которого нужно изменить.
2. В разделе Работа с рисунками SmartArt на вкладке Конструктор в группе Стили SmartArt выберите стиль. Чтобы отобразить другие стили SmartArt, нажмите кнопку Дополнительно . Если вкладка Работа с рисунками SmartArt или Конструктор не отображается, выделите графический элемент SmartArt.

• (ПРИМЕЧАНИЕ.) При наведении указателя мыши на эскиз становится видно, как изменяется стиль SmartArt в рисунке SmartArt.
• Вы также можете настроить графический элемент SmartArt, перемещая поля,меняя их размер,добавляя заливку или эффект и добавляя рисунок.

Анимировать иерархию

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

1. Щелкните иерархию графического элементов SmartArt, которую нужно анимировать.
2. На вкладке Анимация в группе Анимация нажмите кнопку Анимация ивыберите по ветви по одному.

Примечание: При копировании иерархии с примененной к ней анимацией на другой слайд также копируется анимация.

Нужна дополнительная помощь?

Нужны дополнительные параметры?

Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.

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

Иерархии в Power Pivot

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

Иерархия — это список столбцов, которые скатываются к одному элементу в отчете Pivot или Power View. Иерархия отображается в списке полей как один объект. Иерархии упрощают выбор и навигацию по общим путям данных при создании отчетов и степных таблиц. Чтобы создать иерархии, необходимо включить надстройку Power Pivot.

1. Откройте окно Power Pivot.
2. Щелкните Главная >представление >схемы.
3. В представлении диаграммы выберите один или несколько столбцов в той же таблице, которую вы хотите разместить в иерархии. Если в таблице нет всех столбцов, которые вы хотите использовать, вы можете добавить их с помощью related. См. функцию RELATED (DAX).
4. Щелкните правой кнопкой мыши один из выбранных столбцов.
5. Нажмите кнопку Создать иерархию, чтобы создать родительский уровень иерархии в нижней части таблицы. Столбцы будут копироваться в иерархию в качестве уровней для детей.
6. Введите имя новой иерархии.
7. Перетащите дополнительные столбцы на родительский уровень иерархии, что создаст на их уровне ребенка и помещает уровни в нижней части иерархии. При желании вы можете разместить столбец в определенном расположении списка иерархии, перетащив столбец в нужное место в иерархии.

• Если для создания иерархии используется несколько выборок, сначала устанавливается порядок уровней для детей в соответствии с емким количеством столбцов. Первое число отображается в списке, где значения являются самыми редкими и уникальными ( например, идентификационные номера и имена). Столбцы с наименьшим емким значением отображаются последними в списке, где значения являются более распространенными, например состояние, boolean values или common classifications. Помните, что добавление дополнительных столбцов помещает уровни детей в нижней части списка. Чтобы изменить порядок столбцов, можно перетащить их.
• Иерархию можно создать из скрытого столбца (столбца, который скрыт от клиентских средств).
• Если вы знаете, какие столбцы нужно создать в иерархии в качестве уровней для детей, щелкните элемент Создать иерархию в контекстном меню, чтобы выбрать несколько столбцов и быстро создать иерархию с несколькими дополнительными уровнями.

Изменение иерархии

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

Изменение имени иерархии или уровня ребенка

Щелкните родительский или родительский уровень иерархии правой кнопкой мыши и выберите переименовать. Можно также дважды щелкнуть родительская иерархия и изменить имя.

Изменение порядка уровня ребенка в иерархии

Перетащите уровень ребенка в новую позицию в иерархии. Можно также щелкнуть правой кнопкой мыши на уровне ребенка иерархии и нажать кнопку Вверх, чтобы переместить уровень вверх по списку. Можно также нажать кнопкуВниз, чтобы переместить уровень вниз по списку.

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

Добавление еще одного уровня ребенка в иерархию

Это можно сделать несколькими вариантами.

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

Удаление уровня ребенка из иерархии

Щелкните правой кнопкой мыши уровень ребенка и выберите удалить из иерархии. Можно также щелкнуть уровень ребенка в иерархии и нажать клавишу DELETE. Если вы хотите освободить этот уровень ребенка, просто добавьте столбец еще раз.

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

Скрытие или показ имени источника

Щелкните правой кнопкой мыши child level иерархии и выберите скрыть имя столбца из источника или Показать имя столбца источника, чтобы перейдите между двумя вариантами.

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

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

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

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

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

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 или получить поддержку в сообществах.

Её величество Иерархия. Классификация и способы хранение в MS Excel

Я работаю консультантом в области бюджетирования, финансовой отчетности. Уже много лет я интересуюсь иерархиями и эффективной работой с ними. Основной средой работы до недавнего времени был MS Excel, сейчас уже половина работы на Pandas. Оба этих инструмента по своей природе плоские (т.е. в основе – плоские таблицы). Тем не менее, каждый день мы встречаем иерархии и с ними приходится как-то работать. Давайте разбираться.

1. Термины
2. Ограничения
3. Классификация
4. Прочие свойства иерархии
5. Форматы хранения в MS Excel
6. Работа с иерархиями в Python, SQL
7. Как это практически применять?
8. Заключение

Термины

Когда мы начинаем работать со сложной системой, первый шаг – определение терминов. Во многом они из теории графов. Некоторые термины определены не строго, ряд моментов я посчитал очевидными. Исходил скорее из практической бизнес-потребности и здравого смысла.

Вершина/элемент – базовый объект иерархии. Между вершинами устанавливаются связи (ребра)
Ребро – направленная связь от одной вершины к другой. От родителя к ребенку
Дети/ребенок/дочерние вершины – потомок/потомки вершины (количество от 0 до N)
Родитель вершины – предок вершины (количество от 0 до 1)
Лист – часть вершин может хранить на себе данные, это и есть листовые вершины
Узел – часть вершин являются группировочными, они сами по себе не хранят данные. Они всегда расчетные (т.е. показатели на них рассчитываются агрегацией всех детей на всех уровнях ниже
Уровни – каждая вершина иерархии принадлежит определенному уровню
Корень – вершина на первом уровне. Вершина не имеющая родителей
Дерево – иерархия у которой один корень
Лес – иерархия у которой более одного корня

Ограничения

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

1. Очевидные ограничения: нет циклов; у каждой вершины либо ноль, либо один родитель; все вершины уникальные (нет дубликатов)
2. Иерархия выровнена по левому краю (left balanced, сбалансированная слева). Т.е. вершины без родителей могут быть только на первом уровне
3. Уровень вершины определяется полным путем к этой вершине и не может быть установлен принудительно. Ребенок всегда должен быть на уровне +1 к родительскому (skip level not allowed, пропуск уровней не допускается)
4. Вершины без детей могут быть на разных уровнях (right unbalanced, допускается иерархия несбалансированная справа)

Классификация

Раньше я думал, что все иерархии одинаковые. Но это не так. Давайте рассмотрим две иерархии:

1. Есть множество сотрудников компании. У каждого сотрудника может быть от 0 до 1 начальников и от 0 до N подчиненных.
2. Есть справочник Продукция. Продукция иерархически сгруппирована (например, стол и стул попадут в узел Мебель и т.д.)

В целом это две иерархии. Но с другой стороны очевидно, что у них разная природа. Ключевое отличие заключается в том, что все сотрудники — это равноправные объекты, т.е. у них одинаковые атрибуты, и вообще, это экземпляры одного класса. А в справочнике Продукция, стул и мебель нельзя назвать равноправными объектами. Стул – это конкретный продукт (листовой элемент), а мебель – это группировка (узловой элемент).

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

Далее посмотрим на неоднородные иерархии. В них есть ключевой вопрос, как отличить узел от листа. Долгое время я думал, что действует простое правило: «вершина без детей — это лист, вершина с детьми — это узел». Это правило часто используют для написания формул в Excel, которые автоматически определяют тип вершины (лист или узел). Но этот принцип не является аксиомой, есть и другие иерархии. Рассмотрим две:

1. Иерархия папок и файлов в Windows (объект «ссылка» не учитываем, иначе будет граф)
2. Справочник Продукция, разрабатываемый для какой-нибудь Excel модели

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

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

Если выполняется, то узлы и листы иерархии определяются автоматически по ее структуре. Для удобства будем называть это — Узлы автоматом неоднородной иерархии.

Если не выполняется, то для каждой вершины без детей существует неопределенность, поэтому надо хранить признак, является ли такая вершина листовой или узловой. А это будет — Узлы вручную.

Прочие свойства иерархий

Ограничение на однородность детей

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

Режим сортировки

Важным свойством иерархии является сортировка ее вершин. Сортировка может иметь такие режимы:

1. Нет сортировки, дети каждого узла — это неупорядоченное множество
2. Сортировка есть. Она сделана вручную, т.е. не алгоритмически
3. Сортировка есть. Она установлена в виде правила (по убыванию/по возрастанию). Правило может быть установлено для:
   1. Всей иерархии в целом
   2. Каждого уровня отдельно
   3. Каждого узла отдельно

   Форматы хранение в MS Excel

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

   «Формат 1. Ребенок справа снизу»

   

   Обычно однородную иерархию в таком формате не хранят. А вот неоднородные хранят именно в таком виде. Если имеем дело с неоднородной иерархией с автоматическими узлами (наиболее частая ситуация), то столбец 7 на скрине рассчитывается формулой.

   • самый распространенный формат в среде Excel для неоднородных иерархий с автоматическими узлами (для ручных узлов тоже может быть использован)
   • наглядная иерархия для восприятия человеком
   • позволяет хранить сортировку вершин, атрибуты листов
   • невозможно работать как с плоской таблицей
   • нельзя сделать сводную таблицу

   Кстати, в Excel для сводных таблиц есть функционал показа иерархии в таком виде: «Show in Outline form».

   «Формат 2. Путь к элементу»

   

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

   «Формат 3. Путь к листу (все элементы)»

   

   Почти тоже самое, что и формат 2, но только полный путь указывается не для всех вершин иерархии, а только для листовых. Редко используется, т.к. нет очевидных преимуществ по сравнению с форматом 2. А вот минусы есть — более сложная нотация, сложность в заполнении таблицы человеком.

   «Формат 4. Отступы/пробелы»

   

   Этот формат похож на формат 1, но только тут играют роль не столбцы с уровнями, а количество отступов (табуляция или определенное количество пробелов). Формат часто встречается, однако с ним сложно работать. В процессе определения уровня для вершин часто возникают ошибки из-за несистемных отступов и человеческих ошибок. В Excel надо использовать макрофункцию, чтобы посчитать количество отступов в ячейке. Из плюсов — более компактное представление иерархии (один столбец, вместо нескольких).

   Кстати, в Excel для сводных таблиц есть функционал показа иерархии в таком виде: «Show in Compact form». Там можно настраивать количество отступов, что удобно.

   «Формат 5. Перечень элементов и их уровней»

   

   Этот формат похож на формат 4, однако вместо отступов используются уже готовые уровни для каждой вершины (столбец — «Уровень»). Используется редко, т.к. сложен для восприятия человеком и плохо пригоден для анализа в Excel.

   «Формат 6. Путь к листу (только листы)»

   

   Это самое плоское представление иерархии, которое можно себе представить. По сути получается из формата 3, только надо удалить все узловые элементы. Удобно анализировать сводными таблицами, фильтрами. Человеком воспринимается не очень, т.к. нет визуальной иерархии (ступенек с уровнями).

   ВАЖНО! Это единственный формат хранения, где каждая строчка – листовой элемент и порядок строк не важен. В связи с этим, данный формат иерархии может быть добавлен в любую плоскую таблицу, к любому столбцу. Это позволяет подходить к иерархии как к группировке листовых элементов.

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

   «Формат 7. Элемент-родитель»

   

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

   Для неоднородных иерархий плохо подходит, т.к. список узлов + листов одновременно получается разнородным. Такого я не встречал на практике.

   «8. Ребенок справа»

   

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

   Раньше я думал, что данный вид иерархий редко применяется, но недавно выяснили, что на формате 8 почти всегда основано строение многострочной шапки таблицы. Т.е. если вы видите таблицу в Excel и в ней шапка состоит из более, чем одной строки, то вероятнее всего, она использует формат иерархии 8 (естественно с учетом того, что шапка – это транспонированная таблица и ее надо читать сверху вниз).

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

   Кстати, в Excel для сводных таблиц есть функционал показа иерархии в таком виде: «Show in Tabular form».

   «9. Совмещенные иерархии»

   

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

   Я видел попытки вести совмещенную иерархию, но там не было никакой утвержденной нотации. Поэтому расшифровать таблицу было крайне сложно.

   Работа с иерархиями в Python, SQL

   Мы часто получаем данные из Excel в Python для обработки. Это удобно и позволяет решить множество сложных задач, например, найти неточные дубликаты в справочнике (fuzzy match, неточное сопоставление текстовых строк). В части иерархий в Python мы пока не выработали единых подходов к хранению и обработке иерархий. Пока для каждой задаче разрабатываем подход индивидуально. Однако есть идеи по стандартизации этого процесса и созданию Python класса, который бы позволил структурировать эти операции.

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

   Как это практически применять?

   Есть два режима работы с иерархиями: 1) вам надо спроектировать иерархию в Excel 2) вам дали уже готовую иерархию и надо с ней работать. В любом случае следует нужно:

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

   Заключение

   Зачем все это? Я ставил перед собой несколько целей:

   1. При работе со сложными системами половина проблем возникает ввиду отсутствия общей терминологии. Наличие единых терминов — критически важно для решения практических проблем с иерархиями
   2. Задачи с иерархиями возникает каждый день, их надо как-то решать средствами Excel, иногда в связке с Python. Две наиболее частые проблемы – определения оптимального формата хранения иерархии в Excel и интерпретация уже созданных кем-то иерархий. Надеюсь моя классификация и перечень форматов хранения упростят эти процессы
   3. Глубокое понимание иерархии позволит контролировать её целостность, автоматизировать проверки в Excel и Python, делать анализ и преобразование форматов

   p.s. Цифровизация консалтинга

   Систематизация подходов к работе с иерархиями проходит в рамках нашего первого шага по цифровизации. Его концепция в следующем:

   «Все ключевые данные на проекте должны быть в 1NF (плоская таблица), либо должен существовать алгоритм их перевода в 1NF»

   А ниже полный перечень направлений цифровизации по которым мы двигаемся вперед:

   1. Данные, плоские таблицы, реляционные базы данных
   2. Алгоритмы, Python, VBA
   3. Иерархии
   4. Графы
   5. Математика, системное мышление, комбинаторика, теория множеств
   6. Нейросети
   Читать:

   Как поставить фото по центру в css