Тема 11. Подбор параметра

            Цель выполнения работы: освоить технологию работы со средством Excel – Подбор параметра для решения экономических и математических задач.

Средство подбор параметра определяет значение одной входной ячейки, которое обеспечивает получение желаемого результата в зависимой ячейке (в ячейке для результата). Другими словами, подбор параметра позволяет найти значение, которое требуется ввести в формулу, для получения нужного результата (когда известен требуемый ответ, имеется формула для вычисления этого ответа и существует только одно входное значение для получения этого ответа). Инструмент Подбор параметра может быть использован при выполнении двух условий:

·           в установленной ячейке должно быть число;

·           значения, находящиеся в установленной и изменяемой ячейке, должны быть связаны между собой функциональной зависимостью.

 

Решение финансовых задач с применением инструмента Подбор параметра.

Ситуация: предприятию необходимо получить кредит для закупки деталей, такая задача рассмотрена в теме 3, раздел – Проведение финансовых расчётов (было получено решение о величине кредита, который необходим для закупки деталей при выполнении производственной программы). Менеджер должен оценить условия получения кредита, провести всесторонний анализ возможных вариантов, а затем заключать договора на приобретение деталей. Если размер периодического платежа по кредиту не устраивает получателя, а кредитное учреждение отказывается снижать ставку, то перед менеджером возникает проблема варианта последующих действий. Действительно, для уменьшения платежей можно:

1)        Снизить размер кредита за счет уменьшения количества закупаемых деталей.

2)        Продлить срок выплаты кредитной задолженности.

3)        Добиться снижения кредитной ставки.

Предположим, что получатель кредита не в состоянии выплачивать более 1700 рублей, отсюда ясно, что необходимо подобрать такие параметры в задаче, чтобы достичь благоприятного результата (Видимо, придется снизить величину кредита).

Задание 1. Рассмотреть два альтернативных варианта обращения в банк для получения кредита. 1) определить сумму кредита, который следует получить в банке, чтобы ежемесячные выплаты по кредиту не превышали 1700 рублей при соблюдении условий банка, и вычислить, какое количество деталей можно будет приобрести на полученную сумму кредита; 2) на сколько месяцев можно взять кредит, чтобы выполнить договор с заказчиком (выпустить 15 комплектов изделий), а ежемесячно выплачивать не более 1700 рублей на погашение кредита.

Для решения поставленной задачи, в качестве отправной точки, воспользуемся решённым примером (рис. 1), в котором рассмотрена задача по использованию функции ПЛТ (Платежи).

Рис. 1. Начальные данные для проведения анализа данных с использованием инструмента – Подбор параметра

 

·           Создать таблицу с исходными данными (рис. 1) или скопировать из файла в новую книгу Excel.

·           Вызвать инструмент Подбор параметра. Вкладка «Данные»  Анализ «что - если» - Подбор параметра.

·           Ввести в диалоговое окно «Подбор параметра» условия, ячейка C12 должна содержать фиксированное значение  (в рассматриваемой задаче, устанавливается минус 1700 руб.), а изменяемой ячейкой будет С4 (это та ячейка, в которой должен отразится результат), как это показано на рис. 2.

Рис. 2. Так заполняется диалоговое окно Подбор параметра

 

После того как будет нажата кнопка ОК в окне Подбор параметра, произойдёт  изменения значений данных во всех ячейках, которые связаны между собой формулами (функционально). В рассматриваемом примере, на рис. 3 в таблицах Потребности деталей и Платежи в ячейках C4, C5, C8, C12 изменились значения. Посмотрите на окно «Результаты подбора параметра», в котором написано, что решение найдено. В отдельных случаях система будет сигнализировать, что не может найти решения, тогда потребуется изменить исходные данные.

 

Рис. 3. Результат подбора параметра при заданных условиях

 

Менеджер, который воспользовался инструментом Подбор параметра для решения поставленного задания, видит, что при фиксированном значении оплаты кредита (1700 руб. ежемесячно) на условиях банка, можно получить сумму в размере 38548, 65 руб., на которую можно закупить не более 11 деталей. Следовательно, составляя договор с заказчиком о выпуске оборудования, придётся учесть результаты проведённых вычислений с учётом возможности предприятия.

 

Решение математической задачи методом подбора параметра.

Задание 2. Построить график функции y = 2,34x2 – 3,12x + 1 в диапазоне изменения неизвестной x от 0,5 до 0,85, и найти (Вычислить) корни квадратного уравнения.

Известно, что уравнение описывает  зависимость одной переменной (функции) от второй, независимой, называемой аргументом. Корнем уравнения называется такое значение аргумента, при котором функция равняется 0. На графике функции корням уравнения соответствуют точки пересечения функции с осью абцисс.

Для нахождения всех корней уравнения путём подбора параметра целесообразно выполнить следующие действия:

·      Создать таблицу значений функции для n значений аргумента в заданном диапазоне. Для рассматриваемого примера, выбираем шаг изменения переменной равный 0,05, тогда получим результат в Excel, как показано на рис. 4.

Рис. 4. Таблица значений и график функции

 

·      Выбрать в созданной таблице ячейку (или ячейки), содержащую значение, наиболее близкое к точке перемены знака (от положительных значений к отрицательным или наоборот). В таблице на рис. 4 знак результата вычисления функции меняется в ячейке C5 и C9 (можно выбрать и ячейку C10).

·      Выбрать команду Подбор параметра, и занести в диалоговое окно адрес C5 – ячейка, в которой значение должно принимать ноль, а непосредственно подбор величины искомого корня уравнения должен быть в ячейке B5, как это показано на рис. 5.

 

Рис. 5. Диалоговые окна подбора параметра при задании условий и поиске решения

 

·      Для поиска второго значения корня квадратного уравнения, потребуется провести аналогичные действия с ячейками C9, B9.  В итоге будут получены два значения для корней квадратного уравнения x1 = 0,5535417, x2 = 0,7973565.

 

Вопросы для самоконтроля

  1. В каких случаях целесообразно использовать средство Excel – Подбор параметра?
  2. Какой адрес ячейки следует указывать в окне Подбор параметра для строки с наименованием «Установить в ячейке»?
  3. Какие предварительные исследования следует сделать, в случае использования средства Подбор параметра, при решении математических задач?