Как создать древовидную структуру в excel

Обновлено: 07.01.2025

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

Как настроить группировку

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

Развернуть вкладку «Данные» в том документе, котором надо сгруппировать информацию.
Открываем раздел «Структура», в котором разворачиваем набор инструментов.
Всплывет окно, в каком надо задать настройки группировки. Предустановленные параметры следующие: результаты по колонкам записываются непосредственно в правой части от колонок, а по рядам – внизу. Часто людям не нравится эта идея, поскольку по их мнению расположение наименования сверху позволяет значительно сэкономить время. Чтобы это сделать, надо убрать флажок с чекбокса, который находится непосредственно возле соответствующего параметра. В целом, всем пользователям доступна настройка группировки таким способом, чтобы она подходила именно им. Но также можно сделать формирование стиля автоматическим, включив соответствующий пункт.
Подтверждаем свои действия после того, как в параметры будут внесены нужные изменения, нажатием клавиши ОК.

На этом подготовительный этап можно считать оконченным.

Группировка по строкам в Excel

Осуществим группировку информации по рядам.

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

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

Теперь переходим в раздел «Структура». Для этого надо сделать клик по соответствующей кнопке, располагаемой в правой части ленты. Кликаем по кнопке «Группировать». Там появится специальное меню, в котором и нажимаем эту кнопку.

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

После того, как группа спрячется, знак минуса будет заменен на плюс. Клик на него позволит развернуть группу.

Группировка по столбцам в Excel

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

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

На этом группирование данных завершена. Точно так же, как и в предыдущем случае, можно сворачивать и разворачивать элементы группы с помощью клавиши минус или плюс соответственно.

Создание вложенных групп

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

Если материнская группа была свернута, ее нужно развернуть.
Те ячейки, которые надо отнести к подгруппе, надо выделить.
После этого открываем вкладку «Данные», переходим в раздел «Структура» и там нажимаем «Группировать».
Затем выбираем способ группировки – по строкам или по колонкам, после чего подтверждаем свои действия.

После этого у нас появится дополнительная вложенная группа. Сколько максимально подгрупп может создавать пользователь? Количество неограниченное. Мы можем увидеть, что сверху над значками минуса появились цифры. Они позволяют быстро переключаться между разными уровнями вложенности.

Если же группировка осуществляется по строкам, а не столбцам, а не строкам, то эти цифры будут располагаться слева от листа (или знаков минуса/плюса).

Разгруппирование в Эксель

Предположим, нам группа уже не нужна. Что делать в такой ситуации? Есть ли возможность ее удалить? Да, конечно. Эта операция называется разгруппированием. Для начала выделяем все элементы группы, которую нужно расформировать. После этого переходим во вкладку «Данные» и там ищем раздел «Структура». После того, как мы нажмем на соответствующую кнопку, и в этом меню появится кнопка «Разгруппировать». Нажимаем на нее.

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

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

Сначала осуществляем настройку, соответствующую первому разделу этой статьи.
После этого создаем заголовок группы. Для этого делаем дополнительную строку или колонку в зависимости от того, какие данные мы хотим сгруппировать.
Присваиваем в этой колонке или строке название группы.
Выделяем ячейки, которые нужно объединить в одну группу.
Переходим во вкладку «Данные», и там находим группу инструментов «Структура».
Затем нажимаем на кнопку «Группировать», выбираем тип группировки, который нужен в конкретной ситуации и нажимаем на кнопку ОК.
После этого определяемся с тем, как должна называться вторая группа и создаем дополнительную строку с этими данными.
Выделяем ячейки подгруппы, которую нужно выделить. Заголовок, как и в предыдущем примере, не включаем в группу.
Повторяем шаги 5 и 6 применительно ко второму уровню группы.
Повторяем шаги 7-9 применительно к каждому следующему уровню группы.

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

Автоматическая структуризация в Excel

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

Самое большое преимущество автоматической структуризации данных в Эксель – экономия времени. Хотя нужно всегда быть готовым к тому, что придется некоторые правки вносить самостоятельно. К сожалению, роботы пока не научились читать мысли.

Когда оптимальнее всего использовать автоматическую группировку? Лучше всего она работает, когда пользователь работает с формулами. В этом случае ошибок, как правило, не возникает.

Что же нужно делать для того, чтобы сгруппировать данные автоматически? Нужно открыть раздел «Группировать» и там выбрать пункт «Создание структуры». Все остальное Эксель сделает за пользователя.

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

Как сортировать данные таблицы

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

Цветовое деление

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

Затем переходим в раздел «Столбец». Там находим группу «Сортировать по», и далее выбираем ту колонку, которая нужна в нашей ситуации. Далее задаем критерий, по которому должна осуществляться сортировка данных. Например, им может выступать цвет ячеек. Если мы планируем его использовать, надо предварительно окрасить подходящие ячейки соответствующим цветом.

Чтобы определить цвет, необходимо в разделе «Порядок» сделать клик по изображению стрелочки. Рядом будет располагаться пункт, с помощью которого можно определить, куда будут направляться данные, которые программа отсортировала в автоматическом режиме. Если вам нужно, чтобы они были размещены сверху, надо нажать соответствующую кнопку. Если же требуется, чтобы смещение осуществлялось по направлению по строке, надо выбрать пункт «Влево».

Эти же действия можно выполнить и с другими критериями, создав еще один уровень. Для этого нужно найти пункт «Добавить уровень» в диалоговом окне. Далее мы сохраняем документ, после чего можно снова объединить данные, нажав на кнопку «Применить повторно».

Объединение значений

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

Нас в группе «Порядок» интересует пункт «Настраиваемый список», кликнув по которому, пользователь может как сделать собственный перечень условий для сортировки, так и воспользоваться предустановленным в Excel. Так можно осуществлять объединение данных по любому критерию, включая дату, дни недели и так далее.

Как упростить большую таблицу Эксель

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

В начале каждого диапазона должен быть заголовок и не должно быть пустых ячеек. Заголовки должны включать одинакового типа данные.
Предварительно рекомендуется отсортировать данные в алфавитном порядке.
Воспользуемся командой «Промежуточные итоги», которая находится в том же разделе – «Группировать».

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

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

Прежде чем приступить к работе с моделью данных, необходимо получить некоторые данные. Для этого мы используем power query & Transform ( Get & Transform), чтобы вы могли захотеть вернуться на шаг назад и посмотреть видео или воспользоваться учебным руководством по & Преобразование и Power Pivot.

Excel 2016 & Excel для Microsoft 365 — На ленте есть Power Pivot.

Excel 2013 — Power Pivot входит в выпуск Office профессиональный плюс версии Excel 2013, но по умолчанию не включен. Подробнее о запуске надстройки Power Pivot для Excel 2013.

Excel 2010 — скачайте надстройку Power Pivot, а затем установите надстройку Power Pivot,

Excel 2016 & Excel для Microsoft 365 — & Power Query интегрирован с Excel на вкладке Данные.

Excel 2013 : Power Query — это надстройка, которая входит в состав Excel, но ее необходимо активировать. Перейдите в > файлов > надстройки ,а затем в меню Управление в нижней части области выберите Надстройки COM > Перейти. Проверьте, нет ли в Microsoft Power Query Excel, а затем ОК, чтобы активировать его. На ленту будет добавлена вкладка Power Query.

Excel 2010— скачивание и установка надстройки Power Query.. После активации на ленту будет добавлена вкладка Power Query.

Начало работы

Во-первых, вам нужно получить некоторые данные.

В Excel 2016 и Excel для Microsoft 365 используйте data > Get & Transform Data > Get Data (Получить данные), чтобы импортировать данные из любого числа внешних источников данных, таких как текстовый файл, книга Excel, веб-сайт, Microsoft Access, SQL Server или другая реляционная база данных, которая содержит несколько связанных таблиц.

В Excel 2013 и 2010 перейдите в Power Query > Получитьвнешние данные и выберите источник данных.

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

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

Выберите одну или несколько таблиц и нажмите кнопку Загрузить.

Если вам нужно изменить исходные данные, можно выбрать параметр Изменить. Дополнительные сведения см. в обзоре редактора запросов (Power Query).

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

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

Модели создаются явно, если вы импортируете данные с помощью надстройки Power Pivot. В надстройке модель представлена в макете со вкладками так же, как Excel, где каждая вкладка содержит табличные данные. Сведения о том,как импортировать данные с помощью базы данных, см. в этой SQL Server Power Pivot.

Модель может содержать одну таблицу. Чтобы создать модель на основе только одной таблицы, выберите таблицу и нажмите кнопку Добавить в модель данных в Power Pivot. Это может понадобиться в том случае, если вы хотите использовать функции Power Pivot, например отфильтрованные наборы данных, вычисляемые столбцы, вычисляемые поля, ключевые показатели эффективности и иерархии.

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

Советы по уменьшению размера модели данных см. в Excel и Power Pivot.

Дополнительные сведения см. в учебнике Импорт данных в Excel и Создание модели данных.

Совет: Как узнать, есть ли в книге модель данных? Перейдите в Power Pivot > Управление. Если вы видите данные, похожие на таблицу, то модель существует. Дополнительные сведения см. в этой теме.

Создание связей между таблицами

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

Перейдите на > PowerPivot .

На вкладке Главная выберите представление диаграммы.

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

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

Мы создали следующие ссылки:

tbl_Students | ИД учащегося > tbl_Grades | ИД учащегося

Другими словами, перетащите поле "ИД учащегося" из таблицы "Учащиеся" в поле "ИД учащегося" в таблице "Оценки".

tbl_Semesters | ИД семестра > tbl_Grades | Семестр

tbl_Classes | Номер > tbl_Grades | Номер класса

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

Соединители в представлении диаграммы имеют 1 с одной стороны, а "*" — на другой. Это означает, что между таблицами существует связь "один-к-многим", которая определяет способ использования данных в таблицах. Дополнительные сведения см. в этой теме: Связи между таблицами в модели данных.

Соединитетели указывают только на связь между таблицами. Они не будут показывать, какие поля связаны друг с другом. Чтобы увидеть ссылки, перейдите в Power Pivot > Управление > конструктором > связи > Управление связями. В Excel вы можете перейти к data > Relationships (Отношения > данных).

Создание и создание с помощью модели данных для создания сводная диаграмма

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

В Power Pivot, перейдите вуправление .

На вкладке Главная выберите вкладку "PivotTable".

Выберите место для размещения таблицы: новый или текущий.

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

Добавление имеющихся несвязанных данных в модель данных

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

Добавьте данные одним из следующих способов.

Щелкните Power Pivot > Добавить в модель данных.

Выберите Вставка > Сводная таблица и установите флажок Добавить эти данные в модель данных в диалоговом окне "Создание сводной таблицы".

Диапазон или таблица будут добавлены в модель как связанная таблица. Дополнительные сведения о работе со связанными таблицами в модели см. в статье Добавление данных с помощью связанных таблиц Excel в Power Pivot.

Добавление данных в Power Pivot таблицу

В Power Pivot невозможно добавить строку в таблицу, введя текст непосредственно в новой строке, как это можно сделать на листе Excel. Но вы можете добавить строки, скопируяи впав их или обновив исходные данные и обновив модель Power Pivot.

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

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

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

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

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

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

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

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

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

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

Вставка итоговых строк при помощи команды Промежуточные итоги

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

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

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

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

Откроется диалоговое окно Параметры.

Затем в диалоговом окне Параметры сберегайте поле Итоги в строках под данными и нажмите кнопку ОК.

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

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

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

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

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

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

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

1. Первая строка содержит метки и не выбрана.

2. Поскольку это внешняя группа, выберем все строки с подытоговами и подробными сведениями.

3. Не выбирайте общий итог.

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

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

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

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

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

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

1. Можно создать несколько групп на каждом внутреннем уровне. Здесь два раздела уже сгруппировали на уровне 2.

2. Этот раздел выбран и готов к группировке.

3. Не выбирайте итоговую строку для групп данных.

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

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

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

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

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

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

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

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

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

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

Откроется диалоговое окно Параметры.

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

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

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

Выделите ячейку в диапазоне.

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

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

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

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

1. Столбец A содержит метки.

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

3. Не выбирайте столбец общего итога.

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

Над группой появится символ контура.

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

1. Можно создать несколько групп на каждом внутреннем уровне. Здесь два раздела уже сгруппировали на уровне 2.

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

3. Не выбирайте итоговую группу данных.

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

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

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

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

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

Если символы контура , и не отображаются, перейдите в раздел Файл > Параметры > Дополнительные параметры ,а затем в разделе Показать параметры для этого таблицы, выберите показывать символы контура, если она применяется, и нажмите кнопку ОК.

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

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

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

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

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

В символы контура щелкните нужное число уровней. Подробные данные более низких уровней будут скрыты.

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

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

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

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

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

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

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

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

Откроется диалоговое окно Параметры.

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

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

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

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

Откроется диалоговое окно Параметры.

Выберите автоматические стили и нажмите кнопку Применить стили.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Диаграмма сводного отчета

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

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

Например, если выбран параметр Гограмма с кластером, диаграмма будет выглядеть так:

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

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

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

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

Контур строк в Excel Online

Первый столбец содержит метки.

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

Общий итог не сгруппировали в структуре.

Контур столбцов в Excel Online

Первая строка содержит метки.

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

Общий итог не сгруппировали в структуре.

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

Выберем данные (включая все итоги строк или столбцов).

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

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

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

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

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

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

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

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

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

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

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

В символы контура щелкните нужное число уровней. Подробные данные более низких уровней будут скрыты.

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

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

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

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

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

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

Создание диаграммы "дерево"

Перейдите на вкладку Вставка > вставить иерархическую диаграмму >дерево.

Рекомендуемые диаграммы также можно использовать для создания диаграммы "дерево", на вкладке Вставка > Рекомендуемые диаграммы > всех диаграмм.

Совет: На вкладке Конструктори Формат можно настроить внешний вид диаграммы. Если эти вкладки не вы увидите, щелкните в любом месте диаграммы "дерево", чтобы активировать их.

Изменение способа отображения меток

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

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

В разделе Параметры ряда > Параметры подписи выберите нужный параметр отображения.

Создание диаграммы "дерево"

На ленте на вкладке Вставка нажмите кнопку (значок иерархии) и выберите дерево.

Примечание: На вкладке Конструктори Формат можно настроить внешний вид диаграммы. Если эти вкладки не вы увидите, щелкните в любом месте диаграммы "дерево", чтобы активировать их.

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

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

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

На рисунке детальный пример отчета о продажах по структурным подразделениям подневно. А что если нужно предоставить руководителям отчет в разрезе только лишь департаментов и помесячно, без детализации? Чтобы было вот так:

Согласитесь, такой вид таблицы более нагляден и показателен для анализа работы Компании в целом. Но как получить такую таблицу достаточно быстро, не скрывая и не удаляя ячейки? Очень просто, задайте структуру документа.

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

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

Есть два способа создать структуру листа: автоматический и ручной.

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

Программа может попытаться создать структуру автоматически. Для этого нажмите на ленте: Структура – Группировать – Создать структуру .

Если у Вас правильно и логично записаны формулы суммирования, структура будет создана правильно. У меня эта функция часто срабатывает правильно, поэтому сначала я пробую именно автоматическое создание. Структура из примера отлично создалась автоматически.

Ручное создание структуры

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

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

, которые нужно группировать (не выделяйте итоговые). Делайте это с каждой группой по очереди, начиная с самых «глубоких». В моем примере это фамилии менеджеров.

Нажмите на ленте Структура – Группировать (ли нажмите Alt+Shift+стрелка вправо ). Будет создана группировка

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

Когда Вы вставляете новые строки и столбцы в структурированную таблицу – будьте осторожны. Если, например, добавляется новый менеджер в группу, вставляйте строку где-то между сгруппированными строками, не первым элементом группы и не последним. В противном случае, новая строка не впишется в структуру.

При копировании диапазона ячеек, скрытые строки и столбцы не копируются. Но когда они скрыты группировкой – все равно копируются. Чтобы выделить только данные, отображенные на экране, выделите нужный диапазон и нажмите F5 . В открывшемся окне выберите Выделить – Только видимые ячейки . Теперь ячейки, скрытые группировкой, не будут скопированы.

Если Вам больше не нужна структура – удалите ее. Для этого выполните на ленте Данные – Структура – Разгруппировать – Удалить структуру .

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

Вот и все, тренируйтесь и применяйте на практике структурирование данных в Экселе. Приглашаю Вас комментировать изложенный материал, задавайте Ваши вопросы и делитесь опытом!

Добавить комментарий Отменить ответ

12 комментариев

Добрый день!
Как в книге создать 2 группировки друг за другом, чтобы когда они свернуты не отображались страницы.
Например, в таблице 15 строк. 3 всегда отображаются. Затем 4 строки в группировке и следующие 4 строки в группировке. Надо чтобы,когда обе группировки собраны из них ничего не отображалось.

Добрый день!
Как сделать чтобы группировки были разного цвета( например 1 группа где знак (+) красного. 2 группа где знак (+) зеленого итд)

Читайте также: