Реализация метода Монте-Карло

(Применение табличных процессоров в практической деятельности судебных экспертов; Глава 2. Другие примеры использования электронных таблиц)

На предыдущих примерах можно было убедиться, что табличные процессоры хорошо знакомы с математическим аппаратом. Многообразие заложенного в них функционала очень велико, что подразумевает широчайший круг потенциальных возможностей применения электронных таблиц. Так, одна только функция для вычисления значения определителя матрицы (детерминанта)

МОПРЕД( )

MDETERM( )

позволяет реализовать программу для решения систем линейных алгебраических уравнений по методу Крамера.

Поскольку экспертная работа базируется на научном подходе, то ей отнюдь не чужды такие вещи как моделирование, использование численных методов в расчётах и т. п. Поэтому в качестве последней темы, освещаемой в этом пособии, мне хотелось бы рассказать про реализацию любопытного способа выполнения вычислений, который называется методом Монте-Карло – ниже будет рассмотрено определение с помощью него приближённого значения числа π.

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

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

Во-вторых, определение числа π методом Монте-Карло – хрестоматийный пример, так как встречается в школьных учебниках по информатике (Угринович Н.Д. Информатика и информационные технологии: Учебник для 10-11 классов. М.: БИНОМ. Лаборатория знаний. 2003. 512 с.), однако там обычно решение этой задачи реализуется в виде программы, написанной на рассматриваемом в данном учебнике языке программирования. И вот здесь-то будет совершенно уместным вспомнить, что в среде электронных таблиц осуществимо «программирование без программирования», поэтому можно будет обойтись при помощи одних формул.

Итак, рассмотрим окружность единичного радиуса, центр которой находится в начале координат. Уравнение такой окружности имеет вид:
x² + y² = 1 , а площадь круга, ей ограничиваемого, равна Sₒ = πR² = π·1² = π. Опишем вокруг этой окружности квадрат:

Так как сторона его равна 2, то площадь составит S = 2² = 4. Теперь будем случайным образом размещать внутри этого квадрата точки. Вероятность p того, что точка окажется внутри окружности, равна:

p = Sₒ / S

Если же исходить из статистического определения вероятности, то при достаточно большом числе испытаний

p ≈ Nₒ / N

где N – общее число испытаний, Nₒ – число испытаний, когда точка оказалась внутри окружности. Если приравнять правые части приведённых выражений, то из них можно получить, что

π ≈ 4Nₒ / N

Для определения числа π рассматриваемым методом необходимо ввести нужные формулы в лист электронной таблицы. Программа для этого выглядит так:

Ячейки в столбцах “В” и “Е” содержат формулу:

=СЛЧИС()*2-1

=RAND()*2-1

Использованная в ней функция

СЛЧИС()

RAND()

не имеет аргументов и генерирует случайное число с равномерным распределением, принимающее значение в интервале от 0 до 1. Так как нам требуется разместить случайным образом точку внутри квадрата со стороной 2 и центром в начале координат, то координаты х и у такой точки должны находиться в интервале (–1; 1). Умножение на 2 случайного числа, принимающего значения от 0 до 1, даёт случайную величину, которая будет принимать значения от 0 до 2, а дополнительное вычитание единицы как раз и позволяет добиться требуемого.

Хочется обратить внимание на особенность рассматриваемой функции. При любом изменении данных в листе (даже при простом нажатии [Delete], когда выделена заведомо пустая ячейка) она каждый раз выдаёт новое случайное значение.

Ячейки в столбце “G” содержат следующее (показано на примере “G1”):

=ЕСЛИ(B1^2+E1^2<=1;1;0)

=IF(B1^2+E1^2<=1;1;0)

– в них проверяется, попадает ли случайная точка внутрь круга, то есть удовлетворяют ли её координаты условию x² + y² ≤ 1 . Если да, то в ячейку помещается число 1, а если нет – 0.

Указанные формулы при помощи маркера заполнения размножены до 1000-й строки. Значение π вычисляется в ячейке “J1”по формуле:

=4*СУММ(G1:G1000)/1000

=4*SUM(G1:G1000)/1000

Входящая в неё функция

СУММ(G1:G1000)

SUM(G1:G1000)

суммирует все числа в указанном ей диапазоне "G1:G1000", а поскольку попадание точки внутрь круга обозначается единицей, а непопадание – нулём, то данная функция фактически подсчитывает общее число попаданий точки внутрь круга после тысячи испытаний.

Формула в ячейке “J2” для иллюстрации точности определения π вычисляет относительную погрешность в процентах:

=ABS(J1/ПИ()-1)*100

=ABS(J1/PI()-1)*100

Функция ABS( ) в ней возвращает модуль (абсолютное значение) своего аргумента, а функция

ПИ()

PI()

возвращает заложенное в табличный процессор приближённое значение π с точностью до 14-гознака после запятой: 3,14159265358979.

В завершение можно отметить, что 1000 испытаний – не так уж много для метода Монте-Карло по причине его вероятностного характера, поэтому высокой точности в этом случае ожидать не стоит – не удивляйтесь, если рассчитанное значение π окажется равным 3,2 или 2,9.

* * *

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

Хочется надеяться, что даже не смотря на относительно малый объём этого пособия, главная цель его создания всё-таки достигнута, а именно наглядно показана возможность применения табличных процессоров как мощного многогранного инструмента в арсенале используемых судебными экспертами средств при осуществлении их профессиональной деятельности.

Рекомендуемая литература:

1. Угринович Н.Д. Информатика и ИКТ. Базовый курс: Учебник для 8 класса. – 3‑е изд., испр. – М.: БИНОМ. Лаборатория знаний. 2005. 205 с.
2. Семакин И.Г., Хеннер Е.К. Информатика. 11-й класс. – 3-е изд., испр. – М.: БИНОМ. Лаборатория знаний. 2005. 135 с.
3. Кузьмин В. Microsoft Office Excel 2003. Учебный курс. СПб.: Питер. 2004. 493 с.
4. Пащенко И.Г. Excel 2007. Шаг за шагом. М.: Эксмо-Пресс. 2007. 351 с.
5. Уокенбах Дж. Microsoft Office Excel 2007. Библия пользователя. М.: Вильямc. 2008. 816 с.
6. Гладкий А.А., Чиртик А.А. Excel 2007. Трюки и эффекты. СПб.: Питер. 2007. 368 с.
7. Ковригина Е.В. Создание и редактирование электронных таблиц в среде OpenOffice.org: Учебное пособие. М.: 2008. 85 с.
8. Костромин В.А. OpenOffice.org – открытый офис для Linux и Windows. СПб.: БХВ-Петербург. 2005. 272 с.
9. Технология работы в LibreOffice: текстовый процессор Writer, табличный процессор Calc: практикум / авт.-сост. В.А. Павлушина; Ряз. гос. ун-т им. С.А. Есенина. Рязань: 2012. 80 с.

Файлы с примерами: xls / ods

◁◁◁ Построение кривых распределения (сортировка данных)

Оглавление пособия