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

Мастер сводных таблиц и диаграмм позволяет консолидировать данные  из  нескольких диапазонов 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 с: ил.

Похожие посты:

Вы можете оставить комментарий, или ссылку на Ваш сайт.

Оставить комментарий