Решение уравнений в эксель in

Решение системы уравнений в Microsoft Excel

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

Варианты решений

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

Способ 1: матричный метод

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

Заполняем матрицу числами, которые являются коэффициентами уравнения. Данные числа должны располагаться последовательно по порядку с учетом расположения каждого корня, которому они соответствуют. Если в каком-то выражении один из корней отсутствует, то в этом случае коэффициент считается равным нулю. Если коэффициент не обозначен в уравнении, но соответствующий корень имеется, то считается, что коэффициент равен 1. Обозначаем полученную таблицу, как вектор A.

Отдельно записываем значения после знака «равно». Обозначаем их общим наименованием, как вектор B.

Аргумент «Массив» — это, собственно, адрес исходной таблицы.

Итак, выделяем на листе область пустых ячеек, которая по размеру равна диапазону исходной матрицы. Щелкаем по кнопке «Вставить функцию», расположенную около строки формул.

Выполняется запуск Мастера функций. Переходим в категорию «Математические». В представившемся списке ищем наименование «МОБР». После того, как оно отыскано, выделяем его и жмем на кнопку «OK».

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

Теперь нам нужно будет умножить обратную матрицу на матрицу B, которая состоит из одного столбца значений, расположенных после знака «равно» в выражениях. Для умножения таблиц в Экселе также имеется отдельная функция, которая называется МУМНОЖ. Данный оператор имеет следующий синтаксис:

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

В категории «Математические», запустившегося Мастера функций, выделяем наименование «МУМНОЖ» и жмем на кнопку «OK».

Активируется окно аргументов функции МУМНОЖ. В поле «Массив1» заносим координаты нашей обратной матрицы. Для этого, как и в прошлый раз, устанавливаем курсор в поле и с зажатой левой кнопкой мыши выделяем курсором соответствующую таблицу. Аналогичное действие проводим для внесения координат в поле «Массив2», только на этот раз выделяем значения колонки B. После того, как вышеуказанные действия проведены, опять не спешим жать на кнопку «OK» или клавишу Enter, а набираем комбинацию клавиш Ctrl+Shift+Enter.

Способ 2: подбор параметров

Второй известный способ решения системы уравнений в Экселе – это применение метода подбора параметров. Суть данного метода заключается в поиске от обратного. То есть, основываясь на известном результате, мы производим поиск неизвестного аргумента. Давайте для примера используем квадратное уравнение

Принимаем значение x за равное 0. Высчитываем соответствующее для него значение f(x), применив следующую формулу:

Вместо значения «X» подставляем адрес той ячейки, где расположено число 0, принятое нами за x.

Переходим во вкладку «Данные». Жмем на кнопку «Анализ «что если»». Эта кнопка размещена на ленте в блоке инструментов «Работа с данными». Открывается выпадающий список. Выбираем в нем позицию «Подбор параметра…».

Запускается окно подбора параметров. Как видим, оно состоит из трех полей. В поле «Установить в ячейке» указываем адрес ячейки, в которой находится формула f(x), рассчитанная нами чуть ранее. В поле «Значение» вводим число «0». В поле «Изменяя значения» указываем адрес ячейки, в которой расположено значение x, ранее принятое нами за 0. После выполнения данных действий жмем на кнопку «OK».

После этого Эксель произведет вычисление с помощью подбора параметра. Об этом сообщит появившееся информационное окно. В нем следует нажать на кнопку «OK».

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

Способ 3: метод Крамера

Теперь попробуем решить систему уравнений методом Крамера. Для примера возьмем все ту же систему, которую использовали в Способе 1:

Как и в первом способе, составляем матрицу A из коэффициентов уравнений и таблицу B из значений, которые стоят после знака «равно».

Далее делаем ещё четыре таблицы. Каждая из них является копией матрицы A, только у этих копий поочередно один столбец заменен на таблицу B. У первой таблицы – это первый столбец, у второй таблицы – второй и т.д.

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

Таким образом, как и у функции МОБР, единственным аргументом выступает ссылка на обрабатываемую таблицу.

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

Активируется окно Мастера функций. Переходим в категорию «Математические» и среди списка операторов выделяем там наименование «МОПРЕД». После этого жмем на кнопку «OK».

Запускается окно аргументов функции МОПРЕД. Как видим, оно имеет только одно поле – «Массив». В это поле вписываем адрес первой преобразованной матрицы. Для этого устанавливаем курсор в поле, а затем выделяем матричный диапазон. После этого жмем на кнопку «OK». Данная функция выводит результат в одну ячейку, а не массивом, поэтому для получения расчета не нужно прибегать к нажатию комбинации клавиш Ctrl+Shift+Enter.

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

Аналогичным образом производим подсчет определителей для остальных трех таблиц.

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

Способ 4: метод Гаусса

Решить систему уравнений можно также, применив метод Гаусса. Для примера возьмем более простую систему уравнений из трех неизвестных:

Опять последовательно записываем коэффициенты в таблицу A, а свободные члены, расположенные после знака «равно» — в таблицу B. Но на этот раз сблизим обе таблицы, так как это понадобится нам для работы в дальнейшем. Важным условием является то, чтобы в первой ячейке матрицы A значение было отличным от нуля. В обратном случае следует переставить строки местами.

Копируем первую строку двух соединенных матриц в строчку ниже (для наглядности можно пропустить одну строку). В первую ячейку, которая расположена в строке ещё ниже предыдущей, вводим следующую формулу:

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

После того, как формула введена, выделите весь ряд ячеек и нажмите комбинацию клавиш Ctrl+Shift+Enter. К ряду будет применена формула массива и он будет заполнен значениями. Таким образом мы произвели вычитание из второй строки первой, умноженной на отношение первых коэффициентов двух первых выражений системы.

После этого копируем полученную строку и вставляем её в строчку ниже.

Выделяем две первые строки после пропущенной строчки. Жмем на кнопку «Копировать», которая расположена на ленте во вкладке «Главная».

Пропускаем строку после последней записи на листе. Выделяем первую ячейку в следующей строке. Кликаем правой кнопкой мыши. В открывшемся контекстном меню наводим курсор на пункт «Специальная вставка». В запустившемся дополнительном списке выбираем позицию «Значения».

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

После ввода формулы выделяем весь ряд и применяем сочетание клавиш Ctrl+Shift+Enter.

Теперь следует выполнить обратную прогонку по методу Гаусса. Пропускаем три строки от последней записи. В четвертой строке вводим формулу массива:

Таким образом, мы делим последнюю рассчитанную нами строку на её же третий коэффициент. После того, как набрали формулу, выделяем всю строчку и жмем сочетание клавиш Ctrl+Shift+Enter.

Поднимаемся на строку вверх и вводим в неё следующую формулу массива:

Жмем привычное уже нам сочетание клавиш для применения формулы массива.

Поднимаемся ещё на одну строку выше. В неё вводим формулу массива следующего вида:

Опять выделяем всю строку и применяем сочетание клавиш Ctrl+Shift+Enter.

Как видим, в Экселе систему уравнений можно решить целым рядом способов, каждый из которых имеет собственные преимущества и недостатки. Но все эти методы можно условно разделить на две большие группы: матричные и с применением инструмента подбора параметров. В некоторых случаях не всегда матричные методы подходят для решения задачи. В частности тогда, когда определитель матрицы равен нулю. В остальных же случаях пользователь сам волен решать, какой вариант он считает более удобным для себя.

Помимо этой статьи, на сайте еще 12680 инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Графический способ решения уравнений в среде Microsoft Excel 2007

Тип урока: Обобщение, закрепление пройденного материала и объяснение нового.

Цели и задачи урока:

• повторение изученных графиков функций;
• повторение и закрепление графического способа решения уравнений;
• закрепление навыков записи и копирования формул, построения графиков функций в электронных таблицах Excel 2007;
• формирование и первичное закрепление знаний о решении уравнений с использованием возможностей электронных таблиц Excel 2007;
• формирование мышления, направленного на выбор оптимального решения;
• формирование информационной культуры школьников.

Оборудование: персональные компьютеры, мультимедиапроектор, проекционный экран.

Материалы к уроку: презентация Power Point на компьютере учителя (Приложение 1).

Слайд 1 из Приложения1 ( далее ссылки на слайды идут без указания Приложения1).

Объявление темы урока.

1. Устная работа (актуализация знаний).

Слайд 2 — Соотнесите перечисленные ниже функции с графиками на чертеже (Рис. 1):

у = 6 — х; у = 2х + 3; у = (х + 3) 2 ; у = -(х — 4) 2 ; .

Слайд 3 Графический способ решения уравнений вида f(x)=0.

Корнями уравнения f(x)=0 являются значения х₁, х₂, … точек пересечения графика функции y=f(x) с осью абсцисс (Рис. 2).

Найдите корни уравнения х 2 -2х-3=0, используя графический способ решения уравнений (Рис.3).

Слайд 5 Графический способ решения уравнений вида f (x)=g (x).

Корнями уравнения f(x)=g(x) являются значения х₁, х₂, … точек пересечения графиков функций y=f(x) и у=g(x). (Рис. 4):

Слайд 6 Найдите корни уравнения , используя графический способ решения уравнений (Рис. 5).

2. Объяснение нового материала. Практическая работа.

Решение уравнений графическим способом требует больших временных затрат на построение графиков функций и в большинстве случаев дает грубо приближенные решения. При использовании электронных таблиц, в данном случае – Microsoft Excel 2007, существенно экономится время на построение графиков функций, и появляются дополнительные возможности нахождения корней уравнения с заданной точностью (метод Подбор параметра).

I. Графический способ решения уравнений вида f(x)=0 в Excel.

Дальнейшая работа выполняется учителем в Excel одновременно с учениками с подробными (при необходимости) инструкциями и выводом результатов на проекционный экран. Слайды Приложения 1 используются для формулировки задач и подведения промежуточных итогов.

Пример1: Используя средства построения диаграмм в Excel, решить графическим способом уравнение —х 2 +5х-4=0.

Для этого: построить график функции у=-х 2 +5х-4 на промежутке [ 0; 5 ] с шагом 0,25; \найти значения х точек пересечения графика функции с осью абсцисс.

Выполнение задания можно разбить на этапы:

1 этап: Представление функции в табличной форме (рис. 6):

• в ячейку А1 ввести текст Х, в ячейку A2 — Y;
• в ячейку В1 ввести число 0, в ячейку С1 – число 0,25;
• выделить ячейки В1:С1, подвести указатель мыши к маркеру выделения, и в тот момент, когда указатель мыши примет форму черного крестика, протянуть маркер выделения вправо до ячейки V1 (Рис. 7).

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

После ввода формулы в ячейке окажется результат вычисления по формуле, а в поле ввода строки формул — сама формула (Рис. 8):

• скопировать содержимое ячейки B2 в ячейки C2:V2 за маркер выделения. Весь ряд выделенных ячеек заполнится содержимым первой ячейки. При этом ссылки на ячейки в формулах изменятся относительно смещения самой формулы.

2 этап: Построение диаграммы типа График.

• выделить диапазон ячеек B2:V2;
• на вкладке Вставка|Диаграммы|График выбрать вид График;
• на вкладке Конструктор|Выбрать данные (Рис. 9) в открывшемся окне «Выбор источника данных» щелкнуть по кнопке Изменить в поле Подписи горизонтальной оси — откроется окно «Подписи оси». Выделить в таблице диапазон ячеек B1:V1 (значения переменной х). В обоих окнах щелкнуть по кнопкам ОК;

• на вкладке Макет|Оси|Основная горизонтальная ось|Дополнительные параметры основной горизонтальной оси выбрать:

Интервал между делениями: 4;

Интервал между подписями: Единица измерения интервала: 4;

Положение оси: по делениям;

Выбрать ширину и цвет линии (Вкладки Тип линии и Цвет линии);

• самостоятельно изменить ширину и цвет линии для вертикальной оси;
• на вкладке Макет|Сетка|Вертикальные линии сетки по основной оси выбрать Основные линии сетки.

Примерный результат работы приведен на рис. 10:

3 этап: Определение корней уравнения.

График функции у=-х 2 +5х-4 пересекает ось абсцисс в двух точках и, следовательно, уравнение -х 2 +5х-4=0 имеет два корня: х₁=1; х₂=4.

II. Графический способ решения уравнений вида f(x)=g(x) в Excel.

Пример 2: Решить графическим способом уравнение .

Для этого: в одной системе координат построить графики функций у₁= и у₂=1-х на промежутке [ -1; 4 ] с шагом 0,25; найти значение х точки пересечения графиков функций.

1 этап: Представление функций в табличной форме (рис. 1):

Аналогично Примеру 1, применив приемы копирования, заполнить таблицу. При табулировании функции у₁=воспользоваться встроенной функцией Корень (Рис. 11).

2 этап: Построение диаграммы типа График.

Примерный результат работы приведен на Рис. 12:

3 этап: Определение корней уравнения.

Графики функций у₁= и у₂=1-х пересекаются в одной точке (0;1) и, следовательно, уравнение имеет один корень – абсцисса этой точки: х=0.

III. Метод Подбор параметра.

Графический способ решения уравнений красив, но далеко не всегда точки пересечения могут быть такими «хорошими», как в специально подобранных примерах 1 и 2.

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

Пример 3: Разберем метод Подбор параметра на примере решения уравнения —х 2 +5х-3=0.

1 этап: Построение диаграммы типа График для приближенного определения корней уравнения.

Построить график функции у=—х 2 +5х-3, отредактировав полученные в Примере 1 формулы.

• выполнить двойной щелчок по ячейке B2, внести необходимые изменения;
• с помощью маркера выделения скопировать формулу во все ячейки диапазона C2:V2.

Все изменения сразу отобразятся на графике.

Примерный результат работы приведен на Рис. 13:

2 этап: Определение приближенных значений корней уравнения.

График функции у=-х 2 +5х-3 пересекает ось абсцисс в двух точках и, следовательно, уравнение -х 2 +5х-4=0 имеет два корня.

По графику приближенно можно определить, что х₁≈0,7; х₂≈4,3.

3 этап: Поиск приближенного решения уравнения с заданной точностью методом Подбор параметра.

1) Начать с поиска более точного значения меньшего корня.

По графику видно, что ближайший аргумент к точке пересечения графика с осью абсцисс равен 0,75. В таблице значений функции этот аргумент размещается в ячейке E1.

• Выделить ячейку Е2;
• перейти на вкладку Данные|Анализ «что-если»|Подбор параметра…;

В открывшемся диалоговом окне Подбор параметра (Рис. 14) в поле Значение ввести требуемое значение функции: 0.

В поле Изменяя значение ячейки: ввести $E$1 (щелкнув по ячейке E1).

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

• В окне Результат подбора (Рис. 15) выводится информация о величине подбираемого и подобранного значения функции:
• В ячейке E1 выводится подобранное значение аргумента 0,6972 с требуемой точностью (0,0001).

Установить точность можно путем установки в ячейках таблицы точности представления чисел – числа знаков после запятой (Формат ячеек|Число|Числовой).

Итак, первый корень уравнения определен с заданной точностью: х₁≈0,6972.

2) Самостоятельно найти значение большего корня с той же точностью. (х₂≈4,3029).

IV. Метод Подбор параметра для решения уравнений вида f(x)=g(x).

При использовании метода Подбор параметров для решения уравнений вида f(x)=g(x) вводят вспомогательную функцию y(x)=f(x)-g(x) и находят с требуемой точностью значения х точек пересечения графика функции y(x) с осью абсцисс.

3. Закрепление изученного материала. Самостоятельная работа.

Задание: Используя метода Подбор параметров, найти корни уравнения с точностью до 0,001.

• ввести функцию у=и построить ее график на промежутке [ -1; 4 ] с шагом 0,25 (Рис. 16):

• найти приближенное значение х точки пересечения графика функции с осью абсцисс (х≈1,4);
• найти приближенное решение уравнения с точностью до 0,001 методом Подбор параметра (х≈1,438).

4. Итог урока.

Слайд 12 Проверка результатов самостоятельной работы.

Слайд 13 Повторение графического способа решения уравнения вида f(x)=0.

Слайд 14 Повторение графического способа решения уравнения вида f(x)=g(x).

5. Домашнее задание.

Используя средства построения диаграмм в Excel и метод Подбор параметра, определите корни уравнения х 2 -5х+2=0 с точностью до 0,01.

Решение уравнений в эксель in

Если в ячейку Excel введена формула, содержащая ссылку на эту же самую ячейку (может быть и не напрямую, а опосредованно — через цепочку других ссылок), то говорят, что имеет место циклическая ссылка (цикл). На практике к циклическим ссылкам прибегают, когда речь идет о реализации итерационного процесса, вычислениях по рекуррентным соотношениям. В обычном режиме Excel обнаруживает цикл и выдает сообщение о возникшей ситуации, требуя ее устранения. Excel не может провести вычисления, так как циклические ссылки порождают бесконечное количество вычислений. Есть два выхода из этой ситуации: устранить циклические ссылки или допустить вычисления по формулам с циклическими ссылками (в последнем случае число повторений цикла должно быть конечным).

Рассмотрим задачу нахождения корня уравнения методом Ньютона с использованием циклических ссылок. Возьмем для примера квадратное уравнение: х 2 — 5х + 6=0, графическое представление которого приведено на рис. 8. Найти корень этого (и любого другого) уравнения можно, используя всего одну ячейку Excel.

Для включения режима циклических вычислений в меню Сервис/Параметры/вкладка Вычисления включаем флажок Итерации, при необходимости изменяем число повторений цикла в поле Предельное число итераций и точность вычислений в поле Относительная погрешность (по умолчанию их значения равны 100 и 0,0001 соответственно). Кроме этих установок выбираем вариант ведения вычислений: автоматически или вручную. При автоматическом вычислении Excel выдает сразу конечный результат, при вычислениях, производимых вручную, можно наблюдать результат каждой итерации.

Рис. 8. График функции

Выберем произвольную ячейку, присвоим ей новое имя, скажем — Х, и введем в нее рекуррентную формулу, задающую вычисления по методу Ньютона:

где F и F1 задают соответственно выражения для вычисления значений функции и ее производной. Для нашего квадратного уравнения после ввода формулы в ячейке появится значение 2, соответствующее одному из корней уравнения (рис. 8). В нашем случае начальное приближение не задавалось, итерационный вычислительный процесс начинался со значения, по умолчанию хранимого в ячейке Х и равного нулю. А как получить второй корень? Обычно это можно сделать изменением начального приближения. Решать проблему задания начальных установок в каждом случае можно по-разному. Мы продемонстрируем один прием, основанный на использовании функции ЕСЛИ. С целью повышения наглядности вычислений ячейкам были присвоены содержательные имена (рис. 9).

• В ячейку Хнач (В4) заносим начальное приближение — 5.
• В ячейку Хтекущ (С4) записываем формулу:
  =ЕСЛИ(Хтекущ=0;Хнач; Хтекущ-(Хтекущ^2-5*Хтекущ+6)/(2*Хтекущ-5)).
• В ячейку D4 помещаем формулу, задающую вычисление значения функции в точке Хтекущ, что позволит следить за процессом решения.
• Заметьте, что на первом шаге вычислений в ячейку Хтекущ будет помещено начальное значение, а затем уже начнется счет по формуле на последующих шагах.
• Чтобы сменить начальное приближение, недостаточно изменить содержимое ячейки Хнач и запустить процесс вычислений. В этом случае вычисления будут продолжены, начиная с последнего вычисленного
  

  Рис. 9. Определение начальных установок

  значения. Чтобы обнулить значение, хранящееся в ячейке Хтекущ, нужно заново записать туда формулу. Для этого достаточно для редактирования выбрать ячейку, содержащую формулу, дважды щелкнув мышью на ней (при этом содержимое ячейки отобразится в строке формул). Щелчок по кнопке (нажатие клавиши) Enter запустит вычисления с новым начальным приближением.

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

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

Возьмем в качестве примера все то же квадратное уравнение х 2 -5х+6=0. Для нахождения корней уравнения выполним следующие действия:

• В ячейку С3 (рис. 10) введем формулу для вычисления значения функции,
  

  Рис. 10. Окно диалога Подбор параметра

  стоящей в уравнении слева от знака равенства. В качестве аргумента используем ссылку на ячейку С2, т.е. =С2^2-5*C2+6.

• В окне диалога Подбор параметра (рис. 10) в поле Установить в ячейке введем ссылку на ячейку с формулой, в поле Значение — ожидаемый результат, в поле Изменяя значения ячейки — ссылку на ячейку, в которой будет храниться значение подбираемого параметра (содержимое этой ячейки не может быть формулой).
• После нажатия на кнопку Ok Excel выведет окно диалога Результат подбора параметра. Если подобранное значение необходимо сохранить, то нажмите на Оk, и результат будет сохранен в ячейке, заданной ранее в поле Изменяя значения ячейки. Для восстановления значения, которое было в ячейке С2 до использования команды Подбор параметра, нажмите кнопку Отмена.

При подборе параметра Excel использует итерационный (циклический) процесс. Количество итераций и точность устанавливаются в меню Сервис/Параметры/вкладка Вычисления. Если Excel выполняет сложную задачу подбора параметра, можно нажать кнопку Пауза в окне диалога Результат подбора параметра и прервать вычисление, а затем нажать кнопку Шаг, чтобы выполнить очередную итерацию и просмотреть результат. При решении задачи в пошаговом режиме появляется кнопка Продолжить — для возврата в обычный режим подбора параметра.

Вернемся к примеру. Опять возникает вопрос: как получить второй корень? Как и в предыдущем случае необходимо задать начальное приближение. Это можно сделать следующим образом (рис. 11,а):

а

б

Рис. 11. Поиск второго корня

• В ячейку Х (С2) вводим начальное приближение.
• В ячейку Х_i (С3) вводим формулу для вычисления очередного приближения к корню, т.е.
  =X-(X^2-5*X+6)/(2*X-5).
• В ячейку С4 поместим формулу, задающую вычисление значения функции, стоящей в левой части исходного уравнения, в точке Х_i.
• После этого выбираем команду Подбор параметра, где в качестве изменяемой ячейки принимаем ячейку С2. Результат вычислений изображен на рис. 11,б (в ячейке С2 — конечное значение, а в ячейке С3 — предыдущее).

Однако все это можно сделать и несколько проще. Для того чтобы найти второй корень, достаточно в качестве начального приближения (рис. 10) в ячейку C2 поместить константу 5 и после этого запустить процесс Подбор параметра.

2.3. Поиск решения

Команда Подбор параметра является удобной для решения задач поиска определенного целевого значения, зависящего от одного неизвестного параметра. Для более сложных задач следует использовать команду Поиск решения (Решатель), доступ к которой реализован через пункт меню Сервис/Поиск решения.

Задачи, которые можно решать с помощью Поиска решения, в общей постановке формулируются так:

Искомые переменные — ячейки рабочего листа Excel — называются регулируемыми ячейками. Целевая функция F(х₁, х₂, … , х_n), называемая иногда просто целью, должна задаваться в виде формулы в ячейке рабочего листа. Эта формула может содержать функции, определенные пользователем, и должна зависеть (ссылаться) от регулируемых ячеек. В момент постановки задачи определяется, что делать с целевой функцией. Возможен выбор одного из вариантов:

• найти максимум целевой функции F(х₁, х₂, … , х_n);
• найти минимум целевой функции F(х₁, х₂, … , х_n);
• добиться того, чтобы целевая функция F(х₁, х₂, … , х_n) имела фиксированное значение: F(х₁, х₂, … , х_n) = a.

Функции G(х₁, х₂, … , х_n) называются ограничениями. Их можно задать как в виде равенств, так и неравенств. На регулируемые ячейки можно наложить дополнительные ограничения: неотрицательности и/или целочисленности, тогда искомое решение ищется в области положительных и/или целых чисел.

Под эту постановку попадает самый широкий круг задач оптимизации, в том числе решение различных уравнений и систем уравнений, задачи линейного и нелинейного программирования. Такие задачи обычно проще сформулировать, чем решать. И тогда для решения конкретной оптимизационной задачи требуется специально для нее сконструированный метод. Решатель имеет в своем арсенале мощные средства решения подобных задач: метод обобщенного градиента, симплекс-метод, метод ветвей и границ.

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

Рис. 12. Окно диалога Поиск решения

После открытия диалога Поиск решения (рис. 12) необходимо выполнить следующие действия:

1. в поле Установить целевую ячейку ввести адрес ячейки, содержащей формулу для вычисления значений оптимизируемой функции, в нашем примере целевая ячейка — это С4, а формула в ней имеет вид: = C3^2 — 5*C3 + 6;
2. для максимизации значения целевой ячейки, установить переключатель максимальному значению в положение 8 , для минимизации используется переключатель минимальному значению, в нашем случае устанавливаем переключатель в положение значению и вводим значение 0;
3. в поле Изменяя ячейки ввести адреса изменяемых ячеек, т.е. аргументов целевой функции (С3), разделяя их знаком «;» (или щелкая мышью при нажатой клавише Сtrl на соответствующих ячейках), для автоматического поиска всех влияющих на решение ячеек используется кнопка Предположить;
4. в поле Ограничения с помощью кнопки Добавить ввести все ограничения, которым должен отвечать результат поиска: для нашего примера ограничений задавать не нужно;
5. для запуска процесса поиска решения нажать кнопку Выполнить.

Рис. 13. Результаты поиска

Для сохранения полученного решения необходимо использовать переключатель Сохранить найденное решение в открывшемся окне диалога Результаты поиска решения. После чего рабочий лист примет вид, представленный на рис. 13. Полученное решение зависит от выбора начального приближения, которое задается в ячейке С4 (аргумент функции). Если в качестве начального приближения в ячейку С4 ввести значение, равное 1,0, то с помощью Поиска решения найдем второй корень, равный 2,0.

Опции, управляющие работой Поиска решения, задаваемые в окне Параметры (окно появляется, если нажать на кнопку Параметры окна Поиск решения), следующие (рис. 14):

Рис. 14. Настройка параметров Решателя

• Максимальное время — ограничивает время, отведенное на процесс поиска решения (по умолчанию задано 100 секунд, что достаточно для задач, имеющих около 10 ограничений, если задача большой размерности, то время необходимо увеличить).
• Предельное число итераций — еще один способ ограничения времени поиска путем задания максимального числа итераций. По умолчанию задано 100, и, чаще всего, если решение не получено за 100 итераций, то при увеличении их количества (в поле можно ввести время, не превышающее 32767 секунд) вероятность получить результат мала. Лучше попытаться изменить начальное приближение и запустить процесс поиска заново.
• Относительная погрешность — задает точность, с которой определяется соответствие ячейки целевому значению или приближение к указанным ограничениям (десятичная дробь от 0 до 1).
• Допустимое отклонение — задается в % только для задач с целочисленными ограничениями. Поиск решения в таких задачах сначала находит оптимальное нецелочисленное решение, а потом пытается найти ближайшую целочисленную точку, решение в которой отличалось бы от оптимального не более, чем на указанное данным параметром количество процентов.
• Сходимость — когда относительное изменение значения в целевой ячейке за последние пять итераций становится меньше числа (дробь из интервала от 0 до 1), указанного в данном параметре, поиск прекращается.
• Линейная модель — этот флажок следует включать, когда целевая функция и ограничения — линейные функции. Это ускоряет процесс поиска решения.
• Неотрицательные значения — этим флажком можно задать ограничения на переменные, что позволит искать решения в положительной области значений, не задавая специальных ограничений на их нижнюю границу.
• Автоматическое масштабирование — этот флажок следует включать, когда масштаб значений входных переменных и целевой функции и ограничений отличается, возможно, на порядки. Например, переменные задаются в штуках, а целевая функция, определяющая максимальную прибыль, измеряется в миллиардах рублей.
• Показывать результаты итераций — этот флажок позволяет включить пошаговый процесс поиска, показывая на экране результаты каждой итерации.
• Оценки — эта группа служит для указания метода экстраполяции — линейная или квадратичная, — используемого для получения исходных оценок значений переменных в каждом одномерном поиске. Линейная служит для использования линейной экстраполяции вдоль касательного вектора. Квадратичная служит для использования квадратичной экстраполяции, которая дает лучшие результаты при решении нелинейных задач.
• Разности (производные) — эта группа служит для указания метода численного дифференцирования, который используется для вычисления частных производных целевых и ограничивающих функций. Параметр Прямые используется в большинстве задач, где скорость изменения ограничений относительно невысока. Параметр Центральные используется для функций, имеющих разрывную производную. Данный способ требует больше вычислений, однако его применение может быть оправданным, если выдается сообщение о том, что получить более точное решение не удается.
• Метод поиска — служит для выбора алгоритма оптимизации. Метод Ньютона был рассмотрен ранее. В Методе сопряженных градиентов запрашивается меньше памяти, но выполняется больше итераций, чем в методе Ньютона. Данный метод следует использовать, если задача достаточно велика и необходимо экономить память, а также если итерации дают слишком малое отличие в последовательных приближениях.

Сохранить модель поиска решения можно следующими способами:

1. при сохранении книги Excel после поиска решения все значения, введенные в окнах диалога Поиск решения, сохраняются вместе с данными рабочего листа. С каждым рабочим листом в рабочей книге можно сохранить один набор значений параметров Поиска решения;
2. если в пределах одного рабочего листа Excel необходимо рассмотреть несколько моделей оптимизации (например найти максимум и минимум одной функции, или максимальные значения нескольких функций), то удобнее сохранить эти модели, используя кнопку Параметры/Сохранить модель окна Поиск решения. Диапазон для сохраняемой модели содержит информацию о целевой ячейке, об изменяемых ячейках, о каждом из ограничений и все значения диалога Параметры. Выбор модели для решения конкретной оптимизационной задачи осуществляется с помощью кнопки Параметры/Загрузить модель диалога Поиск решения;
3. еще один способ сохранения параметров поиска — сохранение их в виде именованных сценариев. Для этого необходимо нажать на кнопку Сохранить сценарий диалогового окна Результаты поиска решений.

Кроме вставки оптимальных значений в изменяемые ячейки Поиск решения позволяет представлять результаты в виде трех отчетов: Результаты, Устойчивость и Пределы. Для генерации одного или нескольких отчетов необходимо выделить их названия в окне диалога Результаты поиска решения. Рассмотрим более подробно каждый из них.

Рис. 15. Отчет по устойчивости

Отчет по устойчивости (рис.15) содержит информацию о том, насколько целевая ячейка чувствительна к изменениям ограничений и переменных. Этот отчет имеет два раздела: один для изменяемых ячеек, а второй для ограничений. Правый столбец в каждом разделе содержит информацию о чувствительности. Каждая изменяемая ячейка и ограничения приводятся в отдельной строке. Раздел для изменяемых ячеек содержит значение нормированного градиента, которое показывает, как целая ячейка реагирует на увеличение значения в соответствующей изменяемой ячейке на одну единицу. Подобным образом, множитель Лагранжа в разделе для ограничений показывает, как целевая ячейка реагирует на увеличение соответствующего значения ограничения на одну единицу. При использовании целочисленных ограничений Excel выводит сообщение Отчеты устойчивость и Пределы не применимы для задач с целочисленными ограничениями. Если в окне диалога Параметры поиска решения установлен флажок Линейная модель, то отчет по устойчивости содержит несколько дополнительных столбцов информации.

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

Отчет по результатам (рис.16) содержит три таблицы: в первой приведены сведения о целевой функции до начала вычисления, во второй — значения искомых переменных, полученные в результате решения задачи, в третьей — результаты оптимального решения для ограничений. Этот отчет также содержит информацию о таких параметрах каждого ограничения, как статус и разница. Статус может принимать три состояния: связанное, несвязанное или невыполненное. Значение разницы — это разность между значением, выводимым в ячейке ограничения при получении решения, и числом, заданным в правой части формулы ограничения. Связанное ограничение — это ограничение, для которого значение разницы равно нулю. Несвязанное ограничение — это ограничение, которое было выполнено с ненулевым значением разницы.

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

Исправляем ошибки: Нашли опечатку? Выделите ее мышкой и нажмите Ctrl+Enter