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

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

Excel позволяет легко решить уравнение с одним неизвестным. Поиск решения осуществляется методом итераций (от лат. – последовательное приближение). Сначала в изменяемой ячейке задается начальное (приближенное) значение искомого корня уравнения, а в целевую ячейку (т. е. ячейку результата) вводится решаемое уравнение. Затем необходимые данные указываются в окне «Подбор параметра» (вкладка «Данные»). Если начальное значение параметра не дает требуемого результата в целевой ячейке, то Excel автоматически делает следующий шаг итерационного процесса и находит новое значение параметра в изменяемой ячейке. Этот процесс продолжается до тех пор, пока не будет найдено нужное значение (при условии, что решение задачи существует).

Если при решении уравнения начальное приближение корня неизвестно, то можно построить таблицу значений функции или график и определить, на каком интервале изменения аргумента функция меняет знак.

Настройка команды «Подбор параметра»

Если пункта «Подбор параметра» во вкладке «Данные» нет, то его можно установить на панель быстрого доступа.

1. См п. 1-2 настройки команды «Поиск решения».

2. В окне «Параметры Excel» нажмите «Настройка» выберите из ниспадающего списка вкладок «Вкладка «Данные»», выделите команду «Подбор параметра», нажмите кнопку «Добавить» и «Ок» (рис 12).

Рис. 12. Настройка команды «Подбор параметра»

Пример решения трансцендентного (т.е. содержащего неалгебраические функции) уравнения x³=e^x .

Для удобства преобразуем это уравнение к виду x³ – e^x = 0. Для определения начального приближения корня протабулируем функцию шагом 0,5 и построим ее график, например, на отрезке [0;3] (рис. 13).

1. Внесем пояснительный текст в ячейки А1 и А2, А4 и В4 (рис. 13)

2. Внесем в ячейки А5: А11 значения аргумента х.

3. Введем в ячейку B5 соответствующую формулу для вычисления значений функции y= x³ – e^x (рис. 13)

Рис. 13. Вычисление значений функции y= x³ – e^x на отрезке [0;3]

4. Скопируйте формулу автозаполнением в ячейки В6:В11.

5. Постройте график функции используя вид диаграммы «Точечная».

6. График функции имеет следующий вид (рис.14)

Рис. 14. График функции y= x³ – e^x на отрезке [0;3]

7. Из таблицы значений функции и графика следует, что на отрезке xЄ[1,5; 2] функция меняет знак, т.е. на этом отрезке находится корень функции. Для уточнения его значения выберем в качестве начального приближения корня любую границу этого отрезка, например, 1,5.

8. Введем это значение в ячейку A14.

9. Для удобства присвоим ей имя x.

10. В ячейку B14 введем формулу – левую часть решаемого уравнения, т.е. =x^3-EXP(x).

11. Находясь в этой ячейке, т.е в В14 выполним команду «Подбор параметра», которая находится либо во вкладке «Данные», либо на панели быстрого доступа.

12. В диалоговом окне зададим необходимые параметры (рис. 15). При этом в окошке «Значение» вводится правая часть решаемого уравнения (в нашем случае 0). После внесения необходимых параметров нажимаем «Ок». Установки в окне «Подбор параметра» на рис. 15 поручают Excel перебирать значения в ячейке x (A14) до тех пор, пока зависящее от x выражение, записанное в ячейке B14, не достигнет заданного значения 0.

Рис. 15. Окно «Подбор параметра»

13. Если решение найдено, то появляется соответствующее окно (рис. 16). Нажимаем «Ок».

Рис. 16. Результат подбора параметра

14. По окончании процесса поиска получим значение корня x =1,85694 (рис. 17)

Рис. 17. Решение уравнения x³=e^x

По умолчанию команда Подборпараметра прекращает вычисления после выполнения 100 итераций или при получении результата, который находится в пределах 0,001 от заданного целевого значения.

Если найденное решение не устраивает пользователя, можно процесс поиска повторить, задав новое начальное приближение, точность вычисления и предельное число итераций.