Технологія складання і рішення моделей у MS Excel

[ виправити ] текст може містити помилки, будь ласка перевіряйте перш ніж використовувати.

Технологія складання і рішення моделей у 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)

Елементи системи

Елементи

Допустимі кордону

хв

макс

Речовини

Кисень, не менш

10,000

БПК _5, не більше

6,000

Атразин

0,005

Метафос

0,020

Цинеб

0,030

Інтенсивність підприємства

150,000

Інтенсивність ферми: свині

2000,000

Інтенсивність ферми: корови

1000,000

Всього

= СУММ (B38: B45)

= СУММ (C38: C45)

Прибуток, руб

Економ. збиток, руб

5000000

Рішення

Елементи системи

Кількість одиниць

Вартість, руб

Лісосмуга, 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	Підкачка води, км ³	0	5	0,5
14	Скидання води, км ³	0	5
15	Штучна аерація, руб за мг / л	0	10	0,25
1 червня	Фінансування на 2 місяці, руб	300
1 липня	Рішення
1 серпня		За умовою						За рішенням
19	Зона	Промислова зона		Середня зона		Культурна зона		Промислова зона	Середня зона	Культурна зона
2 0	Речовини	хв	макс	хв	макс	хв	макс
2 Січень	Неорганіка							= C4	= E4	= G4
2 лютого	Органіка							= C5	= E5	= G5
2 Березня	Кисень							= C6	= E6	= G6
2 квітня	Рівень води
5 лютого	Фінансування
26	Вид діяльності	За умовою				За рішенням
27		Кількість		Вартість		Кількість	Вартість
28		хв	макс	хв	макс
29	Підкачка води, м ³					= C13	= F33 * D13 * 30
3 0	Скидання води, м ³
3 січня	Штучна аерація, руб за мг / л					= C15	= F35 * D15 * 30
2 Березня	Фінансування на 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. Внести до файл вихідну інформацію:

Графічний метод розрахунку рівнів звуку L _А.екв

Час доби

	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
Всього

Автосуммирование знайдіть загальне скорочення або збільшення викидів на підприємствах. За отриманими даними побудуйте кругові діаграми для кожного з шести підприємств (не включаючи до діаграми осередку "Всього").

Проаналізуйте побудовані діаграми.

Додати в блог або на сайт

Цей текст може містити помилки.

Програмування, комп'ютери, інформатика і кібернетика | Методичка
117кб. | скачати

Схожі роботи:
Рішення математичних завдань засобами Excel
Використання Excel для рішення статистичних завдань
Рішення математичних задач за допомогою алгоритмічної мови Turbo Pascal Microsoft Excel
Технологія інтенсифікації навчання на основі схемних і знакових моделей навчального матеріалу на уроках
Технологія складання літака
Технологія складання дюрітових сполук
Технологія складання межгондольной перегородки
Технологія складання та випробування літальних апаратів
Технологія складання нестандартних задач з математики