Методичка на тему Технология составления и решения моделей в MS Excel
Работа добавлена на сайт bukvasha.net: 2015-07-01Поможем написать учебную работу
Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
Технология составления и решения моделей в 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 | | |
Метафос
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.
В списке "Ограничения" отражается система основных ограничений модели. В нашем примере это группы ограничений:
По размеру экономического ущерба:
$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 порядков). Три следующих группы опций относятся к методам решения модели. Правильно задав эти опции (индивидуально в каждом случае) пользователь имеет возможность повысить сходимость задачи, сократить время решения модели и найти все (или большинство) имеющихся в задаче экстремумов (решений).
Перед тем как закрыть диалог "Параметры", щелкнув на кнопку "ОК", пользователь имеет возможность сохранить (в специально отведенном для этого поле) сценарий модели или загрузить новую модель.
Поиск решения начинается щелчком на кнопке "Выполнить". В процессе решения модели в информационной строке состояний отражается информация о ходе процесса.
После выполнения операции поиска решения появляется диалоговое окно "Результаты поиска решения".
Если все прошло успешно, в этом окне написано следующее сообщение "Решение найдено. Все ограничения и условия оптимальности выполнены".
Установите указатель в виде точки в положение "Сохранить найденное решение". В области "Тип отчета" укажите все три типа: результаты, устойчивость, пределы. После этого нажмите кнопку ОК.
Убедитесь, что в результате проделанных действий Excel заполнил незаполненные ячейки таблиц и создал три новых листа с отчетами, они называются: "Отчет по результатам 1", "Отчет по устойчивости 1" и "Отчет по пределам 1".
Просмотрите и проанализируйте созданные отчеты.
Модель оптимизации хозяйственной деятельности на примере экологической игры "Озеро"
|
| A | B | C | D | E | F | G | H | I | J |
| 1 | Исходная информация |
|
|
| ||||||
| 2 | Зона | Промышленная зона | Средняя зона | Культурная зона |
|
|
| |||
| 3 | Вещества | Наличие | ПДК | Наличие | ПДК | Наличие | ПДК |
|
|
|
| 4 | Неорганика | 500 | 350 | 450 | 300 | 400 | 230 |
|
|
|
| 5 | Органика | 100 | 60 | 100 | 40 | 100 | 25 |
|
|
|
| 6 | Кислород | 0,5 | 2,0 | 1,0 | 4,0 | 1,5 | 6,0 |
|
|
|
| 7 | Забор воды | 9950 |
| 6000 |
| 2800 |
|
|
|
|
| 8 | Уровень воды | мин | макс |
|
|
|
|
|
|
|
| 9 |
| 9,8 | 10,2 |
|
|
|
|
|
|
|
| 10 | Финансирование |
|
|
|
|
|
| |||
| 11 | Вид деятельности | Количество | Стоимость, ед. |
|
|
|
|
|
| |
| 12 |
| мин | макс |
|
|
|
|
|
|
|
| 13 | Подкачка воды, км3 | 0 | 5 | 0,5 |
|
|
|
|
|
|
| 14 | Сброс воды, км3 | 0 | 5 |
|
|
|
|
|
|
|
| 15 | Искусственная аэрация, руб за мг/л | 0 | 10 | 0,25 |
|
|
|
|
|
|
| 16 | Финансирование на 2 месяца, руб | 300 |
|
|
|
|
|
|
|
|
| 17 | Решение | |||||||||
| 18 |
| По условию | По решению | |||||||
| 19 | Зона | Промышленная зона | Средняя зона | Культурная зона | Промышленная зона | Средняя зона | Культурная зона | |||
| 20 | Вещества | мин | макс | мин | макс | мин | макс |
|
|
|
| 21 | Неорганика |
|
|
|
|
|
| =C4 | =E4 | =G4 |
| 22 | Органика |
|
|
|
|
|
| =C5 | =E5 | =G5 |
| 23 | Кислород |
|
|
|
|
|
| =C6 | =E6 | =G6 |
| 24 | Уровень воды |
|
|
|
|
|
|
|
|
|
| 25 | Финансирование |
|
|
| ||||||
| 26 | Вид деятельности | По условию | По решению |
|
|
| ||||
| 27 |
| Количество | Стоимость | Количество | Стоимость |
|
|
| ||
| 28 |
| мин | макс | мин | макс |
|
|
|
|
|
| 29 | Подкачка воды, м3 |
|
|
|
| =C13 | =F33*D13*30 |
|
|
|
| 30 | Сброс воды, м3 |
|
|
|
|
|
|
|
|
|
| 31 | Искусственная аэрация, руб за мг/л |
|
|
|
| =C15 | =F35*D15*30 |
|
|
|
| 32 | Финансирование на 2 месяца, руб | =G33+G35 |
|
|
|
|
|
|
|
|
Обработка модели "решателем" (поиск решения) на примере использования Excel
Целевая ячейка: $В$37
Изменяя ячейки: $B$33:$E$35;$B$22:$G$24;$B$26
Ограничения:
По размеру финансирования:
$В$37<$В$17
По максимальному уровню воды:
$B$26<$C$9
По минимальному уровню воды:
$B$26> $B$9
По максимальной мощности мероприятий:
$F$33:$F$35< $C$33:$C$35
По минимальной мощности мероприятий:
$F$33:$F$35> $B$33:$B$35
По максимальному размеру стоимости мероприятий:
$G$33:$G$35< $E$33:$E$35
По минимальному размеру стоимости мероприятий:
$G$33:$G$35 > $D$33:$D$35
По максимальному содержанию веществ в промышленной зоне:
$H$22:$H$24 < $C$22:$C$24
По минимальному содержанию веществ в промышленной зоне:
$H$22:$H$24 > $B$22:$B$24
По максимальному содержанию веществ в средней зоне:
$I$22:$I$24 < $E$22:$E$24
По минимальному содержанию веществ в средней зоне:
$I$22:$I$24 > $D$22:$D$24
По максимальному содержанию веществ в культурной зоне:
$J$22:$J$24 < $G$22:$G$24
По минимальному содержанию веществ в культурной зоне:
$J$22:$J$24 > $F$22:$F$24
Графический метод расчета уровней звука, LА.экв
Задание: вычислить изменение уровня звука в зависимости от времени суток, по результатам построить график.
Теоретическая часть
Суммарный уровень звука на расстоянии 7,5 м от оси крайней проезжей части магистрали LА.экв, дБа, определяется по формуле:
LА.экв=А lg N+1,7 lg v +43,2,
где А=6,83+0,025+0,0375 р – коэффициент, зависящий от интервалов движения и характеристики проезжей части; N – интенсивность движения в оба направления, авт/ч; v – средняя скорость автомобильного потока, км/ч; р– суммарный процент грузового и общественного транспорта, %.
Эта формула рекомендована ЦНИИП градостроительства, она выведена на основе независимых исследований ряда специалистов.
В этой формуле приняты некоторые допущения. Например, считают, что расстояние между экипажами S<20м, интенсивность движения составляет N<2000 авт/ч, а скорость движения v>40км/ч. При таких значениях транспортного потока его относят к линейному источнику шума. Эти допущения позволили упростить расчеты, а определенные погрешности в результатах вполне допустимы для градостроительного проектирования.
За нормативный барьер звукового комфорта для жилой застройки принята величина, равная LА.н =55дБа.
Решение
1. Создать файл "Шум" в программе Excel.
2. Внести в файл исходную информацию:
-
А
B
C
D
E
F
G
H
1
Графический метод расчета уровней звука LА.экв
2
Время суток
3
7
9
11
13
15
17
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/Всего.
Маркером заполнения заполните эту формулу до ячейки С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
Всего
Автосуммированием найдите общее сокращение или увеличение выбросов на предприятиях. По полученным данным постройте круговые диаграммы для каждого из шести предприятий (не включая в диаграммы ячейки "Всего").
Проанализируйте построенные диаграммы.
1. Реферат Мобильные персональные компьютеры виды, варианты исполнения, их сравнение
2. Реферат на тему Catcher In The Rye Essay Research Paper
3. Реферат Технологии социальной работы с неполной семьей
4. Реферат на тему Work Essay Research Paper The World of
5. Реферат на тему Larkin Philip Comments On This Be The
6. Реферат на тему Rousseau And The Artists Of The French
7. Курсовая на тему Анализ зависимости между уровня комплемента в крови больных системной красной волчанкой и степенью
8. Сочинение на тему О великой отчественной войне Подвиг человека на войне
9. Контрольная работа Контрольная работа по Бухгалтерскому делу 2
10. Реферат Квалификация налоговых преступлений
-
