Создание сводной таблицы на основе данных, находящихся в нескольких диапазонах консолидации

Можно получать сводные таблицы на основе данных, находящихся в нескольких диапазонах консолидации. Для этого достаточно в первом окне Мастера сводных таблиц (рис.14) выделить соответствующую опцию.

 

Рис.14.Вид окна Шаг1 из 4 Мастера сводных таблиц

 

Поскольку консолидированные данные как правило бывают получены на базе исходных данных, имеющих разную структуру, то и структура области СТРАНИЦА сводной таблицы в этом случае несколько меняется.

Для отображения каждого диапазона исходных данных в консолидации используются дополнительные поля страниц. Элементы полей страниц представляют один или несколько исходных диапазонов. Например, если имеется консолидация бюджетных данных по нескольким отделам («Маркетинг», «Сбыт» и «Производство»), в поле страницы могут находиться элементы, представляющие данные по каждому отделу или их комбинации.

Выбрать тип поля страницы можно в диалоговом окне Мастера сводных таблиц - шаг 2а из 4 (рис. 15.).

Если необходимо отобразить одну страницу для каждого исходного диапазона или страницу, объединяющую все диапазоны исходных данных, установите переключатель в положение Создать одно поле страницы в диалоговом окне Мастер сводных таблиц - шаг 2a из 4.

Если необходимо создать несколько (можно создать до четырех) полей страниц и назначить каждому элементу исходного диапазона имя, а также создать структуру, не имеющую полей страниц, то установите переключатель в положение Создать поля страницы в диалоговом окне Мастер сводных таблиц - шаг 2a из 4. Этот переключатель используется для сравнения частей исходных данных или отображения всех данных.

 

Рис.15.Вид окна Шаг 2а из 4 Мастера сводных таблиц

Пример 11.

Проанализировать данные о продаже различных видов продукции филиалами компании за 3 месяца 1995 года.

Для этого выполните следующие действия:

1. Запустите Мастер сводных таблиц.

2. Установите переключатель на шаге 1 в положение В нескольких диапазонах консолидации(рис.14)

3. На шаге 2а установите переключатель в положение Создать одно поле страницы(рис.15)

4.Укажите диапазоны для консолидации на шаге 2б (рис.16)

 

 

5. На третьем шаге Мастера сводных таблиц нажмите кнопку Далее, чтобы подтвердить положение установленных по умолчанию полей (рис.17)

 

Рис.17 Вид окна Шаг 3 Мастера сводных таблиц

 

 

6. Укажите место, куда будет помещена сводная таблица, на четвертом шаге.

Результат построения сводной таблицы представлен на рис.18

 

Рис.18 Сводная таблица примера 11

 

Если щелкнуть по раскрывающемуся списку Страница1, то появятся общие имена: Объект1,Объект2,Объект3 ,которые Excel по умолчанию присвоил каждому из диапазонов консолидации. Для того, чтобы задать имена диапазонов консолидации необходимо:

1. На шаге 2а (рис.15) установить переключатель в положение Создать поля страницы.

2. На шаге 2б необходимо указать количество страничных полей сводной таблицы и для каждого диапазона консолидации указать его имя или метку (редактируется Первое поле). (рис.19)

 

Рис.19. Создание страничных полей.

Остальные шаги в алгоритме построения сводной таблицы рассмотрены в Примере 11.

 

Связывание объектов

 

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

Рабочая книга, содержащая исходные данные, называется исходной книгой или книгой-источником, книга, получающая данные называется книгой-получателем или целевой рабочей книгой.

Независимо от того, открыта ли исходная рабочая книга или закрыта, целевая рабочая книга получит необходимые данные через связь. Если во время открытия целевой рабочей книги исходная рабочая книга открыта, то связи обновляются автоматически. Если исходная рабочая книга закрыта, то при открытии целевой пользователю будет задан вопрос, хочет ли он работать с данными, оставшимися после последнего сохранения или их следует обновить из исходной рабочей книги (см.п."Открытие связанных рабочих книг").

Можно установить связь исходных данных

1) с ячейками и диапазонами ячеек

2) с изображениями ячеек рабочего листа.

Связь с данными позволяет избежать проблем, присущих работе с чересчур большими, громоздкими рабочими книгами. Благодаря возможности связывать данные друг с другом, можно создавать небольшие рабочие книги с небольшими рабочими листами, соответствующие определенным задачам пользователя. Затем эти книги можно связывать друг с другом, строя из них большие информационные системы. В приведенном ниже списке перечислены основные преимущества построения систем, составленных из небольших рабочих книг, данные которых связаны друг с другом:

- С помощью связей можно передавать от одной рабочей книги к другой любые данные, числа и текст, которые затем могут быть использованы в формулах

- Связанные данные могут быть отформатированы так же, как и любые другие данные в других ячейках рабочего листа. При работе требуется меньшее количество памяти, т. к. не все рабочие книги, входящие в информационную систему, должны обязательно быть открыты

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

- Небольшие рабочие книги пересчитываются намного быстрее

- Можно создавать отдельные компоненты, для работы с ними на разных компьютерах. Компоненты могут быть связаны друг с другом с помощью обобщающей электронной таблицы, тогда при обновлении этой таблицы результаты работы многих пользователей будут собраны в одном месте. Такие информационные системы обладают рядом преимуществ: много пользователей может работать одновременно на разных компьютерах, общая работа может быть выполнена быстрее, и, наконец, значительно меньше вероятность того, что неопытный оператор повредит всю информационную систему

- Систему, состоящую из отдельных компонентов, проще обслуживать и отлаживать

- Рабочие книги, входящие в состав системы, могут быть переделаны для использования в другой системе

Большое количество связей может замедлить процесс открытия целевой рабочей книги. Кроме того, размеры рабочей книги с большим количеством внешних связей могут быть очень большими, так как Excel хранит в рабочей книге последнюю копию использованных в работе внешних данных, чтобы иметь возможность работать с рабочей книгой не обновляя ее связей. Если исходная рабочая книга всегда открывается вместе с целевой, или если вы всегда обновляете связи при открытии, или же если вы просто хотите сэкономить дисковое пространство, отключите сохранение копии внешних данных вместе с рабочей книгой. Для этого выберите команду Сервис, Параметры , в появившемся диалоговом окне раскройте вкладку Вычисления, снимите флажок Сохранять значения внешних связей и нажмите кнопку ОК (рис.20).

 

	Рис.20.Вид окна Параметры команды Сервис

 

1.2.1 Связывание с ячейками и диапазонами ячеек рабочей книги

С точки зрения консолидации данных возможны следующие варианты связывания ячеек и диапазонов ячеек:

- ячейка с ячейкой;

- ячейка связывается с диапазоном ячеек;

- диапазон ячеек с диапазоном ячеек.

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

Связь ячейка-ячейка

Пример 12. На рис. 21 изображены рабочие книги, связанные с помощью внешней ссылки. Рабочая книга Квартал 1 является исходной для рабочей книги Годовые отчеты. В строке формул находится формула внешней ссылки, использованная в рабочей книге Годовые отчеты. Эта формула выглядит следующим образом:

 

Рис.21. Иллюстрация примера 12

 

 

='[Квартал l.xls]1 kb 1996'!$Е$2

и означает, что ячейка В2, в которой она помещена, связана с ячейкой Е2 рабочего листа 1кв1996 рабочей книги Квартал1. При изменении содержимого исходной ячейки, конечная ячейка также изменится.

В этой формуле Квартал1 — имя исходной рабочей книги, 1кв1996 — имя рабочего листа в этой книге, а $Е$2 — адрес ячейки, в которой хранятся связанные данные. Восклицательный знак отделяет адрес ячейки от имени рабочей книги и рабочего листа.