Лабораторная работа № 1
Создание многостраничной электронной книги при расчете заработной платы в Microsoft Excel
Цель занятия. Применение относительной и абсолютной адресации для финансовых расчетов. Сортировка, условное форматирование и копирование созданных таблиц. Работа с листами электронной книги.
Задание. Создать таблицы ведомости начисления заработной платы за два месяца на разных листах электронной книги, произвести расчеты, форматирование и сортировку данных. Исходные данные представлены в таблице 1.1.
Технология выполнение работы:
1. Откройте редактор электронных таблиц Microsoft Excel и создайте новую электронную книгу с названием "Зарплата".
2. Создайте на "Листе 1" таблицу расчета заработной платы по образцу (таблица 1.1). Выделите отдельные ячейки для значений %Премии (D4) и %Удержания (F4). Введите исходные данные: Табельный номер, ФИО и Оклад; %Премии = 27%; %Удержания = 13%.
Таблица 1.1 - Исходные данные для задания
Ведомость начисления заработной платы за октябрь
| Таб. номер | Фамилия И.О. | Оклад (руб.) | Премия (руб.) | Всего начислено (руб.) | Удержания (руб.) | К выдаче (руб.) |
| 27% | 13% | |||||
| Петрова И.Л. | ? | ? | ? | ? | ||
| Иванова И.Г. | ? | ? | ? | ? | ||
| Степанова А.Ш. | ? | ? | ? | ? | ||
| Шорохов С.М. | ? | ? | ? | ? | ||
| Галкин В.Ж. | ? | ? | ? | ? | ||
| Портнов М.Т. | ? | ? | ? | ? | ||
| Орлова Н.Н. | ? | ? | ? | ? | ||
| Степанкина А.В. | ? | ? | ? | ? | ||
| Жарова Г.А. | ? | ? | ? | ? | ||
| Стольникова О.Д. | ? | ? | ? | ? | ||
| Дрынкина С.С. | ? | ? | ? | ? | ||
| Шпаро Н.Г. | ? | ? | ? | ? | ||
| Шашкин Р.Н. | ? | ? | ? | ? | ||
| Максимальный доход | ? | |||||
| Минимальный доход | ? | |||||
| Средний доход | ? |
Произведите расчеты во всех столбцах таблицы.
При расчете "Премии" используйте формулу Премия = Оклад х %Премии, в ячейке D5 наберите формулу =$D$4*C5 (ячейка D4 используется в виде абсолютной адресации). Скопируйте набранную формулу вниз по столбцу автозаполнением.
Краткая справка. Для удобства работы и формирования навыков работы с абсолютным видом адресации рекомендуется при оформлении констант окрашивать ячейку цветом, отличным от цвета расчетной таблицы. Тогда при вводе формул в расчетную ячейку окрашенная ячейка с константой будет вам напоминанием о том, что следует установить абсолютную адресацию (набором с клавиатуры в адресе символов $ или нажатием клавиши [F4]).
Формула для расчета "Всего начислено": Всего начислено = Оклад + Премия.
При расчете "Удержания" используйте формулу Удержания = Всего начислено х %Удержаний, в ячейке F5 наберите формулу = $F$4 * Е5.
Формула для расчета столбца "К выдаче": К выдаче = Всего начислено - Удержания.
3. Рассчитайте итоги по столбцам, а также максимальный, минимальный и средний доходы по данным колонки "К выдаче" (Вставка/Функция/категория Статистические).
4. Переименуйте ярлычок "Листа 1", присвоив ему имя "Зарплата октябрь". Для этого дважды щелкните мышью по ярлычку и наберите новое имя. Можно воспользоваться командой "Переименовать" контекстного меню ярлычка, вызываемого правой кнопкой мыши. Результаты работы представлены в таблице 1.2.
Таблица 1.2 - Итоговый вид таблицы расчета заработной платы за октябрь
Ведомость начисления заработной платы за октябрь
| Таб. номер | Фамилия И.О. | Оклад (руб.) | Премия (руб.) | Всего начислено (руб.) | Удержания (руб.) | К выдаче (руб.) |
| 27% | 13% | |||||
| Петрова И.Л. | 1215,00 | 5715,00 | 742,95 | 4972,05 | ||
| Иванова И.Г. | 1309,50 | 6159,50 | 800,74 | 5358,77 | ||
| Степанова А.Ш. | 1404,00 | 6604,00 | 858,52 | 5745,48 | ||
| Шорохов С.М. | 1498,50 | 7048,50 | 916,31 | 6132,20 | ||
| Галкин В.Ж. | 1593,00 | 7493,00 | 974,09 | 6518,91 | ||
| Портнов М.Т. | 1687,50 | 7937,50 | 1031,88 | 6905,63 | ||
| Орлова Н.Н. | 1782,00 | 8382,00 | 1089,66 | 7292,34 | ||
| Степанкина А.В. | 1876,50 | 8826,50 | 1147,45 | 7679,06 | ||
| Жарова Г.А. | 1971,00 | 9271,00 | 1205,23 | 8065,77 | ||
| Стольникова О.Д. | 2065,50 | 9715,50 | 1263,02 | 8452,49 | ||
| Дрынкина С.С. | 2160,00 | 10160,00 | 1320,80 | 8839,20 | ||
| Шпаро Н.Г. | 2254,50 | 10604,50 | 1378,59 | 9225,92 | ||
| Шашкин Р.Н. | 2349,00 | 11049,00 | 1436,37 | 9612,63 | ||
| Максимальный доход | 9999,35 | |||||
| Минимальный доход | 4972,05 | |||||
| Средний доход | 7485,7 |
5. Скопируйте содержимое листа "Зарплата октябрь" на новый лист (Правка/Переместить/Скопировать лист). Можно воспользоваться командой "Переместить/Скопировать" контекстного меню ярлычка. Не забудьте для копирования поставить галочку в окошке "Создавать копию" (рисунок 1.3).
Рисунок 1.3 - Копирование листа электронной книги
Краткая справка. Перемещать и копировать листы можно, перетаскивая их ярлыки (для копирования удерживайте нажатой клавишу [Ctrl]).
Присвойте скопированному листу название "Зарплата ноябрь". Исправьте название месяца в названии таблицы. Измените значение "Премии" на 32%. Убедитесь, что программа произвела пересчет формул.
7. Между колонками "Премия" и "Всего начислено" вставьте новую колонку - "Доплата" (выделите столбец Е "Всего начислено" и выполните команду Вставка/Столбцы); рассчитайте значение доплаты по формуле Доплата = Оклад х %Доплаты. Значение доплаты примите равным 5%.
8. Измените формулу для расчета значений колонки "Всего начислено": Всего начислено = Оклад + Премия + Доплата. Скопируйте формулу вниз по столбцу.
9. Проведите условное форматирование значений колонки "К выдаче". Установите формат вывода значений между 7000 и 10 000 - зеленым цветом шрифта, меньше или равно 7000 - красным цветом шрифта, больше или равно 10 000 - синим цветом шрифта (Формат/Условное форматирование).
10. Проведите сортировку по фамилиям в алфавитном порядке по возрастанию (выделите фрагмент таблицы с 5-й по 18-ю строки без строки "Всего", выберите меню "Данные/Сортировка", сортировать по - Столбец В).
11. Поставьте к ячейке D3 комментарии "Премия пропорциональна окладу" (Вставка/Примечание), при этом в правом верхнем углу ячейки появится красная точка, которая свидетельствует о наличии примечания.
12. Защитите лист "Зарплата ноябрь" от изменений (Сервис/Защита/Защитить лист). Задайте пароль на лист, создайте подтверждение пароля. Убедитесь, что лист защищен и невозможно удаление данных. Снимите защиту листа (Сервис/Защита/Снять защиту листа).