Учебное пособие: Технология составления и решения моделей в MS Excel
Учебное пособие: Технология составления и решения моделей в 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, не более |
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) |