поиск оптимальных решений задач линейного программирования с использованием программных средств excel 7.0

(Руководство пользователя)

Решение задач линейного программирования с использованием Excel 7.0 осуществляется с помощью инструментального средства Поиск решения. Для запуска этого инструмента выполните команду Сервис/Настройки. Появится окно диалога «Настройки», в котором установите флажок на строке Поиск решения и нажмите кнопку ОК.

После загрузки инструмента Поиск решения в списке опций ниспадающего меню Сервис появится новая команда Поиск решения. В результате выполнения этой команды появится окно диалога «Поиск решения».

В поле ввода Установить целевую ячейку указывается ссылка на ячейку с целевой функцией, значение которой будет максимизироваться, минимизироваться или станет нулевым, в зависимости от выбранного пользователем переключателя.

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

Кнопка Параметры вызывает окно диалога «Параметры поиска решения».

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

Предположим, что предприятие выпускает два вида продукции. Цена единицы I-го вида продукции равна 25000 р., II-го вида – 50000 р. При производстве продукции используются три вида сырья, запасы которого оцениваются в 37, 57.6 и 7 условных единиц. Для каждого вида сырья определён коэффициент его расхода при производстве единицы продукции. Соответствующие коэффициенты приведены в табл.П.3.1. В этой же таблице приведена цена одной единицы продукции I-го и II-го видов.

Таблица П.3.1

Вид сырья	Нормы расхода сырья на изготовление единицы продукции	Общее количество сырья
I вид	II вид
	1.2 2.3 0.1	1.9 1.8 0.7	57.6
Цена единицы продукции

Обозначим количество произведённой продукции I вида через с 1, II вида – с 2. В этом случае показатель качества распределения ресурсов (или, по-другому, целевая функция) есть выражение следующего вида:

I(c 1, c 2) = 25000* c 1 + 50000* c 2.

Эта функция позволяет количественно оценивать стоимость произведённой продукции. В соответствии с содержательной постановкой задачи на величины с 1 и с 2 накладываются ограничения следующего вида:

1.2* c 1 + 1.9* c 2 <= 37;

2.3* c 1 + 1.8* c 2 <= 57.6;

0.1* c 1 + 0.7* c 2 <= 7;

c 1 >= 0; c 2 >= 0.

Задача линейного программирования формализована. Теперь можно приступать к её решению с помощью табличного процессора Excel 7.0.

Выполните следующие действия.

1. Введите в ячейку А1 формулу для целевой функции: =25000* c 1 + 50000* c 2.

2. Введите в ячейку А3 формулу для ограничения: =1.2* c 1 + 1.9* c 2.

3. Введите в ячейку А4 формулу для ограничения: =2.3* c 1 + 1.8* c 2.

4. Введите в ячейку А5 формулу для ограничения: =0.1* c 1 + 0.7* c 2.

5. Введите в ячейку А6 формулу для ограничения: = с 1.

6. Введите в ячейку А6 формулу для ограничения: = с 2.

7. Введите в ячейки С1:С2 начальные значения переменных. В рассматриваемой задаче их значение равняется нулю.

8. Выполните команду Сервис/Поиск решения. Появится окно диалога «Поиск решения».

9. В поле ввода Установить целевую ячейку введите ссылку на ячейку А1 (необходимо ввести следующую запись - $A$1).

В поле ввода Изменяя ячейки укажите ссылки на ячейки С1:С2 (необходимо ввести следующую запись = $C$1:$C$2).

11. Начинаем вводить информацию в поле ввода Ограничения. Нажмите кнопку Добавить. Появится окно диалога «Добавить ограничения». В поле ввода Ссылка на ячейку введите ссылку на ячейку А3 (вводится $A$3). В поле ввода Ограничение введите <= и число 37.

12. Воспользуйтесь кнопкой Добавить для ввода остальных ограничений. Для изменения ограничения установите на него курсор и нажмите кнопку Изменить.

13. Нажмите кнопку Выполнить. После окончания расчёта Excel откроет окно диалога «Результаты поиска решения».

14. Выберите в окне «Тип отчёта» Результаты и нажмите кнопку ОК. Перед тем листом, где записана постановка задачи, будет вставлен лист «Отчёт по результатам 1», а на экране будут представлены результаты решения рассматриваемой задачи линейного программирования. В ячейках С1 и С2 отображаются значения переменных, которые максимизируют целевую функцию I(c 1, c 2).

15. Нажмите мышью ярлык «Отчёт по результатам 1». На экране ПЭВМ появится отчёт Excel о решённой задаче. Следует обратить внимание, что в данной задаче при полученном плане производства продукции с 1 и с 2 (19,38 и 7,23 соответственно), выделенные ресурсы 1, 2, 3 видов оказываются полностью израсходованными.

16. Для исследования устойчивости полученных решений к изменению исходных данных и определения пределов изменения оптимальных решений при сохранении структуры оптимального плана выпуска продукции необходимо в окне «Тип отчёта» выбрать Устойчивость, Пределы и получить соответствующие отчёты.

литература

1. Акулич И.Л. Математическое программирование в примерах и задачах: Уч. пособие для студентов экон. спец. вузов. – М.: Высшая школа, 1998.

2. Акулич И.Л., Ворончук И.С. Задачи нелинейного и динамического программирования. – Рига: Изд-во ЛГУ, 1989..

3. Иванилов Ю.П., Лотов А.В. Математические модели принятия решений в управлении и экономике. – М.: Наука, 1979.

4. Ларичев О.И. Наука и искусство принятия решений. – М.: Наука, 1979.

5. Саати Т. Принятие решений. Метод анализ иерархий: Пер. с англ. – М.: Ради и связь, 1989.

6. Князевский Н.В., Князевская В.С. Принятие раскованных решений в экономике и бизнесе: Уч. пособие. – М.: Контур, 1998.

7. Фатхутдинов Р.А. Разработка управленческого решения. – Учебник, М.: ЗАО «Бизнес-школа Интел-Синтез», 1998.

8. Андрейчиков А.В., Андрейчикова О.Н. Анализ, синтез, планирование решений в экономике. – Учебник. – М.: Финансы и статистика, 2000.

9. Красников В.С. Разработка управленческих решений. – СПб.: Изд-во СЗАГС, 1999.

10. Глухов В.В., Медников М.Д., Коробко С.Б. Экономико–математические методы и модели в менеджменте. – Уч. пособие. – СПб.: Изд-во СПб ГТУ, 1999.

11. Курицкий Б.Я. Поиск оптимальных решений средствами Excel 7.0. – СПб., ВНV Санкт-Петербург, 1997.

ОГЛАВЛЕНИЕ

введение.............................................................................................. 2

1. цель лабораторной работы.......................................................... 3

2. теоретические основы работы................................................ 4

2.1. Общая характеристика задач подготовки и принятия решений в сложных технико-экономических системах.......................................................................................... 4

2.2. Постановка задачи линейного программирования 8

2.2. ЭКОНОМИЧЕСКАЯ ИНТЕРПРЕТАЦИЯ ЗАДАЧИ ЛИНЕЙНОГО ПРОГРАММИРОВАНИЯ.................................................................... 9

2.4. ПРОВЕРКА СБАЛАНСИРОВАННОСТИ ПЛАНОВ..................... 12

2.5. ТРЕБОВАНИЯ СОВМЕСТНОСТИ УСЛОВИЙ............................ 15

2.6. ГРАФИЧЕСКИЙ МЕТОД РЕШЕНИЯ ЗАДАЧ ЛИНЕЙНОГО ПРОГРАММИРОВАНИЯ.................................................................. 16

2.7.ИДЕЯ СИМПЛЕКС-МЕТОДА........................................................... 19

2.8. ДВОЙСТВЕННЫЕ ЗАДАЧИ ЛИНЕЙНОГО ПРОГРАММИРОВАНИЯ............................................................................................................... 21

3. Методические указания по выполнению лабораторной работы.............................................................................................. 24

4. форма отчётности по выполненной лабораторной работе............................................................................................................... 25

Приложение 1. варианты индивидуальных заданий на выполнение лабораторной работы.............................. 26

Приложение 2. использование пакета прикладных программ qsb в процессе принятия решений................................ 37

П.2.1. Общие сведения о QSB.............................................................. 37

П.2.2. Решение задач линейного программирования...................... 39

П.2.3. Решение задач целочисленного программирования............ 46

П.2.4. Решение транспортной задачи................................................. 50

П.2.5. Решение задачи о назначениях................................................ 53

П.2.6. Решение сетевых задач (NET)................................................... 55

П.2.7. Решение сетевых задач (СРМ).................................................. 57

П.2.8. Решение задач динамического программирования.............. 60

П.2.9. Решение вероятностных моделей............................................. 62

Приложение 3. поиск оптимальных решений задач линейного программирования с использованием программных средств excel 7.0......................................................................... 63

литература................................................................................................ 65