حساب معامل التحديد في Microsoft Excel

أحد المؤشرات التي تصف جودة النموذج الذي تم إنشاؤه في الإحصاء هو معامل التحديد (R ^ 2) ، والذي يسمى أيضًا قيمة موثوقية التقريب. مع مساعدته ، يمكنك تحديد مستوى دقة التوقعات. دعونا معرفة كيفية حساب هذا المؤشر باستخدام أدوات Excel المختلفة.

حساب معامل التحديد

اعتمادًا على مستوى معامل التحديد ، من الشائع تقسيم النماذج إلى ثلاث مجموعات:

• 0،8 - 1 - نموذج من نوعية جيدة ؛
• 0،5 - 0،8 - نموذج الجودة المقبولة ؛
• 0 - 0.5 - نموذج رديء الجودة.

في الحالة الأخيرة ، تشير جودة النموذج إلى أنه لا يمكن استخدامه للتنبؤ.

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

الطريقة الأولى: حساب معامل التحديد للوظيفة الخطية

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

1. حدد الخلية حيث سيتم إخراج معامل التحديد بعد حسابها ، وانقر فوق رمز "إدراج الدالة" .
2. يبدأ معالج الدالة . ننتقل إلى فئتها "الإحصائية" وعلامة الاسم "KVPIRSON" . ثم انقر على زر "موافق" .
3. يتم تشغيل نافذة الوسائط لوظيفة KVPIRSON . يستخدم هذا المشغل من المجموعة الإحصائية لحساب مربع معامل الارتباط لوظيفة Pearson ، أي الدالة الخطية. وكما نتذكر ، مع دالة خطية ، فإن معامل التحديد يساوي بالضبط مربع معامل الارتباط.

   بناء جملة هذا البيان هو:

   =КВПИРСОН(известные_значения_y;известные_значения_x)

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

   اضبط المؤشر في الحقل "اسم المستخدم" . نضغط على زر الماوس الأيسر ونحدد محتويات العمود "Y" بالجدول. كما ترى ، يتم عرض عنوان مصفوفة البيانات المحددة على الفور في النافذة.

   وبالمثل ، نملأ الحقل "القيم المعروفة لـ x" . نضع المؤشر في هذا الحقل ، لكننا نختار هذه المرة قيم العمود "X" .

   بعد عرض جميع البيانات في نافذة الوسيطة KVPIRSON ، انقر فوق الزر "موافق" الموجود في أسفلها.

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

الدرس: معالج الدالة في Microsoft Excel

الطريقة الثانية: حساب معامل التحديد في الدالات غير الخطية

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

1. ولكن قبل استخدام الأداة المحددة ، يجب عليك تنشيط "حزمة التحليل" نفسها ، والتي يتم تعطيلها افتراضيًا في Excel. ننتقل إلى علامة التبويب "ملف" ، ثم ننتقل إلى "خيارات" .
2. في النافذة التي تفتح ، ننتقل إلى قسم "الإضافات" عن طريق التنقل خلال القائمة الرأسية اليسرى. يقع حقل "الإدارة" في الجزء السفلي من الجزء الأيمن من النافذة. من قائمة الأقسام الفرعية المتاحة ، حدد الاسم "Excel الوظائف الإضافية ..." ، ثم انقر فوق الزر " انتقال ..." إلى يمين الحقل.
3. يتم تشغيل نافذة الوظيفة الإضافية. في الجزء المركزي منه توجد قائمة الوظائف الإضافية المتوفرة. حدد المربع المجاور لعنصر "حزمة التحليل" . بعد ذلك ، انقر على زر "موافق" على الجانب الأيمن من واجهة النافذة.
4. سيتم تنشيط حزمة أداة "تحليل البيانات" في المثيل الحالي لـ Excel. يقع الوصول إليه على الشريط في علامة التبويب "البيانات" . الانتقال إلى علامة التبويب المحددة والنقر على زر "تحليل البيانات" في مجموعة "تحليل" الإعدادات.
5. يتم تنشيط نافذة "تحليل البيانات" مع قائمة بأدوات معالجة معلومات الملف الشخصي. نختار العنصر "الانحدار" من هذه القائمة وننقر على زر "موافق" .
6. ثم يفتح نافذة أداة الانحدار . أول كتلة من الإعدادات هي "بيانات الإدخال" . هنا ، في حقلين ، تحتاج إلى تحديد عناوين النطاقات حيث توجد قيم الوسيطة والدالة. وضعنا المؤشر في الحقل "Input interval Y" وحدد محتويات العمود "Y" على الورقة. بعد عرض عنوان الصفيف في نافذة "الانحدار" ، ضع المؤشر في الحقل "Input interval Y" وحدد خلايا العمود "X" بالطريقة نفسها.

   حول معلمات "التصنيف" و "ثابت الصفرية" ، لا نضبط مربعات الاختيار. يمكن تعيين خانة الاختيار بالقرب من معلمة "مستوى الموثوقية" وفي الحقل المقابل يمكنك تحديد القيمة المطلوبة للمؤشر المقابل (95٪ بشكل افتراضي).

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

   • منطقة على الورقة الحالية ؛
   • ورقة أخرى
   • كتاب آخر (ملف جديد).

   سنتوقف عن الخيار الأول ، بحيث يتم وضع البيانات الأصلية والنتيجة في ورقة عمل واحدة. ضع المفتاح حول المعلمة "Output interval" . في الحقل المقابل لهذا البند ، نضع المؤشر. نضغط زر الماوس الأيسر على العنصر الفارغ الموجود على الورقة ، والذي يهدف إلى أن يصبح الخلية العلوية اليسرى لجدول الإخراج لنتائج الحساب. يجب تمييز عنوان هذا العنصر في مربع "الانحدار" .

   يتم تجاهل مجموعات المعلمة "Remains" و "Normal Probability" ، حيث أنها ليست مهمة لحل هذه المهمة. بعد ذلك ، انقر فوق الزر "موافق" ، الموجود في الزاوية اليمنى العليا من نافذة "الانحدار" .

7. يحسب البرنامج على أساس البيانات التي تم إدخالها مسبقا وينتج النتيجة إلى النطاق المحدد. كما ترى ، تعرض هذه الأداة عددًا كبيرًا من النتائج على معلمات مختلفة على الورقة. ولكن في سياق الدرس الحالي ، نحن مهتمون بمؤشر "R-square" . في هذه الحالة ، يكون الرقم 0.947664 ، الذي يميز النموذج المختار كنموذج ذو نوعية جيدة.

الطريقة الثالثة: تحديد المعامل لخط الاتجاه

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

1. لدينا رسم بياني يستند إلى جدول الوسائط وقيم الدالة التي تم استخدامها للمثال السابق. دعونا نبني خط اتجاه لها. انقر فوق أي مكان في منطقة البناء حيث يتم وضع الرسم البياني باستخدام زر الماوس الأيسر. في هذه الحالة ، تظهر مجموعة إضافية من علامات التبويب على الشريط - "العمل مع المخططات البيانية" . انتقل إلى علامة التبويب "تخطيط" . نضغط على زر "خط الاتجاه" ، الموجود في مربع أداة "التحليل" . تظهر قائمة تظهر نوع خط الاتجاه. نوقف الاختيار على النوع الذي يتوافق مع مهمة محددة. دعنا نختار خيار "التقريب الأسي" لمثالنا .
2. ينشئ Excel خط اتجاه مباشرة على مستوى الرسم البياني في شكل منحنى أسود إضافي.
3. الآن مهمتنا هي عرض معامل التحديد الفعلي. انقر بزر الماوس الأيمن على خط الاتجاه. يتم تنشيط قائمة السياق. نوقف الاختيار في هذا البند على "تنسيق خط الاتجاه ..." .

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

4. بعد أي من الإجراءين المذكورين أعلاه ، يتم تشغيل نافذة تنسيق يمكن من خلالها إجراء إعدادات إضافية. على وجه الخصوص ، لإنجاز مهمتنا ، نحتاج إلى تحديد المربع بجوار "ضع قيمة دقة التقريب (R ^ 2) على الرسم التخطيطي" . وهي تقع في قاع النافذة. وهذا هو ، في هذه الطريقة نقوم بتضمين تعيين معامل التحديد لمنطقة البناء. ثم لا تنس النقر على زر "إغلاق" في أسفل النافذة الحالية.
5. سيتم عرض قيمة موثوقية التقريب ، أي قيمة معامل التحديد ، على الورقة في منطقة البناء. في هذه الحالة ، هذه القيمة ، كما نرى ، هي 0.9242 ، والتي تميز التقريب ، كنموذج ذو نوعية جيدة.
6. بشكل مطلق بهذه الطريقة ، يمكنك ضبط عرض معامل التحديد لأي نوع آخر من خطوط الاتجاه. يمكنك تغيير نوع خط الاتجاه بالانتقال عبر الزر الموجود على الشريط أو قائمة السياق في نافذة المعلمات ، كما هو موضح أعلاه. بعد ذلك ، يمكنك التبديل إلى نوع آخر في مجموعة "Trend Line" في النافذة نفسها. لا ننسى التحكم ، على سبيل المثال ، في تحديد مربع الاختيار الموجود بجوار "وضع قيمة دقة التقريب" في الرسم التخطيطي . بعد الانتهاء من الخطوات المذكورة أعلاه ، انقر فوق الزر "إغلاق" في الزاوية اليمنى السفلى من النافذة.
7. من خلال النوع الخطي ، فإن خط الاتجاه لديه بالفعل قيمة ثقة للتقييم تقريبًا تساوي 0.9477 ، والتي تميز هذا النموذج ، حتى أنها أكثر موثوقية من خط الاتجاه للنمط الأسي الذي رأيناه سابقًا.
8. وهكذا ، فإن التبديل بين أنواع مختلفة من خطوط الاتجاه ومقارنة قيم موثوقية التقريب (معامل التحديد) ، يمكن للمرء أن يجد البديل الذي يصف النموذج الخاص به بدقة الرسم البياني المعروض. سيكون البديل ذو المؤشر الأعلى لمعامل التحديد هو الأكثر موثوقية. على أساسها ، يمكنك بناء توقعات أكثر دقة.

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

   ولكن ، في الوقت نفسه ، لا يعني ذلك أن هذا النوع من خطوط الاتجاه هو الأكثر موثوقية في رسم بياني آخر. يعتمد الاختيار الأمثل لنوع خط الاتجاه على نوع الوظيفة التي تم بناء الرسم البياني على أساسها. إذا لم يكن لدى المستخدم ما يكفي من المعرفة "لتخمين" الخيار الأفضل ، فإن الطريقة الوحيدة للخروج من تحديد أفضل التنبؤ هي مقارنة معاملات التحديد ، كما هو موضح في المثال أعلاه.

اقرأ أيضا:
بناء خط الاتجاه في إكسل
التقريب في Excel

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

• ← تغيير حجم الخلايا في Microsoft Excel
• تحديد معامل الارتباط المتعدد في MS Excel →