Учебное пособие: Технология составления и решения моделей в MS Excel
Обработка модели "решателем" (поиск решения) на примере использования 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 |
| Всего | |
Автосуммированием найдите общее сокращение или увеличение выбросов на предприятиях. По полученным данным постройте круговые диаграммы для каждого из шести предприятий (не включая в диаграммы ячейки "Всего").
Проанализируйте построенные диаграммы.
