7. Подбор параметра. Поиск решения

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

Microsoft Excel - это мощный инструмент для решения нелинейных уравнений и ряда оптимизационных задач.

Рис. 2.33. Квадратное уравнение

Попытаемся получить решение квадратного уравнения, например f(х)=2х²+3х-600 = 0. Для этого в любой свободной зоне таблицы выберите 5 ячеек (рис. 2.8). Три из них отводятся под коэффициенты. Одна ячейка (например, D4) отводится для искомой переменной (предварительно в нее записывается начальное значение) и одна (например, H4) - для записи функции, значение которой должно быть равным 0. После ввода исходных четырех чисел в Н4 формируется нечто, отличное от нуля.

• Выполните Сервис/Подбор параметра. В диалоговом окне установите необходимые параметры (рис. 2.9). Завершите выбор параметров нажатием клавиши [OK].

Рис. 2.34. Подбор параметра

Появляется диалоговое окно "Результат подбора параметра" и, если вас там все устраивает, выполните [OK], иначе - [Отмена]. Программа подобрала такое з начение для ячейки D4, что значение в ячейке H4 стало примерно равно заданному, т.е. 0.

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

2. Поиск Решения

Рассмотрим решение системы двух нелинейных уравнений

Рис. 2.35. Нелинейные

уравнения

Д анную задачу решаем как задачу нелинейного программирования (часто используемый прием для решения систем нелинейных уравнений): определить переменныех₁, х₂, минимизирующие суммарную квадратичную невязку

.

Поскольку мы решаем некоторую техническую задачу, будем считать, что нас интересуют лишь положительные корни.

Для получения решения необходимо выделить область переменных (С2:С3) и область функций (D2:D3). В некоторой ячейке (D4) вычисляется функционал (рис. 2.10).

• На листе «Проба» выполните построение данной таблицы расчетов. Рекомендуем упомянутые области выделить цветом.

• ВыполнитеСервис/Поиск Решения. В диалоговых окнах укажите: «Установить Целевую Ячейку»- (D4); «Изменяя Ячейки» - С2:С3 (ссылки на блоки указываются мышкой). Флажок цели установите на «минимизацию». В окне ограничений нажмите клавишу «Добавить» для ввода условия неотрицательности переменных. Появляется новая диалоговая панель, где в окне слева указываются адреса переменных, в среднем окне – условие, в правом - значения (рис. 2.11). - все переменные больше нуля. Закончить диалог. Вы получили решение Х=(1,2)^t? Если нет – повторите решение.

Рис. 2.36. Ограничения

Получите решение системы нелинейных уравнений

3. Транспортная задача

Типичным примером задачи линейного программирования, которая также решается методом «Поиск решения» является транспортная задача, для которой, в силу ее широкого распространения, разработано достаточно большое число оригинальных алгоритмов. В типовой постановке эта задача формулируется следующим образом. Требуется определить план {x_ij} перевозок однородного груза, минимизирующий суммарную стоимость перевозок

Ф=

при наличии ограничений

(по ресурсам);	(2.0)
(по потреблению);	(2.0)
(условие замкнутости );	(2.0)
(простые ограничения).	(2.0)

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

Рис. 2.37. Транспортная задача

Одним из возможных путей учета реального отсутствия транспортных связей между топливными бассейнами и электрическими станциями является назначение штрафных (априори избыточных) цен перевозки таким образом, чтобы соответствующие искомые переменные в результате оптимизации стали равными нулю. В представленном примере (см. рис. 2.12) это цены 999 в ячейках I9, J10, J11.

Матрица стоимости перевозок получается путем поэлементного умножения матрицы перевозок на матрицу цен перевозок и вектор цен на топливо (блок D15:F17 формируется с помощью матричной формулы {=D9:F11*I9:K11*C9:C11}).

В рассматриваемой задаче вывоз топлива из бассейна может быть ниже его максимальной производительности (условие ( 2 .0) преобразуется в неравенство.). Для учета ограничений ( 2 .0)-( 2 .0) в блоках D12:F12, G9:G11 формируются соответственно построчная и постолбцовая суммы матрицы перевозок, которые сопоставляются соответственно с блоками D5:F5, В9:В11. В ячейке G12 представлена целевая функция – сумма элементов матрицы D9:F11 стоимости перевозок.

Панель «Поиск решения» показана на рис. 2.13.

Рис. 2.38. Панель «Поиск решения» транспортной задачи

• Выполните представленные расчеты.

• Выполните расчеты по индивидуальному заданию преподавателя.

4. Рис. 2.39. Таблица подстановки

   Таблицы подстановки

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

Таблицы подстановки чаще всего используются для математического и визуального анализов функциональных отношений. Рассмотрим нелинейную функцию f(х)=min(х²-4х+3; x-2). Хотелось бы получить ее график. Для этого необходимо задать ряд параметров и получить соответствующие значения функции. Это относительно простая задача - получение функционального ряда не представляет проблем - необходимо только записать одну формулу и распространить (скопировать) ее на все члены ряда. Однако в практике встречаются более сложные ситуации, когда практически невозможно явно выразить функцию. Здесь весьма полезным представляется аппарат таблиц подстановок. Выполним с его помощью решение поставленной задачи.

• Рис. 2.41. Таблица умножения

  На листе «Проба» выделить место для параметра и функции, например, так, как представлено на рис. 2.14. Разработать и реализовать алгоритм расчета функции для заданного параметра в ячейке В4 – формула =МИН(B2:B3)). Составить варьируемый ряд параметра (столбец D). Справа от этого ряда будет сформирован ряд функций, а над ним (в ячейке Е2 записана ссылка на ячейку, где формируется функция (=В4).

• Выделить блок D2:E9. Выполнить Данные/Таблица подстановки. В окне "Ячейка ввода строки" указывается ссылка на параметр х (В1). В результате будет сформирована таблица подстановки (рис. 2.14). Формально Excel выбирает очередную величину из ряда параметров, подставляет ее в отведенную для параметра ячейку, получает значение функции (в В4) и переносит его на соответствующее место в таблице подстановки.

• Представьте полученную функцию в графическом виде (рис. 2.15).

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

С помощью инструментария Excel составим таблицу умножения.

Рассмотрим произведение двух переменных x, y, числовые значения которых хранятся в ячейках B12 и В13 (рис. 2.16). Однако нас интересуют не единичные значения переменных, а ряды данных, согласно которым изменяются переменные. Их попарные сочетания образуют таблицу, строки которой соответствуют первому сомножителю, а столбцы - второму. Запишем упомянутые ряды в столбце А16:А24 и строке В15:F15.На их пересечении, в ячейке А15, записывается формула = B12*B13, согласно которой будет выполнено заполнение блока В16: F15:F24. В результате у нас обозначились левая и верхняя границы таблицы. Теперь всё готово к созданию таблицы умножения.

Выделим всю таблицу, включая и заданные значения параметров. Выполним: Данные/Таблица подстановки. В окне "Ячейка ввода строки" укажем В12, а в окне «Ячейка ввода столбца» - В13, [OK]. Получилось то, что нужно? ОК!

Решите задачу. Вы вкладываете в банк под определенный процент (Пр) некоторую сумму (Вклад). Вам очень интересно знать, что вы получите через t=3 года (Выплата = Вклад*(1+Пр)^t)?. Немного помечтайте: А что было бы, если процентная ставка была бы ... . Постройте таблицу выплат в зависимости:

1) от процентной ставки;

2) от процентной ставки и вклада.

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