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

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

Тема : СОЗДАНИЕ МНОГОСТРАНИЧНОЙ

ЭЛЕКТРОННОЙ КНИГИ ПРИ РАСЧЕТЕ

ЗАРАБОТНОЙ ПЛАТЫ В MICROSOFT EXCEL

Цель занятия:

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

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

Исходные данные представлены на рис. 10.1.

Порядок работы

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

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

                                      Рис. 10.1 Исходные данные для задания 10.1

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

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

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

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

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

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

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

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

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

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

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

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

Краткая справка.

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

5. Скопируйте содержимое листа «Зарплата октябрь» на новый лист {Правка/Переместить/копировать лист). Можно воспользоваться командой Переместить/скопировать  контекстного  меню ярлычка. Не забудьте для копирования поставить галочку в окошке Создавать  копию.

Краткая справка.

Перемещать и копировать листы можно, пepeтаскивая их ярлыки (для копирования

удерживайте нажатой клавишу [Ctrl]).

6.Присвойте скопированному листу название «Зарплата ноябрь».

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

7. Между колонками «Премия» и Всего начислено» вставьте новую колонку — «Доплата» (выделите столбец Е «Всего начислено» и выполните команду Вставка/Столбы). рассчитайте значение доплаты по формуле Доплата = Оклад × %Доплаты. Значение доплаты примите равным 5%.

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

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

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

9. Проведите условное форматирование значений колонки «К выдаче». Установите формат вывода значений между 7000 и 10000  — зеленым цветом шрифта, меньше или равно 7000 — красным цветом шрифта, больше или равно 10 000 — синим цветом (Формат/Условное форматирование).

10. Проведите сортировку по фамилиям в алфавитном порядке по возрастанию (выделите фрагмент таблицы с 5-й по 18-ю строки без «Всего», выберите меню Данные/Сортировка, сортировать по – Столбец В).

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

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

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

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