Краткий обзор Microsoft Office 2003



Консолидация данных


Консолидация данных состоит в создании итоговой таблицы, позволяющей обобщить однородные данные. Например, можно произвести суммирование данных по товарам одних и тех же наименований, хранящихся на разных складах (рис. 18.14). При консолидации значения, приведенные в итоговой таблице, могут рассчитываться на основе исходных данных с использованием различных функций Excel.
Исходные данные (области) могут располагаться на одном или разных листах, в других открытых книгах. При консолидации можно создать связи, обеспечивающие автоматическое обновление данных в итоговой таблице (области назначения) при изменении данных в исходных областях.
Консолидацию данных можно произвести по расположению ячеек, содержащих исходные данные, по категориям, с помощью трехмерных ссылок, сводной таблицы и т.д; При этом во всех исходных диапазонах данные должны быть расположены в одинаковом порядке. Так, если мы рассматриваем количество товаров одного наименования на разных складах, то во всех отчетах, представленных разными складами, строки и столбцы таблиц отчетов должны быть расположены в одинаковом порядке.



Рис. 18.14 Пример таблицы исходных данных для консолидации

Консолидация данных по расположению


Диапазоны, данные которых консолидируются и помещаются в указанный конечный диапазон, называются исходными областями. Исходные области могут располагаться на любом листе или книге, на других открытых листах или книгах, а также на листах Lotus 1-2-3.
Если во всех ведомостях количество упаковок Рыбные продукты приводится в ячейке F3, то при консолидации в итоговой ведомости будут обобщены значения, хранящиеся в этой ячейке во всех ведомостях. Области консолидации



Рис. 18.15 Диалоговое окно Консолидация можно задать либо трехмерными формулами, либо в поле Ссылка (Reference) (рис. 18.15).-

В диалоговом окне Консолидация в раскрывающемся списке Функция (Function) выберите итоговую функцию, которую следует использовать для обработки данных, например, Сумма (Sum) , если будут суммироваться значения. Описание функций, приведенных в списке, дано в таблице 18.3.

Таблица 18.3. Итоговые функции, используемые при построении сводных таблиц и сводных диаграмм


Операция

Назначение

Сумма (Sum)

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

Количество (Count)

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

Среднее (Average)

Вычисление среднего числа по данным, хранящимся в исходных ячейках

Максимум (Мах)

Определение максимального числа по данным, хранящимся в исходных ячейках

Минимум (Min)

Определение минимального числа по данным, хранящимся вч исходных ячейках

Произведение (Product)

Вычисление произведение чисел, хранящихся в исходных ячейках

Количество чисел (Count Nums)

Количество записей или строк, содержащих числа

Смещенное отклонение (StdDev)

Смещенная оценка стандартного отклонения генеральной совокупности по выборке данных

Несмещенное отклонение (StdDevp)

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

Смещенная дисперсия (Var)

Смещенная оценка дисперсии генеральной совокупности по выборке данных

Несмещенная дисперсия (Varp)

Несмещенная оценка дисперсии генеральной совокупности по выборке данных


После установки курсора в поле Ссылка (Reference) введите ссылку на первый диапазон данных, который консолидируется в указанный конечный диапазон. Ссылку введите вручную или, если лист, содержащий новую исходную область, является текущим, выделите на нем исходную область. Если исходные области и область назначения находятся на разных листах, используйте имя листа и имя или ссылку на диапазон. Чтобы убрать диалоговое окно Консолидация (Consolidate) на время выбора исходной области, нажмите кнопку Свернуть диалоговое окно (Collapse dialog) в правой части поля. Повторное нажатие на эту кнопку восстанавливает окно.
Если исходные данные находятся в другой книге, которая в данный момент закрыта, нажмите кнопку Обзор (Browse) и выберите книгу. Путь к выбранной ссылке отобразится в поле Ссылка (Reference). Затем добавьте ссылку.
Нажмите кнопку Дoбaвumъ(Add ). Введенная ссылка отобразится в окне Список диапазонов (All references). Повторите эту операцию для всех консолидируемых исходных областей.
Чтобы автоматически обновлять итоговую таблицу при изменении источников данных, установите флажок Создавать связи с исходными данными (Create links to source data). Связи нельзя использовать, если исходная область и область назначения находятся на одном листе. После установки связей нельзя добавлять новые исходные области и изменять исходные области, уже входящие в консолидацию.
При консолидации данных по расположению заголовки категорий исходных областей не копируются автоматически в область назначения. Если в области назначения требуется разместить заголовки, скопируйте или введите их вручную. .

Консолидация данных по категориям


Выберите из раскрывающегося списка Функция (Function) функцию, которую следует использовать для обработки данных (рис. 18.15). Используемые итоговые функции приведены в таблице 18.3. Введите исходную область консолидируемых данных в поле Ссылка (Reference) . Убедитесь, что исходная область имеет заголовок.
Нажмите кнопку Добавить (Add) для добавления диапазона к списку исходных диапазонов консолидации. Повторите эту операцию для всех консолидируемых исходных областей. В наборе флажков Использовать в качестве имен (Use labels in) установите флажки, соответствующие расположению в исходной области заголовков: в верхней строке, в левом столбце или в верхней строке и в левом столбце одновременно.
Чтобы автоматически обновлять итоговую таблицу при изменении источников данных, установите флажок Создавать связи с исходными данными (Create links to source data). Связи нельзя использовать, если исходная область и область назначения находятся на одном листе. После установки связей нельзя добавлять новые исходные области и изменять исходные области, уже входящие в консолидацию.