كيفية حساب معامل الارتباط بين متغيرين في Excel؟
نستخدم عادةً معامل الارتباط (قيمة تتراوح بين -1 و 1) للإشارة إلى قوة واتجاه العلاقة الخطية بين متغيرين. يعتبر معامل الارتباط إحصائيًا شائع الاستخدام يساعدك على فهم العلاقات مثل الارتباط بين المبيعات والمصاريف الإعلانية، درجة الحرارة ومبيعات الآيس كريم، أو بيانات مزدوجة أخرى. في Excel، هناك عدة طرق مباشرة لحساب معامل الارتباط، بما في ذلك الوظائف المدمجة وأدوات التحليل.
الطريقة أ: استخدام دالة CORREL مباشرة
الطريقة ب: تطبيق تحليل البيانات وإخراج التحليل
الطريقة ج: استخدام دالة PEARSON كبديل
الطريقة د: استخدام كود VBA لحساب معاملات الارتباط لعدة أزواج
الطريقة أ: استخدام دالة CORREL مباشرة
ضع في اعتبارك قائمتين من البيانات، تمثل كل قائمة متغيرًا. إذا كنت تريد حساب معامل الارتباط بين هذين المتغيرين في Excel، فإن هذه الطريقة تكون سريعة وفعالة.
للاستخدام العملي، تأكد من أن نطاقات البيانات لكلا المتغيرين عددية وتحتوي على نفس عدد الملاحظات. على سبيل المثال، إذا كان لديك البيانات المقترنة التالية:
حدد خلية فارغة حيث تريد عرض نتيجة الحساب. أدخل الصيغة التالية، ثم اضغط على زر "Enter" لحساب معامل الارتباط:
=CORREL(A2:A7,B2:B7)

في هذه الصيغة، A2:A7 و B2:B7 يمثلان القائمتين اللتين تريد تحليلهما. يجب أن تكون النطاقات متساوية الطول، وكل زوج يجب أن يتوافق مع نفس الملاحظة.
نصيحة عملية: تقوم الدالة CORREL تلقائيًا بتجاهل الخلايا الفارغة والنصوص، ولكن إذا لم يكن هناك أزواج عددية صحيحة في العمودين، فستعيد خطأ #DIV/0!. تأكد من أن بياناتك محاذاة بشكل صحيح وتحتوي على أزواج عددية لضمان حساب دقيق لمعامل الارتباط.
بمجرد أن تحسب معامل الارتباط، يمكنك إدراج مخطط خطي لمشاهدة العلاقات بصريًا وتفسير الارتباط بشكل أكبر، كما هو موضح أدناه:
هذه الطريقة هي الأفضل للتحقق السريع اليدوي بين مجموعتي بيانات صغيرة أو عند العمل تفاعليًا داخل جدول البيانات الخاص بك. وهي مناسبة للمستخدمين الذين يبحثون عن نتائج فورية دون الحاجة إلى مخرجات إحصائية متقدمة.

اكتشف سحر Excel مع Kutools AI
- التنفيذ الذكي: قم بعمليات الخلية، وتحليل البيانات، وإنشاء المخططات - كل ذلك يتم من خلال أوامر بسيطة.
- الصيغ المخصصة: قم بإنشاء صيغ مصممة خصيصًا لتسهيل سير عملك.
- برمجة VBA: كتابة وتطبيق أكواد VBA بسهولة.
- تفسير الصيغ: فهم الصيغ المعقدة بسهولة.
- ترجمة النصوص: تجاوز الحواجز اللغوية داخل جداول البيانات الخاصة بك.
الطريقة ب: تطبيق تحليل البيانات وإخراج التحليل
إذا كنت بحاجة إلى تحليل الارتباط بين عدة متغيرات دفعة واحدة أو تريد جدول إخراج أكثر شمولًا، توفر أداة "Analysis Toolpak" في Excel حلاً مفيدًا. هذا الملحق يولد مصفوفة ارتباط ويسمح لك بمقارنة عدة متغيرات في خطوة واحدة، وهو أمر ذو قيمة لمجموعات البيانات الكبيرة أو لإعداد التقارير الإحصائية.
1. إذا كنت قد أضفت بالفعل الملحق Data Analysis إلى علامة التبويب Data، يمكنك تخطي هذه الخطوة والانتقال إلى الخطوة 3. وإلا، انقر على ملف > خيارات. في نافذة "Excel Options"، اختر Add-Ins من الجزء الأيسر، ثم انقر على الزر اذهب بجانب مربع "Excel Add-ins".
2. في نافذة "Add-Ins"، قم بتحديد المربع المكتوب عليه Analysis ToolPak, ثم انقر على موافق. سيضيف هذا مجموعة "Data Analysis" إلى علامة التبويب Data .
3. بعد ذلك، انقر على Data > تحليل البيانات. في نافذة "Data Analysis"، اختر الارتباط من القائمة ثم انقر على موافق.
4. في مربع الحوار Correlation، قم بتخصيص التالي:
1) حدد النطاق الذي يحتوي على بياناتك.
2) اختر الخيار "الأعمدة" أو "الصفوف" بناءً على كيفية تنظيم بياناتك.
3) إذا كانت بياناتك تحتوي على عناوين، قم بتحديد الخيار “Labels in first row” (العناوين في الصف الأول).
4) حدد موقع الإخراج في “Output options” لعرض النتائج.
5. انقر على موافق لتوليد جدول تحليل الارتباط. سيتم تقديم معاملات الارتباط في النطاق المحدد.
هذه الطريقة مناسبة عندما تحتاج إلى تقييم العلاقات بين أكثر من متغيرين أو إذا كنت ترغب في الحصول على جدول ملخص لأغراض التقارير. إخراج تحليل البيانات مختصر ولكنه لا يقدم إحصائيات إضافية. إذا تلقيت نتائج غير متوقعة، تحقق مرة أخرى من بياناتك للتأكد من الاتساق، وعدم وجود خلايا فارغة واختيار النطاق الصحيح.
الطريقة ج: استخدام دالة PEARSON كبديل
بالإضافة إلى CORREL، توفر Excel دالة PEARSON التي تحسب أيضًا معامل الارتباط لـ Pearson بين متغيرين. وظيفيًا، تقدم كل من PEARSON و CORREL نفس النتيجة. ومع ذلك، تلتزم PEARSON بشكل صارم بالصيغة الرياضية الأصلية، بينما تم تحسين CORREL لبيئة Excel. إذا كنت معتادًا على النظرية الإحصائية أو تعمل مع أدوات إحصائية خارج Excel، قد تشعر بأن PEARSON أكثر شيوعًا.
على سبيل المثال، مع قائمتين عدديتين في A2:A7 و B2:B7، يمكنك حساب الارتباط على النحو التالي:
1. حدد الخلية التي تريد عرض النتيجة فيها، وأدخل هذه الصيغة:
=PEARSON(A2:A7,B2:B7)
2. اضغط Enter لإكمال الحساب. إذا كنت ترغب في تحليل أزواج إضافية من البيانات، قم بتعديل نطاقات الخلايا وفقًا لذلك، أو اسحب الصيغة إلى خلايا أخرى.
نصائح: تتجاهل PEARSON النصوص أو القيم المنطقية، لذا تأكد من أن كلتا النطاقات تحتوي فقط على قيم عددية وأنها متساوية الطول. إذا كانت هناك بيانات مفقودة في أحد الأعمدة، قم بمحاذاة النطاقات وفقًا لذلك لتجنب الأخطاء.
استخدام PEARSON يكون عمليًا خاصة للمستخدمين الذين ينتقلون من برامج إحصائية أخرى، أو في البيئات الأكاديمية حيث يتوقع الالتزام الصارم بالمصطلحات. تقدم كل من CORREL و PEARSON نفس النتيجة لمعظم حالات الاستخدام في Excel.
إذا واجهت خطأ #DIV/0!، تحقق من أن كلتا النطاقين متساويين في الطول ولا تحتوي على خلايا فارغة أو غير عددية غير متطابقة.
المزايا: سهلة الاستخدام، متسقة مع البرمجيات الإحصائية؛ العيوب: لا تقدم اختلافًا كبيرًا عن CORREL لمعظم المستخدمين.
الطريقة د: استخدام كود VBA لحساب معاملات الارتباط لعدة أزواج
إذا كنت بحاجة إلى أتمتة حساب معاملات الارتباط لعدة أزواج من البيانات (على سبيل المثال، عند العمل مع العديد من تركيبات المتغيرات)، كتابة ماكرو VBA بسيطة يعد خيارًا فعالاً. هذه الطريقة مناسبة للمستخدمين المتقدمين الذين يريدون معالجة مجموعات بيانات كبيرة أو أتمتة مهام التحليل المتكررة.
1. لاستخدام هذه الطريقة، افتح أولاً محرر VBA عن طريق النقر على Developer > Visual Basic. في نافذة Visual Basic for Applications، انتقل إلى Insert > Module، ثم ألصق الكود التالي في الوحدة:
Sub BatchCalculateCorrelations()
Dim ws As Worksheet
Dim rng1 As Range, rng2 As Range
Dim lastRow As Long
Dim i As Long
Dim resultCol As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = ActiveSheet
Set rng1 = Application.InputBox("Select first variable range (single column)", xTitleId, Type:=8)
Set rng2 = Application.InputBox("Select second variable range (multiple columns)", xTitleId, Type:=8)
Set resultCol = Application.InputBox("Select starting cell for output", xTitleId, Type:=8)
If rng1.Rows.Count <> rng2.Rows.Count Then
MsgBox "The two data ranges must have the same number of rows.", vbCritical, xTitleId
Exit Sub
End If
For i = 1 To rng2.Columns.Count
resultCol.Cells(1, i).Value = "Correlation with " & rng2.Cells(1, i).EntireColumn.Column
resultCol.Cells(2, i).Value = WorksheetFunction.Correl(rng1, rng2.Columns(i))
Next i
End Sub
2. بعد إدخال الكود، أغلق محرر VBA. في Excel، اضغط Alt + F8، حدد BatchCalculateCorrelations، وانقر على Run. ستتم مطالبتك باختيار:
- نطاق المتغير الأول (عمود واحد، على سبيل المثال A2:A7)
- نطاق المتغير الثاني (عمود واحد أو أكثر، على سبيل المثال B2:D7)
- الخلية التي تريد أن تبدأ فيها النتائج (على سبيل المثال F2)
ثم يقوم الماكرو بحساب معامل الارتباط بين المتغير الأول وكل عمود في النطاق الثاني، ويعرض النتائج أفقيًا بدءًا من الخلية المختارة.
المزايا: تؤتم الحسابات المتكررة، توفر وقتًا كبيرًا مع مجموعات البيانات الكبيرة، وضمان الاتساق.
إذا واجهت مشكلات مثل "يجب أن يكون لدى نطاقي البيانات نفس عدد الصفوف"، تأكد من أن جميع الأعمدة المحددة تشترك في نفس عدد الصفوف تمامًا ولا تحتوي على صفوف فارغة. لاستكشاف الأخطاء وإصلاحها، تحقق مما إذا كانت الماكرو مفعلة والنطاقات مختارة بشكل صحيح.
عند العمل مع معاملات الارتباط في Excel، يعتمد اختيار الطريقة الصحيحة على هيكل بياناتك واحتياجات التحليل. بالنسبة للحسابات السريعة لمرة واحدة بين سلسلتين، تعتبر الصيغ مثل CORREL أو PEARSON فعالة وسهلة الاستخدام. بالنسبة لعدة متغيرات أو الحاجة إلى جداول ملخصة، تكون أداة Analysis Toolpak عملية للغاية. إذا كنت بحاجة إلى تحليل متكرر على مجموعات بيانات كبيرة أو تريد تدفقات عمل مخصصة، ففكر في الأتمتة باستخدام VBA لتوفير الوقت وتقليل الأخطاء البشرية.
تأكد دائمًا من أن نطاقات البيانات الخاصة بك محاذاة، نظيفة، ولا تحتوي على خلايا فارغة أو غير عددية لتجنب أخطاء الصيغ. إذا حصلت على نتائج غير متوقعة، تحقق مرة أخرى من الاختيارات وأنواع البيانات.
مقالات ذات صلة
- حساب نسبة التغيير أو الفرق بين رقمين في Excel
تتحدث هذه المقالة عن حساب نسبة التغيير أو الفرق بين رقمين في Excel.
- حساب أو تعيين التقدير بالأحرف في Excel
لتعيين التقدير بالأحرف لكل طالب بناءً على درجاته قد يكون مهمة شائعة للمعلم. على سبيل المثال، لدي مقياس تقدير محدد حيث 0-59 = F، 60-69 = D، 70-79 = C، 80-89 = B، و 90-100 = A، راجع المزيد.
- حساب معدل الخصم أو السعر في Excel
عندما يقترب عيد الميلاد، هناك بالتأكيد العديد من العروض الترويجية في مراكز التسوق. لكن إذا كانت أنواع مختلفة من العناصر لها خصومات مختلفة، كيف يمكنك حساب معدلات الخصم أو أسعار العناصر المختلفة؟
- حساب عدد الأيام / أيام العمل / عطلات نهاية الأسبوع بين تاريخين في Excel
قد يكون، في بعض الأحيان، أنك ترغب فقط في حساب أيام العمل بين تاريخين، وفي أحيان أخرى، تحتاج إلى حساب أيام عطلة نهاية الأسبوع فقط بين التاريخين.
أفضل أدوات زيادة الإنتاجية لمجموعة Office
عزز مهاراتك في Excel مع Kutools لـ Excel، وتمتع بكفاءة غير مسبوقة. يقدم Kutools لـ Excel أكثر من300 ميزة متقدمة لتعزيز الإنتاجية وتوفير وقت الحفظ. انقر هنا للحصول على الميزة التي تحتاجها أكثر...
Office Tab يجلب تجربة التبويبات إلى Office، ويجعل عملك أسهل بكثير
- فعّل تحرير وقراءة المستندات عبر التبويبات في Word وExcel وPowerPoint
- افتح وأنشئ مستندات متعددة في تبويبات جديدة ضمن نفس النافذة، بدلاً من فتحها في نوافذ جديدة.
- يمنحك زيادة إنتاجية تصل إلى50% ويوفر عليك مئات النقرات يومياً!