التوزيع الطويل في Excel (الصيغة ، الأمثلة) | كيف تستعمل؟
في الإحصاء لدينا مصطلح يسمى التوزيع اللوغاريتمي الطبيعي والذي يتم حسابه لمعرفة توزيع المتغير الذي يتم توزيع لوغاريتمه بشكل طبيعي ، الصيغة الأصلية هي صيغة معقدة للغاية لحسابها ولكن في Excel لدينا وظيفة مضمنة لحساب اللوغاريتم الطبيعي التوزيع الذي دالة Lognorm.Dist.
ما هو التوزيع اللوغاريتمي في إكسيل
يُرجع التوزيع اللوغاريتمي توزيعًا إحصائيًا مستمرًا لمتغير عشوائي يتم توزيعه بشكل طبيعي لوغاريتم. فيما يلي أنواع الوظائف اللوغاريتمية المستخدمة في Excel: -
صيغة LOGNORM.DIST
يتم تعريف صيغة دالة التوزيع على أنها LOGNORM.DIST (x، mean، standard_dev، cumulative) في Excel والتي تُرجع التوزيع اللوغاريتمي الطبيعي لـ x ، مع معلمات معينة للمتوسط والانحراف المعياري للوغاريتم الطبيعي ، Ln (x). تتطلب الوظيفة أعلاه المعلمات أو الوسائط التالية: -
- x: - القيمة المطلوبة لـ "x" التي سيتم إرجاع التوزيع اللوغاريتمي الطبيعي الخاص بها.
- يعني: - يعني Ln (x)
- Standard_dev: - الانحراف المعياري لـ Ln (x)
- تراكمي: - إذا كان التراكمي هو TRUE ، فإن الدالة ترجع التوزيع التراكمي ، وإلا ستعطي FALSE كثافة الاحتمال.
دالة التوزيع التراكمي (CDF) هي متغير احتمالي يأخذ قيمة أقل من تساوي x. في حين أن دالة كثافة الاحتمال (PDF) لمتغير عشوائي مستمر تشرح الاحتمال النسبي للمتغير العشوائي x ليأخذ قيمة معينة.
كما أن LOGNORM.DIST مفيد بشكل عام في تحليل أسعار الأسهم حيث لا يمكن تطبيق التوزيع الطبيعي لحساب سعر الأسهم. يمكن أيضًا استخدام الوظيفة لحساب تسعير الخيار لنموذج Black Scholes.
حساب التوزيع اللوغاريتمي الطبيعي لمعلمات Excel
دعونا نستعرض بعض الأمثلة للتوزيع اللوغاريتمي الطبيعي المستخدم في برنامج Excel.
يمكنك تنزيل قالب Excel لتوزيع السجل الطبيعي من هنا - قالب Excel لتوزيع السجل الطبيعيضع في اعتبارك أقل من سعر سهم الشركات المدرجة للوصول إلى متوسط الانحراف المعياري ومعلمات التفوق.
الخطوة 1: - الآن احسب قيم اللوغاريتم الطبيعي لأسعار الأسهم المعنية.
كما يتضح من البيانات أعلاه ، ترجع = LN (رقم) قيمة اللوغاريتم الطبيعي للرقم المحدد.
الخطوة 2: - بعد ذلك احسب القيم التربيعية لأرقام اللوغاريتم الطبيعي ، كما هو موضح في الجدول أدناه.
الخطوة 3: - الآن سنطلب أيضًا مجموع اللوغاريتم الطبيعي لسعر السهم ومجموع قيم اللوغاريتم الطبيعي التربيعي لحساب الانحراف المعياري.
الخطوة 4: - بعد ذلك احسب متوسط اللوغاريتم الطبيعي لسعر السهم.
يعني ، µ = (5.97 + 5.99 + 6.21 + 6.54) / 4
أو µ = 6.18
الخطوة 5: - يمكن حساب الانحراف المعياري يدويًا وباستخدام صيغة إكسل المباشرة.
يوجد أدناه جدول قيم الانحراف المتوسط والمعياري لسعر السهم.
يتم حساب الانحراف المعياري باستخدام = STDEV.S (نطاق عمود اللوغاريتم الطبيعي ln (سعر السهم)).
ومع ذلك ، يمكن استخدام المعلمات المذكورة أعلاه للانحراف المعياري والمتوسط لحساب التوزيع اللوغاريتمي الطبيعي للتفوق لأي قيمة معينة "X" ، أو سعر السهم. شرح لنفسه مبين أدناه.
الخطوة 1: - ضع في اعتبارك الجدول أدناه لفهم وظيفة LOGNORM.DIST
يوضح الجدول أعلاه قيم المعلمات المطلوبة لحساب التوزيع الطبيعي لسجل Excel لـ x ، وهو 10.
الخطوة 2: - سنقوم الآن بإدخال القيم في دالة الصيغة للوصول إلى النتيجة ، عن طريق تحديد الوسيطات B2 ، و B3 ، و B4 ، وسيكون للمعامل التراكمي خياري TRUE و FALSE ليتم تحديدهما.
LOGNORM.DIST (x، mean، standard_dev، تراكمي)
كما هو موضح في لقطة الشاشة أعلاه ، سيتم إدخال خيار TRUE أولاً للحصول على دالة التوزيع التراكمي.
وبذلك نصل إلى القيمة كما هو موضح في الخلية C19 لوظيفة التوزيع التراكمي (CDF).
الخطوة 3: الآن دعونا نحسب التوزيع اللوغاريتمي الطبيعي في Excel لوظيفة كثافة الاحتمال (PDF) عن طريق تحديد نفس الوسيطة B2 و B3 و B4 و FALSE في المعامل التراكمي.
كما هو موضح في الصورة أعلاه ، نصل إلى النتيجة في الخلية C20 لوظيفة كثافة الاحتمال (PDF).
الخطوة 4: - كما رأينا في الوظيفة أعلاه ، يتوافق LOGNORM.DIST مع إصدار Excel 2010 والإصدارات الأحدث. ومع ذلك ، يمكننا أيضًا استخدام LOGNORMDIST الذي يستخدم نفس المعلمات المستخدمة في الإصدارات الأحدث. بالنظر إلى قيم المعلمات نفسها ، سنقوم بملء وظيفة LOGNORMDIST كما هو موضح أدناه.
كما يمكن رؤيته ، نتج عن القيمة نفس الشكل مثل LOGNORM.DIST للمعلمة TRUE في الوسيطة التراكمية.
أشياء يجب تذكرها حول التوزيع اللوغاريتمي في Excel
- إذا كانت أي معلمة أو وسيطة غير رقمية ، فإن التوزيع اللوغاريتمي الطبيعي يتفوق ، ستُرجع الدالة #VALUE! رسالة خطأ.
- إذا كانت الوسيطات x أقل من وتساوي 0 أو إذا كان الانحراف المعياري أقل من 0 ويساوي ، فستُرجع الدالة #NUM! رسالة خطأ.
- التعبير المكافئ لحساب LOGNORM.DIST هو LOGNORM.DIST (x، mean، standard_dev) = NORM.SDIST ((ln (x) -mean) / standard_dev)
- هذه الوظيفة متوافقة مع الإصدار 2010 والإصدارات الأحدث ، في الإصدار 2007 والإصدارات الأقدم ، يجب استخدام LOGNORMDIST (x ، mean ، standard_dev) ، والذي يقوم بإرجاع التوزيع اللوغاريتمي الطبيعي التراكمي لـ x ، حيث يتم توزيع ln (x) عادةً مع متوسط المعلمات / الوسائط و قياسي
