Приклад розрахунку в Excel

Ми розглянемо|розгледимо| приклад|зразок| розподілу енергетичних ресурсів (див. п. рис. 3.2) для порівняння розрахунку уручну|вручну| і з використанням Excel. Як вже наголошувалося, розроблена таблиця розрахунків призначена для вибору від двох до п'яти альтернатив по 2-8 критеріям. Тому всі приведені таблиці мають відповідне число рядків і стовпців, з|із| яких в даному завданні|задачі| буде заповнено тільки|лише| по три (по числу критеріїв і альтернатив завдання|задачі|)

Перший лист|аркуш| – "Дані" – показаний рис. 7.1.

Таблиця "Шкала відносної важливості" приведена лише для нагадування і на розрахунки не впливає. Решту частини|частки| листа|аркуша| розглянемо|розгледимо| докладніше.

Всі приведені таблиці з|із| формулами містити|утримувати| назви рядків і стовпців для полегшення складання таблиць з|із| розрахунками.

ТІЛЬКИ|лише| КОРЕКТНЕ ВВЕДЕННЯ ПРАВИЛЬНИХ ФОРМУЛ У ВІДПОВІДНІ ОСЕРЕДКИ|чарунок,вічка,комірки| ДОЗВОЛИТЬ ВАМ ОДЕРЖАТИ|отримати| РІШЕННЯ|розв'язання,вирішення,розв'язування|.

Більшість формул можуть бути введені|запроваджені| з використанням функції автозаповнення.

Для тих, хто не має достатніх навиків|навичок| введення формул, рекомендується вводити|запроваджувати| формули точно так, як вони приведені в таблицях, в т.ч. точно дотримуючи пунктуацію. Лапки, дужки, коми, крапка з комою мають своє значення у формулах.

Всі формули листа|аркуша| "Дані" (див. рис. 7.2) використовуються тільки|лише| для автоматизації заповнення осередків|чарунок,вічок,комірок| D25:D28. Тому можна відмовитися від використання формул на цьому листі|аркуші|, задаючи в осередках|чарунках,вічках,комірках| D25:D28 потрібні значення для критеріїв і альтернатив.

Рис. 7.1

Рис. 7.2 – Формули листа "Дані"

На листі|аркуші| "Критерії" проводиться|виробляється,справляється| попарне порівняння критеріїв з|із| отриманням|здобуттям| вектора пріоритетів:

Рис. 7.3

На цьому листі|аркуші| є ряд|лава,низка| додаткових логічних функцій (на перевірку значень). Ці функції використовуються для того, щоб спростити роботу користувачу і для оформлення таблиці. Так, в заголовках рядків і стовпців автоматично з'являються|появляються| критерії, вказані на листі|аркуші| "Дані". У даному прикладі|зразку| таких критеріїв три (вони з'являються|появляються| в таблиці автоматично), а рядки, що залишилися, із|із| стовпці залишаються порожніми|пустими|. Крім того, одиниці по головній діагоналі також з'являються|появляються| автоматично, "допомагаючи" користувачу визначити розмір таблиці. Порівнюючи критерії, необхідно заповнити тільки|лише| частину|частку| матриці над головною діагоналлю. Друга частина|частка| матриці заповнюється автоматично.

Всі необхідні розрахунки також виконуються автоматично, формуючи вектор пріоритетів.

У матрицях зручно відформатувати осередки|чарунок,вічка,комірки| як "Дробовий", "Простими дробами" (команда "Формат осередку|чарунки,вічка,комірки|") для того, щоб можна було вносити величини 1/3, 1/7 і ін.

Всі формули матриці порівнянь приведені в таблиці:

Таблица 7.1 – формули листа "Критерии"

	B	C	D	E	F	G	H	I	J	L
	КРИТЕРИИ	=Данные!C4	=Данные!C5	=ЕСЛИ(Данные!C6="";"";Данные!C6)	=ЕСЛИ(Данные!C7="";"";Данные!C7)	=ЕСЛИ(Данные!C8="";"";Данные!C8)	=ЕСЛИ(Данные!C9="";"";Данные!C9)	=ЕСЛИ(Данные!C10="";"";Данные!C10)	=ЕСЛИ(Данные!C11="";"";Данные!C11)	Вектор приоритетов
	0=Данные!C4									=D17
	=Данные!C5	=1/D4		1/3						=D18
	=ЕСЛИ(Данные!C6="";"";Данные!C6)	=ЕСЛИ(ЕОШ(1/E4);"";(1/E4))	=ЕСЛИ(ЕОШ(1/E5);"";(1/E5))	=ЕСЛИ(B6="";"";1)						=D19
	=ЕСЛИ(Данные!C7="";"";Данные!C7)	=ЕСЛИ(ЕОШ(1/F4);"";(1/F4))	=ЕСЛИ(ЕОШ(1/F5);"";(1/F5))	=ЕСЛИ(ЕОШ(1/F6);"";(1/F6))	=ЕСЛИ(B7="";"";1)					=D20
	=ЕСЛИ(Данные!C8="";"";Данные!C8)	=ЕСЛИ(ЕОШ(1/G4);"";(1/G4))	=ЕСЛИ(ЕОШ(1/G5);"";(1/G5))	=ЕСЛИ(ЕОШ(1/G6);"";(1/G6))	=ЕСЛИ(ЕОШ(1/G7);"";(1/G7))	=ЕСЛИ(B8="";"";1)				=D21
	=ЕСЛИ (Данные!C9="";"";Данные!C9)	=ЕСЛИ(ЕОШ(1/H4);"";(1/H4))	=ЕСЛИ(ЕОШ(1/H5);"";(1/H5))	=ЕСЛИ(ЕОШ(1/H6);"";(1/H6))	=ЕСЛИ(ЕОШ(1/H7);"";(1/H7))	=ЕСЛИ(ЕОШ(1/H8);"";(1/H8))	=ЕСЛИ(B9="";"";1)			=D22
	=ЕСЛИ (Данные!C10="";"";Данные!C10)	=ЕСЛИ(ЕОШ(1/I4);"";(1/I4))	=ЕСЛИ(ЕОШ(1/I5);"";(1/I5))	=ЕСЛИ(ЕОШ(1/I6);"";(1/I6))	=ЕСЛИ(ЕОШ(1/I7);"";(1/I7))	=ЕСЛИ(ЕОШ(1/I8);"";(1/I8))	=ЕСЛИ(ЕОШ(1/I9);"";(1/I9))	=ЕСЛИ(B10="";"";1)		=D23
	=ЕСЛИ (Данные!C11="";"";Данные!C11)	=ЕСЛИ(ЕОШ(1/J4);"";(1/J4))	=ЕСЛИ(ЕОШ(1/J5);"";(1/J5))	=ЕСЛИ(ЕОШ(1/J6);"";(1/J6))	=ЕСЛИ(ЕОШ(1/J7);"";(1/J7))	=ЕСЛИ(ЕОШ(1/J8);"";(1/J8))	=ЕСЛИ(ЕОШ(1/J9);"";(1/J9))	=ЕСЛИ(ЕОШ(1/J10);"";(1/J10))	=ЕСЛИ(B11="";"";1)	=D24

Як вже наголошувалося, значна частина|частка| формул призначена лише для зручної роботи. Таблица 7.1 містить|утримує| формули, які тільки|лише| полегшують роботу по введенню даних:

- автоматична підстановка критеріїв по рядках і стовпцях

- автоматична розстановка|розставляння| "одиниць" по головній діагоналі матриці

- автоматичний розрахунок зворотних величин для тих осередків|чарунок,вічок,комірок|, в яких проставлені дані порівнянь

- формування порожніх|пустих| осередків|чарунок,вічок,комірок| для тих, порівняння яких або поки не проведено|вироблено,справлено|, або для тих осередків|чарунок,вічок,комірок|, які не будуть заповнені (із-за меншого розміру матриці)

Враховуючи викладене вище, можна відмовитися від введення формул в матрицю, виконуючи всі необхідні дії уручну|вручну|, тобто самостійно внести критерії по рядках і стовпцях і заповнити матрицю.

На цьому ж листі|аркуші| проводиться|виробляється,справляється| розрахунок вектора пріоритетів. Для цього розрахунку введення формул обов'язкове:

Рис. 7.4 – Розрахунок вектора приоритетів на листі "Критерии"

Як видно|показно| з|із|, розрахунок вектора пріоритетів проводиться|виробляється,справляється| через середнє геометричне (Алгоритм №4, стор.31)

У осередку|чарунці,вічку,комірці| Е13 розраховується відношення|ставлення| узгодженості|погодженості| по формулі:

"=D38/Данные!D27"

При правильному розрахунку матриця має вигляд|вид|, як на рис. 7.3 (для умови даного завдання|задачі|).

Після|потім| оцінки критеріїв необхідно провести|виробити,справити| порівняння альтернатив по кожному з критеріїв. Порівняння по кожному критерію проводяться на окремих листах (що мають назви Кр1, Кр2 ., Кр8). У нашому завданні|задачі| критеріїв тільки|лише| три, тому для неї достатньо|досить| трьох листів. Всі листи містять|утримують| однакові формули, відрізняючись тільки|лише| осередком|чарункою,вічком,коміркою| А1, в якому дається посилання|заслання| на критерій, по якому порівнюються альтернативи, тобто на листі|аркуші| Кр1 посилання|заслання| на перший критерій, на листі|аркуші| Кр2 – на другій і т.д.

Рис. 7.5 – Матрица сравнений по первому критерию (Э)

Как и для листа "Критерии", ввод формул в эту часть листа является необязательным; формулы используются лишь для удобства работы и быстроты заполнения матриц:

	A	B	C	D	E	F	G	H	I
	=Данные!C4
		Альтернативы:	="Значение для критерия """&A1&""""
		=Данные!C17
		=ЕСЛИ(Данные!C18= "";"";Данные!C18)
		=ЕСЛИ(Данные!C19= "";"";Данные!C19)
		=ЕСЛИ(Данные!C20= "";"";Данные!C20)
		=ЕСЛИ(Данные!C21= "";"";Данные!C21)
			=Данные!C17	=Данные!C18	=ЕСЛИ(Данные!C19= "";"";Данные!C19)	=ЕСЛИ(Данные!C20= "";"";Данные!C20)	=ЕСЛИ(Данные!C21= "";"";Данные!C21)		нормированные значения
		=Данные!C17						=ЕСЛИ(Данные!D$26>= Данные!B17;1;0)	=ЕСЛИ(Данные!D$26>= Данные!B17;D23;"")
		=ЕСЛИ(Данные!C18= "";"";Данные!C18)	=ЕСЛИ(ЕОШ(1/D10);""; 1/D10)					=ЕСЛИ(Данные!D$26>= Данные!B18;1;0)	=ЕСЛИ(Данные!D$26>= Данные!B18;D24;"")
		=ЕСЛИ(Данные!C19= "";"";Данные!C19)	=ЕСЛИ(ЕОШ(1/E10); "";(1/E10))	=ЕСЛИ(ЕОШ(1/E11); "";(1/E11))	=ЕСЛИ(B12="";"";1)			=ЕСЛИ(Данные!D$26>= Данные!B19;1;0)	=ЕСЛИ(Данные!D$26>= Данные!B19;D25;"")
		=ЕСЛИ(Данные!C20= "";"";Данные!C20)	=ЕСЛИ(ЕОШ(1/F10); "";(1/F10))	=ЕСЛИ(ЕОШ(1/F11); "";(1/F11))	=ЕСЛИ(ЕОШ(1/F12); "";(1/F12))	=ЕСЛИ(B13="";"";1)		=ЕСЛИ(Данные!D$26>=Данные!B20;1;0)	=ЕСЛИ(Данные!D$26>=Данные!B20;D26;"")
		=ЕСЛИ(Данные!C21=""; "";Данные!C21)	=ЕСЛИ(ЕОШ(1/G10);""; (1/G10))	=ЕСЛИ(ЕОШ(1/G11);""; (1/G11))	=ЕСЛИ(ЕОШ(1/G12);""; (1/G12))	=ЕСЛИ(ЕОШ(1/G13);""; (1/G13))	=ЕСЛИ(B14="";"";1)	=ЕСЛИ(Данные!D$26>=Данные!B21;1;0)	=ЕСЛИ(Данные!D$26>=Данные!B21;D27;"")
		Отношение согласованности (ОС) =			=ЕСЛИ(ЕОШ(D39/Данные!D28);"";D39/Данные!D28)

Друга частина|частка| цього листа|аркуша| є|з'являється,являється| розрахунковою і введення формул є|з'являється,являється| обов'язковим:

Мал. 7.6 – Формули розрахунку вектора пріоритетів по першому критерію

Більшість формул (див. Ошибка! Источник ссылки не найден.) можуть бути введені|запроваджені| за допомогою автозаповнення.

Листи Кр2, Кр3 ., Кр8 містять|утримують| всі ті ж формули, що і лист|аркуш| Кр1, тому можуть бути одержані|отримані| копіюванням листа|аркуша| Кр1. (з|із| різницею тільки|лише| в осередку|чарунці,вічку,комірці| А1).

Рис. 7.7 – Листи "Кр2" и "Кр3"

Далі розглянемо лист "Результат":

Рис. 7.8 – Лист "Результат"

На листі|аркуші| "Результат" здійснюється побудова|шикування| вектора глобальних пріоритетів. Для випадку вибору автоматично формується відповідь (осередок|чарунка,вічко,комірка| К13). У нашому випадку, в завданнях|задачах| розподілу ресурсів, слід керуватися самими вектором глобальних пріоритетів.

Формули на цьому листі|аркуші| наступні|слідуючі|:

B

C

D

E

F

G

H

I

J

K

Альтернативы

Критерии

=Данные!C4

=Данные!C5

=ЕСЛИ(Данные!C6=""; "";Данные!C6)

=ЕСЛИ(Данные!C7=""; "";Данные!C7)

=ЕСЛИ(Данные!C8=""; "";Данные!C8)

=ЕСЛИ(Данные!C9=""; "";Данные!C9)

=ЕСЛИ(Данные!C10="";"";Данные!C10)

=ЕСЛИ(Данные!C11="";"";Данные!C11)

Глобальные приоритеты

Численное значение вектора приоритета

=Критерии!D17

=Критерии!D18

=ЕСЛИ(Критерии!D19=""; "";Критерии!D19)

=ЕСЛИ(Критерии!D20=""; "";Критерии!D20)

=ЕСЛИ(Критерии!D21=""; "";Критерии!D21)

=ЕСЛИ(Критерии!D22=""; "";Критерии!D22)

=ЕСЛИ(Критерии!D23=""; "";Критерии!D23)

=ЕСЛИ(Критерии!D24=""; "";Критерии!D24)

=Данные!C17

=Кр1!I10

=Кр2!I10

=Кр3!I10

=Кр4!I10

=Кр5!I10

=Кр6!I10

=Кр7!I10

=Кр8!I10

=C$6*Кр1!D23+Результат!D$6*Кр2!D23+Результат!E$6*Кр3!D23+Результат!F$6*Кр4!D23+Результат!G$6*Кр5!D23+Результат!H$6*Кр6!D23+Результат!I$6*Кр7!D23+Результат!J$6*Кр8!D23

=ЕСЛИ(Данные!C18="";""; Данные!C18)

=Кр1!I11

=Кр2!I11

=Кр3!I11

=Кр4!I11

=Кр5!I11

=Кр6!I11

=Кр7!I11

=Кр8!I11

=C$6*Кр1!D24+Результат!D$6*Кр2!D24+Результат!E$6*Кр3!D24+Результат!F$6*Кр4!D24+Результат!G$6*Кр5!D24+Результат!H$6*Кр6!D24+Результат!I$6*Кр7!D24+Результат!J$6*Кр8!D24

=ЕСЛИ(Данные!C19="";""; Данные!C19)

=Кр1!I12

=Кр2!I12

=Кр3!I12

=Кр4!I12

=Кр5!I12

=Кр6!I12

=Кр7!I12

=Кр8!I12

=C$6*Кр1!D25+Результат!D$6*Кр2!D25+Результат!E$6*Кр3!D25+Результат!F$6*Кр4!D25+Результат!G$6*Кр5!D25+Результат!H$6*Кр6!D25+Результат!I$6*Кр7!D25+Результат!J$6*Кр8!D25

=ЕСЛИ(Данные!C20="";""; Данные!C20)

=Кр1!I13

=Кр2!I13

=Кр3!I13

=Кр4!I13

=Кр5!I13

=Кр6!I13

=Кр7!I13

=Кр8!I13

=C$6*Кр1!D26+Результат!D$6*Кр2!D26+Результат!E$6*Кр3!D26+Результат!F$6*Кр4!D26+Результат!G$6*Кр5!D26+Результат!H$6*Кр6!D26+Результат!I$6*Кр7!D26+Результат!J$6*Кр8!D26

=ЕСЛИ(Данные!C21="";"";Данные!C21)

=Кр1!I14

=Кр2!I14

=Кр3!I14

=Кр4!I14

=Кр5!I14

=Кр6!I14

=Кр7!I14

=Кр8!I14

=C$6*Кр1!D27+Результат!D$6*Кр2!D27+Результат!E$6*Кр3!D27+Результат!F$6*Кр4!D27+Результат!G$6*Кр5!D27+Результат!H$6*Кр6!D27+Результат!I$6*Кр7!D27+Результат!J$6*Кр8!D27

="Следует остановить свой выбор на альтернативе """&СМЕЩ(B7;ПОИСКПОЗ(K13;K7:K11;0)-1;0;1;1)&""""

=НАИБОЛЬШИЙ(K7:K12;1)

с максимальным значением глобального приоритета =

Такий розрахунок не є|з'являється,являється| єдиним і можливо його як удосконалення (використання вбудованих в Excel можливостей|спроможностей| роботи з|із| масивами, додавання|добавка| макросів і ін.), так і спрощення (залишити тільки|лише| ті листи і формули, які безпосередньо беруть участь в розрахунках в збиток|шкоду| універсальності, зручності роботи і красі|вроді| оформлення).

Витрати|затрати| часу на створення|створіння| такої таблиці, зрозуміло, дещо більші, ніж розрахунок розбираного прикладу|зразка| уручну|вручну|. Проте|однак| це одноразові витрати|затрати|, які будуть доцільні, якщо доводиться періодично здійснювати вибір з|із| допомогою ТРАВЕНЬ. Кількість критеріїв і альтернатив, пропоноване в таблиці буде достатнім для більшості рішень|розв'язань,вирішень,розв'язувань|.

Переваги Excel стають очевидними при рішенні складніших задач.

Наприклад, рішення задачі про вибір Вузу (див. рис. 6.2) може бути знайдено за допомогою тієї ж самої таблиці, про яку було написано вище за умови, що|при умові , що,при условии | були створені всі вісім (або хоч би шість) листів порівнянь альтернатив по критеріях Кр1, Кр2., Кр6.

Введення початкових даних здійснюється на першому листі|аркуші|:

Рис. 7.9 – Критерії та альтернативи для вибору ВУЗу

Матриці порівнянь для критеріїв (всі оцінки см. п.6.2.1):

Рис. 7.10 – Матриця парних порівнянь для критеріїв

Матриці порівнянь альтернатив за критеріями мають вигляд|вид|:

Рис. 7.11 – Матриця парних порівнянь за критерієм "Організація навчального процессу" (У)

Рис. 7.12 – Матриця парних порівнянь за критерієм "Друзі" (Д)

Рис. 7.13 – Матриця парних порівнянь за критерієм: "Робота в студентських асоціаціях" (А)

Рис. 7.14 – Матриця парних порівнянь за критерієм: "Якість навчання" (ПО)

Рис. 7.15 – Матриця парних порівнянь за критерієм "Можливості бакалаврату" (БП)

Рис. 7.16 – Матриця парних порівнянь за критерієм "Можливості індивідуальних программ" (ИП)

Вектор глобальных приоритетов имеет вид:

Рис. 7.17 – Вектор глобальних приоритетів

Розрахунок в Excel проводиться|виробляється,справляється| з|із| набагато більш високою точністю, чим це робилося|чинилося| при ручних розрахунках (див. п.6.2.1), проте|однак| одержані|отримані| результати досить близькі. Слід ще раз відзначити, що результат в Excel формується відразу ж після|потім| введення всіх даних без проведення яких-небудь додаткових побудов|шикувань| або розрахунків.

Ще однією перевагою розрахунків за допомогою ЕОМ (зокрема, Excel, хоча це відноситься практично до всіх програмних продуктів) є|з'являється,являється| те, що можна дуже швидко оцінити стійкість рішення|розв'язання,вирішення,розв'язування|, тобто вплив оцінок на результат.

Покажемо це на останньому прикладі|зразку|.

Матриця попарних порівнянь критеріїв має дуже високе відношення узгодженості|погодженості| (див. рис. 7.10) – 22,65%. Допустимо, що що ухвалює рішення переглянув свої оцінки, поліпшивши їх узгодженість|погодженість| (див. рис. 7.18).

При цьому пріоритети змінилися (див. рис. 7.19)

Рис. 7.18 – Нова матриця порівнянь крирерієв

Рис. 7.19 – Новий результат выбору альтернативи

Зміна важливості критеріїв привела до того, що відносна важливість Вузу А стала домінуючою, тоді як за початкових умов перевага віддавалася Вузу В.

7.2 Специальні програмні продукти[1]

«Імператор» – це унікальна системадля вирішення завдань|задач| вибору і незамінний помічникв ухваленні|прийнятті,прийманні| рішень|розв'язань,вирішень,розв'язувань| для політиків, фінансистів, аналітиків, економістів, працівників управління всіх рівнів, незалежних експертів. Це універсальна система підтримки ухвалення|прийняття,приймання| рішень|розв'язань,вирішень,розв'язувань| (Decision Support System) в різних сферах діяльності.

«Імператор» дозволить ухвалювати рішення в умовах недоліку|нестачі| або суперечності інформації, різноманітності або великої кількості чинників|факторів|, і інших умов, що утрудняють ухвалення|прийняття,приймання| рішення|розв'язання,вирішення,розв'язування|.

Система «Імператор» дозволяє вирішити найважливіші проблеми:

· вибір пріоритетного рішення|розв'язання,вирішення,розв'язування|;

· розподіл ресурсів.

У основусистеми підтримки ухвалення|прийняття,приймання| рішення|розв'язання,вирішення,розв'язування| «Імператор» покладений метод аналізу ієрархій (метод багатокритерійного рейтингування| альтернатив), що має чіткі математичні і психологічні обгрунтування і численні|багаточисельні| додатки|застосування|. Дана методологія відповідає природному ходу людського мислення і узагальнює підхід, що реалізовується в найбільш поширених експертних системах, заснованих на байєсовському| логічному висновку|виведенні|.

Ученими науково-дослідного центру НЕЙРОСПЛАВ|зроблені важливі|поважні| удосконалення методу аналізу ієрархій. Завдяки цьому можливості|спроможності|системи «Імператор» вражають і вигідно відрізняються від аналогів, що є|наявний| на ринку інтелектуальних програмних продуктів.

«Імператор» дозволяє:

· створювати графічну схему проблеми;

· проводити збір|збирання| даних від експертів;

· оцінювати і мінімізувати ступінь|міру| суперечності даних;

· обчислювати|обчисляти,вичисляти| рейтинг альтернативних рішень|розв'язань,вирішень,розв'язувань|;

· вести тематичний каталог проектів, що включають моделі рейтингування| і набори даних;

· досліджувати стійкість рейтингу;

· виявляти істотні|суттєві| чинники|фактори|;

· створювати і аналізувати динамічні сценарії розвитку ситуації;

· вирішувати задачі відновлення ситуацій по відомих рейтингах (так звані зворотні завдання|задачі|);

· проводити аналіз проблеми на основі декількох моделей;

· моделювати дію випадкових чинників|факторів|;

· здійснювати роботу з|із| Microsoft Excel по експорту і імпорту моделей;

· створювати докладні звіти в Microsoft Word.

«Імператор» дозволяє проводити аналіз і синтездля ситуацій ухвалення|прийняття,приймання| рішень|розв'язань,вирішень,розв'язувань|, як за наявності об'єктивної кількісної інформації, так і за наявності якісної інформації (дані типу переваг), наданої експертами. Завдяки цьому в процесі ухвалення|прийняття,приймання| рішень|розв'язань,вирішень,розв'язувань| враховується «людський чинник|фактор|».

«Імператор» має сучасний зручний інтерфейс, оснащений докладною довідкою, в якій описані прийоми роботи з|із| програмою, технології створення|створіння| моделей і проектів, приведені приклади|зразки|. «Імператор» реалізований в російсько-| і англомовному варіантах.

«Імператор», завдяки принципам, закладеним в його основу, універсальний. Він надає систематичні, регламентовані процедури осмисленого ухвалення|прийняття,приймання| рішень.|розв'язань,вирішень,розв'язувань|

Демо-версія "Імператора" доступна на сайті www.neirosplav.com