Задание 2. Поиск решения.

Лабораторная работа 5

Подбор параметров и поиск решений

Цель работы. Получение навыков решения оптимизационных задач в среде Excel.

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

Пример. С помощью подбора параметра найти корень нелинейного уравнения x⁵ - 4x⁴ + 3x³ - 2x² + x + 4 = 0,

x принадлежит отрезку [-5,5]

Для этого в ячейку B1 введем произвольное значение X (при некоторых начальных значениях процесс решения может и не сойтись), в ячейку B2 введем формулу =B1^5-4*B1^4+3*B1^3-2*B1^2+B1+4

Установив курсор в ячейку B2, обратимся к команде Подбор параметра меню Сервис. В поле “значение” окна диалога введем число 0, в поле “изменяя значение ячейки” – значение B1 и нажмем кнопку ОК. Excel проиллюстрирует результаты подбора параметра в новом окне диалога.

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

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

Зависимая_ячейка Знак Выражение, где Знак может быть <=, =, >= или ограничение до целого числа, если задача целочисленна.

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

Пример: определить длины сторон a,b,h прямоугольного бака заданного объема V, минимизируя длину сварного шва, которая вычисляется по формуле: L=2(a+2b)+h (см. пунктирную линию на рисунке).

Математическая модель этой задачи вместе с ограничениями такова: L→min – оптимизируемая функция; V=const; a,b,h>0 – ограничения.

Решение: Введем начальные значения зависимых переменных а=1, b=1, c=1 в ячейки B3, C3, D3, а ограничение для них – число 0 – в ячейку B4. Зависимую переменную V=a*b*h введем в ячейку C7 в виде формулы =B3*C3*D3, а ограничение на нее в виде значения 2 – в ячейку E7. Целевую формулу для вычисления L в виде =2*(B3+2*C3)+D3 введем в ячейку C8. После этого в окне Поиск решения укажем $C$8 в качестве целевой ячейки, выберем поиск минимального значения, в поле «Изменяя ячейки» укажем ссылку $B$3:$D$3, в поле ограничения введем ограничения вида $B$3>=$B$4, $C$3>=$B$4,$D$3>=$B$4, $C$7=$E$7. Найденное решение должно быть таким: A=1,26, B=0,63, C=2,52.

Задания к лабораторной работе.

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

Решить с помощью подбора параметра нелинейное уравнение из приведенной ниже таблицы вариантовЖ

Дополнительно построить таблицу значений функции на указанном в таблице интервале. Шаг по аргументу выбрать самостоятельно. Изобразить график функции на этом интервале.

Задание 2. Поиск решения.

Задача 1. Имеетcя квадратный лист жести со стороной L=1 м. По его углам вырезаются 4 одинаковых квадрата со стороной А, затем полученная крестообразная заготовка сгибается в прямоугольную коробку без верхней крышки, а швы завариваются (см. рис.). Какой должна быть величина А, чтобы объем получившейся коробки был максимальным?

Задача 2. Фабрика может выпускать продукцию 2 типов - по P1 и P2 рублей за единицу. Для выпуска продукции выделено L единиц ресурсов и T человеко-часов. Известно, что на выпуск единицы продукции 1-го и 2-го вида уходит по ZT1 и ZT2 единиц ресурсов и ZL1 и ZL2 человеко-часов соответственно. Сколько нужно произвести продукции первого и второго видов, чтобы ее общая стоимость была максимальна? Данные взять из приведенной ниже таблицы. Оптимизируемую функцию и систему ограничений задачи выбрать самостоятельно.

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

Задача 3. Предположим, что мы решили производить несколько видов конфет. Назовем их условно "A", "B" и "C". Известно, что реализация 10-и килограмм конфет "А" дает прибыль 9 руб., "В" - 10 руб. и "С" - 16 руб. Конфеты можно производить в любых количествах (сбыт обеспечен), но запасы сырья ограничены. Необходимо определить, каких конфет и сколько десятков килограмм необходимо произвести, чтобы общая прибыль от реализации была максимальной.

Нормы расхода сырья на производство 10 кг конфет каждого вида приведены ниже.