مدونة النائب للعلوم والتكنلوجيا

دوال الاكسل المالية للاستثمار والاقتراض

عزيزي القارئ يعتبر مجال العلوم المالية من المجالات العلمية التطبيقية التي تعتمد على تحليل كم هائل من البيانات وإجراء العمليات الحسابية البسيطة والمعقدة والتي تتصف بشيء من التكرارية وتأخذ وقتاً كبيراً وجهداً من العاملين في هذا المجال، فان برنامج الاكسل Excel يعتبر من أفضل البرمجيات التي تلبي متطلبات العاملين في هذا المجال فيما إذا تمكنوا من تعلم آلية التعامل معه وأسلوب إدخال البيانات ومعالجتها وتحليلها واستخلاص المعلومات اللازمة لاتخاذ القرارات المالية.

يتضمن مايكروسوفت اكسل أكثر من 55 دالة مالية جاهزة، والتي تساعد الإداري في معالجة بياناته واتخاذ القرارات بالسرعة والدقة اللازمة، وتأتى أهمية هذه الدوال في أنها تخفف على المستخدم عبئ حفظ وتذكر كيفية استخدام وحساب العلاقات المالية وخاصة العلاقات التي تحتاج إلى حسابات طويلة ومعقدة، إذ يطلب الحاسوب من المستخدم عند إدراج دالة مالية معينة تحديد أماكن البيانات اللازمة لتطبيق الدالة (الخلايا التي تضم البيانات التي سنعالجها) من خلال صناديق الحوار التي تظهر على شاشة الحاسوب.

لمزيد من المعلومات عن الأكسل:

عزيزي القارئ سنشرح في مقالات أخرى بعض هذه الدوال المتعلقة بالاستثمار والقروض وهي: RATE، PMT، PV, FV، NPER وهذه الدوال موجودة ضمن فئة الدوال “مالية Financial”

للتعلم آلية التعامل مع دوال الإكسل

اما في هذا المقال فسنستعرض بعض المفاهيم والمصطلحات الضرورية التي يجب عليك عزيزي القارئ التعرف عليها ان كنت من غير المختصين في مجال العلوم المالية والمصرفية، اما المختصين فيمكنهم تخطي هذا المقال الى المقالات التي تشرح الدوال الآتية:

الدالة RATE لحساب معدل الفائدة بالفترة الزمنية لقرض أو استثمار
الدالة PMT لحساب قيمة القسط اللازم دفعه لتسديد قرض
الدالة FV لحساب القيمة المستقبلية
الدالة NPER لحساب عدد الدفعات
الدالة PV لحساب القيمة الحالية

مفاهيم ومصطلحات

سنذكر فيما يلي ببعض المفاهيم والمصطلحات المالية الضرورية قبل التطرق للدوال المالية[1]:

الفائدة Interest: من وجهة نظر المستثمر (صاحب رأس المال) هي العائد أو المردود المادي الناتج عن استثمار مبلغ من المال خلال فترة زمنية محددة بمعدل فائدة سنوي محدد، وتدعى فائدة الاستثمار. أما من وجهة نظر المقتَرِض هي ثمن الأموال المقتَرَضة خلال فترة زمنية محددة بمعدل فائدة سنوي محدد، وتدعى فائدة القرض.

المبلغ المستثمر Investment Amount: هو عبارة عن المبلغ المودع أو المبلغ المقترض والذي يترتب على استخدامه تعويض مادي (الفائدة) يلتزم الشخص المدين (المقتَرِض) تجاه الدائن صاحب رأس المال (المقرِض).

معدل الفائدة Rate of Interest: هو العائد الناتج عن استثمار وحدة رأس المال في نهاية دورة زمنية، وقد جرت العادة على استخدام الدورة الزمنية فترة السنة، وكذلك استخدام مائة وحدة من النقود عند تحديد معدل الفائدة، مثال: معدل الفائدة خمسة بالمائة وتكتب 5% أو 0.05.

الدورة الزمنية The Period (Time): هي المدة التي يستحق بعدها صرف مبلغ الفائدة. فإذا كان الاتفاق بين الدائن والمدين على سداد قيمة فائدة رأس المال المستثمر في نهاية:

كل سنة فإن الدورة الزمنية هي السنة. ويكون معدل الفائدة المستخدم سنوي.
كل شهر فإن الدورة الزمنية هي الشهر. ويكون معدل الفائدة المستخدم شهري.
كل ثلاثة أشهر فإن الدورة الزمنية هي ربع سنة. ويكون معدل الفائدة المستخدم ربع سنوي.
كل ستة أشهر فإن الدورة الزمنية هي نصف سنة. ويكون معدل الفائدة المستخدم نصف سنوي.

الفائدة البسيطة Simple Interest: هي الفائدة المترتبة على أصل المبلغ المستثمر عن وحدة زمنية محددة (سنة مثلاً) يتم الاتفاق عليها بمعدل فائدة معلوم لدورة زمنية محددة، حيث يسدد مقدار الفائدة في نهاية الوحدة الزمنية ويبقى رأس المال الأصلي ثابتاً.

الفائدة المركبة Compound Interest: هي الفائدة المترتبة على المبلغ المستثمر التي تضاف إليه في نهاية كل وحدة زمنية (سنة عادة) لتستثمر من جديد وتنتج فائدة جديدة، أي أن الفائدة المركبة تتحول في نهاية كل وحدة زمنية إلى رأس مال جديد تكون قابلة للاستثمار.

القيمة الحالية Present Value: هي قيمة مبلغ الاستثمار أو القرض الآن، أي بداية فترة الاستثمار، ويرمز له بـ Pv.

القيمة المستقبلية Future Value: هي جملة مبلغ الاستثمار الحالي أو القرض الحالي بعد n سنة، أي نهاية فترة الاستثمار، ويرمز له بـ Fv.

العلاقة بين القيمة الحالية لمبلغ وقيمته المستقبلية[2]:

Fv= Pv(1+Rate)ⁿ

حيث:

Fv: القيمة المستقبلية (جملة المبلغ) لمبلغ حالي بعد n سنة.

Pv: المبلغ الحالي (رأس المال) أو المبلغ الأصلي.

Rate: معدل الفائدة السنوي.

n: مدة الاستثمار (مدة الاقتراض) وتقاس دائماً بالسنوات.

الدفعات الدورية Periodic Payments: هي دفعات متساوية منتظمة تدفع بقيم متساوية وعلى فترات زمنية متساوية، قد تكون سنوية، أو نصف سنوية، أو ربع سنوية، أو كل شهرية ..، وهذه الدفعات تأخذ قيم معينة في كل لحظة من لحظات الزمن، ولكن أهم اللحظات هما اللحظتين الآتيتين:

اللحظة الحالية: (الآن) وتسمى فيها قيمة الدفعات المتساوية بالقيمة الحالية لهذه الدفعات.
اللحظة الأخيرة: أي لحظة نهاية الفترة التي تسدد من أجلها الدفعة الأخيرة وتسمى قيمة الدفعات فيها بالقيمة المستقبلية لهذه الدفعات، أو جملة الدفعات.

أنواع الدفعات من حيث لحظة التسديد، وتقسم إلى نوعين:

الدفعات العادية Regular Payments: وهي التي تدفع في نهاية الدورة الزمنية (مثلاً السنة)، وتسمى أيضاَ بدفعات السداد، وسميت بهذا الاسم لأنه إذا حصل شخص على قرض الآن على أن يرده على أقساط فلن يكون الأمر منطقي أن يقوم بتسديد أول دفعة من أقساطه الآن، لأن هذا يعني أن المقترض لن يحصل على كامل مبلغ القرض.
الدفعات الفورية Instant Payments: وهي التي تدفع في بداية الدورة الزمنية (مثلاً السنة) وتسمى أيضاَ بدفعات الاستثمار وسميت بهذا الاسم لأنه إذا أراد شخص أن يستثمر المال في مصرف مثلاً على شكل دفعات مالية متساوية فلا يبدأ حساب الأرباح (الفوائد) إلا من تاريخ مباشرة الدفعة الأولى.

محدّدات عملية الاقتراض Determinates of borrowing process:

عندما يقترض أحد الأشخاص مبلغاً ما من أحد المصارف فإنّه عادةً يتفق على سداد القرض مع فوائده من خلال عدد من الأقساط المتساوية، وفي هذه الحالة فإنّ قيمة القرض التي يستطيع الشخص الحصول عليه تتحدّد وفقاً لقيمة القسط الذي يستطيع الشخص دفعه بشكل دوري (في نهاية أو بداية كل دورة) وعدد الأقساط التي يرغب المدين بدفعها، ومعدّل الفائدة الذي يفرضه المصرف (الدائن) على المدين، يحسب بإحدى المعادلتين الآتيتين[3]:

الدفعات عادية:

الدفعات فورية:

حيث:

Pv: قيمة القرض الذي يمكن للمدين استلامه الآن أو ما يعرف بالقيمة الحاليّة (Present Value).

PMT: قيمة القسط الدوري المتساوي الذي سيدفعه المدين للدائن لسداد القرض والفوائد وذلك في نهاية أو بداية كل دورة (Payment).

Rate: معدّل الفائدة (Interest Rate) الدوري الذي يطلبه الدائن من المدين والجدير بالذكر أنّ الدورة المتعلّقة بالمعدّل تتوافق مع الدورة المتعلّقة بالقسط فإذا كان القسط سنوياً فالمعدّل سنوي وإذا كان القسط شهرياً فالمعدّل شهري وهكذا..

Nper: عدد الدورات التي سيدفع المدين في نهاية أو بداية كل منها قسطاً، أي عدد الأقساط (Number Premium).

وبالتالي لمعرفة قيمة أياً من المحددات الأربع السابقة: (Pv أو PMT أو Rate أو Nper) يجب أن يكون معلوماً قيم باقي المحددات اعتماداً على إحدى العلاقتين السابقتين، أما باستخدام برنامج الكسل فإنه بمكننا استخدام الدوال الآتية والتي سنتناولها بالتفصيل في الفقرات اللاحقة: Pv، PMT، Rate، Nper.

محدّدات عملية الاستثمار Determinates of investment process:

عندما يريد أحد الأشخاص استثمار مبلغاً ما من أحد المصارف فإنّه عادةً يتفق على إيداع المبلغ على عدد من الدفعات المتساوية، وفي هذه الحالة فإنّ القيمة المستقبلية التي يستطيع الشخص الحصول عليه تتحدّد وفقاً لقيمة الدفعة الذي يستطيع الشخص دفعه بشكل دوري (في نهاية أو بداية كل دورة) وعدد الأقساط التي يرغب الشخص بدفعها، ومعدّل الفائدة المتفق عليه بين الشخص والمصرف، يحسب بإحدى المعادلتين الآتيتين[4]:

الدفعات عادية:

الدفعات فورية:

حيث:

Fv: القيمة المستقبلية لمبلغ أو لدفعات دوربة متساوية الذي يمكن للمستثمر استلامه في نهاية فترة الاستثمار أو ما يعرف بالقيمة المستقبلية (Future Value).

PMT: قيمة الدفعة الدورية المتساوية الذي سيدفعه المستثمر لسداد مبلغ الاستثمار وذلك في نهاية أو بداية كل دورة (Payment).

Rate: معدّل الفائدة (Interest Rate) الدوري المتفق عليه بين المستثمر والمصرف، والجدير بالذكر أنّ الدورة المتعلّقة بالمعدّل تتوافق مع الدورة المتعلّقة بالدفعة فإذا كانت الدفعة تدفع سنوياً فالمعدّل سنوي وإذا كان الدفعة تدفع شهرياً فالمعدّل شهري وهكذا..

Nper: عدد الدورات التي سيدفع المستثمر في نهاية أو بداية كل منها دفعة، أي عدد الدفعات (Number Premium).

وبالتالي لمعرفة قيمة أياً من المحددات الأربع السابقة: (Fv أو PMT أو Rate أو Nper) يجب أن يكون معلوماً قيم باقي المحددات اعتماداً على إحدى العلاقتين السابقتين، أما باستخدام برنامج الاكسل فإنه بمكننا استخدام الدوال الآتية والتي سنتناولها بالتفصيل في الفقرات اللاحقة: Fv، PMT، Rate، Nper.

تنويه: يستطيع برنامج الاكسل التمييز بين عمليّات الاستثمار والخصم (الاقتراض) المتعلّقة بمبلغ واحد وتلك المتعلّقة بعدد من المبالغ (الدفعات)، بحيث أنّه يطبّق المعادلات المبيّنة أعلاه حسب الحاجة.

ملاحظة هامة: قبل أن نشرح الدوال المالية المتعلقة بالاقتراض أو الاستثمار يجب تذكّر أمرين مهمين عند العمل على كل منها وهما:

1- التأكّد من الحفاظ على ثبات افتراضاتنا للفترات الزمنيّة الخاصّة بالمدفوعات والمدّة والمعدّل كأن نعمل وفق فترات (دفعات) شهريّة أو سنويّة.

2- من الأفضل أن نعتمد إشارة خاصّة للقيمة وذلك لبيان فيما إذا كان التدفق النقدي داخلاً أم خارجاً، ويجب أن نعطي الرصيد الأوّلي للقرض قيمة موجبة (بفرض أنّنا نحن المقترضون) لأنّه يمثّل تدفقاً نقدياً داخلاً، كما نعطي لأقساط أو دفعات القرض قيمة سالبة (بفرض أنّنا نحن أيضاً المقترضون) لأنّها تمثل تدفقاً نقدياً خارجاً، كما نلاحظ أنّ Excel يستخدم إشارات للقيم بالطريقة نفسها فهو يظهر التدفقات النقدية الداخلة بقيم موجبة والتدفقات النقدية الخارجية بقيم سالبة.

في النهاية أعزائي القراء أتمنى أن تكون هذه التدوينة مفيدة وتساعدكم لمعرفة المزيد عن الاكسل، وإلى لقاءات قادمة ومتجددة على مدونتكم مدونة النائب للعلوم والتكنلوجيا. وأريد أن أطلب منكم ألا تترددوا أبداً في طرح أي سؤال علينا في التعليقات أو من خلال صفحتنا الرسمية على الفيس بوك حيث نتواجد هناك باستمرار. وإذا كان لديكم استفسارات أخرى يمكنكم دائماً مرسلتنا عبر هذا الرابط.

وإلى لقاءٍ قريب.

[1] لمزيد من الايضاحات يمكن الرجوع إلى الكتب الرياضيات المالية

[2] كبية، محمد -2007- استخدام الحاسوب في العلوم المالية والمصرفية – مديرية الكتب والمطبوعات الجامعية – جامعة حلب- ص159

[3] دركزنلي، محمد سمير- 2016- الرياضيات المالية والتأمين – مديرية الكتب والمطبوعات الجامعية – جامعة حلب- ص119- 122.

[4] دركزنلي، محمد سمير (مرجع سابق) ص126-128

اقرأ أيضاً:

استخدام الاكسل لحساب التباين المشترك

عزيزي القارئ في هذا المقال سنتناول استخدام الاكسل لحساب التباين المشترك

تذكير:

يستخدم التباين المشترك Covariance لتحديد العلاقة بين مجموعتين من البيانات، ويساوي إلى معدل ضرب الانحرافات لكل زوج من نقاط البيانات في مجموعتين من البيانات.

ونميز بين نوعين من التباينات المشتركة:

التباين المشترك لعينة مأخوذة من مجتمع: ويحسب في الإحصاء باستخدام العلاقة:

التباين المشترك للمجتمع: ويحسب في الإحصاء باستخدام العلاقة:

أما باستخدام الاكسل فيمكن حساب التباين المشترك بواسطة إحدى الدالتين: COVARIANCE.P أو COVARIANCE.S

الدالة COVARIANCE.S

تُستخدم الدالة COVARIANCE.S في حساب التباين المشترك لعينة مأخوذة من مجتمع، والشكل العام لهذه الدالة هو:

= COVARIANCE.S (Array1;Array2)

حيث:

Array1: تمثل نطاق الخلايا المرجعية لقيم أحد المتغيرين X أو Y.

Array2: تمثل نطاق الخلايا المرجعية لقيم المتغير الآخر.

الدالة COVARIANCE.P

تُستخدم الدالة COVARIANCE.P في حساب التباين المشترك لمجتمع، والشكل العام لهذه الدالة هو:

= COVARIANCE.P (Array1;Array2)

حيث:

Array1: تمثل نطاق الخلايا المرجعية لقيم أحد المتغيرين X أو Y.

Array2: تمثل نطاق الخلايا المرجعية لقيم المتغير الآخر.

ملاحظات

1-يجب أن تكون الوسيطات إما أرقاماً أو أسماءً أو نطاق خلايا أو مراجع تحتوي على أرقام..

2- إذا احتوت وسيطة نطاق خلايا أو مرجع على نص أو قيم منطقية أو خلايا فارغة، يتم تجاهل تلك القيم، وبالرغم من ذلك، يتم تضمين الخلايا التي تحتوي على قيمة الصفر (0).

3-إذا كانت Array1 وArray2 تتضمنان عددين مختلفين من نقاط البيانات، تُرجع دالتي التباين المشترك قيمة الخطأ غير قابل للتطبيق #N/A

4- إذا كانت أي من Array1 أو Array2 فارغة أو يحتوي كل منهما على نقطة بيانات واحدة فقط، تُرجع دالتي التباين المشترك قيمة الخطأ #DIV/0!.

تطبيق عملي (1)

احسب التباين المشترك لبيانات الجدول التالي الذي يمثل الدخل والإنفاق على المعيشة لمجموعة من الأسر بآلاف الليرات السورية شهرياً، حيث Y يمثل الإنفاق على المعيشة وX يمثل دخل الأسرة.

التنفيذ:

من أجل حساب التباين المشترك على اعتبار البيانات عينة من مجتمع ندخل في الخلية E7 الدالة:

= COVARIANCE.S (C3:L3; C4:L4)

من أجل حساب التباين المشترك على اعتبار البيانات المجتمع بأكمله ندخل في الخلية E8 الدالة:

= COVARIANCE.P (C3:L3; C4:L4)

وإلى لقاءٍ قريب.

المرجع

نائب إبراهيم –كتاب “تطبيقات الحاسوب في الإدارة (باستخدام الاكسل)” –منشورات جامعة حلـب

اقرأ أيضاً:

مواضيع اخرى مفيدة

استخدام الاكسل لحساب الخطأ المعياري للتقدير

عزيزي القارئ في هذا المقال سنتناول استخدام الاكسل لحساب الخطأ المعياري للتقدير

تذكير:

يقيس الخطأ المعياري للتقدير Standard Error مدى التشتت حول مستقيم الانحدار الذي يمثل العلاقة بين متغيرين، ويحسب في الإحصاء باستخدام العلاقة:

حيث القيم التقديرية لـ Y والتي نحصل عليها من معادلة الانحدار، القيم الفعلية لـ Y، n حجم العينة..

يتضمن برنامج Microsoft Excel الدالة STEYX لحساب الخطأ المعياري للتقدير

الدالة STEYX

تُستخدم الدالة STEYX لحساب خطأ المعياري للتقدير والشكل العام لهذه الدالة هو:

= STEYX(Known_y’s; Known_x’s)

حيث:

Known_x’s: تمثل نطاق الخلايا المرجعية لقيم المتغير x.

Known_y’s: تمثل نطاق الخلايا المرجعية لقيم المتغير Y.

ملاحظات

1-يجب أن تكون بيانات الوسطاء أرقاماً أو مراجع خلايا تحتوي على أرقام.

2- إذا احتوت وسيطة نطاق خلايا أو مرجع على نص أو قيم منطقية أو خلايا فارغة، يتم تجاهل تلك القيم، وبالرغم من ذلك، يتم تضمين الخلايا التي تحتوي على قيمة الصفر (0).

3-إذا كان عدد بيانات المتغيرين (عدد خلايا الوسطاء Known_y’s و Known_x’s) غير متساوي عندئذ ترجع الدالة قيمة الخطأ غير قابل للتطبيق #N/A

4- إذا كانت أي من Known_y’s و Known_x’s فارغة أو يحتوي كل منهما على نقطة بيانات واحدة فقط، تُرجع الدالة STEYX قيمة الخطأ #DIV/0!..

تطبيق عملي (1)

احسب الخطأ المعياري للتقدير لبيانات الجدول التالي الذي يمثل الدخل والإنفاق على المعيشة لمجموعة من الأسر بآلاف الليرات السورية شهرياً، حيث Y يمثل الإنفاق على المعيشة وX يمثل دخل الأسرة.

التنفيذ:

من أجل حساب الخطأ المعياري للتقدير باستخدام الدالة STEYX ندخل في الخلية E7 الدالة:

= STEYX(C4:L4; C3:L3)

وإلى لقاءٍ قريب.

المرجع

نائب إبراهيم –كتاب “تطبيقات الحاسوب في الإدارة (باستخدام الاكسل)” –منشورات جامعة حلـب

اقرأ أيضاً:

مواضيع اخرى مفيدة

استخدام الاكسل لحساب معامل الإرتباط

عزيزي القارئ في هذا المقال سنتناول استخدام الاكسل لحساب معامل الارتباط ومعامل التحديد

تذكير:

يستخدم معامل الارتباط Correlation Coefficient لقياس كثافة العلاقة الارتباطية بين متغيرين X وY، وذلك عندما لا يكون لدينا معلومات عن أي من المتغيرين يعتبر مستقلاً أو تابعاً.

تذكير:

معاملات الارتباط هي مؤشرات لمعرفة قوة العلاقة الخطية بين متغيرين مختلفين، ويشير معامل الارتباط الخطي الأكبر من الصفر إلى وجود علاقة موجبة، والأقل من الصفر إلى وجود علاقة سالبة، وتشير القيمة صفر إلى عدم وجود علاقة بين المتغيرين. حيث: عندما r=0.4 يشير إلى علاقة إيجابية قوية

تذكير:

يُعرف معامل التحديد coefficient of determination بأنه مربع معامل الارتباط، ويُرمز إليه بـ “آر التربيعية” (R2)، ويُستخدم لتفسير التباين في متغير واحد من خلال تغيير في متغير ثانٍ، ويمكن تمثيله على أساس قيمة بين 0 و1، وكلما كانت القيمة أقرب على واحد كان التناسب أفضل.

يتضمن برنامج Microsoft Excel دالتين إحصائيتين لحساب معامل الارتباط PEARSON و CORREL. ودالة RSQ لحساب معامل التحديد

الدالةمعامل الارتباط PEARSON

تُستخدم الدالة PEARSON لحساب معامل ارتباط بيرسون والشكل العام لهذه الدالة هو:

= PEARSON(Array1; Array2)

حيث:

Array1: تمثل نطاق الخلايا المرجعية لقيم أحد المتغيرين x أو y.

Array2: تمثل نطاق الخلايا المرجعية لقيم المتغير الآخر.

دالة معامل الارتباط CORREL

تُستخدم الدالة CORREL أيضاً لحساب معامل ارتباط والشكل العام لهذه الدالة هو:

= CORREL(Array1; Array2)

حيث:

Array1: تمثل نطاق الخلايا المرجعية لقيم أحد المتغيرين x أو y.

Array2: تمثل نطاق الخلايا المرجعية لقيم المتغير الآخر.

دالة معامل التحديد RSQ

تُستخدم الدالة RSQ معامل التحديد والشكل العام لهذه الدالة هو:

= RSQ(Known_y’s; Known_x’s)

حيث:

Known_x’s: تمثل نطاق الخلايا المرجعية لقيم المتغير x.

Known_y’s: تمثل نطاق الخلايا المرجعية لقيم المتغير Y.

ملاحظات حول الدوال RSQ – PEARSONE -CORREL

1-يجب أن تكون بيانات الوسطاء أرقاماً أو مراجع خلايا تحتوي على أرقام.

2-إذا احتوت بعض خلايا نطاق الخلايا المطبق عليها الدالة على نص أو قيمة منطقية أو أنها فارغة فيتم تجاهلها.

3-إذا كان عدد بيانات المتغيرين (عدد خلايا الوسطاء Known_y’s و Known_x’s) غير متساوي عندئذ ترجع الدالة قيمة الخطأ غير قابل للتطبيق #N/A

4- ترجع الدالة قيمة الخطأ #DIV/0! إذا كان الانحراف المعياري لقيم المتغيرين المستقل والتابع (الانحراف المعياري للوسيطة Known_x’s أو للوسيطة Known_y’s) يساوي الصفر.

تطبيق عملي (1)

احسب معامل الارتباط ومعامل التحديد لبيانات الجدول التالي الذي يمثل الدخل والإنفاق على المعيشة لمجموعة من الأسر بآلاف الليرات السورية شهرياً، حيث Y يمثل الإنفاق على المعيشة وX يمثل دخل الأسرة.

التنفيذ:

من أجل حساب معامل الارتباط باستخدام الدالة PEARSONE ندخل في الخلية G7 الدالة:

= PEARSONE (C3:L3; C4:L4)

أو الدالة:

= PEARSONE (C4:L4; C3:L3)

من أجل حساب معامل الارتباط باستخدام الدالة CORREL ندخل في الخلية G8 الدالة:

= CORREL (C3:L3; C4:L4)

أو الدالة:

= CORREL (C4:L4; C3:L3)

من أجل حساب معامل التحديد باستخدام الدالة RSQ ندخل في الخلية G9 الدالة:

= RSQ (C3:L3; C4:L4)

أو الدالة:

= RSQ (C4:L4; C3:L3)

من أجل حساب معامل التحديد بتربيع معامل الارتباط ندخل في الخلية G10 الصيغة:

= G7^2

وإلى لقاءٍ قريب.

المرجع

نائب إبراهيم –كتاب “تطبيقات الحاسوب في الإدارة (باستخدام الاكسل)” –منشورات جامعة حلـب

اقرأ أيضاً:

مواضيع اخرى مفيدة

استخدام الاكسل للتنبؤ من خلال معادلة الانحدار

عزيزي القارئ تتمثل إحدى القيم المهمة لمعادلة الانحدار المقدرة في قدرتها على التنبؤ بقيم المتغير التابع Y بتأثيرات التغيير في قيمة المتغير المستقل X

في هذا المقال سنتناول استخدام الاكسل للتنبؤ بقيمة Y من أجل قيمة معينة للمتغير X بالاستناد إلى معادلة الانحدار الخطي لـ Y على X.

تذكير

تستخدم معادلة الانحدار الخطي البسيط في التنبؤ Forecasting قيمة المتغير التابع y من أجل قيمة معينة للمتغير المستقل x، والطريقة التقليدية هي تعويض قيمة x في معادلة الانحدار بعد ايجاد قيم الثوابت a وb.

يتضمن برنامج Microsoft Excel دالة إحصائية التي تقوم باالتنبؤ بقيم المتغير التابع Y بتأثيرات التغيير في قيمة المتغير المستقل X

الدالة FORECAST

تُستخدم الدالة FORECASY للتنبؤ بقيمة y من أجل قيمة معينة للمتغير x بالاستناد إلى معادلة الانحدار الخطي لـ y على x، والشكل العام لهذه الدالة هي:

= FORCAST(X;Known_y’s; Known_x’s)

حيث:

x: القيمة لـ x التي عندها نريد أن نتنبأ بقيمة المتغير التابع y.

Known_y’s: تمثل نطاق الخلايا المرجعية لقيم المتغير التابع y.

Known_x’s: تمثل نطاق الخلايا المرجعية لقيم المتغير المستقل x.

ملاحظات حول الدالة FORECAST

1-إذا كانت الوسيطة X قيمة غير رقمية فترجع الدالة قيمة الخطأ !VALUE#.

2-إذا كان عدد بيانات المتغيرين (عدد خلايا الوسطاء Known_y’s و Known_x’s) غير متساوي عندئذ ترجع الدالة قيمة الخطأ غير قابل للتطبيق N/A#

3- ترجع الدالة قيمة الخطأ !DIV/0 # إذا كان تباين المتغير المستقل (تباين الوسيطة Known_x’s) يساوي الصفر.

تطبيق عملي (1)

ليكن لدينا الجدول التالي الذي يمثل الدخل والإنفاق على المعيشة لمجموعة من الأسر بآلاف الليرات السورية شهرياً، حيث Y يمثل الإنفاق على المعيشة وX يمثل دخل الأسرة.

الدخل	30	25	35	27	32	20	23	31	37	40
الإنفاق	10	6	14	8	10	7	5	9	12	14

وكانت معادلة الانحدار الخطي البسيط هي:

Y=0.45X-4.1

والتي قمنا بحساب ثوابتها في المقال السابق كما هو موضح في الشكل الآتي:

والمطلوب ايجاد القيمة التنبؤية لـ Y عندما X=45

التنفيذ:

ندخل في الخلية D11 الدالة:

=FORECAST(45;C4:L4; C3:L3)

وتظهر النتيجة: Y=16.30، كما هو مبين في الشكل الآتي:

وإلى لقاءٍ قريب.

المرجع

نائب إبراهيم –كتاب “تطبيقات الحاسوب في الإدارة (باستخدام الاكسل)” –منشورات جامعة حلـب

اقرأ أيضاً:

مواضيع اخرى مفيدة

دوال حساب ثوابت معادلة الانحدار الخطي البسيط

عزيزي القارئ عندما تكون المعلومات الاحصائية المتوفرة تتعلق بسلوك متغيرين أو أكثر مثل: دراسة العلاقة بين دخول الافراد ونفقاتهم، أو دراسة العلاقة بين معدل الوفيات ومعدل التحضر، أو دراسة العلاقة بين عدد السياح وايراداتهم….، نقوم بدراسة وجود أم عدم وجود علاقة بينهما، وتحديد قوتها ونوعها في حال وجودها، وهل هي طردية أم عكسية وماهي هذ العلاقة هل هي علاقة انحدار خطي أم غير خطي؟

في هذا المقال سنتناول دراسة العلاقة الخطية بين المتغيرات باستخدام الاكسل.

تذكير

الانحدار Regression: هو أسلوب يمكن بواسطته تقدير قيم أحد المتغيرين بمعلومية قيم المتغير الآخر عن طريق معادلة الانحدار وله أنواع:

الانحدار الخطي البسيط Simple Linear Regression: كلمة “بسيط” تعني ان المتغير التابع Y يعتمد على متغير مستقل واحد هو X، وكلمة “خطي” تعني ان العلاقة بين المتغيرين (X,Y) علاقة خطية.

الانحدار المتعدد Multiple Regression: يعني ان المتغير التابع Y يعتمد على أكثر من متغير مستقل.

الانحدار غير الخطي Nonlinear regression: يعني ان العلاقة بين المتغير التابع Y والمتغيرات المستقلة غير خطية، كأن تكون من الدرجة الثانية او أسية أو …

تذكير:

يسمى المستقيم الذي يصف العلاقة بين متغيرين أو ظاهرتين بمستقيم الانحدار Regression Line، ويعبر عنه بمعادلة من الدرجة الأولى من الشكل الآتي:

y_i = ax_i +b

حيث:

    y يمثل المتغير التابع.

     x يمثل المتغير المستقل.

     a ثابت يسمى معامل الانحدار وهو يعبر عن ميل المستقيم، ويدل على مقدار التغير في القيم المقدرة للمتغير Y لكل وحدة تغير واحدة في المتغير المستقل X.

     b ثابت معادلة الانحدار ويمثل نقطة تقاطع خط الانحدار مع المحور العمودي في مستوي المحورين الإحداثيين، ويدل على قيمة Y عندما X تساوي الصفر.

وبالتالي لتحديد معادلة الانحدار يجب تقدير الثوابت a وb ، يمكن الرجوع الى مراجع الاحصاء للتعرف على طريقة حسابها.

بينما في هذا المقال سنتعرف على كيفية حسابها باستخدام دوال الاكسل

يتضمن برنامج Microsoft Excel مجموعة من الدوال الإحصائية التي تقوم بحساب ثوابت معادلة الانحدار الخطي البسيط والتي بموجبها نتحقق من معنوية معاملات النموذج ومن معنوية النموذج الرياضي ومدى ملاءمته لتمثيل بيانات الظواهر الاجتماعية والنفسية أو التربوية قيد الدراسة والبحث

الدالة INTERCEPT

تُستخدم الدالة INTERCEPT لحساب الثابت b في معادلة الانحدار، والشكل العام لهذه الدالة هي:

= INTERCEPT (Known_y’s; Known_x’s)

حيث:

Known_y’s: تمثل نطاق الخلايا المرجعية لقيم المتغير التابع y.

Known_x’s: تمثل نطاق الخلايا المرجعية لقيم المتغير المستقل x.

الدالة SLOPE

تُستخدم الدالة SLOPE لحساب الثابت a في معادلة الانحدار (ميل معادلة الانحدار)، والشكل العام لهذه الدالة هي:

= SLOPE(Known_y’s; Known_x’s)

حيث:

Known_y’s: تمثل نطاق الخلايا المرجعية لقيم المتغير التابع y.

Known_x’s: تمثل نطاق الخلايا المرجعية لقيم المتغير المستقل x.

ملاحظات حول الدالتين INTERCEPT وSLOPE:

1-يجب أن تكون بيانات الوسطاء أرقاماً أو مراجع خلايا تحتوي على أرقام.

2-إذا احتوت بعض خلايا نطاق الخلايا المطبق عليها الدالة على نص أو قيمة منطقية أو أنها فارغة فيتم تجاهلها.

3-إذا كان عدد بيانات المتغيرين (عدد خلايا الوسطاء Known_y’s و Known_x’s) غير متساوي عندئذ ترجع الدالة قيمة الخطأ غير قابل للتطبيق #N/A

تطبيق عملي (1)

ليكن لدينا الجدول التالي الذي يمثل الدخل والإنفاق على المعيشة لمجموعة من الأسر بآلاف الليرات السورية شهرياً، حيث Y يمثل الإنفاق على المعيشة وX يمثل دخل الأسرة، والمطلوب ايجاد معادلة مستقيم إنحدار Y على X.

الدخل	30	25	35	27	32	20	23	31	37	40
الإنفاق	10	6	14	8	10	7	5	9	12	14

التنفيذ:

يمكن تنظيم ورقة العمل كما في الشكل

لإيجاد معادلة مستقيم انحدار Y على X :

أولاً: ندخل في الخلية C6 دالة ميل معادلة الانحدار، أي:

=SLOPE (C4:L4; C3:L3)

ثانياً: ندخل في الخلية C7 دالة ثابت معادلة الانحدار، أي:

=INTERCEPT (C4:L4; C3:L3)

ثالثاً: ندخل في الخلية E9 الصيغة:

=C6

رابعاً: ندخل في الخلية C9 الصيغة:

=C7

والشكل الآتي يوضح شكل نتائج الحسابات

وإلى لقاءٍ قريب.

المرجع

نائب إبراهيم –كتاب “تطبيقات الحاسوب في الإدارة (باستخدام الاكسل)” –منشورات جامعة حلـب

اقرأ أيضاً:

مواضيع اخرى مفيدة

تصميم جداول التوزيعات التكرارية للبيانات الكمية باستخدام الاكسل

عزيزي القارئ جدول التوزيع التكراري Frequency Table هو: وسيلة لتلخيص وتنظيم البيانات الإحصائية في عدد محدود من الفئات في صورة جداول أو رسوم بيانية، تسهل طريقة فهمها بمجرد النظر إليها سواء كانت بيانات نوعية أو كمية، من خلال ترتيبها تنازلياً أو تصاعدياً وحساب مرات تكرارها.

تنويه:

من المعروف أن البيانات الإحصائية تنقسم إلى نوعين هما:

بيانات نوعية (وصفية)، مثل: الوضع الاجتماعي، المستوى التعليمي، لون الشعر، الجنس، فصيلة الدم،….

بيانات كمية، مثل: عدد الأولاد، الطول، الوزن، العمر، علامات الطلاب، ….

ناقشنا في مقال سابق آلية حساب التكرارات لبيانات نوعية، وسنناقش في هذا المقال آلية حساب التكرارارية للبيانات الكمية.

يمكن عمل أربعة أنواع من التكرارات للبيانات الكمية هي: التكرار العادي والتكرار التجميعي الصاعد والتكرار التجميعي الهابط والتكرار النسبي.

لتصميم جدول التوزيع التكراري للبيانات الكمية، اتبع الخطوات التالية:

أدخل البيانات الكميةفي نطاق من الخلايا المرجعية.
حدد عدد ومدى كل فئة (يتم ذلك حسب طبيعة البيانات وخبرة المستخدم، أو بالاعتماد على طرق إحصائية).
أدخل الحد الأعلى والأدنى لكل فئة في نطاق من الخلايا المرجعية.
لحساب التكرار العادي لكل فئة في نطاق الخلايا المرجعية المجاورة للخلايا التي تحوي الفئات، استخدام الدالة FREQUENCY كما يلي:
- حدد نطاق الخلايا المرجعية الفارغة المجاورة للخلايا التي تحوي الفئات.
- أدخل الدالة الآتية:
  FREQUENCY (Data_array; Bins_array)=
  حيث:
  Data_array: نطاق الخلايا المرجعية التي تحوي البيانات الكمية.
  Bins_array: نطاق الخلايا المرجعية التي تحوي الحد الأعلى للفئات.
- اضغط المفتاحين SHIFT+CTRL معاً مع الاستمرار والضغط على مفتاح الإدخال Enter. عندئذٍ سيمتلئ نطاق الخلايا المحدد في الخطوة الأولى بتكرارات كل فئة.
لحساب التكرار التجميعي الصاعد لكل فئة، كالآتي:
- للفئة الأولى استخدم الصيغة:
  = [مرجع الخلية التي تحوي على التكرار العادي للفئة الأولى]
- للفئة الثانية استخدم الصيغة:
  = [مرجع الخلية التي تحوي على التكرار التجميعي الصاعد للفئة الأولى] + [مرجع الخلية التي تحوي على التكرار العادي للفئة الثانية]
- ثم عمم الصيغة للفئة الثانية على باقي الفئات.
لحساب التكرار التجميعي الهابط لكل فئة، كالآتي:
- للفئة الأولى استخدم الصيغة:
  = [مرجع الخلية التي تحوي على التكرار التجميعي الصاعد للفئة الأخيرة]
  أو
  = [مرجع الخلية التي تحوي على مجموع التكرارات العادية]
- للفئة الثانية استخدم الصيغة:
  = [مرجع الخلية التي تحوي على التكرار التجميعي الهابط للفئة الأولى] – [مرجع الخلية التي تحوي على التكرار العادي للفئة الأولى]
- ثم عمم الصيغة للفئة الثانية على باقي الفئات.
لحساب التكرار النسبي لكل فئة استخدام الصيغة:
- = التكرار العادي للفئة / مجموع التكرارات العادية

تطبيق عملي (1): احسب التكرارات العادية لبيانات 100 استمارة إحصائية تتضمن علامات الطلاب في مقرر تطبيقات الحاسوب وهي:

بيانات 100 استمارة احصائية عن علامات الطلاب في مقرر تطبيقات الحاسوب
20	66	43	43	76	20	66	43	43	76
30	43	56	78	50	30	43	56	78	50
43	78	56	53	60	43	78	56	53	60
56	53	76	45	60	56	53	76	45	60
56	45	100	60	56	56	45	50	60	56
76	89	60	60	56	3	89	60	60	56
86	90	89	43	76	86	90	89	43	76
43	10	90	20	66	43	10	90	20	66
89	89	16	30	43	89	89	16	30	43
90	90	61	45	29	90	90	61	45	29

التنفيذ:

أولاً: نقوم بادخال البيانات الى ورقة العمل في الاكسل كما في الشكل التالي:

تنويه:

لاحظ أنه أخذنا عشرة فئات ومدى كل فئة هو 10، وهي:

[0-10], ]10-20], ]20-30], ]30-40], ]40-50], ]50-60], ]60-70], ]70-80], ]80-90], ]90-100]

حيث:

أدخلنا البيانات أعلاه على صفحة الاكسل ضمن الخلايا المرجعية من A3 وحتى J12

وأدخلنا الفئات السابقة في نطاق من الخلايا المرجعية B24:A15

ثانياً: اجراءات حساب التوزيع التكرار العادي:

1- حدد نطاق الخلايا المرجعية الفارغة C24:C15.

2 – أدخل الدالة الآتية:

=FREQUENCY(A3:J12;B15:B24)

3- اضغط المفتاحين SHIFT+CTRL معاً مع الاستمرار والضغط على مفتاح الإدخال Enter، عندئذٍ سيمتلئ نطاق الخلايا C15:C24 بتكرارات كل فئة، كما في الشكل أددناه.

ثالثا: اجراءات حساب التكرارات الصاعدة:

1- في الخلية D15 اكتب الصيغة الآتية:

= C15

2- في الخلية D16 اكتب الصيغة الآتية:

= D15+C16

3- عمم الصيغة في الخلية D16 على الخلايا من D17 ولغاية D24،

سيمتلئ نطاق الخلايا D15:D24 بتكرارات كل فئة، كما في الشكل أددناه.

رابعاً: اجراءات حساب التكرارات الهابطة:

1- في الخلية E15 اكتب الصيغة الآتية:

= C25

2- في الخلية E16 اكتب الصيغة الآتية:

= E15+C15

3- عمم الصيغة في الخلية E16 على الخلايا من E17 ولغاية E24،

سيمتلئ نطاق الخلايا E15:E24 بتكرارات كل فئة، كما في الشكل أددناه.

خامساً: اجراءات حساب التكرارات النسبية:

1- في الخلية F15 اكتب الصيغة الآتية:

= C15/$C$25

2- عمم الصيغة في الخلية F15 على الخلايا من F16 ولغاية F24،

سيمتلئ نطاق الخلايا F15:F24 بتكرارات كل فئة، كما في الشكل أددناه.

تنويه: يمكن تحويل الأرقام في خلايا التكرار النسبي إلى تنسيق النسبة المئوية %، وذلك:

بتحديد خلايا النطاق B15:B25

ثم النقر على أيقونة النسبة المئوية الموجودة في مجموعة: الرقم Number” ضمن تبويب “الصفحة الرئيسية HOME”

عندئذ تأخذ بيانات النطاق كما في الشكل ادناه

وإلى لقاءٍ قريب.

المرجع

نائب إبراهيم –كتاب “تطبيقات الحاسوب في الإدارة (باستخدام الاكسل)” –منشورات جامعة حلـب

اقرأ أيضاً:

مواضيع اخرى مفيدة

تصميم جداول التوزيعات التكرارية للبيانات النوعية باستخدام الاكسل

تنويه:

من المعروف أن البيانات الإحصائية تنقسم إلى نوعين هما:

بيانات نوعية (وصفية)، مثل: الوضع الاجتماعي، المستوى التعليمي، لون الشعر، الجنس، فصيلة الدم،….

بيانات كمية، مثل: عدد الأولاد، الطول، الوزن، العمر، علامات الطلاب، ….

لتصميم جدول التوزيع التكراري للبيانات النوعية، اتبع الخطوات التالية:

أدخل البيانات الوصفية في نطاق من الخلايا المرجعية.
احصر الصفات المختلفة في البيانات الوصفية المدخلة.
أدخل الصفات المختلفة في نطاق من الخلايا المرجعية (أفقياً أو عمودياً حسب الرغبة).
أوجد تكرار كل صفة في نطاق من الخلايا المرجعية المجاورة للخلايا التي تحوي الصفات المختلفة، باستخدام الدالة COUNTIF.

تطبيق عملي (1): ليكن لدينا مئة استمارة احصائية تتضمن بيانات عن الوضع العائلي لأصحابها وهذه البيانات هي:

ارمل	مطلق	متزوج	عازب	عازب	مطلق	ارمل	متزوج	متزوج	عازب
مطلق	متزوج	عازب	عازب	متزوج	مطلق	متزوج	مطلق	عازب	ارمل
متزوج	عازب	مطلق	متزوج	ارمل	متزوج	عازب	عازب	ارمل	مطلق
عازب	مطلق	متزوج	عازب	عازب	مطلق	ارمل	متزوج	متزوج	عازب
مطلق	متزوج	عازب	عازب	متزوج	مطلق	متزوج	مطلق	عازب	ارمل
متزوج	عازب	مطلق	متزوج	ارمل	متزوج	عازب	عازب	ارمل	مطلق
عازب	مطلق	متزوج	عازب	عازب	مطلق	ارمل	متزوج	متزوج	عازب
مطلق	متزوج	عازب	عازب	متزوج	مطلق	متزوج	مطلق	عازب	ارمل
متزوج	عازب	مطلق	متزوج	ارمل	متزوج	عازب	عازب	ارمل	مطلق
عازب	مطلق	متزوج	عازب	عازب	مطلق	ارمل	متزوج	متزوج	عازب

والمطلوب: حساب عدد تكرارات كل صفة من صفات الوضع العائلي (ارمل – عازب – مطلق – متزوج).

التنفيذ:

أولاً: نقوم بادخال البيانات الى ورقة العمل في الاكسل كما في الشكل التالي:

ثانياً: نصميم جدول التوزيعات التكرارية يمكن للمستخدم تصميمه افقياً أو عمودياً

الأسلوب الأول: تصميم جدول التوزيعات التكرارية للبيانات النوعية أفقياً:

أدخل الصفات السابقة في نطاق من الخلايا المرجعية B14:E14 كما في الشكل التالي:

ثالثا: لحساب تكرارات كل صفة، أدخل الدالة الآتية في الخلية B15:

=COUNTIF($A$3:$J$12;B14)

ثم اسحب من مقبض التعبئة للخلية B15 لتعميم الدالة السابقة على الخلايا من C15 ولغاية الخلية E15.

رابعاً: احسب مجموع التكرارات في الخلية F15 باستخدام الدالة:

=SUM(B15:E15)

ويبين الشكل التالي المظهر النهائي لورقة العمل بعد تطبيق الخطوات أعلاه.

الأسلوب الثاني: تصميم جدول التوزيعات التكرارية للبيانات النوعية عمودياً:

أدخل الصفات السابقة في نطاق من الخلايا المرجعية B18:E15 كما في الشكل التالي:

ثالثا: لحساب تكرارات كل صفة، أدخل الدالة الآتية في الخلية B15:

=COUNTIF($A$3:$J$12;A15)

ثم اسحب من مقبض التعبئة للخلية B15 لتعميم الدالة السابقة على الخلايا من B16 ولغاية الخلية B18.

رابعاً: احسب مجموع التكرارات في الخلية B19 باستخدام الدالة:

=SUM(B15:B18)

ويبين الشكل التالي المظهر النهائي لورقة العمل بعد تطبيق الخطوات أعلاه.

وإلى لقاءٍ قريب.

المرجع

نائب إبراهيم –كتاب “تطبيقات الحاسوب في الإدارة (باستخدام الاكسل)” –منشورات جامعة حلـب

اقرأ أيضاً:

مواضيع اخرى مفيدة

حساب التباين Variance باستخدام الاكسل

عزيزي القارئ يتضمن برنامج Microsoft Excel مجموعة كبيرة جداً من الدوال الإحصائية التي تقوم بحساب كافة المقاييس والمؤشرات الإحصائية المتعلقة بظاهرة أو أكثر من الظواهر الاجتماعية أو النفسية أو التربوية أو المالية بمجرد كتابة الصيغة الرياضية الملاءمة للدالة وتحديد نطاق البيانات الخاصة بالظاهرة المدروسة.

عرضنا في مقالات سابقة حساب مقاييس النزعة المركزية باستخدام الاكسل [الوسط الحسابي – الوسيط – المنوال – الربيعات]

إن مقاييس النزعة المركزية غير كافية لوحدها تحديد خواص الظاهرة المدروسة بشكل جيد، ولاسيما في مجال المقارنة بين عدة مجموعات من الظواهر المدروسة، لذلك ندرس مقاييس التشتت لوصف درجة ابتعاد أو تشتت أو تفاوت القيم حول وسطها الحسابي، ومن أهم مقاييس التشتت: الانحراف المعياري والتباين.

في الاحصاء: يعرف التباين Variance لمجموعة قيم بأنه مربع الانحراف المعياري، بعبارة أخرة هو مقياس للتشتت الإحصائي للقيم الممكنة حول وسطها الحسابي. ونميز بين نوعين من التباينات:

أولاً: التباين لعينة مأخوذة من مجتمع: وهو حاصل قسمة مجموع مربعات فروق القيمة عن وسطها الحسابي على عددها ناقص واحد، ويحسب في الإحصاء باستخدام العلاقة:

ثانياً: التبياين للمجتمع بأكمله: وهو حاصل قسمة مجموع مربعات فروق القيمة عن وسطها الحسابي على عددها، ويحسب في الإحصاء باستخدام العلاقة:

في الاكسل: يوجد أربعة دوال لحساب الانحراف المعياري:

الدالة الأولى VAR.S: تُستخدم الدالة VAR.S في حساب التباين لعينة مأخوذة من مجتمع (وتتجاهل القيم المنطقية والنص في العينة)، والشكل العام لهذه الدالة هو:

= VAR.S (Number1; Number2; …)

حيث:

Number1; Number1; …: وسطاء الدالة،وتمثل نطاق الخلايا المرجعية التي تحتوي على القيم العددية (أو القيم العددية أو مراجع خلايا تحتوي على قيم عددية) المراد حساب تباينها.

تنويه:

تفترض الدالة VAR.S أن وسيطاتها تمثل عينة من مجتمع. إذا كانت بياناتك تمثل المجتمع بأكمله، فاحسب حينئذٍ التباين باستخدام VAR.P.

يتم تجاهل الخلايا الفارغة أو القيم المنطقية أو النص أو قيم الخطأ في النطاق أو مراجع الخلايا.

تتسبب الوسيطات التي تكون عبارة عن قيم خطأ أو نص لا يمكن ترجمته إلى أرقام في حدوث أخطاء.

إذا أردت تضمين قيم منطقية وتمثيلات نصية للأرقام في مرجع كجزء من العمليات الحسابية، فاستخدم الدالة VARA.

الدالة الثانية VARA: تُستخدم الدالة VARA في حساب التباين لعينة مأخوذة من مجتمع، والشكل العام لهذه الدالة هو:

= VARA (Value1; Value2; …)

حيث:

Value1; Value1; …: وسطاء الدالة،وتمثل نطاق الخلايا المرجعية التي تحتوي على البيانات المراد حساب تباينها.

تنويه:

تفترض الدالة VARA أن وسيطاتها تمثل عينة من مجتمع. إذا كانت بياناتك تمثل المجتمع بأكمله، فاحسب حينئذٍ الانحراف المعياري باستخدام VARPA.

يمكن للوسيطات أن تكون عبارة عن أرقام أو أسماء أو نطاق خلايا أو مراجع تحتوي على أرقام؛ أو تمثيلات نصية لأرقام؛ أو قيم منطقية مثل TRUE وFALSE.

يتم تقييم الوسيطات التي تحتوي على TRUE كـ 1 (واحد)، بينما يتم تقييم الوسيطات التي تحتوي على نص أو FALSE كـ 0 (صفر).

تتسبب الوسيطات التي تكون عبارة عن قيم خطأ أو نص لا يمكن ترجمته إلى أرقام في حدوث أخطاء.

إذا كنت لا تريد تضمين قيم منطقية وتمثيلات نصية للأرقام في مرجع كجزء من العملية الحسابية، فاستخدم الدالة VAR.S.

الدالة الثالثة VAR.P: تُستخدم الدالة VAR.P في حساب التباين لمجتمع (وتتجاهل القيم المنطقية والنص في العينة)، والشكل العام لهذه الدالة هو:

= VARA .P (Number1; Number2; …)

حيث:

تنويه:

تفترض الدالة VAR.P أن وسيطاتها تمثل المجتمع بأكمله. إذا كانت بياناتك تمثل عينة من مجتمع، فاحسب حينئذٍ الانحراف المعياري باستخدام VAR.S.

يتم تجاهل الخلايا الفارغة أو القيم المنطقية أو النص أو قيم الخطأ في النطاق أو مراجع الخلايا.

تتسبب الوسيطات التي تكون عبارة عن قيم خطأ أو نص لا يمكن ترجمته إلى أرقام في حدوث أخطاء.

إذا أردت تضمين قيم منطقية وتمثيلات نصية للأرقام في مرجع كجزء من العمليات الحسابية، فاستخدم الدالة VARPA.

الدالة الرابعة VARPA: تُستخدم الدالة VARPA في حساب الانحراف المعياري لمجتمع، والشكل العام لهذه الدالة هو:

= VARPA (Value1; Value2; …)

حيث:

Value1; Valuer1; …: وسطاء الدالة،وتمثل نطاق الخلايا المرجعية التي تحتوي البيانات المراد حساب تباينها.

تنويه:

تفترض الدالة VARPA أن وسيطاتها تمثل المجتمع بأكمله. وإذا كانت بياناتك تمثل عينة من المجتمع، فعليك حينئذٍ حساب الانحراف المعياري باستخدام VARA.

يمكن للوسيطات أن تكون عبارة عن أرقام أو أسماء أو نطاق خلايا أو مراجع تحتوي على أرقام؛ أو تمثيلات نصية لأرقام؛ أو قيم منطقية مثل TRUE وFALSE.

يتم تقييم الوسيطات التي تحتوي على TRUE كـ 1 (واحد)، بينما يتم تقييم الوسيطات التي تحتوي على نص أو FALSE كـ 0 (صفر).

تتسبب الوسيطات التي تكون عبارة عن قيم خطأ أو نص لا يمكن ترجمته إلى أرقام في حدوث أخطاء.

إذا كنت لا تريد تضمين قيم منطقية وتمثيلات نصية للأرقام في مرجع كجزء من العملية الحسابية، فاستخدم الدالة VAR.P.

تطبيق عملي (1): ليكن الجدول التالي والذي يبين كميات الانتاج من القمح لعدة أعوام:

العام	كمية الإنتاج من القمح / ألف طن
2005	50
2006	88
2007	92
2008	10
2009	20
2010	76
2011	79
2012	56
2013	مجهولة
2014	55
2015	65
2016	75
2017	77

والمطلوب:

انقل البيانات إلى ورقة عمل في برنامج الاكسل.
اكتب الدالة المناسبة لحساب كل من التباين لكميات الإنتاج من القمح في الحالتين: الأولى بدون تضمين الخلايا التي تحوي على نص والثانية تضمين الخلايا التي تحوي على نص، على اعتبار البيانات كعينة من مجتمع.
اكتب الدالة المناسبة لحساب كل من التباين لكميات الإنتاج من القمح في الحالتين: الأولى بدون تضمين الخلايا التي تحوي على نص والثانية تضمين الخلايا التي تحوي على نص، على اعتبار البيانات كمجتمع بأكمله.

التنفيذ:

أولاً: نقوم بادخال البيانات الى ورقة العمل في الاكسل كما في الشكل التالي:

ثانياً: لحساب التباين في الخلية E3 بدون تضمين الخلايا التي تحوي على نص، على اعتبار البيانات كعينة نكتب:

E3 = VAR.S (B2:B14)

ثالثا: لحساب التباين في الخلية E4 بدون تضمين الخلايا التي تحوي على نص، على اعتبار البيانات كمجتمع نكتب

E4 = VAR.P (B2:B14)

رابعاً: لحساب التباين في الخلية E9 مع تضمين الخلايا التي تحوي على نص، على اعتبار البيانات كعينة نكتب

E9 = VARA (B2:B14)

خامساً: لحساب التباين في الخلية E10 مع تضمين الخلايا التي تحوي على نص، على اعتبار البيانات كمجتمع نكتب:

E10 = VARPA (B2:B14)

ويبين الشكل التالي المظهر النهائي لورقة العمل بعد تطبيق الدوال أعلاه.

وإلى لقاءٍ قريب.

المرجع

نائب إبراهيم –كتاب “تطبيقات الحاسوب في الإدارة (باستخدام الاكسل)” –منشورات جامعة حلـب

اقرأ أيضاً:

مواضيع اخرى مفيدة

حساب الانحراف المعياري Standard Deviation باستخدام الاكسل

في الاحصاء: الانحراف المعياري Standard Deviation هو مقياس مدى بُعد القيم عن وسطها الحسابي. ونميز بين نوعين من الانحرافات المعيارية:

أولاً: الانحراف المعياري لعينة مأخوذة من مجتمع: وهو الجذر التربيعي الموجب لحاصل قسمة مجموع مربعات فروق القيمة عن وسطها الحسابي على عددها ناقص واحد، ويحسب في الإحصاء باستخدام العلاقة:

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

في الاكسل: يوجد أربعة دوال لحساب الانحراف المعياري:

الدالة الأولى STDEV.S: تُستخدم الدالة STDEV.S في حساب الانحراف المعياري لعينة مأخوذة من مجتمع (وتتجاهل القيم المنطقية والنص في العينة)، والشكل العام لهذه الدالة هو:

= STDEV.S (Number1; Number2; …)

حيث:

Number1; Number1; …: وسطاء الدالة،وتمثل نطاق الخلايا المرجعية التي تحتوي على القيم العددية (أو القيم العددية أو مراجع خلايا تحتوي على قيم عددية) المراد حساب انحرافها المعياري.

تنويه:

تفترض الدالة STDEV.S أن وسيطاتها تمثل عينة من مجتمع. إذا كانت بياناتك تمثل المجتمع بأكمله، فاحسب حينئذٍ الانحراف المعياري باستخدام STDEV.P.

يجب أن تكون الوسيطات إما أرقام أو أسماء أو نطاق خلايا أو مراجع تحتوي على أرقام.

يتم تجاهل الخلايا الفارغة أو القيم المنطقية أو النص أو قيم الخطأ في النطاق أو مراجع الخلايا.

تتسبب الوسيطات التي تكون عبارة عن قيم خطأ أو نص لا يمكن ترجمته إلى أرقام في حدوث أخطاء.

إذا أردت تضمين قيم منطقية وتمثيلات نصية للأرقام في مرجع كجزء من العمليات الحسابية، فاستخدم الدالة STDEVA.

الدالة الثانية STDEVA: تُستخدم الدالة STDEVA في حساب الانحراف المعياري لعينة مأخوذة من مجتمع، والشكل العام لهذه الدالة هو:

= STDEVA (Value1; Value2; …)

حيث:

Value1; Value1; …: وسطاء الدالة،وتمثل نطاق الخلايا المرجعية التي تحتوي على البيانات المراد حساب انحرافها المعياري.

تنويه:

تفترض الدالة STDEVA أن وسيطاتها تمثل عينة من مجتمع. إذا كانت بياناتك تمثل المجتمع بأكمله، فاحسب حينئذٍ الانحراف المعياري باستخدام STDEVPA.

يمكن للوسيطات أن تكون عبارة عن أرقام أو أسماء أو نطاق خلايا أو مراجع تحتوي على أرقام؛ أو تمثيلات نصية لأرقام؛ أو قيم منطقية مثل TRUE وFALSE.

يتم تقييم الوسيطات التي تحتوي على TRUE كـ 1 (واحد)، بينما يتم تقييم الوسيطات التي تحتوي على نص أو FALSE كـ 0 (صفر).

تتسبب الوسيطات التي تكون عبارة عن قيم خطأ أو نص لا يمكن ترجمته إلى أرقام في حدوث أخطاء.

إذا كنت لا تريد تضمين قيم منطقية وتمثيلات نصية للأرقام في مرجع كجزء من العملية الحسابية، فاستخدم الدالة STDEV.S.

الدالة الثالثة STDEV.P: تُستخدم الدالة STDEV.P في حساب الانحراف المعياري لمجتمع (وتتجاهل القيم المنطقية والنص في العينة)، والشكل العام لهذه الدالة هو:

= STDEV.P (Number1; Number2; …)

حيث:

تنويه:

تفترض الدالة STDEV.P أن وسيطاتها تمثل المجتمع بأكمله. إذا كانت بياناتك تمثل عينة من مجتمع، فاحسب حينئذٍ الانحراف المعياري باستخدام STDEV.S.

يجب أن تكون الوسيطات إما أرقام أو أسماء أو نطاق خلايا أو مراجع تحتوي على أرقام.

بالنسبة للعينات كبيرة الحجم، تُرجع STDEV.S وSTDEV.P قيماً متساوية تقريباً.

يتم تجاهل الخلايا الفارغة أو القيم المنطقية أو النص أو قيم الخطأ في النطاق أو مراجع الخلايا.

تتسبب الوسيطات التي تكون عبارة عن قيم خطأ أو نص لا يمكن ترجمته إلى أرقام في حدوث أخطاء.

إذا أردت تضمين قيم منطقية وتمثيلات نصية للأرقام في مرجع كجزء من العمليات الحسابية، فاستخدم الدالة STDEVPA.

الدالة الرابعة STDEVPA: تُستخدم الدالة STDEVPA في حساب الانحراف المعياري لمجتمع، والشكل العام لهذه الدالة هو:

= STDEVPA (Value1; Value2; …)

حيث:

Value1; Valuer1; …: وسطاء الدالة،وتمثل نطاق الخلايا المرجعية التي تحتوي البيانات المراد حساب انحرافها المعياري.

تنويه:

تفترض الدالة STDEVPA أن وسيطاتها تمثل المجتمع بأكمله. وإذا كانت بياناتك تمثل عينة من المجتمع، فعليك حينئذٍ حساب الانحراف المعياري باستخدام STDEVA.

بالنسبة إلى عينات كبيرة الحجم، تُرجع STDEV.S وSTDEV.P قيماً متساوية تقريباً.

يمكن للوسيطات أن تكون عبارة عن أرقام أو أسماء أو نطاق خلايا أو مراجع تحتوي على أرقام؛ أو تمثيلات نصية لأرقام؛ أو قيم منطقية مثل TRUE وFALSE.

يتم تقييم الوسيطات التي تحتوي على TRUE كـ 1 (واحد)، بينما يتم تقييم الوسيطات التي تحتوي على نص أو FALSE كـ 0 (صفر).

تتسبب الوسيطات التي تكون عبارة عن قيم خطأ أو نص لا يمكن ترجمته إلى أرقام في حدوث أخطاء.

إذا كنت لا تريد تضمين قيم منطقية وتمثيلات نصية للأرقام في مرجع كجزء من العملية الحسابية، فاستخدم الدالة STDEV.P.

تطبيق عملي (1): ليكن الجدول التالي والذي يبين كميات الانتاج من القمح لعدة أعوام:

العام	كمية الإنتاج من القمح / ألف طن
2005	50
2006	88
2007	92
2008	10
2009	20
2010	76
2011	79
2012	56
2013	مجهولة
2014	55
2015	65
2016	75
2017	77

والمطلوب:

انقل البيانات إلى ورقة عمل في برنامج الاكسل.
اكتب الدالة المناسبة لحساب كل من الانحراف المعياري لكميات الإنتاج من القمح في الحالتين: الأولى بدون تضمين الخلايا التي تحوي على نص والثانية تضمين الخلايا التي تحوي على نص، على اعتبار البيانات كعينة من مجتمع.
اكتب الدالة المناسبة لحساب كل من الانحراف المعياري لكميات الإنتاج من القمح في الحالتين: الأولى بدون تضمين الخلايا التي تحوي على نص والثانية تضمين الخلايا التي تحوي على نص، على اعتبار البيانات كمجتمع بأكمله.

التنفيذ:

أولاً: نقوم بادخال البيانات الى ورقة العمل في الاكسل كما في الشكل التالي:

ثانياً: لحساب الانحراف المعياري في الخلية E3 بدون تضمين الخلايا التي تحوي على نص، على اعتبار البيانات كعينة نكتب

E3 = STDEV.S (B2:B14)

ثالثا: لحساب الانحراف المعياري في الخلية E4 بدون تضمين الخلايا التي تحوي على نص، على اعتبار البيانات كمجتمع نكتب

E4 = STDEV.P (B2:B14)

رابعاً: لحساب الانحراف المعياري في الخلية E9 مع تضمين الخلايا التي تحوي على نص، على اعتبار البيانات كعينة نكتب

E9 = STDEVA (B2:B14)

خامساً: لحساب الانحراف المعياري في الخلية E10 مع تضمين الخلايا التي تحوي على نص، على اعتبار البيانات كمجتمع نكتب:

E10 = STDEVPA (B2:B14)

ويبين الشكل التالي المظهر النهائي لورقة العمل بعد تطبيق الدوال أعلاه.

وإلى لقاءٍ قريب.

المرجع

نائب إبراهيم –كتاب “تطبيقات الحاسوب في الإدارة (باستخدام الاكسل)” –منشورات جامعة حلـب

اقرأ أيضاً:

مواضيع اخرى مفيدة

مفاهيم ومصطلحات

شارك هذا الموضوع:

المرجع

شارك هذا الموضوع:

المرجع

شارك هذا الموضوع:

المرجع

شارك هذا الموضوع:

المرجع

شارك هذا الموضوع:

المرجع

شارك هذا الموضوع:

المرجع

شارك هذا الموضوع:

المرجع

شارك هذا الموضوع:

المرجع

شارك هذا الموضوع:

المرجع

شارك هذا الموضوع: