2. Решение алгебраических уравнений

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

• подбор параметра – позволяет отыскать значение одного неизвестного параметра, от которого зависит результат;

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

2.1. Инструмент Подбор параметра

Для поиска значения одного неизвестного параметра можно подставлять различные значения в заданное уравнение до тех пор, пока не будет достигнуто требуемое равенство. Однако при использовании инструмента подбор параметра искомое значение можно получить гораздо быстрее.

Для этого необходимо в какую-либо из ячеек ввести предполагаемое (начальное) значение корня уравнения, в другой ячейке записать само уравнение f(x), на вкладке Данные в группе Работа с данными выбрать команду Анализ «что-если»  Подбор параметра. В раскрывшемся диалоговом окне Подбор параметра (рис. 1.13) необходимо задать ссылки на соответствующие ячейки.

Рис. 1.13. Диалоговое окно Подбор параметра

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

После нажатия кнопки ОК, появится окно Результат подбора параметра, где Excel сообщит, что решение найдено (рис.1.14).

Рис. 1.14. Диалоговое окно Подбор параметра

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

2.2. Инструмент Поиск решения

Одной из интереснейших особенностей Excel является возможность нахождения корней уравнений типа f(x) = a. Эта возможность доступна, если установлена надстройка Поиск решения. Для этого необходимо в какую-либо из ячеек ввести предполагаемое (начальное) значение корня уравнения, в другой ячейке записать само уравнение f(x), на вкладке Данные в группе Анализ выбрать команду Поиск решения. В окне Поиск решения (рис. 1.15) в поле Установить целевую ячейку записать адрес ячейки, где записано уравнение f(x), в поле Равной:  Значению ввести значение a, в поле Изменяя ячейки ввести адрес ячейки, где находится предполагаемое (начальное) значение корня уравнения, и нажать кнопку Выполнить.

Рис. 1.15. Диалоговое окно Поиск решения

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

Рис. 1.16. Диалоговое окно Параметры поиска решения

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

Замечание 1. Начальное значение необходимо подбирать с определенной осторожностью, если функция, в которой ищутся корни, имеет различные особенности, так как при поиске решения начальное значение может находиться в области отрицательных градиентов, в результате чего «спираль» поиска может быть не сходящейся, а расходящейся.

Замечание 2. Если команда Поиск решения или группа Анализ отсутствует, необходимо загрузить надстройку (вспомогательную программу, служащую для добавления в Microsoft Office специальных команд или возможностей.) Поиск решения.

Для этого необходимо щелкнуть значок Microsoft Office , щелкнуть Параметры Excel, а затем выбрать категорию Надстройки. В поле Управление выбрать значение Надстройки Excel и нажать кнопку Перейти. В поле Доступные надстройки установить флажок рядом с пунктом Поиск решения и нажать кнопку ОК. При этом произойдет установка соответствующей настройки на ваш компьютер.