Методика решения оптимизационных задач в Excel

Вызов процедуры Подбор параметров
Меню Сервис пункт Подбор параметров.

Установка и вызов процедуры Поиск решения
Вызов
Меню Сервис.
Пункт Поиск решения.
Установка пакета 1.Меню Сервис, пункт Надстройки.
2. В окне установить флажок Поиск решения и ОК.

2. Решение оптимизационных задач.
Оптимизационные задачи
Численное решение уравнений
Задачи линейного программирования
Подбор параметров
Поиск решения
Процедуры

Пример решения уравнения
Условие задачи.
Найти решение уравнения Lnx=0
Найти решение можно графоаналитическим, аналитическим или численным методом.
X=e^0=1

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

Правая часть уравнения
Адрес изменяемого значения
1.Вызываем процедуру Подбор параметра.
2. Заносим в окна данные.
3. ОК и получаем приближенное значение корня.

Получаем приближенное значение корня 0,999872.

В задаче линейного программирования все функции линейны.
Требуется найти значения F(X), которые максимизируют или минимизируют целевую функцию
F(X)= ∑Ci*Xi, i=1,n , X=(X1,X2,…,Xn)
при выполнении ограничений
Gj(X) = ∑ Аji*Xji ≤Bj или
Gj(X) = ∑ Аji*Xji ≥Bj j=1,m.
Задачи линейного программирования

Методика решения задачи линейного программирования
1.Определение параметров задачи в соответствии с общей постановкой задачи.
2. Введение обозначений неизвестных.
3. Создание целевой функции (максимальная стоимость или прибыль, минимальные затраты).
4. Составление систем ограничений.
5. Решение задачи на ПК с помощью процедуры Поиск решения.

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

Окно Параметры поиска решения

Пример решения линейной оптимизационной задачи
Условие задачи.
Туристская фирма заключила договор с двумя турбазами.
На турбазах могут отдыхать 200 и 150 человек.
Туристам предлагается 3 объекта для экскурсий.
Составить маршрут движения туристов так, чтобы это обошлось возможно дешевле, если в один день
1-й объект может принять 70 человек,
2-й – 180 человек,
3-й – 110 человек.
Стоимость посещений объектов представлена в таблице
Найти решение с помощью процедуры Поиск решения.

1. Введем обозначения для числа туристов, которые посещают соответствующие объекты.
2. Составим целевую функцию, которую нужно минимизировать.
F(X)= ∑Ci*Xi=5*Х1+6*Х2+20*Х3+10*Х4+12*Х5+5*Х6

3.Определим ограничения из условия задачи.
1. Х1+Х4≤ 70
2. Х2+Х5 ≤ 180
3. Х3+х6 ≤ 110
4. Х1+Х2+Х3=200
5. Х4+Х5+Х6=150
Хi не могут быть отрицательными и являются целыми.
4.Открываем приложение Excel и заносим исходные данные.

Заносим исходные данные, выражения для ограничений и целевой функции

Вызов процедуры Поиск решения

Запись параметров в окно Поиск решения
Устанавливаем параметры и заносим адреса
Добавляем ограничения

Добавление ограничений
1. Щелкнуть по Добавить.
2. Внести адреса выражений ограничений.
3. Выбрать знак условия.
4. Внести адреса ограничений.
5. ОК.
6. Повторить пп 1-5 для всех ограничений.