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

Методика решения оптимизационных задач в Excel. 1. Возможности приложения Excel по решению оптимизационных задач. 2. Решение оптимизационных задач. ©квд

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Результат решения задачи Турбаза 1 2 1 -й 30 40 2 -й 170 0 3 -й 0 110 Суммарные расходы 2120 руб. 20