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

Приветствую всех, дорогие читатели блога TutorExcel.Ru!

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

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

Мы разберем 3 основных варианта группировки полей сводной таблицы в зависимости от типа данных:

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

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

Промежуточные итоги в Excel (Эксель): примеры использования (2019)

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

Для начала научимся делать сводные таблицы.

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

И в каждой строке 3-м параметра связаны между собой, т.е. например, 01.02.2010 года Товар 1 продали на 422 656 руб.

После того, как вы подготовили данные для сводной таблицы, устанавливаем курсор в первый столбец в первую ячейку простой таблицы, далее заходим в меню «Вставка» и нажимаем кнопку «Сводная таблица»

• вы можете сразу нажать кнопку «ОК», и сводная таблица выведется в отдельный лист.
• а можете настроить параметры вывода данных сводной таблицы:

1. Диапазон с данными, которые будут выведены в сводную таблицу;
2. Куда вывести сводную (в новый лист или на существующий (если выберите на существующий, то необходимо будет указать ячейку, в которую вы хотите поместить сводную таблицу)).

Нажимаем «ОК», сводная таблица готова и выведена в новый лист. Назовем лист «Сводная».

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

Теперь, зажимаем левой кнопкой мыши поле «Товар» — перетаскиваем его в «Название строк», поле «Продажи в руб.» — в «Значения» в сводной таблице. Таким образом мы получили сумму продаж по товарам за весь период:

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

Как сделать сводную таблицу, сгруппировать временной ряд? | Что важно знать о | Статьи

• Как сделать сводную таблицу;
• Как с помощью сводной таблицы сгруппировать временные ряды и оценить данные в динамике по годам, кварталам, месяцам, дням.
• Как рассчитать прогноз с помощью сводной таблицы и Forecast4AC PRO;

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

Пример реализации промежуточных итогов

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

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

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

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

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

2 Теперь, сохраняя выделение, откройте на панели инструментов программы поле «Данные». Затем в правой части окна опций найдите иконку «Структура» и нажмите на неё;

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

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

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

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

Рассмотрим практическое использование этой функции.

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

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

Определите, для каких данных нужно проводит итоговые вычисления. В нашем случае это столбец «Размер». Его содержимое нужно отсортировать. Сортировка проводится от большего элемента к меньшему. Выделяем столбец «Размер»;

Далее необходимо перейди во вкладку «Данные» на панели инструментов;

Найдите поле «Сортировка» и нажмите на него;

В появившемся окне выберите пункт «Сортировать в диапазоне указных значений» и нажмите на клавишу «ОК».

Теперь перейдем к реализации функции «Промежуточный итог»:

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

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

В писке «Добавить итоги по» вы выбираете тот столбец, в котором будут отображаться промежуточные итоги. Выбираем пункт «Размер» ;

После настройки всех параметров еще раз перепроверьте введённые значения и нажмите на ОК.

В результате, на листе отобразится таблица в сгруппированном по размерам виде. Теперь очень легко посмотреть все заказанные вещи размера Small , Extra Large и так далее.

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

Как видно из рисунка выше, все итоги выводятся между показанными группами в новой строчке.

Итог для одежды с размером Small – 5 штук, для одежды с размером Extra Large – 2 штуки. Под таблицей показывается и общее количество элементов.

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

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

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

Уровни групп

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

Нажмите на первый второй или третий уровень в панели управления группами. Выберите наиболее подходящий вариант представления информации:

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

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

Создание сводной таблицы Excel из нескольких листов

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

Использование уровней для организации ваших данных

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

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

Когда вы раскроете все, щелкнув цифру три, у вас появятся знаки «-» и «+» в столбце уровней. Используйте их, чтобы вручную свернуть и развернуть каждую группу самостоятельно.

Благодаря этой функции Excel позволяет скрыть от просмотра определенные части вашей электронной таблицы. Это полезно, когда есть некоторые данные, которые не важны для вашей работы в данный момент. Конечно, вы всегда можете вспомнить его, нажав знак «+».

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

Как сделать общий итог в excel?

1. Откройте электронную таблицу, в которую вы хотите добавить промежуточные итоги.
2. Сортируйте весь лист по столбцу, который содержит данные, которые вы хотите иметь промежуточные итоги.
3. Теперь нажмите вкладку «Данные» в верхнем меню.
4. В разделе «Outline» нажмите «Subtotal», чтобы открыть соответствующее меню.
5. В раскрывающемся меню «При каждом изменении» выберите столбец, содержащий данные, которые вы хотите использовать для промежуточных итогов.
6. Затем вы должны выбрать, какую операцию должны рассчитывать промежуточные итоги. В раскрывающемся меню «Использовать функцию» выберите один из доступных вариантов. Некоторые из наиболее распространенных операций — Sum, Count и Average.
7. В поле «Добавить промежуточные итоги» выберите столбец, в котором вы хотите отображать промежуточные итоги. Обычно это тот же столбец, для которого вы выполняете промежуточные итоги, как определено в шаге 5.
8. Вы можете оставить флажки «Заменить текущие промежуточные итоги» и «Сводка данных ниже».
9. Если вы удовлетворены своим выбором, нажмите «ОК», чтобы подтвердить изменения.

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

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

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

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

Важно! Обновить сводную табличку можно через вкладку «Данные». Для этого выбираем ячейку в таблице и находим на вкладке функцию «Обновить все».

16

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

Вычисление промежуточных итогов в Excel

1. Таблица составляется по практически любому объему данных.
2. Редактировать вид отчета можно через меню форматов – встроенная библиотека содержит множество цветовых тем таблиц.
3. Возможно объединение данных в более широкие группы, например несколько дат объединяются в кварталы.
4. По результатам отчета можно провести расчеты с помощью инструментов Excel, это не повлияет на источники данных.
5. Информация в сводной таблице может стать основой для составления графика или другого визуального отчета.

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

Написание формулы промежуточных итогов вручную

Есть еще один способ посчитать промежуточные итоги – с помощью специальной функции.

Примечание: Как и другие функции Эксель, использовать “ПРОМЕЖУТОЧНЫЕ.ИТОГИ” можно, не прибегая к помощи Мастера функций. Для этого в нужной ячейке вручную прописываем формулу, которая выглядит следующим образом:

= ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер обработки данных;координаты ячеек)

Далее жмем клавишу Enter и получаем желаемый результат в заданной ячейке.

В следующем примере мы воспользуемся командой Промежуточный итог, чтобы определить сколько заказано футболок каждого размера (S, M, L и XL). В результате рабочий лист Excel обретет структуру в виде групп по каждому размеру футболок, а затем будет подсчитано общее количество футболок в каждой группе.

Формула «Промежуточные итоги» в Excel: примеры

• Таблица оформлена в виде простого списка или базы данных.
• Первая строка – названия столбцов.
• В столбцах содержатся однотипные значения.
• В таблице нет пустых строк или столбцов.

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