Технология составления и решения моделей в MS Excel

2. Технология "Электронная таблица-модель-электронная таблица" ("ЭТ-МОД-ЭТ") Схема технологии "ЭТ — МОД — ЭТ"

Рассматриваемая технология реализации модели заключается в выполнении следующих технологических этапов (операций):

1. формирование исходной матрицы числовой экономико-математической модели на основе исходной информации в одном или нескольких блоках электронной таблицы,

2. решение модели программным комплексом для данного класса моделей,

3. возврат результатов решения в электронную таблицу и расчет аналитических таблиц.

1. Технология "Формирование и решение модели в электронной таблице" ("МОД в ЭТ")

Классификация элементов системы переменных и ограничений модели

Важнейшее методическое положение при реализации современной технологии формирования и решения моделей является концептуальная дифференциация переменных ограничений модели. Поэтому перед началом работы необходимо составить классификацию элементов системы -переменных и ограничений модели по следующей схеме:

• Основные переменные модели

• Основные ограничения модели

• Формирующие ограничения модели

С хема реализации технологии "МОД в ЭТ"

Реализация технологии "МОД в ЭТ' может происходить в рамках одного из современных пакетов электронных таблиц и содержит следующие технологические этапы (стадии):

а) формирование вспомогательных и дополнительных взаимосвязей системы переменных и ограничений модели в логически и информационно взаимосвязанной системе исходных, вспомогательных, дополнительных и заключительных аналитических таблиц,

б) обработка модели "решателем" (поиск решения) электронной таблицы с указанием ячейки целевой функции и системы основных переменных и ограничений модели.

Пример реализации технологии "МОД в ЭТ"

1. Модель оптимизации хозяйственной деятельности на примере экологической игры "Малая река"

	А	B	C		D
	Исходная информация
	Прибыль
	Вид деятельности	Прибыль, руб.
	Предприятие	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)
	Элементы системы
	Элементы	Допустимые границы
		мин	макс
	Вещества
	Кислород, не менее	4	10,000
	БПК5, не более	0	6,000
	Атразин	0	0,005
	Метафос	0	0,020
	Цинеб	0	0,030
	Интенсивность предприятия	0	150,000
	Интенсивность фермы: свиньи	0	2000,000
	Интенсивность фермы: коровы	0	1000,000
	Всего	=СУММ(B38:B45)	=СУММ(C38:C45)
		Прибыль, руб	Эконом. ущерб, руб
		5000000	0
	Решение
	Элементы системы	Кол-во единиц	Стоимость, руб
	Лесополоса, 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.