ПР_Создание многостраничной электронной книги при расчёте заработной платы в Microsoft Excel

Практическая работа №8

Тема: Создание многостраничной электронной книги при расчёте заработной платы в Microsoft Excel.

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

Вид работы: фронтальный

Время выполнения: 2 часа

Задания к практической работе

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

Исходные данные представлены на рис. 1, результаты работы – на рис. 6.

Ход работы

1.   Запустите редактор электронных таблиц Microsoft Excel и создайте новую электронную книгу.

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

Рисунок 1 – Исходные данные для задания 1

Примечание. Выделите отдельные ячейки для значений % Премии (D4) и % Удержания (F4).

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

При расчете Премии используется формула Премия = Оклад * % Премии, в ячейке  D5 наберите формулу =$D$4 *C5 (ячейка D4 используется в виде абсолютной адресации) и скопируйте автозаполнением.

Рекомендации. Для удобства работы и формирования навыков работы с абсолютным видом адресации рекомендуется при оформлении констант окрашивать ячейку цветом, отличным от цвета расчетной таблицы. Тогда при вводе формул в расчетную окрашенная ячейка (т. е. ячейка с константой) будет вам напоминанием, что следует установить абсолютную адресацию (набором символов $ с клавиатуры или нажатием клавиши [F4]).

Формула для расчета «Всего начислено»:

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

При расчете Удержания используется формула

Удержание = Всего начислено х % Удержания,

для этого в ячейке F5 наберите формулу = $F$4 * E5.

Формула для расчета столбца «К выдаче»:

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

3.   Рассчитайте итоги по столбцам, а также максимальный, минимальный и средний доход по данным колонки «К выдаче» (Формулы – Вставить функцию – категория Статистические).

4.   Переименуйте ярлычок Лист 1, присвоив ему имя «Зарплата октябрь». Для этого дважды щелкните мышью по ярлычку и наберите новое имя. Можно воспользоваться командой Переименовать контекстного меню ярлычка, вызываемого правой кнопкой мыши. Результаты работы представлены на рис. 2.

Рисунок 2 – Итоговый вид таблицы расчета заработной платы за октябрь

Краткая справка. Каждая рабочая книга Excel может содержать до 255 рабочих листов. Это позволяет, используя несколько листов, создавать понятные и четко структурированные документы, вместо того, чтобы хранить большие последовательные наборы данных на одном листе.

5.   Скопируйте содержимое листа «Зарплата октябрь» на новый лист нажатием правой кнопки мыши по имени листа, в контекстном меню выбрать функцию Переместить/Скопировать… - поставить галочку в окошке Создавать копию (Рис. 3)

Краткая справка. Перемещать и копировать лист можно, перетаскивая их корешки (для копирования удерживайте нажатой клавишу [Ctrl]).

Рисунок 3 – Копирование листа электронной книги

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

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

8.   Измените формулу для расчета значений колонки «Всего начислено»:

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

Скопируйте формулу вниз по столбцу.

9.   Проведите условное форматирование значений колонки «К выдаче». Установите формат вывода значений между 7000 и 10000 – зеленом цветом шрифта; меньше 7000 – красным; больше или равно 10000 – синим цветом шрифта (Главная – Условное форматирование – Правила выделения ячеек) (Рис. 4).

Рисунок 4 – Условное форматирование данных

10.   Проведите сортировку по фамилиям в алфавитном порядке по возрастанию (выделите фрагмент с 5 по 18 строки таблицы – без итогов, выберите меню Главная – Сортировка и фильтр – сортировка от минимального к максимальному – в приделах указанного выделения) (Рис. 5.).

Рисунок 5 - Сортировка данных.

11.   Поставьте к ячейке D3 комментарии «Премия пропорциональна окладу» (Рецензирование – Создать примечание), при этом в правом верхнем углу ячейки появится красная точка, которая свидетельствует о наличии примечания. Конечный вид расчета заработной платы за ноябрь приведен на рис. 6.

Рисунок 6 – Конечный вид зарплаты за ноябрь

12.   Защитите лист «Зарплата ноябрь» от изменений (Рецензирование - Защитить лист). Задайте пароль на лист (рис. 7), создайте подтверждение пароля (рис. 8).

Рисунок 8 - Защита листа электронной книги

Убедитесь, что лист защищен и невозможно удаление данных. Снимите

защиту листа (Рецензирование – Снять защиту листа).

13.   Сохраните созданную электронную книгу под именем «Зарплата» в своей папке.

Задание 2. Сделать примечание к двум – трем ячейкам.

Задание 3. Выполнить условное форматирование оклада и премии за ноябрь месяц:

-       до 2000 р. – желтым цветом заливки;

-       от 2000 до 10000 р. – зеленым цветом шрифта;

-       свыше 10000 р. – малиновым цветом заливки, белым цветом шрифта.

Задание 4. Защитить лист зарплаты от за октябрь от изменений.

Проверьте защиту. Убедитесь в неизменности данных. Снимите защиту со всех листов электронной книги «Зарплата».

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