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

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

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

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

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

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

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

Как рассчитать процентное изменение с помощью сводных таблиц в Excel.

После добавления знака = при написании формул можно увидеть доступные варианты. Ознакомьтесь с описанием от разработчиков, если пока не знаете, как производить похожие расчеты в Microsoft Excel.

Excel. Сводная таблица на основе другой сводной

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

Клиент	Дата отгрузки	Сумма отгрузки
ООО «Ромашка»	21 декабря 2010	12 039,58 руб.
…	…	…
ООО «Лютик»	22 декабря 2010	291 574,23 руб.

На основе таких данных в Excel несложно сформировать сводную таблицу, что-то типа (табл. 2):

Клиент	Сумма отгрузок за период
ООО «Ромашка»	1 156 421,97 руб.
…	…
ООО «Лютик»	2 586 924,36 руб.

Если же вы хотите распределить клиентов по объему продаж, чтобы получить сводную таблицу типа (табл. 3):

Диапазон объема продаж	Число клиентов в диапазоне
0 – 499 999,99 руб.	254
…	…
2 000 000 – 2 499 999,99 руб.	21

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

Если это разовая процедура, то вы сначала создадите табл. 2, а потом на её основе создадите табл. 3. Но как быть, если вы хотите поддерживать табл. 3 в актуальном состоянии при изменении исходных данных (табл.1)!?

Мне не известны стандартные методы в Excel, позволяющие это сделать. К сожалению, опция в мастере сводных таблиц[1] «создать таблицу на основе данных, находящихся… в другой сводной таблице», не подходит:

При использовании мастера новая сводная таблица «опирается» на те же исходные данные, что и первоначальная сводная таблица. Для удобства воспользуйтесь Excel-файлом с примером. Перейдите на лист «стандарт». На нем представлены детальные данные о продажах: номер и дата заказа, номенклатура, входящая в заказ, и количество товаров

Стандартным образом создайте сводную таблицу на основе этих данных, а затем запустите мастер сводных таблиц, выберите опцию «создать таблицу на основе данных, находящихся… в другой сводной таблице», нажмите «Далее»:

На шаге 2 выберите на основе, какой сводной таблицы вы создадите новую таблицу:

Видно, что вторая сводная «опирается» на те же данные, что и первая:

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

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

Пример приведен в Excel-файле на листе «хитрость». Для начала создадим именованный динамический диапазон на основе исходных данных – «исх1» с использованием функции СМЕЩ (как это делать можно посмотреть здесь). Именованный диапазон избавит нас от проблем при добавлении исходных данных, и позволит актуализировать все сводные таблицы простым нажатием кнопки «Обновить»:

Создавая сводную таблицу, укажите, что исходные данные – это диапазон с именем «исх1»:

Создайте именованный диапазон для сводной таблице, также с помощью функции СМЕЩ:

Вот зачем мы отключили итоги – чтобы они «не лезли» в этот диапазон!

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

На втором шаге, укажите диапазон, содержащий исходные данные для второй сводной таблицы – «св1», нажмите «Далее»:

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

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

[1] Как вызвать мастера сводных таблиц в Excel2007 (где он в явном виде не представлен) см. здесь . Чтобы сделать все это, мы сначала отформатируем наш диапазон значений в виде таблицы в Excel, а затем мы собираемся создать сводную таблицу, чтобы сделать и отобразить наши вычисления процентного изменения.

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

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

Клиент	Сумма отгрузок за период
ООО «Ромашка»	1 156 421,97 руб.
…	…
ООО «Лютик»	2 586 924,36 руб.