كيف تحسب معامل الارتباط بين متغيرين في Excel؟
عادةً ما نستخدم معامل الارتباط—وهو قيمة تتراوح بين -1 و1—للدلالة على قوة واتجاه العلاقة الخطية بين متغيرين. يُعدّ هذا المعامل إحصاءً شائع الاستخدام يساعدك على فهم العلاقات بين أزواج البيانات، مثل العلاقة بين المبيعات وإنفاق الإعلانات، أو بين درجة الحرارة ومبيعات الآيس كريم. وفي Excel، يمكنك حساب معامل الارتباط بسهولة من خلال عدة طرق مباشرة، بما في ذلك الدوال المدمجة وأدوات التحليل.
الطريقة أ: استخدام دالة CORREL مباشرةً
الطريقة ب: تطبيق تحليل البيانات وإخراج التحليل
الطريقة ج: استخدام دالة PEARSON كبديل
الطريقة د: استخدام كود VBA لحساب معاملات الارتباط لأزواج متعددة
الطريقة أ: استخدام دالة CORREL مباشرةً
لديك قائمتان من البيانات، كلٌّ منهما يمثّل متغيرًا. إذا أردت حساب معامل الارتباط بين هذين المتغيرين في Excel، فهذه الطريقة سريعة وفعّالة.
للتطبيق العملي، تأكد من أن كلا النطاقين رقميان ويحتويان على نفس عدد القيم. على سبيل المثال، إذا كانت لديك البيانات الزوجية التالية:
اختر خلية فارغة لعرض نتيجة الحساب فيها، ثم أدخل الصيغة التالية واضغط مفتاح «Enter» لحساب معامل الارتباط:
=CORREL(A2:A7,B2:B7)
في هذه الصيغة، يمثّل A2:A7 وB2:B7 قائمتي المتغيرات اللتين ترغب في تحليلهما. يجب أن يكون طولا القائمتين متساويين، وأن يتوافق كل زوج من القيم مع نفس الملاحظة.
نصيحة عملية: تتجاهل دالة CORREL تلقائيًا الخلايا الفارغة والنصوص، لكنها تُرجع خطأ #DIV/0! إذا لم يحتوِ العمودان على أي أزواج رقمية صالحة. لضمان حساب ارتباط دقيق، تأكد من محاذاة بياناتك بشكل صحيح واحتوائها على أزواج رقمية.
بمجرد حسابك لمعامل الارتباط، يمكنك إدراج مخطط خطي لملاحظة العلاقات بصريًّا وتفسير الارتباط بشكل أعمق، كما هو موضح أدناه:
هذه الطريقة مثالية للتحقق السريع يدويًّا بين مجموعتين صغيرتين من البيانات أو عند العمل التفاعلي داخل جدولك، وهي تناسب المستخدمين الباحثين عن نتيجة فورية دون الحاجة إلى مخرجات إحصائية متقدمة.
افتح سحر إكسل مع KUTOOLS AI
- التنفيذ الذكي: نفِّذ عمليات الخلايا، وحلِّل البيانات، وأنشئ المخططات البيانية — كل ذلك بأوامر بسيطة!
- الصيغ المخصصة: أنشئ صيغًا مخصصة لتبسيط سير عملك.
- برمجة VBA: اكتب وأَنفِذ أكواد VBA بسلاسة تامة.
- تفسير الصيغ: افهم الصيغ المعقدة بسهولة!
- ترجمة النصوص: اكسر الحواجز اللغوية في جداولك الإلكترونية!
الطريقة ب: تطبيق تحليل البيانات وإخراج التحليل
إذا كنت بحاجة إلى تحليل الارتباط بين عدة متغيرات دفعة واحدة أو رغبت في جدول مخرجات أكثر شمولًا، فإن «حزمة أدوات التحليل» (Analysis Toolpak) في Excel تُعد حلاً مثاليًا. يُنشئ هذا الملحق مصفوفة ارتباط تتيح لك مقارنة عدة متغيرات في خطوة واحدة، مما يجعله خيارًا ممتازًا لمجموعات البيانات الكبيرة أو لإعداد التقارير الإحصائية باحترافية.
1. إذا كنت قد أضفت بالفعل ملحق تحليل البيانات إلى تبويب البيانات، فيمكنك الانتقال مباشرةً إلى الخطوة 3. وإلا، انقر فوقملف > خيارات. في مربع حوار «خيارات Excel»، اخترالوظائف الإضافيةمن الجزء الأيسر، ثم انقر فوق الزرانتقالبجانب مربع «وظائف Excel الإضافية».
2. في مربع حوار «الوظائف الإضافية»، ضع علامة في المربع المسمىحزمة أدوات التحليل (Analysis ToolPak)، ثم انقر فوقموافق. وسيتم إضافة مجموعة «تحليل البيانات» تلقائيًا إلى تبويبالبيانات.
3. بعد ذلك، انقر فوقالبيانات > تحليل البيانات. في مربع الحوار المنبثق «تحليل البيانات»، اخترالارتباط (Correlation)من القائمة، ثم انقر فوقموافق.
4. في مربع حوار الارتباط، قم بتكوين ما يلي:
1) حدد النطاق الذي يحتوي على بياناتك.
2) اختر بين خيارَي «الأعمدة» أو «الصفوف»، وفقًا لكيفية تنظيم بياناتك.
3) إذا كانت بياناتك تحتوي على رؤوس، ففعّل خيار «التسميات في الصف الأول».
4) حدّد موقع الإخراج ضمن «خيارات الإخراج» لعرض النتائج.
5. انقر فوقموافقلتوليد جدول تحليل الارتباط. سيتم عرض معاملات الارتباط في نطاق محدود.
هذه الطريقة مثالية عندما تريد تقييم العلاقات بين أكثر من متغير أو تحتاج إلى جدول ملخّص فعّال لأغراض إعداد التقارير. يُرجى ملاحظة أن مخرج تحليل البيانات لا يتضمّن إحصائيات دلالة إضافية. وفي حال ظهور نتائج غير متوقعة، تأكد من اتساق بياناتك، وخلوّها من الخلايا الفارغة، واختيار النطاق الصحيح بدقة.
الطريقة ج: استخدام دالة PEARSON كبديل
بالإضافة إلى دالةCORREL، يوفّر Excel دالةPEARSON التي تحسب أيضًا معامل ارتباط بيرسون بين متغيرين. من الناحية الوظيفية، تُعيد الدالتان نفس النتيجة تمامًا. لكن دالة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 بالنقر فوقالمطوّر > Visual Basic. في نافذة Visual Basic for Applications، انتقل إلىإدراج > وحدة نمطية (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 Sub2. بعد إدراج الكود، أغلق محرر VBA. في Excel، اضغطAlt + F8، ثم حددBatchCalculateCorrelations، وانقرتشغيل. سيُطلب منك تحديد:
- نطاق المتغير الأول (عمود واحد، مثال: 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
قد ترغب أحيانًا في حساب عدد أيام العمل بين تاريخين، بينما قد تحتاج في أحيانٍ أخرى إلى عدّ عطل نهاية الأسبوع فقط بين هذين التاريخين.
أفضل أدوات الإنتاجية لمكتبتك
عزِّز مهاراتك في Excel باستخدام Kutools لـ Excel، وعايش الكفاءة كما لم تفعل من قبل.يقدّم Kutools لـ Excel أكثر من 300 ميزة متقدمة لتعزيز الإنتاجية ووقت الحفظ.انقر هنا للحصول على الميزة التي تحتاجها أكثر من غيرها...
يجلب Office Tab واجهة ذات علامات تبويب إلى Office، ويجعل عملك أسهل بكثير
- تمكّن من التحرير والقراءة باستخدام علامات التبويب في Word وExcel وPowerPoint، وPublisher وAccess وVisio وProject.
- افتح وأنشئ مستندات متعددة في علامات تبويب جديدة داخل النافذة نفسها، بدلاً من فتح نوافذ جديدة.
- يزيد إنتاجيتك بنسبة 50% ويوفّر لك مئات نقرات الفأرة كل يوم!
جميع الإضافات من Kutools في برنامج تثبيت واحد!
Kutools for Office حزمةٌ تحتوي على إضافاتٍ مخصصة لتطبيقات Excel وWord وOutlook وPowerPoint، إلى جانب Office Tab Pro، مما يجعلها الخيار المثالي للفِرق التي تعمل عبر تطبيقات Office.
- حزمة شاملة واحدة— إضافات Excel وWord وOutlook وPowerPoint بالإضافة إلى Office Tab Pro
- برنامج تثبيت واحد، ترخيص واحد— الإعداد خلال دقائق (جاهز لـ MSI)
- يعمل بشكل أفضل معًا— إنتاجية ميسَّرة عبر تطبيقات Office
- تجربة مجانية لمدة 30 يومًا بكامل الميزات— بدون تسجيل، بدون بطاقة ائتمان
- أفضل قيمة— وفِّر مقارنةً بشراء الإضافات بشكل منفصل