Создание электронной книги. Относительная и абсолютная адресация в Excel.

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

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

Порядок выполнения задания.

1) Создайте новую электронную книгу с именем «Ведомость зарплаты».

2) Создайте таблицу расчета заработной платы по образцу (рис. 7).

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

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

Формулы для расчетов:

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

Удержание = Всего начислено * Процент удержания (в ячейке F5 введите формулу = $F$4*E5);

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

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

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

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

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

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

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

9) Измените формулу расчета значений колонки «Всего начислено»:

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

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

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

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

Рис. 9 Конечный вид зарплаты за ноябрь.

13) Сохраните файл «Ведомость зарплаты».

Задание 6. Сделайте примечания к двум-трем ячейкам.

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

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

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

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

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

Задание 9. Сохраните изменения в файле «Ведомость зарплаты».