2020-06-12
190
Цель работы
Познакомиться с:
· функцией консолидации;
· методами консолидации.
В результате выполнения задания студент должен уметь:
· строить таблицу консолидации по исходным диапазонам данных.
Теоретическая часть
Данные, расположенные в различных областях одного рабочего листа, на различных рабочих листах или в различных рабочих книгах, могут быть сведены вместе путём их консолидации.
В Microsoft Excel существуют следующие способы консолидации данных.
По расположению, если данные исходных областей находятся в одном и том же месте и размещены в одном и том же порядке. Этот способ используется для консолидации данных нескольких листов, созданных на основе одного шаблона.
По категориям (именам), если данные исходных областей не упорядочены, но имеют одни и те же заголовки. Этот способ используется для консолидации данных листов, имеющих разную структуру, но одинаковые заголовки.
С помощью трехмерных ссылок, что является наиболее предпочтительным способом. При использовании трехмерных ссылок отсутствуют ограничения по расположению данных в исходных областях.
С помощью сводной таблицы. Этот способ сходен с консолидацией по категориям, но обеспечивает большую гибкость при реорганизации категорий.
Чтобы консолидировать данные, сначала необходимо определить область назначения для размещения результатов. Область назначения может быть выбрана на любом рабочем листе или в любой рабочей книге.
Задание исходных областей консолидируемых данных. Исходные области обрабатываемых данных задаются либо трехмерными формулами, либо в поле Ссылка диалогового окна Консолидация. Источники данных могут находиться на том же листе, что и таблица консолидации, на других листах той же книги, в других книгах.
При описании исходных областей нужно следовать следующим рекомендациям:
· для облегчения работы с исходными областями необходимо задать имя каждого диапазона и использовать в поле Ссылка только имена. Если исходные области и область назначения находятся на одном листе, нужно использовать имена или ссылки на диапазоны;
· если исходные области и область назначения находятся на разных листах, используется имя листа и имя или ссылка на диапазон;
· чтобы задать описание источника данных, не нажимая клавиш клавиатуры, можно указать поле Ссылка, а затем выделить исходную область. Чтобы задать исходную область в другой книге, необходимо нажать кнопку Обзор. Чтобы убрать диалоговое окно Консолидация на время выбора исходной области, нужно нажать кнопку свертывания диалогового окна.
Консолидация по расположению. При использовании этого способа данные во всех консолидируемых областях должны располагаться идентично. Для консолидации следует выполнить следующие действия:
1. На новом листе создать итоговую таблицу, структура которой соответствует сводимым таблицам. Установить курсор в верхнюю левую ячейку области, где будут располагаться значения консолидируемых данных.
2. Задать команду Данные - Консолидация.
3. В окне Консолидация в поле Функция указать нужную функцию.
4. Установив курсор в поле Ссылка, в каждой из сводимых таблиц поочередно с помощью мыши выделить диапазон подлежащих консолидации данных (без имен-категорий) и в окне Консолидация нажать кнопку Добавить.
5. Если результат консолидации должен изменяться при изменении исходных данных, в окне Консолидация следует включить параметр Создать связи с исходными данными. Для исходных данных, расположенных в других рабочих книгах, обновление данных надо делать, используя команду Связи в меню Правка. После добавления всех консолидированных областей нажать ОК.
Связи нельзя использовать, если исходная область и область назначения находятся на одном листе. После установки связей нельзя добавлять новые исходные области и изменять исходные области, уже участвующие в консолидации.
При консолидации данных по расположению заголовки категорий исходных областей не копируются автоматически в область назначения. Если в области назначения требуется разместить заголовки, скопируйте или введите их вручную.
Консолидация по категориям (именам). В данном случае необязательным является одинаковое расположение данных в сводимых областях (они могут располагаться даже на одном рабочем листе, но обязательно должны иметь одинаковые имена-категории сводимых диапазонов). Для выполнения консолидации по категориям необходимо:
1. Указать верхнюю левую ячейку конечной области консолидированных данных.
2. Выбрать команду Консолидация в меню Данные.
3. Из раскрывающегося списка Функция выбрать функцию, которую следует использовать для обработки данных.
4. Ввести исходную область консолидируемых данных в поле Ссылка. Нужно убедиться, что исходная область имеет заголовок.
5. Нажать кнопку Добавить.
