Эксперт по сдаче вступительных испытаний в ВУЗах
Работа 4. Решение линейных уравнений с одной переменной в MS Excel
C помощью надстройки Подбор параметра в MS Excel реализован поиск значения параметра формулы, удовлетворяющего ее конкретному значению. Эту процедуру используют для поиска такого значения ячейки, при котором значение другой ячейки, вычисляемое по формуле, заранее задано. В формуле должна быть ссылка на ячейку, значение которой ищут. Ограничения на искомое значение ячейки не налагают.
Познакомимся с этой процедурой на примере расчета заработной платы.
Пусть известно, что в штате некоторой торговой компании состоит 7 кассиров, 2 старших кассира, 10 продавцов — консультантов, 2 секретаря, 2 кладовщика, 4 грузчика, 4 охранника, 1 начальник охраны, 2 администратора зала, 2 товароведа, 2 системных администратора, водитель, бухгалтер и директор. Общий месячный фонд зарплаты составляет 500000 р. Необходимо определить, какими должны быть оклады сотрудников компании.
Построим модель решения этой задачи. За основу возьмем оклад кассира, а остальные оклады будем вычислять, исходя из него. Говоря математическим языком, каждый оклад является линейной функцией от оклада кассира: A
i*С + В
i , где С – оклад кассира; А
i – коэффициенты, В
i – надбавки, которые для каждой должности определяют следующим образом:
- старшие кассиры, продавцы-консультанты и секретари получают в 1,5 раза больше кассира (А1=1,5; А2=1,5; А3 =1,5; В1 =0; В2 =0; В3 =0);
- кладовщики, грузчики, охранникии водитель получают в 2 раза больше кассира (А4=2; А5=2; А6 =2; А7 =2; А8 =2; В4 =0; В5 =0; В6 =0; В7 =0; В8 =0);
- системные администраторы– в 2,5 раза больше кассира (А11=2,5; В11=0) ;
- администраторы зала получают в 2 раза больше кассира и имеют надбавку 1000 руб.;
- начальник охраны получает в 3 раза больше кассира;
- товароведы – в 3 раза больше кассира (А12=3; В12=0) ;
- бухгалтер – в 4 раза больше кассира (А13=4; В13=0) ;
- директор – в 10 разбольше кассира (А14=10; В14=0) .
Зная количество человек на каждой должности, нашу модель можно записать как уравнение
N0*A0*C+N1*(A1*C+B1)+…+N14*(A14*C+B14 )= 500000 ,
где N
0 – число кассиров, N
1 – число старших кассиров и т.д.
В этом уравнении нам известны A
0 … A
14 , B
1 … B
14 и N
0 … N
14 , а С неизвестно.
Анализ уравнения показывает, что задача составления расписания свелась к решению линейного уравнения относительно С . Решим его.
Введите исходные данные в рабочий лист электронной таблицы:
|
A |
B |
C |
D |
E |
F |
G |
H |
| 1 |
Штатное расписание торговой компании |
|
|
| 2 |
Должность |
Коэффициент А |
Персональная надбавка В |
Зарплата сотрудника |
Количество сотрудников |
Суммарная зарплата |
|
Зарплата кассира |
| 3 |
Кассир |
1 |
|
= H$3*B3+C3 |
7 |
= D3*E 3 |
|
5500 |
| 4 |
Старший кассир |
1,5 |
|
|
|
|
|
|
| 5 |
Продавец-консультант |
1,5 |
|
|
|
|
|
|
| … |
… |
… |
|
|
|
|
|
|
| 16 |
Директор |
10 |
|
|
|
|
|
|
| 17 |
Итого: |
|
|
|
|
=СУММА (F3:F16) |
|
|
В ячейку H3 введем начальное значение зарплаты кассира, например, 5500 руб.
В столбце D вычислите заработную плату для каждой должности. Например, для ячейки D3 формула расчета имеет вид =B3*H$3+C3. Знак $ означает абсолютный адрес строки, т.е. при копировании данной формулы в ячейку D4 этот адрес не изменится. В данном случае используется абсолютный адрес ячейки H3, в которой находится зарплата кассира. Именно от этой зарплаты зависят все остальные зарплаты.
В столбце F вычислите заработную плату всех работников данной должности. Например, для ячейки F3 формула расчета имеет вид = D3*E3 . В ячейке F17 вычислите суммарную заработную плату торгового предприятия.
Определите оклад кассира так, чтобы суммарная зарплата по предприятию была равна заданному значению фонда оплаты труда, т.е. 500000 руб.:
- выделите ячейку F17 ;
- активизируйте команду Подбор параметраиз меню Сервис;
- в поле Установитьв ячейкепоявившегося окна введите ссылку на ячейку F17, содержащую формулу;
- в поле Значение наберите заданное число 500000;
- в поле Изменяя значение ячейкивведите ссылку на изменяемую ячейку H3и щелкните на кнопке ОК.
В результате выполненных действий в ячейке F17 будет установлено значение 500000, значение зарплаты кассира и всех других сотрудников изменятся. Надстройка Подбор параметра используется для решения линейных уравнений с одной переменной, в нашем случае такой переменной была зарплата кассира.
Дополнительное задание: выразите в денежном формате данные в ячейках C3:F16, H3 и F17 .
Сохраните файл.
Ссылка на первоисточник:
http://www.aitip.ru/
