Создание отчета по результатам поиска решения
МИНИСТЕРСТВО СЕЛЬСКОГО ХОЗЯЙСТВА РОССИЙСКОЙ ФЕДЕРАЦИИ
|
Кафедра статистики и
информационных систем
в экономике
ДС.04 ЭКОНОМИКО-МАТЕМАТИЧЕСКИЕ МЕТОДЫ ОБРАБОТКИ КАДАСТРОВОЙ ИНФОРМАЦИИ
МЕТОДИЧЕСКИЕ УКАЗАНИЯ
К лабораторным работам
Направление подготовки дипломированного специалиста
Землеустройство и кадастры
Специальность 120302 Земельный кадастр
Специальность 120303 Городской кадастр
Уфа 2010
УДК 378.147:004.02
ББК 74.58:32.973-018
М 54
Рекомендовано к изданию методической комиссией факультета землеустройства и лесного хозяйства (протокол №_ от «__» ___________ 2010г.)
Составитель: доцент, к.э.н. Аслаева С.Ш.
Рецензент: доцент, к.э.н. Ханова И.М.
Ответственный за выпуск: зав. кафедрой статистики и ИСЭ, доцент, к.э.н. Аблеева А.М
Лабораторная работа №1. Решение землеустроительных задач с использованием методов линейного программирования и анализ результатов
Цель: решить задачи ЗЛП с использованием пакета прикладных программ, анализ решения, построение двойственной задачи.
|
|
|
ВВЕДЕНИЕ
Алгоритмы задач принятия решений настолько сложны, что без применения компьютера реализовать их практически невозможно. Компьютер с помощью программного обеспечения реализует алгоритмы поиска оптимального решения, которые преобразуют исходные данные в результат. Таким программным обеспечением, выполняющим поиск оптимальных решений, является Excel7.0 для Windows95 (и более поздние версии Excel), а также и ППП Simplex. Поиску оптимальных решений задач линейного программирования с помощью Excel7.0 и посвящено методическое указание.
Образец решения задачи
Рассмотрим простейший пример решения задач в Excel.
Условие задачи: В хозяйстве имеется 200 га неиспользуемых земель, пригодных для освоения под пашню и сенокос. Затраты труда на освоение 1 га земель под пашню составляют 37 чел.-ч., в сенокос 1 чел.-ч. Для вовлечения земель в сельскохозяйственный оборот предприятие может затратить не более 1200 чел.-ч. механизированного труда. Стоимость продукции, получаемой с 1 га пашни, составляет 16000 руб., с 1 га сенокосов -2000 руб. В задание на проектирование установлено, что площадь земель осваиваемых под пашню не должна превышать 50 % площади сенокосов. Требуется определить, какую площадь нужно освоить под пашню и сенокосы, чтобы получить максимальное количество продукции в стоимостном выражении.
|
|
|
4.3.1 Построим математическую модель задачи
Введем переменные
Х1 – площадь земель трансформируемая в пашню, га,
Х2 – площадь земель трансформируемая в сенокосы, га.
Запишем ограничения
1) По площади неиспользуемых земель, пригодных для освоения под пашню и сенокосы, га
Х1 + Х2 ≤ 200
2) По затратам труда, чел – ч.
37Х1 +Х2 ≤ 1200
3) По соотношению площадей земель осваиваемых под пашню и под сенокосы, га
Х1 ≤ 0,5Х2
Наложим условие неотрицательности на переменные
Х1≥ 0, Х2≥0.
Запишем целевую функцию (критерий оптимальности – максимальный выход продукции, рублей)
Z= 16000Х1 +2000Х2 → max
Сформулируем математическую задачу: найти такие значения переменных Х1 и Х2 , чтоб выполнялись ограничения задачи и достигалось максимальное значение целевой функции Z.
4.3.2 Построим матрицу модели
Таблица 4.1 Матрица модели
| Ограничения | Площадь под пашню, га, Х1 | Площадь под сенокосы, га, Х2 | Тип ограничения | Объем ограничения |
| 1. 1. Общая площадь, га 2.Трудовые ресурсы, чел.-ч 2. 3.Соотношение площадей, га Цф (max выход продукции) | 1 37 1 1600 | 1 1 -0,5 2000 | <= <= <= => | 200 1200 0 max |
|
|
|
Сформулируем экономическую задачу: найти площадь земли, трансформируемую под пашню и площадь земли, трансформируемую в сенокосы, чтобы уложиться в выделенные ресурсы земли и труда, а также выполнить задание на проектирование по соотношению площадей земель осваиваемых под пашню и под сенокосы. При этом получить максимальное количество продукции в стоимостном выражении.
3 Решим задачу в Excel
Excel - это программа обработки электронных таблиц, которая предоставляет огромные возможности по различным направлениям.
Поиск решения - это надстройка Excel, которая позволяет решать оптимизационные задачи.
Примечания: 1) Если в меню Сервис отсутствует команда Поиск решения, значит, необходимо загрузить эту надстройку. Для этого выберите команду Сервиса Надстройки и активизируйте надстройку Поиск решения.
2) Если же этой надстройки нет в диалоговом окне Надстройки, то необходимо обратиться к панели управления Windows, щелкнуть по пиктограмме Установка и удаление программ и с помощью программы установки Excel (или Office) установить надстройку Поиск решения.
|
|
|
4.3.3 Последовательность решения задачи
1) Создать форму для ввода условий задачи.
2) Ввести исходные данные.
3) Ввести зависимость для целевой функции.
4) Ввести зависимости для ограничений.
1) Создаем форму для ввода условий задачи, т.е. распределяем ячейки для записи модели. Форма состоит из двух частей. В первой будут находиться: название таблицы, служебные слова, названия переменных, значения переменных, коэффициенты при переменных в целевой функции, направление и значение целевой функции.
Во второй части будут находиться: название таблицы, служебные слова, названия ограничений, коэффициенты при переменных в ограничениях, значения ограничений, тип ограничений, объемы ограничений.
2) Введем исходные данные в созданную форму.
Получим результат, который представлен на рисунке 3.
Рисунок 3 Форма с введенными исходными данными
3. Введем зависимость для целевой функции:
• Курсор подводим к ячейке, в которой будет находиться значение целевой функции D4; М1 (Обозначим через М1 следующее действие - «один щелчок левой кнопкой мыши»).
• Курсор на кнопку Мастер функции; М1.
• На экране появится диалоговое окно Мастер функций шаг 1 из 2.
• Курсор в окно Категория на категорию Математические; Ml.
• Курсор в окно Функции на СУММПРОИЗВ; Ml.
 |
Появится диалоговое окно СУММПРОИЗВ, котороепредставлено на рисунке 4.
• В массив 1 диалогового окна СУММПРОИЗВ вводим адреса ячеек В$3:C$3, в которых находятся значения переменных . (Адреса ячеек во все диалоговые окна удобно вводить не с клавиатуры, а протаскивая
Рисунок 4 Диалоговое окно СУММПРОИЗВ
мышь по ячейкам, чьи адреса следует ввести).
• В массив 2 вводим адреса ячеек В4:C4, в которых находятся коэффициенты целевой функции В4:C4.
• Готово. На экране в D4 введена формула для вычисления целевой функции.
4. Введем зависимость для левых частей ограничений:
• Курсор в D4; M1; Копировать в буфер.
• В ячейку D7 вводим левую часть ограничения Х1+Х 2 формулой СУММПРОИЗВ(В3:C3;B7:C7) для этого подводим курсор в ячейку D7; M1; Вставить из буфера.. Аналогично вводим зависимость в ячейки D8, D9. На этом ввод зависимостей закончен.
В результате значения в ячейках D4, D7, D8, D9 равны нулю. На рисунке 5 представлена форма с введенными формулами в данные ячейки.
| Переменные | ||||||
| Имя | x1 | x2 |
| |||
| Значение | ЦФ | напр | ||||
| коэфф в ЦФ | 16000 | 2000 | =СУММПРОИЗВ(B$3:C$3;B4:C4) | макс | ||
| Ограничения | ||||||
| Вид | левая часть | знак | правая часть | |||
| Общая площадь, га | 1 | 1 | =СУММПРОИЗВ(B$3:C$3;B7:C7) | <= | 200 | |
| Трудовые рес, чел.-ч | 37 | 1 | =СУММПРОИЗВ(B$3:C$3;B8:C8) | <= | 1200 | |
| Соотношение пл., га | 1 | -0,5 | =СУММПРОИЗВ(B$3:C$3;B9:C9) | <= | 0 | |
Рисунок 5 Форма с формулами, определяющими зависимости целевой функции и зависимости для левых частей ограничения.
4.3.3 Запуск на решение задачи
1) Запустить Поиск решения.
2) Указать назначение целевой функции (установить целевую ячейку).
3) Установить изменяемые ячейки.
4) Ввести ограничения.
5) Ввести параметры для решения задачи линейного программирования (ЗЛП).
1) Запустим Поиск решения.
- в главном меню выбрать команду Сервис,
- из раскрывшего меню выбрать команду Поиск решения,
- появится диалоговое окно Поиск решения.
2) Установим целевую ячейку.
Примечание: во всех задачах для средства Поиск решения оптимизируется результат в одной из ячеек рабочего листа. Целевая ячейка связана с другими ячейками этого рабочего листа с помощью формул. Средство Поиск решения использует формулы, которые дают результат в целевой ячейке, для проверки возможных решений. Можно выбрать поиск наименьшего или наибольшего значения для целевой ячейки или же установить конкретное значение.
• Курсор в поле «Установить целевую ячейку».
• Ввести адрес ячейки $D$4, в котором будет находиться значение целевой функции.
• Ввести направление целевой функции: максимальное значение.
3) Ввести адреса искомых переменных.
Примечание: второй важный параметр средства Поиск решения - это параметр Изменяя ячейки. Изменяемые ячейки - это те ячейки, значения в которых будут изменяться для того, чтобы оптимизировать результат в целевой ячейке. К изменяемым ячейкам предъявляется два основных требования: они не должны содержать формул, и изменение их значений должно отражаться на изменении результата в целевой ячейке.
• Курсор в поле «Изменяя ячейки».
• Ввести адреса ячеек В$3:С$3, в котором будут находиться значения переменных..
В результате данных действий диалоговое окно Поиск решения примет вид, представленный на рисунке 5.
Рисунок 5 Диалоговое окно Поиск решения
4) Введем ограничения.
• Курсор в поле «Добавить». Появится диалоговое окно Добавление ограничения.
• В поле «Ссылка на ячейку» ввести адрес $D$7.
• Ввести знак ограничения <=.
• Курсор в правое окно.
• Ввести адрес $F$7 (рисунок 6).
• Добавить. На экране опять диалоговое окно Добавление ограничения.
• Ввести остальные ограничения
• После ввода последнего ограничения ввести ОК. На экране появится диалоговое окно Поиск решения с введенными условиями (рисунок 7).
 |
Рисунок 6 Диалоговое окно Добавление ограничений
5) Ввод параметров для решения ЗЛП.
• Открыть окно Параметры поиска решения.
• Установить флажок Линейная модель, что обеспечивает применение симплекс-метода.
• Установить флажок Неотрицательные значения. После чего нажимаем на клавишу ОК.
• Нажимаем на клавишу Выполнить в диалоговом окне Поиск решения.
Рисунок 7 Решение найдено
Получено оптимальное решение (рисунок 6). То есть, определена площадь неиспользуемых земель, трансформируемая в пашню 27,7778 га, в сенокосы 172,2222 га. Максимальное количество продукции в стоимостном выражении составит 788888,89 рубля. Вся площадь неиспользуемых земель вовлечена в сельскохозяйственный оборот, трудовые ресурсы используются полностью, задание на проектирование выполнено: площадь неиспользуемой земли, трансформируемая в пашню меньше половины площади трансформируемой в сенокосы на 58,3333 га.
Создание отчета по результатам поиска решения
Excel позволяет представить результаты поиска решения в форме отчета. Существует три типа таких отчетов:
Результаты.В отчет включаются исходные и конечные значения целевой и влияющих ячеек, дополнительные сведения об ограничениях.
Устойчивость. Отчет, содержащий сведения о чувствительности решения к малым изменениям в изменяемых ячейках или формулах ограничений.
Пределы. Помимо исходных и конечных значений изменяемых и целевой ячеек в отчет включаются верхние и нижние границы значений, которые могут принимать влияющие ячейки при соблюдении ограничений.
Для этого в появившемся диалоговом окне Результаты поиска решения выбираем тип отчета и нажимаем на клавишу ОК.
При выборе типа отчета «Результаты» появится лист Отчет по результатам1, который представлен на рисунке 8.
|
Microsoft Excel 10.0 Отчет по результатам | ||||||
| Рабочий лист: [ЭММ.xls]Лист1 | ||||||
| Целевая ячейка (Максимум) | ||||||
| Ячейка | Имя | Исходное значение | Результат | |||
| $D$4 | ЦФ | 0 | 788888,889 | |||
| Изменяемые ячейки | ||||||
| Ячейка | Имя | Исходное значение | Результат | |||
| $B$3 | значение X1, S под пашню, га | 0 | 27,7777778 | |||
| $C$3 | значение X2, S под сенокосы, га | 0 | 172,222222 | |||
| Ограничения | ||||||
| Ячейка | Имя | Значение | Формула | Статус | Разница | |
| $D$7 | общая площадь, га левая часть | 200 | $D$7<=$F$7 | связанное | 0 | |
| $D$8 | трудовые рес., чел.-ч. левая часть | 1200 | $D$8<=$F$8 | связанное | 0 | |
| $D$9 | соотношение пл., га левая часть | -58,33333333 | $D$9<=$F$9 | не связан. | 58,333 | |
Рисунок 8 Лист «Отчет по результатам 1»
Отчет по результатам состоит из 3 таблиц:
Таблица 1 приводит сведения о целевой функции: адрес ячейки, в которой находится значение целевой функции, имя, значение – 788888,89 рубля. В столбце Исходное значение приведены значения целевой функции до начала вычислений 0 .
Таблица 2 приводит сведения об искомых переменных Х1 и Х2: адрес ячеек, в которых находятся значения переменных, названия, значения полученные в результате решения задачи, которые соответственно равны 27,7778 и 172,2222. В столбце Исходное значение приведены значения переменных до начала вычислений 0 .
Таблица 3 показывает результаты оптимального решения для ограничений. Для ограничений в графе Ячейка приведены адреса ячеек, в которых находятся, значение левой части ограничения, в графе Имя названия ограничений, в графе Формула приведены зависимости, которые были введены в диалоговое окно Поиск решения; в графе Значение приведены величины использованного ресурса: общей площади 200 га, трудовых ресурсов 1200 чел.-ч., разница между площадью земель осваиваемых под пашню и 50% земель осваиваемых под сенокосы - 58,3333 га; в графе Разница показано количество неиспользованного ресурса: площадь земли 0 га, трудовых ресурсов 0 чел.- ч., площадь, трансформируемая под пашню меньше 50% площади, трансформируемой в сенокосы на 58,3333 га. Если ресурс используется полностью, то в графе Статус указывается связанное; при неполном использование ресурса в этой графе указывается, не связан.
При выборе типа отчета «Устойчивость» появится лист «Отчет по устойчивости 1», который представлен на рисунке 11.
Отчет по устойчивости состоит из 2 таблиц.
В таблице 1 приведены следующие значения для переменных:
- результат решения задачи: площадь земли, трансформируемой под пашню 27,7778 га и под сенокосы 172,2222 га;
- нормируемая стоимость, т.е. разница между правыми и левыми частями ограничений двойственной задачи, равная 0. Площадь трансформируемых земель в пашню и сенокосы эффективны, выгодны с точки зрения принятого критерия оптимальности.
Примечание: если стоимость ресурсов, затраченных на производство одного изделия, больше его цены, то это изделие не войдет в оптимальный план из-за его убыточности;
- коэффициенты целевой функции при переменной Х1 16000 и при переменной Х2 2000;
- чувствительность решения к изменению коэффициентов целевой функции исходной задачи. В графе Допустимое увеличение содержится информация о допустимом увеличении коэффициентов
целевой функции, при которых не меняется оптимальный план исходной задачи. Если увеличить коэффициенты целевой функции при переменной Х1 не более, чем на 58000 и при переменной Х2 не более чем на 14000 оптимальный план не изменится. В графе Допустимое уменьшение содержится информация о допустимом уменьшении коэффициентов целевой функции, при которых не меняется оптимальный план исходной задачи. Если уменьшить коэффициенты целевой функции при переменной Х1 не более чем на 14000 и при переменной Х2 не более чем на 1567,5675 оптимальный план не изменится.
В таблице 2 приводятся аналогичные значения для ограничений:
- величина использованных ресурсов;
-теневая цена, т.е. двойственные оценки, которые показывают, как изменится целевая функция при изменении ресурсов на единицу. Ресурсы земля и труд имеют отличные от нуля оценки 1611,1111 и 308,8889 – эти ресурсы полностью используются в оптимальном плане, являются дефицитными сдерживающими рост целевой функции. Нулевая оценка ресурса свидетельствует о его недефицитности, он не препятствует и дальше максимизировать целевую функцию. Соотношение между площадью земель трансформируемых в пашню и в сенокосы имеет нулевую оценку.
- чувствительность решения к изменению запасов сырья, т.е. значения приращения ресурсов, при которых сохраняется оптимальный набор переменных, входящих в оптимальное решение. После увеличения площади земли не более чем на 1000 га и уменьшения не более чем на 107,6923 га , уменьшения трудовых ресурсов не более чем на 1000 и увеличения не более чем на 1400 га, уменьшения соотношения площади пашни и сенокосов на 58,3333 га и увелечения на 1Е+30 га структура оптимального плана не изменится.
| Microsoft Excel 10.0 Отчет по устойчивости | |||||||
| [ЭММ.xls]Лист1 | |||||||
| Изменяемые ячейки | |||||||
| Результ. | Нормир. | Целевой | Допустимое | Допустимое | |||
| Ячейка | Имя | значение | стоимость | Коэффициент | Увеличение | Уменьшение | |
| $B$3 | Значение Х1, S под пашню, га | 27,777 | 0 | 16000 | 58000 | 14000 | |
| $C$3 | Значение Х2,S под сенокосы, га | 172,22 | 0 | 2000 | 14000 | 1567,5675 | |
| Ограничения | Результ. | Теневая | Ограничение | Допустимое | Допустимое | ||
| Ячейка | Имя | значение | Цена | Правая часть | Увеличение | Уменьшение | |
| $D$7 | Общая площадь левая часть | 200 | 1611,1 | 200 | 1000 | 107,69230 | |
| $D$8 | Трудовые рес. Левая часть | 1200 | 388,88 | 1200 | 1400 | 1000 | |
| $D$9 | Соотношение пл. левая часть | -58,333 | 0 | 0 | 1E+30 | 58,333333 | |
Рисунок 8 Лист «Отчет по устойчивости 1»
При выборе типа отчета «Пределы» появится лист «Отчет по пределам1» , который представлен на рисунке 9. В нем показано, в каких пределах может изменяться стоимость продукции, вошедшей в оптимальное решение, при сохранении структуры оптимального решения:
- приводятся значения Х1 и Х2 в оптимальном решении;
- приводятся нижние пределы изменения значений Х1 и Х2. Кроме этого, в отчете указаны значения целевой функции при трансформации земли на нижнем пределе: если неосвоенную землю не трансформировать в пашню, то стоимость продукции равна 344444,44 руб., если по нижнему пределу трансформировать в сенокосы 55,5556 га земли, то стоимость продукции составит 555555,58 руб. Далее приводятся верхние пределы изменения Х1 и Х2 и значения целевой функции при выпуске продукции, вошедший в оптимальное решение на верхних пределах: при трансформации в пашню 27,7778 га земли и 172,2222 га в сенокосы стоимость продукции составит 788888,87 рублей.
| Microsoft Excel 10.0 Отчет по пределам | ||||||||
| Целевое | ||||||||
| Ячейка | Имя | Значение | ||||||
| $D$4 | коэфф в ЦФ ЦФ | 788888,89 | ||||||
| Изменяемое | Нижний | Целевой | Верхний | Целевой | ||||
| Ячейка | Имя | Значение | предел | Результат | предел | результат | ||
| $B$3 | значение x1 | 27,777777 | 0 | 344444 | 27,777 | 788888 | ||
| $C$3 | значение x2 | 172,22222 | 55,5556 | 555555, | 172,22 | 788888 | ||
Рисунок 9 Лист «Отчет по пределам 1»
Дата добавления: 2018-04-04; просмотров: 1814; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!