Использование метода монте-карло для расчета риска. Создать симуляцию Монте-Карло с помощью Excel

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

Моделирование Монте-Карло

Метод Монте-Карло был изобретен Николаем Метрополисом в 1947 году и направлен на решение сложных проблем с использованием случайных и вероятностных методов. Термин «Монте-Карло» происходит от административного района Монако, широко известного как место, где европейские элиты играют в азартные игры. Мы используем метод Монте-Карло, когда проблема слишком сложна и сложна при непосредственном вычислении. Большое количество итераций позволяет моделировать нормальное распределение.

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

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

Игра в кости

Вот как игра в кости играется:

Игрок бросает три кости, которые имеют 6 сторон 3 раза.

Если общее количество 3 бросков составляет 7 или 11, игрок выигрывает.

Если общее количество 3 бросков: 3, 4, 5, 16, 17 или 18, проигрыватель проигрывает.

Если общий результат - любой другой результат, игрок снова играет и повторно свертывает штамп.

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

Шаг 1: События прокатки в кости

Сначала мы разрабатываем ряд данных с результатами каждого из 3 кубиков для 50 рулонов. Для этого предлагается использовать функцию «RANDBETWEEN (1. 6)». Таким образом, каждый раз, когда мы нажимаем F9, мы генерируем новый набор результатов каротажа. Ячейка «Результат» - это сумма итогов трех рулонов.

Шаг 2: Диапазон результатов

Затем нам нужно разработать ряд данных для определения возможных результатов для первого раунда и последующих раундов. Ниже приведен диапазон данных с тремя столбцами.В первом столбце у нас есть числа от 1 до 18. Эти цифры представляют собой возможные результаты после того, как катятся кости 3 раза: максимум составляет 3 * 6 = 18. Вы заметите, что для ячеек 1 и 2 результаты N / A, так как невозможно получить 1 или 2, используя 3 кости. Минимальное значение равно 3.

Во втором столбце включены возможные выводы после первого раунда. Как указано в первоначальном заявлении, либо игрок выигрывает (выигрывает), либо проигрывает (проигрывает), либо повторяет его (Re-roll), в зависимости от результата (всего 3 кубика).

В третьей колонке регистрируются возможные выводы для последующих раундов. Мы можем достичь этих результатов, используя функцию «If. «Это гарантирует, что если полученный результат будет эквивалентен результату, полученному в первом раунде, мы выиграем, иначе мы будем следовать первоначальным правилам первоначальной игры, чтобы определить, будем ли мы повторно бросать кости.

Шаг 3: Выводы

На этом этапе мы определяем результат 50 кубиков. Первый вывод можно получить с помощью индексной функции. Эта функция выполняет поиск возможных результатов первого раунда, вывод, соответствующий полученному результату. Например, при получении 6, как это имеет место на рисунке ниже, мы снова играем.

Можно получить результаты других рулонов кости, используя функцию «Or» и функцию индекса, вложенную в функцию «If». Эта функция сообщает Excel: «Если предыдущий результат -« Выиграть или проиграть », перестаньте бросать кости, потому что как только мы выиграли или проиграли, мы закончили. В противном случае мы переходим к столбцу следующих возможных выводов, и мы определяем вывод результата.

Шаг 4: Количество рулонов кости

Теперь мы определяем количество бросков кубиков, необходимых до проигрыша или выигрыша. Для этого мы можем использовать функцию «Countif», которая требует, чтобы Excel подсчитывал результаты «Re-Roll» и добавлял номер 1 к ней. Он добавляет один, потому что у нас есть один дополнительный раунд, и мы получаем окончательный результат (выигрываем или проигрываем).

Шаг 5: Моделирование

Мы разрабатываем диапазон для отслеживания результатов различных симуляций. Для этого мы создадим три столбца. В первом столбце одна из приведенных цифр - 5 000. Во второй колонке мы будем искать результат после 50 кубиков. В третьем столбце, в заголовке столбца, мы будем искать количество бросков кубиков, прежде чем получить окончательный статус (выиграть или проиграть).

Затем мы создадим таблицу анализа чувствительности с использованием данных характеристик или таблицы данных таблицы (эта чувствительность будет вставлена во вторую таблицу и в третьи столбцы). В этом анализе чувствительности номера событий 1 - 5, 000 должны быть вставлены в ячейку A1 файла. Фактически, можно было выбрать любую пустую ячейку. Идея состоит в том, чтобы просто произвести перерасчет каждый раз и таким образом получить новые броски кубиков (результаты новых симуляций), не повредив формулы на месте.

Шаг 6: Вероятность

Мы можем, наконец, вычислить вероятности выигрыша и проигрыша. Мы делаем это с помощью функции «Countif».Формула подсчитывает количество «выигрышей» и «проиграет», а затем делит на общее количество событий, 5, 000, чтобы получить соответствующую долю одного и другого. Наконец, мы видим, что вероятность получить выигрыш составляет 73. 2%, а результат Lose - 26,8%.

Любая инвестиция нуждается в тщательных расчетах. Иначе инвестор рискует потерять вложенные средства.

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

Расчет инвестиционного проекта в Excel

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

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

Итак, у нас есть идея открыть небольшой магазин. Определимся с затратами. Они бывают

постоянными (нельзя рассчитать на единицу товара);
переменными (можно рассчитать на единицу товара).

Первоначальные вложения – 300 000 рублей. Деньги расходуются на оформление предпринимательства, оборудование помещения, закупку первой партии товара и т.д.

Составляем таблицу с постоянными затратами:

* Статьи расходов индивидуальны. Но принцип составления - понятен.

По такому же принципу составляем отдельно таблицу с переменными затратами:

Для нахождения цены продажи использовали формулу: =B4*(1+C4/100).

Следующий этап – прогнозируем объем продаж, выручку и прибыль. Это самый ответственный этап при составлении инвестиционного проекта.

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

Для подсчета выручки использовалась формула: =СУММПРОИЗВ(B3:B6;Лист2!$D$4:$D$7). Где первый массив – объемы продаж; второй массив – цены реализации.

Выручка минус переменные затраты: =B7-СУММПРОИЗВ(B3:B6;Лист2!$B$4:$B$7).

Прибыль до уплаты налогов: =B8-Лист1!$B$14 (выручка без переменных и постоянных затрат).

Налоги ЕНВД: =Лист1!A10*1800*0,15*3 (1800 – базовая доходность по виду деятельности, 3 – количество месяцев, С12 – площадь помещения).

Чистая прибыль: прибыль – налоги.

Оценка инвестиционного проекта в Excel

Рассчитывают 4 основных показателя:

чистый приведенный эффект (ЧПЭ, NPV);
индекс рентабельности инвестиций (ИРИ, PI);
внутреннюю норму доходности (ВНД, IRR);
дисконтированный срок окупаемости (ДСО, DPP).

Для примера возьмем следующий вариант инвестиций:

Сначала дисконтируем каждый положительный элемент денежного потока.

Создадим новый столбец. Введем формулу вида: = положительный элемент денежного потока / (1 + ставка дисконтирования)^ степень, равная периоду.

Теперь рассчитаем чистый приведенный эффект:

Найдем индекс рентабельности инвестиций. Для этого нужно разделить чистую приведенную стоимость (ЧПС) на объем инвестированных средств (со знаком «+»):

Результат – 1,90.

Посчитаем IRR инвестиционного проекта в Excel. Напомним формулу:

ВНД = ΣДП t / (1 + ВНР) t = И.

ДП t – положительные элементы денежного потока, которые нужно продисконтировать по такой ставке, чтобы чистый приведенный эффект равнялся нулю. Внутренняя норма доходности – такая ставка дисконтирования, при которой выпадает равенство вида:

ΣДП t / (1 + ВНР) t – И = 0,

Воспользуемся инструментом «Анализ «Что-Если»»:

Ставка дисконтирования равняется 0,41. Следовательно, внутренняя норма доходности составила 41%.

Моделирование рисков инвестиционных проектов в Excel

Используем метод имитационного моделирования Монте-Карло. Задача – воспроизвести развитие бизнеса на основе результатов анализа известных элементов и взаимосвязей между ними.

Продемонстрируем моделирование рисков на простейшем примере. Составим условный шаблон с данными:

Ячейки, которые содержат формулы ниже подписаны своими значениями соответственно.

Прогнозируемые показатели – цена услуги и количество пользователей. Под этими данными делаем запись «Результаты имитации». На вкладке «Данные» нажимаем «Анализ данных» (если там нет инструмента придется подключить настройку). В открывшемся окне выбираем «Генерация случайных чисел».

Заполняем параметры следующим образом:

Нам нужно смоделировать ситуацию на основе распределений разного типа.

Для генерации количества пользователей воспользуемся функцией СЛУЧМЕЖДУ. Нижняя граница (при самом плохом варианте событий) – 1 пользователь. Верхняя граница (при самом хорошем варианте развития бизнеса) – 50 покупателей услуги.

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

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

Программа выдает результат (по столбцу «Коэффициент эффективности»):

Можно делать выводы и принимать окончательное решение.

ЗАКАЗАТЬ РЕШЕНИЕ ЗАДАЧ МЕТОДОМ МОНТЕ-КАРЛО
Один из самых прикладных методов статистической оценки риска. К нему нужно отнестись с большим участием. В данной статье будет рассмотрен пример имитационного моделирования с использованием данного подхода.

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

В процессе работы нам понадобится «генератор случайных чисел» из MS Excel и функция «Описательная статистика».

Оценка риска инвестиционного проекта

Есть следующие условия задачи:

Таким образом, нам нужно оценить три периода – за три года. Запишем все исходные данные в таблицу. Значения, полученные в ячейках D5-X5, имеют формулу для вычисления или есть в условиях задачи. Вы, как экономист, с формулами должны быть знакомы. Обратите внимание на заголовок, выделенный красным цветом на рисунке ниже – «Имитационная модель NCF1». Это говорит о том, что мы имитируем первый год, а всего их будет три на разных листах в MS Excel. На новый лист переключиться внизу окна программы.

Теперь в MS Excel переключаемся на «Данные» и выбираем пункт «Анализ данных».

В появившемся окне выбираем «Генерация случайных чисел». Выполняем генерацию с параметрами, продемонстрированными на картинке ниже, для пункта «Кол-во пользователей».

Параметры будут отталкиваться от среднего значения 250, оно есть в ожидаемых значениях в нашей таблице. Нужно выполнить 1000 генераций. Если вы знакомы со статистикой, то понимаете, что большее количество испытаний даёт более точную оценку. Используя метод Монте-Карло, можно имитировать и 10 000 значений для большей точности.

После мы имитируем все стохастические, то есть, меняющиеся значения по аналогии, как показано выше. Копируем формулы переменных или констант из ячеек D7-X7 под «Результаты имитации» с учетом имитированных значений. Получаем следующий результат.

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

Теперь делаем то же самое, но для имитационной модели NCF2.

Это второй год работы проекта. Как видим, под «СКО» процентные соотношения увеличились. Об этом говорится в условии задачи, что налоги и зарплата должны расти каждый год.

Повторяем это действие в третий раз, увеличивая налоги и зарплаты, как говорит условие.

Наибольшую важность в оценке инвестиционного проекта имеет параметр NCF – чистый денежный поток. Копируем все значения NCF на четвертый лист с каждой из трёх предыдущих страниц.

Формула для расчета NPV есть вверху картинки. Используем её. Теперь точно так же заходим в «Данные», жмём на «Анализ данных» и выбираем там «Описательная статистика». Вот, что в появившемся окне вам нужно указать.

Во входном интервале выбирается 1000 полученных значений NPV. Выходной интервал можете выбрать произвольно. На выходе у вас будет таблица со статистическими данными.

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

Заключение

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

Существует немало программ для моделирования методом Монте-Карло. С их обзором можно ознакомиться, например, в книге

Инструмент	Кем разработан	Описание
@Risk	Palisade Corporation, Итака, штат Нью-Йорк	Достаточно совершенный инструмент для работы на основе Excel; описывает большое число распределений; широкая база пользователей, предоставляется техническая поддержка
AIE	Hubbard Decision Research, Глен-Эллин, штат Иллинойс	Набор макросов на основе Excel; также позволяет рассчитывать стоимость информации и оптимальный портфель; подчеркивает приоритетность методологии над инструментарием; предоставляются консалтинговые услуги по практическим вопросам внедрения
Crystal Ball	Decisioneering, Inc, Денвер, штат Колорадо	Еще один инструмент на базе Excel. Продукт, успешно конкурирующий с @Risk. Много пользователей, предоставляется техническая поддержка
Risk Solver Engine	Frontline Systems, Инклин-Вилладж, штат Невада	Уникальная платформа разработки на базе Excel, позволяющая выполнять моделирование методом Монте-Карло с беспрецедентной скоростью. Поддерживает форматы SIP и SLURPs, необходимые для управления вероятностями
SAS	SAS Corporation, Роли, штат Северная Каролина	Пакет программ высшей степени сложности, используемый многими профессиональными статистиками и далеко выходящий за рамки метода Монте-Карло
SPSS	SPSS Inc., Чикаго, штат Иллинойс	Также выходит за пределы метода Монте-Карло; весьма популярен среди ученых
XLSim	Профессор Стэнфордского университета Сэм Сэвидж, AnalyCorp	Недорогой пакет программ, предназначенный для легкого изучения, удобен в применении. Сэвидж проводит в организациях семинары по методу Монте-Карло

Книга написана американским автором и вышла в США в 2007 г. Программа Crystal Ball, упомянутая в таблице сейчас принадлежит уже Oracle . Демо-версия программы доступна для скачивания с сайта компании. Описание базовых функциональных возможностей Crystal Ball я нашел на сайте Финансовое моделирование, бюджетирование, планирование .

Скачайте и установите Crystal Ball на ПК. Прежде чем запустить программу закройте все окна Excel. Запустите Crystal Ball. Сначала откроется Excel, а затем в нем появится закладка Crystal Ball (рис. 1).

Рис. 1. Запуск Crystal Ball сначала открывает Excel, а затем появляется закладка Crystal Ball

Воспользуемся примером Хаббарда, рассмотренным , и на его основе изучим основы работы в программе Crystal Ball.

Предположим, что вы хотите арендовать новый станок. Стоимость годовой аренды станка 400 000 дол., и договор нужно подписать на несколько лет. Поэтому, даже не достигнув , вы всё равно не сможете сразу вернуть станок. Вы собираетесь подписать договор, думая, что современное оборудование позволит сэкономить на трудозатратах и стоимости сырья и материалов, а также считаете, что материально-техническое обслуживание нового станка обойдется дешевле.

Ваши калиброванные специалисты по оценке дали следующие интервалы значений ожидаемой экономии и годового объема производства (в таблице приведены 90%-ные доверительные интервалы):

Шаг. 1. Формирование модели. Разместим исходные данные на листе Excel. Они будут включать названия параметров и их средние значения, а также формулу для расчета годовой экономии (рис. 2)

Рис. 2. Исходные данные

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

Шаг. 2. Задание параметров распределения влияющих переменных. Встаньте в ячейку В2 и на вкладке Crystal Ball щелкните Define Assumption. В открывшемся окне выберите Normal и нажмите Ok

Рис. 3. Выбор нормального распределения для первого параметра «Экономия на материально-техническом обслуживании»

Задайте среднее значение – Mean и стандартное отклонение – Std. Dev. (рис. 4). Поскольку исходные данные сформулированы в терминах 90%-ного доверительного интервала (CI), формулы для расчета следующие:

Среднее (Mean) = (Верхняя граница 90%-ного CI + Нижняя граница 90%-ного С I)/2;

Стандартное отклонение (Std. Dev.) = (Верхняя граница 90%-ного CI – Нижняя граница 90%-ного С I)/3,29

а наша таблица, приспособленная для работы в Crystal Ball примет вид:

Параметр	Границы 90%-ного доверительного интервала	Среднее	Стандартное отклонение
экономия на материально-техническом обслуживании	от 10 до 20 дол. на единицу продукции	15	3,04
экономия на трудозатратах	от «–2» до 8 дол. на единицу продукции	3	3,04
экономия на сырье и материалах	от 3 до 9 дол. на единицу продукции	6	1,82
объем производства	от 15 000 до 35 000 единиц продукции в год	25 000	6 079
годовая экономия	(MS + LS + RMS) х PL

Рис. 4. Выбор параметров нормального распределения

Последовательно вставая курсором в ячейки В3:В5 выберите вид и параметры распределения для всех четырех влияющих переменных. После задания параметров ячейки окрашиваются в зеленый цвет.

Шаг 3. Выбор зависимой переменной. Встаньте в ячейку В6, содержащую формулу расчета годовой экономии, и щелкните Define Forecast. В открывшемся окне в поле «Units» укажите ссылку на ячейку (рис. 5).

Рис. 5. Выбор зависимой переменной

Шаг. 4. Выбор условий моделирования. Этот шаг не является обязательным, так как система предложит параметры моделирования по умолчанию. Учитывая, что наша модель довольно простая, можно увеличить число итераций (по умолчанию оно равно 1000). Щелкните Run Preferences, и выберите 10 000 (рис. 6). Чем больше итераций, тем надежней результаты моделирования!

Рис. 6. Выбор числа итераций

Шаг. 5. Запуск моделирования. Щелкните Start, и наслаждайте результатом вашего первого моделирования в Crystal Ball 🙂 После 10 000 итераций программа выведет результаты в графическом виде (рис. 7).

Рис. 7. Результаты моделирования – распределение годовой экономии

В будущем вы всегда можете увидеть результаты моделирования, если щелкните View Charts (рис. 8)

Рис. 8. Вывод диаграммы с результатами моделирования на экран монитора

Вы также можете создать отчет о моделировании (в отдельном файле Excel), если щелкните на Create Report (рис. 9).

Рис. 9. Фрагмент отчета.

Обратите внимание на величину стандартного отклонения прогнозного значения «Годовая экономия». Вспомним, что среднее значение и стандартное отклонение однозначно задают верхнюю и нижнюю границы 90%-ного доверительного интервала, и вычислим эти границы:

Нижняя граница = среднее – стандартное отклонение * 3,29 / 2 = 600 127 – 189 495 * 3,29 /2 = 288 408

Верхняя граница = среднее + стандартное отклонение * 3,29 / 2 = 600 127 + 189 495 * 3,29 /2 = 911 846

Видно, что не весь 90%-ный доверительный интервал «Годовой экономии» превышает точку безубыточности – 400 000 долл. То есть, существует вероятность того, что точка безубыточности достигнута не будет…

Заметим, что моделирование в Crystal Ball дало те же результаты, что и моделирование в Excel с помощью функции СЛЧИС (рис. 10).

Рис. 10. Результаты моделирования в Excel с помощью функции СЛЧИС

См. главу 5 упоминавшейся книги Дугласа Хаббарда

Цели:

образовательные: изучение численного метода Монте–Карло.

развивающие:

научить анализировать при нахождении общего, частного в понятиях информатики и ЭТ;
научить рассуждать;
составлять алгоритм задач;
уметь составлять формулы.

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

Ход урока

I. Оргмомент.

Цель нашего урока – это знакомство с функцией случайного числа и применением метода Монте–Карло в электронных таблицах.

II. Усвоение новых знаний.

В математике для решения задач часто требуются математические модели. Одна из таких задач – вычисление площадей. Конечно для простейших фигур (прямоугольников, многоугольников, кругов) вычисление площади не составляет труда: надо в известные формулы подставить исходные данные. А как быть если фигура имеет сложные формы? Итак, задача: Дана фигура сложной формы. Вычислить её площадь.

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

Можно придумать “физическую” модель, скопировать фигуру на картон, аккуратно вырезать её, взвесить и поделить на вес единичного квадрата из этого же картона.

В 11-м классе вы познакомитесь ещё с одним способом нахождения площадей фигур: с помощью интегралов.

Однако все эти модели трудно поддаются расчётам на ЭВМ. Мы попробуем построить математическую модель, которая позволит эффективно применять ЭВМ для решения задач на нахождение площадей, объемов и тому подобное.

Поместим данную фигуру в квадрат. Будем наугад (как говорят математики, случайным образом) бросать точки в этот квадрат. Естественно, что чем больше площадь фигуры, тем чаще в неё будут попадать точки. Представьте себе квадратный дворик и в нем детскую круглую площадку. Каждому ясно, что во время снегопада количество снежинок, попавших на детскую площадку, пропорционально её площади. Таким образом, можно сделать допущение: при большом числе точек, наугад выбранных внутри квадрата, доля точек, содержащихся в данной фигуре, приближенно равна отношению площади квадрата.

Такой метод приближенного нахождения площадей фигур носит название метода Монте–Карло (по названию города, где расположена знаменитая рулетка, которую можно рассматривать как “генератор” случайных чисел).

Только случайность поможет нам найти площадь фигуры методом Монте–Карло.

В Exсel имеется возможность проводить моделирование с использованием случайных чисел.

Функция СЛЧИС() (без аргументов) генерирует случайное число в диапазоне от 0 до 1. Совокупность этих чисел равномерно распределена на отрезке . При нажатии функциональной клавиши F9 (пересчет) в ячейках, содержащих формулу с функцией СЛЧИС , генерируется новое случайное число.

Показываю на ЭВМ (увеличив размер шрифта).

Вводим в ячейку формулу =СЛЧИС() и нажимаю F9 . В ячейках изменяется выводимое число.

Вопрос: Как изменить формулу, чтобы диапазон расширился от 0 до 10?

Ответ: Нужно умножить на 10, то есть =СЛЧИС()*10 .

Вопрос: Как изменить формулу, чтобы диапазон расширился от 2 до 3?

Ответ: Нужно сложить с числом 2, то есть =СЛЧИС()+2 .

Вопрос: Как изменить формулу, чтобы диапазон лежал на промежутке ?

Ответ: =(10–5)*СЛЧИС()+5 .

Вопрос: Как изменить формулу, чтобы диапазон лежал на промежутке ?

Ответ: Нужно записать формулу следующего вида =(b–a)*СЛЧИС()+a .

III. Проверка понимания материала. (Раздаю тесты.)

Тест на функцию генератор случайных чисел.

Вариант 1

Вопрос 1.
=СРЗНАЧ(A1: A5) .

=СЧЕТ(А1: А4) .

=ЕСЛИ(В1>В2; 1; 0) .

=(В – А)*СЛЧИС()+А .

Вопрос 2. Дана формула = СЛЧИС()* 1.4+3.2 .

[ 0; 3,2 ].

[ 1,4; 3,2 ].

[ 3,2; 4,6 ].

[ 0; 4,6 ].

Вопрос 3. Дана формула = СЛЧИС()* 50 .

В каком диапазоне будут получены числа.
[ 0; 1 ].

[ 0; 50 ].

[ 1; 50 ].

(0; 50).

Вопрос 4. Дана формула = (100 – 20)* СЛЧИС()+20 .

В каком диапазоне будут получены числа.
[ 0; 20 ].

[ 0; 100 ].

[ 20; 100 ].

[ 80; 100 ].

Вопрос 5.
Вопрос 6. Дана формула = СЛЧИС()+12 .

В каком диапазоне будут получены числа.
[ 0; 12 ].

[ 1; 12 ].

[ 11; 13 ].

[ 12; 13 ].

Вариант 2

Вопрос 1. Дана формула = СЛЧИС()* 30 .

В каком диапазоне будут получены числа.
[ 0; 1 ].

[ 0; 30 ].

[ 1; 30 ].

(0; 30) .

Вопрос 2. Дана формула = СЛЧИС()* 3.2+1.4 .

В каком диапазоне будут получены числа.
[ 0; 1,4 ].

[ 1,4; 3,2 ].

[ 3,2; 4,6 ].

[ 1,4; 4,6 ].

Вопрос 3. Выберите из предложенных выражений формулу, определяющую числа случайным образом:
=СРЗНАЧ(B1: B5) .

=ЕСЛИ(В1>В2; 1; 0) .

=СЛЧИС()+А .

=СЧЕТ(А1: А4) .

Вопрос 4. Дана формула = (50 – 10)* СЛЧИС()+10 .

В каком диапазоне будут получены числа.
[ 0; 10 ].

[ 0; 50 ].

[ 10; 40 ].

[ 10; 50 ].

Вопрос 5. Дана формула = 21+ СЛЧИС() .

В каком диапазоне будут получены числа.
[ 0; 21 ].

[ 1; 21 ].

[ 21; 22 ].

[ 21; 23 ].

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

Ответы.

Вариант 1 . 1.4, 2.3, 3.2, 4.3, 5.4, 6.4.

Вариант 2. 1.2, 2.4, 3.3, 4.4, 5.3, 6.3.

IV. Подготовка к практической работе.

Давайте вычислим число p методом Монте–Карло. Для этого вспомним формулу площади круга. Назовите её. Ответ: S = R 2 Посмотрите на рис. 1.

Пусть окружность вписана в квадрат со стороной а = 2. Скажите, пожалуйста, чему равен радиус окружности? (Ответ: 1). Тогда площадь круга чему будет равна? (Ответ: S = ).

Рассмотрим единичный квадрат, вершины которого имеют координаты (0,0), (1,0), (1,1), (0,1). В квадрат будем бросать точку со случайными координатами. Этот квадрат высекает из окружности единичного радиуса с центром в начале координат сектор, площадь которого составляет четверть площади окружности, то есть /4.

Вспомним уравнение окружности с центром в начале координат.

Вопрос: Назовите запись данного факта. Ответ: x 2 + y 2 = 1.

Если точка оказалась внутри сектора, то фиксируем “удачное попадание” единицей, если точка оказалась вне сектора, записываем нуль.

Значит, если x 2 + y 2 < = 1, то точка попадает в круг, иначе она вне круга. Это и есть математическое соотношение, позволяющее определить, лежит ли точка в фигуре. После многократных бросаний вычислим отношение числа удачных исходов к общему количеству бросаний. Это число умножим на 4. Получим приближение к числу p .

Компьютерная модель .

Организуем вычисления на рабочем листе.

В ячейки А1 и В1 поместим заголовки x и y. В ячейку А2 поместим формулу генератора случайного числа =СЛЧИС() и скопируем ее до ячейки В1001 .

В ячейку С2 введем формулу, которая описывает условие попадания или не попадания точек в сектор, то есть =Если(А2^2+B2^2 < = 1; 1; 0) cкопируем до С1001 .

В ячейку С1002 разместим формулу подсчета удачных исходов =СУММ(С2:С1001)/250 или a / 250 . Таблица сконструирована. Теперь проведем компьютерный эксперимент.

Теперь нажимая F9 в ячейке С1002 сменяют друг друга десятичные приближения (не слишком точные) числа .

	A	B	C
1	x	y	попадание
2	=СЛЧИС()	=СЛЧИС()	=ЕСЛИ(A2^2+B2^2 <= 1; 1; 0)
3	=СЛЧИС()	=СЛЧИС()	=ЕСЛИ(A3^2+B3^2 <= 1; 1; 0)
	…	…	…
1001	=СЛЧИС()	=СЛЧИС()	=ЕСЛИ(A1001^2+B1001^2 <= 1; 1; 0)
1002			=СУММ(С2:С1001)/250

V. Подведение итога.

Сегодня мы познакомились с методом Монте–Карло, провели компьютерный эксперимент и нашли практически значение числа ПИ.

КАТЕГОРИИ