Лабораторная работа 5. Относительная и абсолютная адресация. Условное форматирование

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

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

Создайте таблицу расчета заработной платы по образцу

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

2. Произведите расчеты во всех столбцах таблицы по формулам:

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

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

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

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

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

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

5. Скопируйте содержимое листа «Зарплата октябрь» на новый лист Перемещать и копировать, листы можно, перетаскивая их корешки (для копирования удерживайте нажатой клавишу [Ctrl|).

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

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

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

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

Задание 2.

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

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

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

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

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

Лабораторная работа 6. Основные статистические и логические функции Microsoft Excel. Подготовка сложной таблицы.

Статистические функции

МАКС – возвращает максимальное число из списка аргументов. Например, формула =МАКС(C1:C7) определит максимальное из чисел, записанных в ячейках C1, C2, …, C7.

МИН – возвращает минимальное число из списка аргументов. Записывается аналогично функции МАКС.

Логические функции

При решении некоторых задач значение ячейки необходимо вычислять одним из нескольких способов, в зависимости от выполнения или невыполнения одного или нескольких условий. Для решения таких задач применяют условную функцию ЕСЛИ. Эта функция имеет следующий формат:

ЕСЛИ (лог_выражение;значение_если_истина;значение_если_ложь).

Приведем наиболее простые примеры.

Пример 1. В ячейку A1 помещена отметка за экзамен. Ячейка A2 должна «сообщить», сдан экзамен или нет.

Известно, что экзамен считается сданным, если получена отметка больше, чем «2». Если бы Excel понимал русский язык, то в ячейку A2 можно было бы ввести такую формулу:

«Если A1>2, то сдал, иначе не сдал».

Но Excel не понимает русского языка, а на его языке эта формула будет выглядеть так:

=ЕСЛИ(A1>2;”сдал”;”не сдал”)

Пример 2. В ячейку A1 помещено какое-либо число. Ячейка A2 должна «определить», положительное это число, отрицательное или нуль.

В отличие от предыдущей задачи, здесь ячейка A2 может принимать одно из трех значений в зависимости от выполнения соответствующих условий. Запишем, как бы могла выглядеть формула для ячейки A2 на русском языке:

«Если A1>0, то положительное, иначе если A1<0, то отрицательное, иначе нуль».

Здесь мы имеем дело с вложенной функцией ЕСЛИ. Запишем эту формулу для Excel:

=ЕСЛИ(A1>0;”положительное”;ЕСЛИ(A1<0;”отрицательное”;”нуль”))

Очень часто при работе в Excel применяются логические функции И и ИЛИ. Формат этих функций одинаков:

И(лог_выражение_1;лог_выражение_2; …),

ИЛИ(лог_выражение_1;лог_выражение_2; …).

Пример 3. В ячейку A1 помещено какое-либо число. Если оно находится в интервале от 0 до 30, то ячейка A2 должна «сообщить» «Да», иначе «Нет».

В этом случае формула для ячейки A2 будет выглядеть так:

=ЕСЛИ(И(A1>0;A1<30);”Да”;”Нет”)

Задание 1. Проиллюстрируйте все три вышеприведенных примера на разных листах Вашей книги.

Задание 2. Пусть имеется список лиц, поступающих в высшее учебное заведение на внеконкурсной основе. Известно, что для зачисления вне конкурса нужно сдать все экзамены как минимум на «удовлетворительно».

Применяя функцию Иили ИЛИ, оформите таблицу сдачи экзаменов

=ЕСЛИ(И(В3>2; C3>2; D3>2);”поступил”;”не поступил”)

=ЕСЛИ(ИЛИ(В3<3; C3<3; D3<3);”не поступил”;”поступил”)

Задание 3.

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

Исходными данными для расчета являются: Фамилия рабочего, тариф, количество рабочих дней.

Надо рассчитать:

1. Заработную плату - тариф * количество рабочих дней;

2. Районный коэффициент, зависящий от заработной платы:

Если зарплата <=3000, то районный коэффициент равен - 0,15 * зарплата

Если зарплата >3000, то районный коэффициент равен 45 руб.

3. Всего начислено - зарплата + районный коэффициент

4. Подоходный налог зависит от суммы всего начислено:

Если всего начислено <=150, то налог не начисляется, т.е. равен 0;

Если всего начислено <=2000, то налог равен 0,13*(всего начислено - 150);

Если всего начислено >2000, то налог равен 0,13*всего начислено.

5. Сумма к выдаче рассчитывается по формуле: всего начислено - налог.

Необходимо также определить итоги по графам "всего начислено" и "сумма к выдаче".

Пример оформления задачи: