Пример 2.
Требуется найти корень следующего алгебраического уравнения:
Для этого на рабочем листе введем данные:
Поместим в ячейку A3 некоторое начальное значение, например единицу2, а затем присвоим этой ячейке имя X:
Вставка Имя Присвоить… Имя := X
Теперь введем в ячейку B3 формулу преобразованного уравнения, перенеся все его элементы в одну часть:
=(2*X^2+3)*(1-SIN(X))-LN(X)-2
Рисунок 8
Теперь можно воспользоваться командой Подбор параметра для проведения итерационных вычислений и поиска корня:
Сервис
Подбор
параметра…
Рисунок 9
Если необходимо отыскать корень более точно (обычно по умолчанию итерации проводятся с точностью до четвертого знака после запятой), то можно предварительно увеличить требуемую точность с помощью изменения параметров вкладки Вычисления. Скажем, нам нужна точность до шестого знака после запятой:
Сервис Параметры Вычисления Относительная погрешность := 1E-6
После этого следует повторить итерационные вычисления с помощью Подбора параметра, и вы получите в ячейке A3 корень, равный 0,6357434.
Пример 3.
Составление штатного расписания хозрасчетной больницы
Постановка задачи. Заведующий хозрасчетной больницей должен составить штатное расписание, т.е. определить, сколько сотрудников, на каких должностях и с каким окладом он должен принять на работу. Общий месячный фонд зарплат составляет $10 000. Требуется получить такое штатное расписание, в котором предусмотрено оптимальное количество работников и достойная зарплата каждому.
Решение. Построим модель решения этой задачи.
Выясним, что является исходными данными. Казалось бы, ничего не надо, кроме общего фонда заработной платы. Однако заведующему известно больше, он знает, что для нормальной работы больницы нужно:
5-7 санитарок,
8-10 медсестер,
10-12 врачей,
3 заведующих отделениями,
1 главный врач,
1 заведующий хозяйством,
1 заведующий аптекой,
1 заведующий больницей.
На некоторых должностях число людей может варьироваться. Например, зная, что найти санитарок трудно, руководитель может принять решение о сокращении числа санитарок, чтобы увеличить оклад каждой из них.
Итак,
заведующий принимает следующую модель
задачи. За основу берется оклад санитарки
или минимальная заработная плата, а все
остальные вычисляются исходя из него:
во сколько-то раз или на столько-то
больше. Говоря математическим языком,
каждый оклад является линейной функцией
от оклада санитарки:
,
где С – минимальная зарплата, А и В –
коэффициенты, которые для каждой
должности определяются решением совета
трудового коллектива или заведующим.
Допустим, совет решил, что:
медсестра должна получать в 1,5 раза больше санитарки (А=1,5; В=0);
врач – в 3 раза больше санитарки (А=3; В=0);
зав. отделением – на $30 больше, чем врач (А=3; В=30);
зав. аптекой – в 2 раза больше санитарки (А=2; В=0);
завхоз – на $40 больше медсестры (А=1,5; В=40);
главврач – в 4 раза больше санитарки (А=4; В=0);
зав. больницей – на $20 больше главного врача (А=4; В=20);
Задав количество человек на каждой должности, можно составить уравнение:
где М1 – количество санитарок,
М2 – количество медсестер и т.д.
В этом запросе нам известны А1…А8 и В1…В8, а неизвестны С и М1…М8.
Ясно, что решить такое уравнение известными методами не удастся, т.к. единственно верного решения нет. Остается решать уравнение путем подбора. Взяв первоначально какие-либо приемлемые значения неизвестных, рассчитаем формулу. Если эта сумма равна фонду заработной платы, то нам повезло. Если фонд заработной платы превышен, то можно снизить оклад санитарки, либо отказаться от услуг какого-либо работника и т.д. Проделать такую работу вручную трудно, поэтому воспользуемся возможностями MS Excel.
Построение таблицы штатного расписания
Постройте таблицу по образцу (рис.6).
Отвести для каждой должности одну строку и вписать название должностей в столбец В.
В столбцах C и D указать соответственно коэффициенты А и В.
В какую-нибудь ячейку (например, в F18) занести минимальную заработную плату (в формате с фиксированной точкой и двумя знаками после нее) – множитель С.
Рядом подписать поясняющий текст – «Минимальная заработная плата». В столбце Е вычислить заработную плату для каждой должности по формуле .
Обратите внимание! Этот столбец должен заполняться формулой с использованием абсолютной ссылки на ячейку, в которой указана минимальная заработная плата. Изменение содержимого этой ячейки должно приводить к изменению содержимого всего столбца Е и пересчету всей таблицы
В столбце F указать количество сотрудников на соответствующих должностях в соответствии со штатным расписанием.
В столбце G вычислить заработную плату всех сотрудников больницы. Тогда сумма элементов столбца G даст суммарный фонд заработной платы. (Данные в столбцах Е, G представьте в формате с фиксированной точкой и двумя знаками после нее).
.
Рисунок 10 Пример таблицы – результат построения таблицы штатного расписания
Подбор параметра
Выделить щелчком мыши ту ячейку, для которой будут производиться вычисления. В нашем случае это G14 (именно она должна содержать формулу).
Выполнить команду Сервис Подбор параметра…. Появится диалоговое окно (Рис.11). Адрес выделенной ячейки ($G$14) уже содержится в верхнем его поле – Установить в ячейке (его можно изменить).
В поле Значение ввести результат, который необходимо получить. В нашем примере 10000.
В третьем поле Изменяя ячейку указать адрес ячейки, числовое значение в которой программа должна изменить для достижения нужного нам результата, или просто щелкнуть по ней курсором мыши. В нашем примере, ячейка $F$18.
Рисунок 11 Диалоговое окно Подбор параметра
Нажать кнопку [OK]. Это приведет к изменению значения в ячейке F18. Одновременно появится окно, отображающее состояние подбора параметра.
В случае, если найденное решение устраивает, нажать [OK].
Рисунок 12
Аналогичным образом можно подбирать другие параметры в гораздо более сложных таблицах, чем наша демонстрационная, т.к. существует возможность многовариантного подбора.
СОВЕТ
Подбор параметра можно выполнить на диаграмме с помощью перетаскивания маркера элемента данных. Чтобы получить более подробные сведения о подборе параметра на диаграмме, воспользуйтесь справкой MS Excel.