1.6 Лабораторная работа №2 Excel. Вычисления с использованием функций в связанных таблицах

Цель работы

• размещение таблиц на разных рабочих листах;

• использование функций различных категорий;

• использование табличных формул;

• перемещение, копирование, , удалениие, добавление и связывание рабочих листов;

• применение функции ВПР.

Данные

В техническом отделе завода имеется справочник работ (таблица 1.15). В отделе кадров хранятся цеховые списки (таблица 1.16) и сведения об отработанных днях работниками завода (таблица 1.17). В бухгалтерии производится начисление зарплаты (таблица 1.18).

Таблица 1.15 – Справочник работ

	A	B
1	Код работы	Название работы
2	024	Сварочная
3	026	Слесарная

Таблица 1.16 – Цеховые списки

	A	В	С	D	E	F
1	Табельный номер	Ф.И.О.	Дата поступления на завод	День рождения	Стаж работы	Оклад
2	00876	Петров Т.О.	12.09.1982	06.03.1961

Таблица 1.17 – Отработанные дни

	А	В	С	D	E	F
1	Табельный номер	Количество рабочих дней				Количество отработанных дней
2		в месяце	из них пропущено
3			по больничному	другие причины	всего
4	00876

Таблица 1.18 – Расчет

	A	B	C	D	E	F	G	H	I	J	K
1	РАСЧЕТ ЗАРАБОТНОЙ ПЛАТЫ
2	Табельный номер	Фамилия И.О.	Код работы	Название работы	Повре-менно	Районный коэфф.	Премия	Подохо-дный налог	Пенси-онный взнос	Проф. взнос	Итого на руки
3	00876	Петров Т.О.

Требуется:

1) Разместить таблицы 1.15, 1.16 и 1.17 на различных листах.

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

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

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

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

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

Столбцы A и B заполнить произвольно (5 записей).

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

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

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

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

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

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

случайное число от 5000 до 15000. При вычислении использовать функцию ЦЕЛОЕ.

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

1. Колонка A – копия колонки A таблицы 1.16 (воспользоваться ссылкой на ячейки таблицы 1.16, введя в ячейку A2 формулу =Кадры!A2).

2. Колонки B, C, D заполняются произвольно, причем «Количество рабочих дней в месяце» – одинаково для каждой строки (например, 24).

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

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

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

1. Колонки A и B – копии колонок A и B таблицы 1.16 (ссылки на соответствующие ячейки).

2. Колонка C заполняется повторяющимися кодами работ из таблицы 1.15.

3. Столбец «Название работы» вычисляется с помощью функции ВПР и значений из таблицы 1.15.

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

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

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

30% от «Оклада» + 10% от «Оклада» за каждые 2 полных года работы.

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

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

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

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

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

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

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

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

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

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