49267 (Технология составления и решения моделей в MS Excel), страница 2

В списке "Ограничения" отражается система основных ограничений модели. В нашем примере это группы ограничений:

По размеру экономического ущерба:

$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".

Просмотрите и проанализируйте созданные отчеты.

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

Обработка модели "решателем" (поиск решения) на примере использования 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

1. Графический метод расчета уровней звука, L_А.экв

Задание: вычислить изменение уровня звука в зависимости от времени суток, по результатам построить график.

Теоретическая часть

Суммарный уровень звука на расстоянии 7,5 м от оси крайней проезжей части магистрали L_А.экв, дБа, определяется по формуле:

L_А.экв=А lg N+1,7 lg v +43,2,

где А=6,83+0,025+0,0375 р – коэффициент, зависящий от интервалов движения и характеристики проезжей части; N – интенсивность движения в оба направления, авт/ч; v – средняя скорость автомобильного потока, км/ч; р– суммарный процент грузового и общественного транспорта, %.

Эта формула рекомендована ЦНИИП градостроительства, она выведена на основе независимых исследований ряда специалистов.

В этой формуле приняты некоторые допущения. Например, считают, что расстояние между экипажами S<20м, интенсивность движения составляет N40км/ч. При таких значениях транспортного потока его относят к линейному источнику шума. Эти допущения позволили упростить расчеты, а определенные погрешности в результатах вполне допустимы для градостроительного проектирования.