Лабораторная работа 3. Связывание рабочих листов.

Цель:

1. Закрепить навыки работы с листами электронной книги;

2. Овладеть навыками составления табличных формул;

3. Научиться связывать данные с разных листов электронной книги.

Данные

В отделе кадров завода хранятся цеховые списки (таблица 3) и сведения об отработанных днях работников завода (таблица 4).

Таблица 3

Таблица 4

Таблица 5

Требуется

1) Разместить таблицы 3 и 4 на различных листах.

2) Переименовать листы соответственно в «Кадры» и «Рабочие дни».

3) Составить итоговый отчет (таблица 5) на третьем листе. Переименовать данный лист в “Расчет 3”.

4) Выполнить задания.

Заполнение таблиц

Для таблицы 3.

Колонки A, B, C, D заполняются произвольно (4-5 записей).

Колонка А имеет такой числовой формат, при котором недостающие до 5 цифр числа дополняются нулями. Колонки C и D имеют указанный в примере формат даты.

Колонка «Стаж работы» вычисляется по следующей формуле :

текущий год - год от «Даты поступления на завод». Для этого воспользоваться функциями ГОД и СЕГОДНЯ.

4. Колонка «Оклад» задается по следующей формуле:

случайное число от 200 до 5000. При вычислении использовать функцию ОКРУГЛ.

Для таблицы 4.

Колонка A - копия колонки A таблицы 1.

Колонки B, C, D заполняются произвольно числами между 28 и 0.

Колонка E высчитывается по формуле : «Количество рабочих дней (из них пропущено по больничному)» + «Количество рабочих дней (из них пропущено по другим причинам) ».

Поле «Количество отработанных дней» вычисляется по формуле: «Количество рабочих дней (в месяце)» - «Количество рабочих дней (из них пропущено всего)»

Для таблицы 5.

1. Колонка A - копия колонки A таблицы 1.

2. Колонка B переносится из колонки B таблицы 1. При перенесении воспользоваться функцией ЕСЛИ.

3. Колонка «Повременно» вычисляется по формуле :

(«Количество отработанных дней» / «Количество рабочих дней (в месяце)»)*«Оклад»

4. Колонка «Районный коэффициент» вычисляется по формуле :

30% от «Оклада» + 10% от «Оклада» за каждые 2 полных года работы. При вычислении использовать функцию ОКРУГЛВНИЗ.

5. Премия начисляется в размере оклада если человек не пропустил ни одного рабочего дня (смотри таблицу 4).

6. Колонка «Подоходный налог» вычисляется по формуле :

(«Повременно» + «Районный коэффициент» + «Премия») * 0,12.

7. Колонка «Пенсионный взнос» высчитывается по формуле :

(«Повременно» + «Районный коэффициент» + «Премия») * 0,01.

8. Колонка «Профсоюзный взнос» высчитывается по формуле :

(«Повременно» + «Районный коэффициент» + «Премия») * 0,015.

9. Колонка «Итого на руки» высчитывается по формуле :

(«Повременно» + «Районный коэффициент» + «Премия» ) - («Подоходный налог» + «Пенсионный взнос» + «Профсоюзный взнос» )

10. Для колонок "Подоходный налог", "Профсоюзный взнос", "Итого на руки" использовать числовой формат с округлением до двух знаков после запятой.

Задания (выполняются на листе «Расчет 3»)

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

1*. Подсчитать тремя способами сколько людей проболели более 10 дней. Для расчета по 1-му способу использовать функцию СЧЁТЕСЛИ, для расчета по 2-му способу- табличный вид формулы и функции ЕСЛИ, СЧЕТ, для расчета по 3-му способу - табличный вид формулы и функции ЕСЛИ, СУММ.

Подсчитать двумя способами сколько людей имеют оклад более 1000 р. Для расчета по 1-му способу использовать функцию СЧЁТЕСЛИ, для расчета по 2-му способу - табличный вид формулы и функции ЕСЛИ, СУММ.

Подсчитать количество людей пенсионного возраста, т.е. тех кому за 60 лет. Для расчета использовать табличный вид формулы и функции ЕСЛИ, СУММ, ГОД.

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

5*. Найти сколько людей получают самые большие оклады, т.е. отличие их окладов от максимального оклада не более чем на 10% от максимальной величины оклада. Для расчета использовать табличный вид формулы и функции ЕСЛИ, СУММ.

6*. Подсчитать сколько человек имеют больничный лист и получили на руки (колонка "Итого на руки") от 1000 до 20000 рублей. Для этого ввести отдельную строку внизу таблицы и озаглавить ее. Для расчета использовать табличный вид формулы и функции ЕСЛИ, СУММ.

Посчитать сумму выплаченной премии, которую получили работники за 20 и более дней работы и районным коэффициентом более 3000 р. Для расчета использовать табличный вид формулы и функции ЕСЛИ, СУММ.

Контрольные вопросы

Чем отличается табличная формула от простой?

Что вычисляют функции ТДАТА, ДАТАЗНАЧ, ВРЕМЯЗНАЧ, ВРЕМЯ? Что может являться значением их параметров?

Какие функции округления существуют в EXCELe?

Какие команды необходимо использовать чтобы добавить новый лист в книгу или убрать ненужный лист из книги?

Перечислите основные операции с листами.

Лабораторная работа 4. Работа с категорией «Ссылки массивы».

Цель:

• Овладеть навыками составления формул с использованием функций ВПР, ПОИСКПОЗ и ЕНД;
• Научиться работать с мастером функций.

Данные

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

Таблица 6

Таблица 7

Таблица 8