Использование сводной таблицы для консолидации диапазонов

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

Консолидация диапазонов при помощи сводной таблицы является альтернативой команде Консолидация (Consolidation) меню Данные (Data), особенно в случаях, когда объединяемые данные находятся в разных книгах или рабочие листы одной книги структурированы неидентично. (Команда Консолидация рассматривается в разделе

«Объединение данных с разных листов» главы 9.) Например, на рис. 30.38 показана книга, аналогичная той, которую мы привлекли для иллюстрации применения команды Консолидация (см. рис. 9.39). На двенадцати листах этой книги хранятся данные о работе продавцов на протяжении года.

Рис. 30.38. Для консолидации 12 листов этой книги используется команда Сводная таблица

Повторим наши действия. Для создания консолидированной сводной таблицы выполните следующее:

1.   Выберите в меню Данные (Data) команду Сводная таблица (PivotTable And PivotChart Report).

2.   В первом диалоговом окне Мастера сводных таблиц и диаграмм установите переключатель в положение В нескольких диапазонах консолидации (Multiple Consolidation Ranges) и нажмите кнопку Далее.

3.   Когда откроется диалоговое окно Мастер сводных таблиц и диаграмм шаг 2а из 3, оставьте установленным переключатель Создать одно поле страницы (Create A Single Page Field For Me). Нажмите кнопку Далее, и Excel выведет на экран диалоговое окно, показанное на рис. 30.39.

Рис. 30.39. В этом окне мастера укажите все диапазоны данных,

которые вы хотите консолидировать

4.   Укажите первый диапазон исходных данных и щелкните на кнопке Добавить (Add). В нашем примере первый диапазон консолидации — это Янв!$А$3:$С$8, второй — Фев!$А$3:$С$9 и т. д.

5.   Повторите шаг 4 для каждого исходного диапазона. По окончании нажмите кнопку Далее. В последнем окне мастера укажите место для размещения сводной таблицы и нажмите кнопку Готово.

На рис. 30.40 представлена созданная мастером сводная таблица. Обратите внимание, что текущим элементом поля страницы является элемент Все (All). В этом случае сводная таблица отображает консолидированные результаты продажи музыкальных инструментов. Все другие элементы поля страницы фактически дублируют 12 исходных диапазонов сводной таблицы. Таким образом, если эти диапазоны находятся в разных книгах, их можно легко просмотреть прямо в сводной таблице без открытия исходных книг, выбирая элементы поля страницы.

Рис. 30.40. Элемент (Все) поля страницы отображает консолидированные результаты продажи музыкальных инструментов

В рассмотренном примере мы предпочли вариант Создать одно поле страницы в диалоговом окне Мастер сводных таблиц и диаграмм шаг 2а из 3. Разберем теперь пример с установкой переключателя в положение Создать поля страницы (I Will Create The Page Fields).

На рис. 30.41 показана книга, где каждый из восьми листов содержит информацию о продажах (количество проданных книг и выручка) для конкретного года и квартала,

представленные по номерам в каталоге. В сводную таблицу мы хотим включить два поля страниц: Год и Квартал.

Рис. 30.41. Каждый лист в этой книге содержит квартальные данные о продаже книг

Чтобы создать такую сводную таблицу, выполните следующие действия:

1.   Выберите в меню Данные команду Сводная таблица. В первом диалоговом окне Мастера сводных таблиц и диаграмм установите переключатель в положение В нескольких диапазонах консолидации и нажмите кнопку Далее.

2.   Когда откроется диалоговое окно Мастер сводных таблиц и диаграмм шаг 2а из 3, установите переключатель в положение Создать поля страницы и нажмите кнопку Далее. Excel выведет на экран диалоговое окно Мастер сводных таблиц и диаграмм

шаг 26 из 3 (рис. 30.42). Поскольку установлен переключатель Создать поля страницы, это диалоговое окно оказывается более сложным, чем его вариант,

представленный ранее на рис. 30.39.

3.   В этом окне укажите первый диапазон консолидации и нажмите кнопку Добавить.

4.   Повторите шаг 3 для каждого исходного диапазона.

5.   После задания всех исходных диапазонов установите горизонтально расположенный переключатель в положение 2, поскольку нам нужно создать два поля страниц. Всего же мастер позволяет создать до четырех таких полей. На рис. 30.43 изображено то же окно, Мастер сводных таблиц и диаграмм -шаг 26 из 3, после задания источников данных.

6.   Выделите первый диапазон в списке Список диапазонов (All Ranges). Затем введите значение 2002 в Первое поле (Field One) и Кв1 — во Второе поле (Field Two).

Рис. 30.42. При создании нескольких полей страниц диалоговое окно мастера на шаге

26 требует задания некоторой дополнительной информации

Рис. 30.43. В диалоговом окне мастера на шаге 26 после указания источников данных и количества полей страниц задайте имена элементов полей для каждого исходного диапазона

7.  Выделите второй диапазон в Списке диапазонов. Введите 2003 и Кв1 в Первое поле и Второе поле соответственно. Повторите эти действия для каждого диапазона, перечисленного в списке Список диапазонов. Затем нажмите кнопку Далее и укажите место для размещения сводной таблицы.

Созданная мастером сводная таблица показана на рис. 30.44. Выбирая элементы в раскрывающихся списках рядом с заголовками полей страниц, вы можете видеть различные «срезы» консолидированных данных.

Рис. 30.44. Эта консолидированная сводная таблица имеет два поля страниц

Источник: Эффективная работа: Microsoft Office Excel 2003 / М. Додж, К. Стинсон — СПб.: Питер, 2005. — 1088 с: ил.