Инструкция по использованию Microsoft Excel

Пример 1 Директор финансовой компании проводит риско­ванную финансовую операцию. Страховая компания предлагает за­страховать сделку и предлагает 4 варианта страховки:

A ₁, A₂, A ₃, 4. Компенсация ущерба для каждого варианта зависит от того, какой из возможных страховых случаев произошел. Выделяют 5 видов страховых случаев: S₁, S₂, S₃, S₄, S₅. Компенсации (тыс. у. е.) для каждого вида страховки при каждом страховом случае состав­ляют ма трицу выигр ы шей ви д а

Ai Sj	S1 43 41 39 37	S2 22 37 48 29	S3 42 40 37 32	S4 49 38 42 58	S5 45 42 36 41
A1
A2
A3
A4

Выбрать наилучшую альтернативу, используя критерии Лап­ласа, Вальда, Байеса (при вероятностях состояний исходов p₁ = 0,3; p₂ = 0,2; p₃ = 0,1; p₄ = 0,3; p₅ = 0,1), Сэвиджа и Гурвица (при коэффициенте доверия = 0,4).

Вводим данные в электронную таблицу и готовим подписи в ячейках для дальнейшего расчета согласно рисунку:

Вычисляем функции полезности для критерия Лапласа. Для этого ставим курсор в ячейку G2 и вводим формулу, усредняющую значения показателей привлекательности по первой ал ь тернативе. Для этого вызываем мастер функций, нажимая на кнопку fx и выбираем в категории «Статистические» функцию «СРЗНАЧ», в качестве аргу­мента функции указываем ячейки B2:F2, обводя их курсором. Нажи­маем ОК, видим результат 40,2. Автозаполняем ячейки G2-G5, пере­таскивая нижний правый уголок ячейки G2. Видно, что наибольшая функция полезности 40,4 для альтернативы А3. Вводим в G6: «А3».

Для критерия Вальда вычисляем наименьшие показатели при­влекательности для каждой альтернативы. Для этого вводим в Н2 функцию МИН с аргументами B2:F2: «=МИН(B2:F2)» (кавычки не вводить!). Автозаполняем на Н2-Н5. Выбираем альтернативу, где ре­зультат наибольший. Это значение 37 для альтернативы А2, вводим в Н6: «А2».

Для критерия Байеса функции полезности равны суммам вы­игрышей, умноженным на вероятности их исходов. Вводим в I2 фор­мулу:

«=В2*0,3+C2*0,2+D2*0,1+E2*0,3+F2*0,1», автозаполняем на I2-I5. Выбираем альтернативу с наибольшей функ­цией полезности, то есть А4, вводим в I6: «А4».

Для критерия Сэвиджа необходимо построить матрицу рисков. Для этого ставим курсор в ячейку В8 и вводим формулу «=МАКС(B$2:B$5)-B2», автозаполняем результат на ячейки В8-F11. Далее находим максимальный риск для каждой альтернативы. Для этого ставим курсор в ячейку J2 и вводим «=МАКС(B8:F8)», автозаполняем результат на J2-J5. Выбираем альтернативу с минимальным риском, это А3. Вводим в J6: «А3».

Для критерия Гурвица нужно наибольшее значение каждой альтернативы умножить на  (по условию  = 0,4), наименьшее на (1-) и результаты сложить. Вводим в К2 формулу:

=МАКС(B2:F2)*0,4+МИН(B2:F2)*0,6

и автозаполняем результат на К2-К5. Выбираем альтернативу с наи­большей функцией полезности. Это А3, вводим К6: «А3». Задача ре­шена.

Рассмотрим теперь метод решения задачи в случае минимиза­ции критерия – «чем меньше, тем лучше».

ПРИМЕР 2. Фермер, имея в аренде большие площади под посев кукурузы, заметил, что влажности почвы в сезон созревания ку­курузы недостаточно, чтобы получить максимальный урожай. Экспер­ты советовали фермеру провести дренажные каналы в период конца весны – начала лета, что должно значительно повысить урожай. Были предложены 5 проектов дренажных каналов: A₁,A₂,A₃,A₄,A₅, за­траты на которые зависят от погодных условий в период весна – лето. Возможны варианты: S₁ – дождливая весна и дождливое лето; S₂ – дождливая весна и сухое лето; S₃ – сухая весна и дождливое лето; S₄ – сухая весна и сухое лето. Матрица затрат имеет вид:

Ai Sj	S1	S2	S3	S4
A1
A2
A3
A4
A5

Выбрать наилучшую альтернативу, используя критерии Лап­ласа, Вальда, Байеса с p₁ = 0,2; p₂ = 0,3; p₃ = 0,3; p₄ = 0,2, Сэвиджа и Гурвица при коэффициенте доверия  = 0,7.

Вводим данные в электронную таблицу и готовим подписи в ячейках для дальнейшего расчета согласно рисунку:

Вычисляем функции полезности для критерия Лапласа. Для этого ставим курсор в ячейку F2 и вводим формулу: «=СРЗНАЧ(В2:Е2)», автозаполняем на F2-F6. Наилучшей в данном случае считается альтернатива с минимальной функцией полезности, это А2. Вводим в F7: «А2».

Для критерия Вальда вычисляем наибольшие показатели при­влекательности для каждой альтернативы. Для этого вводим в G2 функцию «=МАКС(B2:E2)», автозаполняем на G2-G6. Выбираем аль­тернативу, где результат наименьший, вводим в G7: «А2».

Для критерия Байеса функция полезности вычисляется так же как и для предыдущего примера (но для 4-х состояний природы), в ячейку Н2 формулу «=B2*0,2+C2*0,3+D2*0,3+E2*0,2», автозаполняем на Н2-Н6. Выбираем альтернативу с наименьшей функцией полезно­сти, это А1, вводим в Н7: «А1».

Для критерия Сэвиджа необходимо построить матрицу рисков. Для этого ставим курсор в ячейку В9 и вводим формулу «=B2-МИН(B$2:B$6)», автозаполняем результат на ячейки В9-Е13. Далее находим максимальный риск для каждой альтернативы. Для этого ставим курсор в ячейку I2 и вводим «=МАКС(B9:E9)», автоза-полняем результат на I2-I6. Выбираем альтернативу с минимальным риском, таких альтернатив две, это А1 и А4. Вводим в I7: «А1, А4».

Для критерия Гурвица нужно наименьшее значение каждой альтернативы умножить на  (по условию  = 0,7), наибольшее на

(1-) и результаты сложить. Вводим в J2 формулу:

= МИН(B2:E2) *0,7+МАКС(B2:E2)*0,3 и автозаполняем результат на J2-J6. Выбираем альтернативу с наи­меньшей функцией полезности. Это А1, вводим J7: «А1». Задача ре­шена.

Задание 1. Директор торговой фирмы, продающей телевизоры, решил открыть представительство в областном центре. У него имеются альтернативы либо создавать собственный магазин в отдельном помещении, либо организовывать сотрудничество с местны­ми торговыми центрами. Всего можно выделить 5 альтернатив ре­шения: A₁, A₂, A₃, A₄, A₅. Успех торговой фирмы зависит от того, как сложится ситуация на рынке предоставляемых услуг. Эксперты выделяют 4 возможных варианта развития ситуации S₁,S₂,S₃,S₄. Прибыль фирмы для каждой альтернативы при каждой ситуации представлена матрицей выигрышей a_ij (млн. р./год).

Bj Аi А1	S1	S2	S3	S4
a1
А2
А3
А4
А5

Выбрать наилучшую альтернативу, используя критерии Лап­ласа, Вальда, Байеса с p₁ = 0,4; p₂ = 0,3; p₃ = 0,1; p₄ = 0,2, Сэвиджа и Гурвица при коэффициенте доверия  = 0,6.

Задание 2. Нефтяная компания собирается построить в рай­оне крайнего севера нефтяную вышку. Имеется 4 проекта A, B, C и D. Затраты на строительство (млн. руб.) зависят от того, какие по­годные условия будут в период строительства. Возможны 5 вариан-S,S,S,S,S тов погоды ¹²³⁴⁵. Выбрать оптимальный проект для

строительства используя критерии Лапласа, Вальда, Байеса с p₁ = 0,1; p₂ = 0,2; p₃ = 0,3; p₄ = 0,2; p₅ = 0,2, Сэвиджа и Гурвица при a = 0,6. Матрица затрат имеет вид:

Аi Sj	S1	S2	S3	S4	S5
A1 A2 A3 A4	а 1
А 2
А3
А4

Выбрать наилучшую альтернативу, используя критерии Лап­ласа, Вальда, максимального оптимизма, Сэвиджа и Гурвица при ко­эффициенте доверия  = 0,5

Величина а использованием процессора Excel равна номеру варианта. Отчет по каждому заданию должен содержать функции полезности либо иные показатели каждого критерия, вывод о том, какую альтернативу следует принять.

ПРИМЕРНЫЕ ВОПРОСЫ НА ЗАЩИТЕ РАБОТЫ

1. Какие методы называются Играми с природой?

2. Какой критерий называется критерием – «чем меньше, тем лучше»?

3. Что из себя представляют критерии Лап­ласа, Вальда, Байеса?

Лабораторная работа № 19