Общие сведения. Математический аппарат Excel позволяет решать задачи линейного, нелинейного и целочисленного программирования

Математический аппарат 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	Всего
Трудовые
Сырьевые
Финансы

 

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

 

целевая функция прибыли: Fmax = 60Х1+70Х2+120Х3+130Х4 → max (1)

 

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

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

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

 

Граничные условия модели Х_j > =0, i = 1, …, 4 (3)

 

Смысл модели:

Уравнение (1) означает, что каждая единица товара Х1 вносит в прибыль Fmax 60 денежных единиц, напр., рублей., товара Х2 – 70 руб., товара Х3 - 120 руб. и товара х4 – 130 руб.:

Fmax = 60Х1+70Х2+120Х3+130Х4 → max

Первое уравнение системы ограничений (2) означает, что для производства единицы товаров Х1…Х4 требуется по одной единице ресурсов первого вида - трудовых (все коэффициенты при всех Х_i равны 1), а всего имеется 16 единиц трудовых ресурсов, поэтому:

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

Второе уравнение системы ограничений (2) означает, что для производства единицы товара Х1 требуется 6 единиц ресурсов второго вида – сырьевых, для производства единицы Х2 – 5 единиц сырья, для Х3 – 4 единицы и для Х4 – 1 единица сырья. Всего на складе имеется 110 единиц исходного сырья:

6Х1+5Х2+4Х3+Х4 < = 110

Третье уравнение системы ограничений (2) означает, что для производства единицы товара Х1 требуется 4 единиц ресурсов третьего вида – финансовых, напр., - рублей, для производства единицы Х2 – 6 рублей, для Х3 – 10 рублей и для Х4 – 13 рублей. Всего для производства имеется 100 денежных единиц:

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

Граничные условия (3) показывают, что все товары Х1…Х4 реальны, существуют на самом деле, они неотрицательны, т.е

Х_j > =0, i = 1, …, 4

где i изменяется от 1 до 4.

 

 

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

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

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

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