Технология составления и решения моделей в MS Excel
Технология «Электронная таблица-модель-электронная таблица» («ЭТ-МОД-ЭТ») Схема технологии «ЭТ — МОД — ЭТ»
Рассматриваемая технология реализации модели заключается в выполнении следующих технологических этапов (операций):
1. формирование исходной матрицы числовой экономико-математической модели на основе исходной информации в одном или нескольких блоках электронной таблицы,
2. решение модели программным комплексом для данного класса моделей,
3. возврат результатов решения в электронную таблицу и расчет аналитических таблиц.
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
--PAGE_BREAK----PAGE_BREAK----PAGE_BREAK----PAGE_BREAK----PAGE_BREAK----PAGE_BREAK--
19
21
4
Средневзвешенная скорость потока, км/ч
5
90
70
60
60
70
60
80
90
6
Процент грузового и общественного транспорта в поток, %
7
30
30
50
50
50
30
30
40
8
Интенсивность движения в двух направлениях, авт/ч
9
900
800
500
500
500
900
900
800
3. Приступить к расчетам, для этого записать следующие формулы:
А
B
C
D
E
F
G
H
10
Коэффициент, зависящий от интервалов движения и характеристики проезжей части, А
11
=6,83+0,025+0,0375*A7
Маркером заполнения заполнить эту формулу до ячейки Н11
А
B
C
D
E
F
G
H
12
Уровень звука на расстоянии 7,5 м от оси крайней проезжей части
13
=A11*LOG(A9)+1,7*LOG(A5)+43,2
14
Превышение нормы
15
=A13-55
Для остальных значений формулы заполнить маркером заполнения соответственно до ячеек Н13 и Н15.
4. По полученным данным построить график изменения уровня шума в зависимости от времени суток:
Выделить диапазон ячеек А13: Н13;
Запустить мастер диаграмм;
В первом шаге указать тип диаграммы – график → нажать кнопку Далее;
Второй шаг остается без изменений (кнопка Далее);
В третьем шаге написать название диаграммы – «График зависимости уровня шума от времени суток»; ось Х – «Время суток»; ось Y – «Уровень шума» → нажать кнопку Далее;
В 4-м шаге указать размещение графика – на отдельном листе, название листа – График → нажать кнопку Готово.
5. Проанализировать график.
Состояние атмосферного воздуха г. Челябинска
В программе Excel постройте таблицу с данными:
А
В
С
1
Основные загрязнители г. Челябинска на 1998 г.
2
Предприятие
Выбросы, тыс.т
Выбросы, %
3
«ОАО» Мечел
41,170
4
ТЭЦ-2
12,765
5
ТЭЦ-1
7,141
6
ОАО «Челябинский электрометаллургический комбинат»
15,752
7
АО «Челябинский электролитный цинковый завод»
4,423
8
ОАО «Челябинский электродный завод»
3,311
9
Другие
11,838
10
Всего
Отформатируйте ячейки с цифрами (В3: С10) так, чтобы они содержали три десятичных знака.
С помощью автосуммирования найдите общее количество загрязняющих веществ (ячейка В10).
Ячейке В10 присвойте абсолютное имя «Всего», т.е. поставьте курсор на данную ячейку → щелкните мышью в поле адреса → напишите там «Всего» → нажмите Enter. Чтобы рассчитать процентные данные, в ячейке С3 установите процентный формат и напишите формулу: =B3/Всего. продолжение
--PAGE_BREAK--
Маркером заполнения заполните эту формулу до ячейки С9. В ячейке С10 рассчитайте сумму ячеек С3: С9.
По полученным данным (диапазон ячеек А2: А9; С2: С9) постройте круговую диаграмму. Проанализируйте результат.
Сокращение выбросов на предприятиях по сравнению с 1997 г.
ОАО «Челябинский электрометаллургический комбинат»
Мероприятие
Сокращение выбросов, т/год
Ремонт газоочистки печи
32,49
Ремонт аспирационной установки от дробилки
8,29
Эксплуатация термокаталитического реактора
342,918
Всего
«ОАО» Мечел
Мероприятие
Сокращение выбросов, т/год
Капитальный ремонт газоочистки кислородно-конвертерного цеха
49
Ремонт 3-х аспирационных систем
1,6
Капитальный ремонт аспирационной системы
2
Замена скрубберов аспирационных систем
10
Капитальный ремонт сепараторов пыли
45
Всего
Снижение выбросов загрязняющих веществ по сравнению с 1997 г.
АО «Челябинский электролитный цинковый завод»
Загрязняющее вещество
Количество, тыс. т
Сернистый ангидрид
143,743
Окислы азота
5,825
Ртуть металлическая
0,298
Всего
ОАО «Челябинский электродный завод»
Загрязняющее вещество
Количество, тыс. т
Хлор
15,895
Толуол
0,206
Эпихлоргидрин
0,014
Дибутилфталат
0,03
Ангидрид фталевый
0,006
Всего
ТЭЦ-1
Загрязняющее вещество
Количество, тыс. т
Твердые вещества
1,268
Сернистый ангидрид
2,629
Окислы азота
0,381
Всего
Увеличение выбросов загрязняющих веществ по сравнению с 1997 г. на ТЭЦ-2
Загрязняющее вещество
Количество, тыс. т
Твердые вещества
2,133
Сернистый ангидрид
2,057
Окись углерода
0,001
Всего
Автосуммированием найдите общее сокращение или увеличение выбросов на предприятиях. По полученным данным постройте круговые диаграммы для каждого из шести предприятий (не включая в диаграммы ячейки «Всего»).
Проанализируйте построенные диаграммы.