Учебное пособие: Технология составления и решения моделей в 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 , не более | 0 | 6,000 | |
| 40. | Атразин | 0 | 0,005 | |
| 41. | Метафос | 0 | 0,020 | |
| 42. | Цинеб | 0 | 0,030 | |
| 43. | Интенсивность предприятия | 0 | 150,000 | |
| 44. | Интенсивность фермы: свиньи | 0 | 2000,000 | |
| 45. | Интенсивность фермы: коровы | 0 | 1000,000 | |
| 46. | Всего | =СУММ(B38:B45) | =СУММ(C38:C45) | |
| 47. | Прибыль, руб | Эконом. ущерб, руб | ||
| 48. | 5000000 | 0 | ||
| Решение | ||||
| 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 порядков). Три следующих группы опций относятся к методам решения модели. Правильно задав эти опции (индивидуально в каждом случае) пользователь имеет возможность повысить сходимость задачи, сократить время решения модели и найти все (или большинство) имеющихся в задаче экстремумов (решений).
Перед тем как закрыть диалог "Параметры", щелкнув на кнопку "ОК", пользователь имеет возможность сохранить (в специально отведенном для этого поле) сценарий модели или загрузить новую модель.
Поиск решения начинается щелчком на кнопке "Выполнить". В процессе решения модели в информационной строке состояний отражается информация о ходе процесса.
После выполнения операции поиска решения появляется диалоговое окно "Результаты поиска решения".
--> ЧИТАТЬ ПОЛНОСТЬЮ <--