Решение прикладных задач обработки информации средствами MS Excel: Учебно-методическое пособие

88 Pages·2021·2.464 MB·Russian

by  Демидова Л. А.

МИНИСТЕРСТВО НАУКИ И ВЫСШЕГО ОБРАЗОВАНИЯ
РОССИЙСКОЙ ФЕДЕРАЦИИ
ФЕДЕРАЛЬНОЕ ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ
ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ
ВЫСШЕГО ОБРАЗОВАНИЯ
«МИРЭА ‐ Российский технологический университет»
(РТУ МИРЭА)
Демидова Л. А.
Решение прикладных задач обработки
информации средствами MS Excel
Учебно-методическое пособие
Москва 2021
УДК 004
ББК 32.973
Д 30

Демидова Л.А. Решение прикладных задач обработки информации средствами MS
Excel [Электронный ресурс]: Учебно-методическое пособие / Демидова Л.А. — М.: МИРЭА
– Российский технологический университет, 2021. — 1 электрон. опт. диск (CD-ROM)
В учебно-методическом пособии рассматриваются аспекты комплексного решения
прикладных задач обработки информации средствами MS Excel на примере задач,
рассматриваемых как задачи статистических игр. Предлагается перечень заданий, при
выполнении которых применяются различные критериев принятия решений. Предназначено
для бакалавров по направлению 09.03.04 Программная инженерия.

Учебно-методическое пособие издается в авторской редакции.
Автор: Демидова Лилия Анатольевна.

Рецензенты:
Андреева Ольга Николаевна, д.т.н., доцент, начальник отдела научной работы АО «Концерн
МОРИНФОРМСИСТЕМА-АГАТ»
Головин Сергей Анатольевич, д.т.н., профессор, заведующий кафедрой математического
обеспечения и стандартизации информационных технологий, Институт
информационных технологий, РТУ МИРЭА

Системные требования:
Наличие операционной системы Windows, поддерживаемой производителем.
Наличие свободного места в оперативной памяти не менее 128 Мб.
Наличие свободного места в памяти постоянного хранения (на жестком диске) не менее 30 Мб.
Наличие интерфейса ввода информации.
Дополнительные программные средства: программа для чтения pdf-файлов (Adobe Reader).
Подписано к использованию по решению Редакционно-издательского совета
МИРЭА — Российский технологический университет.
Обьем: 3.05 мб
Тираж: 10

© Демидова Л.А., 2021
© МИРЭА – Российский технологический
университет, 2021
ОГЛАВЛЕНИЕ
ВВЕДЕНИЕ ........................................................................................................................................ 4
СТАТИСТИЧЕСКИЕ ИГРЫ ............................................................................................................. 5
1. Основные понятия .................................................................................................................. 5
2. Критерий азартного игрока (максмаксный критерий) ........................................................ 9
2.1. Математические аспекты критерия азартного игрока ................................................ 9
2.2. Решение задачи 1 с использованием критерия азартного игрока средствами MS
Excel …………………………………………………………………………………………….10
3. Максиминный критерий (критерий Вальда) ...................................................................... 20
3.1. Математические аспекты максиминного критерия (критерия Вальда) ................... 20
3.2. Решение задачи 1 с использованием максиминного критерия (критерия Вальда)
средствами MS Excel ................................................................................................................... 20
4. Критерий Гурвица (критерий пессимизма – оптимизма) ................................................. 24
4.1. Математические аспекты критерия Гурвица .............................................................. 24
4.2. Решение задачи 1 с использованием критерия Гурвица ................................................ 25
4.3. Исследование зависимости выбранной по критерию Гурвица стратегии игрока A
(фермера) от значения коэффициента пессимизма .................................................................. 30
5. Критерий Сэвиджа (критерий минимаксного риска) ........................................................ 39
5.1. Математические аспекты критерия Сэвиджа ............................................................ 39
5.2. Решение задачи 1 с использованием критерия Сэвиджа .............................................. 39
6. Критерий Байеса ................................................................................................................... 44
6.1. Математические аспекты критерия Байеса (случай 1) .............................................. 44
6.2. Решение задачи 1 с использованием критерия Байеса (случай 1) ................................ 44
6.3. Математические аспекты критерия Байеса (случай 2) .............................................. 51
7. Критерий Лапласа (критерий недостаточного основания) ............................................... 56
7.1. Математические аспекты критерия Лапласа .............................................................. 56
7.2. Решение задачи 1 с использованием критерия Лапласа ............................................... 57
8. Критерий Ходжеса-Лемана .................................................................................................. 60
8.1. Математические аспекты критерия Ходжеса-Лемана .............................................. 60
8.2. Решение задачи 1 с использованием критерия Ходжеса-Лемана................................ 61
8.3. Исследование зависимости выбранной по критерию Ходжеса-Лемана стратегии
игрока A (фермера) от значения коэффициента доверия ....................................................... 65
10. Подведение итогов ............................................................................................................ 68
11. Пример задачи, сводящейся к задаче на статистические решения .............................. 70
ЗАДАНИЯ ......................................................................................................................................... 74
СПИСОК ЛИТЕРАТУРЫ ............................................................................................................... 86
3
ВВЕДЕНИЕ
Учебно-методическое пособие излагает общие принципы решения при-
кладных задач обработки информации средствами MS Excel на примере задач,
рассматриваемых как задачи статистических игр, то есть задачи, в которых
принимают участие два игрока – «статистик» и «природа», и все решения при-
нимаются с точки зрения «статистика» с применением различных критериях
принятия решений.
В учебно-методическом пособии рассматриваются вопросы комплексного
использования средств MS Excel при решении прикладных задач обработки
информации на примере типичной задачи, формулируемой как задача стати-
стической игры: приводятся рекомендации по форматированию данных, ис-
пользованию относительных, абсолютных, смешанных ссылок, применению
встроенных функций, созданию и табулированию пользовательских функций,
созданию диаграмм, работе с различными листами рабочей книги MS Excel, с
формулами массивов, со средствами автозаполнения и подбора параметров.
Читателю предлагается перечень заданий: каждое из них содержит форму-
лировку задачи статистической игры, в ходе решения которой с применением
предлагаемых критериев должна быть выбрана лучшая стратегия «статистика».
Читатель может получить дополнительные сведения по теории статистиче-
ских решений в книгах [1 – 13], по работе с MS Excel – в книгах [14 – 20].

4
СТАТИСТИЧЕСКИЕ ИГРЫ
1. Основные понятия
Математические модели принятия оптимальных решений могут строиться:
 в условиях определенности;
 в условиях риска;
 в условиях неопределенности.
Моделирование в условиях определенности предполагает наличие всех не-
обходимых для этого исходных нормативных данных (такое моделирование
применяется, например, в задачах производственного и сетевого планирова-
ния).
Моделирование в условиях риска проводится при стохастической неопре-
деленности, когда значения некоторых исходных данных случайны и известны
законы распределения вероятностей этих случайных величин (такое моделиро-
вание применяется, например, в задачах регрессионного анализа, а также в за-
дачах теории массового обслуживания).
Моделирование в условиях неопределенности соответствует полному от-
сутствию некоторых необходимых для этого данных (такое моделирование
применяется, например, в задачах теории игр, а также в задачах теории стати-
стических решений).
Математические модели принятия оптимальных решений в конфликтных
ситуациях строятся в условиях неопределенности.
В теории игр большое внимание уделяется матричным играм.
Матричные игры – это стратегические конечные одношаговые игры с дву-
мя игроками.
Матричная игра полностью определяется матрицей игры F , номера строк
и столбцов которой – это чистые стратегии первого и второго игроков соот-
ветственно.
Элемент f матрицы игры – это выигрыш первого игрока, если он приме-
ij
нил i -ю чистую стратегию, а второй игрок применил j-ю чистую стратегию;
при этом выигрыш второго игрока равен ( f ).
ij
Кортеж i, j , где i , j – чистые стратегии первого и второго игроков соот-
ветственно, называется ситуацией в чистых стратегиях.
В матричной игре матрица игры представляется в виде
5
 f f ... f 
11 12 1n
 
f f ... f
F   21 22 2n,
 ... ... ... ... 
 
f f ... f
 
m1 m2 mn
где f – выигрыш первого игрока, если он выбрал i-ю стратегию (i 1,m), а
ij
второй игрок выбрал j-ю стратегию ( j 1,n).
В такой игре значения выигрышей обоих игроков в каждой ситуации i, j
равны по величине и противоположны по знаку: f (f )  0, то есть выиг-
ij ij
рыш одного игрока в матричной игре всегда равен проигрышу другого. Игру
такого типа называют игрой с нулевой суммой.
Матрицу игры F также называют платежной матрицей.
Матричная игра осуществляется следующим образом:
 задается матрица игры F ;
 первый игрок независимо от второго игрока выбирает одну из m строк
матрицы игры, например, i -ю строку;
 второй игрок независимо от первого игрока выбирает один из n столб-
цов матрицы игры, например, j-й столбец;
 первый игрок получает выигрыш f , равный проигрышу второго игро-
ij
ка, и на этом игра заканчивается.
В отличие от задач теории игр в задачах теории статистических решений
(в статистических играх) неопределенная ситуация не имеет антагонистиче-
ской конфликтной окраски и зависит от объективной действительности, кото-
рую принято называть «природой».
В матричных играх с «природой» в качестве второго игрока выступает со-
вокупность неопределенных факторов, влияющих на эффективность принима-
емых решений.
Пусть первый игрок в статистической игре – это игрок A, а второй игрок –
это игрок B.
В таких задачах игрок A называется статистиком, поэтому рассматрива-
емая модель принятия решений о выборе игроком A стратегии и называется
статистической игрой.
В статистической игре два игрока: действующий сознательно игрок A
(статистик) и действующий неосознанно (случайно) игрок B (природа).
Игрок A (статистик) имеет m стратегий: a , a ,…, a , где m  2.
1 2 m
Игрок B (природа) имеет n стратегий: b , b ,…, b , где n2.
1 2 n
6
Если игрок A (статистик) применил стратегию a (i 1,m) и при этом
i
игрок B (природа) применил стратегию b ( j 1,n), то выигрыш (доход) игро-
j
ка A (статистика) равен f [если значение f отрицательное, то оно имеет
ij ij
смысл потерь игрока A (статистика)]. Выигрыши игрока B (природы) в ста-
тистической игре не рассматриваются.
Все значения f (i 1,m, j 1,n) известны игроку A (статистику) и яв-
ij
ляются элементами матрицы игры F размером mn. Эта матрица называется
матрицей статистической игры.
Статистическая игра проходит так: игрок A (статистик), не зная о том,
какую стратегию выберет игрок B (природа), выбирает, зная матрицу F , одну
свою стратегию a (i 1,m).
i
Игрок B (природа) случайно выбирает одну свою стратегию b ( j 1,n).
j
Игрок A (статистик) получает выигрыш f , т.е. выигрыш, равный эле-
ij
менту в i -й строке и j-м столбце матрицы F . На этом статистическая игра за-
канчивается.
Матричные игры с природой отличаются от обычных матричных игр тем,
что при выборе оптимальной стратегии первым игроком уже нельзя ориентиро-
ваться на то, что второй игрок будет стремиться минимизировать свой проиг-
рыш.
При этом наряду с матрицей F статистической игры используется матри-
ца рисков R, задаваемая как:
r r ... r 
11 12 1n
 
r r ... r
R   21 22 2n .
... ... ... ... 
 
r r ... r
 
m1 m2 mn
Матрица рисков R имеет тот же размер, что и матрица статистической иг-
ры F .
Элементы r (i 1,m, j 1,n) матрицы рисков R, называемые рисками,
ij
вычисляются так:
 в каждом j-м столбце ( j 1,n) матрицы статистической игры F
находится максимальное значение H ;
j
 в каждом j-м столбце ( j 1,n) матрицы рисков R риск r находится
ij
как r  H  f (i 1,m).
ij j ij
7
Величина H – это выигрыш, который получил бы игрок A (статистик),
j
если заранее знал, что игрок B (природа) будет применять стратегию b . Риск
j
r – это недополученный выигрыш (относительно максимально возможного)
ij
игрока A (статистика) при использовании им стратегии a , когда игрок B
i
(природа) применяет стратегию b .
j
Возможны различные постановки задачи о выборе решения в матричной
игре с природой (в статистической игре), предполагающие, например, максими-
зацию выигрыша или минимизацию риска.
Принятие решений в статистических играх может осуществляться:
 в условиях риска, когда известна функция распределения вероятностей
выбора стратегий игроком B (природой);
 в условиях неопределенности, когда функция распределения вероятно-
стей выбора стратегий игроком B (природой) неизвестна.
Для выбора стратегии игрока A (статистика) в статистической игре мо-
гут быть использованы такие критерии, как:
 критерий азартного игрока;
 максиминный критерий (критерий Вальда);
 критерий Гурвица;
 критерий Сэвиджа;
 критерий Байеса;
 критерий Лапласа;
 критерий Ходжеса-Лемана.
Применение вышеуказанных критериев может быть рассмотрено на при-
мере следующей задачи.

Задача 1.
Пусть весной фермер может засеять поле одной из двух культур: К1 или
К2.
Пусть лето может быть одним из трех: засушливым, нормальным, сырым.
Пусть игрок A– это фермер, имеющий две стратегии:
 стратегию a – засеять поле культурой К1;
1
 стратегия a – засеять поле культурой К2.
2
Пусть игрок B– это природа, имеющая три стратегии:
 стратегию b – сделать лето засушливым;
1
 стратегию b – сделать лето нормальным;
2
8
 стратегию b – сделать лето сырым.
3
Доход, который получит фермер (игрок A) после сбора и реализации уро-
жая с данного поля, зависит от выбранной им стратегии и стратегии природы.
В табл. 1 приведены значения этого дохода в условных единицах (у.е.).
При этом отрицательное значение дохода соответствует потерям фермера.

Таблица 1. Доходы фермера
Стратегия Стратегия игрока B
игрока A b b b
1 2 3
a 10 50 –1
1
a –50 80 2
2

Какую стратегию должен выбрать игрок A (фермер), выступающий в роли
статистика?
Для получения максимального дохода, равного 80 у.е., игроку A (ферме-
ру) надо выбрать стратегию a . Но этот максимальный доход будет получен
2
только для стратегии b игрока B (природы). Если же игрок B (природа) при-
2
меняет стратегию b , то игрок A (фермер) имеет потери, равные 50 у.е.
1
Таким образом, игрок A (фермер) должен осознанно выбрать стратегию в
условиях неопределенности относительно поведения игрока B (природы), вы-
бирающего свою стратегию неосознанно.
2. Критерий азартного игрока (максмаксный критерий)

2.1. Математические аспекты критерия азартного игрока
В критерии азартного игрока выигрыш игрока A (статистика) будет ра-
вен максимальному элементу матрицы F статистической игры:
f  maxmax f
max ij
i1,m j1,n
(при «удачном стечении обстоятельств») или меньше (в общем случае).
В этом критерии считается, что принятие решения игроком А (статисти-
ком) осуществляется в условиях неопределенности.
При использовании критерия азартного игрока для выбора стратегии на
основе матрицы F статистической игры необходимо выбрать стратегию игрока
A (статистика), соответствующую строке с максимальным элементом матри-
9
цы F статистической игры (если таких строк несколько, то следует выбрать
стратегию, соответствующую любой из них).
При использовании критерия азартного игрока игра с природой осуществ-
ляется с позиции крайнего оптимизма для матрицы F статистической игры.
В этом критерии игрок A (статистик) стремится получить максимально
возможный выигрыш.
Критерий азартного игрока имеет смысл использовать в случае очень низ-
кого риска, а также когда выигрыш намного превышает возможные потери.
2.2. Решение задачи 1 с использованием критерия азартного игрока
средствами MS Excel
В задаче 1 про фермера и природу матрица F статистической игры имеет
вид:
b b b
1 2 3
a 10 50 1
1
a 50 80 2 .
2
В матрице F статистической игры максимальный элемент равен 80, он
находится в строке 2, поэтому игрок A (фермер) по критерию азартного игрока
должен, стремясь получить максимально возможный выигрыш, выбрать страте-
гию a (засеять поле культурой К2).
2

Рисунок 1. Фрагмент рабочей книги с именами рабочих листов по умолчанию
и выбранным активным листом ЛИСТ1

Рисунок 2. Фрагмент рабочей книги с выбранным активным листом ЛИСТ1
и контекстным меню

10