Имя файла] Имя листа! Ссылка,например, [5__Consol.xls] Ceмecтp1! $B$3:$F$7
Лабораторная работа № 5.
Консолидация данных. Анализ решений
Для успешного выполнения этой и последующих лабораторных работ необходимо в диалоговом окне Надстройки,вызываемом по команде Сервис|Надстройки,установить все флажки.
Цель работы.Изучить методы консолидации данных и основные приемы анализа решений.
Задание 1.Изучите выполнение консолидации данных.
Методические указания.
Команда Данные|Консолидацияпредназначена для объединения данных из различных областей-источников и вывода результата в область назначения. Можно объединять до 255 областей-источников, размещенных на разных р/л и в разных р/к. Различают консолидацию по расположению иконсолидацию по категории.
При консолидации по расположениюячейки в каждой исходной области должны быть одинаково расположены. Этот способ простой, но требует одинаковой организации областей-источников. Реализация способа осуществляется по следующему алгоритму:
1.В новой р/к четырем р/л присвоить имена Семестр1 — Семестр4.Пятому р/л присвоить имя СреднЭкзам.
2.На всех пяти листах создать одинаково расположенные одинаковые таблицы, подобные показанной ниже.
| А | В | С | D | E | F | ||
| Студент | Экзамен | Экзамен | Экзамен | Экзамен | Среднее | ||
| Иванов | |||||||
| Петров | |||||||
| Сидоров | |||||||
| Семенов | |||||||
| Васильев | |||||||
3.Заполнить таблицы листов Семестр 1 - Семестр4оценками. Поле Среднееи лист Средн_Экзамне заполнять.
4.На листах Семестр1 — Семестр4вычислить и заполнить столбец Среднее.
5.Активизировать итоговый лист СреднЭкзами выделить область назначения (в приведенном образце B3:F7).
6.Выполнить команду Данные|Консолидация.
7.Вдиалоговом окне Консолидацияв списке Функциявыбрать Среднее.
8.Флажок Создавать связи с исходными даннымии флажки в поле Использовать в качестве имен— сбросить.
9.В поле Ссылкавнести поочередно интервалы исходных данных из р/л Семестр1 - Семестр4(без шапки и левого столбца). После ввода каждой ссылки нажимать кнопку Добавить.
10.После ввода последней ссылки щелкнуть ОК.
11.Если при вводе интервала р/к с исходными данными закрыта, то с помощью кнопки Обзорее нужно найти, открыть и ввести в поле Ссылкаимена нужного файла, листа, интервала ячеек (или набрать ссылку на интервал ячеек) по следующей форме:
Имя файла] Имя листа! Ссылка,например, [5__Consol.xls] Ceмecтp1! $B$3:$F$7.
При консолидации по категорииячейки в каждой исходной области могут быть расположены неодинаково, а исходные области могут быть разного размера. Реализация способа осуществляется по нижеприведенному алгоритму:
1. На листах Семестр 1 - Семестр4и СреднЭкзамсоздать новые таблицы, ниже использованных при консолидации по расположению. На листе Семестр!создать таблицу
| Студент | Экзамен 1 | Экзамен 2 | Экзамен 3 | Экзамен 4 | Среднее |
| Ковалев | |||||
| Соловьев |
На листе Семестр2- таблицу
| Студент | Экзамен 1 | Экзамен 2 | Экзамен 3 | Экзамен 4 | Среднее |
| Чернов | |||||
| Попов | |||||
| Романов |
На листе СеместрЗ- таблицу
| Студент | Экзамен 1 | Экзамен 2 | Экзамен 3 | Экзамен 4 | Среднее |
| Королев |
На листе Семестр4— таблицу
| Студент | Экзамен 1 | Экзамен 2 | Экзамен 3 | Экзамен 4 | Среднее |
| Воронов | |||||
| Николаев | |||||
| Киселев | |||||
| Уткин |
На листе Средн_Экзамсоздать шапку таблицы
| Студент |Экзамен 1 | Экзамен 2 | Экзамен 3 | Экзамен 4 | Среднее | |
| область назнач | |||||
2.Заполнить таблицы листов Семестр1 — Семестр4оценками. Поле Среднееи лист СреднЭкзамне заполнять.
3.На листах Семестр 1 — Семестр4в таблицах вычислить и заполнить столбец Среднее.
4.Активизировать итоговый лист СреднЭкзами выделить в нем левый верхний угол области назначения (отмечен на рисунке).
5.Выполнить команду Данные|Консолидация.
6.В диалоговом окне Консолидацияв списке Функциявыбрать Среднее.