Вопрос 28. консолидация данных.

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

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

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

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

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

— по физическому расположению (п.1.2);

— по заголовкам строк и столбцов (п.1.3);

— с использованием ссылок (п.1.4);

— ручная консолидация (п.1.5).

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

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

Процесс консолидации предполагает обязательное указание

— диапазона назначения;

— источников данных;

— способа консолидации;

— наличия связи между объектами консолидации;

— типа (функции) консолидации.

Последние четыре действия выполняются с помощью диалогового окна Консолидация, которое вызывается по команде Данные – Консолидация

Диапазон назначения указывается с помощью выделения на рабочем:

— отдельной ячейки;

— строки ячеек;

— столбца ячеек;

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

Заполнение его осуществляется по правилам, представленным в табл.1.

Заполнение диапазона назначения Выделение Результат

Ячейка

Заполняются все ячейки, необходимые для всех консолидируемых категорий (элементов) исходных данных

Строка ячеек

Заполняются ячейки вниз от выделения. Ширина области назначения в точности совпадает с шириной выделения

Столбец ячеек

Заполняются ячейки вправо от выделения. Высота области назначения в точности совпадает с высотой выделения

Диапазон ячеек

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

Источники данных представляют собой диапазоны ячеек. Число диапазонов может достигать 255. Источники данных не обязаны быть открыты во время консолидации. Исходные области обрабатываемых данных задаются либо трехмерными формулами непосредственно в ячейках диапазона назначения, либо в поле Ссылка диалогового окна Консолидация (рис.1). Источники данных могут находиться на том же листе, что и таблица консолидации, на других листах той же книги, в других книгах или в файлах Lotus 1-2-3.

Для указания источников данных могут быть использованы два способа:

— выделение исходного диапазона с помощью мыши;

— ввод ссылки на диапазон с клавиатуры.

Выделение исходного диапазона с помощью мыши осуществляется стандартными приемами.

Консолидация данных по физическому расположению

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

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

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

Ручная консолидация рабочих листов

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

Статьи к прочтению:

Консолидация данных из разных источников с помощью надстройки Excel Power Query

Похожие статьи: