کواریانس و Correlation با افزونه Data Analysis در اکسل + فیلم

محاسبه کواریانس و Correlation با افزونه Data Analysis در اکسل (Excel) + فیلم

در این مبحث آموزشی، با افزونه Data Analysis در اکسل (Excel) به محاسبه کواریانس (Covariance) و همبستگی (Correlation) می‌پردازیم! و با مثال و فیلم آموزشی مبحث رو به طور کامل یاد خواهید گرفت!

در صورتی که به هر دلیل، دوست ندارید متن زیر را بخوانید و می‌خواهید فقط فیلم آموزشی را مشاهده کنید، به انتهای همین مطلب مراجعه کنید و فیلم را دانلود و تماشا کنید!

قبل از هر چیز باید افزونه Data Analysis که تووووی خود اکسل هم موجوده رو فعال کنیم! چجوری؟! به بخش زیر دقت کنید:!

فعال کردن افزونه Data Analysis

به تب File بروید و سپس از سمت چپ، گزینه Options رو انتخاب کنید، در پنجره‌ای که شبیه تصویر زیر خواهد بود، از سمت چپش گزینه Add-ins رو انتخاب کنید!

بعد پنجره شبیه تصویر بالا میشه! حالا از قسمت پایین همون پنجره، و اووون قسمتی که نوشته Manage، گزینه Excel Add-ins رو انتخاب کنید و بعد روی دکمه Go کلیک کنید تا پنجره جدید زیر باز بشه!

توی این پنجره هم که فقط کافی است تیکِ گزینه Analysis ToolPak رو بزنید! و بعدش هم دکمه OK رو بزنید!

تموم شد! افزونه Data Analysis برای شما الان فعال شده و میتونید اووون رو در تبِ Data و اووون آخر (سمت راست) ببینید!

خلاصه مطابق تصویر زیر روی Data Analysis کلیک کنید!

محاسبه کواریانس با افزونه Data Analysis در Excel (اکسل)

پس از باز کردن افزونه Data Analysis که در بالاتر آموزش دادیم، پنجره زیر باز می‌شود! توی لیست موجود، یک سری گزینه وجود داره، یکی از گزینه‌هاش Covariance است! و گزینه دیگه‌ای رو هم بالای همین کواریانس می‌بینید به اسم Correlation (به معنای همبستگی) که در بخش دوم همین آموزش باهاش کار داریم!

بپردازیم به همووون کواریانس خودمون!

بعد از اینکه که روی Covariance کلیک کردید، پنجره‌ای مشابه تصویر زیر باز میشه!

input range: در این قسمت باید داده‌های خودتون رو بهش ورودی بدید! مثلاً در داده‌های زیر رو در نظر بگیرید!

در این مجموعه داده، ما اومدیم سلول‌های A2 تا B9 رو انتخاب کردیم! (توجه کنید که خود Label ها رو هم انتخاب کردیم (Label به معنی برچسب)! شاید بپرسید Label چیست؟!)

اگر دقت کنید در سلول A2 و B2 نوشته شده X و Y! قبول دارید که اینا عدد نیستن! ولی در واقع یه برچسب هستن واسه اعداد زیرشووون که بیان میکنن: آقا زیر سلول A2 اعداد X وجود داره! و زیر اعداد سلول B2 اعداد Y وجود داره و این یعنی اینکه سلول‌های A2 و B2 برچسب‌هایی هستن واسه اعداد زیرشوووون!

و اون جایی هم که می‌بینید تیکِ گزینه Labels in First Row رو زدیم! (معنی: برچسب‌ها در سطر اول وجود دارند!) یعنی عملاً داریم به اکسل میگیم، آقای Excel ما برچسب‌هامون رو توی سطر اول این محدوده‌ای که بهت دادیم، قرار دادیم!

grouped by: در این قسمت میگه که اون ورودی‌هایی که دادیم بر اساس چی گروه بندی شدن؟! که واضحه توی داده‌های ما بر اساس ستون گروه بندی شدن! (یعنی اعداد مربوط به X توووی یک ستون هستن! و اعداد مربوط به Y توووی یک ستون دیگه هستن!) پس در اینجا ما می‌گیم اعداد بر اساس ستون (Column گروه بندی شدن!)

توجه:

اگر یه وقتی، یه جایی مثالی داشتید که گروه بندی در اون براساس ردیف بود (Row)، در اون صورت کافیه گزینه Row به معنی ردیف رو انتخاب کنید! همین!

Output Options: در اینجا هم بنده خدای اکسل (Excel) میخواد بدونه خروجی تحلیل هاش رو به شما چجوری تحویل بده؟! که گزینه‌ها زیر رو داره:

1- Output range: که شما یک بازه (یک range) رو در sheet اکسل مشخص می‌کنید واسه خروجی این تحلیل‌ها!

2- New Worksheet ply: اگر اینو انتخاب کنید، تحلیل‌ها رو در یک Sheet دیگه به شما خروجی میده!

3- New Workbook: اگر اینو انتخاب کنید، خروجی رو در یک Workbook دیگه به شما خروجی میده!

در اینجا ما همون گزینه 1 رو انتخاب میکنیم! و از اکسل میخوایم تا خروجی‌هاش رو در قالب یک range در خود sheet های خودش به ما نشون بده! که مثلاً ما (همینطور که در تصویر مربوطه می‌بینید) وارد کردیم: سلول C3! و منظورمون اینه که آقای Excel! شروع خروجی‌هات این سلول باشه! (چون چند سلول برای خروجی دادن، خواهیم داشت، ولی ما فقط یک سلول میدیم، میگیم آقا، شما از اینجا شروع کن و خروجی ها رو به ترتیب از اینجا به بعد واسه ما چاپ کن!)

بعد از اینکه OK بزنیم! محاسبات مربوط به کواریانس مطابق تصویر زیر انجام میشه!

و مثلاً در تصویر فوق، D5 مربوط به کواریانس Y و X است! یعنی Cov(Y,X)

شاید بپرسید چرا بعضی سلول‌ها رو خالی گذاشته؟! دلیلش اینه که همینطور که از آمار می‌دونید Cov(X,Y) با Cov(Y,X) برابره! و اکسل خودش رو دوبار به زحمت نمیندازه! و فقط یکیش رو چاپ میکنه!

محاسبه همبستگی (Correlation) با افزونه Data Analysis در اکسل (Excel)

در پنجره زیر که قبلاً هم دیدیم، حالا اینبار به جای کلیک بر روی Covariance، روووی Correlation کلیک کنید!

خب! حالا وقتی Correlation رو انتخاب کردید و OK بزنید! پنجره مشابه زیر ظاهر میشه! که ما پُر شده و تکمیل شده این پنجره رو برای داده‌های خودمون براتووون در تصویر زیر گذاشتیم!  چون کاملاً مشابه همون توضیحات کواریانس است، دیگه اجازه بدید، دوباره کاری نکنیم! و دوباره توضیح ندیم!

فقط یک نکته: اگر دقت کنید می‌بیینید که در بخش input range دیگه Label ها رو انتخاب نکردیم! (هرچند اصلاً کار خوبی نیست! و بعد توووی قسمت خروجی بهتون نشون میدم که چرا خوب نیست! ولی برای اهداف آموزشی اینکار رو کردیم!)

و در ضمن چون دیگه Label رو انتخاب نکردم، پس تیک اون Labels in First Row رو هم نزدیم!

بعد از کلیک بر رو دکمه OK نتایج رو مطابق تصویر زیر مشاهده می‌کنید!

فرمول های محاسباتی این محاسبات

شاید براتون این سوال پیش بیاد که براساس چه فرمول‌ها و روابطی، اکسل میاد این ها رو محاسبه می‌کنه! در این صورت برای جواب به شما، باید تصویر زیر رو به عنوان بحث پایانی تقدیم شما کنیم!

دانلود فیلم آموزشی

دانلود فیلم (حجم: حدود 12 مگابایت)(مدت زمان: 5 دقیقه و 50 ثانیه)