КОНТРОЛЬНАЯ РАБОТА Варианты заданий (режим подбора параметра) с использованием Microsoft Excel

Заполните данными и рассчитайте таблицу согласно варианту
Используя режим подбора параметра, определите штатное расписание фирмы. Исходные данные проведены в таблице.

ДЛЯ ВСЕХ ВАРИАНТОВ
Известно, что в штате фирмы состоят:
6 курьеров;
8 младших менеджеров;
10 менеджеров;
3 заведующих отделами;
1 главный бухгалтер;
1 программист;
1 системный аналитик;
1 генеральный директор фирмы.


A
B
C
D
E
F

1
Штатное расписание фирмы

2







3

Зарплата курьера
?



4







5
Должность
Коэф. А
Коэф. В
Зарплата сотрудников
Кол-во сотрудников
Суммарная зарплата

6
Курьер
1
0
?
6
?

7
Младший менеджер
1,5
0
?
8
?

8
Менеджер
3
0
?
10
?

9
Зав.отделом
3
1000
?
3
?

10
Глав. Бухгалтер
5
0
?
1


11
Программист
1,5
1500
?
1
?

12
Системный аналитик
4
0
?
1
?

13
Ген. директор
5
2000
?
1
?

14


Фонд заработной платы:
?

15







Общий месячный фонд зарплаты составляет 100 000 руб.
Необходимо определить, какими должны быть оклады сотрудников фирмы.
Каждый оклад является линейной функцией от оклада курьера, а именно:
Зарплата = Аi *X+Bi ,
где Х – оклад курьера;
Аi и Bi – коэффициенты, показывающие:
Аi – во сколько раз превышается значение Х,
Bi – на сколько превышается значение Х.
Порядок работы.
Запустите редактор электронных таблиц Microsoft Excel.
Создай таблицу штатного расписания фирмы по приведенному образцу (Штатное расписание фирмы). Введите исходные данные в рабочий лист электронной книги.
Выделите отдельную ячейку D3 для зарплаты (переменная «Х») и все расчеты задайте с учетом этого. В ячейку D3 временно введите произвольное число.
В столбце D введите формулу для расчета заработной платы по каждой должности. Например, для ячейки D6 формула расчета имеет вид: = В6*$D$3+С6 (ячейка D3 задана виде абсолютной адресации).Далее скопируйте формулу из ячейки D6 вниз по столбцу автокопированием интервале ячеек D6 : D13.
В столбце F задайте формулу расчета заработной платы всех работающих в данной должности. Например, для ячейки F6 формула расчета имеет вид: = D6*Е6. Далее скопируйте формулу из ячейки F6 вниз по столбцу автокопированием в интервале ячеек F6: F13.
В ячейке F14 вычислите суммарный фонд заработной платы фирмы.
Произведите подбор зарплат сотрудников фирмы для суммарной заработной платы в сумме 100 000 руб. Для этого в меню Сервис активизируйте команду Подбор параметра
В поле Установите в ячейке появившегося окна введите ссылку на ячейку F14, содержащую формулу расчета фонда заработной платы.
В поле Значение наберите искомый результат 100 000.
В поле Изменяя значение ячейки введите ссылку на изменяемую ячейку D3, в которой находится значение зарплаты курьера, и щелкните по кнопке ОК. Произойдет обратный расчет зарплаты сотрудников по заданному условию при фонде зарплаты, равном 100 000 руб.
Сохраните созданную электронную книгу под именем «Штатное расписание» в своей папке.

ЗАДАНИЕ ПО ВАРИАНТАМ
Используя режим подбора параметров и таблицу расчета штатного расписания(Штатное расписание фирмы), определите величину заработной платы сотрудника фирмы для ряда заданных значений фонда заработной платы.

Порядок работы

Выберите коэффициенты уравнения для расчета согласно таблице «Выбор исходных данных» (1 – 15 ВАРИАНТОВ РАСЧЕТОВ).

Методом подбора параметра последовательно определите зарплаты сотрудника фирмы для различных значений фонда заработной платы: 100 000, 150 000,200 000, 250 000, 300 000, 350 000, 400 000 руб.
Результаты подбора значений зарплат скопируйте в таблицу «Результаты подбора значения заработной платы» в виде специальной вставки.
Краткая справка. Для копирования результатов расчетов в виде значений необходимо выделить копируемые данные, произвести запись в буфер памяти (Правка/Копировать), установить курсор в первую ячейку таблицы ответов соответствующего столбца, задать режим специальной вставки (Правка/Специальная вставка), отменив в качестве объекта вставки – значения ( Правка/Специальная вставка/вставить - Значения).
Специальная вставка данных в виде значений позволяет копировать данные, полученные в результате расчетов, без дальнейшей зависимости их от пересчета формул.

Выбор исходных данных

Должность
Вариант 1
Вариант 2
Вариант 3
Вариант 4
Вариант 5


Коэффициенты


А
В
А
В
А
В
А
В
А
В

Курьер
1
0
1
0
1
0
1
0
1
0

Младший менеджер
1,2
500
1,3
0
1,3
700
1,4
0
1,45
500

Менеджер
2,5
800
2,6
500
2,7
700
2,6
300
2,5
1000

Зав.отделом
3
1500
3,1
1200
3,2
800
3,3
700
3,1
1000

Глав.бухгалтер
4
1000
4,1
1200
4,2
500
4,3
0
4,2
1200

Программист
1,5
1200
1,6
800
1,7
500
1,6
1000
1,5
1300

Системный аналитик
3,5
2000
3,6
500
3,7
800
3,6
1000
3,5
1500

Ген.директор
5
2500
5,2
2000
5,3
1500
5,5
1000
5,4
3000



Должность
Вариант 6
Вариант 7
Вариант 8
Вариант 9
Вариант1 0


Коэффициенты


А
В
А
В
А
В
А
В
А
В

Курьер
1
0
1
0
1
0
1
0
1
0

Младший менеджер
1,4
600
1,6
0
1,6
800
1,8
0
1,55
600

Менеджер
3,5
900
3,6
700
3,7
800
3,6
600
3,5
2000

Зав.отделом
4
1,800
4,1
1400
4,2
900
4,4
800
4,1
2500

Глав.бухгалтер
5
2000
4,9
1300
5,2
600
5,3
1000
5,2
1300

Программист
1,6
1400
1,7
900
1,8
600
1,7
2000
1,6
1400

Системный аналитик
5,5
1000
4,8
600
5,7
900
5,6
2000
5,5
1700

Ген.директор
6
2600
5,2
3000
6,3
1600
6,0
2000
6,1
2800




Должность
Вариант11
Вариант 12
Вариант 13
Вариант 14
Вариант 15


Коэффициенты


А
В
А
В
А
В
А
В
А
В

Курьер
1
300
1
400
1
0
1
0
1
0

Младший менеджер
1,5
900
1,6
300
1,8
500
1,5
150
3,45
400

Менеджер
2,0
400
2,5
300
2,1
600
2,8
200
1,5
1000

Зав.отделом
2
1400
2,1
1300
2,2
700
2,2
600
2,1
1100

Глав.бухгалтер
3,5
800
2,8
1150
3,1
600
3,1
700
4,1
1155

Программист
2
1000
1,3
500
1,6
600
1,7
650
1,9
3000

Системный аналитик
3,1
1500
2,6
200
3,5
200
1,6
2000
1,1
5000

Ген.директор
3,9
3000
3,2
2500
3,8
3000
3,6
2500
3,3
8000



Результаты подбора значения заработной платы

Фонд заработной платы руб.
100 000
150 000
200 000
250 000
300 000
350 000
400 000

Должность
Зарплата сотрудника
Зарплата сотрудника
Зарплата сотрудника
Зарплата сотрудника
Зарплата сотрудника
Зарплата сотрудника
Зарплата сотрудника

Курьер
?
?
?
?
?
?
?

Младший менеджер
?
?
?
?
?
?
?

Менеджер
?
?
?
?
?
?
?

Зав. Отделом
?
?
?
?
?
?
?

Глав.бухгалтер
?
?
?
?
?
?
?

Программист
?
?
?
?
?
?
?

Системный аналитик
?
?
?
?
?
?
?

Ген. директор
?
?
?
?
?
?
?














Заголовок 1 ђЗаголовок 2Заголовок 3 ђЗаголовок 4сђЗаголовок 5{ђЗаголовок 6{ђЗаголовок 7zђЗаголовок 8
ђЗаголовок 915