Практическая работа №17 - 18

«Использование функций в расчетах. Фильтрация данных и условное формирование MS Excel»

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

Задание №1. Создание ведомости начисления заработной платы за октябрь.

1. Запустите MS Excel и создайте новую электронную книгу.

2. Создайте таблицу расчета заработной платы за октябрь 2008г. по образцу. Введите исходные данные – Табельный номер, ФИО, Оклад, Премия (27% от оклада), Налог (13% от общей суммы).

3. Произведите расчеты во всех столбцах таблицы:

Премия = Оклад * Процент премии

Всего начислено = Оклад + Премия

Удержание = Всего начислено * 13%

К выдаче = Всего начислено - Удержание

4. Рассчитайте итоги по столбцам, а также максимальный, минимальный и средний доходы по данным колонки «К выдаче».

5. Переименуйте «Лист1», присвоив ему имя «Зарплата октябрь».

Задание №2. Создание ведомости начисления заработной платы за ноябрь.

1. Скопируйте содержимое листа «Зарплата октябрь» на новый лист. Для этого можно воспользоваться командой Переместить/Скопировать контекстного меню ярлычка. Для копирования необходимо поставить галочку о окне «Создать копию». Перемещать и копировать листы так же можно, перетаскивая их корешки (для копирования удерживайте нажатой клавишу Ctrl.

2. Присвойте скопированному листу название «Зарплата ноябрь». Исправьте название месяца в названии таблицы. Измените значение премии на 32% от оклада.

3. Между колонками «Премия» и «Всего начислено» вставьте новую колонку «Доплата» и рассчитайте значение доплаты по формуле Доплата = Оклад * 5%.

4. Измените формулу для расчета значений колонки «Всего начислено»: Всего начислено = Оклад + Премия + Доплата.

5. Произведите условное форматирование значений колонки «К выдаче». Установите формат вывода значений меду 18 000,00 и 20 000,00 – зеленым цветом шрифта; меньше 18 000,00 – красным; больше или равно 20 000,00 – синим цветом шрифта (вкладка «Главная» кнопка «Условное форматирование»).

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

7. Поставьте к ячейке D3 комментарий «Премия по результатам 4 квартала» (контекстное меню → Вставить примечание), при этом в правом верхнем углу ячейки появиться красный значок, который свидетельствует о наличии примечания.

8. Выполните условное форматирование оклада и премии за ноябрь: до 5 000,00 – желтым цветом заливки; свыше 15 000,00 – малиновым цветом заливки и белый шрифт; все остальные значения – зеленым цветом шрифта.

Задание №3. Создание ведомости начисления заработной платы за декабрь.

1. Скопируйте содержимое листа «Зарплата ноябрь» на новый лист электронной книги. Назовите скопированный лист «Зарплата декабрь». Исправьте название месяца в ведомости на декабрь.

2. Измените значение Премии на 50%, Доплаты – на 8%. Измените примечание на «Премия по результатам года».

3. По данным таблицы «Зарплата декабрь» постройте гистограмму доходов сотрудников. В качестве подписей оси Х выберите фамилии сотрудников.

4. Проведите сортировку по фамилии в алфавитном порядке (по возрастанию) в ведомостях начисления зарплаты за октябрь и декабрь.

Задание №4. Создание итоговой ведомости.

1. Скопируйте содержимое листа «Зарплата октябрь» на новый лист. Назовите скопированный лист «Итоги». Измените название таблицы на «Ведомость начисления заработной платы за 4 квартал».

2. Отредактируйте лист «Итоги» согласно образцу:

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

В ячейке D5 для расчета квартальных начислений «Всего начислено» формула имеет вид = ‘Зарплата декабрь’!F5 + ‘Зарплата ноябрь’!F5 + ‘Зарплата октябрь’!E5.

4. Аналогично произведите квартальный расчет «Удержание» и «К выдаче».

5. Для расчета промежуточных итогов проведите сортировку по подразделению, а внутри подразделения – по фамилии.

6. Подведите промежуточные итоги по подразделениям, используя формулу суммирования. Для этого выделите всю таблицу, перейдите на вкладку «Данные» → Промежуточные итоги. Задайте параметры подсчета промежуточных итогов: при каждом изменении в – Подразделение; операция – Сумма; добавить итоги по: Всего начислено, Удержание, К выдаче. Отметьте галочкой операции «Заменить текущие итоги» и «Итоги под данными».

7. Изучите полученную структуру и формулы подведения промежуточных итогов. Научитесь сворачивает и разворачивать структуру до разных уровней (кнопки «+» и «-»).

8. Сохраните файл.

Контрольные вопросы:

Каково назначение формул Excel? Что может входить в формулу?

Что отображается в ячейке после введения в нее формулы? Как увидеть формулу?

Что такое абсолютный и относительный адрес ячейки?

Что такое абсолютный и относительный адрес ячейки

Отчет должен содержать:

1) Название работы.

2) Цель работы.

3) Задание и ход его выполнения.

4) Ответы на контрольные вопросы.