مقدمه حول محاكاه مونتي كارلو في Excel

ملاحظة:  نرغب في تزويدك بأحدث محتوى تعليمات لدينا بأسرع ما يمكن وبلغتك الخاصة. وقد تمت ترجمة هذه الصفحة آلياً وقد تحتوي على أخطاء نحوية أو أخطاء تتعلق بالدقة. كما نهدف إلى أن يكون هذا المحتوى مفيداً لك. هل يمكنك إخبارنا إذا كانت المعلومات مفيدة لك في أسفل هذه الصفحة؟ إليك المقالة بالغة الإنجليزية لسهولة الرجوع إليها.

هذه المقاله ماخوذه من تحليل بيانات Microsoft Excel و# انشاء نماذج الاعمال ب# اين وينستون L..

  • من هم المستخدمون محاكاه مونتي كارلو؟

  • ماذا يحدث عند كتابه = rand () في خليه؟

  • كيف يمكنك محاكاه قيم متغير عشوائي منفصله؟

  • كيف يمكنك محاكاه قيم متغير عشوائي عادي؟

  • كيف تحديد شركه بطاقه ترحيب كم عدد الاوراق ل# انتاج؟

نحن تريد دقه تقدير احتمالات احداث متاكد. علي سبيل المثال، ما هو الاحتمال التدفقات النقديه منتج جديد سيكون موجبه قيمه الحاليه الصافيه (NPV)؟ ما هو عامل المخاطره من حزمه الاستثمار الخاصه بنا؟ يمكن محاكاه مونتي كارلو بنا ل# حالات نموذج تقديم الشك و# ثم تشغيلها علي كمبيوتر الالاف من المرات.

ملاحظة: الاسم محاكاه مونتي كارلو تاتي من عمليات محاكاه الكمبيوتر الذي تم تنفيذه ب# اثناء الثلاثينيات و# الاربعينات ل# تقدير احتمال ان مناسبا رد سلسله المطلوبه ل# قنبله atom ديتوناتي ب# نجاح. تم فيسيسيستس المضمنه في هذا العمل المراوح كبيره المقامره، حيث يمكنهم وفر عمليات المحاكاه اسم رمز مونتي كارلو.

في فصول الخمسه التاليه، ستري امثله حول كيفيه استخدام Excel ل# اجراء عمليات محاكاه مونتي كارلو.

استخدام العديد من الشركات محاكاه مونتي كارلو ك# جزء من عمليه اتخاذ القرار هام. فيما يلي بعض الامثله.

  • المحركات عام بروكتور و# يقامر، فيزير، سكويب مييرس بريستول و# ليلي الي استخدام محاكاه ل# تقدير كل من الارجاع average وعامل المخاطره المنتجات الجديده. في GM، يتم استخدام هذه المعلومات رئيس مجلس الاداره ل# تحديد المنتجات التي تصل الي السوق.

  • يستخدم GM محاكاه ل# انشطه مثل التنبؤ الدخل الصافي ل# الشركه و# التوقع التكاليف بنائيه و# الشراء، و# تحديد سوسسيبتيبيليتي الخاص به ل# انواع مختلفه من المخاطر (مثل التغييرات معدل الفائده و# التغيرات معدل exchange).

  • يستخدم ليلي محاكاه ل# تحديد سعه مصنع افضل طريقه ل# كل الادويه.

  • يستخدم بروكتور و# يقامر محاكاه ل# نموذج و# الشكل الامثل التهرب المخاطر الصرف.

  • يستخدم سيرس محاكاه ل# تحديد عدد الوحدات ل# كل سطر منتج يجب ان ترتيبها من موردين — علي سبيل المثال، عدد ازواج من تروسيرس دوكيرس التي يجب ترتيب هذه السنه.

  • استخدم الشركات البترول و# الادويه محاكاه قيمه الحقيقي "خيارات"، مثل قيمه خيار ل# توسيع او تتعاقد تاجيل مشروع.

  • استخدام المخططات الماليه محاكاه مونتي كارلو ل# تحديد استراتيجيات الاستثمار افضل طريقه ل# التقاعد العملاء الخاصه بهم.

عند كتابه الصيغه = rand () في خليه، يمكنك الحصول علي رقم التي من المحتمل التساوي يفترض اي قيمه بين 0 و 1. وب# التالي، حوالي 25 ب# المائه الوقت، يجب الحصول علي رقم اقل من او تساوي 0.25؛ حوالي 10 ب# المائه الوقت الذي يجب ان يحصلوا علي رقم التي يتم 0.90 علي الاقل و# غير ذلك. ل# توضيح كيفيه عمل الداله RAND، الق نظره علي ملف Randdemo.xlsx، يظهر في الرسم التوضيحي 60-1.

صورة الكتاب
60 الشكل-1 يوضح الداله RAND

ملاحظة: عند فتح الملف Randdemo.xlsx، لا تري نفس ارقام عشوائيه هو مبين في الرسم التوضيحي 60-1. الداله RAND دائما يعيد الارقام يقوم ب# انشاء عند فتح ورقه عمل او عند ادخال معلومات جديده في ورقه العمل.

اولا، نسخ من الخليه C3 الي C4:C402 الصيغه = rand (). ثم ادخل اسما النطاق C3:C402 البيانات. بعد ذلك، في العمود F، يمكنك تعقب متوسط ارقام عشوائيه 400 (الخليه F2)، و# استخدم الداله COUNTIF ل# تحديد الكسور الموجوده بين 0 و 0.25 و 0.25 و 0.50، 0.50 و 0.75، و 0.75 و 1. عند الضغط علي المفتاح F9، تتم اعاده احتساب ارقام عشوائيه. لاحظ ان متوسط الارقام 400 ب# شكل دائم تقريبا 0.5، و# ان حوالي 25 ب# المائه النتائج في فواصل زمنيه 0.25. تكون هذه النتائج متناسقه مع تعريف رقم عشوائي. تجدر الاشاره ايضا الي ان القيم التي تم انشاؤها عن طريق RAND في خلايا مختلفه ب# مستقل. علي سبيل المثال، اذا كان انشاء رقم عشوائي في الخليه C3 عدد كبير (علي سبيل المثال، 0.99)، يشير الي انه الينا شيئا حول قيم ارقام عشوائيه الاخري التي يتم انشاؤها.

ل# نفترض يخضع الطلب علي تقويم الي متغير عشوائي منفصله ما يلي:

الطلب

الاحتمال

10,000

0.10

20,000

0.35

40.000

0,3

60000

0,25

كيف يمكن لدينا Excel تشغيل، او محاكاه، هذا الطلب ل# التقويمات عده مرات؟ يتم الحصول علي النتيجه المطلوبه ل# اقران كل قيمه المحتمله ل# الداله RAND ب# طلب المحتمله ل# التقويمات. التعيين التاليه يضمن طلب 10000 سيتم تحدث 10 ب# المائه الوقت، و# ما الي ذلك.

الطلب

رقم عشوائي تم تعيينه

10,000

اقل من 0.10

20,000

اكبر من او يساوي 0.10 و# اقل من 0.45

40.000

اكبر من او يساوي 0.45 و# اقل من 0.75

60000

اكبر من او يساوي 0.75

ل# توضيح المحاكاه الطلب، انظر الملف Discretesim.xlsx، هو مبين في الرسم التوضيحي 60-2 علي الصفحه التاليه.

صورة الكتاب
60 الشكل-2 محاكاه عن متغير عشوائي منفصله

المفتاح ل# محاكاه الخاص بنا ب# استخدام رقم عشوائي ل# بدء عمليه بحث من نطاق الجدول F2:G5 (المسماه البحث). ارقام عشوائيه اكبر من او يساوي 0 و# اقل من 0.10 الي كل طلب ل 10000؛ ارقام عشوائيه اكبر من او يساوي 0.10 و# اقل من 0.45 الي كل طلب ل 20000؛ ارقام عشوائيه اكبر من او يساوي 0.45 و# اقل من 0.75 الي كل طلب ل# 40.000؛ و# ارقام عشوائيه اكبر من او يساوي 0.75 الي كل طلب ل 60000. انشاء ارقام عشوائيه 400 ب# نسخ من C3 الي C4:C402 صيغه rand (). يمكنك انشاء ثم 400 trials او تكرارات، من الطلب التقويم ب# نسخ من B3 ل B4:B402 الصيغه VLOOKUP(C3,lookup,2). تضمن هذه الصيغه ان اي رقم عشوائي اقل من 0.10 ينشئ طلب 10000، ينشئ اي رقم عشوائي بين 0.10 و 0.45 طلب 20000، و# ما الي ذلك. في نطاق الخلايا F8:F11، استخدم الداله COUNTIF ل# تحديد كسر الخاصه بنا التكرارات 400 مما ينتج عن كل الطلب. عندما نقوم ب# الضغط علي F9 ل# اعاده حساب ارقام عشوائيه، يتم احتمالات تمت محاكاتها اغلاق ل# الاحتمالات الطلب مفترض الخاصه بنا.

اذا قمت ب# كتابه في اي خليه في صيغه NORMINV(rand(),mu,sigma)، سيقوم ب# انشاء قيمه متغير عشوائي العادي تواجه الوسط mu و# الانحراف المعياري sigmaتمت محاكاتها. يتم توضيح هذا الاجراء في الملف Normalsim.xlsx، هو مبين في الرسم التوضيحي 60-3.

صورة الكتاب
60 الشكل-3 محاكاه عن متغير عشوائي عادي

فل# نفترض اننا نريد ان محاكاه 400 trials، او تكرار ل# عن متغير عشوائي عادي مع وسط 40.000 و# انحراف معياري من 10000. (يمكنك كتابه هذه القيم في الخلايا E1 و E2، و# ادخل اسما هذه الخلايا تعني و sigma، علي التوالي.) نسخ الصيغه = rand () من الخليه C4 الي C5:C403 انشاء 400 ارقام عشوائيه مختلفه. نسخ من B4 الي B5:B403 الصيغه NORMINV(C4,mean,sigma) يولد 400 قيم التجريبي مختلفه من عن متغير عشوائي عادي مع وسط 40.000 و# انحراف معياري من 10000. عندما نقوم ب# الضغط علي المفتاح F9 ل# اعاده حساب ارقام عشوائيه، تبقي الوسط اغلاق ل# 40.000 و# الانحراف المعياري ب# القرب من 10000.

ب# شكل اساسي، ينشئ صيغه NORMINV(p,mu,sigma)pعن متغير عشوائي العادي مع قيمه الوسيطه mean mu و انحراف المعياري sigmaترتيب النسب المئويه ل# رقم عشوائي x. علي سبيل المثال، (راجع الشكل 60-3) انشاء رقم عشوائي 0.77 في الخليه C4 في الخليه B4 تقريبا 77th القيمه المئويه ل# عن متغير عشوائي العادي مع وسط 40.000 و# انحراف معياري من 10000.

في هذا القسم، ف# ستشاهد كيفيه استخدام محاكاه كارلو مونتي ك# اداه اتخاذ القرار. ل# نفترض ان الطلب علي بطاقه عيد الحب يخضع متغير عشوائي منفصله ما يلي:

الطلب

الاحتمال

10,000

0.10

20,000

0.35

40.000

0,3

60000

0,25

بطاقه ترحيب يبيع ل $4.00، و# هو تكلفه متغير انتاج كل بطاقه $1.50. يجب ان تكون بطاقات متبقيه التخلص من كلفه 0.20 كل بطاقه. يجب طباعه بطاقات عدد؟

ب# شكل اساسي، يمكننا محاكاه كل كميه الانتاج المحتمله (10000 20000، 40.000 او 60000) عده مرات (علي سبيل المثال، تكرار 1000). ثم تحديد كميه الامر الذي يعطي الارباح متوسط الحد الاقصي عبر التكرارات 1000. يمكنك العثور علي البيانات ل# هذا المقطع في ملف Valentine.xlsx، هو مبين في الرسم التوضيحي 60-4. تعيين اسماء النطاقات في B1:B11 الخلايا ل C1:C11 الخلايا. يتم تعيين نطاق الخلايا G3:H6 اسم البحث. يتم ادخال الخاصه بنا سعر المبيعات و# التكلفه المعلمات في C4:C6 الخلايا.

صورة الكتاب
محاكاه بطاقه عيد الحب الشكل 60-4

يمكنك ادخال كميه انتاج تجريبيه (40.000 في هذا المثال) في الخليه C1. بعد ذلك، انشاء رقم عشوائي في الخليه C2 مع الصيغه = rand (). كما تم وصفه مسبقا، يمكنك محاكاه الطلب ل# الحصول علي البطاقه في الخليه C3 مع صيغه VLOOKUP(rand,lookup,2). (في صيغه VLOOKUP، rand هو اسم الخليه المعينه الي الخليه C3، لا تقوم الداله RAND).

عدد الوحدات المباعه هو اصغر كميه الانتاج لدينا و# الطلب. في الخليه C8، ف# حساب الخاصه بنا الايرادات تحتوي علي الصيغه MIN (الطلب انتاج،) * unit_price. في الخليه C9، ف# حساب تكلفه الانتاج اجمالي تحتوي علي الصيغه مقدار الانتاج * unit_prod_cost.

اذا كان يمكننا الحصول علي المزيد من البطاقات في الطلب، عدد الوحدات متبقيه الانتاج يساوي ناقص الطلب؛ الا متبقيه لا الوحدات. نحن حساب تكلفه التخلص الخاصه بنا في الخليه C10 تحتوي علي الصيغه unit_disp_cost * IF (مقدار الانتاج > الطلب، مقدار الانتاج – الطلب، 0). و# اخيرا، في الخليه C11، يمكننا حساب الخاصه بنا الربح ك# الايرادات – total_var_cost-total_disposing_cost.

يمكننا ك# طريقه فعاله ل# اضغط علي F9 عده مرات (علي سبيل المثال، 1000) ل# كل كميه الانتاج و# مطابقه الخاصه بنا الارباح المتوقعه ل# كل الكميه. يعتبر هذا الموقف احد حيث ياتي جدول بيانات ثنائي الاتجاه ل# مساعدتكم الخاصه بنا. (راجع الفصل 15, "الحساسيه تحليل ب# استخدام جداول البيانات،" ل# الحصول علي تفاصيل حول جداول البيانات.) الجدول البيانات المستخدمه في هذا المثال الموضح في الشكل 60-5.

صورة الكتاب
جدول بيانات ثنائي الاتجاه الشكل 60-5 ل# محاكاه بطاقه ترحيب

في نطاق الخلايا A16:A1015، ادخل الارقام 1 – 1000 (المقابله ل# الاصدارات التجريبيه من 1000 الخاصه بنا). يعتبر واحد طريقه سهله ل# انشاء هذه القيم ل# البدء ب# ادخال 1 في الخليه A16. حدد الخليه، ثم علي علامه التبويب الصفحه الرئيسيه في المجموعه تحرير، انقر فوق تعبئه، و# حدد سلسله ل# عرض مربع الحوار السلسله. في مربع الحوار السلسله، هو مبين في الرسم التوضيحي 60-6، ادخل قيمه الخطوه 1 و# قيمه التوقف عن 1000. في ناحيه السلسله في، حدد الخيار الاعمده، و# ثم انقر فوق موافق. سوف يظهر الارقام 1 – 1000 التي تم ادخالها في العمود بدء في الخليه A16.

صورة الكتاب
-6 60 الشكل استخدام مربع الحوار سلسله ل# تعبئه الارقام الاصدار التجريبي من 1 الي 1000

بعد ذلك نحن ادخل كميات الانتاج المحتمله الخاصه بنا (10000، 20000، 40.000، 60000) في الخلايا B15:E15. اننا نريد ان حساب الارباح ل# كل رقم الاصدار التجريبي (من 1 الي 1000) و# كل كميه الانتاج. نحن الاشاره الي ان صيغه الارباح (الخليه المحسوبه في C11) في الخليه العلويه اليمني من جدول البيانات الخاصه بنا (A15) ب# ادخال = C11.

نحن اصبحت الان جاهزا ل# الدوره Excel في محاكاه التكرارات 1000 ل# الطلب ل# كل كميه الانتاج. حدد نطاق الجدول (A15:E1014)، و# في المجموعه "ادوات البيانات" ضمن علامه التبويب بيانات، انقر فوق تحليل اذا What،، ثم حدد "جدول البيانات". ل# اعداد جدول بيانات ثنائي الاتجاه، اختر الخاصه بنا كميه الانتاج ك# "خليه ادخال الصف" (الخليه C1) و# حدد اي خليه فارغه (لقد اخترنا الخليه I14) ك# "خليه ادخال العمود". بعد النقر فوق موافق، يحاكي Excel قيم الطلب 1000 ل# كل كميه الامر.

ل# فهم سبب القيام ب# ذلك، خذ في الاعتبار القيم موضوعه ب# واسطه جدول البيانات في نطاق الخلايا C16:C1015. ل# كل من هذه الخلايا، سيستخدم Excel قيمه 20000 في الخليه C1. في C16، يتم وضع قيمه خليه ادخال العمود 1 في خليه فارغه و# رقم عشوائي في الخليه C2 اعاده حساب. يتم تسجيله الارباح المناظر في الخليه C16. ثم يتم وضع قيمه عمود الخليه الادخال 2 في خليه فارغه، و# اعاده حساب رقم عشوائي في الخليه C2 مره اخري. يتم ادخال الارباح المناظر في الخليه C17.

من خلال نسخ من الخليه B13 الي C13:E13 الصيغه AVERAGE(B16:B1015)، يمكننا حساب متوسط الارباح تمت محاكاتها ل# كل كميه الانتاج. من خلال نسخ من الخليه B14 الي C14:E14 الصيغه STDEV(B16:B1015)، يمكننا حساب الانحراف المعياري الخاصه بنا الارباح تمت محاكاتها ل# كل كميه الامر. كلما نحن اضغط علي F9، تتم محاكاه تكرارات 1000 من الطلب ل# كل كميه الامر. انتاج بطاقات 40000 دائما تعطي الارباح المتوقعه الاكبر. وب# التالي، يبدو ان انتاج بطاقات 40000 قرار عاديه.

تاثير المخاطر علي الخاصه بنا القرار     اذا نحن انتاج 20000 بدلا من بطاقات 40000، يسقط الخاصه بنا الارباح المتوقعه النسبه المئويه 22 تقريبا، و# لكن الخطر الخاصه بنا (كما هو محدد ب# الانحراف المعياري ل# الارباح) يسقط النسبه المئويه 73 تقريبا. ل# ذلك، في حاله نحن تتجنب جدا ل# المخاطره، انتاج بطاقات 20000 قد يكون القرار الصحيح. في حاله حدوث خاطء، يتضمن انتاج بطاقات 10000 دائما انحراف معياري من بطاقات 0 ل# ان اذا نحن انتاج بطاقات 10000، يمكننا سيؤدي دائما البيع جميعا دون اي بقايا الطعام.

ملاحظة: في هذا المصنف، يتم تعيين خيار الحساب الي تلقائي ب# استثناء ل# جداول. (استخدم الامر العمليات الحسابيه في المجموعه حساب ضمن علامه التبويب صيغ.) يضمن هذا الاعداد ان جدول البيانات الخاص بنا عدم اعاده الحساب الا اننا اضغط علي F9، و# فكره جيده ل# ان جدول بيانات كبيره سيؤدي الي ابطاء العمل اذا كانت اعاده حساب كل مره كتبت شيئا الي ورقه عمل. لاحظ ان في هذا المثال، عندما تضغط علي المفتاح F9، الارباح الوسط سيؤدي الي تغيير. يحدث هذا ل# ان كل مره تضغط علي المفتاح F9، يتم استخدام تسلسل مختلفه من 1000 ارقام عشوائيه ل# انشاء المطالبات ل# كل كميه الامر.

فاصل الثقه ل# الوسط الارباح     يتم سؤال طبيعي ل# طلب في هذه الحاله، الي ما هي الفاصل الزمني ل# يتم نحن 95 ب# المائه من سيتم تقع الربح true قيمه الوسيطه mean؟ يسمي هذا الفاصل الزمني ل# النسبه المئويه 95 فاصل الثقه ل# قيمه الوسيطه mean الارباح. فاصل ثقه 95 ب# المائه ل# الوسط ل# اي اخراج محاكاه تحتسب ب# واسطه الصيغه التاليه:

صورة الكتاب

في الخليه J11، يتم حساب الحد الادني ل# فاصل الثقه 95 ب# المائه علي الارباح الوسط عندما يتم انتاج 40000 التقويمات ب# استخدام صيغه D13–1.96*D14/SQRT(1000). في الخليه J12، ف# حساب الحد الاعلي ل# الخاصه بنا فاصل الثقه 95 ب# المائه تحتوي علي الصيغه D13+1.96*D14/SQRT(1000). يتم عرض هذه الحسابات في الرسم التوضيحي 60-7.

صورة الكتاب
الشكل 60-7 95 ب# المائه فاصل الثقه ل# قيمه الوسيطه mean الارباح عندما يتم ترتيب تقويمات 40000

نحن يتم من بين $56,687 و $62,589 الخاصه بنا الارباح الوسط عندما يتم ترتيب تقويمات 40000 95 ب# المائه.

  1. ب# موزع جمك تعتقد انه سيتم توزيع الطلب علي انفيس 2005 مع وسط 200 و# الانحراف المعياري ل# كل 30 ب# شكل طبيعي. تكلفه به ل# تلقي الارسال 25000 $، و# التي يعمل يبيع الارسال ل 40000 $. يمكن بيع نصف كافه انفيس غير المبيعه سعر الكامل ل 30000 $. يقوم تدرس ترتيب 200 220، 240، 260، 280 او انفيس 300. ما هو عدد يجب ان يقوم ترتيب؟

  2. يحاول ماركت صغيره ل# تحديد عدد النسخ الاشخاص المجلات التي يجب ترتيب كل اسبوع. تعتقد انها يخضع الطلب الخاصه بها ل# الاشخاص الي متغير عشوائي منفصله ما يلي:

    الطلب

    الاحتمال

    15

    0.10

    20

    0.20

    25

    0.30

    30

    0,25

    35

    0.15

  3. ماركت يستحق عنها 1.00 دولارا ل# كل نسخه من الاشخاص و# يبيع ل $1.95. يمكن ارجاع كل نسخه غير مبيع ل $0.50. عدد النسخ الاشخاص الذين يجب ان المتجر ترتيب؟

هل تحتاج إلى مزيد من المساعدة؟

ويمكنك دائماً الاستفسار من أحد الخبراء في مجتمع Excel‏ التقني،أو الحصول على الدعم من خلال إجابات المجتمع،أو اقتراح ميزة جديدة أو تحسين عن طريق ‏Excel User Voice‏.

تعزيز مهارات Office
استكشاف التدريب
الحصول على الميزات الجديدة أولاً
الانضمام إلى Office Insider

هل كانت المعلومات مفيدة؟

نشكرك على ملاحظاتك!

شكراً لك على الملاحظات! يبدو أنه من المفيد إيصالك بأحد وكلاء دعم Office لدينا.

×