1. . Применение процедуры Поиск решения.

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

1. На новом листе рабочей книге в ячейку А1 введите значение 2.

2. В ячейку В1 введите значение 1.

3. Для поиска решения используем функцию двух переменных

4. При задании фиксированного значения zэта функция превращается в уравнение с двумя переменными, поэтому в ячейку С1 введите следующую формулу: =(А1^2 –B1^3)/(B1 –A1).

5. Щелкните на ячейке С1 и выберите команду Сервис →Поиск решения. На экран буде выведено окноПоиск решения.

6. Задайте следующие параметры:

- в поле Установить целевую ячейкувведите адрес $C$1;

- в группе Равнойустановите переключательЗначениюи введите число 20 в расположенное рядом поле;

- в поле Изменяя ячейкивведите диапазон $A$1:$B$1 (это значение будет введено автоматически, если вы щелкните на кнопкеПредположить).

7. Щелкните на кнопке Выполнить.

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

Поиск максимума.

1. Введём сначала начальные значения -1 и 1 в ячейках А1 и В1.

2. Выберем команду Сервис →Поиск решенияи зададим поиск максимального значения. Вы получите значение целевой функции 2196750 при других значениях переменных.

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

4. Попробуйте найти решение при наличии ограничивающих значений. для ячейки А1 это должны быть два значения: >=1 и <=2, - а для ячейки В1 - >=3 и <=4. В этом случае и максимальное, и минимальное значения будут найдены однозначно.

2.Функционирование процедуры поиска решения рассмотрим на примере.

Задача. Составить производственную программу выпуска изделий А и В по критерию максимальной рентабельности при заданных ограничениях производственной мощности четырёх цехов завода. Оба изделия последовательно обрабатываются в цехах. Время возможной работы цехов составляет соответственно 12, 8, 16 и 12 часов за плановый период, а нормы затрат времени на изготовление единицы товара А по цехам соответственно равна 2, 1, 4 и 0 часов, а на изделие В – 2, 2, 0 и 4 часов. Прибыль о реализации единицы продукции типа А составляет 2 грн., а типа В – 3 грн.

Решение.

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

Тогда математическую модель можно записать в виде системы линейных неравенств:

Где х₁, х₂– количество изделий А и В соответственно.

Целевая функция будет иметь вид: .

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

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

Ввходные данные записаны в диапазон В3:D7. В этих ячейках находятся коэффициенты при неизвестныхх₁, х₂системы линейных неравенств (строки 3-6 рабочего листаExcel) и целевой функции (строка 7), а также соответствующие свободные члены системы (в столбце С «Норма затрат времени»). В ячейкахВ9иС9будут находиться искомые решения неизвестных х₁, х₂(в начальном варианте принимаем эти значения равными 0). Формула по которой вычисляется целевая функция, находится в ячейкеЕ7.В ячейках Е3:Е6 записаны формулы, по которым можно вычислить время которое затрачивается на изготовление изделий в каждом из цехов (взаимосвязь между коэффициентами на неизвестные системы).

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

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

В поле Изменяя ячейки укажите, в каких ячейках программа должна изменять значения для получения оптимального решения (в нашем решении это ячейки В9:С9, в которых будет находиться решение задачи х₁их₂).

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

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

В поле Ссылка на ячейкунеобходимо ввести адрес ячейки, на содержание которой накладывается определённое ограничение (в нашем случае – это линейная комбинация неизвестных и соответствующих коэффициентов, которые определяют затраты времени на изготовление обеих деталей для каждого из цехов). В полеОграничения можно ввести либо конкретное ограничивающее число, либо адрес ячейки, где находится ограничение. Между этими полями необходимо выбрать необходимый оператор (<=, =, >=, цел., двоич.), который устанавливает отношения между ячейкой, на которую накладываются ограничения, и самим ограничением. После ввода каждого из ограничений необходимо нажимать кнопку Добавить.

После введения всех ограничений в поле ОграниченияокнаПоиск решенияпоявятся строки с заданными ограничениями.

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

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

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

Совокупность установленных параметров и ограничений можно сохранить вместе с рабочим листом в роли модели (кнопка Сохранить модельдиалогового окна). Загрузить эту модель после можно будет с помощью кнопкиЗагрузить модель.

Ответ: х₁=4, х₂=2,z=14.

3. Задача. Требуется определить, в каком количестве надо выпускать продукцию четырех типов Прод1, Прод2, Прод3, Прод4, для изготовления которой требуются ресурсы трех видов: трудовые, сырье, финансы. Количество ресурса каждого вида, необходимое для выпуска единицы продукции данного типа, называется нормой расхода. Нормы расхода, наличие располагаемого ресурса, а также прибыль, получаемая от реализации единицы каждого типа продукции, приведены в таблице:

Указания см. в приложении.

Аналогично решите задачу: Мастерская по покраске кузовов автомобилей рассчитана на покраску не более 160 кузовов в месяц. На покраску кузова “Москвича” краски расходуется 4 кг, а кузова “Волги” - 7 кг. Мастерская располагает 820 кг краски на месяц. Составить месячный план покраски автомобилей, максимизирующий прибыль мастерской, если покраска одного “Москвича” дает 30 д.е. прибыли, а одной “Волги” - 40 д.е. прибыли..