Лабораторная работа № 5. Обработка и анализ данных в MS Excel.

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

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

Материал для работы: файл-заготовка ЛР5.xlsx.

Результат работы: файлы ЛР5_ФАМ.xlsx.

Выполнение работы

Сводная таблица – это еще один способ обработки больших списков в MS Excel. С помощью сводных таблиц данные группируются по различным критериям, вычисляются итоговые показатели (сумма, среднее, минимум и т.п.). Создаются таблицы командой вкладка Вставка/Таблицы/Сводная таблица. MS Excel предоставляет возможность построения сводных диаграмм (вкладка Вставка/Диаграммы/Сводная диаграмма).

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

 вставьте дополнительный столбец Возраст после столбца дата рождения. Выделите столбец Дата приема, последовательно выберите вкладка Главная/Ячейки/Вставить. Дайте заголовок столбцу;

 для определения возраста в ячейке Е2 наберите формулу

=ГОД(ТДАТА())-ГОД(D3)

Вместо функции ТДАТА() можно использовать функцию СЕГОДНЯ(). Функция ГОД заносит в ячейку год, соответствующий заданной дате. Результатом вычисления будет дата. Поэтому необходимо изменить формат данных в ячейке.

 выберите контекстное меню/Формат ячеек/Число/Числовой. В ячейке отобразится число 55;

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

 аналогично определите стаж сотрудников. Формулу можно скопировать из ячейки Е2, так как адресация относительная, то в формуле автоматически заменится адрес Е2 на G2.

Для определения надбавки за стаж используйте следующе условия: стаж менее 1 года – надбавка 0%;

от 1 года до 5 лет– 5%;

от 5 до 10 – 10%;

от 10 до 15 – 15%;

свыше 15 лет – 20%.

Учесть указанные условия помогает функция ЕСЛИ.

вставьте столбец Надбавка;

наберите формулу для вычисления надбавки за стаж:

=ЕСЛИ(G2>=15;M2*0,2;ЕСЛИ(G2>=10;M2*0,15;ЕСЛИ(G2>=5;M2*0,1; ЕСЛИ(G2>=1;M2*0,05;0))))

 обратите внимание на соотношение количества альтернатив (5) и количества функций ЕСЛИ в формуле (4); на количество открытых и закрытых скобок;

 скопируйте формулу для других ячеек столбца, применив прием, описанный выше;

 вставьте столбец Заработная плата и вычислите заработную плату каждого сотрудника (оклад+надбавка).

Постройте сводную таблицу.

выберите вкладка Вставка/Сводная таблица;

в окне диалога в поле Таблица или диапазон укажите блок ячеек, который занимает таблица Лист1!$A$1:$R$36. Установите параметр На новый лист/ОК. На экране появится макет будущей сводной таблицы и дополнительные вкладки Работа со сводными таблицами Анализ и Конструктор;

 в качестве основного фильтра в правой части макета сводной таблицы выберите поле Подразделение и перетащите его в область Фильтры;

 поля Вид работы и Образование перетащите в области Строки и Колонны соответственно. В область Значения – поле Заработная плата. В результате получится сводная таблица;

 проведите форматирование ячеек сводной таблицы так, как показано на рисунке 16.

Рисунок 16 – Экранная форма сводной таблицы.

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

Выберите команду Анализ/Сводная диаграмма;

 укажите тип Гистограмма с группировкой. Гистограмма будет построена на листе сводной таблицы. Появится ряд дополнительных вкладок для работы со сводной диаграммой: Анализировать, Конструктор, Формат;

 переместите сводную диаграмму на отдельный лист с помощью команды Анализировать/Действия/Переместить диаграмму/параметр на отдельном листе/ОК.

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

 отобразите данные по отделу разработки ПО и юридическому отделу.

В ячейке В2 установлен фильтр для выбора подразделения(ий). В А4 – для установки одного из видов работ, в В4 – одного из видов образования. Для поля Заработная плата установлена операция Сумма. Каждая ячейка имеет всплывающую подсказку. Параметры каждой ячейки отображаются на вкладке Анализ в области Активное поле.

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

 установите в ячейке В2 соответствующие фильтры. Для выделения нескольких элементов установите флажок Выделить несколько элементов;

 в области Значения откройте список и выберите Параметры полей значений. В окне диалога выберите параметр Среднее/ОК.

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

 для проверки выполнения задания создайте экранную форму полученной таблицы.

Определите количество сотрудников по подразделениям, принятых на работу в том или ином году. Так как данные в столбце Дата приема имеют формат Даты dd.mm.yyyy, необходимо использовать группировку данных с сводной таблицы по годам.

вставьте сводную таблицу;

в область Строки перетащите поле Подразделение, в область Колонны

–   Дата приема, в область Значения – Должность (при этом автоматические для текстового поля будет выставлена операция Количество);

 выделите первую ячейку с датой приема и откройте контекстное меню. Выберите команду Группировать (или выберите вкладку Анализ/Группировать/Группировка по полю);

 в окне диалога в области с шагом установите параметр Годы/ОК. На экране отобразятся сведения о количестве принятых на работу сотрудников по годам в каждом подразделении;

 детализируйте данные, добавив группировку по месяцам: Анализ/Группировать/Группировка по полю/параметр Месяцы/ОК. На экране появятся сведения о приеме сотрудников по месяцам каждого года.

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

1.       Создайте сводную таблицу для определения количество мужчин и женщин в организации в зависимости от уровня образования. Постройте круговую диаграмму.

2.       Определите количество и постройте сводную диаграмму: мужчин и женщин в определенных возрастных группах; сотрудников по уровню образования в возрастных группах.

3.       Определите   количество   сотрудников   в   подразделениях,   которые

проработали более года, более 5 лет, более 10 лет, более 15 лет и т.д.

4.       ¶ В основной таблице определите единовременные выплаты (20% от оклада) сотрудникам в связи с личным юбилеем, когда тому или иному лицу исполняется 40, 45, 50 лет и так далее (через 5 лет).