Консолидация данных по категориям
При консолидации по категориямобласти-источники содержат однотипные данные, но они организованны в различных областях-источниках неодинаково. Для консолидации данных по категориям используются имена строк и/или столбцов (имена включаются в выделенные области-источники).
Пример 2 На рабочих листах представлена информация областей-источников в виде структуры на рис.12.4. Число строк и столбцов - переменное, состав показателей и виды товаров могут различаться или совпадать, при консолидации по категориям они собираются вместе. Цветом показана консолидируемая область источников.
| Товары | Показатели | Товары | Показатели | |||
| Виды товаров | Показа-тель 1 | Показа- тель 2 | Виды товаров | Показа- тель 1 | Показа- тель 2 | |
| Товар 1 | Товар 1 | |||||
| Товар 2 | Товар 2 | |||||
| Товар 3 |
Рисунок 12.4 Области-источники при консолидации
данных по категориям
При консолидации по категориям область имен входит в выделение, устанавливаются флажки подписи верхней строки или значения левого столбца (рис. 12.5).MSExcel автоматически переносит эти имена в область назначения.
Рисунок 12.5 Диалоговое окно Консолидация
для задания условий консолидации
При консолидации внешних данных в диалоговом окне Консолидация следует нажать кнопку 0бзор, в диалоговом окне Обзор выбрать файл, содержащий области-источники для добавления к списку, а затем добавить ссылку на ячейку или указать имя блока ячеек.
Переключатель Создавать связи с исходными данными включается при консолидации связи области назначения к областям-источникам. При изменениях в области назначения результаты консолидации автоматически обновляются.
Примечание. Нельзя корректировать ссылки на области-источники (добавлять или удалять новые области-источники) при наличии флажка переключателя Создавать связи с исходными данными.
В окне Список диапазонов для текущего рабочего листа итогов консолидации перечислены ссылки на все области-источники. Ссылки можно модифицировать: добавить новые области-источника, удалить существующие области-источника либо изменить его конфигурацию, если только до этого не был выбран переключатель Создавать связь с исходными данными.
Для одного листа итогов консолидации набор ссылок на области-источники постоянен, на нем можно построить несколько видов консолидации с помощью различных функций. Курсор переставляется в новое место, выполняется команда Данные\ Консолидация,выбирается другая функция для получения сводной информации.
Контрольные вопросы
1 Что называется консолидацией?
2 Где располагается результат консолидации?
3 Что такое области-источники и где они могут располагаться?
4 Какие существуют варианты консолидации?
5 Чем отличается консолидация по расположению от консолидации по категориям?
6 Из чего состоит область консолидации при объединении данных по областям приёмников?
Задание
Для таблицы своего варианта из лабораторной работы№9 (excel-9) "Списки в MSExcel. Сортировка и фильтрация данных" построить две таблицы:
· консолидированную по областям;
· консолидированную по категориям.
Предварительно подготовить таблицы для консолидации, т.е. для первой консолидации снять копию таблицы-оригинала из л.р.№9, а для второй – снять копию и дополнить её (копию) новым столбцом и новой строкой.
Пояснения к выполнению
Консолидация по областям
1 Скопировать таблицу своего варианта из л.р.№9 (например, "Показатели в 1 квартале") на новый лист, переименовать лист в "Консолидация" (дважды щелкнуть мышью по имени и ввести новое имя).
2 Сделать копию таблицы своего варианта из л.р.№9 на том же листе, изменить в ней данные. Эта таблица будет отражать, например, показатели во 2 квартале (рис. 12.6).
3 Выполнить консолидацию данных по расположению:
§ установить курсор в первую ячейку области, где будет располагаться консолидированная таблица, например в ячейку A10;
§ выполнить команду Данные\ Консолидация;
в диалоговом окне Консолидация выбрать из списка функцию Сумма и установить флажки подписи верхней строки и значения левого столбца;
§ установить курсор в окне Ссылка и выделить блок ячеек А2:D7
(показатели в 1 квартале);
§ нажать кнопку Добавить, в окне Список диапазонов появится ссылка на выделенный диапазон;
§ установить курсор в окне Ссылка, удалить прежнюю запись и выделить блок ячеек F2:I7 (показатели во 2 квартале);
§ нажать кнопку Добавить, в окне Список диапазонов появится ссылка на выделенный диапазон;
§ нажать кнопку ОК и сравнить полученные результаты с рис. 12.6.
Рисунок 12.6Пример консолидации данных по расположению
Консолидация по категориям
1 Добавить строку с товаром TV-тюнер с соответствующими числами в первую таблицу "Показатели в 1 квартале"(рис. 12.7).
2 Вставить новый столбец с именем % реализации и заполнить формулой Продано*100/ Получено. Чтобы выводилось 2 знака после запятой, в меню Главная\ Ячейки (Ctrl + 1), вкладка Число выбрать в поле Числовые форматы строку Числовой и установить Число десятичных знаков - 2;
3 Сделать консолидацию данных по категориям:
§ установить курсор в первую ячейку области, где будет располагаться консолидиpoванная таблица, например в ячейку A11;
§ выполнить команду Данные\ Консолидация;
§ в диалоговом окне Консолидация выбрать из списка функцию Сумма и установить флажки подписи верхней строки и значения левого столбцов;
§ установить курсор в окне Ссылка и выделить блок ячеек A2:Е8 (показатели в 1 квартале);
§ нажать кнопку Добавить, в окне Список диапазонов появится ссылка на выделенный диапазон;
§ установить курсор в окне Ссылка, удалить предыдущую запись и выделить блок ячеек G2:J7 (показатели во 2 квартале);
§ нажать кнопку Добавить, в окне Список диапазонов появится ссылка на выделенный диапазон;
§ нажать кнопку <ОК> и сравнить результаты с рис. 12.7.
Рисунок 12.7 Пример консолидации данных по категориям
Содержание отчёта
1 Название работы
2 Цель работы
3 Содержание работы
4 Вариант задания – исходные и консолидированные таблицы – 6 шт.
5 Письменные ответы на контрольные вопросы
6 Выводы по работе
[1] Если выделить оба столбца (А2:В12), то на графике появится ещё одна линия (прямая) - аргумент х будет построен как функция от номера строки.