Джерард Вершурен. 100 симуляций в Excel

Эта перевод книги, посвященной моделированию в Excel. Моделирование – в первую очередь это анализ «что, если». В его основе лежат фиксированные значения в ячейках. Более сложные модели основаны на изменяющихся значениях, распределенных нормально. И это уже моделирование по методу Монте-Карло. Моделирование находит применение в науке, финансах, статистике, логистике, … при решении головоломок. Модели в этой книге основаны на функциях и инструментах Excel (без использования VBA). Каждая глава описывает одну модель по схеме: что делает симуляция, что нужно об этом знать и что нужно сделать. Разделы посвящены отдельным областям знания, таким как статистика или генетика. Даже если это не ваша область интересов, вы всё равно получите пользу от изучения моделей. Они подарят вам массу идей для создания собственных симуляций. Кроме того, моделирование – это весело. Наслаждайтесь!

Dr. Gerard M. Verschuuren. 100 Excel Simulations. Using Excel to Model Risk, Investments, Genetics, Growth, Gambling and Monte Carlo Analysis. – USA: Holy Macro! Books, 2017.

Скачать заметку в формате Word или pdf, примеры в формате Excel

zip-архив содержит набор файлов, которые можно использовать для пошаговых инструкций, содержащихся в книге. В архиве также есть второй набор с финальным видом моделей. Стиль изложения предполагает, что вы не новичок в Excel.

При переводе я использовал Excel365 и современные формулы, не все из которых были доступны автору при написании книги. – Здесь и далее текст, набранный с отступом примечания Багузина.

Содержание

Азартные игры
Статистика
Генетика
Финансы
Эволюция
Метод Монте-Карло
Итерации
Дополнительно
Разное
Приложения

Часть 1. Азартные игры

Глава 1. Жребий брошен

Что делает симуляция. В ячейке A1 находится формула, которая генерит случайное число от 1 до 6. Грань кубика отражает соответствующее число глаз в нужных местах. При изменении числа в А1 изображение автоматически меняется.

Что нужно знать. Ячейка A1 содержит формулу: =СЛУЧМЕЖДУ(1;6). Чтобы получить новое случайное число, нажмите клавишу F9, либо Shift+F9. В первом случае под пересчет попадут все листы книги, во втором – только активный лист (это экономит время). Глаза прописываются в некоторых ячейках с помощью функции ЕСЛИ. Эта функция является «лицом, принимающим решение»: должен ли конкретный глаз быть включен или выключен.

Что нужно сделать. Введите формулы, как показано ниже:

Рис. 1. Шестигранный кубик

Глава 2. Шесть кубиков

Что делает симуляция. Теперь у вас 6 кубиков. Добавлена интерактивная столбчатая диаграмма и два вида подсветки, если выпадает одна шестерка или более.

Что вам нужно знать. Настройка аналогична предыдущему кейсу. Добавлено условное форматирование для подсветки кубиков.

Что нужно сделать. Во-первых, введите формулы в шести областях , аналогично предыдущему кейсу. Первая область – B1, B3:D7. Вторая область: F1, F3:H7. И т.д. Настройте условное форматирование. Выделите область В3:D7. Пройдите по меню Главная –> Условное форматирование –> Создать правило –> Использовать формулу для определения форматируемых ячеек. Введите формулу =$B$1=6. С двумя знаками равно она кажется странной. Однако формулу легко понять, если знать, что формула условного форматирования всегда должна возвращать значение ИСТИНА или ЛОЖЬ. Первый знак равно предваряет любую формулу. Далее следует выражение $B$1=6, которое возвращает ИСТИНА, если в $B$1 значение 6, и ЛОЖЬ, если иное значение. Форматироваться будут только ячейки, для которых выражение будет истинным.

Рис. 2. Настройка условного форматирования

Нажмите кнопку Формат. Перейдите на вкладку Заливка. Выберите цвет заливки ячеек:

Рис. 3. Формат ячеек

Аналогично настройте еще пять областей: F3:H7, J3:L7, …

Выделите область В3:Х7 и задайте для нее формулу условного форматирования =СЧЁТЕСЛИ($B$1:$V$1;6)>=2. Формула позволит окрасить в зеленый цвет область В3:Х7, если выпало две или более шестерок. Сделайте это правило первым:

Рис. 4. Порядок применения правил форматирования; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Правила применяются в указанном порядке. Если правило с зеленым цветом будет последним, то на картинке будет смешение: область В3:Х7 – зеленая, а поверх отдельные оранжевые квадраты.

Постройте диаграмму для ячеек, содержащих случайные числа:

Рис. 5. Диапазон данных для диаграммы

Обратите внимание, что диапазон данных содержит отдельные ячейки, а не весь ряд B1:V1.

Рис. 6. Примеры работы модели для наборов случайных чисел: а) нет шестерок, б) одна шестерка, в) две шестерки

Глава 3. Частоты

Что делает симуляция. На этот раз мы одновременно бросаем две кости и сумму отражаем в столбце C. Делаем 10 подходов. В столбце F подсчитываем, как часто у нас было на двух костях 2 очка, 3 очка и так далее. Частоты выводим на график. В ячейке F14 вычисляем среднее значение по столбцу C. Среднее значение выводим на график в виде вертикальной линии на основе координат в ячейках E16:F17. Экстремальные средние значения в ячейке F14 подсвечиваем.

Кривая меняется после каждого нажатия Shift+F9. Вид кривой не соответствует теоретическим вероятностям, из-за небольшого числа опытов.

Рис. 7. Теоретические вероятности исходов бросания двух кубиков

Что вам нужно знать. Для расчета значений в столбце F используется функция ЧАСТОТА(). Цвет ячейки F14 изменяется с помощью условного форматирования. Мы хотим подсветить средние значения ниже 5,5 и выше 8,5.

Что вам нужно сделать. В ячейке А2 введите формулу =СЛМАССИВ(10;2;1;6). Формула основана на новом свойстве динамических массивов. Формула создаст массив случайных чисел размером 10 строк на 2 столбца, от 1 до 6 из целых чисел (пятый аргумент ИСТИНА). Вы ввели формулу только в ячейку А2, а значения «разлились» на область А2:В11.

Рис. 8. Синтаксис функции СЛМАССИВ()

В ячейке С2 введите формулу =СУММ(A2:B2). Протащите ее до С11.

В ячейку F2 введите формулу =ЧАСТОТА(C2:C11;E2:E11). Обратите внимание, что для функции ЧАСТОТА() в первом аргументе – Массив_данных – нужно указать все данные (С2:С11). А вот во втором аргументе – Массив_интервалов – на один интервал меньше (Е2:Е11). Это связано с особенностями функции ЧАСТОТА. Она возвращает на одно значение больше, чем число интервалов. Последнее значение формируется для частоты значений больших, чем величина последнего указанного интервала. В нашем случае в ячейке F12 будет указано количество значений из диапазона С2:С11 больших чем 11.

Рис. 9. Работа модели

Функция ЧАСТОТА тоже использует свойства динамических массивов. Вы ввели ее только в ячейку F2, а она «разлилась» до F12.

Введите в ячейку F14 формулу =СРЗНАЧ(C2:C11). Выделите ячейку F14, пройдите по меню Главная –> Условное форматирование –> Создать правило –> Использовать формулу для определения форматируемых ячеек. Введите формулу =ИЛИ($F$14<5,5;$F$14>8,5). Она вернет значение ИСТИНА для средних значений менее 5,5 или более 8,5. Нажмите кнопку Формат. Перейдите на вкладку Шрифт. Выберите шрифт Cambria, начертание – полужирный курсив, цвет – синий. Перейдите на вкладку Заливка. Нажмите кнопку Способы заливки. Сделайте настройки:

Рис. 10. Настройки заливки условного форматирования

Средняя линия на графике основана на новой серии значений с двумя наборами координат: E16:E17 для значений X и F16:F17 для значений Y (подробнее см. Как добавить линию на гистограмму). Выделите ячейки Е16:F17. Скопируйте в буфер памяти. Выделите диаграмму. Пройдите по меню Главная –> Вставить –> Специальная вставка. В появившемся окне установите Добавить значение как в положение новые ряды, Значения Y – в положение в столбцах; поставьте галочку Категории (подписи оси Х) в первом столбце.

Рис. 11. Специальная вставка

Нажимайте Shift+F9. Следите за изменением графика.

Глава 4. Рулетка

Что делает симуляция. Показывает, насколько хорошо вы можете предсказывать выпадение четных или нечетных чисел рулетки. В диапазоне А2:А21 находятся случайные числа от 1 до 36. Видно только первое число, остальные скрыты условным форматированием. В ячейке B2 введите 1, если ожидаете, что следующее число будет нечетным. Если ожидаете четное, введите 0. В столбце E подсчитывается результат вашего прогноза накопительным итогом. Если вы угадали, добавляется 1, если вы  не угадали вычитается 1. Выполните 19 угадываний. Если вы хотите повторить эксперимент, удалите значения из ячеек В2:В20, и начните снова с В2.

Что вам нужно знать. Многие люди считают, что выгодно постоянно делать ставки на нечет. Попробуйте такую стратегию в «реальной жизни» (на этом симуляторе) и увидите, как казино зарабатывает деньги на людях, которые так думают.

Что вам нужно сделать. В ячейке А2 введите формулу: =СЛМАССИВ(20;;1;36;ИСТИНА). Она сгенерит вертикальный массив из 20 случайных целых чисел от 1 до 36. Введите в ячейку Е2 формулу: =ЕСЛИ(ОСТАТ(A3;2)=B2;E1+1;E1-1). Протяните формулу до Е20. Функция ОСТАТ() возвращает остаток от деления на 2 выпавшего значения рулетки. Остаток равен 0 или 1. Далее это значение сравнивается с предсказанием в столбце B. Если остаток и предсказание совпали сумма в столбце Е увеличивается на 1, если не совпали – уменьшается на 1.

Выделите диапазон А3:A21, и примените к нему условное форматирование на основе формулы =B2="". Задайте форматирование в виде белого шрифта. Пока не введено очередное предсказание, следующие значения в столбце А остаются невидимыми.

Выделите диапазон B2:B20, и ведите для него проверку значений. Для этого пройдите по меню Данные –> Работа с данными –> Проверка данных. В открывшемся окне настройке проверку:

Рис. 12. Проверка данных

После такой настройки вы сможете ввести в ячейки B2:B20 только 0 или 1.

Теперь вы можете последовательно вводить 0 и 1 в столбец В. Чтобы начать все сначала, удалите значения из столбца В. Как вариант, можете набрать все 0 или все 1, и посмотреть, что получится.

Рис. 13. Пример угадывания

Глава 5. Разорение игрока

Что делает симуляция. Имитирует, что может произойти с людьми, которые увлекаются азартными играми. У игрока есть 100 шансов (в столбце А) сыграть на чет/нечет с вероятностью 50:50. Если «угадал», сумма в столбце В увеличивается на 1, не угадал – уменьшается на 1. Затем мы имитируем, что таких серий (по 100 игр) было 20. Для каждой серии рассчитываем среднее, минимальное, максимальное, стандартное отклонение и итоговую сумму. Наконец, подсчитываем, сколько раз игрок закончил игру в плюсе и в минусе.

Что вам нужно знать. В ячейке А1 мы используем новую функцию динамических массивов СЛМАССИВ(). В диапазоне D2:H22 мы имитируем 20 серий. Для этого используем инструмент Таблицы данных. Это идеальный инструмент Excel для анализа «что, если» (к сожалению, он интуитивно не понятен, из-за чего его используют довольно редко). В нашем случае мы вообще используем недокументированные свойства этого инструмента!

Что вам нужно сделать. Введите в ячейку A2 формулу: =СЛМАССИВ(100;;0;1;ИСТИНА). В ячейку В1: =ЕСЛИ(A1=0;-1;1), В2: =B1+ЕСЛИ(A2=0;-1;1); протащите её до В100. D2: =СРЗНАЧ(B:B), Е2: =МИН(B:B), F2: =МАКС(B:B), G2: =СТАНДОТКЛОН.В(B:B), H2: =B100.

Постройте Таблицу данных. Для этого выделите диапазон C2:H22. Да, пустые ячейки С2:С22 тоже надо включить! Пройдите по меню: Данные –> Анализ "что, если" –> Таблица данных. Настройте таблицу данных. Оставьте поле Подставлять значения по столбцам в: пустым, а в поле Подставлять значения по строкам в: щелкните на любую пустую ячейку, например J2. Нажмите Ok.

Рис. 14. Настройка таблицы данных

Встаньте на любую ячейку в диапазоне D3:Н22. В строке формул вы увидите формулу массива {=ТАБЛИЦА(;J2)}. Обычно Таблицы данных используют иначе. Сначала организуют последовательность вычислений для одного значения параметра, а потом изучают влияние изменения значения параметра на результат. В нашем случае мы используем таблицу «что, если» просто для того, чтобы обмануть Excel, имитируя 20 итераций набора из 100 случайных значений в столбце A.

Добавьте условное форматирование для диапазона С2:Н22. Выделите этот диапазон и пройдите по меню Главная –> Условное форматирование –> Создать правило –> Форматировать все ячейки на основании их значений. Выберите синюю градиентную заливку, а для отрицательных значений – красную.

В ячейке Н24 введите формулу: =СЧЁТЕСЛИ(H2:H22;">0"), Н25: =СЧЁТЕСЛИ(H2:H22;"<0"). После каждого нажатия Shift+F9 картинка будет меняться:

Рис. 15. Симуляция успешности игрока

Глава 6. Случайное блуждание

Что делает симуляция. Альтернатива предыдущему кейсу – случайное блуждание. Когда мы выходим из дома (позиция 0), мы подбрасываем монету. Орел – идем на один квартал на север (позиция +1). Решка – идем на один квартал на юг (позиция –1). Как далеко мы окажемся от дома после большого числа бросаний? Мы также можем спросить, какова вероятность того, что мы вернемся туда, откуда начали? Для очень большого числа бросаний с некоторой точностью мы всегда вернемся домой.

Мы смоделируем первые 50 шагов сначала для одного (столбец В), а затем для двух измерений (столбцы В и С). Некоторые «прогулки» могут завести нас далеко от дома. Но это обманчивая ситуация. Повторив симуляцию много раз, мы увидим, что среднее стремится к нулю.

Что вам нужно знать. Мы используем несколько новых функций. Сначала найдем, в какой строке столбца B мы впервые вернулись в позицию 0. Для этого используем функцию ПОИСКПОЗ(). Если такого возврата за время блуждания не случилось функция вернет ошибку. Чтобы избежать этого, мы поместим ПОИСКПОЗ() внутрь функции ЕСЛИОШИБКА(). Второй аргумент позволит указать значение, которое следует вернуть при возникновении ошибки.

Что вам нужно сделать. Введите в ячейке B3: =ЕСЛИ(СЛЧИС()<0,5;B2-1;B2+1). Протащите формулу до В52. Функция СЛЧИС() возвращает десятичное число от 0 до 1. Вероятности для диапазонов (0;0,5) и (0,5;1) равны. Мы имитируем подбрасывание монетки. СЛЧИС() определяет, пойдем ли мы по вниз или вверх. На основании значений в диапазоне В3:В52 строится верхний график.

В ячейке N1 мы показываем, как часто случайное блуждание возвращало нас к нулю. В N1 введите: =СЧЁТЕСЛИ(B3:B52;0)&" раз вернулись к нулю". Амперсанд (&) – оператор конкатенации (связывания), двух текстовых строк.

В ячейке N3 вычисляем, на каком шаге симуляции мы вернулись к 0 в первый раз: ="Первый возврат к 0 на шаге "&ЕСЛИОШИБКА(ПОИСКПОЗ(0;B3:B52;0);" -").

Рис. 16. Случайное блуждание

Для двумерного блуждания в ячейку C3 введите =ЕСЛИ(СЛЧИС()<0,5;C2-1;C2+1). Скопируйте формулу до С52. Нижний график заполнится случайными блужданиями. Начальная и конечная точки выделены.

В ячейках О5 и Р5 отражаются координаты конечной точки блуждания. Выберите диапазон N5:P18 и пройдите по меню Данные –> Анализ "что, если" –> Таблица данных. Оставьте поле Подставлять значения по столбцам в: пустым, а в поле Подставлять значения по строкам в: щелкните на любую пустую ячейку, например R5. Это создает в диапазоне О6:Р18 формулу массива: {=ТАБЛИЦА(;R5)}.

В ячейке P20 подсчитаем, как часто мы финишировали в точке (0;0). Т.е., сколько таких значений в Таблице данных: =СЧЁТЕСЛИМН(O6:O18;0;P6:P18;0).

В ячейке P21 подсчитаем, как часто обе координаты конечной точки были отрицательными: =СЧЁТЕСЛИМН(O6:O18;"<0";P6:P18;"<0")

В P22 подсчитаем, как часто одна координата конечной точки была отрицательной: =СЧЁТЕСЛИМН(O6:O18;"<0";P6:P18;">0")+СЧЁТЕСЛИМН(O6:O18;">0";P6:P18;"<0")

Условным форматированием, сделайте диапазон B2:B52 красным, если конечная точка меньше нуля: =$B$52<0.

Условным форматированием, сделайте диапазон B2:C52 желтым, если мы вернулись в точку (0;0): =И($B$52=0;$C$52=0).

 

Продолжение следует

Добавить комментарий

Ваш адрес email не будет опубликован.