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

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

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

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

Рисунок 1Области-источники при консолидации данных по расположению

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

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

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

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

Рисунок 2 Области-источники при консолидации данных по категориям

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

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

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

 

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

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

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

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

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

 

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

 

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

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

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

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

Задание 1

 

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

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

 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 

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

Задание 2

Подсчитать количество пропущенных часов за семестр. Если пропущено более 200 часов, то студент попадает под отчисление.

1. Создайте в новой книге на отдельных листах таблицы посещаемости по месяцам (сентябрь-декабрь):

2. Заполните таблицы на 4 листах и рассчитайте Пропусков всего.

3. Переименуйте листы как Посещаемость в «месяц».

4. На отдельном листе создайте такую же таблицу, только под названием Посещаемость за семестр (кол-во пропусков).

5. Переименуйте этот лист как Посещаемость за семестр.

6. Сохраните книгу в папку WORK как Сведения о посещаемости.

7. Для объединения данных из четырех предыдущих листов воспользуемся командой Консолидация. В данном упражнении мы воспользуемся консолидацией по расположению и без создания связей. При таком типе консолидации данные собираются из одинаково расположенных ячеек каждого из четырех исходных листов.

8. На листе Посещаемость за семестр необходимо выделить конечную область (область назначения), т.е. блок ячеек, в который будут помещены консолидированные данные. В нашем случае это диапазон, B4:G10.

9. Выберите Данные►Консолидация.

10. В появившемся диалоговом окне в поле Функция выберите Среднее[1] в поле с раскрывающимся списком. Оставьте флажки в секции Использовать метки неустановленными. Так как мы не собирались устанавливать связи с исходными листами, флажок Создавать связи с исходными данными также оставьте неустановленными.

11. В поле ссылка необходимо ввести ссылки на каждый исходный диапазон:

– установите курсор в поле Ссылка;

– перейдите на лист Посещаемость в сентябре, щелкнув по его ярлычку;

– выделите диапазон ячеек B4:G10;

– нажмите кнопку Добавить в диалоговом окне Консолидация. Эта ссылка появиться в Списке диапазонов;

– добавьте самостоятельно ссылки на листы посещаемости в октябре, ноябре, декабре.

12. Нажмите ОК.

13. В столбце Н определим студентов попадающих под отчисление:

– в ячейке Н2 введите На отчисление;

– в ячейке Н4 самостоятельно введите формулу для определения отчисляемых студентов (используется функция ЕСЛИ, для отчисляемых студентов должна выводиться надпись «на отчисление»)

14. Используя Автофильтр, отберите только отчисляемых студентов и скопируйте их в текстовый редактор WORD.

15. Сохраните документ под именем Список на отчисление.

 Задание 3

Имеются данные о деятельность ОАО «Эльдорадо» за каждый месяц. Необходимо подвести итоги за каждый месяц, квартал и за весь год. Построить диаграмму сравнения продаж по кварталам. Скопировать диаграмму и итоговую таблицу в текстовый редактор WORD.  

1. Создайте в новой книге на отдельных листах таблицу Продаж ОАО «Эльдорадо» по кварталам (каждый квартал на отдельном листе) по образцу: