Формирование целевой функции

Для формирования целевой функции введем вначале формулы, отражающие транспортные расходы по каждому потребителю, т.е. формулы:

, в ячейки блока «Транспортные расходы по потребителям»

1. Введите в первую ячейку блока «Транспортные расходы по потребителям» (ячейка С21) формулу =СУММ (С6:С8*С14:С16).

2. Активируйте эту строку формул и нажмите одновременно три клавиши: «CTRL»+«SHIFT»+«ENTER» и получите формулу массива {=СУММ (С6:С8*С14:С16)}.

Формула массива может выполнить несколько вычислений, а затем вернуть одно значение или группу значений. Формула массива обрабатывает несколько наборов значений, называемых аргументами массива. Каждый аргумент массива должен включать одинаковое число строк и столбцов. Формула массива создается так же, как и другие формулы, с той разницей, что для ввода такой формулы используются клавиши CTRL+SHIFT+ВВОД. Некоторые встроенные функции являются формулами массива, и для получения правильных результатов их следует вводить как массивы.

3. Скопируйте формулу {=СУММ (С6:С8*С14:С16)} в остальные ячейки блока «Транспортные расходы по потребителям»;

4. Сформируем теперь целевую функцию транспортной задачи, выражаемую формулой , в ячейку «Итого расходы». Для этого введите в ячейку «Итого расходы» (ячейка I21) формулу =СУММ (С21:F21);

После формирования элементов математической модели и целевой функции транспортной задачи рабочий лист EXСEL примет вид, показанный на рисунке 3.

Теперь можно приступить к настройке программы «Поиск решения».

Рисунок 3

1.5 Настройка программы «Поиск решения»

Для настройки программы «Поиск решения» выполните следующие действия:

1. Откройте надстройку Поиск решения (из меню Сервис) или Данные для Excel 2010.

Выделите «Итого расходы» (ячейка);

2. Установите целевую ячейку I21.

3. Установите переключатель на «Минимальное значение»

4. В поле «Изменяя ячейки» установите блок ячеек «Матрица перевозок» (блок С14:F16);

5. Ограничения установить в режиме Добавить через диалога команды «Добавление ограничения». (См. рисунок 4.)

· $С$14:$F$16>=0 (положительность результатов);

· $С$18:$F$18=$С$10:$F$10 (полное удовлетворение спроса потребителей);

· $I$14:$I$16<=$I$6:$I$8 (Реализация не превышает предложений поставщиков);

Рисунок 4

6. По кнопке «Параметры» откройте окно диалога «Параметры поиска решения» (см. рисунок 5), и установите курсор на флажок «Линейная модель». (Поиск решения линейных задач симплекс-методом).

7. На форме Параметры установите указанные на рисунке 5 значения.

Рисунок 5

Убедитесь, что на рабочем листе EXCEL в блоке "Матрица перевозок" появляется решение транспортной задачи, показанное на рисунке 6.

Рисунок 6

2 Задача распределения ресурсов

Если финансы, оборудование, сырье и даже людей полагать ресурсами, то значительное число задач в экономике можно рассматривать как задачи распределения ресурсов. Достаточно часто математической моделью таких задач является задача линейного программирования.

Рассмотрим следующий пример.

Требуется определить, в каком количестве надо выпускать продукцию четырех типов П1, П2, ПЗ, П4, для изготовления которой требуются ресурсы трех видов: трудовые, сырье, финансы. Количество ресурса каждого вида, необходимое для выпуска единицы продукции данного типа, называется нормой расхода. Нормы расхода, а также прибыль, получаемая от реализации единицы каждого типа продукции, приведены на рисунке 6. Там же приведено наличие располагаемого ресурса.

Рисунок 6

Составим математическую модель, для чего введем следующие обозначения:

- x_j — количество выпускаемой продукции j -гo типа, j = 1..4;

- b_i — количество располагаемого ресурса i - гo вида, i = 1..3;

- a_ij — норма расхода i - гo ресурса для выпуска единицы продукции j -гo типа;

- c_j — прибыль, получаемая от реализации единицы продукции j -гo типа.

Теперь приступим к составлению модели.

Как видно из рисунка 6, для выпуска единицы П1 требуется 6 единиц сырья, значит, для выпуска всей продукции П1 требуется 6× x ₁ единиц сырья, где x ₁ — количество выпускаемой продукции П1. С учетом того, что для других видов про­дукции зависимости аналогичны, ограничение по сырью будет иметь вид:

6 x ₁+5 x ₂+4 x ₃+3 x ₄ £ 110.

В этом ограничении левая часть равна величине потребного ресурса, а правая показывает количество имеющегося ресурса.

Аналогично можно составить ограничения для остальных ре­сурсов и написать зависимость для целевой функции. Тогда математическая модель задачи будет иметь вид:

(7)

Рассмотрим аналитический метод решения таких задач с помощью Excel (метод линейного программирования) на примере задачи рас­пределения ресурсов, математическая модель которой имеет вид (7).

Для задачи, приведенной на рисунке 6, необходимо сделать форму для ввода условий задачи и ввести исходные данные (рисунок 7).

По той же формуле вводится целевая функция.

Далее следует обратиться к надстройке «Поиск решений». Данные ® Поиск решений. Если необходимо, ее следует подключить: Разработчик ® Настройки.

Нужно отметить галочкой условие неотрицательных переменных и выбрать в качестве метода решения симплекс-метод (рисунок 8).

Остальные параметры могут быть назначены так же, как показано на рисунке 9.

Рисунок 7

Рисунок 8

Рисунок 9

В случае успешного решения числовые данные появятся на Вашей форме.

Рисунок 10. Оптимальное решение.

Можно оформить решение, пользуясь формой «Результаты поиска решения» (разобраться самостоятельно).

Результаты оптимального решения:

П1 = В3 = 10, П2 = С3 = 0, П3 = D3 = 6, П4 = Е3 = 0.

При этом максимальная прибыль будет составлять F6 = 1320, а количество использованных ресурсов равно: трудовых = F9 =16, сырья = F10 = 84, финансов = F11 = 100.

Таково оптимальное решение рассматриваемой задачи распределения ресурсов.