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

۵۵۵۵ بازدید
آخرین به‌روزرسانی: ۰۹ خرداد ۱۴۰۲
زمان مطالعه: ۱۳ دقیقه
محاسبه کوواریانس و ضریب همبستگی در اکسل — به زبان ساده

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

برای آشنایی بیشتر با مفهوم کوواریانس بهتر است ابتدا نوشتار امید ریاضی (Mathematical Expectation) — مفاهیم و کاربردها و به منظور آگاهی از نحوه محاسبه انواع ضریب همبستگی نیز مطلب ضریب‌های همبستگی (Correlation Coefficients) و شیوه‌ محاسبه آن‌ها — به زبان ساده را بخوانید. البته خواندن نوشتار واریانس و اندازه‌های پراکندگی — به زبان ساده و محاسبه واریانس در اکسل — به زبان ساده نیز خالی از لطف نیست.

محاسبه کوواریانس و ضریب همبستگی

معمولا از دو شیوه یا روش برای اندازه‌گیری میزان ارتباط بین دو متغیر یا دو سری داده استفاده می‌شود. محاسبه کوواریانس یکی از این شاخص‌ها و روش‌ها است. برای مثال اگر به نمودارهای زیر توجه کنید، ارتباط بین دو متغیر که یکی روی محور افقی و دیگری در محور عمودی ظاهر شده، به خوبی وجود رابطه را نشان می‌دهد.

مشخص است که در تصویر شماره ۱ با افزایش یکی، دیگری نیز افزایش یافته است. به چنین حالتی، رابطه‌ مستقیم می‌گویند.

direct relation
تصویر شماره ۱- رابطه مستقیم

همچنین در نموداری که در تصویر شماره ۲ دیده می‌شود، وجود رابطه معکوس بین دو متغیر مشخص است به این معنی که با افزایش یکی، دیگری کاهش می‌یابد.

INVERSE RELATION
تصویر شماره ۲- رابطه معکوس

کوواریانس به واحد اندازه‌گیری دو متغیر حساس است. اگر بخواهیم حساسیت به مقیاس را از بین ببریم، محاسبه ضریب همبستگی اهمیت پیدا می‌کند. در ادامه متن ابتدا به معرفی کوواریانس پرداخته، سپس شیوه محاسبه ضریب همبستگی را معرفی می‌کنیم.

محاسبه کوواریانس

«کوواریانس» (Covariance)، شاخصی است که میزان هماهنگی یا تغییرات بین دو متغیر را نشان می‌دهد. اگر جهت تغییرات بین دو متغیر یکسان باشد مقدار کوواریانس مثبت و در صورتی که دو متغیر در جهت عکس یکدیگر تغییر کنند، مقدار کوواریانس منفی خواهد بود. به این ترتیب اگر با افزایش متغیر $$X$$ متغیر $$Y$$ نیز افزایش یابد، کوواریانس و البته «ضریب همبستگی» (Correlation Coefficient) مثبت خواهد بود. همین توضیح را در زمانی که کاهش متغیر $$X$$ منجر به کاهش متغیر $$Y$$ نیز شود می‌توان به کار برد. در هر دو صورت، کوواریانس و ضریب همبستگی مثبت هستند زیرا جهت تغییرات دو متغیر یکسان است.

برای مثال، فرض کنید میزان ارزش سهام یک شرکت و سود سهامداران دارای رابطه مستقیم است در نتیجه کوواریانس مثبت خواهد بود. به این معنی که با افزایش یا کاهش یکی، دیگری هم به همان ترتیب افزایش یا کاهش خواهد داشت. بزرگی کوواریانس و ضریب همبستگی نشان دهنده شدت رابطه بین دو متغیر نیز هست. به این معنی که اگر کوواریانس بین ارزش سهام و سود سهامداران در بین دو شرکت به ترتیب برابر با ۱۰۰+ و ۱۵+ باشد، به نظر می‌رسد که سرمایه‌گذاری در شرکت دوم به صرفه‌تر است.

ولی اگر با افزایش یکی دیگری کاهش یابد، کوواریانس منفی است تا نشان دهد که جهت تغییرات دو متغیر عکس یکدیگر است. برای مثال اگر $$X$$‌ افزایش یابد، همزمان $$Y$$ نیز کاهش یابد، کوواریانس و ضریب همبستگی منفی شده و بیانگر تغییرات دو متغیر در جهت عکس یکدیگر است. برای مثال می‌توان میزان ساعات مطالعه کتاب و قیمت کتاب در کشور را در نظر گرفت. هر چه قیمت کتاب افزایش یابد ساعت مطالعه کاهش خواهد یافت و برعکس با کاهش قیمت کتاب، ساعات مطالعه در کشور افزایشی خواهد بود. بنابراین اگر در مقایسه سال ۱۳۹۷ و ۱۳۹۸، کوواریانس این دو متغیر به ترتیب ۱۲۰- و ۵۰۰- باشد، نشان می‌دهد که در سال ۱۳۹۸ افزایش قیمت کتاب، کاهش زمان مطالعه بیشتری را نسبت به سال ۱۳۹۷ رقم زده است.

مثال ۱

محاسبه کوواریانس بسیار ساده است. با استفاده از داده‌های ارائه شده در جدول زیر با نحوه اندازه‌گیری کوواریانس آشنا می‌شویم. این داده‌ها مربوط به ارزش سهام و سود 8 شرکت بورسی در سال ۱۳۹۷ است.

نام شرکتالفبپتثجچح
ارزش سهام (تومان)۲۰۰۲۵۰۳۰۰۱۲۰۱۴۰۴۰۱۰۰۱۵۰
سود سهام (تومان)201530101551015

فرض کنید ارزش سهام را با $$X$$ و سود سهام را با $$Y$$‌ نشان دهیم. فرمول محاسباتی برای کوواریانس بین $$X$$ و $$Y$$ به شکل زیر محاسبه می‌شود.

$$\large Covariance(X,Y)=\dfrac{1}{n}\sum_{i=1}^n(X_i-\overline{X})(Y_i-\overline{Y})$$

فرمول ۱

مشخص است که $$\overline{X}$$ و $$\overline{Y}$$ میانگین $$X$$ و $$Y$$ را نشان می‌دهند.

نکته: با توجه به فرمول ارائه شده به نظر می‌رسد که کوواریانس خاصیت تقارن دارد. به این معنی که $$Covariance(X,Y)=Covariance(Y,X)$$. پس اگر کوواریانس $$X$$ با $$Y$$ را محاسبه کرده‌ایم دیگر احتیاجی به محاسبه کوواریانس $$Y$$ با $$X$$ نیست.

حال براساس اطلاعات جدول بالا، محاسبات را طبق فرمول ۱، انجام داده و در جدولی به مانند زیر بازنویسی می‌کنیم.

نام شرکت$$X$$$$X-\overline{X}$$$$Y$$$$Y-\overline{Y}$$$$(X-\overline{X})(Y-\overline{Y})$$
الف200200-162.5=37.52020-15=537.5 ×5=187.5
ب250250-162.5=87.51515-15=087.5 ×0= 0
پ300137.530152062.5
ت120-42.510-5212.5
ث140-22.51500
ج40-122.55-101225
چ100-62.510-5312.5
ح150-12.51500
جمع1300120
میانگین162.515500

از آنجایی که مقدار کوواریانس مثبت است نشان می‌دهد که وابستگی یا ارتباط بین دو متغیر ارزش سهام و سود سهامداران در یک راستا است.

نکته: از آنجایی که هر یک از متغیرها با واحد تومان اندازه‌گیری شده‌اند، واحد اندازه‌گیری کوواریانس در اینجا به صورت تومان مربع (تومان به توان ۲) است زیرا بواسطه ضرب دو مقدار برحسب تومان محاسبه شده است. بنابراین اگر هنگام محاسبه کوواریانس، یک متغیر برحسب کیلوگرم و متغیر دوم برحسب متر اندازه‌گیری شده باشند، کوواریانس آن‌ها برحسب «کیلوگرم در متر» خواهد بود.

می‌توان محاسبه کوواریانس را به شکل ساده‌تری نیز انجام داد. کافی است که فرمول ارائه شده در قسمت قبل را کمی باز کنیم. در ضمن از این به بعد کوواریانس بین دو متغیر $$X$$ و $$Y$$ را با $$Cov(X,Y)$$ نشان خواهیم داد.

$$\large Cov(X,Y)=\dfrac{1}{n}\sum_{i=1}^n(X_i-\overline{X})(Y_i-\overline{Y})=\\ \large \dfrac{1}{n}\sum_{i=1}^n(X_iY_i-X_i\overline{Y}-\overline{X}Y_i+\overline{X}\overline{Y})=\\ \large \dfrac{1}{n}\sum_{i=1}^n X_iY_i-\dfrac{1}{n}\overline{Y}\sum_{i=1}^nX_i-\dfrac{1}{n}\overline{X}\sum_{i=1}^nY_i+\dfrac{1}{n}\sum_{i=1}^n\overline{X}\overline{Y}=\\ \large \overline{XY}-\overline{X}\overline{Y}-\overline{X}\overline{Y}+\overline{X}\overline{Y}=\\ \large \overline{XY}-\overline{X}\overline{Y}$$

بنابراین شیوه ساده‌تر برای محاسبه کوواریانس در فرمول ۲ دیده می‌شود.

$$\large Cov(X,Y)=\overline{XY}-\overline{X}\overline{Y}$$

مشخص است که در اینجا منظور از $$\overline{X}$$ میانگین حاصلضرب دو متغیر است. به این ترتیب جدول بالا را به صورت زیر بازنویسی می‌کنیم تا مقدارهای مورد نظر برای محاسبه کوواریانس بدست آیند.

نام شرکت$$X$$$$Y$$$$X \cdot Y$$
الف200204000
ب250153750
پ300309000
ت120101200
ث140152100
ج405200
چ100101000
ح150152250
جمع130012023500
میانگین162.5152937.5

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

$$\large Cov(X,Y)= 2937.5 - (162.5 \times 15) = 2937.5- 2437.5 = 500$$

نکته: با توجه به نحوه محاسبه کوواریانس در فرمول ۱ یا فرمول ۲، مشخص است که اگر مقدارهای ارزش سهام و سود سهام را برحسب ریال تنظیم می‌کردیم، مقدار کوواریانس ۱۰ برابر می‌شد. بنابراین اگر می‌خواهیم کوواریانس یا میزان رابطه بین ارزش سهام و سود سهام را در بین این دو شرکت برای سال ۱۳۹۷ و ۱۳۹۸ مقایسه کنیم، حتما باید از یک واحد پولی (مثلا تومان) استفاده کرده تا انجام مقایسه صحیح باشد. در غیر اینصورت اگر مقیاس اندازه‌گیری در سال ۱۳۹۷ برحسب تومان و در سال ۱۳۹۸ برحسب ریال باشد، ممکن است تفسیر مناسبی از این موضوع حاصل نشده و به نظر برسد که در سال ۱۳۹۸ میزان ارتباط بین دو متغیر ۱۰ برابر سال ۱۳۹۷ است.

محاسبه کوواریانس در اکسل

در ادامه سعی می‌کنیم که محاسبه کوواریانس را در محیط اکسل برای داده‌های مثال قبل اجرا کنیم. در تصویر زیر داده‌های وارد شده و انواع توابع محاسباتی برای کوواریانس دیده می‌شود.

همانطور که می‌بینید سه تابع به نام‌های COVARIACEN.P, COVARIANCE.S, COVAR برای محاسبه کوواریانس در اکسل وجود دارند. هر چند پارامترهای این توابع دقیقا یکی هستند ولی شیوه محاسبه آن‌ها کمی متفاوت است.

تابع COVARIANCE.P ،COVARIANCE.S و COVAR

این سه تابع مطابق با فرمول ۱ یا مترادف آن یعنی فرمول ۲، محاسبه کوواریانس را انجام می‌دهند. با این تفاوت که توابع COVARIANCE.P و COVAR که به «کوواریانس جامعه آماری» (Population Covariance) معروف هستند مقدار $$n$$ را همان تعداد مشاهدات در نظر می‌گیرند ولی در شیوه محاسبه COVARIANCE.S که به «کوواریانس نمونه‌ای» (Sample Covariance) معروف است، مقدار $$n$$ یکی کمتر از تعداد مشاهدات در فرمول‌های یاد شده است.

پارامترهای مربوط به این توابع به صورت زیر است.

COVARIANCE.P(array1,array2)

COVARIANCE.S(array1,array2)

COVAR(array1,array2)

منظور از array1 همان $$X$$ و array2 نیز $$Y$$ در فرمول‌های گفته شده است که می‌توانند یک بردار شامل مجموعه‌ای از مقدارها یا آدرس ناحیه‌ای از کاربرگ اکسل باشند. باید دقت کنید که اندازه این بردارها باید دقیقا یکسان باشند به این معنی که اگر array1 شامل ناحیه‌ای با ۱۰ سلول است، باید array2 نیز ناحیه‌ای‌ با ۱۰ سلول را مشخص کند. در غیر اینصورت در سلول مورد نظر با خطای $$#VALUE!$$ یا $$#N/A$$ مواجه خواهید شد. به این ترتیب، شکل ورود این توابع و مقدار محاسبه شده هر یک در اکسل به صورت زیر است.

excel data and covariance functions

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

نکته: تابع COVAR‌ مربوط به نسخه‌های قدیمی اکسل است و فقط به منظور سازگاری در نسخه‌های جدیدتر اکسل قرار گرفته است. بهتر است در کاربرگ‌هایی که ایجاد می کنید از این تابع استفاده نکنید زیرا ممکن است در نسخه‌های آینده اکسل از این تابع پشتیبانی نشود.

برای دسترسی به این توابع می‌توانید از برگه Formula، دکمه More Functions را انتخاب کرده و از گروه Statistical تابع‌های COVARIANCE.P یا COVARIANCE.S را انتخاب و پارامترهای آن را مطابق با پنجره زیر تعیین کنید.

excel data and covariance function parameters

به منظور درج تابع COVAR باید از برگه Formula دکمه More Functions و گروه Compatibility را انتخاب و تابع COVAR را از درون لیست پیدا کنید. پنجره تعیین پارامترهای این تابع درست به مانند تصویر قبل است. پنجره Function Arguments یا پنجره پارامترهای تابع دو کادر به عنوان پارامترها توابع محاسبه کوواریانس به نام‌های Array1 و Array2 را نشان می‌دهد. اگر می‌خواهید اکسل راهنمای استفاده از این تابع را ظاهر کند، کافی است که روی لینک Help on this Function کلیک کنید. با فشردن دکمه OK (یا کلید Enter از صفحه کلید) محاسبه انجام شده و در سلول مورد نظر قرار می‌گیرد. ولی با زدن دکمه Cancel اعلام می‌کنید که از ثبت تابع در سلول صرفنظر کرده‌اید و به کاربرگ اطلاعاتی بازخواهید گشت.

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

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

اگر ضریب همبستگی مثبت باشد، هر دو متغیر هم جهت تغییر می‌کنند و اگر ضریب همبستگی منفی باشد، تغییرات دو متغیر عکس یکدیگر است.

نحوه محاسبه ضریب همبستگی بین دو متغیر $$X$$ و $$Y$$ مطابق با فرمول ۳ است. معمولا ضریب همبستگی را با علامت $$\rho$$ یا Corr نشان می‌دهند.

$$\large \rho(X,Y)=Corr(X,Y)=\dfrac{Cov(X,Y)}{\sigma_X \sigma_Y}$$

فرمول ۳

مشخص است که در مخرج کسر، منظور از $$sigma_X$$، انحراف معیار (جذر واریانس) متغیر $$X$$ است. همچنین $$\sigma_Y$$ نیز انحراف معیار متغیر $$Y$$ را نشان می‌دهد. در صورت کسر نیز همان کوواریانس بین دو متغیر محاسبه شده است. از آنجایی که صورت (کوواریانس) و حاصل ضرب انحراف معیارها هم واحد هستند، نسبت‌شان بدون واحد خواهد بود. این شیوه اندازه‌گیری ضریب همبستگی به نام «ضریب همبستگی پیرسون» (Pearson Correlation Coefficient) معروف است.

به این ترتیب اگر یک متغیر برحسب کیلوگرم و متغیر دوم برحسب متر باشد، مقدار ضریب همبستگی بدون واحد بیان خواهد شد. به همین علت برای مقایسه شدت رابطه برای دو متغیر در وضعیت‌های مختلف ضریب همبستگی بسیار مناسب است. برای مثال ممکن است بخواهیم شدت رابطه بین ارزش سهام و سود سهامداران را در دو سال مختلف با هم مقایسه کنیم. در سال اول این متغیرها برحسب تومان اندازه‌گیری شده‌اند. در سال دوم این متغیرها برحسب ریال معرفی شده‌اند. برای آنکه بتوانیم با حذف واحد اندازه‌گیری شدت رابطه بین دو متغیر را در این دو سال مقایسه کنیم، ضریب همبستگی را به کار می‌بریم.

به منظور یادآوری، فرمول محاسبه واریانس جامعه و واریانس نمونه، برای متغیر $$x$$ را در ادامه مشاهده می‌کنید.

واریانس جامعه:$$\frac{\sum_{i=1}^n(x_i-\overline{x})}{n}$$

 واریانس نمونه: $$\frac{\sum_{i=1}^n(x_i-\overline{x})}{n-1}$$

مقدار ضریب همبستگی در فاصله $$[-1,1]$$ تغییر می‌کند. هر چه مقدار ضریب همبستگی به ۱ یا 1- نزدیکتر باشد، میزان رابطه خطی بین دو متغیر شدید‌تر است. اگر ضریب همبستگی به صفر نزدیک باشد، رابطه خطی بین دو متغیر ضعیف در نظر گرفته می‌شود. البته هنگامی که ضریب همبستگی به ۱+ نزدیک باشد، رابطه شدید خطی و از نوع مستقیم و در یک جهت است. در صورتی که ضریب همبستگی به ۱- نزدیک باشد، رابطه خطی بین دو متغیر به صورت معکوس است.

نکته: از آنجایی که ضریب همبستگی برحسب کوواریانس محاسبه می‌شود، خاصیت تقارن را هم خواهد داشت. بنابراین ضریب همبستگی بین $$X$$ و $$Y$$ با ضریب همبستگی $$Y$$ با $$X$$ برابر است.

$$\large \rho(X,Y)=Corr(X,Y)=Corr(Y,X)=\rho(Y,X)$$

مثال 2

براساس داده‌های مربوط به مثال ۱، ضریب همبستگی را محاسبه می‌کنیم. از آنجایی که کوواریانس برابر با ۵۰۰ شده است، فقط کافی است که انحراف معیار را برای هر دو متغیر بدست آوریم. از آنجایی که محاسبه انحراف معیار به کمک واریانس صورت می‌گیرد، ابتدا واریانس را مطابق با روشی که در مطلب واریانس و اندازه‌های پراکندگی — به زبان ساده یا محاسبه واریانس در اکسل — به زبان ساده بیان شده است، بدست می‌آوریم. محاسبات مربوط به واریانس و سپس انحراف معیار در جدول زیر صورت گرفته است.

نام شرکت$$X$$$$X-\overline{X}$$$$(X-\overline{X})^2$$$$Y$$$$Y-\overline{Y}$$$$(Y-\overline{Y})^2$$
الف20037.51406.2520525
ب25087.57656.251500
پ300137.518906.253015225
ت120-42.51806.2510-525
ث140-22.5506.251500
ج40-122.515006.255-10100
چ100-62.53906.2515025
ح150-12.5156.251500
میانگین162.56168.751550

به این ترتیب واریانس جامعه آماری برای متغیر $$X$$ برابر با 6168.75 و برای متغیر $$Y$$ نیز 50 است. اگر از هر یک از این مقدارها جذر گرفته شود، انحراف معیار بدست می‌آید که به ترتیب برابر با $$\sigma_X= 78.54$$ و $$\sigma_Y=7.071$$ خواهد بود. با جایگذاری در فرمول ۳ نتیجه ضریب همبستگی بین دو متغیر $$X$$ و $$Y$$ حاصل می‌شود.

$$\large Corr(X,Y)=\dfrac{Cov(X,Y)}{\sigma_X \sigma_Y}=\dfrac{500}{78.54 \times 7.071}=0.90$$

نکته: اگر به جای استفاده از کوواریانس جامعه آماری از کوواریانس نمونه‌ای و به جای استفاده از واریانس‌ها جامعه از واریانس نمونه‌ای استفاده کنیم، ضریب همبستگی تغییری نخواهد کرد. به رابطه زیر دقت کنید.

$$\large Corr(X,Y)= \dfrac{Corr(X,Y)}{\sigma_X \sigma_Y}=\dfrac{\dfrac{\sum_{i=1}^n(X_i-\overline{X})(Y_i-\overline{Y})}{n-1}}{\left[\left(\dfrac{1}{n-1}\sum_{i=1}^n(X_i-\overline{X})\right) \times \left(\dfrac{1}{n-1}\sum_{i=1}^n(Y_i-\overline{Y})\right)\right]^\frac{1}{2}}$$

با ساده کردن عبارت $$n-1$$ در صورت و مخرج کسر، رابطه به شکل زیر در خواهد آمد. که شامل عبارت $$n-1$$ نیست. به همین علت ضریب همبستگی در اکسل برای جامعه و نمونه تابع مجزایی ندارد.

$$\large Corr(X,Y)=\dfrac{\sum_{i=1}^n(X_i-\overline{X})(Y_i-\overline{Y})}{\left(\sum_{i=1}^n(X_i-\overline{X}) \sum_{i=1}^n(Y_i-\overline{Y})\right)^\frac{1}{2}}$$

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

برای محاسبه ضریب همبستگی در اکسل از دو تابع می‌توان استفاده کرد. توابع CORELL و PEARSON هر دو محاسبه ضریب همبستگی را به عهده دارند، البته تابع CORREL نسبت به روش قبلی محاسبه ضریب همبستگی که در تابع PEARSON به کار رفته، دقیق‌تر و بخصوص برای اعداد بسیار بزرگ مناسب‌ است. برای دسترسی به این دو تابع باز هم از برگه Formula دکمه More Functions را انتخاب کرده و از گروه Statistical تابع‌های CORELL یا PEARSON را انتخاب و پارامترهای آن‌ها را مطابق با پنجره زیر تعیین کنید.

excel data and correlation function parameters

برای داده‌های مربوط به مثال ۱ ضریب همبستگی را محاسبه کرده‌ایم. همانطور که در پایین پنجره پارامترهای این تابع مشاهده می‌کنید، ضریب همبستگی تقریبا برابر با 0.9 است که بیانگر شدت زیاد و مستقیم رابطه بین دو متغیر ارزش سهام و سود سهامداران است. البته همین نتایج را با استفاده از تابع PEARSON نیز بدست خواهید آورد. ولی باید توجه داشت که در نسخه‌های قدیمی‌ اکسل تابع PEARSON هنگام «گِرد کردن» (Round) اعداد یک خطای محاسباتی مرتکب می‌شد که در نسخه‌های بعدی رفع شده ولی بهتر است به هر حال از تابع CORREL برای محاسبه ضریب همبستگی استفاده کنید.

نکته: اگر بردارهایی که در پارامترهای Array1 و Array2 معرفی می‌کنید، هم اندازه نباشند در سلول پیغام خطای $$#N/A$$ مشاهده خواهید کرد. همچنین اگر واریانس یا انحراف هر یک از بردارها صفر باشند (مقدارهای یکسان و تکراری باشند) پیغام خطای $$#DIV/0!$$ ظاهر خواهد شد. البته باید توجه داشت که اگر سلول‌هایی که در ناحیه مربوط به پارامترها معرفی شده‌اند، شامل مقدارهایی متنی یا منطقی باشند، در محاسبات نادیده گرفته می‌شوند.

محاسبه ماتریس کوواریانس و ماتریس ضریب همبستگی (بیش از دو متغیر)

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

مثال ۳

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

covariance matrix

همانطور که در نوار فرمول دیده می‌شود، از آدرس ترکیبی سلول‌ها در پارامتر اول به شکل $A$2:$A$11 و A$2:A$11 نوشته شده است. برای محاسبه کوواریانس برای سطرهای بعدی مثلا بین متغیر $$X3$$ و $$X2$$ باید فرمول را به شکل زیر بنویسید. برای مثال آدرس$$ $C$2 $$ نشان می‌دهد که باید هنگام کپی کردن این فرمول این آدرس بدون تغییر باقی بماند. از طرفی آدرس $$ B$2$$ نیز بیانگر ثابت بودن سطر دوم از ستون‌های مختلف خواهد بود.

covariance matrix elements

به همین ترتیب اگر لازم باشد که ضریب همبستگی را محاسبه کنیم، کافی است که به جای تابع COVARIANCE.P از CORREL استفاده شود.

نکته: اگر لازم است که کوواریانس نمونه‌ای را محاسبه کنید، باید از تابع COVARIANCE.S در فرمول‌ها استفاده کنید.

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

بعضی از توابع و محاسبات در اکسل به واسطه ابزارهایی که در آن قرار دارد، صورت می‌گیرند. کوواریانس و ضریب همبستگی نیز توسط ابزار «تحلیل داده» (Data Analysis) محاسبه می‌شوند. البته با این ابزارها می‌توانید ماتریس کوواریانس و ضریب همبستگی را برای بیش از دو متغیر نیز بدست آورید. برای ظاهر کردن دستور Data Analysis باید آن را در اکسل بارگذاری کنید. به این منظور روند زیر را طی کنید.

File -> Options -> Add-ins -> Excel Add-ins -> Go -> Analysis ToolPack

analysis toolpack

با انتخاب گزینه Analysis ToolPack و فشردن دکمه Ok در برگه Data گزینه‌‌ای به نام Data Analysis ظاهر خواهد شد. حال کافی است که این دکمه را انتخاب کرده تا فهرستی از دستورات و ابزارهای تحلیلی اکسل ظاهر شود.

analysis toolpack dialog

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

data input

حال در پنجره ابزار کوواریانس تنظیمات را مطابق با تصویر زیر در می‌آوریم. مراحل و نقش هر یک از بخش‌های مربوطه در ادامه مشخص می‌شوند.

covariance dialog

در بخش ۱ که قسمت Input Range نامیده می‌شود ناحیه یا آدرس سلول‌هایی را مشخص می‌کنید که باید کوواریانس برای آن‌ها محاسبه شود. در بخش شماره ۲ نیز نحوه برداشت متغیرها را در Input Range تعیین می‌کنید. اگر در قسمت Grouped By گزینه Columns را انتخاب کنید به این معنی است که باید کوواریانس بین ستون‌ها محاسبه شود. در صورتی که Rows انتخاب شود، کوواریانس بین سطرها حاصل خواهد شد.

در بخش ۳، با انتخاب Labels in First Row به اکسل می‌گویید که سطر اول جدول معرفی شده در Input Range شامل اسامی متغیرها نیز هست و نباید از آن برای محاسبات استفاده شود. در قسمت انتهایی (Output options) یا بخش ۴ نیز محل ایجاد ماتریس کوواریانس را تعیین می‌کنید. گزینه Output Range به شما اجازه می‌دهد که سلولی را انتخاب کنید که محل ایجاد ماتریس کوواریانس باشد. گزینه New Worksheet Ply هم برای نمایش ماتریس کوواریانس از یک کاربرگ جدید بهره می‌برد که می‌توانید نام آن را نیز در کادر روبرو وارد کنید. همچنین اگر لازم است که نتیجه این محاسبات را در کارپوشه دیگری ظاهر کنید، گزینه New Workbook، انتخاب مناسبی است. خروجی با توجه به تنظیمات انجام شده در تصویر قبلی به شکل زیر خواهد بود.

covariance add-ins output

نکته: کوواریانس بین متغیر $$X1$$ با $$X1$$ همان واریانس $$X1$$ خواهد بود. از طرفی چون کوواریانس خاصیت تقارن دارد، کوواریانس مربوط به بالای قطر اصلی نمایش داده نشده است.

برای محاسبه ضریب همبستگی نیز کافی است همین مراحل را طی کرده و گزینه Correlation را از پنجره Data Analysis انتخاب کنید. تنظیمات پنجره Correlation برای محاسبه ضریب همبستگی، نیز درست به مانند کوواریانس است. خروجی اجرای این دستور برای داده‌های قبلی به صورت زیر خواهد بود.

correlation add-ins output

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

اگر مطلب بالا برای شما مفید بوده است، آموزش‌های زیر نیز به شما پیشنهاد می‌شود:

^^

بر اساس رای ۳۴ نفر
آیا این مطلب برای شما مفید بود؟
اگر بازخوردی درباره این مطلب دارید یا پرسشی دارید که بدون پاسخ مانده است، آن را از طریق بخش نظرات مطرح کنید.
۶ دیدگاه برای «محاسبه کوواریانس و ضریب همبستگی در اکسل — به زبان ساده»

سلام
وقت بخیر
درمورد محاسبه آزمون کرونباخ سوال داشتم.
پرسشنامه من شامل سوالات طیفی لیکرت و سوالات چند گزینه ای هست چطور برای سوالات چند گزینه ای باید آزمون کرونباخ را محاسبه کنم؟

سلام چرا کوواریانس بین یک مغیر با خودش برابر با واریانس است
اثباتش چگونه است

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

از این که همراه مجله فرادرس هستید، سپاسگزاریم.
تندرست و شاد و پیروز باشید.

سلام
از چه طریقی می شود میزان تشابه بین ماتریس شماره A و B را با ماتریس شماره C نشان داد ؟
بهترین پارامتر آماری چیه و چطوری در اکسل میشه این کار انجام داد ؟
A,B دیتاهای من از دو روش هستن و C دیتاهای طبیعی هست
ابعاد ماتریس هام 5 در 5 هست و یکسان هستند

سلام و وقت بخیر
از اینکه همراه مجله فرادرس هستید بسیار خوشحالیم.

برای پاسخ به سوال شما باید بیشتر در مورد ساختار داده ها بدانیم. اگر ماتریسی که شما در نظر گرفته اید، مربوط به ۵ مشاهده است و هر ستون از ماتریس نشانگر ویژگی یا متغیرهای اندازه‌گیری شده هستند، تمامی روش‌های اندازه گیری فاصله یا شباهت قابل استفاده است. ولی اگر ماتریس‌های مورد نظر شما ماتریس کوواریانس باشند، مسئله بسیار پیچیده می‌شود. شاید با یک چرخش یا جابجایی سطر یا ستون هر یک از ماتریس، برابری با ماتریس دیگر مشخص شود. برای کسب اطلاعات بیشتر در موضوع بهتر است مطلب A simple procedure for the comparison of covariance matrices را مطالعه فرمایید. البته به زودی ترجمه آن در مجله فرادرس منتشر خواهد شد.

پیروز و پاینده باشید.

بسیار عالی و کاربردی توضیح داده شده بود.

نظر شما چیست؟

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *