| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
Решение прикладных задач в Excel 1. Решение уравнений Часто при решении практических задач возникают ситуации, когда необходимо достичь какой-то конкретной цели. Например, необходимо чтобы себестоимость продукции составляла 20 грн. Специфика таких задач состоит в том, что в Вашем распоряжении есть математическая модель исследуемого процесса, например, закон ценообразования, но Вы не знаете, при каком значении входящего в нее параметра1) можно достичь поставленную цель. Решение таких задач можно искать методом перебора. Однако в лучшем случае на это уходит много времени. Можно предложить другие способы решения. В Excel они реализованы как поиск значения параметра формулы, удовлетворяющего ее конкретному значению.
Познакомимся с этой процедурой на примере составления штатного расписания. Пусть известно, что в штате больницы состоит 6 санитарок, 8 медсестер, 10 врачей, 3 заведующих отделениями, главный врач, заведующий аптекой, заведующая хозяйством и заведующий больницей. Общий месячный фонд зарплаты составляет 10 000 грн. Необходимо определить, какими должны быть оклады сотрудников больницы. Построим модель решения этой задачи. За основу возьмем оклад санитарки, а остальные оклады будем вычислять, исходя из него: во столько-то раз или на столько-то больше. Говоря математическим языком, каждый оклад является линейной функцией от оклада санитарки: Ai*С+Вi, где С - оклад санитарки; Аi и Вi - коэффициенты, которые для каждой должности определяют следующим образом:
Зная количество человек на каждой должности, нашу модель можно записать как уравнение N1*A1*C+N2*(A2*C+B2)+...+N8*(A8*C+B8) = 10000, где N1 - число санитарок, N2 - число медсестер и т.д. В этом уравнении нам известны A1...A8, B1...B8 и N1... N8, а С неизвестно. Анализ уравнения показывает, что задача составления расписания свелась к решению линейного уравнения относительно С. Решим его. Введите исходные данные в рабочий лист электронной таблицы, как показано ниже. В столбце D вычислите заработную плату для каждой должности. Например, для ячейки D4 формула расчета имеет вид =B4*$H$8+C4. В столбце F вычислите заработную плату всех рабочих данной должности. Например, для ячейки F4 формула расчета имеет вид =D4*E4. В ячейке F12 вычислите суммарный фонд заработной платы больницы. Рабочий лист электронной таблицы будет выглядеть, как показано ниже. Определите оклад санитарки так, чтобы расчетный фонд был равен заданному:
Сохраните таблицу в личном каталоге под именем hospital.xls. Анализ задачи показывает, что с помощью Excel можно решать линейные уравнения. Конечно, такое уравнение может решить любой школьник. Однако, благодаря этому простому примеру стало, очевидным, что поиск значения параметра формулы, удовлетворяющего ее конкретному значению, - это не что иное, как численное решение уравнений. Другими словами, используя Excel, можно решать любые уравнения с одной переменной. |