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

Консолидация данных нескольких листов на одном листе

Что осваивается и изучается?

Консолидация данных нескольких листов на одном листе таблиц.

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

Существует два основных способа консолидации данных.

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

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

 

Задание 1.	· Скопировать файл Филиалы.xlsx. В нем на разных листах представлены отчеты о продаже товаров тремя филиалами фирмы по месяцам 1-го квартала.
	· Получить объем продаж изделий по месяцам фирмой в целом и по этим данным построить диаграмму.

Методические указания

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

§ Каждый диапазон данных подготовлен на отдельном листе.

§ Все диапазоны данных были представлены в формате списка: первая строка каждого столбца содержит подпись, остальные строки — однотипные данные; пустые строки или столбцы в списке отсутствуют.

§ Макеты всех диапазонов совпадают.

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

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

1. На основном листе (на котором должна выполняться консолидация – назовите его Филиалы) щелкните левый верхний угол.

2. На вкладке Данные в группе Работа с данными выберите команду Консолидация.

3. Выберите в раскрывающемся списке Функция итоговую функцию, которую требуется использовать для консолидации данных - СУММ.

4. Щелкните кнопку справа от поля Ссылка, чтобы открыть диалоговое окно Консолидация — Ссылка.

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

6. В диалоговом окне Консолидация нажмите кнопку Добавить и повторите действия 4 и 5, чтобы добавить все нужные диапазоны.

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

8. Вставьте гистограмму, используя макет диаграмм «Макет 5»:

 

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

Задание 1С.	На следующих 4-х листах построить таблицы.
	1. На первых 3-х листах построить таблицы, показывающие экзаменационные оценки каждого из 10 студентов группы по одной из дисциплин, например: 2. На 4-ом листе консолидировать данные и подвести итоги сессии: Вставить гистограмму, например: