Список «Ведомость за октябрь». Удержание.

Лабораторная работа №3. Консолидация данных и их анализ в сводной таблице.

Цель работы: изучить механизмы подведения Итогов посредством объединения (консолидирования) данных разных источников.

Задачи:

1. Консолидирование данных из разных списков.
2. Конструирование сводных таблиц.
3. Анализ данных в сводных таблицах

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

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

.

В Excel существуют операции, позволяющие выполнять несколько вариантов консолидации списков.

• По расположению
• По категориям
• С помощью сводных таблиц

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

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

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

Создание и обработка сводных таблиц осуществляется с помощью Мастера сводных таблиц, Данные / Сводная таблица.

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

1. Подготовим исходные данные.

• В качестве таблицы источника выберем «Ведомость» (см. пред. работа).
• С помощью команды Фильтр / Автофильтр выберем из нее последовательно записи, относящиеся к месяцам (октябрь и ноябрь).
• Скопируем их и разнесем по разным листам книги. Листы назовем Октябрьи Ноябрь.
• Из таблиц удалим столбцы Стаж и Месяц.
• В таблицу «Ведомость за ноябрь» добавим одну запись, чтобы число их в каждой таблице было одинаковое.

1. Выполним консолидацию данных по расположению.

· Добавим в книгу новый лист Консолидация.

· Активизируем ячейку А1 и выполним команду Данные / Консолидация.

· В открывшемся окне введем диапазон, занимаемым первым списком – Октябрь!$A$3:$I$7. Нажмем кнопку Добавить.

· Далее введем диапазон, занимаемым вторым списком – Ноябрь!$A$3:$I$7. Нажмем кнопку Добавить.

· Установим функцию из раскрываемого списка, например Сумма

· Установим флажки на опциях: Использование в качестве имен, или Создавать связи с исходными данными (но не одновременно). Нажмем ОК.

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

Задание 2. Консолидация по категориям.

1. Подготовим исходные данные.

• В качестве таблицы источника выберем «Ведомость» (см. пред. работа).
• С помощью команды Фильтр / Автофильтр выберем из нее последовательно записи, относящиеся к месяцам (сентябрь, октябрь и ноябрь).
• Скопируем их и разнесем по разным листам книги. Листы назовем Сентябрь, Октябрьи Ноябрь.
• Из таблиц удалим столбцы Стаж и Месяц.
• Данные в источниках дополнять не будем.Количество записей в каждой из таблиц отличается.
1. Выполним консолидацию всех списков по описанной выше технологии. Получим таблицу консолитизированных данных.

Задание 3. Консолидация списков с разным числом полей.

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

1. Список «Ведомость за сентябрь». Начисление

Список «Ведомость за октябрь». Удержание.

Не нашли, что искали? Воспользуйтесь поиском по сайту: