تجزیه و تحلیل همبستگی در اکسل. نمونه ای از انجام تحلیل همبستگی

تحلیل همبستگی یک روش تحقیق رایج است که برای تعیین سطح وابستگی مقدار 1 به مقدار 2 استفاده می شود. صفحه گسترده دارای ابزار خاصی است که به شما امکان می دهد این نوع تحقیق را پیاده سازی کنید.

ماهیت تحلیل همبستگی

تعیین رابطه بین دو کمیت مختلف ضروری است. به عبارت دیگر، نشان می دهد که در کدام جهت (کوچکتر / بزرگتر) مقدار بسته به تغییرات دوم تغییر می کند.

هدف از تحلیل همبستگی

زمانی که شناسایی ضریب همبستگی شروع می شود، وابستگی ایجاد می شود. این روش با تحلیل رگرسیون متفاوت است، زیرا تنها یک شاخص با استفاده از همبستگی محاسبه شده است. فاصله از +1 به -1 تغییر می کند. اگر مثبت باشد، افزایش در اولین مقدار به افزایش در 2 کمک می کند. اگر منفی باشد، افزایش در مقدار 1 به کاهش در 2 کمک می کند. هر چه ضریب بالاتر باشد، یک مقدار قوی‌تر روی دومی تأثیر می‌گذارد.

مهم! در ضریب 0 هیچ رابطه ای بین کمیت ها وجود ندارد.

محاسبه ضریب همبستگی

بیایید محاسبه را روی چندین نمونه تجزیه و تحلیل کنیم. به عنوان مثال، داده‌های جدولی وجود دارد که در آن هزینه‌های تبلیغاتی و حجم فروش در ستون‌های جداگانه به تفکیک ماه توضیح داده می‌شود. بر اساس جدول، میزان وابستگی حجم فروش را به پولی که برای تبلیغات تبلیغاتی صرف می شود، خواهیم فهمید.

روش 1: تعیین همبستگی از طریق Function Wizard

CORREL - تابعی که به شما امکان می دهد تحلیل همبستگی را پیاده سازی کنید. فرم عمومی - CORREL(massiv1;massiv2). دستورالعمل های دقیق:

1. لازم است سلولی را انتخاب کنید که در آن برنامه ریزی شده است که نتیجه محاسبه نمایش داده شود. برای وارد کردن فرمول، روی "درج تابع" واقع در سمت چپ فیلد متن کلیک کنید.

2. Function Wizard باز می شود. در اینجا شما باید پیدا کنید CORREL، روی آن کلیک کنید، سپس روی "OK" کلیک کنید.

3. پنجره آرگومان ها باز می شود. در خط "Array1" باید مختصات فواصل یکم از مقادیر را وارد کنید. در این مثال، این ستون ارزش فروش است. فقط باید تمام سلول هایی که در این ستون هستند را انتخاب کنید. به طور مشابه، شما باید مختصات ستون دوم را به خط "Array1" اضافه کنید. در مثال ما، این ستون هزینه های تبلیغاتی است.

4. پس از وارد کردن تمام محدوده ها، روی دکمه "OK" کلیک کنید.

ضریب در سلولی که در ابتدای اقدامات ما نشان داده شده بود نمایش داده شد. نتیجه به دست آمده 0,97 است. این نشانگر وابستگی بالای مقدار اول به مقدار دوم را نشان می دهد.

روش 2: محاسبه همبستگی با استفاده از Analysis ToolPak

روش دیگری برای تعیین همبستگی وجود دارد. در اینجا یکی از توابع موجود در بسته تجزیه و تحلیل استفاده شده است. قبل از استفاده از آن، باید ابزار را فعال کنید. دستورالعمل های دقیق:

1. به بخش "فایل" بروید.

2. پنجره جدیدی باز می شود که در آن باید روی بخش "تنظیمات" کلیک کنید.
3. بر روی "افزونه ها" کلیک کنید.
4. عنصر "Management" را در پایین پیدا می کنیم. در اینجا باید "Excel Add-ins" را از منوی زمینه انتخاب کنید و روی "OK" کلیک کنید.

5. یک پنجره افزودنی ویژه باز شده است. یک علامت چک در کنار عنصر «بسته تحلیل» قرار دهید. ما روی "OK" کلیک می کنیم.
6. فعال سازی با موفقیت انجام شد. حالا بریم سراغ Data. بلوک "Analysis" ظاهر شد که در آن باید روی "تحلیل داده ها" کلیک کنید.
7. در پنجره جدیدی که ظاهر می شود، عنصر "Correlation" را انتخاب کرده و روی "OK" کلیک کنید.

8. پنجره تنظیمات تجزیه و تحلیل روی صفحه ظاهر شد. در خط "فاصله ورودی" لازم است محدوده کاملاً تمام ستون هایی که در تجزیه و تحلیل شرکت می کنند را وارد کنید. در این مثال، این ستون‌های «ارزش فروش» و «هزینه‌های تبلیغات» هستند. تنظیمات نمایش خروجی در ابتدا روی New Worksheet تنظیم شده است، به این معنی که نتایج در برگه دیگری نمایش داده می شود. به صورت اختیاری، می توانید محل خروجی نتیجه را تغییر دهید. پس از انجام تمام تنظیمات، بر روی "OK" کلیک کنید.

نمرات نهایی اعلام شد. نتیجه مشابه روش اول است - 0,97.

تعریف و محاسبه ضریب همبستگی چندگانه در MS Excel

برای شناسایی سطح وابستگی چند کمیت، از ضرایب متعدد استفاده می شود. در آینده، نتایج در یک جدول جداگانه به نام ماتریس همبستگی خلاصه می شود.

راهنمای مفصل:

1. در بخش «داده‌ها»، بلوک «تحلیل» از قبل شناخته شده را پیدا می‌کنیم و روی «تحلیل داده‌ها» کلیک می‌کنیم.

2. در پنجره ای که ظاهر می شود، روی عنصر "Correlation" کلیک کنید و روی "OK" کلیک کنید.
3. در خط "فاصله ورودی" در فاصله سه یا چند ستون از جدول منبع حرکت می کنیم. محدوده را می توان به صورت دستی وارد کرد یا به سادگی با LMB انتخاب کرد و به طور خودکار در خط مورد نظر ظاهر می شود. در "گروه بندی" روش گروه بندی مناسب را انتخاب کنید. در «پارامتر خروجی» مکان نمایش نتایج همبستگی را مشخص می کند. ما روی "OK" کلیک می کنیم.

4. آماده! ماتریس همبستگی ساخته شد.

ضریب همبستگی جفت در اکسل

بیایید نحوه رسم صحیح ضریب همبستگی جفت را در یک صفحه گسترده اکسل دریابیم.

محاسبه ضریب همبستگی زوج در اکسل

به عنوان مثال، شما مقادیر x و y دارید.

X متغیر وابسته و y مستقل است. باید جهت و قدرت رابطه بین این شاخص ها را پیدا کرد. آموزش گام به گام:

1. بیایید مقادیر میانگین را با استفاده از تابع پیدا کنیم قلب.

2. بیایید هر کدام را محاسبه کنیم х и xavg, у и میانگین با استفاده از عملگر «-».

3. تفاوت های محاسبه شده را ضرب می کنیم.

4. مجموع شاخص های این ستون را محاسبه می کنیم. شمارنده نتیجه یافت شده است.

5. مخرج اختلاف را محاسبه کنید х и میانگین x، y и y-متوسط. برای انجام این کار، ما مربع سازی را انجام می دهیم.

6. با استفاده از تابع AUTOSUMMA، نشانگرها را در ستون های حاصل پیدا کنید. ضرب را انجام می دهیم. با استفاده از تابع ریشه مربع نتیجه

7. ما ضریب را با استفاده از مقادیر مخرج و صورت محاسبه می کنیم.

8. CORREL یک تابع یکپارچه است که به شما امکان می دهد از محاسبات پیچیده جلوگیری کنید. ما به "جادوگر عملکرد" ​​می رویم، CORREL را انتخاب می کنیم و آرایه های نشانگر را مشخص می کنیم х и у. ما یک نمودار می سازیم که مقادیر به دست آمده را نمایش می دهد.

ماتریس ضرایب همبستگی زوجی در اکسل

بیایید نحوه محاسبه ضرایب ماتریس های زوج را تجزیه و تحلیل کنیم. به عنوان مثال، ماتریسی از چهار متغیر وجود دارد.

دستورالعمل گام به گام:

1. ما به "تجزیه و تحلیل داده ها"، واقع در بلوک "تجزیه و تحلیل" تب "داده ها" می رویم. از لیست ظاهر شده گزینه Correlation را انتخاب کنید.
2. ما تمام تنظیمات لازم را تنظیم می کنیم. "فاصله ورودی" - فاصله هر چهار ستون. "فاصله خروجی" - جایی که می خواهیم مجموع ها را نمایش دهیم. ما روی دکمه "OK" کلیک می کنیم.
3. یک ماتریس همبستگی در محل انتخاب شده ساخته شد. هر تقاطع یک سطر و یک ستون یک ضریب همبستگی است. زمانی که مختصات مطابقت دارند عدد 1 نمایش داده می شود.

تابع CORREL برای تعیین رابطه و همبستگی در اکسل

CORREL - تابعی است که برای محاسبه ضریب همبستگی بین 2 آرایه استفاده می شود. بیایید به چهار نمونه از تمام توانایی های این تابع نگاه کنیم.

نمونه هایی از استفاده از تابع CORREL در اکسل

مثال اول یک پلاک اطلاعاتی در مورد میانگین حقوق کارکنان شرکت در طول یازده سال و نرخ تبدیل دلار وجود دارد. شناسایی رابطه بین این دو کمیت ضروری است. جدول به شکل زیر است:

الگوریتم محاسبه به صورت زیر است:

امتیاز نمایش داده شده نزدیک به 1 است. نتیجه:

تعیین ضریب همبستگی تأثیر اقدامات بر نتیجه

مثال دوم دو مناقصه‌دهنده برای کمک به تبلیغات پانزده روزه به دو آژانس مختلف مراجعه کردند. هر روز یک نظرسنجی اجتماعی انجام می شد که میزان حمایت از هر متقاضی را مشخص می کرد. هر مصاحبه شونده می تواند یکی از دو متقاضی را انتخاب کند یا با همه مخالفت کند. باید مشخص شود که هر تبلیغ تبلیغاتی چقدر بر میزان حمایت از متقاضیان تأثیر گذاشته است که کدام شرکت کارآمدتر است.

با استفاده از فرمول های زیر، ضریب همبستگی را محاسبه می کنیم:

• =CORREL(A3:A17;B3:B17).
• =CORREL(A3:A17;C3:C17).

نتایج:

از نتایج به‌دست‌آمده، مشخص می‌شود که درجه حمایت از متقاضی اول با هر روز ارتقاء تبلیغات افزایش می‌یابد، بنابراین، ضریب همبستگی به ۱ نزدیک می‌شود. زمانی که تبلیغات راه‌اندازی شد، متقاضی دیگر اعتماد زیادی داشت و برای 1 روز یک روند مثبت وجود دارد. سپس درجه اعتماد کاهش یافت و در روز پانزدهم به زیر شاخص های اولیه رسید. نمرات پایین نشان می دهد که ارتقاء بر حمایت تأثیر منفی گذاشته است. فراموش نکنید که سایر عوامل همراه که به صورت جدولی در نظر گرفته نمی شوند نیز می توانند بر شاخص ها تأثیر بگذارند.

تجزیه و تحلیل محبوبیت محتوا با همبستگی بین بازدیدهای ویدیویی و ارسال مجدد

مثال سوم. شخصی برای تبلیغ ویدیوهای خود در میزبانی ویدیوی YouTube از شبکه های اجتماعی برای تبلیغ کانال استفاده می کند. او متوجه می شود که بین تعداد بازنشر در شبکه های اجتماعی و تعداد بازدید از کانال رابطه وجود دارد. آیا می توان عملکرد آینده را با استفاده از ابزارهای صفحه گسترده پیش بینی کرد؟ شناسایی منطقی بودن استفاده از معادله رگرسیون خطی برای پیش‌بینی تعداد بازدیدهای ویدیو بسته به تعداد بازپست‌ها ضروری است. جدول با مقادیر:

حال باید وجود رابطه بین 2 شاخص را طبق فرمول زیر تعیین کرد:

0,7;IF(CORREL(A3:A8;B3:B8)>0,7;"رابطه مستقیم قوی";"رابطه معکوس قوی");"رابطه ضعیف یا بدون رابطه")' class='formula'>

اگر ضریب حاصل از 0,7 بیشتر باشد، بهتر است از تابع رگرسیون خطی استفاده شود. در این مثال انجام می دهیم:

اکنون در حال ساخت یک نمودار هستیم:

ما این معادله را برای تعیین تعداد بازدید در 200، 500 و 1000 اشتراک اعمال می کنیم: =9,2937*D4-206,12. ما نتایج زیر را دریافت می کنیم:

عملکرد پیش بینی به شما امکان می دهد تعداد بازدیدها را در لحظه تعیین کنید، اگر مثلاً دویست و پنجاه پست مجدد وجود داشته باشد. ما درخواست می کنیم: 0,7;PREDICTION(D7;B3:B8;A3:A8);"مقادیر مرتبط نیستند")' class='formula'>. ما نتایج زیر را دریافت می کنیم:

ویژگی های استفاده از تابع CORREL در اکسل

این تابع دارای ویژگی های زیر است:

1. سلول های خالی در نظر گرفته نمی شوند.
2. سلول های حاوی اطلاعات نوع Boolean و Text در نظر گرفته نمی شوند.
3. نفی مضاعف "-" برای محاسبه مقادیر منطقی در قالب اعداد استفاده می شود.
4. تعداد سلول های آرایه های مورد مطالعه باید مطابقت داشته باشد، در غیر این صورت پیام #N/A نمایش داده می شود.

ارزیابی اهمیت آماری ضریب همبستگی

هنگام آزمایش اهمیت یک ضریب همبستگی، فرضیه صفر این است که شاخص دارای مقدار 0 است، در حالی که گزینه جایگزین ندارد. از فرمول زیر برای تایید استفاده می شود:

نتیجه

تجزیه و تحلیل همبستگی در یک صفحه گسترده یک فرآیند ساده و خودکار است. برای انجام آن فقط باید بدانید که ابزارهای لازم در کجا قرار دارند و چگونه از طریق تنظیمات برنامه آنها را فعال کنید.