По результатам расчетов сформулируйте выводы

 

ПРАКТИЧЕСКОЕ ЗАНЯТИЕ № 2

Тема: ОТНОСИТЕЛЬНАЯ И АБСОЛЮТНАЯ АДРЕСАЦИЯ

В ТАБЛИЧНОМ ПРОЦЕССОРЕ MS EXCEL

Цель занятия. Изучение информационной технологии применения относительной и абсолютной адресации для финансовых расчетов.

Перед началом занятия необходимо знать: понятие абсолютной и относительной адресации в табличном процессоре MS EXCEL.

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

Оборудование: ПК, программа MS EXCEL.

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

 

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

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

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

 

Рис. 1. Исходные данные для Задания 1

 

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

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

Введите исходные данные — Табельный номер, ФИО и Ок­лад, % Премии = 27 %, % Удержания = 13 %.

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

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

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

 

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

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

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

 

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

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

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

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

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

 

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

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

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

наборы данных на одном листе.

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

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

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

 

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

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

Доплата = Оклад * % Доплаты.

Значение доплаты примите равным 5 %.

 

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

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

 

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

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

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

 

 

Рис. 3. Условное форматирование данных

 

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

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

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

 

 

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

Дополнительные задания

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

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

до 2000 — желтым цветом заливки; от 2000 до 10 000 — зеленым цветом шрифта; свыше 10 000 — малиновым цветом заливки, белым цветом шрифта.

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

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

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