Тип ( функция) консолидации.

Обобщение исходных данных может быть осуществлено с использованием следующих функций:

· Сумма

· Количество значений

· Среднее значение

· Смещенное отклонение

· Максимум

· Несмещенное отклонение

· Минимум

· Смещенная дисперсия

· Произведение

· Несмещенная дисперсия

· Количество чисел

Консолидация данных по физическому расположению

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

Консолидация по заголовкам строк и столбцов

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

Таким образом, достигается большая гибкость.

Консолидация данных с использованием ссылок

Для консолидации данных по ссылке необходимо:

1.Скопировать или задать надписи для данных консолидации.

2.Указать ячейку, в которую следует поместить данные консолидации. Выделить ячейку В2 на Лист1 книги.

 

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

='[Имя.xls]Лист1'!$B$2:$D$2

4. Повторить шаги 2 и 3 для каждой ячейки, в которой требуется вывести результаты консолидации данных.

Ручная консолидация рабочих листов

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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