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

Консолидация [лат. consolidatio, от con (cum) — вместе, заодно и solido — уплотняю, укрепляю, сращиваю], упрочение, укрепление чего-либо; объединение, сплочение отдельных лиц, групп, организаций для усиления борьбы за общие цели.

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

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

Рис. 1.

 

            Перед менеджером встают несколько вопросов (задач), которые можно сформулировать следующим образом:

1)      Какова суммарная стоимость товаров по каждому наименованию (категории) сосредоточена на обоих складах?

2)      Сколько затрачено средств за полугодие на приобретенные товары, находящиеся на обоих складах?

3)      На какую сумму приобретены товары по видам производителей?

            Следовательно, во всех перечисленных задачах потребуется изменять условия отбора и обработки данных. Рассмотрим последовательно решение трех перечисленных задач.

            Для того чтобы ответить на вопрос – «Какова суммарная стоимость товаров по каждому наименованию (категории) сосредоточена на обоих складах?», необходимо выполнить следующие действия:

·        выбрать место в книге Excel для размещения результатов;

·        вызвать процедуру Консолидация: Меню-Данные-Консолидация;

·        в появившемся диалоговом окне выбрать функцию, например, Сумма;

·        в поле Ссылка нажать на символ  (выбор);

·        открыть лист Excel с исходными данными, например, для склада 2, выделить диапазон данных, в рассматриваемом случае это ячейки A3:D10;

·        нажать на кнопку , результат отображен на рис. 2;

·        повторить операции выбора данных и добавления для данных, которые находятся на листе Excel для склада 1;

·        установить флажки в окнах «подписи верхней строки» и «значения левого столбца»;

·        нажать на кнопку ОК.

Рис. 2.

 

            В результате выполненных действий будет получен результат, который представлен на рис.3.

Рис. 3.

 

            Для решения задач 2 и 3 необходимо выполнить аналогичные действия, которые предпринимались для решения первой задачи, с той лишь разницей, что диапазоны данных будут заданы другими. На рисунках 4 и 5 показаны окна для выполнения консолидации данных по категориям для решения задач.

Рис. 4.

Рис. 5.

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

Рис. 6.

 

Посмотрите пример.

Задание. Поставить задачу так, чтобы провести консолидацию данных с использованием функций Количество, Максимум.

 

Вопросы для самопроверки

1.        Что подразумевают под словом консолидация в Excel?

2.        Если списки образуют несколько таблиц, представленных в Excel, имеют разную структуру, то можно осуществлять с ними процедуру консолидации?

3.        Как правильно задавать условия консолидации данных по категориям?

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