Работа с функцией ВПР. Автоматизация расчёта заработной платы работникам-повременщикам

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

Работа с функцией ВПР (пояснения)

ищет значение в крайнем левом столбце таблицы и возвращает значение в той же строке из указанного столбца таблицы

Задание 1.

1. На листе 1 заполнить табл.1

Табл.1

	A	B
1	Материал	Плотность, кг/куб.м
2	Стекло	2500
3	Бетон	2300
4	Резина	1200
5	Асбест	770
6	Никель	9000

2. На листе 2  заполнить табл.2

Табл. 2

	A	B	C
1	Материал	Объем материала, м3	Плотность, кг/куб.м
2	Никель	0,3
3	Резина	0,1

3. Необходимо в табл. 2 заполнить столбец С  соответствующими данными из табл.1 с помощью функции ВПР.

Порядок выполнения

1. Установить курсор в ячейку С2 таблицы 2.

2. Выбрать функцию ВПР

3. Открывается окно диалога функции ВПР

В поле Искомое_ значение  набрать адрес ключа таблицы 2 (ячейка А2 со словом Никель)

По этому ключу (признаку) мы будем искать соответствующие данные в табл.1.

В поле Табл_массив указать адрес массива исходных данных на листе 1 (это ячейки А2:В6 в табл.1)

Адрес должен быть абсолютным (для этого нажать функциональную клавишу F4 - появятся знаки доллара)

В поле Номер_индекса_столбца - указать № столбца Листа 1, из которого  переносятся данные на Лист 2 (т.к. нам нужна плотность материала, то берем данные из столбца 2)

В строке  Диапазон_просмотра -  набрать слово- ложь.

Нажать кнопку ОК.

Табл. 2 примет вид

	A	B	C
1	Материал	Объем материала, м3	Плотность, кг/куб.м
2	Никель	0,3	=ВПР(A2;Лист1!$A$2:$B$6;2;ЛОЖЬ)
3	Резина	0,1

4. Используя маркер заполнения, получить данные в ячейке С3.

Задание 2.

Автоматизация расчёта заработной платы работникам-повременщикам

1.   На листе 1 разместить таблицу:

Единая тарифная сетка по оплате труда

 работников бюджетной сферы

Разряды	Коэффициенты	Оклады (pv6.)	Минимум (руб.)
1	1		1200
2	1,3
3	1,69
4	1,91
5	2,16
6	2,44
7	2,76
8	3,12
9	3,53
10	3,99
11	4,51
12	5,1
13	5,76
14	6,51
15	7,36
16	8,17
17	9,07
18	10,07

При заполнении таблицы необходимо:

а) столбец Разряды заполнить, используя арифметическую прогрессию (Правка, Заполнить, Прогрессия);

б) оклад = минимум * коэффициент            (минимум — абсолютная ссылка);

2.  На листе 2 разместить:

Расчётная ведомость за январь 2006г.Норматив дней     20

№ п/п	ФИО	Отработано дней	Разряд	Оклад (руб.)	Тарифная ставка (руб.)	Начислено (руб.)	Р/к, 20% (руб.)	Начислено итого (руб.)	Налог с физ. лиц, 13%(руб.)	Сумма к выдаче "  (руб.)
1
2
3
4
5
6
7
8
9
10
					Итого:

При заполнении таблицы необходимо:

·  столбец №п/п заполнить, используя арифметическую прогрессию (Правка, Заполнить, Прогрессия);

·  столбец ФИО заполнить произвольными данными;

·  столбец Отработано дней заполнить произвольными данными (<20);

·  столбец Разряд заполнить произвольными данными (£ 18);

·  Оклад выбрать из Тарифной сетки, используя функцию ВПР (совпадение по разряду);

·  Тарифная ставка = Оклад / Норматив дней     (Норматив дней — абсолютная ссылка);

·  Начислено = Тарифная ставка * Отработано дней;

·  Р/к = Начислено * 20%;

·  Начислено итого = Начислено + Р/к;

·  Налог с физ.лиц = Начислено итого * 13%;

·  Сумма к выдаче = Начислено итого - Налог с физ.лиц;

·  Посчитать итого по столбцам, используя автосумму.

3.   На отдельных диаграммых листах построить:

·  гистограмму по Начислено итого, Налог с физических лиц и Сумма к выдаче (обозначить ФИО и подписать ряды);

·  линейчатую диаграмму по Начислено (обозначить ФИО);

·  смешанную диаграмму по Начислено итого и Сумма к выдаче (обозначить ФИО и подписать ряда).