Ustvarjanje simulacije Monte Carlo z uporabo Excela

S pomočjo programa Microsoft Excel in igre s kockami lahko razvijemo simulacijo Monte Carlo. Simulacija Monte Carlo je matematična numerična metoda, ki uporablja naključne risbe za izvajanje izračunov in zapletenih problemov. Danes se pogosto uporablja in igra ključno vlogo na različnih področjih, kot so finance, fizika, kemija in ekonomija.

Monte Carlo Simulacija

Metodo Monte Carlo je izumil Nicolas Metropolis leta 1947 in si prizadeva za reševanje zapletenih problemov z naključnimi in verjetnostnimi metodami. Izraz "Monte Carlo" izvira iz upravnega območja Monaka, ki je splošno znano kot kraj, kjer se igrajo evropske elite. Metodo Monte Carlo uporabljamo, kadar je težava preveč zapletena in jo je težko izvesti z neposrednim izračunom. Veliko število iteracij omogoča simulacijo normalne porazdelitve.

S simulacijsko metodo Monte Carlo izračunamo verjetnosti za integrale in rešimo delne diferencialne enačbe, s čimer uvedemo statistični pristop k tveganju v verjetnostni odločitvi. Čeprav obstaja veliko naprednih statističnih orodij za ustvarjanje simulacij Monte Carla, je lažje simuliranje običajnega in enotnega zakona z uporabo Microsoft Excel in obvladovanje matematičnih podlag.

Za simulacijo Monte Carla izoliramo številne ključne spremenljivke, ki nadzorujejo in opisujejo izid poskusa, nato dodelimo verjetnostno porazdelitev po opravljenem velikem številu naključnih vzorcev. Vzemimo igro kock kot model.

Igra kock

Takole igra kocke:

• Igralec vrže tri kocke, ki imajo 6 strani 3-krat.

• Če je skupno 3 mete 7 ali 11, igralec zmaga.

• Če je skupno 3 metov: 3, 4, 5, 16, 17 ali 18, igralec izgubi.

• Če je v skupnem seštevku kakšen drug izid, igralec znova igra in znova vrže kocke.

• Ko igralec vrže kocke, se igra nadaljuje na enak način, le da igralec zmaga, če je skupni znesek enak vsoti, določeni v prvem krogu.

Za ustvarjanje rezultatov je priporočljivo uporabiti tudi tabelo s podatki. Poleg tega je za pripravo simulacije Monte Carla potrebnih 5000 rezultatov.

1. korak: kotalni dogodki s kockami

Najprej razvijemo vrsto podatkov z rezultati vsake od treh kock za 50 zvitkov. Da bi to naredili, je predlagano, da uporabite funkcijo "RANDBETWEEN (1, 6)". Tako vsakič, ko kliknemo F9, ustvarimo nov niz rezultatov zvitka. Celica "Rezultat" je vsota rezultatov iz treh zvitkov.

2. korak: obseg rezultatov

Nato moramo razviti vrsto podatkov, da ugotovimo možne izide za prvi in ​​naslednji krog. Obstaja razpon podatkov v 3 stolpcih. V prvem stolpcu imamo številke od 1 do 18. Te številke predstavljajo možne izide po kockanju kocke 3-krat: največ 3 * 6 = 18. Opazili boste, da sta za celici 1 in 2 ugotovitve N / A, saj je s 3 kockami nemogoče dobiti 1 ali 2. Najmanj je 3.

V drugem stolpcu so možni zaključki po prvem krogu. Kot je navedeno v začetni izjavi, bodisi igralec zmaga (Win) ali izgubi (Lose), ali pa predvaja (Re-roll), odvisno od rezultata (skupaj 3 zvitke kock).

V tretjem stolpcu so zabeleženi možni zaključki do naslednjih krogov. Te rezultate lahko dosežemo s funkcijo "ČE". To zagotavlja, da če dobljeni rezultat ustreza enakemu rezultatu, dobljenemu v prvem krogu, zmagamo, sicer pa sledimo začetnim pravilom prvotne igre, da ugotovimo, ali bomo kocke ponovno vrteli.

3. korak: sklepi

V tem koraku ugotovimo rezultat 50 zvitkov kock. Prvi sklep je mogoče dobiti s funkcijo indeksa. Ta funkcija išče možne rezultate prvega kroga, pri čemer zaključek ustreza dobljenemu rezultatu. Na primer, ko dobimo 6, spet igramo.

Ugotovimo lahko tudi druge zvitke kock s pomočjo funkcije "ALI" in indeksne funkcije, ugnezdene v funkciji "ČE". Ta funkcija v Excelu pove: "Če je prejšnji rezultat zmaga ali izgubi", prenehajte kotati kocke, ker ko smo zmagali ali izgubili, smo končali. V nasprotnem primeru gremo v stolpec naslednjih možnih zaključkov in ugotovimo zaključek rezultata.

4. korak: Število zvitkov kock

Zdaj določimo število kock, ki so potrebne pred izgubo ali zmago. Če želite to narediti, lahko uporabimo funkcijo "COUNTIF", ki od Excela zahteva, da prešteje rezultate "Ponovni zavoj" in mu doda številko 1. Dodaja enega, ker imamo en dodatni krog in dobimo končni rezultat (zmaga ali poraz).

5. korak: Simulacija

Razvijamo paleto za sledenje rezultatov različnih simulacij. Če želite to narediti, bomo ustvarili tri stolpce. V prvem stolpcu je ena od vključenih številk 5.000. V drugem stolpcu bomo poiskali rezultat po 50 zvitkih kock. V tretjem stolpcu, naslovu stolpca, bomo iskali število zvitkov kock, preden pridobimo končni status (zmaga ali poraz).

Nato bomo ustvarili tabelo analize občutljivosti z uporabo podatkov o značilnostih ali tabele s podatki o tabeli (ta občutljivost bo vstavljena v drugo in tretjo stolpce). Pri tej analizi občutljivosti je treba v celico A1 datoteke vstaviti število dogodkov od 1 do 5000. Pravzaprav bi lahko izbrali katero koli prazno celico. Ideja je preprosto, da vsakič znova prisilite k preračunu in tako dobite nove zvitke kock (rezultati novih simulacij), ne da bi pri tem poškodovali formule.

6. korak: Verjetnost

Končno lahko izračunamo verjetnosti zmage in poraza. To storimo s funkcijo "COUNTIF". Formula šteje število "zmaga" in "izgubi", nato pa se deli s skupnim številom dogodkov, 5.000, da dobimo ustrezen delež enega in drugega. Končno vidimo, da je verjetnost, da dobite izid Win 73, 2% in izgubljeni izid, torej 26, 8%.