Лабораторная работа Агрегирование данных по дисциплине Информационные технологии в профессиональной деятельности

Лабораторная работа № 16

Агрегирование данных

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

Теоретическая часть

Агрегирование данных списков состоит в формировании промежуточных итогов, создании сводных и консолидированных таблиц. Агрегирование информации выполняется для списков MS Excel, в записях которых имеются поля с повторяющимися записями. Наиболее типичными для анализа данных являются различного рода статистические итоги.

Промежуточные итоги

Для формирования промежуточных итогов требуется предварительная сортировка списка по поля группировки записей (рис.1). Команда меню Данные - Структура - Промежуточные итоги позволяет сформировать итоги определённого вида по указанным полям при каждом изменении поля группировки.

По отдельному полю группировки за одно выполнение команды можно сформировать итоги по нескольким полям.

Тип итоговой операции выбирается с учётом типа данных:

• поля числового типа - операции типа сумма, среднее, максимальное, минимальное значение, количество чисел, количество значений, произведение, дисперсия и отклонение;

• поля типа дата/время - операции типа количество значений, максимальное, минимальное значение;

• текстовые поля - операции типа количество значений.

Рис. 1. Диалоговое окно Промежуточные итоги

К полученным промежуточным итогам можно добавить новые итоги с сохранением предыдущих итогов - для этого в диалоговом окне Промежуточные итоги снять установку Заменить текущие итоги. Если установлено итоги под данными, итоги размещаются под детальными строками таблицы, иначе - над строками таблицы. Чтобы выводить каждую группу строк на отдельном листе, следует установить Конец страницы между группами (рис.1).

Для приведения списка в исходное состояние курсор устанавливается в таблицу, выполняется команда Данные - Структура - Промежуточные итоги и в появившемся диалоге нажимается кнопка Убрать всё (рис.1).

Сводные таблицы

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

• список (база данных) MS Excel;

• внешний источник данных (текстовый файл, содержащий табличные данные, реляционная таблица);

• диапазоны ячеек электронной таблицы для консолидации;

• другая сводная таблица MS Excel.

Построение сводной таблицы осуществляется с помощью команды Вставка - Сводная таблица.

Сводная таблица имеет набор параметров, влияющих на внешнее представление. Команда Сводная таблица - Параметры на вкладке Параметры (рис.2) вызывает диалоговое окно настройки формата сводной таблицы и данных внешнего вида источника.

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

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

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

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

Различают два вида консолидации:

• консолидация по расположению ячеек - состав и порядок следования консолидируемых данных во всех диапазонах постоянный;

• консолидация по категориям - на основании одинаковых подписей строк и столбцов для диапазонов ячеек.

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

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

Рис. 3. Диалоговое окно Консолидация

Практическая часть

Задание 1. Ознакомьтесь с теоретической частью лабораторной работы и основные моменты законспектируйте в тетрадь.

Задание 2. Сформируйте промежуточные итоги по полю Профессия. Вычислите средний тариф, средний разряд, количество сотрудников по профессиям.

Последовательность действий

1. Откройте вашу рабочую книгу Excel, в которой вы работали на прошлом занятии, и выберите лист Картотека.

2. Установите курсор в область списка на листе Картотека и отсортируйте список по полю Профессия с помощью команды меню Данные - Сортировка.

3. Скопируйте лист Картотека.

4. Установите курсор в область списка на листе Картотека(2) и выполните команду меню Данные - Структура - Промежуточные итоги, укажите:

• При каждом изменении в - Профессия

• Операция - Среднее

• Добавить итоги по - Разряд работающего, Тариф

• Итоги под данными.

• Нажать кнопку Ok.

5. Установите курсор в область списка на листе Картотека (2) и выберите команду меню Данные - Структура - Промежуточные итоги:

• При каждом изменении в - Профессия

• Операция - Количество

• Добавить итоги по - Табельный №

• Итоги под данными.

• Нажать кнопку Ok.

На рис. 4 представлен внешний вид таблицы Картотека с промежуточными итогами.

Рис. 4. Промежуточные итоги

Задание 3. Постройте сводную таблицу для расчёта месячной зарплат рабочих при повременной форме оплаты труда. Установлена премия по разрядам работающих: 2 разряд 25%, 3 разряд 32%, 4 разряд 50% к тарифу. Вычеты из всех видов начислений составляют 13%.

Последовательность действий

1. Выберите лист Картотека.

2. Установите курсор в список на листе Картотека.

3. Выполните команду меню Вставка - Сводная таблица.

4. Разместите поля сводной таблицы, так как показано на рис. 5

Рис. 5. Размещение списка полей сводной таблицы

5. Установите курсор в область сводной таблицы и задайте параметры сводной таблицы с помощью команды Параметры: Параметры - Сводная таблица - Параметры на вкладке Итоги и фильтры уберите указатель Показывать общие итоги для строк, а на вкладке Вывод выберите команду Классический макет сводной таблицы (разрешено перетаскивание полей) (рис.6).

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

Сводная таблица представлена на рис. 7.

Рис. 7. Сводная таблица для списка Картотека

6. Установите курсор в область сводной таблицы. На вкладке Параметры выберите команду Формулы - Вычисляемое поле. Создайте новое вычисляемое поле (рис. 8):

• Имя поля - Зарплата, формула: =Тариф*168

• Кнопка Добавить.

• Закрыть окно - кнопка Ok.

Рис. 8.

7. Установите курсор в область сводной таблицы.

8. Проверьте, чтобы на вкладке Параметры была активна команда Список полей. В диалоговом окне Список полей сводной таблицы уберите из области Значения поле Сумма по полю Тариф.

9. Дважды щёлкните ЛКМ по полю Сумма по полю Зарплата, таким образом вызвав диалоговое окно Параметры поля значений (рис. 9), в котором измените имя поля на Месячная зарплата и с помощью кнопки Числовой формат выберите формат поля Денежный.

Рис. 9. Окно настройки поля сводной таблицы

10. Установите курсор в области сводной таблицы на поле Разряд работающего и выполните команду Формулы - Вычисляемый объект (рис. 10).

Рис. 10. Вставка в сводную таблицу вычисляемого элемента

• Введите имя поля - Премия;

• Выберите поле Разряд работающего, раскройте список элементов (двойной щелчок ЛКМ на поле);

• В окно формулы введите формулу: ='2'*0,25+'3'*0,32+'4'*0,5;

• Нажмите кнопку Добавить;

• Введите имя поля - Премия;

• Выберите поле Разряд работающего, раскройте список элементов (двойной щелчок ЛКМ на поле);

• В окно формулы введите формулу: =-0,13*('2'*1,25+'3'*1,32+'4'*1,5);

• Нажмите кнопку Добавить;

• Нажмите кнопку OK

На рис. 11 представлен внешний вид таблицы Сводной таблицы с добавленными полями Премия и Вычеты.

Рис. 11.

11. Для просмотра выражения вычисляемого поля и вычисляемых объектов выберите команду Формулы - Вывести формулы на вкладке Параметры (рис. 12).

12. Установите курсор в сводную таблицу.

13. На вкладке Параметры, с помощью команды Список полей, измените положение полей, как показано на рис. 13.

14. Установите курсор в сводную таблицу и на вкладке Параметры нажмите кнопку Сводная диаграмма. Измените на своё усмотрение формат области диаграммы (рис. 14).

Задание 4. Отчитайтесь по проделанной работе преподавателю и оформите отчёт по данной лабораторной работе, ответив письменно на контрольные вопросы (см. шаблон отчёта).

Рис. 12 Лист формул сводной таблицы

Рис. 13. Сводная таблица Зарплата работающих повременщиков

Рис. 14 Диаграмма сводной таблицы зарплата

9