تحليل الارتباط في Excel. مثال على إجراء تحليل الارتباط

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

جوهر تحليل الارتباط

من الضروري تحديد العلاقة بين كميتين مختلفتين. بمعنى آخر ، فإنه يكشف في أي اتجاه (أصغر / أكبر) تتغير القيمة اعتمادًا على التغييرات في الاتجاه الثاني.

الغرض من تحليل الارتباط

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

المهم! عند المعامل 0 ، لا توجد علاقة بين الكميات.

حساب معامل الارتباط

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

الطريقة الأولى: تحديد الارتباط من خلال معالج الدالة

CORREL - وظيفة تسمح لك بتنفيذ تحليل الارتباط. الشكل العام - كوريل (ماسيف 1 ؛ ماسيف 2). تعليمات مفصلة:

1. من الضروري تحديد الخلية التي تم التخطيط لعرض نتيجة الحساب فيها. انقر فوق "إدراج دالة" الموجود على يسار حقل النص لإدخال الصيغة.

2. يفتح معالج الوظيفة. هنا تحتاج أن تجد CORREL، انقر فوقه ، ثم على "موافق".

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

4. بعد إدخال جميع النطاقات ، انقر فوق الزر "موافق".

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

الطريقة 2: حساب الارتباط باستخدام Analysis ToolPak

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

1. انتقل إلى قسم "ملف".

2. ستفتح نافذة جديدة تحتاج فيها إلى النقر فوق قسم "الإعدادات".
3. انقر فوق "الإضافات".
4. نجد عنصر "الإدارة" في الأسفل. هنا تحتاج إلى تحديد "وظائف Excel الإضافية" من قائمة السياق والنقر فوق "موافق".

5. تم فتح نافذة خاصة بالإضافات. ضع علامة اختيار بجوار عنصر "حزمة التحليل". نضغط على "موافق".
6. تم التنشيط بنجاح. الآن دعنا نذهب إلى البيانات. ظهرت كتلة "التحليل" ، حيث تحتاج إلى النقر فوق "تحليل البيانات".
7. في النافذة الجديدة التي تظهر ، حدد عنصر "الارتباط" وانقر على "موافق".

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

الدرجات النهائية خارج. النتيجة هي نفسها كما في الطريقة الأولى - 0,97،XNUMX.

تعريف وحساب معامل الارتباط المتعدد في MS Excel

لتحديد مستوى الاعتماد على عدة كميات ، يتم استخدام معاملات متعددة. في المستقبل ، يتم تلخيص النتائج في جدول منفصل يسمى مصفوفة الارتباط.

دليل مفصل:

1. في قسم "البيانات" ، نجد كتلة "التحليل" المعروفة بالفعل وانقر على "تحليل البيانات".

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

4. مستعد! تم بناء مصفوفة الارتباط.

معامل الارتباط الزوجي في Excel

دعنا نتعرف على كيفية رسم معامل ارتباط الزوج بشكل صحيح في جدول بيانات Excel.

حساب معامل الارتباط الزوجي في Excel

على سبيل المثال ، لديك قيم x و y.

X هو المتغير التابع و y هو المتغير المستقل. من الضروري إيجاد اتجاه وقوة العلاقة بين هذه المؤشرات. تعليمات خطوة بخطوة:

1. لنجد متوسط ​​القيم باستخدام الدالة قلب.

2. دعونا نحسب كل х и xavg, у и المتوسط باستخدام عامل التشغيل «-».

3. نضرب الفروق المحسوبة.

4. نحسب مجموع المؤشرات في هذا العمود. البسط هو النتيجة التي تم العثور عليها.

5. احسب مقامات الفرق х и متوسط ​​x ، y и متوسط ​​ص. للقيام بذلك ، سنقوم بإجراء التربيع.

6. باستخدام الوظيفة الاستلقاء الذاتي، ابحث عن المؤشرات في الأعمدة الناتجة. نقوم بعملية الضرب. استخدام الوظيفة ROOT تربيع النتيجة.

7. نحسب خارج القسمة باستخدام قيم المقام والبسط.

8. CORREL هي وظيفة متكاملة تسمح لك بمنع العمليات الحسابية المعقدة. نذهب إلى "معالج الوظائف" ، ونختار CORREL ونحدد مصفوفات المؤشرات х и у. نقوم ببناء رسم بياني يعرض القيم التي تم الحصول عليها.

مصفوفة معاملات الارتباط الزوجي في Excel

دعنا نحلل كيفية حساب معاملات المصفوفات المزدوجة. على سبيل المثال ، هناك مصفوفة من أربعة متغيرات.

تعليمات خطوة بخطوة:

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

دالة CORREL لتحديد العلاقة والارتباط في Excel

CORREL - دالة تستخدم لحساب معامل الارتباط بين صفيفتين. لنلقِ نظرة على أربعة أمثلة لجميع قدرات هذه الوظيفة.

أمثلة على استخدام دالة CORREL في Excel

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

تبدو خوارزمية الحساب كما يلي:

النتيجة المعروضة قريبة من 1. النتيجة:

تحديد معامل الارتباط لتأثير الإجراءات على النتيجة

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

باستخدام الصيغ أدناه ، نحسب معامل الارتباط:

• =كوريل (A3: A17 ؛ B3: B17).
• = CORREL (A3: A17 ؛ C3: C17).

النتائج:

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

تحليل شعبية المحتوى من خلال الارتباط بين مشاهدات الفيديو وإعادة النشر

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

الآن من الضروري تحديد وجود علاقة بين مؤشرين وفقًا للصيغة أدناه:

0,7،3 ؛ IF (CORREL (A8: A3؛ B8: B0,7)> XNUMX،XNUMX ؛ "علاقة مباشرة قوية" ؛ "علاقة عكسية قوية") ؛ "علاقة ضعيفة أو معدومة") 'class =' ​​صيغة '>

إذا كان المعامل الناتج أعلى من 0,7،XNUMX ، فمن الأنسب استخدام دالة الانحدار الخطي. في هذا المثال نقوم بما يلي:

الآن نقوم ببناء رسم بياني:

نطبق هذه المعادلة لتحديد عدد المشاهدات عند 200 و 500 و 1000 سهم: = 9,2937،4 * D206,12-XNUMX،XNUMX. نحصل على النتائج التالية:

المسمى الوظيفي FORECAST يسمح لك بتحديد عدد المشاهدات في الوقت الحالي ، إذا كان هناك ، على سبيل المثال ، مائتان وخمسون إعادة نشر. نطبق: 0,7،7 ؛ التنبؤ (D3 ؛ B8: B3 ؛ A8: AXNUMX) ؛ "القيم غير مرتبطة") 'class =' ​​الصيغة '>. نحصل على النتائج التالية:

ميزات استخدام وظيفة CORREL في Excel

تحتوي هذه الوظيفة على الميزات التالية:

1. لا تؤخذ الخلايا الفارغة في الاعتبار.
2. لا تؤخذ الخلايا التي تحتوي على معلومات من نوع Boolean و Text في الاعتبار.
3. يستخدم النفي المزدوج "-" لحساب القيم المنطقية في شكل أرقام.
4. يجب أن يتطابق عدد الخلايا في المصفوفات المدروسة ، وإلا سيتم عرض الرسالة # N / A.

تقييم الدلالة الإحصائية لمعامل الارتباط

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

وفي الختام

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