МОДЕЛИРОВАНИЕ УПРАВЛЕНЧЕСКИХ СИТУАЦИЙ И ИХ РЕШЕНИЕ С ПОМОЩИЮ СРЕДСТВ EXCEL

Формализация моделей линейного программирования

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

1. Менеджер по инвестициям имеет в своем распоряжении определенный капитал. Инвестиционные решения ограничены суммой данного капитала и распоряжениями таких правительственных органов, как Комиссия по ценным бумагам и биржам.

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

3. Планы полетов авиакомпании ограничены необходимостью обслуживания самолетов и числом сотрудников.

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

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

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

В приведенных выше примерах менеджер по инвестициям, скорее всего, будет стремиться максимизировать прибыль от портфельных инвестиций; директор завода захочет удовлетворить спрос при минимальных производственных затратах. Аналогично авиакомпания будет стремиться реализовать заданное расписание с минимальными издержками, а нефтеперерабатывающая компания— использовать имеющуюся сырую нефть с максимальной прибылью.

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

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

1. Стулья, произведенные компанией, продаются на той же неделе, удельная валовая прибыль (доход минус расход) составляет $56 для каждого проданного стула марки Captain и $40 для каждого стула марки Mate.

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

3. Запас длинных и коротких штифтов, которые можно будет использовать на следующей неделе, составляет 1280 и 1600 штук соответственно. Для производства одного стула марки Captain требуется 8 длинных и 4 коротких штифта, а для производства стула Mate — 4 длинных и 12 коротких штифтов (табл. 1).

4. Запас ножек на следующую неделю составляет 760 штук. Для производства одного стула любого типа требуется 4 ножки (табл.2).

5. Запас прочных и облегченных сидений составляет 140 и 120 штук соответственно (табл. 3). Для производства стульев Captain используются прочные сиденья, а для Mate — облегченные.

6. Согласно договору между руководством компании и профсоюзом общее число произведенных стульев не может быть менее 100.

Задача состоит в том, чтобы в данных условиях определить, сколько стульев каждой марки необходимо произвести на следующей неделе. Используя терминологию моделирования, нужно найти оптимальный ассортимент продуктов, или составить оптимальный план производства. Покажем, как данную ситуацию можно представить в виде задачи линейного программирования, а затем — в виде оптимизационной модели Excel. Для этого необходимо определить ограничения и целевую функцию.

Как уже отмечалось, существует ограниченный запас деталей, из которых можно собирать стулья Captain и Mate. Это ограничивает суммарное количество стульев, которые можно собрать. Чтобы точно сформулировать ограничения, начнем с определения необходимого количества длинных штифтов. Длинные штифты требуются для производства обоих видов стульев. На изготовление одного стула Captain идет 8 длинных штифтов, a Mate — 4. Таким образом, для любого плана выпуска справедливо следующее равенство:

8 х (к-во произведенных Captain) + 4 х (к-во произведенных Mate) =

Введем обозначения: пусть С — количество произведенных стульев Captain, M — количество произведенных стульев Mate. Тогда выражение для суммарной потребности в длинных штифтах примет следующий вид:

8С + 4M= суммарная потребность в длинных штифтах.

Однако запас длинных штифтов составляет 1280 штук. Поэтому переменные решения С и М должны соответствовать ограничению

Это ограничение на суммарную потребность в длинных штифтах. Условие (1) называется ограничением в виде неравенства. Число 1280 называется правой частью неравенства. Левая часть неравенства, которая зависит от неизвестных С и М, называется функцией ограничения. Неравенство (1)— символический способ представления ограничения, требующего, чтобы суммарная потребность в длинных штифтах для производства С штук стульев Capitan и М штук стульев Mate не превышала имеющийся запас— 1280 штук длинных штифтов.

Для производства одного стула Captain требуется 4 коротких штифта, a Mate— 12. Поскольку запас коротких штифтов составляет 1600 штук, С и М должны также соответствовать ограничению

Неравенства (1) и (2) — два ограничения данной модели. Есть ли другие ограничения? В перечне пунктов, которые необходимо учесть, говорится о существовании соглашения с профсоюзом. Оно касается общего выпуска стульев:

Отметим, что условие (3) является неравенством типа "≥" в отличие от условий (1) и (2), которые являются неравенствами типа "≤".

Еще одно ограничение отражает тот факт, что для сборки каждого стула требуется 4 ножки, а запас ножек составляет 760 штук.

В пятом пункте списка говорится, что для изготовления стула Captain требуется прочное сиденье, а для Mate — облегченное. Указаны также запасы сидений обоих видов. Эта информация записывается в виде двух ограничений:

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

Условие вида (6), которое требует, чтобы переменные принимали неотрицательные значения, называется условием неотрицательности. Следует помнить, что неотрицательность не то же самое, что положительность. Неотрицательность допускает значение 0, в то время как положительность не допускает нулевого значения.

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

Значение пары переменных С и М называется решением; сами переменные С и М называются переменными решения. В данной задаче решение — это структура производства изделий (стульев). Например, С = 6, М = 5 — это решение сделать 6 стульев марки Captain и 5 стульев Mate. Некоторые неотрицательные решения будут соответствовать всем ограничениям модели (1)-(5), другие — нет. Так, решение С= 6, М= 5 удовлетворяет ограничениям (1), (2), (4), (5) и (6), но нарушает ограничение (3). Данное решение недопустимо, поскольку нарушает одно из ограничений.

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

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

При перечислении основных производственных факторов отмечалось, что удельная прибыль составляет $56 для стульев Captain и $40 для Mate. Тогда

Таким образом, решение произвести С стульев марки Captain и М стульев марки Mate приведет к получению суммарной прибыли, вычисляемой по формуле

Заметим, что если известны только данные о доходах, единственное, что можно сделать — это максимизировать доход при соблюдении ограничений. Если же доступны только данные о затратах (себестоимости), то нужно минимизировать затраты, связанные с производством определенного ассортимента изделий. Однако когда известны и данные о доходах, и данные о затратах, предпочтительней максимизировать прибыль, а не просто доход.

Среди бесконечного множества решений, удовлетворяющих всем ограничениям (т.е. среди допустимых решений), существует такое, которое обеспечивает наибольшую суммарную валовую прибыль. Это решение будем называть решением задачи, или оптимальным решением. Таким образом, среди всех возможных допустимых решений мы ищем решение, которое максимизирует недельную прибыль. Суммарная прибыль является функцией переменных С и М, поэтому выражение 56С + 40М называется целевой функцией. Итак, надо найти допустимые значения С и М, которые оптимизируют (в нашем случае максимизируют) целевую функцию. В символической форме это можно записать следующим образом:

Целевую функцию необходимо максимизировать только на множестве допустимых решений.

В предшествующих разделах мы преобразовали словесное описание ситуации реального мира в символическую (математическую) модель, состоящую из целевой функции и ограничений. Эта модель называется моделью линейного программирования и имеет следующий вид:

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

С математической точки зрения с нелинейными функциями работать значительно сложнее, чем с линейными. Сила и привлекательность линейного программирования заключается в простоте линейных связей (уравнений и неравенств) и в том, что менеджеры и аналитики могут использовать линейные модели в практических приложениях, почти не имея специальной математической подготовки. На данном этапе важно запомнить следующее.

1. В задаче линейного программирования всегда присутствуют целевая функция (которую необходимо максимизировать или минимизировать) и ограничения;

2. Все функции (целевая функция и ограничения) в моделях ЛП являются линейными.

Посмотрим еще раз на формулировку задачи. Следует отметить, что если не наложить дополнительные ограничения, требующие, чтобы значения переменных решения были целыми, нам, скорее всего, придется рассматривать дробные решения. Для многих моделей ЛП, как и в данном случае, дробные значения переменных решения не имеют физического смысла. Например, решение “произвести 3,12 стульев Captain и 6,88 стульев Mate" реализовать невозможно. С другой стороны, для многих задач дробные значения, безусловно, имеют смысл (например, "произвести 98,65 галлонов бензина"). В тех случаях, когда дробные ответы смысла не имеют, существует четыре возможных выхода.

1. Добавить в модель ЛП так называемое условие целочисленности, которое требует, чтобы одна или несколько переменных решения принимали только целые значения. Это приведет к изменению модели, которая превратится в модель целочисленной оптимизации или целочисленного программирования. Модели целочисленного программирования имеют дополнительные особенности, которые отличают их от обычных моделей ЛП, поэтому на данном этапе мы их рассматривать не будем.

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

3. Можно считать, что результаты работы модели задают средний недельный уровень производства для периода из нескольких последующих недель. Например, решение "произвести 70,5 стульев Captain и 80,25 стульев Mate" можно реализовать следующим образом: согласно производственному плану еженедельно производится 70,5 стульев Captain, но 1) каждую первую неделю продается 70 стульев Captain, а половина стула переходит на следующую неделю как полуфабрикат, который следует закончить; 2) каждую вторую неделю продается 71 стул марки Captain. Аналогично еженедельно производится 80,25 стульев марки Mate, но 1) каждые три недели продается только 80 стульев этой марки, а все незаконченные части стула рассматриваются как полуфабрикат, и 2) каждую четвертую неделю продается 81 стул Mate. Если следовать этим правилам, то среднее недельное производство для четырехнедельного периода действительно составит 70,5 Captain и 80,25 Mate, как предписывается решением задачи ЛП.

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

На практике применяются все вышеуказанные подходы. На данном этапе будем считать, что дробные значения представляют некие средние уровни производства (вариант 3), или модель разрабатывается в качестве основы для планирования и анализа (вариант 4).

Чтобы описать управленческую ситуацию в виде символической (математической) модели, полезно сначала составить "словесную модель". Это делается следующим образом.

1. Описать словами цель и целевую функцию, т.е. показатель эффективности.

2. Дать словесное описание каждого ограничения, обращая особое внимание на то, является данное ограничение требованием в форме неравенств или равенством.

3. Шаги 1 и 2 приведут к словесному описанию переменных решения.

Очень важно правильно определить переменные решения. Иногда существует несколько возможных вариантов. Например, должны ли переменные решения представлять килограммы готовой продукции или килограммы сырья? Советуем в этом случае задать вопрос: "Какие решения нужно принять, чтобы оптимизировать целевую функцию?". Ответ на этот вопрос поможет правильно выявить переменные решения.

После выполнения пп. 1-3 следует присвоить обозначения (или имена) переменным решения. Затем необходимо выполнить такие действия.

4. Выразить все ограничения через обозначенные переменные решения.

5. Выразить с помощью обозначенных переменных целевую функцию.

На данном этапе следует проверить модель на соответствие единиц измерения. Например, если коэффициенты целевой функции даны в долларах за килограмм, то переменные решения, входящие в целевую функцию, должны выражаться в килограммах, а не в тоннах или унциях. Аналогично нужно проверить соответствие единиц измерения в правой и левой частях каждого ограничения. Например, если налагается ограничение на число часов рабочего времени, то в правой части ограничения должны быть указаны часы рабочего времени. Тогда, если переменные решения измеряются в килограммах, то значения коэффициентов для данной функции ограничения (т.е. числовые коэффициенты перед каждой переменной решения в левой части ограничения) должны выражаться в часах рабочего времени, деленных на килограмм. Нельзя допускать, чтобы в одной части равенства или неравенства стояли часы, а в другой — минуты, секунды, килограммы или тонны.

Рассмотрим еще один аспект формирования модели ЛП. Как уже отмечалось, ограничения могут иметь форму неравенств типа "≤" или "≥". Студенты часто задают вопрос, бывают ли в модели линейного программирования ограничения в виде строгих неравенств типа "≤" или "≥". Ответ — нет. Причина этого имеет математическую природу: так делается для того, чтобы надлежащим образом сформулированная задача имела решение. Математическое доказательство данного утверждения не входит в нашу задачу. Однако не будет преувеличением сказать, что практически в любой реальной жизненной ситуации, в которой встречаются ограничения, неравенств типа "≤" или "≥" вполне достаточно, чтобы передать реальный смысл. Например, если переменная X должна быть ≤15, то в модели вполне можно использовать ограничение Х≤14,9999999999.

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

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

Невозвратные издержки уже были сделаны, это означает, что никакие будущие решения не смогут повлиять на эти расходы. Предположим, было закуплено с последующей доставкой 800 и 500 фунтов алюминия двух сортов (1 и 2) по фиксированным ценам $5 и $10 за фунт соответственно, и контракт уже оплачен. Задача состоит в том, чтобы определить, как оптимально использовать эти 1300 фунтов алюминия, чтобы максимизировать прибыль, полученную от производства алюминиевых шарниров и трубок. С каждым из двух изделий связан доход и переменные затраты на его производство (затраты на механическую обработку, штамповку и т.д.). При формировании модели невозвратные затраты $9000 на закупку алюминия роли не играют. Эта сумма уже потрачена, следовательно, количество закупленного алюминия не является переменной решения. Переменными будут количества изделий, которые следует произвести, и для их определения нужно учитывать только переменные издержки. Сформулируем модель, соответствующую данному описанию. Пусть

К— количество производимых шарниров (переменная решения);

С— количество производимых трубок (переменная решения);

$4 — затраты на производство шарнира (переменные издержки);

$12 — затраты на производство трубки (переменные издержки).

Для каждого продукта мы должны вычислить удельную валовую прибыль, т.е. разность между удельным доходом и удельными переменными издержками. Удельная валовая прибыль составляет для шарниров $10 -$4 = $6, для трубок $30 — $12=$18.

Предположим, что для изготовления одного шарнира используется 1 фунт алюминия 1 сорта и 2 фунта алюминия 2 сорта. Для изготовления трубки требуется 3 фунта алюминия 1 сорта и 5 фунтов 2 сорта. Получается следующая модель линейного программирования:

К+ЗС≤ 800 (ограничение на количество алюминия 1 сорта);

2К+ 5С≤ 500 (ограничение на количество алюминия 2 сорта);

Чтобы показать независимость решения от невозвратных издержек, заметим, что целевая функция в нашей формулировке является суммарной валовой прибылью. Чистая прибыль вычисляется следующим образом; чистая прибыль = валовая прибыль — невозвратные издержки = 6К +18C =9000.

Найти допустимые значения К и С, максимизирующие выражение 6К+ 18С=9000 все равно, что найти допустимые значения К и С, максимизирующие выражение 6К+ 18С. Константу 9000 можно игнорировать. Таким образом, если к оптимизируемой функции прибавить некую константу или умножить функцию на некоторое постоянное положительное число, результат оптимизации не изменится, т.е. оптимальные значения переменных решения останутся неизменными. Однако если прибавить (или отнять) одно и то же постоянное число ко всем коэффициентам переменных решения в целевой функции, результат может измениться.

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

Напомним, что модель ЛП недельного производства компании выглядит следующим образом (С— количество производимых стульев марки Captain, а М— количество производимых стульев Mate).

4С+ 12M ≤ 1600 (ограничение для коротких штифтов);

М≤ 120 (ограничение для облегченных сидений);

Обратите внимание на то, что ограничения были перегруппированы так, чтобы однотипные неравенства находились рядом. Причина такой группировки станет понятна при описании работы средства Поиск решения. Табличная версия упрощенной модели, созданная в рабочей книге Excel Стулья.xls, представлена на рис.1. Здесь показан случай, когда производится 110 стульев Captain и 90 стульев Mate. Заметим, что при таком ассортименте нарушается ограничение для ножек — их требуется больше, чем имеется.

Рис. 1. Упрощенная модель ЛП производства компании

Совет. Наиболее простой способ ввода символов неравенства, таких как ≤ в ячейке Е6, состоит в том, чтобы ввести в ячейку символ ≤, а затем щелкнуть мышью на кнопке Подчеркнутый на панели инструментов форматирования Excel.

Хотя содержимое показанного рабочего листа в особых пояснениях не нуждается, следует сверить формулы на листе (см. рис.1) с формулами математической модели производства компании. Обратим ваше внимание на некоторые "неочевидные" аспекты данного рабочего листа.

Многие ячейки рабочего листа содержат числа. Эти числа представляют

a. числовые значения коэффициентов и правых частей неравенств, они называются параметрами данной модели ЛП;

b. числовые значения двух переменных решения. Они называются значениями решений или просто решениями.

Формулы в Excel используются для вычисления значений целевой функции, функций ограничений и левых частей неравенств (записаны в столбце D). В некоторых случаях используются вспомогательные формулы, с помощью которых вычисляются числовые значения различных коэффициентов модели. Таким образом, числовые значения одних коэффициентов вводятся непосредственно, а других — вычисляются по формулам.

За исключением ячеек G11 и G12, все элементы таблицы имеют очевидный смысл. Осталось объяснить, что представляет собой элемент под названием Резерв в ячейке G12.

В моделях ЛП термином резерв обозначается неотрицательная разность функции ограничения и его правой части.

Часто предпочтительней использовать более содержательные названия (чем "резерв"), например, Конечный запас (т.е. запас на конец недели, как в ячейке G5). Более того, вычисления в столбце G однотипны. Их назначение — показать, насколько близко значение функции ограничения к значению правой части неравенства, при этом нулевой резерв свидетельствует о том, что в ограничении достигнуто равенство. Например, формула =F6-D6 в ячейке G6 соответствует ограничению для длинных штифтов 8С+ 4M ≤ 1280. Здесь из правой части данного ограничения вычитается левая часть. Таким образом, значение запаса на конец периода (или "резерв" для данного ограничения) — это количество неиспользованных длинных штифтов. Однако в ячейке G12, соответствующей ограничению С+М ≥ 100, записана формула "левая часть ограничения минус правая часть"; такой порядок вычитания обусловлен тем, что резерв должен быть неотрицательной величиной для допустимых решений. Итак, сформулируем следующее правило.

Для ограничений типа ≤ при вычислении резерва из правой части неравенства вычитается левая часть.

Для ограничений типа ≥ при вычислении резерва из левой части неравенства вычитается правая часть.

Хотя вычисление резерва и не является обязательным, оно очень полезно. Например, сразу становится очевидным, что производственный план недопустим, поскольку запас на конец периода в ячейке G8 получился отрицательным.

Один из очевидных способов использования полученной модели компании - проведение анализа "Что-если" для различных решений (т.е. различных значений производства стульев Captain и Mate). Для этого следует ввести соответствующие значения в ячейки В4 и С4 и просмотреть значения в ячейке D4, представляющие недельную валовую прибыль. При этом нужно следить, чтобы значения резерва в ячейках G6:G12 были неотрицательными. Если ввести в ячейку В4 значение 20, а в ячейку С4 значение 80 (что означает С= 20, М= 80), то результирующая таблица будет выглядеть так, как показано на рис.2.

Рис. 2. Модель производства компании для С=20 и M.=80

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

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

Рис. 3. Значения С и М, приносящие максимальную прибыль

Модель ЛП и ее представление в электронных таблицах

Итак, у нас есть два представления модели производства компании: символическая (математическая) модель ЛП и ее представление в электронной таблице, которую будем называть табличной моделью.

В связи с этим может возникнуть несколько вопросов. Обязательно ли для каждой моделируемой управленческой ситуации составлять обе модели (символическую и табличную)? Почему табличная модель выглядит именно так, а не иначе? Как использовалось средство Поиск решения для получения оптимального решения, показанного на рис.3?

Ответим на первый вопрос: пока вы не обретете определенный опыт, следует записывать обе версии модели, как символическую (математическую), так и табличную. Электронная таблица хорошо подходит для представления моделей ЛП, особенно при проведении анализа "Что-если". Однако новичкам не следует формировать модель ЛП сразу в электронной таблице. Пока нет достаточного опыта, для создания "правильной" модели линейного программирования в Excel данный процесс лучше разбить на три этапа.

1. Написание и проверка символической модели ЛП. Модель записывается на бумаге в математическом виде; это не займет много времени и поможет при отладке окончательного варианта табличной модели в Excel. Затем анализируются формулировки математической задачи с целью выявления возможных логических ошибок.

2. Создание и отладка табличной модели ЛП. На основе символической модели ЛП создается ее представление в Excel. Затем производится проверка полученной табличной модели путем задания различных значений переменных решения с целью выявить возможные очевидные ошибки (например, для заведомо допустимых решений нарушаются ограничения, значения в ячейках левых частей или критерий эффективности оказываются лишенными смысла и т.д.).

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

Созданная на первом этапе математическая модель полезна для целей документации, она позволяет увидеть всю модель целиком, что облегчает понимание табличной модели в Excel. Для достаточно сложных моделей проще сначала проанализировать структуру символической модели ЛП, а не ее представление в Excel. Фактически после ответа на оставшиеся два вопроса в последующих нескольких главах мы будем заниматься именно объяснением структуры моделей ЛП, представленных в Excel.

Ответим на второй вопрос ("Почему табличная модель выглядит именно так, а не иначе?"). Модель в Excel построена в соответствии с рекомендациями о представлении моделей в Excel. Именно из-за неправильного построения моделей в Excel студентам часто не удается получить нужные результаты на этапе оптимизации. Наши рекомендации позволяют выявить скрытые ошибки в задании связей между ячейками в формулах и избежать определенных проблем интерпретации результатов, получаемых с помощью средства Поиск решения. Накопив опыт формирования моделей линейного программирования в Excel, можно пропускать этап написания символической модели. Для тех, кто такого опыта пока не имеет, предлагаем следующие рекомендации по созданию табличной модели ЛП в Excel.

• Каждая переменная решения располагается в отдельной ячейке, ячейки группируются по строкам или столбцам; каждому ограничению отводится отдельная строка или столбец таблицы. (Чаще всего переменные решения расположены в столбцах, а ограничения — в строках.)

• Переменные решения группируются в отдельный блок столбцов/строк; аналогично ограничения группируются в свой блок строк/столбцов.

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

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

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

• Коэффициент перед определенной переменной решения в каком-либо ограничении записывается в ячейку на пересечении столбца (строки), содержащего данную переменную решения, и строки (столбца), содержащей это ограничение.

• В каждой строке ограничений за ячейками, содержащими коэффициенты данного ограничения, следует ячейка, в которую записано вычисленное значение функции ограничения (значение левой части неравенства), за ней следует ячейка, в которой стоит соответствующий знак неравенства, а затем ячейка, содержащая значение правой части неравенства. Дополнительно может включаться ячейка с формулой вычисления резерва, т.е. разности между значениями левой и правой частей неравенства, вычисляемой таким образом, чтобы она была неотрицательной при соответствии ограничению.

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

• Не следует использовать в формулах модели ЛП функции Excel ЕСЛИ, ABS, MAX, MIN и другие нелинейные функции. Такие функции могут использоваться в формулах рабочего листа, но только в том случае, если они не влияют (прямо или косвенно) на вычисление целевой функции.

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

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

Поиск решения — это надстройка, входящая в поставку Excel, предназначенная для оптимизации моделей при наличии ограничений, в том числе моделей линейно<

Что делать, если нет взаимности? А теперь спустимся с небес на землю. Приземлились? Продолжаем разговор...

Конфликты в семейной жизни. Как это изменить? Редкий брак и взаимоотношения существуют без конфликтов и напряженности. Через это проходят все...

ЧТО ПРОИСХОДИТ, КОГДА МЫ ССОРИМСЯ Не понимая различий, существующих между мужчинами и женщинами, очень легко довести дело до ссоры...

Что делает отдел по эксплуатации и сопровождению ИС? Отвечает за сохранность данных (расписания копирования, копирование и пр.)...

Не нашли то, что искали? Воспользуйтесь поиском гугл на сайте: