Технология составления и решения моделей в MS Excel
Методическое пособие - Компьютеры, программирование
Другие методички по предмету Компьютеры, программирование
Технология составления и решения моделей в MS Excel
Технология "Электронная таблица-модель-электронная таблица" ("ЭТ-МОД-ЭТ") Схема технологии "ЭТ МОД ЭТ"
Рассматриваемая технология реализации модели заключается в выполнении следующих технологических этапов (операций):
1. формирование исходной матрицы числовой экономико-математической модели на основе исходной информации в одном или нескольких блоках электронной таблицы,
2. решение модели программным комплексом для данного класса моделей,
3. возврат результатов решения в электронную таблицу и расчет аналитических таблиц.
1. Технология "Формирование и решение модели в электронной таблице" ("МОД в ЭТ")
Классификация элементов системы переменных и ограничений модели
Важнейшее методическое положение при реализации современной технологии формирования и решения моделей является концептуальная дифференциация переменных ограничений модели. Поэтому перед началом работы необходимо составить классификацию элементов системы -переменных и ограничений модели по следующей схеме:
- Основные переменные модели
- Основные ограничения модели
- Формирующие ограничения модели
Схема реализации технологии "МОД в ЭТ"
Реализация технологии "МОД в ЭТ может происходить в рамках одного из современных пакетов электронных таблиц и содержит следующие технологические этапы (стадии):
а)формирование вспомогательных и дополнительных взаимосвязей системы переменных и ограничений модели в логически и информационно взаимосвязанной системе исходных, вспомогательных, дополнительных и заключительных аналитических таблиц,
б)обработка модели "решателем" (поиск решения) электронной таблицы с указанием ячейки целевой функции и системы основных переменных и ограничений модели.
Пример реализации технологии "МОД в ЭТ"
Модель оптимизации хозяйственной деятельности на примере экологической игры "Малая река"
АBCD
- Исходная информация
- Прибыль
- Вид деятельностиПрибыль, руб.
- Предприятие12
- Свиньи100Ферма
- Коровы200
- Пшеница30с/х культуры
- Ячмень30
- Рожь28
- Кукуруза12
- Картофель10
- Всего=СУММ(B4:B11)
- Затраты
- Вид деятельностиЗатраты, руб.
- Лесополоса, 10м1000Природоохранные мероприятия
- Вспашка уплотненная1000
- с микролиманами1900
- безотвальная1700
- глубиной 22-25 см2500
- глубиной 35-37 см3000
- Очистка сточных вод: механич.0,05
- биологическая0,38
- биол. с доочисткой2
- Аэрация366
- Метафос434Ядохимикаты, удобрения
- Атразин600
- Цинеб600
- Азотные удобрения400
- Калийные400
- Фосфорные400
- Органические2000
- Известкование2000
- Всего=СУММ(B15:B32)
- Элементы системы
- ЭлементыДопустимые границы
- минмакс
- Вещества
- Кислород, не менее410,000
- БПК5, не более06,000
- Атразин00,005
- Метафос00,020
- Цинеб00,030
- Интенсивность предприятия0150,000
- Интенсивность фермы: свиньи02000,000
- Интенсивность фермы: коровы01000,000
- Всего=СУММ(B38:B45)=СУММ(C38:C45)
- Прибыль, рубЭконом. ущерб, руб
- 50000000Решение
- Элементы системыКол-во единицСтоимость, руб
- Лесополоса, 10м=B53*B15Природоохранные мероприятия
- Вспашка уплотненная=B54*B16
- с микролиманами=B55*B17
- безотвальная=B56*B18
- глубиной 22-25 см=B57*B19
- глубиной 35-37 см=B58*B20
- Очистка сточных вод: механич.=B59*B21
- биологическая=B60*B22
- биол. с доочисткой=B61*B23
- Аэрация=B62*B24
- Метафос=B63*B25Ядохимикаты, удобрения
- Атразин=B64*B26
- Цинеб=B65*B27
- Азотные удобрения=B66*B28
- Калийные=B67*B29
- Фосфорные=B68*B30
- Органические=B69*B31
- Известкование=B70*B32
- Всего=СУММ(B53:B70)=СУММ(C53:C70)
- Предприятие=B72*B4
- Свиньи=B73*B5Ферма
- Коровы=B74*B6
- Пшеница=B75*B7с/х культуры
- Ячмень=B76*B8
- Рожь=B77*B9
- Кукуруза=B78*B10
- Картофель=B79*B11
- Всего=СУММ(B72:B79)=СУММ(C72:C79)
- Прибыль, рубЭконом. ущерб, руб
- =СУММ(C72:C79)=СУММ(C53:C70)-B82
- Содержание загрязняющих веществ
- ЭлементыПо условиюПо решению
- минмакс
- Вещества
- Кислород, не менее=C38
- БПК5, не более=C39
- Атразин=C40
- Метафос=C41
- Цинеб=C42
- Интенсивность предприятия=C43
- Интенсивность фермы: свиньи=C44
- Интенсивность фермы: коровы=C45
- Всего=СУММ(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 &l