Az Excel programban Monte Carlo szimulációk – bevezetés

Megjegyzés:  Szeretnénk, ha minél gyorsabban hozzáférhetne a saját nyelvén íródott súgótartalmakhoz. Ez az oldal gépi fordítással lett lefordítva, ezért nyelvtani hibákat és pontatlanságokat tartalmazhat. A célunk az, hogy ezek a tartalmak felhasználóink hasznára váljanak. Kérjük, hogy a lap alján írja meg, hogy hasznos volt-e az Ön számára az itt található információ. Az eredeti angol nyelvű cikket itt találja .

Ez a cikk a Microsoft Excel adatelemzés és üzleti modellezés igazítani, Wayne Winston-L. volt.

  • Kik használják Monte Carlo szimulációk?

  • Mi történik, amikor =RAND() cellába írja be?

  • Hogyan szimulálhatja különálló véletlen változó értékének?

  • Hogyan szimulálhatja normál véletlen változó értékének?

  • Hogyan üdvözlőlapot vállalat kapcsol hány kártyák meg?

Azt szeretné, pontosan becsüljük meg a valószínűség bizonytalan események. Ha például ActiveX-vezérlőket annak a valószínűségét, hogy egy új termék pénzáramlások van-e egy pozitív nettó jelenérték (NMÉ) Mi az a kockázati tényezőhöz a befektetés portfólió? Monte Carlo szimulációk lehetővé teszi, hogy us bizonytalanság bemutatása, majd játssza le azokat hányszor ezreselválasztó számítógépen modell helyzetekben.

Megjegyzés: A számítógép szimulációk, a 1930-as években és annak valószínűségét számítja ki a szükséges egy atom bomba robbanékonyságát lánc reakció sikeresen működő becsléséhez 1940s során végrehajtott származik Monte Carlo szimulációk nevét. Az érintett a munka physicists szerencsejátékok, nagy ventillátortól volt, a kód neve Monte Carlorendelte a szimulációk.

A következő öt fejezetekben fogja látni, hogyan használhatja az Excel Monte Carlo szimulációk végrehajtásához példák.

Számos vállalat Monte Carlo szimulációk használja a döntéshozatalban fontos része. Az alábbiakban néhány példát talál.

  • Általános motorok, Proctor és Gamble, Pfizer, Bristol-Myers Squibb és Eli Lilly használata szimulációk becsüljük meg az átlag vissza és az új termékek a kockázati tényezőhöz is. A GM ezeket az adatokat használja a ügyvezető igazgató határozza meg, mely termékeket értékesítése jár.

  • Tevékenységek, például a nettó bevétel a vállalat előrejelzés, szerkezeti és vásárlási költségek előrejelzésére és meghatározása a különböző típusú kockázatok (például kamatláb módosításokat és árfolyammozgások) iránti fogékonyságának szimulációk GM használja.

  • Lilly szimulációk alapján határozza meg az egyes kábítószerrel optimális növény kapacitása.

  • Proctor és Gamble segítségével szimulációk modell és optimálisan mód fedezésére FX kockázat.

  • Sears szimulációk alapján határozza meg, hány egységei termék soronként kellene haladnia szállítóktól származó – például, amely kellene haladnia idén kikötõmunkások nadrág pár számát.

  • Olaj és kábítószerrel vállalatok szimulációk "valós beállítások" értéket használja, például egy bontsa ki a, a szerződés vagy a projekt elhalasztása beállítás értéke.

  • Pénzügyi tervezők létrehozásához optimális befektetés stratégiák meghatározására ügyfeleik elavulása Monte Carlo szimulációk használja.

Ha egy cellába írja be a képlet =RAND() , kap, amelyek valószínűleg egyaránt tegyük fel, 0 és 1 közötti bármilyen érték szám. Így időt körülbelül 25 %-át szerezheti be egy számot kisebb vagy egyenlő 0,25; 10 százalék szerezheti be egy szám, amely az idő körülbelül legalább 0.90, és így tovább. A VÉL függvény működésének szemléltetésére nézze meg a fájlt Randdemo.xlsx, a 60-1 ábrán látható.

Book Image
Ábra 60-1 igazoló a VÉL függvény

Megjegyzés: A Randdemo.xlsx fájlt nyit meg, amikor látható az ábra 60-1 azonos véletlen számok nem jelenik meg. A VÉL függvény mindig automatikusan újraszámítja a számok hoz létre, amikor egy munkalapon, vagy ha új adatokat írnak a munkalapra.

Első lépésként másolja a C3 cella C4:C402 a képlet =RAND(). Adja meg nevét a tartomány C3:C402 adatok. Ezután F oszlop, nyomon követheti a 400 (F2 cellára) véletlen számok átlagát és használja a DARABTELI függvényt, a 0 és a 0,25 értéket, 0,25 és 0,50, 0,50 és 0,75 értékre, és 0,75 és 1 közötti törtek határozza meg. Az F9 billentyű lenyomásakor a véletlen számok újraszámítása Az értesítés, hogy a 400 számok átlagát értéke mindig körülbelül 0,5, és hogy körülbelül 25 %-át az eredményeket intervallumok 0,25 értéket. Ezek az eredmények konzisztensek definition véletlen szám. Tartsa szem előtt, hogy a különböző cellákban lévő VÉL által generált értékei független. Például ha generált véletlen szám a C3 cella sok (például 0.99), akkor bemutatja us semmi sem az értékeket a többi generált véletlen számot.

Tegyük fel, hogy a következő különálló véletlen változó szabályozza az igény szerinti, a naptár:

Igény szerint

Valószínűség

10 000

0,10

20 000

ami 0,35

40 000

0,3

60 000

0,25

Hogyan azt, hogy az Excel lejátszani, illetve hasonlóan, ezt igény szerint naptárak sokszor? A trükk az összes lehetséges érték a VÉL függvény társíthatja naptárak lehetséges igény szerinti. A következő hozzárendelés biztosítja, hogy 10 000 az igény szerinti fog fordulhat elő, az idő 10 százalék, és így tovább.

Igény szerint

Hozzárendelt véletlen szám

10 000

Kisebb, mint 0,10

20 000

Nagyobb, mint vagy egyenlő 0,10, de kisebb, mint 0,45

40 000

Nagyobb vagy egyenlő 0,45, és kisebb, mint 0,75 értékre

60 000

Nagyobb vagy egyenlő 0,75 értékre

Az igény szerinti a szimulációk bemutatására, nézze meg a fájlt Discretesim.xlsx, a 60-2 ábrán látható a következő lapon.

Book Image
Ábra 60-2 amelyek különálló véletlen változó

A billentyű lenyomásával a szimulációk környezetbe véletlen szám a Keresés a táblázat tartományból F2:G5 (más néven keresési) elindítására. Véletlenszám-nél nagyobb vagy egyenlő 0 és kisebb, mint 0,10 10 000; az igény szerinti eredményez nagyobb vagy egyenlő 0,10, de kisebb, mint 0,45 véletlen számok 20 000; az igény szerinti eredményez. nagyobb vagy egyenlő 0,45 és kisebb, mint 0,75 véletlen számok eredménye 40 000; az igény szerinti és a véletlen számok nagyobb vagy egyenlő 0,75 60 000 az igény szerinti eredményez. 400 véletlen számok elő bemásolja a C3 C4:C402 a képlet RAND(). Kattintson a 400 kísérletek vagy a közelítés naptár igény szerint bemásolja a B3 B4:B402 a képlet VLOOKUP(C3,lookup,2)készítése. Ez a képlet ellenőrzi, hogy 0,10-nél kisebb bármely véletlen számot állít elő 10 000 az igény szerinti, a bármely 0,10 és 0,45 közötti véletlen számot állít elő 20 000, és így tovább igény szerinti. A cellatartomány F8:F11 használja a DARABTELI függvény azon részét, az egyes igény szerint készítésére 400 közelítések határozza meg. F9 billentyű lenyomásakor azt a véletlen számok újraszámításához, a szimulált valószínűség vannak, a feltételezett igény szerinti valószínűség közelébe.

Írja be egy tetszőleges cellájára a képlet NORMINV(rand(),mu,sigma), ha a középérték mu értéknél és szórásnál szigmaproblémákat normál véletlen változó szimulált érték jelenik meg. Ez az eljárás mutatja be a fájl Normalsim.xlsx, a 60-3 ábrán látható.

Book Image
Ábra 60-3-as amelyek normál véletlen változó

Tegyük fel, hogy szeretnénk 400 kísérletek vagy a közelítés a várható értéke, 40 000, és egy 10 000 szórása normális véletlen változó hasonlóan. (Írja be a ezeket az értékeket az E1, illetve az E2 cella, és nevezze el a cellák jelent , a szigma, a kurzor.) 400 különböző véletlen számok a képlet =RAND() másolása C4 C5:C403 hoz létre. Várható értéke, 40 000, és egy 10 000 szórása normális véletlen változó 400 különböző próba értékek bemásolja a B4 B5:B403 NORMINV(C4,mean,sigma) képletet hozza létre. Az automatikus újraszámolás a véletlen számok az F9 billentyű lenyomásakor az átlag közelébe 40 000, és a szórás közelébe 10 000 továbbra is.

Lényegében véletlen számot x, a képlet NORMINV(p,mu,sigma) a padik percentilisét egy átlagos mu és a szórás szigmanormál véletlen változó hoz. Ha például a véletlen számok 0.77 a C4 cella (lásd az ábrát 60-3-as) hoz létre a B4 cellába körülbelül a 77th percentilisét normál véletlen változó 40 000 középértéke és szórása 10 000.

Ebben a részben láthatja, hogyan Monte Carlo szimulációk döntéshozatali eszköz használható. Tegyük fel, hogy az igény szerinti Valentin-napi kártya szabályozza az alábbi különálló véletlen változó:

Igény szerint

Valószínűség

10 000

0,10

20 000

ami 0,35

40 000

0,3

60 000

0,25

Az üdvözlőlap eladja a $4.00, és minden egyes kártyán megtalálható változó költségének $1.50. Maradék kártyák kell távolítva a $0,20 kártya használati költség mellett. Hány kártyák kell-e nyomtatni?

Alapvetően hogy szimulálhatja minden lehetséges termelési mennyiség (10 000, 20 000, 40 000 vagy 60 000) sokszor (például 1000 közelítés). Ezután azt határozza meg, milyen sorrendben mennyiség a maximális átlagos nyereség együttesen az 1000 közelítések fölé. Ebben a szakaszban a fájlban Valentine.xlsx, ábra 60-4 látható megtalálhatja az adatokat. A tartomány nevét a cellák B1:B11 hozzárendelése cellák C1:C11. A cellatartomány G3:H6 nevét a keresésvan-e hozzárendelve. Az értékesítési ár és a költség paraméterek cellák C4:C6 szerepelnek.

Book Image
Ábra 60-4-es Valentin-napi kártya szimulációk

Megadhat egy próba termelési mennyiség (ebben a példában 40 000) a C1 cellába. Ezután hozzon létre egy véletlen szám a C2 cellában a képlet =RAND(). Az előzőekben leírtak szimulálhatja a C3 cellában a képlet VLOOKUP(rand,lookup,2)a kártyához igény szerint. (Az FKERES függvényt a VÉL is a cella nevét, nem a VÉL függvény C3, cella rendelt)

Az eladott egységek számát a termelési mennyiség és az igény szerinti közül a kisebb. C8 cellájában a bevétel, a képlet kiszámításához MIN (gyártott, igény) * unit_price. Cellában C9, a képletet tartalmazó teljes költség kiszámítása előállított * unit_prod_cost.

További kártyák, mint az igény szerinti konzerv azt, ha mennyiségek maradtak egyenlő gyártási mínusz igény szerint; egyéb esetben egységek nem maradtak. Azt kiszámítása a C10 a képletet tartalmazó cellát a költség unit_disp_cost * Ha (előállított > igény szerint, előállított – igény, 0). Végezetül cellában C11 azt számítja ki a profit bevétel – total_var_cost-total_disposing_costszerint.

Azt szeretné, nyomja le az F9 sokszor (például 1000) az egyes termelési mennyiség és a várt eredmény minden mennyiség megegyeznek hatékonyan. Ez a helyzet egyike, amelyben a kétirányú adattábla megjelenik a mentési. (Kapcsolatos további tudnivalók: fejezet 15, "Magánjellegű elemzés az adattáblák," adattáblák.) Az ebben a példában használt adattábla a 60-5 ábrán látható.

Book Image
Kétirányú adattábla ábra 60-5 üdvözlőlap szimulációk

A cellatartomány A16:A1015 írja be a számokat, 1 – 1000 (az 1000 kísérletek megfelelő). Egy egyszerűen készíthet ezeket az értékeket az A16 cellában 1 beírásával indításához. Jelölje ki a cellát, a Kezdőlap lap Szerkesztés csoportjában kattintson a Kitöltésgombra, és válassza a sorozat a sorozat párbeszédpanel megjelenítéséhez. Adja meg a sorozat párbeszédpanelen látható az ábra 60-6, lépés értéke 1 és 1000 leállítása értéket. A Sorozat a területen válassza az oszlopok lehetőséget, és kattintson az OKgombra. A szám 1 – 1000 kezdő A16 cellában megadott oszlopban.

Book Image
Ábra 60-6 és 1000 1 próba számok töltse ki a sorozat párbeszédpanel használatával

Ezután azt adja meg a lehetséges termelési mennyiségek (10 000, 20 000, 40 000, 60 000) cellák B15:E15. Minden próbaverzió szám (1-től 1000) hasznát kiszámításához szeretnénk, és minden egyes termelési mennyiség. Hivatkozunk nyereség (a számított cellát C11), a bal felső cellájába az adattábla (15) a képlet = C11beírásával.

Azt most már készen áll a trükk az Excel be, amelyek 1000 iterációk száma az egyes gyártási mennyiség igény szerint. Jelölje ki a táblázat (A15:E1014), és kattintson az adatok lap Adateszközök csoportjában kattintson a What If Analysis pontra, és válassza az adattábla parancsra. Kétirányú adattábla beállítani, válassza a termelési mennyiség (C1 cellában), a Sorértékek bemeneti cellája, és egy üres cellára (választottunk I14 cella) válassza az oszlop bemeneti cella. Az OK gombra kattint, az Excel keltő szűrő megőrzi az 1000 igény szerinti értékek minden egyes rendelési mennyiség.

Ha meg szeretné érteni, hogy miért működik, fontolja meg az értékeket az adattábla a cellatartomány C16:C1015 elhelyezett. Az egyes ezek a cellák az Excel használja 20 000-es értéket a C1 cellában. C16, az a oszlop bemeneti cella értéke 1 kerül egy üres cellát, és a véletlen számok újraszámítja a C2 cellában. A megfelelő nyereség majd rögzítését C16 cellában. A 2 érték beviteli oszlop cella majd kerül egy üres cellát, és újra újraszámítja a véletlen szám a C2 cella. A megfelelő nyereség cella C17 meg van adva.

Úgy, hogy a képlet AVERAGE(B16:B1015)C13:E13 másolja a B13 cellájára, hogy az egyes termelési mennyiség átlagos szimulált profit számítja ki. Cella B14 másolni a képletet STDEV(B16:B1015)C14:E14, azt az egyes rendelési mennyiség szimulált nyereségének szórását számítja ki. Minden alkalommal, amikor azt nyomja le az F9 billentyűt, az 1000 közelítések igény szerint, az egyes rendelési mennyiség szimulált. A legnagyobb várt eredmény 40 000 kártyák mindig előállító együttesen. Ezért úgy tűnik, hogy 40 000 kártyák előállító a megfelelő döntés.

Milyen következményekkel járnak a döntés a kockázat     20 000 helyett 40 000 kártyák előállítása azt, ha a várt eredmény körülbelül 22 készültségi esik, de a kockázatok (olyan feltételeket által profit szórása) esik szinte 73 százalék. Emiatt azt is rendkívül jelentkezésének kockázat, ha 20 000 kártyák előállító valószínűleg a megfelelő döntés. Mellékesen mindig előállító 10 000 kártyákat tartalmaz, a szórás értéke 0 kártyák mert 10 000 kártyák konzerv azt, ha azt fogja mindig forgalmazott mindegyikük bármely maradék nélkül.

Megjegyzés: Ebben a munkafüzetben a számítási beállítás értéke Automatikus kivéve a táblákat. (Paranccsal a számítási képletek lap számítás csoportjában található.) Ezzel biztosíthatja, hogy az adatok táblában nem újraszámolást kivéve azt nyomja le az F9 billentyűt, amely jó ötlet mivel nagy adattábla lelassul a munkát, ha minden alkalommal, amikor Ön beír egy üzenetet a munkalapra legyen átszámolva. Figyelje meg, hogy ebben a példában, nyomja le az F9 billentyűt, amikor az átlagos nyereség változik. Ennek oka az, hogy minden alkalommal, amikor nyomja le az F9 billentyűt, a véletlen számok 1000 különböző sorozatában létrehozásához használja az egyes rendelési mennyiség igényeivel.

Várható értékének megbízhatósági intervallumát jelenti Profit     Az ebben az esetben kérni természetes kérdést, milyen intervallum be vannak azt, hogy a valódi átlagos nyereség csökken 95 %? Az intervallum az átlagos nyereség százalékban 95 várható értékének megbízhatósági intervallumátneve. Egy 95 % megbízhatósági intervallumát a bármely szimulációk kibocsátás a következő képlet alapján számítja ki:

Book Image

J11 cellába akkor számítja ki az alsó határ az átlagos nyereség a a 95 % konfidencia-intervallum átállítása amikor 40 000 naptárak, a képlet D13–1.96*D14/SQRT(1000)darab termék készült. A cellában J12 a képlet D13+1.96*D14/SQRT(1000)a 95 % konfidencia-intervallum átállítása felső határa számítja ki. Számítások a 60-7 ábra jelennek meg.

Book Image
Ábra 60-7 95 % várható értékének megbízhatósági intervallumát átlagos nyereség amikor 40 000 naptárak vannak rendezve.

Jelenleg 95 %, hogy amikor 40 000 naptárak vannak rendezve az átlagos nyereség $56,687 és $62,589 között.

  1. Egy GMC viszonteladó úgy véli, hogy 2005 különleges képviselői az igény szerinti fog kell normális eloszlású 200 középértéke és szórása 30. A költség egy ügyféloldali fogadására 25 000 $, és az ügyféloldali ő eladja a 40 000 $ a. A teljes áron nem értékesített különleges képviselői fele $ 30 000 feletti számig adható el. Ő figyelembe véve 200, 220, 240, 260, 280 vagy 300 különleges képviselői rendezést. Hány kell ő rendelés?

  2. Egy kis szupermarket megállapíthatja, hogy hány példányt személyek újság azok kell rendelnie az egyes hetekben próbálja. Azok biztonságosnak találja a következő különálló véletlen változó szabályozza az igény szerinti személyek :

    Igény szerint

    Valószínűség

    15

    0,10

    20

    0,20

    25

    0,30

    30

    0,25

    35-tel

    0,15

  3. A szupermarket $1,00 fizet az egyes személyek példányát, és a $1.95 eladja azt. Eladatlan példányokban a $0,50 adhatók vissza. Személyek hány példányban kell a tároló sorrendje?

További segítségre van szüksége?

Bármikor segítséget kérhet az Excel technikai közösségétől és az Answers-közösségtől, az Excel User Voice webhelyen pedig új funkciókra vagy fejlesztésekre tehet javaslatot.

Office-jártasság bővítése
Oktatóanyagok megismerése
Új szolgáltatások listájának lekérése
Részvétel az Office Insider programban

Hasznos volt az információ?

Köszönjük a visszajelzését!

Köszönjük visszajelzését. Jobbnak látjuk, ha az Office egyik támogatási szakemberéhez irányítjuk.

×