Excel сводная таблица со сводных
![](http://officeassist.ru/wp-content/uploads/15664/tmp-2005d614-9fb3-436b-a04c-6c42f49a1f05-360x261.jpg)
В моей практике не раз возникала задача анализа данных ранее собранных в сводную таблицу. Допустим, что анализируя продажи, вы воспользовались исходными данными, представленными в следующем формате (табл. 1):
На основе таких данных в Excel несложно сформировать сводную таблицу, что-то типа (табл. 2):
Если же вы хотите распределить клиентов по объему продаж, чтобы получить сводную таблицу типа (табл. 3):
то вы столкнетесь с трудностями, так как создать такую таблицу на основе исходных данных (таблица 1), вам не удастся.
Скачать статью в формате Word, примеры в формате Excel2007 или Excel2003
Если это разовая процедура, то вы сначала создадите табл. 2, а потом на её основе создадите табл. 3. Но как быть, если вы хотите поддерживать табл. 3 в актуальном состоянии при изменении исходных данных (табл.1)!?
Мне не известны стандартные методы в Excel, позволяющие это сделать. К сожалению, опция в мастере сводных таблиц[1] «создать таблицу на основе данных, находящихся… в другой сводной таблице», не подходит:
![св-св1](http://officeassist.ru/wp-content/uploads/15664/tmp-2005d614-9fb3-436b-a04c-6c42f49a1f05.jpg)
При использовании мастера новая сводная таблица «опирается» на те же исходные данные, что и первоначальная сводная таблица. Для удобства воспользуйтесь Excel-файлом с примером. Перейдите на лист «стандарт». На нем представлены детальные данные о продажах: номер и дата заказа, номенклатура, входящая в заказ, и количество товаров
![св-св2](http://officeassist.ru/wp-content/uploads/15664/tmp-26537908-354e-48a5-8ea4-592a3d968dfa.jpg)
Стандартным образом создайте сводную таблицу на основе этих данных, а затем запустите мастер сводных таблиц, выберите опцию «создать таблицу на основе данных, находящихся… в другой сводной таблице», нажмите «Далее»:
![св-св3](http://officeassist.ru/wp-content/uploads/15664/tmp-a609c54b-d6ad-4561-95aa-d3ecae26ac0e.jpg)
На шаге 2 выберите на основе, какой сводной таблицы вы создадите новую таблицу:
![св-св4](http://officeassist.ru/wp-content/uploads/15664/tmp-61e28b6f-72bc-4dad-90c7-0603e0c79225.jpg)
Видно, что вторая сводная «опирается» на те же данные, что и первая:
![св-св5](http://officeassist.ru/wp-content/uploads/15664/tmp-42218165-11d7-4720-bd06-a4b91c4196c1.jpg)
Можете поэкспериментировать, и убедиться, что набор данных второй сводной таблицы не зависит от вида первой сводной таблицы (то есть от того, какие поля и как мы выбрали в первой таблице), а зависит только от исходных данных (колонки А—D).
Фактически мы создали копию первой сводной таблицы. Так что стандартные методы Excel для решения нашей задачи не подходят. Применим маленькие хитрости.
Пример приведен в Excel-файле на листе «хитрость». Для начала создадим именованный динамический диапазон на основе исходных данных — «исх1» с использованием функции СМЕЩ (как это делать можно посмотреть здесь). Именованный диапазон избавит нас от проблем при добавлении исходных данных, и позволит актуализировать все сводные таблицы простым нажатием кнопки «Обновить»:
![св-св6](http://officeassist.ru/wp-content/uploads/15664/tmp-d79ff51b-126e-45c5-bcdf-8d93ea8304fe.jpg)
Создавая сводную таблицу, укажите, что исходные данные — это диапазон с именем «исх1»:
![св-св7](http://officeassist.ru/wp-content/uploads/15664/tmp-e4131f2c-565a-4b77-9e8b-45f9799009c4.jpg)
Для сводной таблицы отключите общие итоги:
![св-св8](http://officeassist.ru/wp-content/uploads/15664/tmp-39b18344-d8bd-4734-a592-fcdbb49fbac4.jpg)
Создайте именованный диапазон для сводной таблице, также с помощью функции СМЕЩ:
![св-св9](http://officeassist.ru/wp-content/uploads/15664/tmp-e0830bae-2220-4f0f-82b9-7290aaba272d.jpg)
Вот зачем мы отключили итоги — чтобы они «не лезли» в этот диапазон!
Запустите мастер сводных таблиц (из панели быстрого доступа) и выберите «Создать таблицу на основе данных, находящихся… в списке или базе данных MS Excel», нажмите «Далее»:
![св-св10](http://officeassist.ru/wp-content/uploads/15664/tmp-9f5e5fe7-c61b-4c69-acac-0d27c85a86f0.jpg)
На втором шаге, укажите диапазон, содержащий исходные данные для второй сводной таблицы — «св1», нажмите «Далее»:
![св-св11](http://officeassist.ru/wp-content/uploads/15664/tmp-bcb9abf8-ca70-4a86-96ba-f34223be4b4f.jpg)
Разместите вторую сводную рядом с первой:
![св-св12](http://officeassist.ru/wp-content/uploads/15664/tmp-fc5a479a-443b-41e2-aa4d-6440bc8963a5.jpg)
Сгруппируйте индивидуальные значения в диапазоны, постройте сводную диаграмму:
![св-св13](http://officeassist.ru/wp-content/uploads/15664/tmp-c0a05724-d8ce-4419-ab35-32e747b5f19a.jpg)
Поэкспериментируйте с исходными данными, добавляя / удаляя строки. Достаточно последовательно обновить первую и вторую сводные таблицы, и все внесенные изменения будут учтены.
[1] Как вызвать мастера сводных таблиц в Excel2007 (где он в явном виде не представлен) см. здесь.