CC BY
38УДК 519.85
Е.И. Смирнов, Е.Н. Трофимец
Построение и анализ оптимизационных моделей экономики в обучении математике с использованием компьютерных технологий
Рассматривается процесс решения транспортной задачи, вопросы разработки ее компьютерной модели и последующего анализа различных практических ситуаций, формулируются общие выводы об основных этапах решения задач линейного программирования в MS Excel.
Ключевые слова: компьютерная модель, оптимизация, транспортная задача.
E.I. Smirnov, E.N. Trofimets
Construction and the Analysis of Optimizing Models of Economics in Mathematics Training
with the Use of Computer Technologies
The process of solving a transport task is considered; problems of development of its computer model and the subsequent analysis of various practical situations; common conclusions about the basic stages of solution tasks of a linear programming in MS Excel are formulated.
Key words: a computer model, optimisation, a transport problem.
В настоящей статье авторы хотели бы поделиться своими взглядами на технологию построения компьютерных моделей оптимизационных задач в MS Ехес! При рассмотрении прикладной задачи в курсе высшей математики акценты смещаются с собственно нахождения оптимального решения на исследование построенной модели, что является крайне важным для развития аналитического мышления студентов.
Широкое распространение математического моделирования в экономике в значительной степени обусловлено развитием информационных инструментальных сред, которые позволяют переводить экономико-математические модели из классической символьной формы представления в компьютерную и тем самым предоставляют пользователю доступные и эффективные средства всестороннего анализа моделей, что для практической деятельности играет решающую роль. В свою очередь, современные потребности экономической науки и практики послужили основными предпосылками для широкого распространения технологий компьютерного моделирования в образовательном процессе специалистов финансово-экономического профиля, о чем свидетельствуют многочисленные публикации как в специализированных журналах, посвященных информатизации образования («Информатика и образование», «Компьютерные учебные программы и инновации», «Образовательные технологии и общество» и др.), так и в изданиях различных научных и образовательных учреждений. Анализируя данные публикации, можно отметить, что использование компьютерного моделирования в образовательном процессе имеет два основных аспекта: первый аспект - компьютерное моделирование рассматривается как средство передачи обучаемым артикулируемой части знаний, второй аспект - компьютерное моделирование рассматривается как средство передачи обучаемым неартикулируе-мой части знаний.
Артикулируемая часть знаний относительно легко поддается структурированию и может быть передана обучаемому с помощью порций информации (текстовой, графической, видео и т. д.). Программно-методические средства, применяемые для поддержки процесса освоения артикулируемой части знаний, получили название декларативных. К ним относятся: автоматизированные учебники; автоматизированные системы информационного обеспечения лекционных занятий; автоматизированные учебные курсы; автоматизированные системы контроля усвоения знаний и другие программ-
© Смирнов Е.И., Трофимец Е.Н, 2011
но-методические средства, позволяющие хранить, передавать и проверять правильность усвоения обучаемым информации учебного характера. Компьютерные модели, встроенные в программно-методические средства декларативного типа, играют, как правило, поясняющую роль и позволяют нагляднее представить обучаемым суть изучаемого объекта (явления).
Неартикулируемая часть знаний представляет собой компонент знания, основанный на опыте и интуиции. Эта часть знаний охватывает умения, навыки, интуитивные образы и другие формы человеческого опыта, которые не передаются обучающемуся непосредственно, а «добываются» им в ходе самостоятельной познавательной деятельности при решении практических задач. Программно-методические средства, применяемые для поддержки процесса освоения неартикулируемой части знаний, получили название процедурных. Программно-методические средства процедурного типа не содержат овеществленные знания в виде информации, они строятся на основе моделей, которые позволяют обучаемому в ходе детерминированного или свободного учебного исследования получать знания о свойствах изучаемых объектов или процессов. К процедурным программно-методическим средствам относятся: автоматизированные практикумы; автоматизированные лабораторные работы; прикладные программы, позволяющие конструировать модели; автоматизированные тренажеры и другие программно-методические средства, позволяющие обучаемому «добывать» знания в исследуемой предметной области.
Необходимо отметить, что классификация учебных программно-методических средств на декларативные и процедурные не является строгой. В одном и том же программно-методическом средстве можно выделить и декларативную и процедурную составляющие, поэтому в данном случае следует говорить о доминировании одной составляющей над другой. Например, автоматизированный практикум, в котором явно преобладает процедурная составляющая, может быть снабжен инструкциями о последовательности действий при решении типовых задач. В этом случае обучаемый получает готовую информацию о процессе решения задачи и, соответственно, приобретает декларативные знания.
В информационно-аналитической подготовке специалистов финансово-экономического профиля наиболее эффективными, на наш взгляд, оказались программно-методические средства с доминирующей процедурной частью. Среди них можно выделить прикладные программы, в которых реализованы готовые модели и методы, используемые в профессиональной деятельности, а также прикладные программы, позволяющие конструировать модели и методы профессиональной деятельности (программы-конструкторы). К прикладным программам первого типа относятся такие программы, как STADIA, ТЭО-Инвест, Project Expert, Forecast Expert, Microsoft Project и др., к прикладным программам второго типа - MS Excel, MathCad, MathLab, UniCalc, MVS, КОГНИТРОН и др.
При всей несомненной полезности прикладных программ первого типа их применение не всегда приводит к повышению качества собственно аналитической подготовки. Обучаемые не получают в полной мере представления о сути реализованных в программе методов, что проявляется в недоверии к получаемым результатам, а отсюда и в неуверенном использовании программы. Плохую услугу аналитической подготовке иногда оказывает и скрытность вычислительных процедур, выполняемых в программе. Многие расчеты, которые, на первый взгляд, кажутся рутинной работой, обладают большим обучающим эффектом, так как позволяют проследить и понять связь анализируемых показателей объекта (процесса) с варьируемыми переменными.
Таким образом, несмотря на высокий дидактический потенциал прикладных программ первого типа, во многих случаях он оказывается в полной мере не реализованным, так как требует предварительного осмысления используемых в прикладных программах экономико-математических методов и моделей на более «осязаемом» уровне. Такой уровень может быть достигнут при построении соответствующих компьютерных моделей в «прозрачной» среде, которую предоставляют программы-конструкторы.
Проводя сравнительный анализ программ-конструкторов, следует отметить, что такие программы, как MVS, КОГНИТРОН, Unicalc, не получили широкого распространения в вузах финансово-экономического профиля, что объясняется, по всей видимости, сложностью их освоения без специальной математической подготовки и, как следствие, их недостаточной распространенностью в экономической сфере. Данное предположение подтверждается практикой использования этих программных продуктов в аналитических подразделениях, когда при построении моделей с использованием указанных программных сред экономистам-аналитикам нередко приходится обращаться за по-
мощью к математикам. Также приходится констатировать, что и универсальные математические пакеты, такие как MathCad, MatLab, Mathematica и др., которые в технических вузах получили широкое распространение, в вузах финансово-экономического профиля пользуются значительно меньшей популярностью.
Принимая во внимание указанные обстоятельства, в качестве универсальной моделирующей среды в информационно-аналитической подготовке специалистов финансово-экономического профиля предлагается использовать табличный процессор MS Excel. Выбор Ехсе1 в качестве инструмента программной реализации экономико-математических моделей обусловлен рядом обстоятельств. Во-первых, данный программный продукт достаточно глубоко изучается во всех вузах финансово-экономического профиля; во-вторых, он установлен во всех организациях; в-третьих, MS Excel имеет специальные программные надстройки и развитую библиотеку аналитико-расчетных функций, которые могут использоваться для решения широкого класса задач экономического анализа; в-четвертых, MS Excel обладает открытой архитектурой и при необходимости его функциональные возможности могут быть значительно расширены за счет разработки пользовательских функций и программных надстроек; в-пятых, MS Excel интегрируется с большим числом программных продуктов, что позволяет его рассматривать как связывающее звено при разработке учебных фрагментов распределенной системы поддержки принятия экономических решений.
Практика использования табличного процессора MS Excel в качестве среды моделирования экономических систем и процессов на инженерно-экономическом факультете Ярославского государственного технического университета подтвердила его высокий дидактический потенциал. Кроме того, в процессе обучения на основе компьютерного моделирования следует ожидать и активизации психологических механизмов усвоения знаний обучаемыми.
В качестве примера предлагается рассмотреть оптимизационные модели транспортного типа [1]. Такие модели используются для составления наиболее экономичных планов перевозки продукции из нескольких пунктов отправления (например, складов) в несколько пунктов назначения (например, магазины). Транспортную модель можно также применять и при рассмотрении ряда других практических ситуаций, связанных с управлением запасами, составлением сменных графиков, назначением исполнителей по рабочим местам и др. Кроме того, ее можно видоизменять, с тем чтобы она учитывала перевозку нескольких видов продукции.
Широкое практическое приложение транспортной задачи обусловило ее обязательное рассмотрение в курсе линейного программирования большинства вузов. Можно предположить, что у многих читателей линейное программирование ассоциируется именно с решением транспортной задачи, рассмотрению которой уделено достаточно внимания в книгах по исследованию операций, экономико-математическому моделированию, логистике, экономическому анализу и некоторых других. Поэтому мы не будем подробно останавливаться на теоретических аспектах решения транспортной задачи, а сфокусируем свое внимание на вопросах разработки ее компьютерной модели и последующего анализа различных практических ситуаций.
Содержательная постановка задачи
Компанией разрабатывается план обеспечения потребителей горюче-смазочными материалами (ГСМ). Исходные данные о запасах ГСМ в хранилищах, заявках на ГСМ в центрах распределения и стоимости перевозки 1 т ГСМ от хранилищ к центрам распределения представлены в нижеследующей таблице.
Хранилища Центры распределения Запасы ГСМ в
ГСМ Центр 1 Центр 2 Центр 3 Центр 4 Центр 5 хранилищах, т
Хранилище 1 4 6 7 9 1 350
Хранилище 2 6 4 1 2 2 200
Хранилище 3 5 8 7 4 9 450
Хранилище 4 2 3 8 5 7 350
Хранилища ГСМ Центры распределения Запасы ГСМ в хранилищах, т
Центр 1 Центр 2 Центр 3 Центр 4 Центр 5
Потребность в ГСМ, т 350 400 250 100 250
Требуется разработать такой план доставки ГСМ от хранилищ к центрам распределения, чтобы общая стоимость перевозок была минимальной [3].
Математическая модель задачи
Рассматриваемая задача является транспортной задачей закрытого типа, или, другими словами, сбалансированной транспортной задачей. Последнее название, на наш взгляд, наиболее удачно, так как отражает условие сбалансированности запасов и потребностей, имеющееся в рассматриваемой задаче, то есть:
т п
I=Е Ъ,, (1)
г=\ ]=1
где т - количество исходных пунктов (в рассматриваемой задаче количество хранилищ); п - количество пунктов назначения (в рассматриваемой задаче количество центров распределения); а7 - количество (объем ) груза в 7-м исходном пункте;
Ъ, - количество (объем) груза, которое должно быть завезено в,-й пункт назначения.
4 5 4 5
Для рассматриваемой задачи имеем: I а7 =1350, IЪ, = 1350, то есть I а7 =1Ъ, , таким
7=1 ]=1 7=1 ]=1
образом, она является сбалансированной.
Так как запасы равны потребностям, то все запасы будут вывезены, а все потребности будут удовлетворены. Данные условия можно записать в виде следующих уравнений:
п
I Хч = а, 7 = и.т, (2)
]=1
т
IХ, = Ъ], ] = и. п, (3)
7=1
где х7] - искомые переменные задачи - количество (объем) груза, которое должно быть перевезено с 7-го исходного пункта в ]-й пункт назначения.
Так как количество перевозимого груза не может принимать отрицательные значения, то в рассматриваемой задаче имеет место условие неотрицательности, то есть:
Х] > 0. (4)
Выражения (2)-(4) образуют систему ограничений задачи, целевая функция в которой задается выражением:
т п
Р = ЦСуХг] ^ ^ (5)
7=1 ]=1
где с, - стоимость перевозки одной единицы груза (в рассматриваемой задаче 1 т ГСМ) с 7-го
исходного пункта в /-й пункт назначения.
Экономическая интерпретация выражения (5) становится очевидной, если его записать в развернутом виде:
Р = С11Х11 + С12Х12 + ... + СЦХЦ + ... + СтпХтп ^ т1п. (6)
Так как с/ - это стоимость перевозки одной единицы груза с 7-го исходного пункта в /-й пункт назначения, а х7]- - объем перевозимого груза по данному маршруту, то с/ х х7]- - это стоимость перевозки груза по маршруту 7-й исходный пункт - /-й пункт назначения. Сложение стоимостей перевозок по всем возможным маршрутам образует стоимость общего плана перевозок.
Объединяя выражения (2)-(6), получаем модель сбалансированной транспортной задачи:
Ё xv = ai, 1 = m , (71)
j=i
Ё xv = bj, j = 1,..., n , (7.2) (7)
i=1
Xj > 0, (7.3)
ij
m n
F = ЁЁсуХу ^ min. (7.4)
¿=1 j=i
Опираясь на общую модель (7), разработаем математическую модель для рассматриваемой задачи. Выражение (7.1) запишется в виде системы следующих уравнений:
Хц Х12 Х13 Х14 Х15 — 350,
Х21 + Х22 + Х23 + Х24 + Х25 = 200, (8)
21 22 23 24 25 Х31 + Х32 + Х33 + Х34 + Х35 = 450 , Х41 + Х42 + Х43 + Х44 + Х45 — 350 ,
выражение (7.2) - в виде системы уравнений:
Хц X21 Х31 Х41 —
350,
Х12 + Х22 + Х32 + Х42 = 400 ,
Х13 ^ъ Х23 Х33 Х43 — 250, (9)
Х14 + Х24 + Х34 + Х44 — 100,
Х15 X25 Х35 Х45 —
250.
Условие неотрицательности (7.3) будет задано двадцатью неравенствами следующего вида:
х„ > 0, Х12 > 0, ..., Х45 > 0. (10)
Целевая функция (7.4) запишется в виде выражения: F = 4х11 + 6х12 +... + 5х31 + ... + 7х45 ^ min. (11)
Таким образом, математическая модель рассматриваемой задачи разработана, можно переходить к разработке вычислительной модели задачи на рабочем листе.
42 Е.И. Смирнов, Е.Н. Трофимец
Вычислительная модель задачи на рабочем листе
Форма представления вычислительной модели задачи на рабочем листе может быть самой различной. Для рассматриваемой задачи нами предлагается вычислительная модель (см. рис. 1).
А B C D E F G H I
1 Хр-ща ГСМ Центры распределения Запасы ГСМ в хр-щах, т
2 Центр 1 Центр 2 Центр 3 Центр 4 Центр 5
3 Хр-ще 1 4 6 7 9 1 350
4 Хр-ще 2 6 4 1 2 2 200
5 Хр-ще 3 5 8 7 4 9 450
6 Хр-ще 4 2 3 8 5 7 350
7 Потреби. в ГСМ, т 350 400 250 100 250
8
9
10 Хр-ща ГСМ Центры распределения Уравнения (неравенства)
11 Центр 1 Центр 2 Центр 3 Центр 4 Центр 5 лев. часть (вывезено) знак прав. часть (запас)
12 Хр-ще 1 0,00 = 350,00
13 Хр-ще 2 0,00 = 200,00
14 Хр-ще 3 0,00 = 450,00
15 Хр-ще 4 0,00 = 350,00
16 лев. часть (завезено) 0,00 0,00 0,00 0,00 0,00
18 прав. часть (по-требн.) 350,00 400,00 250,00 100,00 250,00
19 Стоимость перевозки 0,00 min
Рис. 1. Вычислительная модель задачи - состояние 1
Представленная вычислительная модель включает в себя две таблицы. Первая таблица служит для ввода и корректировки исходных данных задачи, вторая- для ввода формул и расчета значений, соответствующих оптимальному решению, при этом диапазон В 12:F15 отведен для расчета оптимального плана перевозок, а ячейка G19 - для расчета целевой функции.
Логика построения вычислительной модели задачи на рабочем листе достаточно проста и состоит в записи выражений (8)—(11) в «терминологии» MS Excel. Покажем, как это делается.
Так как мы отвели диапазон B12:F15 для расчета оптимального плана перевозок, то ячейка B12 соответствует переменной х1Ь ячейка C12 — переменной x12, ячейка D12 — переменной x13 и т. д. Любое уравнение (неравенство) состоит из левой части, знака и правой части (заметим, что таким же образом мы обозначили и ячейки в диапазонах G12:I15 и B16:F18). Тогда первое уравнение математической модели задачи (xn + x12 + x13 + x14 + x15 = 350) может быть «записано» на рабочем листе следующим образом:
• левая часть уравнения (xn + x12 + x13 + x14 + x15) — в ячейке G12 путем ввода формулы =CYMM(B12:F12);
• знак "=" — в ячейке H12 путем ввода соответствующего символа с клавиатуры;
• правая часть уравнения (350) — в ячейке I12 путем ввода формулы =G3 (в ячейку I12 можно непосредственно ввести число 350, но первый способ является более удобным при последующих изменениях исходных данных задачи).
Подобным образом записываются и другие уравнения. Уравнения (8) записываются в диапазоне G12:I15, а уравнения (9) - в диапазоне B16:F18. Уравнения можно быстро ввести, если воспользоваться маркером заполнения MS Excel. Для этого необходимо ввести первое уравнение, выделить соответствующие ему три смежные ячейки, после чего с помощью маркера заполнения получить другие однородные уравнения.
Условие неотрицательности (10) на рабочем листе задавать не будем, зададим его позже в диалоговом окне «Параметры поиска решения».
Для расчета целевой функции отведена ячейка G19. Введем в эту ячейку формулу =СУММПРОИЗВ(В3^6;В12^15), которая соответствует математическому выражению (11).
Таким образом, вычислительная модель задачи на рабочем листе разработана, можно переходить к разработке вычислительной модели задачи в диалоговом окне «Поиск решения».
Вычислительная модель задачи в диалоговом окне «Поиск решения»
Находясь на рабочем листе с разработанной вычислительной моделью, вызовем на выполнение надстройку «Поиск решения».
В поле Установить целевую ячейку введем ссылку на ячейку, в которой будет рассчитываться значение целевой функции. Такой ячейкой на рабочем листе является ячейка 019, содержащая формулу расчета стоимости плана перевозок. Так как стоимость плана необходимо минимизировать, то и переключатель Равной необходимо установить в положение минимальному значению (рис. 2).
Поиск решения
^х]
значению:
Установить целевую ячейку: Равной: С максимальному значению
(• минимальному значению Изменяя ячейки:
|$В$12:$Р$15 51
Выполнить
Закрыть
Ограничения:
Предположить
$B$16:$F$16 = $B$18:$F$18 A Добавить
i<ä|12:|G|15 = $I$12:$I$15
Изменить
d Удалить
Параметры
Восстановить
Справка
Рис. 2. Вычислительная модель задачи в диалоговом окне «Поиск решения»
В поле Изменяя ячейки введем ссылку на диапазон ячеек, в которых будут рассчитываться значения искомых переменных, то есть на диапазон В12:Б15.
В поле Ограничения введем ссылки на диапазоны 012:П5 и В16:Б18, которые соответствуют выражениям (8) и (9).
В диалоговом окне «Параметры поиска решения» активизируем флажки Линейная модель и Неотрицательные значения.
Нажмем кнопку Выполнить - получим решение задачи (рис. 3).
A B C D E F G H I
9
10 Хр-ща ГСМ Центры распределения Уравнения (неравенства)
11 Центр 1 Центр 2 Центр 3 Центр 4 Центр 5 лев. часть (вывезено) знак прав. часть (запас)
12 Хр-ще 1 50,00 50,00 0,00 0,00 250,00 350,00 = 350,00
13 Хр-ще 2 0,00 0,00 200,00 0,00 0,00 200,00 = 200,00
14 Хр-ще 3 300,00 0,00 50,00 100,00 0,00 450,00 = 450,00
15 Хр-ще 4 0,00 350,00 0,00 0,00 0,00 350,00 = 350,00
16 лев. часть (завезено) 350,00 400,00 250,00 100,00 250,00
A B C D E F G H I
17 знак = = = = =
18 прав. часть (потребн.) 350,00 400,00 250,00 100,00 250,00
19 Стоимость перевозки 4250,00 min
Рис. 3. Вычислительная модель задачи - состояние 2
План перевозок, рассчитанный в ячейках Б12:Б15, является оптимальным. При данном решении значение целевой функции, рассчитанное в ячейке 019, является минимальным и равно 4250 у. е.
Построенная модель сбалансированной транспортной задачи представляет собой платформой, на основе которой могут быть разработаны различные модифицированные модели, учитывающие разнообразные ситуации, возникающие в практической деятельности. Рассмотрим некоторые из них. Увеличим потребности в ГСМ у первого центра до 500 тонн, а у второго центра до 650 тонн (см.
4 5 4 5
ячейки Б7 и С7 на рис. 4). Получим, что ^а1 =1350, ^Ъ^ = 1750, то есть ^Ъ^ . Таким об-
1=1 1=1 1=1 1=1 разом, спрос превышает предложение и задача стала несбалансированной. В подобной ситуации для решения задачи обычно вводят фиктивного поставщика. При использовании надстройки «Поиск решения» в этом нет необходимости, надо только правильно расставить знаки в системе ограничений. Посмотрим, как это можно сделать в рассматриваемой задаче.
п
С точки зрения экономической интерпретации выражение ^ х^ = а7 означает, что все запасы
1=1
т
должны быть вывезены, а выражение ^ х1]- = Ъ1 - что все потребности должны быть удовлетворены.
1=1
п
В ситуации дефицита запасы будут вывезены полностью, то есть выражение ^ х^ = останется без
1=1
изменений, а вот потребности будут удовлетворены не в полном объеме, то есть уравнение
тт
^ х{- = Ъ1 трансформируется в неравенство ^ х1]- < Ъ1 . Обратим внимание, что используется знак
1=1 1=1
нестрогого неравенства, так как какие-то центры могут быть обеспечены ГСМ в полном объеме, а какие-то нет.
После модификации математической модели задачи внесем соответствующие изменения в ее вычислительную модель. Для этого на рабочем листе в диапазоне Б17:Б17 поменяем знаки "=" на "<" (изменение знаков на рабочем листе, хотя и имеет лишь иллюстративный характер и не влияет на процедуру поиска решения, тем не менее, является очень полезным для понимания сути задачи и позволяет в последующем избежать ошибок при задании новых ограничений), то же самое сделаем и в диалоговом окне «Поиск решения». Нажмем кнопку Выполнить - получим решение модифицированной задачи (рис. 4).
A B C D E F G H I
1 Хр-ща ГСМ Центры распределения Запасы ГСМ в
2 Центр 1 Центр 2 Центр 3 Центр 4 Центр 5 хр-щах, т
3 Хр-ще 1 4 6 7 9 1 350
4 Хр-ще 2 6 4 1 2 2 200
5 Хр-ще 3 5 8 7 4 9 450
6 Хр-ще 4 2 3 8 5 7 350
7 Потребн. в ГСМ, т 500 650 250 100 250
8
9
10 Хр-ща Центры распределения Уравнения (неравенства)
А B C D E F G H I
11 ГСМ Центр 1 Центр 2 Центр 3 Центр 4 Центр 5 лев. часть (вывезено) знак прав. часть (запас)
12 Хр-ще 1 100,00 0,00 0,00 0,00 250,00 350,00 = 350,00
13 Хр-ще 2 0,00 0,00 200,00 0,00 0,00 200,00 = 200,00
14 Хр-ще 3 350,00 0,00 0,00 100,00 0,00 450,00 = 450,00
15 Хр-ще 4 50,00 300,00 0,00 0,00 0,00 350,00 = 350,00
16 лев. часть (завезено) 500,00 300,00 200,00 100,00 250,00
17 знак <= <= <= <= <=
18 прав. часть (потреби.) 500,00 650,00 250,00 100,00 250,00
19 Стоимость перевозки 4000,00 min
Рис. 4. Вычислительная модель задачи - состояние 3
Обратим внимание на то, что ячейки диапазона 012:015 равны ячейкам диапазона 112:115, то есть все запасы вывезены. Ячейки В16, Е16 и Пб равны соответствующим ячейкам в диапазоне В18:Б18, то есть Центр 1, Центр 4 и Центр 5 удовлетворены ГСМ в полном объеме, а ячейки С16 и Б16 меньше соответствующих ячеек в диапазоне В18:Б18, то есть в Центр 2 и Центр 3 горюче-смазочные материалы недопоставлены в размере 250 и 50 тонн соответственно. Ниже мы рассмотрим, как в данной ситуации осуществить «справедливую» недопоставку ГСМ по всем центрам. А сейчас приведем еще ряд важных ситуаций, встречающихся на практике.
Допустим, Вам поступило указание обеспечить Центр 2 в полном объеме. Для этого необходимо лишь изменить в ячейке С17 знак "<" на знак "=" и ввести дополнительное ограничение С16=С18 в диалоговом окне «Поиск решения». Решение представлено на рис. 5.
А B C D E F G H I
9
10 Хр-ща ГСМ Центры распределения Уравнения (неравенства)
11 Центр 1 Центр 2 Центр 3 Центр 4 Центр 5 лев. часть (вывезено) знак прав. часть (запас)
12 Хр-ще 1 0,00 100,00 0,00 0,00 250,00 350,00 = 350,00
13 Хр-ще 2 0,00 200,00 0,00 0,00 0,00 200,00 = 200,00
14 Хр-ще 3 350,00 0,00 0,00 100,00 0,00 450,00 = 450,00
15 Хр-ще 4 0,00 350,00 0,00 0,00 0,00 350,00 = 350,00
16 лев. часть (завезено) 350,00 650,00 0,00 100,00 250,00
17 знак прав. часть <= 500 00 650 00 <= <= 1 00 00 <= 2 50 00
19 (потребн.) Стоимость перевозки 250,00 4850,00 min
Рис. 5. Вычислительная модель задачи - состояние 4
Как видно из рис. 5, в Центр 3 горюче-смазочные материалы вообще не доставляются. Допустим, Вам поступило указание обеспечить Центр 3 не менее чем на 85% от его потребностей. В математической интерпретации это указание запишется как Ь3 > 212,5 . С учетом ранее имеющегося ограничения на потребность ГСМ в Центре 3 будем иметь, что 212,5 < Ь3 < 250.
Ограничение Ь3 < 250 в разработанной модели имеется, остается добавить ограничение Ь3 > 212,5. Это можно сделать различными способами, например, непосредственно в диалоговом окне «Поиск решения» ввести Б16 >= 212,5. Решение представлено на рис. 6.
А В С Б Е С Н I
9
10 Хр-ща ГСМ Центры распределения Уравнения (неравенства)
11 Центр 1 Центр 2 Центр 3 Центр 4 Центр 5 лев. часть (вывезено) знак прав. часть (запас)
12 Хр-ще 1 0,00 100,00 0,00 0,00 250,00 350,00 = 350,00
13 Хр-ще 2 0,00 0,00 200,00 0,00 0,00 200,00 = 200,00
14 Хр-ще 3 137,50 200,00 12,50 100,00 0,00 450,00 = 450,00
15 Хр-ще 4 0,00 350,00 0,00 0,00 0,00 350,00 = 350,00
16 17 лев. часть (завезено) знак 137,50 <= 650,00 212,50 <= 100,00 <= 250,00 <=
18 19 прав. часть (потреби.) Стоимость 500,00 перевозки 650,00 250,00 100,00 250,00 4875,00 шш
Рис. 6. Вычислительная модель задачи - состояние 5
В практической деятельности также встречаются задачи, связанные с блокированием перевозок. Это может быть обусловлено причинами техногенного характера (оползень, сход снега на перевал и т. п.), ремонтно-восстановительными работами (закрытие моста на реконструкцию и т. п.) или, что чаще встречается на практике, коммерческими соображениями.
Например, в плане на рис. 6 осуществляется перевозка по маршруту Хранилище 2 - Центр 3 в размере 200 тонн, необходимо заблокировать перевозку по данному маршруту. В математической интерпретации это указание запишется в виде ограничения х23 = 0 . Другим, «искусственным», способом задания блокировки является назначение большой стоимости перевозки блокируемому маршруту, например, с31 = 1000.
Используем первый способ для блокирования маршрута Хранилище 2 - Центр 3, для чего в диалоговом окне «Поиск решения» введем ограничение Б13 = 0. Второй способ используем для блокирования маршрута Хранилище 3 - Центр 1, для чего в ячейку В5 введем какое-нибудь относительно большое число, например, 1000. Решение представлено на рис. 7.
А В С Б Е С Н I
9
10 Хр-ща ГСМ Центры распределения Уравнения (неравенства)
11 Центр 1 Центр 2 Центр 3 Центр 4 Центр 5 лев. часть (вывезено) знак прав. часть (запас)
12 Хр-ще 1 100,00 0,00 0,00 0,00 250,00 350,00 = 350,00
13 Хр-ще 2 0,00 200,00 0,00 0,00 0,00 200,00 = 200,00
14 Хр-ще 3 0,00 100,00 250,00 100,00 0,00 450,00 = 450,00
15 Хр-ще 4 0,00 350,00 0,00 0,00 0,00 350,00 = 350,00
16 лев. часть (завезено) 100,00 650,00 250,00 100,00 250,00
17 знак прав. часть <= <= <= <=
19 (потребн.) Стоимость перевозки 250,00 5450,00 шш
Рис. 7. Вычислительная модель задачи - состояние 6
Другой распространенной задачей является задача перевозки определенного объема груза по указанному маршруту. Например, между Хранилищем 4 и Центром 1 заключен договор на поставку 300 т ГСМ, а между Хранилищем 1 и Центром 4 - на поставку 100 т ГСМ. Решение задачи представлено на рис. 8.
А B C D E F G H I
9
10 Хр-ща ГСМ Центры распределения Уравнения (неравенства)
11 Центр 1 Центр 2 Центр 3 Центр 4 Центр 5 лев. часть (вывезено) знак прав. часть (запас)
12 Хр-ще 1 0,00 162,50 0,00 100,00 87,50 350,00 = 350,00
13 Хр-ще 2 0,00 200,00 0,00 0,00 0,00 200,00 = 200,00
14 Хр-ще 3 0,00 237,50 212,50 0,00 0,00 450,00 = 450,00
15 Хр-ще 4 300,00 50,00 0,00 0,00 0,00 350,00 = 350,00
16 17 лев. часть (завезено) знак 300,00 <= 650,00 212,50 <= 100,00 <= 87,50 <=
18 19 прав. часть (потребн.) Стоимость 500,00 перевозки 650,00 250,00 100,00 250,00 6900,00 min
Рис. 8. Вычислительная модель задачи - состояние 7
И в заключение рассмотрим схему «справедливой» недопоставки в условиях дефицита, для чего обратимся к модели, представленной на рис. 4. Как видим, недопоставка коснулась только двух центров - Центра 2 и Центра 3, что является по отношению к ним несправедливым решением. Постараемся исправить сложившуюся ситуацию.
Самым простым решением в этом случае является равномерная недопоставка ГСМ во все пять центров. При дефиците в 400 тонн (400 = 1750 - 1350) она будет составлять 80 тонн ГСМ для каждого центра. Однако, как легко заметить, такое решение так же будет несправедливым. Например, недопоставка в 80 тонн для Центра 2 будет лишь «некоторой неприятностью», а для Центра 4 - «бедой». Отсюда следует пропорциональное распределение ГСМ относительно масштабов (потребностей) центров, что, в нашем понимании, и будет являться справедливым решением.
Существуют различные подходы к пропорциональному распределению ресурсов. Рассмотрим два из них.
Первый подход состоит в расчете коэффициента обеспеченности
I
a,
у = J=-
' n
j=i
Корректировка потребностей осуществляется в соответствии с формулой
b^ = ybold,
7 new
где bj - новое значение спроса в j-м центре;
7 old
bj - старое значение спроса в j-м центре.
Второй подход состоит в следующих, несколько более длинных рассуждениях. Вначале определяется доля каждого центра в общем объеме потребностей
л =
1 n
'■1
Z ь,
1=1
Логичным будет в соответствии с этими долями осуществить и недопоставку:
Vн д = d.Vдеф,
где V"д - объем недопоставки в j-й центр;
i
n m
Vдеф = ^ ь. - ^at - общий объем недостающих ресурсов (объем дефицита).
1=1 i=1
Корректировка потребностей осуществляется в соответствии с формулой
inew _ 7 old нд
Ь1 = b1 " V1 .
Выбрав тот или иной способ корректировки потребностей, рассчитаем их новые значения. Все расчеты целесообразно проводить непосредственно на рабочем листе MS Excel, при этом заметим, что задача из несбалансированной вновь превращается в сбалансированную.
После внесения необходимых изменений на рабочем листе и в диалоговом окне «Поиск решения» запускаем процедуру поиска. Решение задачи представлено на рис. 9.
А B C D E F G H I
9
10 Хр-ща ГСМ Центры распределения Уравнения (неравенства)
11 Центр 1 Центр 2 Центр 3 Центр 4 Центр 5 лев. часть (вывезено) знак прав. часть (запас)
12 Хр-ще 1 12,86 144,29 0,00 0,00 192,86 350,00 = 350,00
13 Хр-ще 2 0,00 7,14 192,86 0,00 0,00 200,00 = 200,00
14 Хр-ще 3 372,86 0,00 0,00 77,14 0,00 450,00 = 450,00
15 Хр-ще 4 0,00 350,00 0,00 0,00 0,00 350,00 = 350,00
16 лев. часть (завезено) 385,71 501,43 192,86 77,14 192,86
17 знак = = = = =
18 прав. часть (потребн.) 385,71 501,43 192,86 77,14 192,86
19 Стоимость перевозки 4554,29 min
Рис. 9. Вычислительная модель задачи - состояние 8 В рассмотренной задаче транспортировка ГСМ осуществлялась в условиях дефицита, то есть вы-
т п
полнялось условие ^ Ц Ъ^ . Наряду с «дефицитной» постановкой задачи, возможна и противо-
1=1
положная ей «избыточная» постановка, то есть когда выполняется условие ^ Ц Ъ^ . Исходные
1=1 1=1
данные для такой задачи представлены на рис. 10.
1=1
A B C D E F G H I
1 Хр-ща ГСМ Центры распределения Запасы ГСМ в
2 Центр 1 Центр 2 Центр 3 Центр 4 Центр 5 хр-щах, т
3 Хр-ще 1 4 6 7 9 1 850
4 Хр-ще 2 6 4 1 2 2 400
5 Хр-ще 3 5 8 7 4 9 700
6 Хр-ще 4 2 3 8 5 7 500
7 Потреби. в ГСМ, т 700 400 250 100 250
8
Рис. 10. Исходные данные транспортной задачи с избытком запасов
4 5 4 5
Как видим ^ =2450, ^ Ь] = 1750, то есть наблюдается избыток запасов ГСМ - ^ai Ь] . ¿=1 ]=1 ¿=1 ]=1 В данной ситуации потребности центров будут удовлетворены в полном объеме, то есть имеет место
4
ограничение ^ х{- = Ь■ (будем считать, что удовлетворение потребностей производится в объеме, не
¿=1
превышающем поданных заявок). В то же время запасы ГСМ будут вывезены из хранилищ не в пол-
5
ном объеме, то есть имеет место ограничение ^ х^ < (знак нестрогого неравенства указывает на
] =1
то, что из некоторых хранилищ запасы могут быть вывезены в полном объеме, а из каких-то - не в полном).
Решение задачи представлено на рис. 11.
A B C D E F G H I
9
10 Хр-ща ГСМ Центры распределения Уравнения (неравенства)
11 Центр 1 Центр 2 Центр 3 Центр 4 Центр 5 лев. часть (вывезено) знак прав. часть (запас)
12 Хр-ще 1 550,00 0,00 0,00 0,00 250,00 800,00 <= 850,00
13 Хр-ще 2 0,00 50,00 250,00 100,00 0,00 400,00 <= 400,00
14 Хр-ще 3 0,00 0,00 0,00 0,00 0,00 0,00 <= 700,00
15 16 Хр-ще 4 лев. часть 150,00 700,00 350,00 400,00 0,00 250,00 0,00 100,00 0,00 250,00 500,00 <= 500,00
17 18 знак прав. часть (потребн.) 700,00 400,00 250,00 100,00 250,00
19 Стоимость перевозки 4450,00 min
Рис. 11. Вычислительная модель транспортной задачи с избытком запасов - состояние 1
Предположим, что поступила следующая информация:
а) Хранилище 3 ликвидируется, поэтому запасы ГСМ должны быть вывезены из него в полном объеме;
б) мост по дороге от Хранилища 2 к Центру 3 закрыт на реконструкцию, поэтому необходимо запретить перевозку по указанному маршруту.
Решение задачи представлено на рис. 12.
А B C D E F G H I
9
10 Хр-ща ГСМ Центры распределения Уравнения (неравенства)
11 Центр 1 Центр 2 Центр 3 Центр 4 Центр 5 лев. часть (вывезено) знак прав. часть (запас)
12 Хр-ще 1 0,00 0,00 0,00 0,00 250,00 250,00 <= 850,00
13 Хр-ще 2 0,00 150,00 0,00 100,00 0,00 250,00 <= 400,00
14 Хр-ще 3 450,00 0,00 250,00 0,00 0,00 700,00 = 700,00
15 Хр-ще 4 250,00 250,00 0,00 0,00 0,00 500,00 <= 500,00
16 17 лев. часть (завезено) знак 700,00 400,00 250,00 100,00 250,00
18 19 прав. часть (потреби.) Стоимость 700,00 перевозки 400,00 250,00 100,00 250,00 6300,00 min
Рис. 12. Вычислительная модель транспортной задачи с избытком запасов - состояние 2
Предположим, что неприкосновенный (неснижаемый запас) в Хранилище 2 составляет 300 тонн. Тогда при емкости в 400 тонн из него в пределе может быть вывезено 100 тонн ГСМ. Решение задачи представлено на рис. 13.
A B C D E F G H I
9
10 Хр-ща ГСМ Центры распределения Уравнения (неравенства)
11 Центр 1 Центр 2 Центр 3 Центр 4 Центр 5 лев. часть (вывезено) знак прав. часть (запас)
12 Хр-ще 1 150,00 0,00 0,00 0,00 250,00 400,00 <= 850,00
13 Хр-ще 2 0,00 0,00 0,00 100,00 0,00 100,00 <= 400,00
14 Хр-ще 3 450,00 0,00 250,00 0,00 0,00 700,00 = 700,00
15 16 Хр-ще 4 лев. часть (завезено) 100,00 700,00 400,00 400,00 0,00 250,00 0,00 100,00 0,00 250,00 500,00 <= 500,00
17 18 знак прав. часть (потребн.) 700,00 400,00 250,00 100,00 250,00
19 Стоимость перевозки 6450,00 min
Рис. 13. Вычислительная модель транспортной задачи с избытком запасов - состояние 3
Рассмотренные модели, безусловно, не перекрывают разнообразие всех тех многочисленных ситуаций, которые встречаются в практической деятельности. Тем не менее, их можно рекомендовать в качестве отправных точек для дальнейшего исследования. Направлениями таких исследований могут быть многопродуктовые перевозки, перевозки с промежуточными пунктами, перевозки с учетом грузоподъемности транспортных средств и многие другие.
Анализируя процесс решения транспортной задачи, можно сформулировать общие выводы об основных этапах решения задач линейного программирования в MS Excel. Такими этапами являются:
1. На основании содержательной (вербальной) постановки задачи разрабатывается ее математическая модель.
2. На основании математической модели задачи разрабатывается ее вычислительная модель на рабочем листе.
3. На основании вычислительной модели задачи на рабочем листе разрабатывается вычислительная модель задачи в диалоговом окне «Поиск решения» и находится оптимальное решение.
4. Производится анализ модели задачи после нахождения оптимального решения.
Сформулированная технология является по своей сути концептуальной и применима не только для решения задач линейного программирования, но и других типов задач математического программирования.
Библиографический список
1. Акулич, И. Л. Математическое программирование в примерах и задачах [Текст] : учеб. пособие для студентов эконом. спец. вузов / И.Л. Акулич. - М. : Высшая школа, 1986.
2. Курицкий, Б.Я. Поиск оптимальных решений средствами Excel 7.0 [Текст] / Б.Я. Курицкий. - СПб. : BHV, 1997.
3. Трофимец, Е. Н., Трофимец, В. Я. Оптимизация в Excel [Текст] : учеб. пособие для студентов эконом. спец. вузов / Е.Н. Трофимец, В.Я. Трофимец. - Ярославль : Изд-во ЯГТУ, 2008.
