Реферат: Технология составления и решения моделей в MS Excel
Технологиясоставления и решения моделей в MS ExcelТехнология «Электроннаятаблица-модель-электронная таблица» («ЭТ-МОД-ЭТ») Схематехнологии «ЭТ — МОД — ЭТ»
Рассматриваемая технология реализации модели заключается ввыполнении следующих технологических этапов (операций):
1. формирование исходной матрицы числовой экономико-математическоймодели на основе исходной информации в одном или нескольких блоках электроннойтаблицы,
2. решение модели программным комплексом для данного классамоделей,
3. возврат результатов решения в электронную таблицу и расчетаналитических таблиц.
1.Технология «Формирование и решение модели в электронной таблице»(«МОД в ЭТ»)Классификацияэлементов системы переменных и ограничений моделиВажнейшее методическое положение при реализации современнойтехнологии формирования и решения моделей является концептуальнаядифференциация переменных ограничений модели. Поэтому перед началом работынеобходимо составить классификацию элементов системы -переменных и ограничениймодели по следующей схеме:
• Основныепеременные модели
• Основныеограничения модели
• Формирующиеограничения модели
/>/>/>Схема реализации технологии «МОД в ЭТ»Реализация технологии «МОД в ЭТ' может происходить в рамкаходного из современных пакетов электронных таблиц и содержит следующиетехнологические этапы (стадии):
а) формирование вспомогательных и дополнительных взаимосвязей системыпеременных и ограничений модели в логически и информационно взаимосвязаннойсистеме исходных, вспомогательных, дополнительных и заключительных аналитическихтаблиц,
б) обработка модели „решателем“ (поиск решения)электронной таблицы с указанием ячейки целевой функции и системы основныхпеременных и ограничений модели.
Пример реализации технологии „МОД в ЭТ“Модель оптимизации хозяйственнойдеятельности на примере экологической игры „Малая река“ А B C D 1. Исходная информация 2. Прибыль 3. Вид деятельности Прибыль, руб. 4. Предприятие 12 5. Свиньи 100 Ферма 6. Коровы 200 7. Пшеница 30 с/х культуры 8. Ячмень 30 9. Рожь 28 10. Кукуруза 12 11. Картофель 10 12. Всего =СУММ(B4:B11) 13. Затраты 14. Вид деятельности Затраты, руб. 15. Лесополоса, 10м 1000 Природоохранные мероприятия 16. Вспашка уплотненная 1000 17. с микролиманами 1900 18. безотвальная 1700 19. глубиной 22-25 см 2500 20. глубиной 35-37 см 3000 21. Очистка сточных вод: механич. 0,05 22. биологическая 0,38 23. биол. с доочисткой 2 24. Аэрация 366 25. Метафос 434 Ядохимикаты, удобрения 26. Атразин 600 27. Цинеб 600 28. Азотные удобрения 400 29. Калийные 400 30. Фосфорные 400 31. Органические 2000 32. Известкование 2000 33. Всего =СУММ(B15:B32) 34. Элементы системы 35. Элементы Допустимые границы 36. мин макс 37. Вещества 38. Кислород, не менее 4 10,000 39.БПК5, не более
6,000 40. Атразин 0,005 41. Метафос 0,020 42. Цинеб 0,030 43. Интенсивность предприятия 150,000 44. Интенсивность фермы: свиньи 2000,000 45. Интенсивность фермы: коровы 1000,000 46. Всего =СУММ(B38:B45) =СУММ(C38:C45) 47. Прибыль, руб Эконом. ущерб, руб 48. 5000000 Решение 49. Элементы системы Кол-во единиц Стоимость, руб 50. Лесополоса, 10м =B53*B15 Природоохранные мероприятия 51. Вспашка уплотненная =B54*B16 52. с микролиманами =B55*B17 53. безотвальная =B56*B18 54. глубиной 22-25 см =B57*B19 55. глубиной 35-37 см =B58*B20 56. Очистка сточных вод: механич. =B59*B21 57. биологическая =B60*B22 58. биол. с доочисткой =B61*B23 59. Аэрация =B62*B24 60. Метафос =B63*B25 Ядохимикаты, удобрения 61. Атразин =B64*B26 62. Цинеб =B65*B27 63. Азотные удобрения =B66*B28 64. Калийные =B67*B29 65. Фосфорные =B68*B30 66. Органические =B69*B31 67. Известкование =B70*B32 68. Всего =СУММ(B53:B70) =СУММ(C53:C70) 69. Предприятие =B72*B4 70. Свиньи =B73*B5 Ферма 71. Коровы =B74*B6 72. Пшеница =B75*B7 с/х культуры 73. Ячмень =B76*B8 74. Рожь =B77*B9 75. Кукуруза =B78*B10 76. Картофель =B79*B11 77. Всего =СУММ(B72:B79) =СУММ(C72:C79) 78. Прибыль, руб Эконом. ущерб, руб 79. =СУММ(C72:C79) =СУММ(C53:C70)-B82 80. Содержание загрязняющих веществ 81. Элементы По условию По решению 82. мин макс 83. Вещества 84. Кислород, не менее =C38 85.БПК5, не более
=C39 86. Атразин =C40 87. Метафос =C41 88. Цинеб =C42 89. Интенсивность предприятия =C43 90. Интенсивность фермы: свиньи =C44 91. Интенсивность фермы: коровы =C45 92. Всего =СУММ(B88:B95) =СУММ(C88:C95) =СУММ(D88:D95) 2.Обработка модели „решателем“ (поиск решения) на примере использованияExcelЗапись целевой функции, система основных переменных и ограничениймодели в векторной форме производится в команде меню „Сервис-Поискрешений“. При этом открывается диалог „Поиск решений“.
В поле „Установить целевую ячейку“ указываетсяадрес ячейки, в которой записана формула показателя критерия оптимальности — целевой функции модели. В нашем примере это ячейка $C$82 (Величина экономическогоущерба). С помощью опций в левой части диалога задается направление нахожденияэкстремума задачи (максимизация или минимизация) или значение целевой функции.
В поле „Изменяя ячейки“ задается система основныхпеременных модели. Это адреса ячеек, значения которых будут варьироваться впроцессе решения задачи. В нашем примере это совокупность ячеек $B$88:$B$95; $C$88:$C$95;$B$53:$B$70; $B$72:$B$79 (Количество отдельных элементов системы). Система переменныхмодели задается несколькими массивами, они указываются с разделителем (;) иливыделяются при помощи мыши с удерживанием клавиши Ctrl.
В списке „Ограничения“ отражается системаосновных ограничений модели. В нашем примере это группы ограничений:
По размеру экономического ущерба:
$B$82 > $B$49
По размеру прибыли:
$C$82 < $C$49
По максимальным допустимым границам загрязняющих веществ:
$D$88:$D$95 < $C$88:$C$95
По минимальным допустимым границам загрязняющих веществ:
$D$88:$D$95 > $B$88:$B$95
В большинстве случаев необходимо задавать условия по неотрицательностипеременных модели. В нашем примере отражение этих условий не носитобязательного характера.
Для отражения новых ограничений модели (или при начальномформировании модели) необходимо воспользоваться опцией „Добавить“.Для корректировки ограничений служит опция „Изменить“, а для удаления- „Удалить“. Диалоги рассмотренных опций просты и не должны вызватьзатруднений.
Кнопка „Параметры“ открывает диалог, в которомпользователь может указать максимальное (контрольное) время решения модели,максимальное (контрольное) количество итераций решения задачи, точность решения(от 0 до 1) и допустимое отклонение (в %). Далее следуют три опции»Линейная модель", «Показывать результаты итераций» и«Автоматическое масштабирование», которые включаются, если взаимосвязив задаче носят исключительно линейный характер, если необходимо отслеживатьрешение модели на каждой итерации и если разброс в значенияхтехнико-экономических коэффициентов модели значителен (свыше 5 порядков). Триследующих группы опций относятся к методам решения модели. Правильно задав этиопции (индивидуально в каждом случае) пользователь имеет возможность повыситьсходимость задачи, сократить время решения модели и найти все (или большинство)имеющихся в задаче экстремумов (решений).
Перед тем как закрыть диалог «Параметры», щелкнув накнопку «ОК», пользователь имеет возможность сохранить (в специальноотведенном для этого поле) сценарий модели или загрузить новую модель.
Поиск решения начинается щелчком на кнопке «Выполнить».В процессе решения модели в информационной строке состояний отражаетсяинформация о ходе процесса.
После выполнения операции поиска решения появляется диалоговоеокно «Результатыпоиска решения».
Если все прошло успешно, в этом окне написано следующее сообщение «Решение найдено. Все ограничения и условия оптимальности выполнены».
Установитеуказатель в виде точки в положение «Сохранитьнайденное решение». В области «Тип отчета»укажите все три типа: результаты, устойчивость, пределы. После этого нажмитекнопку ОК.
Убедитесь,что в результате проделанных действий Excel заполнил незаполненные ячейки таблиц и создал три новых листа сотчетами, они называются: «Отчет порезультатам 1», «Отчетпо устойчивости 1» и «Отчет по пределам 1».
Просмотритеи проанализируйте созданные отчеты.