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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Консолидация данных в программе Microsoft Excel

Консолидация в Microsoft Excel

При работе с однотипными данными, размещенными в разных таблицах, листах или даже книгах, для удобства восприятия лучше собрать информацию воедино. В Microsoft Excel с этой задачей можно справиться с помощью специального инструмента, который называется «Консолидация». Он предоставляет возможность собрать разрозненные данные в одну таблицу. Давайте узнаем, как это делается.

Условия для выполнения процедуры консолидации

Естественно, что не все таблицы можно консолидировать в одну, а только те, которые соответствуют определенным условиям:

    • столбцы во всех таблицах должны иметь одинаковое название (допускается лишь перестановка столбцов местами);
    • не должно быть столбцов или строк с пустыми значениями;
    • шаблоны у таблиц должны быть одинаковыми.

Создание консолидированной таблицы

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

  1. Открываем отдельный лист для консолидированной таблицы.

Добавление нового листа в Microsoft Excel

  • На открывшемся листе отмечаем ячейку, которая будет являться верхней левой ячейкой новой таблицы.
  • Находясь во вкладке «Данные» кликаем по кнопке «Консолидация», которая расположена на ленте в блоке инструментов «Работа с данными».
  • Переход к консолидации данных в Microsoft Excel

    Открывается окно настройки консолидации данных.

    Настройки консолидации в Microsoft Excel

    В поле «Функция» требуется установить, какое действие с ячейками будет выполняться при совпадении строк и столбцов. Это могут быть следующие действия:

    • сумма;
    • количество;
    • среднее;
    • максимум;
    • минимум;
    • произведение;
    • количество чисел;
    • смещенное отклонение;
    • несмещенное отклонение;
    • смещенная дисперсия;
    • несмещенная дисперсия.

    В большинстве случаев используется функция «Сумма».

    Выбор функции для консолидации в Microsoft Excel

    В поле «Ссылка» указываем диапазон ячеек одной из первичных таблиц, которые подлежат консолидации. Если этот диапазон находится в этом же файле, но на другом листе, то жмем кнопку, которая расположена справа от поля ввода данных.

    Переход к выбору диапазона для консолидации в Microsoft Excel

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

    Выбор диапазона для консолидации в Microsoft Excel

    Добавление диапазона в Microsoft Excel

    Как видим, после этого диапазон добавляется в список.

    Диапазон добавлен в Microsoft Excel

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

    Все диапазоны добавлены для консолидации в Microsoft Excel

    Если же нужный диапазон размещен в другой книге (файле), то сразу жмем на кнопку «Обзор…», выбираем файл на жестком диске или съемном носителе, а уже потом указанным выше способом выделяем диапазон ячеек в этом файле. Естественно, файл должен быть открыт.

    Выбор файла для консолидации в Microsoft Excel

    Точно так же можно произвести некоторые другие настройки консолидированной таблицы.

    Для того, чтобы автоматически добавить название столбцов в шапку, ставим галочку около параметра «Подписи верхней строки». Для того, чтобы производилось суммирование данных устанавливаем галочку около параметра «Значения левого столбца». Если вы хотите, чтобы при обновлении данных в первичных таблицах обновлялась также и вся информация в консолидированной таблице, то обязательно следует установить галочку около параметра «Создавать связи с исходными данными». Но, в этом случае нужно учесть, что, если вы захотите в исходную таблицу добавить новые строки, то придется снять галочку с данного пункта и пересчитать значения вручную.

    Когда все настройки выполнены, жмем на кнопку «OK».

    Установка настроек для консолидации в Microsoft Excel

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

    Просмотр содержимого группы консолидированной таблицы в Microsoft Excel

    Теперь содержимое группы доступно для просмотра. Аналогичным способом можно раскрыть и любую другую группу.

    Содержимое группы группы консолидированной таблицы в Microsoft Excel

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

    Мы рады, что смогли помочь Вам в решении проблемы.

    Помимо этой статьи, на сайте еще 11905 инструкций.
    Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

    Отблагодарите автора, поделитесь статьей в социальных сетях.

    Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

    Ссылка на основную публикацию