Консолидация данных

МЕТОДИЧЕСКИЕ УКАЗАНИЯ

Общие сведения

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

Консолидация данных (рис. 8.1 рис 8.1) выполняется в соответствии с выбранной функцией обработки. Результат консолидации находятся в области- назначения. Таблица консолидации создается путем применения функции обработки к исходным значениям. Области-источники могут находиться на различных листах или рабочих книгах их может быть до 2⁸= 256.

В Excel возможны следующие варианты консолидации данных:

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

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

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

). Консолидируемая область выделена цветом.

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

по расположению

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

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

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

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

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

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

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

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

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

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

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

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

ЗАДАНИЕ

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

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

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

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

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

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

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

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

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

КОНТРОЛЬНЫЕ ВОПРОСЫ