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

Пусть имеется формула, которая прямо или косвенно зависит от некоторого параметра. Задача состоит в определении такого значения этого параметра, которое позволяет получить нужный результат формулы.

Математическая суть задачи состоит в решении уравнения f(x)=a, где f(x) описывается заданной формулой, х – искомый параметр, а – требуемый результат формулы.

Для решения этой задачи необходимо выполнить следующие действия:

1. Выделить ячейку, содержащую формулу, для которой нужно найти определенное решение.

2. В меню Сервис выбрать команду Подбор параметра.

3. В поле Установить в ячейке ввести ссылку на ячейку, содержащую формулу.

4. В поле Значение ввести значение, которое нужно получить по заданной формуле.

5. В поле Изменяя ячейку ввести ссылку на ячейку, содержащую значение изменяемого параметра.

6. Щелкнуть по кнопке ОК.

Пример 1. Рассмотрим применение инструмента Подбор параметра для решения некоторого уравнения, например 2*x^3-3*x^2+x-5=0.

Решение задачи состоит из двух этапов. На первом этапе следует ввести уравнение в ячейку (В2), а в другую ячейку (А2) ввести некоторое ориентировочное значение, здесь 1 (рис.4.1)

Рис. 4.1

На втором этапе следует воспользоваться инструментом Подбор параметра:

1.Встаньте в ячейку В2 и вызовите его - Сервис/Подбор параметра

2.В открывшемся окне введите искомое значение функции и адрес изменяемой ячейки (рис. 4.2)

Рис. 4.2

При нажатии кнопки ОК вы получите решение (рис 5.3)

Рис. 4.3

Найденное решение приближенное, поэтому можно считать, что при х=1,918578609 значение уравнения 2х³-3х²+х-5 стремится к нулю, т.е. к 0, 000107348.

Установите для ячеек А2 и В2 числовой формат отображения данных и получите следующее (рис.4.4)

Рис.4.4

Следует отметить, что уравнение может иметь больше одного решения. Поэтому рекомендуется выполнить подбор параметра для разных начальных значений Х, указывая положительные, отрицательные, большие и малые значения. В нашем примере установите начальное значение А2=-1 и повторите подбор. Решение будет таким же.

Пример 2.

Определить экстремумы функции при условии .

1. В ячейки А1:А3 ввести для себя подсказки х=, у=, z=.

2. В ячейку В3 ввести формулу =3+2*В1-4*В2 (получим значение 3).

3. В ячейку В4 вводим условие =В1*В1+В2*В2 (получаем значение 0).

4. Выделим ячейку В3. В меню Сервис/Надстройки поставить флажок Поиск решения/ОК.

5. Выбираем меню Сервис/Поиск решения. В появившемся контекстном меню Поиск решения в строке Установить целевую ячейку щелкаем по ячейке В3 (получаем $B$3). В строке Изменяя ячейки выделить одновременно ячейки В1, В2. С помощью кнопки Выбираем равной отмечаем, например, максимальное значение; в строке Ограничения выбираем вкладку Добавить.

6. В появившемся контекстном меню в строке Ссылка на ячейку щелкаем по ячейке В4 (получаем $D$4), справа выбираем знак =, в строке Ограничения ставим 4. Нажимаем ОК и далее команду Выполнить.

7. Аналогичные вычислите минимальное значение, выполнив действия п.п. 5-6.