Подбор параметра

Кафедра математического моделирования и информатики

УЧЕБНОЕ ПОСОБИЕ

ПО КУРСУ

"ИНФОРМАТИКА"

"Решение прикладных задач в Excel

Красноярск 2006

Подбор параметра

Часто при решении практических задач возникают ситуации, когда необходимо достичь какой-то конкретной цели. Например, необходимо чтобы себестоимость продукции составляла 20 рублей.

Специфика таких задач состоит в том, что в Вашем распоряжении есть математическая модель исследуемого процесса, например, закон ценообразования, но Вы не знаете, при каком значении входящего в нее параметра можно достичь поставленную цель.

Решение таких задач можно искать методом перебора. Однако в лучшем случае на это уходит много времени.

Можно предложить другие способы решения. В Excel они реализованы как поиск значения параметра формулы, удовлетворяющего ее конкретному значению.

Эту процедуру используют для поиска такого значения ячейки, при котором значение другой ячейки, вычисляемое по формуле, заранее задано. В формуле должна быть ссылка на ячейку, значения которой ищут. Ограничения на искомое значение ячейки не налагают.

Познакомимся с этой процедурой на примере.

Предприятие решило выбросить на рынок новый продукт, от реализации 10 000 единиц которого, намеренно получить прибыль в 100 тыс. рублей.

При изготовлении единицы продукта расходы фирмы составили:

¨ Затраты (материалы, экспл. Оборудования, трансп. и др.) = 0,1*Цена ед. продукта.

¨ Зарплата = 0,3*Цена ед. продукта

¨ Реклама = 0,05*Цена ед. продукта

¨ Пр. расходы (расходы связанные с продажей изделия) = 0,1*Цена ед. продукта

¨ Расходы = Затраты +Зарплата +Реклама +Прочие

¨ Прибыль = Количество * Цена - Расходы * Цена

· Введите все необходимые формулы (Рис. 1) · активизируйте команду Подбор параметра из меню Сервис; · в поле "Установить в ячейке" появившегося окна введите ссылку на ячейку B8, содержащую формулу;

Рис. 1

· в поле "Значение" наберите искомый результат 100 000;

· в поле "изменяя значение ячейки" введите ссылку на изменяемую ячейку B2 и щелкните на кнопке ОК.

Сохраните таблицу в личном каталоге.

Анализ задачи показывает, что с помощью Excel можно решать уравнения. Конечно, такое уравнение может решить любой школьник. Однако, благодаря этому простому примеру стало, очевидным, что поиск значения параметра формулы, удовлетворяющего ее конкретному значению, - это не что иное, как численное решение уравнений. Другими словами, используя Excel, можно решать любые уравнения с одной переменной.

Контрольное задание 1.1.

Дана функция прибыли Q =2P+20, где Q – прибыль, а P – цена.

Используя операцию Подбор параметра, определите цену, при которой прибыль будет равна 100 000 рублей.