Как группировать строки и столбцы с помощью контуров и промежуточных итогов в Excel
Если вы профессиональный аналитик данных, вы, вероятно, будете обрабатывать несколько листов в книгах Excel. В файле Excel есть тысячи столбцов и строк, что затрудняет извлечение значимых данных.
Однако такие функции Excel, как структура, групповые данные и промежуточные итоги в списке данных, необходимы для анализа бизнес-данных и представления впечатляющих отчетов аудитории или клиенту.
Как отформатировать данные для группировки столбцов в Excel
Прежде чем идти дальше , чтобы создавать потрясающие визуализации данных с помощью Excel субтотальных функций и авто группы Excel, необходимо реструктурировать исходные данные.
Чтобы добавить промежуточный итог в данные Excel, сначала вам нужно отсортировать список данных. Затем вы можете использовать функцию сортировки Excel следующим образом:
- Щелкните заголовок любого столбца, который нужно отсортировать.
- На ленте вкладки « Главная » найдите пункт « Редактирование» .
- Теперь нажмите « Сортировка и фильтр», а затем выберите « Сортировка от А до Я» , « Сортировка от Я до А» или « Пользовательская сортировка» по своему усмотрению.
Если вы хотите сгруппировать столбцы в Excel, убедитесь, что рабочий лист содержит наборы данных с уникальными заголовками столбцов, без пустых столбцов или строк и промежуточные итоги для каждого подмножества строк. После реструктуризации ваши данные должны выглядеть так, как показано на этом изображении:
Вы можете загрузить этот бесплатный рабочий лист Excel, чтобы попрактиковаться в использовании функций Outlines и Subtotals, прежде чем работать с реальными данными.
Как добавить промежуточный итог в наборы данных Excel
Функция промежуточных итогов Excel позволяет вычислять общие и промежуточные итоги для столбца в списке данных. Вам не нужно форматировать тысячи столбцов и строк, поскольку Excel автоматически реструктурирует список данных.
Функция Excel Subtotal автоматически группирует данные, и вы сможете использовать такие функции, как COUNT, SUM, AVERAGE, Max, Min, Product, Count Numbers, StdDev и т. Д. Чтобы добавить промежуточные итоги на рабочий лист Excel, выполните следующие действия:
- Отсортируйте данные рабочего листа в соответствии с заголовком столбца, для которого вы хотите добавить промежуточные итоги.
- В текущем примере таблицы столбец Категория является основой для сортировки данных.
- Теперь выберите любую ячейку в диапазоне данных.
- На ленте щелкните вкладку « Данные ».
- В разделе « Структура » на вкладке « Данные » нажмите « Промежуточный итог» .
- Excel выберет весь набор данных на листе, и вы увидите диалоговое окно « Промежуточный итог».
7. В диалоговом окне Промежуточный итог выберите нужный столбец, щелкнув раскрывающееся меню в разделе При каждом изменении .
8. Теперь вам нужно установить поле « Использовать функцию », выбрав один вариант из раскрывающегося поля. Вы можете выбрать что угодно: СУММ , СРЕДНИЙ , Макс , Мин и т. Д.
9. Excel поместит вычисленный промежуточный итог в ячейку, которую вы выберете в разделе « Добавить промежуточный итог в» .
10. Нажмите « ОК» , и Excel автоматически добавит промежуточные итоги вместе с контурами .
Автоматически выделенные группы будут отображать промежуточные итоги по каждой группе в соответствии с выбранным заголовком столбца. Например, в этом руководстве категории продуктов питания, такие как батончики , печенье , крекеры и закуски, представляют собой отдельные группы. Суммарная цена каждой группы доступна в столбце Общая цена (долл. США) .
Как сгруппировать в Excel для визуализации вложенных данных
Excel позволяет создать схему, содержащую до восьми уровней. Это часто используемый инструмент, когда вам нужно суммировать длинный список данных в виде группы отдельных заголовков. Крайний левый или внешний контур начинается с числа 1 (один) , а когда вы идете вправо, он может доходить до числа 8 (восемь) .
Вы можете быстро визуализировать вложенные данные в виде группы или сводных данных или даже раскрыть детали каждой группы с помощью групповых строк Excel или инструмента структуры. Excel позволяет создавать Outline столбцов, контур строк, и Outline обеих строк и столбцов.
Автоматическая группа Excel для создания контура
Когда вам нужно организовать меньшие вложенные списки данных, вы можете использовать инструмент Outline для создания автоматических групп. Вы можете попробовать следующие шаги:
- Щелкните любую ячейку в строке данных, которую необходимо сгруппировать.
- Теперь перейдите на вкладку « Данные » на ленте Excel.
- В разделе Outline вы найдете раскрывающееся меню Group .
- Щелкните « Группа», а затем выберите « Автоматическая схема» .
Вы заметите, что групповая функция идеально организовала строки. Кроме того, вы увидите контур полосы на левой стороне , представляющих несколько структур на уровне данных.
Вы можете изучить несколько вариантов визуализации, взаимодействуя с кнопкой уровней , например, номер 1 (один), номер 2 (два), номер 3 (три) и т. Д.
1. Если вы выберете самый низкий уровень, Номер 1 (Один) , вы увидите минимум данных на рабочем листе. В текущем примере это общая стоимость всех продуктов.
2. Когда вы щелкнете следующий уровень, вы увидите только строки промежуточных итогов вместе со строкой общего итога . Вы не увидите других подробных списков данных на листе Excel.
3. Теперь вы можете выбрать наивысший уровень или номер 3 (три) в текущем примере данных, чтобы развернуть все строки и столбцы вместе с обрисованным изображением.
Вы также можете использовать описанные выше шаги для группировки столбцов в Excel. Отформатируйте наборы данных соответствующим образом, чтобы команда Excel Auto Outline могла распознавать промежуточные итоги.
Групповая команда Excel для группировки строк вручную
Если рабочий лист содержит сложные наборы данных, например более одного уровня информации, вам необходимо использовать команду « Группировать» для группировки вручную.
- Выберите одно подмножество данных, которое вы хотите включить в группу, а затем щелкните команду « Группировать» на вкладке « Данные » ленты Excel.
- Чтобы создать другую группу с другим подмножеством данных, выберите строки и затем либо щелкните команду «Группировать», либо воспользуйтесь сочетанием клавиш Shift + Alt + стрелка вправо .
3. Если вы хотите создать вложенную группу, выберите все подробные строки внутри связанной итоговой строки, а затем используйте сочетание клавиш Shift + Alt + стрелка вправо .
Вы можете скрыть подробные строки, используя параметр свертывания строк в Excel: знак минуса (-) на контурах упрощает сложные вложенные данные. Когда вы сворачиваете одну полосу контура, знак « Минус» (-) меняется на знак « Плюс» (+), чтобы впоследствии отобразить строки.
Вы можете дополнительно использовать кнопки « Показать детали» и « Скрыть детали» в разделе « Структура » вкладки « Данные » на ленте Excel.
На листе Excel есть встроенные стили для групп и контуров. Чтобы применить стили , щелкните значок средства запуска диалогового окна (небольшая стрелка раскрывающегося списка) в разделе « Контуры » на вкладке « Данные ».
Вы увидите диалоговое окно настроек . Установите флажок « Автоматические стили», а затем нажмите кнопку « Применить стили» .
Примените профессиональный подход к своим отчетам Excel
С помощью этого учебника Excel по промежуточным итогам и структурам (группам) Excel вы сможете организовать сложные данные в простую для понимания таблицу. Используя листы Excel, вы можете просматривать вложенные данные с разных точек зрения для извлечения ценных аналитических данных.