Перейти к содержимому

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), вам не удастся.

Скачать статью в формате Word, примеры в формате Excel2007 или Excel2003

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

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

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

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

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

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

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

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

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

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

Для сводной таблицы отключите общие итоги:

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

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

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

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

Разместите вторую сводную рядом с первой:

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

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


[1] Как вызвать мастера сводных таблиц в Excel2007 (где он в явном виде не представлен) см. здесь.

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

  1. Виктория

    Здорово, спасибо большое!
    Так долго искала как такое реализовать.

  2. Большое спасибо за информацию, она помогла разрешить многие вопросы. Я считаю- это гениально ))

  3. пример не работает в Excel 2003, даже не испробовать

  4. Респект. Очень полезно. Посмотрю что на вашем сайте еще интересного есть 🙂

  5. Кстати в 2010 кажется можно не использовать СМЕЩ, а просто именовать диапазон в виде $A:$G. Пустые строки он отсекает сам. Не знаю как с производительностью, но группировка например точно работает

  6. Здравствуйте!
    Не появилось ли новых методов решения данной задачи в новых версиях Excel ?

    С уважением, Lari.

  7. Lari, мне не известны новые стандартные возможности Excel, позволяющие это сделать.

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

  9. Артемий

    Добрый день.
    Уважаемый Сергей Викторович, подскажите пжл — возможно ли в подобной сводной таблице добавить дополнительный столбец «итогов по строке», в котором автоматически рассчитывалась бы сумма «нарастающим итогом»?

  10. Артемий, нарастающий итог — это стандартная опция сводных таблиц. Встаньте на любое поле в области значений, кликните правой кнопкой мыши, выберите Параметры полей значений. В открывшемся окне перейдите на вкладку Дополнительные вычисления, в поле Дополнительные вычисления выберите С нарастающим итогом. Выберите поле, по которому вычислять нарастающий итог. Подробнее см. Вычисления в сводной таблице (в области значений) в Excel 2013.

  11. Сегодня столкнулся с такой задачей, но решил ее гораздо проще. При создании сводной таблицы первую строку листа оставляем пустой. После того, как сводная будет настроена до конца — в первой строек дублируем названия заголовков сводки.

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

  12. Спасибо! Тоже пришла к такому выводу, но опробовала по Вашему комменту.

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

Ваш адрес email не будет опубликован.