РАБОТА 8. КОНСОЛИДАЦИЯ ДАННЫХ

КРАТКАЯ СПРАВКА

Другим способом получения итоговой информации является консолидация данных (рис. 3.51), которая выполняется в соответствии с выбранной функцией обработки.

Консолидация - агрегирование (объединение) данных, представленных в исходных областях-источниках.

Результат консолидации находится в области-назначения. Таблица консолидации создается путем применения функции обработки к исходным значениям. Области-источники могут находиться на различных листах или рабочих книгах. В консолидации может участвовать до 255 областей-источников.

Рис. 3.51. Представление о консолидации данных
Рис. 3.51. Представление о консолидации данных

Существуют следующие варианты консолидации данных:

  • с помощью формул, где используются ссылки;
  • по расположению данных для одинаково организованных областей-источников (фиксированное расположение);
  • по категориям для различающихся по своей структуре области данных;
  • с помощью сводной таблицы (см. работу 7);
  • консолидация внешних данных.

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

  • все области на одном листе - в ссылках указывается адрес блока ячеек, например D1:C8;
  • области на разных листах - в ссылках указывается название листа, диапазон, например лист1 !D1:лист2!С8;
  • области в разных книгах, на разных листах - в ссылках указывается название книги, название листа, диапазон, например [книга1] лист1!D1: [книга2] лист2!С8.

При консолидации по расположению данных все источники имеют одинаковое расположение данных источников (имена категорий данных в выделяемые области-источники не

179

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

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

Номер группы Предметы
Вид занятий Предмет 1 Предмет 2
Лекции Консолидируемая
область
Семинары
Лаб.работы

Рис. 3.52. Пример областей-источников при консолидации данных по расположению

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

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

Группа 101 Предметы
  Предмет 1 Предмет 2
Лекции    
Лаб.раб.    
Группа 101 Предметы
  Предмет 3 Предмет 2
Лекции    
Лаб. раб.    
Семинары    









Рис. 3.53. Пример областей-источников при консолидации данных по категориям

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

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

180

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

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

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

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

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

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

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

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

181

ЗАДАНИЕ

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

  1. Откройте книгу Spisok, вставьте два листа и переименуйте их.
  2. Создайте на листе Консол.распол. таблицу расчета заработной платы (за январь).
  3. Скопируйте созданную таблицу на тот же лист, измените в ней данные. Эта таблица будет отражать уровень заработной платы за февраль.
  4. Выполните консолидацию данных по расположению и сравните результат с рис. 3.55.
  5. Скопируйте обе таблицы (заработная плата за январь и за февраль) с листа Консол.распол. на лист Консол.категор и измените вторую таблицу в соответствии с рис. 3.56.
Рис. 3. 55. Пример консолидации данных по расположению
Рис. 3.55. Пример консолидации данных по расположению
Рис. 3. 56. Пример консолидации данных по категориям
Рис. 3.56. Пример консолидации данных по категориям
  1. Выполните консолидацию данных по категориям и сравните результат с рис. 3.56.

182

ТЕХНОЛОГИЯ РАБОТЫ

1. Откройте книгу Spisok командой Файл, Открыть, вставьте два новых листа и переименуйте их, присвоив им имена Консол.распол. и Консол.категор.

2. Создайте на листе Консол.распол. таблицу расчета заработной платы (за январь), которая приведена на рис. 3.55.

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

4. Выполните консолидацию данных по расположению (рис. 3.55):

  • установите курсор в первую ячейку области, где будет располагаться консолидированная таблица, например в ячейку А11;
  • выполните команду Данные, Консолидация;
  • в диалоговом окне ≪Консолидация≫ выберите из списка функцию Сумма и установите флажки подписи верхней строки, значения левого столбца;
  • установите курсор в окне ≪Ссылка≫, перейдите на лист с исходными таблицами и выделите блок ячеек A2:D7 (заработная плата за январь);
  • нажмите кнопку <Добавить>, в окне ≪Список диапазонов≫ появится ссылка на выделенный диапазон;
  • установите курсор в окне ≪Ссылка≫, перейдите на лист с исходными таблицами и выделите блок ячеек F2:17 (заработная плата за февраль);
  • нажмите кнопку <Добавить>, в окне ≪Список диапазонов≫ появится ссылка на выделенный диапазон;
  • нажмите кнопку <ОК> и сравните полученные результаты с рис. 3.55.

5. Скопируете обе таблицы (заработная плата за январь и за февраль) с листа Консол.распол. на лист Консол.категор и измените вторую таблицу (за февраль) так, как отображено на рис. 3.56, т.е.:

  • вставьте новый столбец Премия и заполните его данными;
  • добавьте строку с фамилией Дятлов и с соответствующими числами.

6. Сделайте консолидацию данных по категориям (см. рис. 3.56):

  • установите курсор в первую ячейку области, где будет располагаться консолидированная таблица, например в ячейку А11;
  • выполните команду Данные, Консолидация;
  • в диалоговом окне ≪Консолидация≫ выберите из списка функцию Сумма и установите флажки подписи верхней строки, значения левого столбца;
  • установите курсор в окне ≪Ссылка≫, перейдите на лист с исходными таблицами и выделите блок ячеек A2:D7 (заработная плата за январь);
  • нажмите кнопку <Добавить>, в окне ≪Список диапазонов≫ появится ссылка на выделенный диапазон;
  • установите курсор в окне ≪Ссылка≫, перейдите на лист с исходными таблицами и выделите блок ячеек F2:J8 (заработная плата за февраль);
  • нажмите кнопку <Добавить>, в окне ≪Список диапазонов≫ появится ссылка на выделенный диапазон;
  • нажмите кнопку <ОК> и сравните полученные результаты с рис. 3.56.

183

Lib4all.Ru © 2010.
Корпоративная почта для бизнеса Tendence.ru