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

При консолидации по расположению данных все источники имеют одинаковое расположение данных источников (имена категорий данных в выделяемые области-источники не включаются). Данные имеют одинаковую структуру, фиксированное расположение ячеек и могут быть консолидированы с определенной функцией обработки (среднее значение, максимальное, минимальное и т.п.) по их расположению. Для консолидации данных курсор устанавливается в область места назначения. Выполняется команда Данные\ Консолидация, выбирается вариант и задаются условия консолидации.

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

Товары	Показатели
Виды товаров	Показатель 1	Показатель 2
Товар 1	Консолидируемая область
Товар 2
Товар 3

 

Рисунок 2Области-источники при консолидации

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

При консолидации по категориям области-источники содержат одно-

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

строк и/или столбцов (имена включаются в выделенные области-источники).

Выполняется команда Данные\ Консолидация, выбирается вариант и задаются условия консолидации.

Пример 2 На рабочих листах представлена информация областей-источников в виде структуры на рис.3. Число строк и столбцов - переменное, состав показателей и виды товаров могут различаться или совпадать, при консолидации по категориям они собираются вместе. Цветом показана консолидируемая область источников.

Товары	Показатели		Товары	Показатели
Виды товаров	Показа-тель 1	Показа- тель 2		Виды товаров	Показа- тель 1	Показа- тель 2
Товар 1				Товар 1
Товар 2				Товар 2
				Товар 3

Рисунок 3 Области-источники при консолидации

данных по категориям

Условия консолидации задаются в диалоговом окне Консолидация (рис.4). В поле Функция выбирается функция консолидации данных. Для каждой области-источника строится ссылка, для чего курсор устанавливается в поле ссылки, затем переходят в область-источника для выделения блока ячеек и нажимается кнопка Добавить.

Ссылка может иметь любую из следующих форм:

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

 

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

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

столбца (рис. 4). Excel автоматически переносит эти имена в область назначения.

 

Рисунок 4 Диалоговое окно Консолидация

для задания условий консолидации

 

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

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

Примечание. Нельзя корректировать ссылки на области-источники (добавлять или удалять новые области-источники) при наличии флажка переключателя Создавать связи с исходными данными.

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

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

Контрольные вопросы

1 Что называется консолидацией?

2 Где располагается результат консолидации?

3 Что такое области-источники и где они могут располагаться?

4 Какие существуют варианты консолидации?

5 Из чего состоит область консолидации при объединении данных по

областям приёмников?

6 Из чего состоит область консолидации при объединении данных по областям приёмников?

Задание

Для таблицы своего варианта из лабораторной работы №9 (excel-9) "Списки в Excel. Сортировка и фильтрация данных" построить две таблицы:

· консолидированную по областям;

· консолидированную по категориям.

Предварительно подготовить таблицы для консолидации, т.е. для первой консолидации снять копию таблицы-оригинала из л.р.№9, а для второй – снять копию и дополнить её (копию) новым столбцом и новой строкой.

Пояснения к выполнению

Консолидация по областям

1 Скопировать таблицу своего варианта из л.р.№9 ("например, Показатели в 1 квартале") на новый лист, переименовать лист в "Консолидация" (дважды щелкнуть мышью по имени и ввести новое имя).

2 Сделать копию таблицы своего варианта из л.р.№9 на том же листе, изменить в ней данные. Эта таблица будет отражать, например, показатели во 2 квартале (рис. 5).

3 Выполнить консолидацию данных по расположению:

§ установить курсор в первую ячейку области, где будет располагаться консолидированная таблица, например в ячейку A10;

§ выполнить команду Данные\ Консолидация;

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

§ установить курсор в окне Ссылка и выделить блок ячеек А2:D7

(показатели в 1 квартале);

§ нажать кнопку Добавить, в окне Список диапазонов появится ссылка на выделенный диапазон;

§ установить курсор в окне Ссылка, удалить прежнюю запись и выделить блок ячеек F2:I7 (показатели во 2 квартале);

§ нажать кнопку Добавить, в окне Список диапазонов появится ссылка на выделенный диапазон;

§ нажать кнопку ОК и сравнить полученные результаты с рис. 5.

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

1 Добавить строку с товаром TV-тюнерс соответствующими числами в первую таблицу "Показатели в 1 квартале"(рис. 6).

2 Вставить новый столбец с именем % реализации и заполнить формулой Продано*100/ Получено. Чтобы выводилось 2 знака после запятой, в меню Формат\Ячейки, вкладка Число выбрать в поле Числовые форматы строку Числовой и установить Число десятичных знаков - 2;

3 Сделать консолидацию данных по категориям:

§ установить курсор в первую ячейку области, где будет располагаться консолидиpoванная таблица, например в ячейку A11;

§ выполнить команду Данные\ Консолидация;

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

§ установить курсор в окне Ссылка и выделить блок ячеек A2:Е8 (показатели в 1 квартале);

§ нажать кнопку Добавить, в окне Список диапазонов появится ссылка на выделенный диапазон;

§ установить курсор в окне Ссылка, удалить предыдущую запись и выделить блок ячеек G2:J7 (показатели во 2 квартале);

§ нажать кнопку Добавить, в окне Список диапазонов появится ссылка на выделенный диапазон;

§ нажать кнопку < ОК > и сравнить результаты с рис. 6.

 

Рисунок 5Пример консолидации данных по расположению

Рисунок 6 Пример консолидации данных по категориям

 

Содержание отчёта

1 Название работы (в скобках – имя файла описания лаб. работы)

2 Цель работы

3 Содержание работы

4 Вариант задания – исходные и консолидированные таблицы – 6 шт.

5 Письменные ответы на контрольные вопросы

6 Выводы по работе