Решение задач линейного программирования в Microsoft Excel

МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ

Федеральное государственное бюджетное образовательное учреждение

Высшего образования

«Севастопольский государственный университет»

 

МЕТОДИЧЕСКИЕ УКАЗАНИЯ

 

к выполнению лабораторных работ

«ПОСТРОЕНИЕ оптимальных МОДЕЛЕЙ

И Решение задач В СИСТЕМЕ EXCEL»

 

по дисциплинам «Исследование операций»,

«Методы оптимальных решений»

для студентов экономических специальностей

всех форм обучения

 

Севастополь

УДК 519.852

 

Методические указания к лабораторным работам «Построение оптимальных моделей и решение задач в системе Excel» по дисциплинам «Исследование операций», «Методы оптимальных решений» для студентов экономических специальностей всех форм обучения/ Сост. Н.А. Русина, А.А. Загорулько - Севастополь: Изд-во СевНТУ, 2014.- 32с.

 

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

 

 

Методические указания утверждены на заседании кафедры менеджмента организаций (протокол № ___ от ___________2015г.).

 

 

Допущено учебно-методическим центром СГУ в качестве методических указаний

 

 

Рецензент: Кокодей Т.А., к.э.н., доцент кафедры Менеджмента и экономико-математических методов.

СОДЕРЖАНИЕ

 

1.	Лабораторная работа №1 Оптимизация задач линейного программирования с помощью команды «Поиск решения» системы Excel.
2.	Лабораторная работа №2 Оптимизация задач линейного программирования с помощью симплекс-метода.
3.	Лабораторная работа №3 Оптимизация транспортных задач.
	Библиографический список

 

Лабораторная работа №1

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

Цель работы

Приобретение навыков решения задач линейного программирования с помощью команды «Поиск решения».

 

Теоретические сведения

Основой исследования операций является построение экономико-математических моделей, т.е. математического описания исследуемого экономического процесса или объекта. Эта модель выражает закономерности экономического процесса в абстрактном виде с помощью математических соотношений. Использование математического моделирования в экономике и менеджменте позволяет углубить количественный экономический анализ, расширить область использования экономической информации, интенсифицировать расчеты [2].

Примеры задач линейного программирования:

- задача об использовании ресурсов (задача планирования производства);

- задача составления рациона (задача о диете, задача о смесях);

- задача об использовании мощностей (задача о загрузке оборудования);

- задача о раскрое материалов;

- транспортная задача.

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

Задача студента – разобраться в методе и решить задачи по предложенному варианту.

Решение задач линейного программирования в Microsoft Excel

Для решения задач линейного программирования в программе MS Excel используется команда «Поиск решения», которая позволяет осуществлять поиск оптимального решения в зависимости от условия задачи.

Работу данной команды предлагается рассмотреть на примере следующей задачи.

Задача. Предприятие занимается выпуском трех видов продукции (продукт 1, продукт 2, продукт 3). Производство этой продукции основано на использовании трех видов сырья. В таблице 14 представлены нормы расхода сырья на производство каждого из видов продукции, а также прибыль от реализации единицы продукции. Требуется отыскать оптимальное количество продукции, которое позволит на основании имеющихся запасов сырья получить максимальную прибыль от реализации продукции. Исходные данные представлены в таблице 14.

 

Таблица 14 – Исходные данные

	A	B	C	D	E	F
	Вид сырья	Норма расхода сырья на производство единицы продукции, шт.	Израсходовано сырья на производство выпускаемой продукции, шт.	Всего ресур-са
Продукт 1	Продукт 2	Продукт 3
	I				=$B$6*B3+$C$6*C3+$D$6*D3,
	II				=$B$6*B4+$C$6*C4+$D$6*D4
	III				=$B$6*B5+$C$6*C5+$D$6*D5
	Количество выпускаемой продукции, шт.
	Прибыль от реализации единицы продукции, грн.
	Общая прибыль от реализации продукции	=B7*B6+C6*C7+D7*D6
	A	B	C	D	E	F

 

Ячейки B6:D6 предназначены для значений изменяемых переменных. В ячейки B3:D5 вводим условия задачи, в ячейку B8 формулу для вычисления целевой функции. Для решения задачи воспользуйтесь надстройкой MS Excel «Поиск решения». Раскройте пункт меню «Сервис» или «Данные», выберите команду «Поиск решения». Если в меню «Сервис» отсутствует команда «Поиск решения», загрузите эту надстройку: выберите команду «Сервис -> Надстройки» и активизируйте надстройку «Поиск решения».

 

 

 

 

Рисунок 1 – Окно команды «Поиск решения»

 

В данном окне есть три основные параметра:

- оптимизировать целевую функцию;

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

- ограничения (правила, которыми команда «Поиск решения» будет руководствоваться для нахождения правильного ответа). Сначала заполните поле «оптимизировать целевую функцию» - B8. Затем установите переключатель равной максимальному значению. Наконец, определите данные поля «Изменяя ячейки переменных», выделив ячейки B6:D6. Теперь определите ограничения: щелкните на кнопке «Добавить». Появится диалоговое окно «Изменение ограничения» (рис. 3). Введите ограничения на не отрицательность переменных B6:D6 >=0 и щелкните на кнопке «Добавить».

Введите также ограничения на количество используемого сырья: $E$2<=$F$2, $E$3<=$F$3, $E$4<=$F$4.

Кнопка «Параметры» позволяет настроить параметры модели. Теперь для процедуры «Поиска решения» готовы все исходные данные. Чтобы начать процесс решения задачи, щелкните на кнопке «ОК». В строке состояния будет отражаться ход решения задачи.

 

 

Рисунок 2 – Окно «Параметры поиска решения»

Через некоторое время на экране появится диалоговое окно «Результаты поиска решения», в котором вы можете выбрать одну из следующих возможностей:

- сохранить найденное решение;

- восстановить исходные значения в изменяемых ячейках;

- создать несколько видов о процедуре поиска.

Установите переключатель на отметке «Сохранить найденное решение» и щелкните на кнопке ОК.

Результаты расчетов представлены в таблице 15.

 

Таблица 15 – Результаты расчетов

 

	A	B	C	D	E	F
	Вид сырья	Норма расхода сырья на производство единицы продукции, шт.	Израсходовано сырья на производство выпускаемой продукции, шт.	Всего ресур-са
Продукт 1	Продукт 2	Продукт 3
	I
	II
	III
	Количество выпускаемой продукции, шт.
	Прибыль от реализации единицы продукции, грн.
	Общая прибыль от реализации выпускаемой продукции, грн.

 

В итоге рассчитано оптимальное решение - необходимое количество каждого вида напитков и величину максимальной при этом прибыли: В ячейках B5:D5 найдены значения переменных x1=0, x2=8, x3=20 в ячейке B8 значение целевой функции F=400.

 

Варианты заданий

Индивидуальная работа предполагает выполнение задач согласно выбранному варианту. Распределение заданий по вариантам представлено в таблице 16.

 

Таблица 16 - Варианты заданий

Номер варианта
Номер задания
Задача 1
Задача 2
Номер варианта
Номер задания
Задача 1
Задача 2

 

Задача 1. Распределение ресурсов.

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

Цель задачи - определить, как нужно распределить ресурсы, чтобы максимизировать значение общей прибыли, т.е. определить в каком количестве необходимо изготовить каждую игрушку для максимальной прибыли.

Ограничения:

· нужно обеспечить, чтобы на выпуск продукции уходило только имеющееся в наличии количество ресурсов;

· количество произведенного не должно быть отрицательным

Исходные данные представлены в таблице 17.

Таблица 17 – Исходные данные

 

номер ресурса

Материал / потребность

Игр 1

Игр 2

Игр3

Игр 4

Игр5

Игр 6

Игр 7

Игр8

Игр 9

Игр10

запас

красная краска

голубая краска

белая краска

Пластик

Дерево

Клей

Прибыль за ед.,грн.

 

 

Таблица 18 - Варианты заданий

 

вариант	Игрушки
	+	+	+	+	+
		+	+	+	+	+
			+	+	+	+	+
				+	+	+	+	+
					+	+	+	+	+
						+	+	+	+	+
	+		+		+		+		+
		+		+		+		+		+
	+	+			+	+			+
		+	+			+	+			+

 

Задача 2.

Предприятие, предпринимающее меры к организации производства двух новых видов изделий, имеет ограниченную сумму собственных средств на капиталовложения К, которую может увеличить за счет использования банковского кредита, сумма которого ограничена J. Естественно, что привлечение заемных средств кажется экономически оправданным только в том случае, если новое производство будет прибыльным с учетом выплачиваемых процентов h.

Определить при помощи программы MS Excel (команды «Поиск решения») объемы производства изделий каждого вида, обеспечивающие максимум прибыли, если известно, что капиталовложения на единицу производства изделий первого и второго видов составляют соответственно а1 и а2, прибыль от реализации единицы изделия каждого вида равна соответственно р1 и р2; минимально допустимый объем производства первого вида – А, второго вида – В. Принять решение о целесообразности использования банковского кредита. Исходные данные задачи по вариантам приведены в таблице 19.

 

Таблица 19 - Исходные данные

Вариант	К, тыс. грн.	J, тыс. грн.	h, %	а1, тыс. грн.	а2, тыс. грн.	р1, тыс. грн.	р2, тыс. грн.	А, ед.	В, ед.
					2,5	1,3	1,6
				0,5	1,2	0,3	0,4
				1,5		0,8	0,6
				2,2	1,8	0,9	0,7
				1,7		0,9	1,1
				0,7	0,4	1,1	1,3
				1,2	0,9	0,7	0,6
				0,7	1,1	0,3	0,5
				1,6	1,8		1,2
				0,4	0,9	1,3	1,1

СОДЕРЖАНИЕ ОТЧЕТА

1) Цель работы;

2) постановка задачи;

3) результаты расчетов, проведенных в Excel;

4) выводы по выполнению работы.

 

КОНТРОЛЬНЫЕ ВОПРОСЫ

1) Что такое математическая модель экономической системы?

2) Для решения какого типа задач используется линейное программирование?

3) Сформулируйте основную задачу линейного программирования?

4) Приведите экономическую интерпретацию двойственной задачи об использовании ресурсов?

5) Какие задачи относятся к задачам линейного программирования?

6) Какие параметры надо задавать при использовании команды «Поиск решения»?

7) Какие задачи можно решать с помощью команды «Поиск решения»?

Лабораторная работа №2