Поиск решения в Excel

Цель работы: научиться использовать процессор Excel

для решения задач оптимизации

Содержание работы:

1 Создание формы

2 Ввод данных в окно Поиск решения

3 Задание параметров поиска и решение задачи

 

Математический аппарат Excel позволяет решать задачи линейного, нелинейного и целочисленного программирования. При этом оптимизация решения выполняется методом поиска решения, который запускается командой Сервис\Поиск решения.

Задача линейного программирования (ЗЛП) в общем случае формулируется следующим образом:

Определить максимум (минимум) целевой функции F max(min) при заданной системе ограничений (2) и граничных условий (3):

 

Fmax(min) =A1*X1+A2*X2+...+An*Xn (1)

B11*X1+B12*X2+...+B1n*Xn< =C1

B21*X1+B22*X2+...+B2n*Xn< =C2

.............................................................. (2)

Bn1*X1+Bn2*X2+...+Bnn*Xn< =Cn

 

Xi> =0, i=1...n (3)

 

Рассмотрим применение табличного процессора Excel для решения ЗЛП на примере.

 

Задача. МП выпускает товары Х1, Х2, Х3, Х4, получая от реализации каждого прибыль в 60, 70, 120, 130 руб. соответственно. Затраты на производство приведены в табл. 1. Определить:

1 Максимум прибыли в зависимости от оптимального распределения затрат.

2 Минимум ресурсов, необходимых для получения максимальной прибыли.

 

 

Таблица 1

Затраты	Х1	Х2	Х3	Х4	Всего
Трудовые
Сырьевые
Финансы

 

Составим математическую модель процесса по описанию задачи:

 

60Х1+70Х2+120Х3+130Х4 = Fmax – целевая функция прибыли.

Х1+Х2+Х3+Х4 < = 16

6Х1+5Х2+4Х3+Х4 < = 110 - ограничения модели

4Х1+6Х2+10Х3+13Х4 < = 100

Х_j > =0 - граничные условия модели

 

Решение задачи средствами Excel состоит из 3 этапов:

1 Создание формы для ввода условий задачи, ввод в неё исходных данных и зависимостей из математической модели.

2 Ввод данных из формы в окно Поиск решения из меню Сервис.

3 Задание параметров поиска и решение задачи.