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

Цель работы: освоение следующих технологических приемов работы:

• консолидация данных;

• работа со списками;

• анализ данных с использованием сценариев;

• подбор параметров.

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

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

Технология формирования сводной таблицы

• открыть новый лист, присвоив ему имя «Консолидация»;

• выделить на листе начальную ячейку, например А5, и выполнить команду ДАННЫЕ/Консолидация;

• в окне «Консолидация» выбрать в качестве функции «Сумму», ввести ссылку на консолидируемые ячейки таблицы, находящейся на листе «январь». Для этого щелкнуть в окне ссылки, затем по ярлыку листа «январь» и выделить ячейки справа и вниз, начиная с ячейки «Фамилия, имя, отчество»;

• вернуться в окно консолидации и нажать кнопку «Добавить»;

• повторить описанные выше действия для следующих месяцев расчета;

• после добавления ссылок на данные июня в окне консолидации включить флажки «использовать в качестве имени значения левого столбца» и «создавать связи с исходными данными»;

• щелкнуть по кнопке «Ok». На экране появится таблица консолидации. Добавить к таблице отсутствующие заголовки столбцов;

• используя расположенную слева схему управления структурой включить детальный (второй) уровень просмотра. Переключить на первый уровень просмотра итогов;

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

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

1. Использование формы данных. С помощью форм можно просматривать и вводить данные в список, искать строки по содержимому ячеек, а также удалять строки из списка.

Задание 1. Использовать форму данных для просмотра и корректировки записей таблицы «Справочник работников». Форма — это наиболее удобный способ для просмотра, изменения, добавления и удаления записей списка, а также для поиска записей, удовлетворяющих заданным условиям. Перед работой с формой необходимо задать заголовки столбцов списка. Эти заголовки используются для создания полей формы.

Технология:

• выделить область рабочего листа с таблицей «Справочник работников», начиная со строки, содержащей названия столбцов;

• выполнить команду ДАННЫЕ/Форма и вывести данные одной строки в форму;

• переместиться вперед и назад по таблице, используя кнопки «Далее» и «Назад»;

• удалить последнюю строку таблицы нажатием кнопки «Удалить»;

• добавить новую строку, используя кнопку «Добавить»;

• закрыть диалог нажатием кнопки «Закрыть»;

• используя кнопку «Критерии», найти данные работника по его фамилии, вывести в форму сведения только по членам профсоюза, просмотреть сведения о работниках, имеющих только 1 льготу.

2. Использование автофильтра. В Microsoft Excel предусмотрены различные методы анализа данных в списке. К списку можно применить фильтр, чтобы отобрать только записи, соответствующие определенным условиям. Для этого служит команда ДАННЫЕ/Автофильтр.

Задание 2. Использовать автофильтр для вывода в таблице «Справочник работников» информации только о работниках, оклад которых меньше заданного значения.