В моей практике не раз возникала задача анализа данных ранее собранных в сводную таблицу. Допустим, что анализируя продажи, вы воспользовались исходными данными, представленными в следующем формате (табл. 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 (где он в явном виде не представлен) см. здесь.
Здорово, спасибо большое!
Так долго искала как такое реализовать.
Большое спасибо за информацию, она помогла разрешить многие вопросы. Я считаю- это гениально ))
пример не работает в Excel 2003, даже не испробовать
Добавил файл с примером в формате Excel2003
Респект. Очень полезно. Посмотрю что на вашем сайте еще интересного есть 🙂
Кстати в 2010 кажется можно не использовать СМЕЩ, а просто именовать диапазон в виде $A:$G. Пустые строки он отсекает сам. Не знаю как с производительностью, но группировка например точно работает
Здравствуйте!
Не появилось ли новых методов решения данной задачи в новых версиях Excel ?
С уважением, Lari.
Lari, мне не известны новые стандартные возможности Excel, позволяющие это сделать.
Это отличнейшее решение для тех, кто детализирует информацию с нескольких отчетов, автору огромная благодарность
Добрый день.
Уважаемый Сергей Викторович, подскажите пжл — возможно ли в подобной сводной таблице добавить дополнительный столбец «итогов по строке», в котором автоматически рассчитывалась бы сумма «нарастающим итогом»?
Артемий, нарастающий итог — это стандартная опция сводных таблиц. Встаньте на любое поле в области значений, кликните правой кнопкой мыши, выберите Параметры полей значений. В открывшемся окне перейдите на вкладку Дополнительные вычисления, в поле Дополнительные вычисления выберите С нарастающим итогом. Выберите поле, по которому вычислять нарастающий итог. Подробнее см. Вычисления в сводной таблице (в области значений) в Excel 2013.
привет из 2020-го!
Спасибо за наводку
Сегодня столкнулся с такой задачей, но решил ее гораздо проще. При создании сводной таблицы первую строку листа оставляем пустой. После того, как сводная будет настроена до конца — в первой строек дублируем названия заголовков сводки.
Далее — выделяем диапазон ячеек, в которых находится сводная вместе (!) с продублированными заголовками. И вуаля — сводная по сводной готова. Осталось только в любом из столбцов новой сводки поставить фильтр на то, чтобы убрать из диапазона значений строку с заголовками старой сводки.
Спасибо! Тоже пришла к такому выводу, но опробовала по Вашему комменту.