Работа с инструментом Консолидация данных

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

Работу с инструментом Консолидация освоим на решении конкретной задачи.

Требуется определить общую стоимость заказов по каждому изделию.

Упражнение 16

1. Создайте новый лист рабочей книги, назовите его «Консолидация».

2. На листе «Консолидация» создайте заголовки столбцов консолидирующей таблицы, ячейки с названиями столбцов копируйте из таблицы «Заказы», чтобы избежать ошибок при вводе. (Рис. 220

 

Рис. 20 Заголовки столбцов консолидирующей таблицы

3. Выделите ячейки A2:B2, щелкните по кнопке Данные /Работа с данными /Консолидация. В открывшемся диалоговом окне из списка Функция выберите функцию Sum (Сумма).

4.  В поле Ссылка выделите все данные из таблицы «Заказы», начиная со столбца «Изделие», так как именно он является первым в консолидирующей таблице, это важно, по столбцу «Сумма заказа». Программа сама определит, из какого столбца собирать данные по заголовкам столбцов консолидирующей таблицы.

5. Нажмите на кнопку Добавить. Адрес диапазона будет добавлен в поле Список диапазонов

6. В поле Использовать в качестве имен установите флажки Значения левого столбца и Подписи верхней строки.

7. Щелкните по кнопке ОК.

Образец заполненной таблицы представлен на рис. 21. Сравните с результатами, полученными на листе «Сортировка и итоги».

Рис. 21. Таблица с результатами консолидации

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

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

8. На листе «Консолидация» выделите консолидирующую таблицу, полученную в результате выполнения упражнения 9.

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

10. Щелкните по кнопке ОК.

Получим заполненную таблицу «Стоимость заказанных изделий». (Рис. 22)

Рис. 22 Консолидирующая таблица со связями с исходными данными

Обратите внимание: слева от названий изделий в таблице появились кнопки со знаком «плюс». С помощью этих кнопок можно развернуть и свернуть группы. То есть изделия в таблице группированы по названиям. Щелкнув по такой кнопке, мы разворачиваем группу конкретного изделия, на кнопке появляется значок «минус», видно из каких значений исходной таблицы «Заказы» складывается общая сумма по каждому изделию. (Рис. 23)

Рис. 23 Консолидирующая таблица со связями с исходными данными

Задание 3 для самостоятельной работы

На отдельном листе самостоятельно постройте консолидирующие таблицы без связи с исходными данными (в поле Создавать связи с исходными данными уберите флажок):

1. Сумма и количество заказов по каждому региону.

2. Количество и средняя сумма заказов по каждому заказчику.

3. Максимальная и минимальная сумма заказов по каждому изделию.

4. Максимальное и минимальное количество заказанных изделий по каждому изделию.

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

Фильтрация данных

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

⇐ Предыдущая10111213141516171819Следующая ⇒

Поделиться: