В моей практике не раз возникала задача анализа данных ранее собранных в сводную таблицу. Допустим, что анализируя продажи, вы воспользовались исходными данными, представленными в следующем формате (табл. 1):
На основе таких данных в Excel несложно сформировать сводную таблицу, что-то типа (табл. 2):
Если же вы хотите распределить клиентов по объему продаж, чтобы получить сводную таблицу типа (табл. 3):
то вы столкнетесь с трудностями, так как создать такую таблицу на основе исходных данных (таблица 1), вам не удастся.
Скачать статью в формате Word, примеры в формате Excel2007 или Excel2003
Если это разовая процедура, то вы сначала создадите табл. 2, а потом на её основе создадите табл. 3. Но как быть, если вы хотите поддерживать табл. 3 в актуальном состоянии при изменении исходных данных (табл.1)!?
Мне не известны стандартные методы в Excel, позволяющие это сделать. К сожалению, опция в мастере сводных таблиц[1] «создать таблицу на основе данных, находящихся… в другой сводной таблице», не подходит:
При использовании мастера новая сводная таблица «опирается» на те же исходные данные, что и первоначальная сводная таблица. Для удобства воспользуйтесь Excel-файлом с примером. Перейдите на лист «стандарт». На нем представлены детальные данные о продажах: номер и дата заказа, номенклатура, входящая в заказ, и количество товаров
Стандартным образом создайте сводную таблицу на основе этих данных, а затем запустите мастер сводных таблиц, выберите опцию «создать таблицу на основе данных, находящихся… в другой сводной таблице», нажмите «Далее»:
На шаге 2 выберите на основе, какой сводной таблицы вы создадите новую таблицу:
Видно, что вторая сводная «опирается» на те же данные, что и первая:
Можете поэкспериментировать, и убедиться, что набор данных второй сводной таблицы не зависит от вида первой сводной таблицы (то есть от того, какие поля и как мы выбрали в первой таблице), а зависит только от исходных данных (колонки А—D).
Фактически мы создали копию первой сводной таблицы. Так что стандартные методы Excel для решения нашей задачи не подходят. Применим маленькие хитрости.
Пример приведен в Excel-файле на листе «хитрость». Для начала создадим именованный динамический диапазон на основе исходных данных — «исх1» с использованием функции СМЕЩ (как это делать можно посмотреть здесь). Именованный диапазон избавит нас от проблем при добавлении исходных данных, и позволит актуализировать все сводные таблицы простым нажатием кнопки «Обновить»:
Создавая сводную таблицу, укажите, что исходные данные — это диапазон с именем «исх1»:
Для сводной таблицы отключите общие итоги:
Создайте именованный диапазон для сводной таблице, также с помощью функции СМЕЩ:
Вот зачем мы отключили итоги — чтобы они «не лезли» в этот диапазон!
Запустите мастер сводных таблиц (из панели быстрого доступа) и выберите «Создать таблицу на основе данных, находящихся… в списке или базе данных MS Excel», нажмите «Далее»:
На втором шаге, укажите диапазон, содержащий исходные данные для второй сводной таблицы — «св1», нажмите «Далее»:
Разместите вторую сводную рядом с первой:
Сгруппируйте индивидуальные значения в диапазоны, постройте сводную диаграмму:
Поэкспериментируйте с исходными данными, добавляя / удаляя строки. Достаточно последовательно обновить первую и вторую сводные таблицы, и все внесенные изменения будут учтены.
[1] Как вызвать мастера сводных таблиц в Excel2007 (где он в явном виде не представлен) см. здесь.
