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

Лабораторная работа

«Консолидация табличных данных»

 

Учебный материал

Понятие консолидации данных

 

Консолидация данных представляет собой способ объединения данных из нескольких источников в одном отчете.

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

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

• с помощью формул, где используются ссылки (одномерное связывание);

• по расположению данных для одинаково организованных областей источников (консолидация по расположению);

• по категориям для различающихся по своей структуре области данных (консолидация по категориям);

• с помощью сводной таблицы;

• консолидация внешних данных.

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

 

Консолидация по формуле

Если требуется применять формулы со ссылками на ячейки или объемными^[1] ссылками на другие листы из-за отсутствия постоянных позиций или категорий, на которые можно было бы опираться, то используется консолидация по формуле.

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

<имя рабочего листа>!<адрес ячейки>

Например: =A1+Лист1!A2

В этом примере происходит суммирование ячейки A1 текущего рабочего листа и ячейки A2 Листа1.

Если при связывании используются листы разных рабочих книг, то в этом случае указывается полный путь к файлу: имя диска, имена папок, имя файла, имя листа – все это в апострофах, адрес ячейки, при этом имя рабочей книги указывается в квадратных скобках:

<‘имя диска:\ папка1\папка2\...\[имя файла]имя рабочего листа’>!<адрес ячейки>

Например: =A1+’D:\ EXCEL\[Primer.xlsx]Лист 2’!A5

В этом примере происходит суммирование ячейки A1 текущего рабочего листа и ячейки A5 Листа2 рабочей книги Primer.xlsx, расположенной в папке EXCEL диска D.

Если диск и папка текущие, то имеем: =A1+[Primer.xlsx]Лист 2!A5

 

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

 

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

• чтобы первая строка каждого столбца содержала название, остальные строки – однотипные данные; пустые строки или столбцы в списке отсутствуют;

• разместить каждый диапазон на отдельном листе. Не помещайте диапазоны на лист, на котором должна выполняться консолидация;

• убедиться, что макеты всех диапазонов совпадают.