Поиск решения в 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

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

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 Задание параметров поиска и решение задачи.