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

В.П. Сухинин, М.В. Горшенина

ПРОИЗВОДСТВЕННЫЙ МЕнеджмент

Методические указания к практическому занятию

«Использование excel для решения задач

производственного планирования»

Сызрань 2011

УДК 658

Методические указания к выполнению практической работы / В.П. Сухинин, М.В. Горшенина; Самар. гос. техн. ун-т., филиал в г. Сызрани. Сызрань, 2011. 12 с.

Методические указания содержат сведения о содержании практического занятия по дисциплине «Производственный менеджмент». В них рассмотрен пример симплекс-метода решения экономической задачи с помощью Excel и приведены задачи для самостоятельной работы.

Данные методические указания адресованы студентам, обучающимся по специальности 080502 – Экономика и управление на предприятии.

Использование excel для решения задач

производственного планирования

Цель работы: формирование навыков решения задач производственного планирования с использованием excel.

Порядок выполнения работы

В Excel имеется эффективный инструмент для решения задач линейной оптимизации – программная надстройка Поиск решения. Установка этой надстройки производится через меню Сервис Надстройки, после чего в диалоговом окне Надстройки необходимо пометить пункт Поиск решения и нажать кнопку ОК. После установки этой надстройки в меню Сервис появляется дополнительный пункт Поиск решения.

Пример

Планом развития региона предполагается ввести в действие 3 нефтяных месторождения с суммарным объемом добычи равным 9 млн. т. На первом месторождении объем добычи составляет не менее 1 млн. т., на втором – 3 млн. т., на третьем – 5 млн. т. Для достижения такой производительности необходимо пробурить не менее 125 скважин. Для реализации данного плана выделено 25 млн. руб. капитальных вложений (показатель К) и 80 км труб (показатель L).

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

Исходные данные по задаче приведены в таблице:

Месторождение	Добыча, млн.т	Фонд скважин	Дебет 1 скважины	Длина трубопровода для 1 скважины	Стоимость строительства 1 скважины	К	L
				1,0	0,3
				2,0	0,2
				0,5	0,5
Итого:

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

Математическая модель:

Целевая функция: , где , , - количество скважин по каждому месторождению.

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

- длина труб;

0,3 х ₁+0,2 х ₂+0,5 х ₃ ≤ 25- стоимость строительства скважин;

- число скважин на I месторождении;

- число скважин на II месторождении;

- число скважин на III месторождении.

Решение: Ввести в электронную таблицу данные так, как показано ниже.

	A	B	C	D	E	F
	Переменные					Правые части ограничений
	Значения
	Протяженность			0,5	=B3*$B$2+C3*$C$2+D3*$D$2
	Стоимость проекта	0,3	0,2	0,5	=B4*$B$2+C4*$C$2+D4*$D$2
	Ограничения количества скважин
	Целевая функция				=B6*$B$2+C6*$C$2+D6*$D$2

Для переменных , , отведены ячейки B2, C2, D2. Эти ячейки называются изменяемыми ячейками. В эти ячейки ничего не заносится, и в результате решения задачи в них будут записаны оптимальные значения переменных. В ячейки E3-E4 вводятся формулы для вычисления левых частей ограничений, а в ячейку E6 формула для вычисления целевой функции. Знаки доллара в формулах вводятся для того, чтобы в ячейках E4, E6 можно было получить копированием формулы из ячейки E3.

Затем в меню Сервис выбираем процедуру Поиск решения. В появившемся окне (рис.1) устанавливаем целевую ячейку E6, выбираем вариант оптимизации – максимизация, водим адреса изменяемых ячеек и ограничения. Для ввода ограничений используем кнопку Добавить. Кнопки Изменить и Удалить служат соответственно для изменения или удаления помеченного ограничения.

Р и с. 1. Окно «Поиск решения»

Кнопка Предположить служит для автоматического выбора изменяемых ячеек. Далее нажимаем кнопку Параметры и в появившемся окне устанавливаем флажки Линейная модель (в этом случае при решении будет использоваться симплекс-метод) и Неотрицательные значения. Остальные значения можно оставить без изменения. Нажимаем кнопку ОК. Для решения задачи в окне Поиск решения нажимаем кнопку Выполнить.

После нахождения решения появляется диалоговое окно для выбора типа отчета (рис. 2).

Р и с. 2. Диалоговое окно для выбора типа отчета

При успешном решении задачи в данном окне появится сообщение (Рис. 2). При несовместной системе ограничений задачи в диалоговом окне появится сообщение «Поиск не может найти подходящего решения». При неограниченности целевой функции в заданном направлении в диалоговом окне появится сообщение «Значения целевой ячейки не сходятся».

Далее при успешном решении задачи выбираем два типа отчета: Результаты и Устойчивость и нажимаем ОК. Первоначальная таблица заполняется результатами, полученными при решении (рис. 3).

Р и с. 3. Вид первоначальной таблицы

Отчеты появляются на отдельных листах книги Excel.

Отчет Результаты состоит из трех таблиц и имеет следующий вид:

Целевая ячейка (Максимум)	Ячейка	Имя	Исходное значение	Результат
	$E$6	Целевая функция

Изменяемые ячейки	Ячейка	Имя	Исходное значение	Результат
	$B$2	Значения x1
	$C$2	Значения x2
	$D$2	Значения x3

Ограничения	Ячейка	Имя	Значение	Формула	Статус	Разница
	$E$3	Протяженность		$E$3<=$F$3	не связан.
	$E$4	Стоимость проекта		$E$4<=$F$4	связанное
	$B$2	Значения x1		$B$2<=$B$5	связанное
	$C$2	Значения x2		$C$2<=$C$5	связанное
	$D$2	Значения x3		$D$2<=$D$5	не связан.

В первой таблице указано оптимальное значение целевой функции, во второй – значения переменных, в третьей – оптимальные расходы ресурсов, их статус и допустимое снижение запасов недефицитных ресурсов без изменения оптимального решения. Фактически вся это информация уже содержится в итоговой таблице.

Отчет Устойчивость состоит из двух таблиц и имеет следующий вид:

Изменяемые ячейки	Ячейка	Имя	Результ. значение	Нормир. стоимость	Целевой коэффициент	Допустимое увеличение	Допустимое уменьшение
	$B$2	Значения x1				1E+30
	$C$2	Значения x2				1E+30
	$D$2	Значения x3				116,6666667

Ограничения	Ячейка	Имя	Результ. значение	Теневая цена	Ограничение правая часть	Допустимое увеличение	Допустимое уменьшение
	$E$3	Протяженность				1E+30
	$E$4	Стоимость проекта					1E+30

Первая из этих таблиц содержит диапазоны изменения коэффициентов целевой функции, в которых не происходит изменения оптимального решения. Во второй таблице приводятся теневые цены ресурсов и диапазоны изменения запасов каждого из ресурсов (при условии неизменности других ресурсов), в которых теневые цены остаются неизменными. Значения 1E+30, приведенные в таблице, означают возможность бесконечного увеличения правых частей соответствующих ограничений без изменения теневых цен.

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

Контрольные вопросы

1. Каковы основные этапы решения задачи линейного программирования в MS Excel?

2. Поясните общий порядок работы с окном «Поиск решения».

3. Каким образом можно изменять, добавлять, удалять ограничения в окне «Поиск решения»?

4. Какие сообщении выдаются в MS Excel в случаях: успешного решения задачи; несовместимости системы ограничений задачи; неограниченности целевой функции?

5. Объясните смысл параметров, задаваемых в окне «Параметры поиска решения».