Консолидация данных по расположению

Лабораторная работа 3

СОЗДАНИЕ ОТЧЕТОВ С ПОМОЩЬЮ КОНСОЛИДАЦИИ ДАННЫХ.

 

Цель работы: изучить и апробировать методы консолидации данных.

 

Теоретические сведения

Консолидация данных

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

Можно консолидировать данные, содержащиеся и в одной книге и даже на одном листе.

Ячейки, содержащие результат консолидации, могут быть связаны с исход­ными данными. В таком случае они будут обновляться при изменении ис­ходных ячеек и отражать текущее положение дел. Однако, если необходимо зафиксировать положение дел только, в один момент времени, консолидиро­ванные данные можно сделать оторванными от исходных. В таком случае итоговые ячейки не будут зависеть от исходных.

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

Консолидация данных по расположению

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

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

2. Убедитесь, что макеты всех диапазонов совпадают.

3. Задайте каждому диапазону собственное имя.

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

5. Перейдите к вкладке «Данные» и в группе «Работа с данными» щелкните по кнопке «Консолидация».

6. В окне «Консолидация» (рис. 3.1) раскройте список графы «Функция» и выберите итоговую функцию.

Рис. 3.1. Окно «Консолидация»

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

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

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

10. В группе «Использовать в качестве имен» отключите все пункты.

11. Закройте окно кнопкой «ОК».

12. На выбранном листе отобразится консолидированная таблица (рис. 3.2).

Рис. 3.2. Пример консолидированной таблицы