Методические указания к проведению
лабораторной работы № 1
Тема: Анализ микроэкономических объектов средствами Excel.
Программное обеспечение: Microsoft Excel
Основные сведения
В большинстве оптимизационных задач зависимости между переменными линейны. Линейность предполагает наличие двух свойств пропорциональности и аддитивности.
1. Пропорциональность означает, что вклад каждой переменной в целевую функцию и общий объем потребления соответствующих ресурсов прямо пропорционален уровню (величине) этой переменной.
Аддитивность заключается в том, что целевая функция представляет собой сумму вкладов от различных переменных. Аналогично левая часть каждого ограничения должна представлять собой сумму расходов, каждое слагаемое которой пропорционально величине соответствующей переменной. Если, например, фирма, производит два конкурирующих вида продукции, увеличение сбыта одного из которых отрицательно сказывается на объеме реализации другого, то такая модель не обладает свойством аддитивности.
Математическую модель задачи линейного программирования в общем виде можно записать в виде:
min (max) W = c1 x1 + c2 x2 +... + cn xn; (целевая функция)
при ограничениях:
a11 x1 + a12 x2 + ... + a1n xn £ (=, ³) b1
a21 x1 + a22 x2 + ... + a2n xn £ (=, ³) b2
. . .
am1 x1 + am2 x2 + ... + amn xn £ (=, ³) bm
xj ³ 0, j=1, 2, ... n
Рассмотрим конкретную задачу:
Задача № 1. Руководство фирмы предполагает производить продукцию двух моделей А1 и А2. Их производство ограниченно наличием сырья, временем эксплуатации оборудования и денежными кредитами. Для каждого изделия модели А1 требуется 0,3 м3 древесины, 0,2 часа работы станков и затратить 1,6 денежных единиц, а для изделия модели А2 - 0,4 м3 древесины, 0,5 часа работы станков и 1 ден. ед. Фирма может получить от своих поставщиков до 170 м3 древесины в неделю и использовать оборудование в течение 160 часов. На финансирование проекта предполагается выделять 800 ден. ед. Сколько изделий каждой модели следует фирме выпускать в неделю, если каждое изделие модели А1 должно приносить 2 ден. ед. прибыли, а каждое изделие модели А2 - 4 ден. ед. прибыли?
Построение математической модели
Переменные. Так как нужно определить объемы производства каждого вида моделей продукции, переменными в модели являются:
х1 - количество выпущенных за неделю изделий модели А1,
х2 - количество выпущенных за неделю изделий модели А2.
Целевая функция. Так как прибыль от реализации 1-го изделия модели А1 равна 2 денежным единицам, недельный доход от ее продажи составит 2*х1 ден. ед. Аналогично доход от реализации х2 штук изделия модели А2 составит 4*х2 ден. ед. в неделю.
При допущении независимости объемов сбыта каждой из моделей общий доход равен сумме двух слагаемых - дохода от продажи модели А1 и дохода от продажи модели А2.
Обозначив общий доход через W, можно дать следующую математическую формулировку целевой функции: определить (допустимые) значения х1 и х2, максимизирующие величину общего дохода W = 2*х1 + 4*х2.
Ограничения. При решении рассматриваемой задачи должны быть учтены ограничения на расход древесины, время эксплуатации оборудования и финансовые возможности фирмы.
Ограничение на расход древесины можно записать следующим образом:
0,3 х1 + 0,4 х2 £ 170,
здесь 0,3 х1 расход древесины на выпуск недельного объема в х1 изделий модели А1, а 0,4 х2 - х2 изделий модели А2. Суммарный расход древесины на выпуск двух моделей не может превышать максимально возможный запас древесины в 170 м3.
Ограничение на время использование оборудования можно записать следующим образом:
0,2 х1 + 0,5 х2 £ 160,
здесь: 0,2 х1 - количество часов работы оборудования в неделю для выпуска х1 изделий модели А, а 0,5 х2 - х2 изделий модели В в неделю. Время эксплуатации станков для выпуска обоих моделей не может превышать максимально возможный запас времени работы оборудования в160 часов.
Ограничение на использование финансов можно записать следующим образом:
1,6 х1 + 1,0 х2 £ 800,
здесь: 1,6*х1 - количество денежных ресурсов расходуемых в неделю для выпуска х1 изделий модели А, а 1,0*х2 - х2 изделий модели В в неделю. Сумма затрат на выпуск обоих моделей не может превышать максимально возможный запас финансов.
Поскольку х1 и х2 выражают еженедельный объём выпускаемых изделий, то они не могут быть отрицательными, т. е. х1 ³ 0 и х2 ³ 0 (условие не отрицательности переменных).
Итак, математическую модель задачи № 1 можно записать следующим образом:
max W= 2 х1 +4 х2 (целевая функция) (1)
при ограничениях:
0,3 х1 + 0,4 х2 £
0,2 х1 + 0,5 х2 £
1,6 х1 + х2 £
х1 ³ 0, х2 ³ 0 (5)
Процесс решения задачи
Вызовите Microsoft Excel. В новой рабочей книге переименуйте «Лист№1» на «Задача № 1».
Начиная с ячейки с именем А1 постройте следующую таблицу:
Поиск оптимального плана выпуска продукции
Затраты ресурсов на выпуск одной единицы продукции | Прибыль от реализации одной единицы продукции | Программа выпуска продукции | Общая прибыль | |||
древесина | время | деньги | ||||
Продукция модели А | 0,3 | 0,2 | 1,6 | 2 | 0 | 0 |
Продукция модели В | 0,4 | 0,5 | 1 | 4 | 0 | 0 |
Наличие ресурсов | 170 | 160 | 800 | |||
Ограничения | 0 | 0 | 0 | |||
Итого: | 0 |
Для переменных x1, x2,..., xn (n £ 200) должны быть выделены произвольно ячейки. В эти ячейки можно записать нули или вообще ничего не записывать. После решения в них появятся оптимальные значения переменных. Для задачи № 1 это ячейки с именами F4 - F5 (см. Рис. 1.)
Ограничение описывается ячейкой или интервалом ячеек, обычно с формулой, которая зависит от одной или более определяемых переменных (ячеек). Для каждой задачи можно указать два ограничения для каждой переменной (изменяемой ячейки - одно ограничение сверху и одно снизу), плюс до ста дополнительных ограничений. В ограничении можно указать отдельные ячейки или интервалы ячеек. Однако ограничения можно накладывать не более, чем на тысячу ячеек в одной задаче.
Запись формулы должна начинаться со знака ”=“, а вместо переменных должны стоять те изменяемые ячейки, которые вы для них определили.
Рис. 1. Вид листа рабочей книги с введенной таблицей
Для условий задачи № 1 заносятся следующие ограничения:
в ячейку В7 - =B4*F4+B5*F5, что соответствует выражению - (0,3 х1 + 0,4 х2)
C7 - =C4*F4+C5*F5 - (0,2 х1 + 0,5 х2)
D7 - =D4*F4+D5*F5 - (1,6 х1 + х2)
После записи формул ограничений сделайте активной целевую ячейку (ту, в которую вы решите записать значение целевой функции – G8) и занесите туда формулу целевой функции. Там после решения появится ее максимальное значение.
Для задачи № 1 заносим:
в ячейку G4 - = Е4*F4, что соответствует члену целевой функции - (2 х1)
G5 - = Е5*F5, - (4 х2)
G8 - = G4+ G5, - целевая функция (2 х1 +4 х2)
Ячейка G8 остается активной. В меню Сервис выберите команду Поиск решения. Откроется диалоговое окно Поиск решения (Рис. 2.).
В строке Установить целевую ячейку появится имя активной ячейки $G$8 (если вы хотите его заменить, то наберите нужное имя).
Из трех альтернативных вариантов нахождения целевой функции в поле - Равной: максимальному значению, минимальному значению или конкретно заданному значению целевой функции, необходимо выбрать один из вариантов.
 |
Рис. 2. Вид диалогового окна Поиск решения
Для задачи № 1 это вариант «Максимальному значению».
В строку Изменяя ячейки занесите имена ячеек соответствующие переменным x1, x2,..., xn или сделайте их активными (установите курсор мыши на ячейку выделенную под переменную x1, нажмите левую кнопку мыши и не отпуская её переместите курсор на ячейку выделенную под переменную xn), и имена автоматически запишутся в строку. Интервал ячеек записывается через символ – «;», а перечисляются они через символ – «:».
Для задачи № 1 - Нажмите левую кнопку мыши и не отпуская ее проведите указателем мыши, по ячейкам F4 -F5 в строке (Изменяя ячейки) появится $F$4:$F$5.
 |
Затем нажмите кнопку (Добавить) откроется диалоговое окно Добавление ограничения (Рис.3).
Рис. 3. Вид диалогового окна Добавление ограничения
В строке (Ссылка на ячейку) вы должны записать имя ячейки, в которой содержится формула какого-нибудь ограничения. Но можно и не набирать на клавиатуре, а просто сделать эту ячейку активной.
Во втором поле вы просто должны выбрать знак ограничения (>=, <=, =).
В третьем поле (Ограничение) вы пишете численное значение ограничения или имя ячейки, где оно хранится (последнее сделать предпочтительнее).
После того, как вы записали одно ограничение, нажмите кнопку Добавить. На экране появится снова пустое окно ограничений, и вы запишете туда следующее ограничение. Для окончания работы нажмите кнопку ОК.
Для задачи № 1 заносятся следующие ограничения:
$В$7 <= $B$6 что соответствует ограничению (0,3 х1 + 0,4 х2 £ 170)
$C$7 <= $C$6 (0,2 х1 + 0,5 х2 £ 160)
$D$7 <= $D$6 (1,6 х1 + х2 £ 800)
Обязательно нужно записать ограничения для переменных. Это лучше всего выполнить, сделав изменяемые ячейки активными или записав их по строке через двоеточие.
$F$4 >= 0[1] (х1 ³ 0)
$F$5 >= 0 (х2 ³ 0)
или
$F$4:$F$5 (х1, х2 ³ 0)
Чтобы удалить ограничение в окне Ограничения выделите ограничения(мышкой), которые требуется удалить и нажмите кнопку Удалить.
 |
Чтобы изменить ограничения (его само, ссылку на ячейку или соотношение между ячейкой и ограничением) , выделите их и нажмите кнопку Изменить. Появится диалоговое окно Изменить ограничение. Сделайте необходимые изменения и нажмите OK.
Когда все ограничения будут записаны, нажмите OK. Вы вернетесь в диалоговое окно Поиска решения. Проверьте, записались ли все ограничения.
Если вы хотите изменить параметры поиска решения, установленные по умолчанию, то нажмите кнопку Параметры... и на экране появится диалоговое окно Параметры поиска решения.
Можно изменять условия и варианты поиска решения для линейных и нелинейных задач, а также загружать и сохранять оптимизируемые модели. Значения и состояния элементов управления, используемые по умолчанию, подходят для решения большинства задач.
Максимальное время - служит для ограничения времени, отпускаемого на поиск решения задачи. В поле можно ввести время (в секундах) не превышающее 32767; значение 100, используемое по умолчанию, подходит для решения большинства простых задач.
 |
Рис.4. Вид диалогового окна Параметры поиска решения
Итерации - служит для управления временем решения задачи, путем ограничения числа промежуточных вычислений. В поле можно ввести количество итераций не превышающее 32767; значение 100, используемое по умолчанию, подходит для решения большинства простых задач.
Точность - служит для задания точности, с которой определяется значения искомых переменных или приближение их значений к указанным границам. Поле должно содержать число из интервала от 0 (нуля) до 1. Низкая точность соответствует введенному числу, содержащему меньшее количество десятичных знаков, чем число, используемое по умолчанию 0,000001 например, 0,0001. Высокая точность увеличит время, которое требуется для того, чтобы сошелся процесс оптимизации. Можно существенно ускорить процесс поиска решения, если задать исходные значения влияющих ячеек модели близкими к искомым результатам.
Допустимое отклонение - служит для задания допуска на отклонение от оптимального решения, если множество значений влияющей ячейки ограничено множеством целых чисел. При указании большего допуска поиск решения заканчивается быстрее.
Линейная модель - служит для ускорения поиска решения линейной задачи оптимизации или линейной аппроксимации нелинейной задачи.
Показывать результаты итераций - служит для приостановки поиска решения для просмотра результатов отдельных итераций. С остальными параметрами мы познакомимся позднее.
Для задачи № 1 установите флажки перед вариантами Показывать результаты итераций и Линейная модель (выбрав этот вариант можно ускорить время выполнения расчетов).
Теперь нажмите Выполнить, вернувшись в диалоговое окно Поиск решения. Окно закроется, и вы увидите процесс поиска решения (он будет идти на рабочем листе). Достигнув первого решения, Excel записывает полученные результаты на рабочий лист и выводит диалоговое окно Текущее состояние поиска решения. Для остановки процесса решения нажмите Стоп, для
 |
продолжения - Продолжить.
Рис. 5. Вид диалогового окна Текущее состояние поиска решения
 |
Когда окончательное решение будет найдено или если за заданное время решение не было найдено или было выполнено максимальное число итераций, поиск решения останавливается, а Excel выдаст наилучшие результаты для текущего решения, пересчитает данные на рабочем листе на основе этих значений и выведет окно диалога Результаты поиска решения.
Рис. 6. Вид диалогового окна Результаты поиска решения
Если поиск решения успешно закончен, в окне диалога Результаты поиска решения выводится одно из следующих сообщений:
* Решение найдено. Все ограничения и условия оптимальности выполнены.
* Все ограничения соблюдены с установленной точностью и найдено заданное значение целевой ячейки.
* Поиск свелся к текущему решению. Все ограничения выполнены.
* Значение целевой ячейки, не менялось в течение последних пяти итераций. Решение возможно найдено или итеративный процесс улучшает решение очень медленно.
Если поиск не способен достичь оптимального решения, в диалоговом окне Результаты поиска решения выводится одно из следующих сообщений:
* Поиск не может улучшить текущее решение. Все ограничения выполнены. В процессе поиска решения нельзя найти такой набор значений влияющих ячеек, который был бы лучше текущего решения. Приблизительное решение найдено, но либо дальнейшее уточнение невозможно, либо заданная погрешность слишком высока. Измените, погрешность на меньшее число и запустите процедуру поиска решения снова.
* Поиск остановлен (истекло заданное на поиск время). Время, отпущенное на решение задачи, исчерпано, но достичь удовлетворительного решения не удалось. Чтобы при следующем запуске процедуры поиска решения не повторять выполненные вычисления, установите переключатель «Сохранить найденное решение» или нажмите кнопку «Сохранить сценарий».
* Поиск остановлен (достигнуто максимальное число итераций). Произведено разрешенное число итераций, но достичь удовлетворительного решения не удалось. Увеличение числа итераций может помочь, однако следует рассмотреть результаты, чтобы понять причины остановки. Чтобы при следующем запуске процедуры поиска решения не повторять выполненные вычисления, установите переключатель «Сохранить найденное решение» или нажмите кнопку «Сохранить сценарий».
* Значения целевой ячейки не сходятся. Значение целевой ячейки неограниченно увеличивается (или уменьшается), даже если все ограничения соблюдены. Возможно, следует в задаче снять одно ограничение или сразу несколько. Изучите процесс расхождения решения, проверьте ограничения и запустите задачу снова.
* Поиск не может найти подходящего решения. В процессе поиска решения нельзя сделать итерацию, которая удовлетворяла бы всем ограничениям при заданной точности. Вероятно, ограничения противоречивы. Исследуйте лист на предмет возможных ошибок в формулах ограничений или в выборе ограничений.
* Поиск остановлен по требованию пользователя. Нажата кнопка «Стоп» в окне диалога Текущее состояние поиска решения после прерывания поиска решения или в процессе пошагового выполнения итераций.
* Условия для линейной модели не удовлетворяются. Установлен флажок «Линейная модель», однако итоговый пересчет порождает такие значения, которые не согласуются с линейной моделью. Это означает, что решение недействительно для данных формул листа. Снимите флажок «Линейная модель» и запустите задачу снова.
* При поиске решения обнаружено ошибочное значение в целевой ячейке или в ячейке ограничения. При пересчете значений ячеек обнаружена ошибка в одной формуле или в нескольких сразу. Найдите целевую ячейку или ячейку ограничения, порождающие ошибку, и измените их формулы так, чтобы они возвращали подходящее числовое значение. В поле Ограничение окна диалога Добавить ограничение набрано слово «целое». Это слово является сообщением процедуре поиска решения о том, что значение ячейки ограничения должно быть целым числом. Чтобы ограничить множество значений ячейки множеством целых чисел, выберите из раскрывающегося списка операторов сравнения в окне диалога Добавить ограничение строку «цел».
* Мало памяти для решения задачи. Система не смогла выделить память, необходимую для поиска решения. Закройте некоторые файлы или приложения, и попытайтесь снова выполнить процедуру поиска решения.
* Другой экземпляр Excel использует SOLVER. DLL. Попробуйте повторить через какое-то время. Запущено несколько копий Microsoft Excel, в одном из которых используется файл Solver. dll.
Чтобы сохранить модель( то есть значения ячеек и ограничений в какой-либо задаче ) нужно в окне Параметры поиска решения нажать кнопку Сохранить модель.
По умолчанию область сохранения модели начинается с активной ячейки, выделяет дополнительный интервал в несколько ячеек - по одной для каждого ограничения и три свободные ячейки. Нужно, чтобы все они были пустыми.
В окно Область модели введите (или выделите) интервал пустых ячеек, куда будет записываться целевая функция и все остальное.
При последующем открытии рабочего листа на экран автоматически выводятся те установки, которые были определены последними.
Для загрузки сохраненной модели в Параметрах нажмите кнопку Загрузить модель, а в поле Область модели введите интервал этих же ячеек. Поиск решения сделает запрос о том, нужно ли переустановить текущие параметры, заменив их на установки загруженной модели. Таким образом, на каждый лист рабочей книги можно поместить отдельную задачу с произвольным количеством моделей.
Индивидуальные задания: в файле «Задачи студентам. doc»
Реализация программы решения иллюстративного примера в файле «lab1.xls».
[1] На Рис. 2. Показаны не относительные адреса переменных F4 и F5, а их имена Переменная_х1 и Переменная_х2.
