Montekarlo simulācijas izveidošana, izmantojot Excel

Montekarlo simulāciju var izveidot, izmantojot Microsoft Excel un kauliņu spēli. Montekarlo simulācija ir matemātiska skaitliska metode, kas aprēķinu un sarežģītu problēmu veikšanai izmanto nejaušas izlozes. Mūsdienās to plaši izmanto un tam ir galvenā loma dažādās jomās, piemēram, finansēs, fizikā, ķīmijā un ekonomikā.

Montekarlo imitācija

Montekarlo metodi izgudroja Nikolā Metropolisa 1947. gadā, un tās mērķis ir sarežģītu problēmu risināšana, izmantojot izlases un varbūtības metodes. Termins "Monte Carlo" cēlies no Monako administratīvā apgabala, ko tautā dēvē par vietu, kur azartiski spēlē Eiropas elites. Mēs izmantojam Montekarlo metodi, kad problēma ir pārāk sarežģīta un grūti izdarāma, izmantojot tiešus aprēķinus. Liels skaits iterāciju ļauj modelēt normālo sadalījumu.

Montekarlo imitācijas metode aprēķina integrāļu varbūtības un atrisina daļējos diferenciālvienādojumus, tādējādi varbūtības lēmumā ieviešot statistisko pieeju riskam. Lai gan ir izveidoti daudzi moderni statistikas rīki, lai izveidotu Montekarlo simulācijas, parasto likumu un vienoto likumu ir vieglāk simulēt, izmantojot Microsoft Excel, un apiet matemātiskos pamatus.

Montekarlo simulācijai mēs izdalām vairākus galvenos mainīgos, kas kontrolē un apraksta eksperimenta iznākumu, pēc tam piešķir lielu varbūtības sadalījumu pēc liela skaita nejaušu izlases veikšanas. Kā modeli ņemsim kauliņu spēli.

Kauliņu spēle

Kauliņu spēle rit šādi:

• Spēlētājs iemet trīs kauliņus, kuriem 3 reizes ir 6 malas.

• Ja 3 metieni kopā ir 7 vai 11, spēlētājs uzvar.

• Ja 3 metieni ir: 3, 4, 5, 16, 17 vai 18, spēlētājs zaudē.

• Ja kopsumma ir kāda cita iznākums, spēlētājs spēlē atkal un atkal ripo kauliņu.

• Kad spēlētājs atkal iemet kauliņu, spēle turpinās tādā pašā veidā, izņemot to, ka spēlētājs uzvar, kad kopsumma ir vienāda ar summu, kas noteikta pirmajā kārtā.

Rezultātu ģenerēšanai ieteicams izmantot arī datu tabulu. Turklāt, lai sagatavotu Montekarlo simulāciju, nepieciešami 5000 rezultāti.

1. solis: Dice Rolling Events

Pirmkārt, mēs izstrādājam datu diapazonu ar katras 3 kauliņu rezultātiem par 50 ruļļiem. Lai to izdarītu, tiek piedāvāts izmantot funkciju "RANDBETWEEN (1, 6)". Tādējādi katru reizi, noklikšķinot uz F9, mēs ģenerējam jaunu saraksta rezultātu kopu. Šūna "Rezultāts" ir visu 3 ruļļu rezultātu summa.

2. solis: rezultātu diapazons

Pēc tam mums ir jāizstrādā datu klāsts, lai identificētu iespējamos rezultātus pirmajai kārtai un nākamajām kārtām. Pastāv 3 kolonnu datu diapazons. Pirmajā kolonnā mums ir skaitļi no 1 līdz 18. Šie skaitļi atspoguļo iespējamos rezultātus, kad kauliņš tiek ripināts 3 reizes: maksimālais ir 3 * 6 = 18. Jūs ņemsit vērā, ka 1. un 2. šūnai atradumi nav nepieciešami, jo nav iespējams iegūt 1 vai 2, izmantojot 3 kauliņus. Minimums ir 3.

Otrajā kolonnā ir iekļauti iespējamie secinājumi pēc pirmās kārtas. Kā teikts sākotnējā paziņojumā, vai nu spēlētājs uzvar (Win) vai zaudē (Lose), vai arī viņi atkārto (Re-roll), atkarībā no rezultāta (kopā 3 kauliņu ripas).

Trešajā ailē tiek reģistrēti iespējamie secinājumi par nākamajām kārtām. Šos rezultātus mēs varam sasniegt, izmantojot funkciju "IF". Tas nodrošina, ka, ja iegūtais rezultāts ir līdzvērtīgs pirmajā kārtā iegūtajam rezultātam, mēs uzvarējam, pretējā gadījumā mēs ievērojam sākotnējās spēles sākotnējos noteikumus, lai noteiktu, vai mēs atkārtoti ripinām kauliņu.

3. solis: Secinājumi

Šajā solī mēs nosakām 50 kauliņu ruļļu iznākumu. Pirmo secinājumu var iegūt, izmantojot indeksa funkciju. Šī funkcija meklē iespējamos pirmās kārtas rezultātus, secinājums atbilst iegūtajam rezultātam. Piemēram, iegūstot 6, mēs atkal spēlējam.

Var iegūt citu kauliņu ruļļu atradumus, izmantojot funkciju “VAI” un indeksa funkciju, kas ligzdota ar “IF” funkciju. Šī funkcija Excel saka: "Ja iepriekšējais rezultāts ir Win vai Lose", pārtrauciet kauliņu ripināšanu, jo, kad esam uzvarējuši vai zaudējuši, mēs esam izdarīti. Pretējā gadījumā mēs ejam uz šādu iespējamo secinājumu kolonnu un nosakām rezultāta secinājumu.

4. solis: kauliņu ruļļu skaits

Tagad mēs nosakām nepieciešamo kauliņu ruļļu skaitu pirms zaudēšanas vai uzvaras. Lai to izdarītu, mēs varam izmantot funkciju “COUNTIF”, kurai nepieciešama Excel, lai saskaitītu “Pārrullēšanas” rezultātus un pievienotu tam skaitli 1. Tas pievieno vienu, jo mums ir viena papildu kārta, un mēs iegūstam gala rezultātu (uzvarēt vai zaudēt).

5. solis: Simulācija

Mēs izstrādājam diapazonu, lai izsekotu dažādu simulāciju rezultātus. Lai to izdarītu, mēs izveidosim trīs kolonnas. Pirmajā kolonnā viens no iekļautajiem skaitļiem ir 5000. Otrajā kolonnā mēs meklēsim rezultātu pēc 50 kauliņu ruļļiem. Trešajā kolonnā, kolonnas nosaukumā, pirms galīgā statusa iegūšanas (uzvarēt vai zaudēt) mēs meklēsim kauliņu ruļļu skaitu.

Pēc tam mēs izveidosim jutīguma analīzes tabulu, izmantojot funkciju datus vai tabulu Datu tabula (šī jutība tiks ievietota otrajā tabulā un trešajā kolonnā). Šajā jutīguma analīzē faila A1 šūnā jāievieto notikumu skaits no 1 līdz 5000. Faktiski varēja izvēlēties jebkuru tukšu šūnu. Ideja ir vienkārši katru reizi piespiest veikt pārrēķinu un tādējādi iegūt jaunus kauliņu ruļļus (jaunu simulāciju rezultāti), nesabojājot uzstādītās formulas.

6. solis: varbūtība

Mēs beidzot varam aprēķināt uzvarēšanas un zaudēšanas varbūtības. Mēs to darām, izmantojot funkciju “COUNTIF”. Formulā tiek saskaitīts "win" un "zaudēt" skaits, pēc tam dalīts ar kopējo notikumu skaitu - 5000, lai iegūtu atbilstošu viena un otra proporciju. Mēs beidzot redzam, ka Win iznākuma varbūtība ir 73, 2%, un tāpēc zaudējuma iznākums ir 26, 8%.