17.3. КОМПЬЮТЕРИЗАЦИЯ ЭКОНОМИЧЕСКИХ РАСЧЕТОВ

На этапе ввода исходных данных в рабочей книге программы Excel рекомендуется создать две матрицы: для области изменяемых ячеек Xj и для области удельных затрат на перевозку (рис. 17.16).

492 А В с D Е F G Н I j К 1 16,4 17 18,4 28 16,2 Bj 2 3 7 1 1 1 1 1 5 4 4 1 1 1 1 1 5 5 И 1 1 1 1 1 5 6 16 1 1 1 1 1 5 Объем 7 8 1 1 1 1 1 5 предложения 8 5 1 1 1 1 1 5 9 45 1 1 1 1 1 5 11 12 л, 7 7 7 7 7 13 14 Объем спроса 15 16 17 1,2 2,3 3,1 1,6 2,7 18 3,1 1,1 4,2 3,8 1,6 19 0,8 3,1 1,5 2,1 4,5 Удельные 20 4,0 2,9 3,7 4,3 2,8 QJ затраты 21 ЗД 4,0 3,6 5,2 2,6 22 3,4 2,8 4,1 3,0 3,7 23 4,8 5,6 6,7 4,2 5,8 24 25 26 117 Целевая ячейка - стоимость перевозки

Рис. 17.16. Образцы матриц и дополнительных полей для ввода исходных данных при решении ТЗЛП с помощью модуля «Поиск решения»

Клеткам матрицы изменяемых ячеек присваиваются единичные значения, данные для заполнения матрицы удельных затрат соответствуют условию конкретной задачи.

В поля C1:G1 и А3:А9 заносятся граничные значения объемов спроса и предложения, взятые из условий задачи.

Далее следует подготовить необходимые формулы, для чего:

- в ячейки I3:I9 с помощью встроенной функции СУММ ($С3:$G3) заносятся формулы для определения суммы по строкам матрицы изменяемых ячеек (объемы предложений);

в ячейки C12:G12 аналогичным способом заносятся формулы для вы-числения сумм по столбцам матрицы изменяемых ячеек (объемов спроса);

в ячейку целевой функции С26 с помощью функции СУММПРОИЗВ за-носится формула для вычисления целевой функции СУММПРОИЗВ (^3:G9; 07:G23).

Далее вызывается модуль «Поиск решения», в диалоговом окне которого задаются адрес целевой ячейки, диапазон изменяемых ячеек и все виды ограни-чений в соответствии с моделью ТЗЛП (см.

Закрыть 10 С" минимальному значению 11 12 изменяя ячеики: 13 1 SJ Предположить 1 14 Огра [Параметры | 15 ниченияі 16 Я

Ш Добавить | 17 Изменить | 13 Удалить | Восстановить | і 2U Справка 21 22 23 24 ІГкаж Хлисті / ЛИСТ2 ГЛистЗ / іДпаск Microsoft Word - методич... Microsoft Excel - Кни... ДПанель Microsoft Office Щ" 18:16

Рис. 17.17. Диалоговое окно модуля «Поиск решения»

При определении параметров поиска решения следует помнить, что оптимизация проводится по линейной модели с минимизацией значения целевой функции (см. рис. 17.18).

ПТдТхІ

Microsoft ЕнсеІ - Книгаї

^JflJxJ

^jj Файл Правка Вид Вставка Формат Сервис Данные Окно ?

т ф т A

Arial Cyr

%

-

ж к ч — — — I

Параметры поиска решения

32767 секунд

0,000001

Загрузить модель...

Со>чэанить модель,,,

После успешного завершения работы модуля «Поиск решения» в области изменяемых ячеек C3:G9 окажутся величины искомых переменных, т.е. оптимальные размеры перевозок от поставщиков до потребителей.

Экономический анализ полученного оптимального решения производится с помощью отчетов по результатам, устойчивости и пределам, вызываемым через диалоговое окно «Результаты поиска решения» (см. рис. 17.19).

Microsoft Excel - Razdel 2.xls

Файл Правка Вид Вставка Формат Сервис Данные Окно ? D С» Q в EL Щ А % 5 ^ О - Га - ft.

j ,00 + ,0 . Ат А . = Транспортная задача линейного программирования

j=e

45

ЯП 13

¦AliM

I

I

45

1=7

Левая часть Ограничения Потребность В сырье филиалов Вк

16,2

17

18,4

28

16,4

Ограничение для X О

16,2

17

18,4

28

16,4

Ш

Филиалы фирмы (к)

Результаты поиска решения

1.2

2,3

3,1

1.6

2.7

Решение найдено. Все ограничения и условия оптимальности выполнены, Результаты J Устойчивость Пределы J Справка

3,1

3,6

5.2

2.6

4,8

j=7

5,6

6,7

4.2

5.8

(* ІСохранить найденное решение] Восстановить исходные значения

ок

Отмена

321,02

44,3

I чг

Сохранить сценарий,,,

Стоимость достатки (перевозки) сырья К Н- му филиалу

Ш > N \Листі /

Готово Писк jj Проводник - Поиск реше... Microsoft Word | ]і<ї Microsoft Excel - Raz... Щ 11:12

Рис. 17.19. Диалоговое окно «Результаты поиска решения»

Отчет по результатам состоит из трех таблиц.

В таблице «Целевая ячейка (максимум)» приведены адрес, исходное и ре-зультатное значения целевой функции.

В таблице «Изменяемые ячейки» находятся адреса, идентификаторы и зна-чения всех искомых переменных задачи.

В таблице «Ограничения» показаны результаты оптимального решения для граничных условий и ограничений задачи.

В графе «Формула» указаны зависимости, которые были введены в диалоговом окне «Поиск решения»; в графе «Значения» приведены величины объе-мов отдельных видов продукции и значения искомых переменных задачи. В графе «Разница» показано количество непроизведенной продукции. Если объем производства продукции данного типа равен максимально возможному, то в графе «Состояние» указывается «Связанное», при неполном производстве про-дукции в графе «Состояние» указывается «Не связанное», а в графе «Разница» -

остаток.

496

Отчет по устойчивости содержит информацию о том, насколько целевая ячейка чувствительна к изменениям ограничений и переменных. Этот отчет имеет два раздела: один для изменяемых ячеек, а второй - для ограничений.

В разделе для изменяемых ячеек графа «Редуцированная стоимость» со-держит значения дополнительных двойственных переменных, показывающих возможности изменения целевой функции.

Графа «Целевой коэффициент» показывает степень зависимости между изменяемой и целевой ячейками, т.е. коэффициенты целевой функции.

Графы «Допустимое увеличение» и «Допустимое уменьшение» показывают предельные значения приращения коэффициентов в целевой функции ДСг-, при которых сохраняется оптимальное решение.

Для ограничений в графе «Теневая цена» приведены двойственные оценки Z, которые показывают, как изменится целевая функция при изменении объема выпуска продукции на единицу.

В графах «Допустимое увеличение» и «Допустимое уменьшение» показаны размеры приращений объемов выпуска продукции ДЬг-, при которых сохра-няется оптимальный набор переменных, входящих в оптимальное решение.

Отчет по пределам показывает, в каких пределах могут измениться ис-ходные данные для сохранения структуры оптимального решения.