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

Математический аппарат MS Excel позволяет решать задачи линейного, нелинейного и целочисленного программирования. При этом оптимизация решения выполняется методом поиска решения, который запускается командой Данные\Поиск решения… (Активируется в Параметры Excel\Надстройки) – рисунок 7.1.

Рисунок 7.1. Вкладка Данные, группа Анализ

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

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

Fmax(min) =A₁*X₁+A₂*X₂+...+A_n*X_n (1)

B₁₁*X₁+B₁₂*X₂+...+B_1n*X_n<=C₁

B₂₁*X₁+B₂₂*X₂+...+B_2n*X_n<=C₂

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

B_n1*X₁+B_n2*X₂+...+B_nn*X_n<=C_n

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

Система уравнений (1)..(3) является математической моделью ЗЛП.

Рассмотрим, как применяется процессора MS Excel для решения ЗЛП:

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

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

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

Таблица 1

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

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

60Х₁+70Х₂+120Х₃+130Х₄ = F_max – целевая функция прибыли.

Х₁+Х₂+Х₃+Х₄ <= 16

6Х₁+5Х₂+4Х₃+Х₄ <= 110 - ограничения модели

4Х₁+6Х₂+10Х₃+13Х₄ <= 100

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

Отметим, что целевая функция и система ограничений математически представляют собой сумму произведений аргументов Х_j на числовые коэффициенты, поэтому при вводе модели в MS Excel применяется функция СУММПРОИЗВ().

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

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

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

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