Как скопировать только верхний уровень группировки в Excel

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

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

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

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

1. Создание внешних групп (уровень 1)

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

В приведенном ниже наборе данных, чтобы сгруппировать все данные для строки 9 (Всего по востоку), мы выбираем строки со 2 по 8.

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

Это добавит полосу в левой части рабочего листа, которая охватывает выбранные строки:

Аналогичным образом вы создаете столько внешних групп, сколько необходимо.

В этом примере нам нужна еще одна внешняя группа для региона Север. Для этого мы выбираем строки с 10 по 16 и нажимаем вкладку Данные > кнопку Группировать > Строки.

Этот набор строк теперь также сгруппирован:

Совет. Чтобы создать новую группу быстрее, нажмите сочетание клавиш Shift + Alt + Стрелка вправо, а не кнопку Группа на ленте.

2. Создание вложенных групп (уровень 2)

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

Например, чтобы создать группу Яблоки в регионе Восток, выберите строки 2 и 3 и нажмите Группа. Чтобы создать группу Апельсины, выберите строки с 5 по 7 и снова нажмите кнопку Группировать.

Аналогичным образом мы создаем вложенные группы для регионов Север и получаем следующий результат:

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

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

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

Как показано на снимке экрана ниже, наши данные теперь сгруппированы по 4 уровням:

• Уровень 1: общая сумма
• Уровень 2. Итоги по региону.
• Уровень 3. Промежуточные итоги по элементам
• Уровень 4: строки сведений

Теперь, когда у нас есть схема строк, давайте посмотрим, как это упрощает просмотр наших данных.

Как свернуть строки в Excel

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

Свернуть строки внутри группы

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

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

Еще один способ свернуть строки в Excel — выбрать любую ячейку в группе и нажать кнопку "Скрыть сведения" на вкладке Данные в группе Структура:

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

Свернуть или развернуть весь план до определенного уровня

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

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

В нашем примере набора данных у нас есть 4 уровня структуры, которые работают следующим образом:

• Уровень 1 показывает только Общую сумму (строка 18) и скрывает все остальные строки.
• Уровень 2 отображает Общий итог и промежуточные итоги Регион (строки 9, 17 и 18).
• Уровень 3 отображает промежуточные итоги Общая сумма, Регион и Элемент (строки 4, 8, 9, 18, 13, 16, 17 и 18).
• Уровень 4 показывает все строки.

На следующем снимке экрана показано, как схема свернута до уровня 3.

Как расширить строки в Excel

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

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

Как удалить контур в Excel

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

Как удалить весь контур

Перейдите на вкладку Данные > группу Структура, щелкните стрелку в разделе Разгруппировать и выберите Очистить структуру.

Примечания:

1. Удаление контура в Excel не приводит к удалению данных.
2. Если удалить структуру с некоторыми свернутыми строками, эти строки могут остаться скрытыми после очистки структуры. Чтобы отобразить строки, используйте любой из методов, описанных в разделе Как отобразить строки в Excel.
3. После того как контур будет удален, вы не сможете вернуть его, нажав кнопку Отменить или нажав сочетание клавиш Отменить ( Ctrl + Z ). Вам придется воссоздать схему с нуля.

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

Чтобы удалить группировку для определенных строк, не удаляя всю структуру, выполните следующие действия:

1. Выберите строки, которые нужно разгруппировать.
2. Перейдите на вкладку Данные в группу Структура и нажмите кнопку Разгруппировать. Или нажмите Shift + Alt + Стрелка влево, что является ярлыком "Разгруппировать" в Excel.
3. В диалоговом окне Разгруппировать выберите "Строки" и нажмите "ОК".

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

Примечание. Невозможно одновременно разгруппировать несмежные группы строк. Вам придется повторить описанные выше шаги для каждой группы отдельно.

Советы по группировке в Excel

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

Как автоматически рассчитать промежуточные итоги группы

Во всех приведенных выше примерах мы вставили собственные строки промежуточных итогов с формулами СУММ. Чтобы промежуточные итоги рассчитывались автоматически, используйте команду «Промежуточный итог» с выбранной вами функцией суммирования, такой как СУММ, СЧЁТ, СРЗНАЧ, МИН., МАКС. и т. д. Команда «Промежуточный итог» не только вставит итоговые строки, но и создаст схему со сворачиваемыми и расширяемыми строками. , тем самым выполняя сразу две задачи!

Применить стили Excel по умолчанию к итоговым строкам

В Microsoft Excel есть предустановленные стили для двух уровней итоговых строк: RowLevel_1 (жирный шрифт) и RowLevel_2 (курсив). Вы можете применять эти стили до или после группировки строк.

Чтобы автоматически применить стили Excel к новому контуру, перейдите на вкладку Данные > группу Структура, щелкните кнопку запуска диалогового окна Структура, затем установите флажок Автоматические стили и нажмите ОК. После этого вы создаете контур, как обычно.

Чтобы применить стили к существующему контуру, также установите флажок Автоматические стили, как показано выше, но нажмите кнопку Применить стили вместо ОК.

Вот как выглядит схема Excel со стилями по умолчанию для итоговых строк:

Как выбрать и скопировать только видимые строки

После того как вы свернули ненужные строки, вы можете скопировать отображаемые релевантные данные в другое место. Однако, когда вы выбираете видимые строки обычным способом с помощью мыши, вы фактически выбираете и скрытые строки.

Чтобы выбрать только видимые строки, необходимо выполнить несколько дополнительных действий:

Выберите видимые строки с помощью мыши.

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

А теперь просто нажмите Ctrl + C, чтобы скопировать выделенные строки, и Ctrl + V, чтобы вставить их куда угодно.

Как скрыть и показать контурные символы

Чтобы скрыть или отобразить полосы контура и номера уровней в Excel, используйте следующую комбинацию клавиш: Ctrl + 8 .

При первом нажатии этой клавиши контурные символы скрываются, при повторном нажатии контур снова отображается.

Символы структуры не отображаются в Excel

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

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

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

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

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

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

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

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

Вставьте свои собственные сводные строки

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

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

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

Затем в диалоговом окне "Настройки" снимите флажок "Сводные строки ниже сведений" и нажмите "ОК".

Обрисуйте свои данные. Выполните одно из следующих действий:

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

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

На вкладке "Данные" в группе "Структура" нажмите стрелку под группой и выберите "Автоматическая схема".

Выделите данные вручную

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Важно! Если разгруппировать структуру, когда подробные данные скрыты, строки сведений могут остаться скрытыми. Чтобы отобразить данные, перетащите видимые номера строк рядом со скрытыми строками. Затем на вкладке "Главная" в группе "Ячейки" нажмите "Формат", выберите "Скрыть и показать", а затем нажмите "Показать строки".

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

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

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

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

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

Затем в диалоговом окне "Настройки" снимите флажок "Сводные столбцы справа от подробностей" и нажмите "ОК".

Чтобы выделить данные, выполните одно из следующих действий:

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

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

На вкладке "Данные" в группе "Структура" нажмите стрелку под "Группой" и выберите "Автоматическая схема".

Выделите данные вручную

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

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

<р>1. Столбец A содержит ярлыки.

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

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

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

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

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

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

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

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

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

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

Выполните одно или несколько из следующих действий:

Показать или скрыть подробные данные для группы

Чтобы отобразить подробные данные в группе, нажмите кнопку группы или нажмите клавиши 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 или получить поддержку в сообществе ответов.

поиск меню

Урок 20. Группы и промежуточные итоги

Введение

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

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

1. Выберите строки или столбцы, которые хотите сгруппировать. В этом примере мы выберем столбцы A, B и C.

Выбор столбцов для группировки

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

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

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

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

Чтобы скрыть и показать группы:

1. Чтобы скрыть группу, нажмите кнопку "Скрыть сведения".

Скрытие группы

Нажатие кнопки "Показать подробности", чтобы отобразить скрытую группу

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

Команда «Промежуточный итог» позволяет автоматически создавать группы и использовать общие функции, такие как СУММ, СЧЁТ и СРЗНАЧ, для суммирования данных. Например, команда «Промежуточный итог» может помочь рассчитать стоимость канцелярских товаров по типам из большого заказа на складские запасы. Он создаст иерархию групп, известную как схема, чтобы упростить организацию рабочего листа.

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

Чтобы создать промежуточный итог:

В нашем примере мы будем использовать команду «Промежуточный итог» с формой заказа футболки, чтобы определить, сколько футболок было заказано в каждом размере (маленький, средний, большой и очень большой). Это создаст структуру нашего рабочего листа с группой для каждого размера футболки, а затем подсчитает общее количество футболок в каждой группе.

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

Таблица отсортирована по размеру футболки

Нажатие команды «Промежуточный итог»

Создание промежуточного итога

Выделенные данные и промежуточные итоги

Чтобы просмотреть группы по уровню:

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

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

Просмотр данных на самом низком уровне

Просмотр данных на следующем уровне

Просмотр данных на самом высоком уровне

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

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

Чтобы удалить промежуточные итоги:

Иногда вы можете не захотеть сохранять промежуточные итоги на своем листе, особенно если вы хотите реорганизовать данные по-разному. Если вы больше не хотите использовать промежуточные итоги, удалите его из рабочего листа.

Выберите вкладку «Данные», затем нажмите команду «Промежуточный итог».

Нажатие команды "Промежуточный итог"

Удаление промежуточных итогов

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

Группировка строк и столбцов в Excel имеет решающее значение для построения и поддержки хорошо организованной и структурированной финансовой модели. Типы финансовых моделей Наиболее распространенные типы финансовых моделей включают в себя: модель с тремя отчетами, модель DCF, модель слияний и поглощений, модель LBO. , бюджетная модель. Откройте для себя 10 лучших типов. Использование функции группы Excel. Функции Список наиболее важных функций Excel для финансовых аналитиков. Эта шпаргалка охватывает сотни функций, которые крайне важно знать, поскольку аналитик Excel — это лучшая практика, когда речь идет о сохранении порядка, поскольку вы никогда не должны скрывать ячейки в Excel. В этом руководстве показано, как группировать в Excel, с пошаговыми инструкциями, примерами и снимками экрана.

Сочетания клавиш

Хотите стать мастером Excel? Повысьте свою производительность с помощью подробного руководства по сочетаниям клавиш CFI.

Групповая функция Excel

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

Причины использования групповой функции Excel:

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

Эта функция находится в разделе «Данные» на ленте, а затем в «Группе».

Пример группировки в Excel

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

Вот шаги, которые нужно выполнить, чтобы сгруппировать строки:

1. Выберите строки, к которым вы хотите добавить группировку (целые строки, а не только отдельные ячейки).
2. Перейти к ленте данных
3. Выбрать группу
4. Выберите группу еще раз.

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

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

Если вы хотите снова развернуть их, нажмите кнопки «+» на полях, как показано на снимке экрана ниже.

Кроме того, в левом верхнем углу есть кнопка «1», чтобы свернуть все группы, и кнопка «2», чтобы развернуть все группы.

Почему никогда не следует скрывать ячейки в Excel

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

Единственный способ увидеть, что ячейки скрыты, – это заметить, что номер строки или столбца внезапно перескакивает (например, из строки 25 в строку 167).

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

Загрузить шаблон группы Excel

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

Дополнительные ресурсы

CFI является официальным глобальным поставщиком услуг аналитика по финансовому моделированию и оценке (FMVA)™. Стать сертифицированным аналитиком по финансовому моделированию и оценке (FMVA)® Сертификат CFI по финансовому моделированию и оценке (FMVA)® поможет вам завоевать доверие нужно в вашей финансовой карьере. Зарегистрируйтесь сегодня! обозначение, ведущая программа сертификации финансовых аналитиков. Эти дополнительные ресурсы CFI будут полезны для продолжения обучения и развития вашей карьеры:

• Список из более чем 300 функций Excel Функции Список наиболее важных функций Excel для финансовых аналитиков. Эта шпаргалка охватывает сотни функций, которые важно знать аналитику Excel.
• Сочетания клавиш Сочетания клавиш Excel Сочетания клавиш для ПК Mac Excel — список наиболее важных и распространенных сочетаний клавиш MS Excel для пользователей ПК и Mac, специалистов в области финансов и бухгалтерского учета. Сочетания клавиш ускоряют ваши навыки моделирования и экономят время. Изучите редактирование, форматирование, навигацию, ленту, специальную вставку, работу с данными, редактирование формул и ячеек и другие сочетания клавиш.
• Что такое финансовое моделирование? Что такое финансовое моделирование Финансовое моделирование выполняется в Excel для прогнозирования финансовых показателей компании. Обзор того, что такое финансовое моделирование, как и зачем создавать модели.
• Курсы по финансовому моделированию Курсы по финансовому моделированию

Бесплатное руководство по Excel

Чтобы овладеть искусством работы с Excel, ознакомьтесь с БЕСПЛАТНЫМ ускоренным курсом CFI по Excel. Основы Excel — формулы для финансов Вы ищете ускоренный курс по Excel? Получите бесплатное обучение Excel для карьеры в области корпоративных финансов и инвестиционно-банковской деятельности от Института корпоративных финансов. , который научит вас, как стать опытным пользователем Excel. Изучите самые важные формулы, функции и сочетания клавиш, чтобы уверенно проводить финансовый анализ.

Запустите бесплатный курс CFI по Excel прямо сейчас Основы Excel — формулы для финансов Вы ищете ускоренный курс Excel? Пройдите бесплатное обучение Excel, чтобы начать карьеру в сфере корпоративных финансов и инвестиционно-банковских услуг, от Института корпоративных финансов.

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

Именно здесь вступают в действие группировка и структурирование.

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

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

Давайте приступим!

Каспер Лангманн, соучредитель Spreadsheeto

*Это руководство предназначено для Excel 2019/Microsoft 365 (для Windows). Есть другая версия? Нет проблем, вы можете выполнить те же действия.

Оглавление

Что такое вложенные данные?
А зачем обрисовывать?

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

Вот одна из таблиц, которую мы будем использовать в наших примерах:

Наши данные выглядят следующим образом:

• Классы
  • Всего за семестр
  • Всего за семестр
    • Всего за год
    • Всего за семестр
    • Всего за семестр
      • Всего за год

      Как видите, существуют согласованные уровни информации, и они повторяются: вложены друг в друга.

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

      Зачем создавать схему для вложенных данных?

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

      Получите БЕСПЛАТНЫЙ файл с упражнениями

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

      В этой статье мы расскажем много нового, так что берите ее прямо сейчас и приступим!

      Загрузите БЕСПЛАТНЫЙ файл упражнения

      Группировка ваших данных

      Первое, что вам нужно сделать, это сгруппировать данные.

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

      Самый простой способ сделать это — заставить Excel автоматически обрисовывать ваши данные.

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

      Нажмите на любую ячейку, затем перейдите на вкладку "Данные" на ленте. Нажмите «Группа» > «Автоматическая структура».

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

      Каждая полоса представляет собой уровень организации. Полосы правее — это второстепенные уровни.

      Каспер Лангманн, соучредитель Spreadsheeto

      В нашем случае Excel идеально сгруппировал данные. Он разделил данные на три уровня и не включал общие строки.

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

      Сначала нажмите "Разгруппировать" > "Очистить контур":

      Полосы в левой части окна исчезли, и мы вернулись к тому, с чего начали.

      Теперь выберите строки со 2 по 13:

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

      Каспер Лангманн, соучредитель Spreadsheeto

      Нажмите "Группа" (на вкладке "Данные"), и вы увидите полосу в левой части экрана, которая охватывает эти строки:

      Этот набор строк теперь является группой.

      Давайте создадим вложенную группу. Выберите строки со 2 по 6 и нажмите "Группировать":

      Вероятно, отсюда вы можете увидеть, к чему все идет. Выберите строки с 8 по 12 и снова нажмите "Группировать".

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

      Совет: группируйте столбцы

      В нашем примере мы сгруппировали строки, но вы также можете сгруппировать столбцы. Выберите столбцы, которые хотите сгруппировать, и нажмите кнопку "Группировать".

      Это работает точно так же, как группировка строк.

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

      Добавление данных в схему

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

      Давайте попробуем это в примере книги.

      Я добавил новую строку данных: общий итог.

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

      Каспер Лангманн, соучредитель Spreadsheeto

      Добавить этот уровень в схему очень просто: выделите строки со 2 по 27 (строки, которые включены в общий итог) и нажмите "Группировать".

      Как видите, теперь у нас есть новый уровень в структуре.

      Совет: повторное автоматическое выделение

      Когда вы добавите в схему больше данных, вы можете щелкнуть в любом месте набора данных и снова нажать «Группировать» > «Автоматическая структура».

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

      Использование структур для быстрого просмотра данных

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

      Одна из самых полезных вещей, которые вы можете сделать, – свернуть определенные группы. Щелкните поле в нижней части панели группы, чтобы свернуть эту группу:

      Это значительно упрощает чтение сложных электронных таблиц.

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

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

      Каспер Лангманн, соучредитель Spreadsheeto

      Автоматическое суммирование групп

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

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

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

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

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

      Теперь Excel создает строки промежуточных итогов для каждого года.

      Мы можем повторить процесс и выбрать «Сеанс» вместо года (не забудьте снять флажок «Заменить текущие промежуточные итоги»), и мы получим строки, которые были на первом листе:

      Совет: промежуточные итоги в правильном порядке

      Если бы мы подсчитали промежуточные итоги сеансов до года, мы бы получили другой результат:

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

      Если вы еще не создали структуру, вы все равно можете использовать команду "Промежуточный итог".

      На самом деле, он создаст для вас схему, выполнив два шага сразу!

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

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

      Но Excel может автоматически стилизовать ваши промежуточные итоги.

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

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

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

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

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

      При работе с планами следует помнить о нескольких вещах.

      Во-первых, когда некоторые из ваших групп свернуты и вы выбираете видимые строки, вы фактически выбираете и невидимые строки, как и при традиционном скрытии строк.

      Чтобы выбрать только видимые строки (например, если вы хотите их скопировать и вставить), вам нужно сделать это по-другому.

      После того как вы свернули группы, которые не хотите копировать, перейдите на вкладку "Главная". Оттуда нажмите кнопку «Найти и выбрать» и выберите «Перейти к специальному»:

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

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

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

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

      Каспер Лангманн, соучредитель Spreadsheeto

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

        
      • Можно ли редактировать файл в облаке
        
      • Поддерживает ли PS4 4K
        
      • При подключении наушников к ноутбуку звук остается в динамиках
        
      • Швейная машина электронная или компьютерная
        
      • Как нарисовать шестеренку в coreldraw