ПОЗНАВАТЕЛЬНОЕ

Оси и плоскости тела человека - Тело человека состоит из определенных топографических частей и участков, в которых расположены органы, мышцы, сосуды, нервы и т.д.

Отёска стен и прирубка косяков - Когда на доме не достаёт окон и дверей, красивое высокое крыльцо ещё только в воображении, приходится подниматься с улицы в дом по трапу.

Дифференциальные уравнения второго порядка (модель рынка с прогнозируемыми ценами) - В простых моделях рынка спрос и предложение обычно полагают зависящими только от текущей цены на товар.

Методика выполнения работы

Лабораторная работа

Тема: Относительная и абсолютная адресация MS Excel

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

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

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

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

Методика выполнения работы

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

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

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

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

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

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

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

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

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

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

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

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

Рассчитайте итоги по столбцам, а также максимальный, минимальный и средний доходы по данным колонки «К выдаче» (Формулы/Другие функции/категория – Статистические функции).
Переименуйте ярлычок Листа 1, присвоив ему имя «Зарплата октябрь». Для этого дважды щёлкните мышью по ярлычку листа и наберите новое имя. Можно воспользоваться командой Переименовать контекстного меню ярлычка, вызываемого правой кнопкой мыши. Результаты работы представлены на рис.2.

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

Скопируёте содержимое листа «Зарплата октябрь» на новый лист. Можно воспользоваться командой Переместить/Скопировать лист контекстного меню ярлычка листа. Не забудьте поставить галочку в окошке Создавать копию.
Присвойте скопированному листу имя «Зарплата ноябрь». Исправьте название месяца в названии таблицы. Измените значение Премии на 32%. Убедитесь, что программа произвела пересчёт формул.
Между колонками «Премия» и «Всего начислено» вставьте новую колонку «Доплата» и рассчитайте значение доплаты по формуле:

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

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

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

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

Проведите условное форматирование значений колонки «К выдаче». Установите формат вывода значений между 7 000 и 10 000 – зелёным цветом шрифта; меньше 7 000 – красным; больше или равно 10 000 – синим цветом шрифта (Главная/Редактирование/Сортировка и фильтр/Фильтр/ Числовые фильтры) (рис.3).

Проведите сортировку по фамилиям в алфавитном порядке по возрастанию (выделите фрагмент с 5 по 18 строки таблицы – без итогов, выберите меню Данные/Сортировка, сортировать по – Столбец Б).
Поставьте к ячейке D3 комментарии «Премия пропорциональна окладу» (Вставка/Примечание), при этом в правом верхнем углу ячейки появится красная точка, которая свидетельствует о наличии примечания. Конечный вид расчёта заработной платы за ноябрь приведён на рис.4.

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

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

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

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

Рис.5. Защита листа электронной книги

Рис. 6. Подтверждения пароля

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

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

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

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

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

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

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

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