تابع covariance.s در اکسل

تابع COVARIANCE.S در اکسل برای محاسبه کوواریانس نمونه‌ای (sample covariance) بین دو مجموعه داده عددی به‌کار می‌رود. کوواریانس معیاری است که جهت و شدت تغییر هم‌زمان دو متغیر را نشان می‌دهد؛ مقدار مثبت نشان‌دهنده تمایل به تغییر هم‌جهت و مقدار منفی نشان‌دهنده تغییر در جهت عکس است. در ادامه، کاربردها، مثال واقعی، نکات فنی و بهترین شیوه‌ها توضیح داده شده‌اند.

قاعده و نحو تابع

نحو ساده تابع در اکسل:

=COVARIANCE.S(array1, array2)

که در آن array1 و array2 دو محدوده یا نام‌های جدول شامل مقادیر عددی هستند. تابع میانگین نمونه‌ای را محاسبه کرده و مجموع ضرب انحراف‑ها را بر n−1 تقسیم می‌کند (n تعداد مشاهدات).

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

فرض کنید دو دارایی با بازده ماهانه زیر داریم. جدول را در اکسل در ستون‌های A و B قرار دهید.

ردیف	بازده A	بازده B
1	0.02	0.01
2	0.03	0.025
3	-0.01	0.00
4	0.04	0.03
5	0.00	-0.005

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

=COVARIANCE.S(B2:B6, C2:C6)

این فرمول کوواریانس نمونه‌ای بین بازده‌های ستون B و C را محاسبه می‌کند و بر n−1 تقسیم می‌کند (در این مثال n=5). مقدار عددی حاصل حدود 0.0002975 خواهد بود. این عدد نشان‌دهنده هم‌جهت حرکت قوی بین دو بازده است.

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

برای تبدیل کوواریانس به ضریب همبستگی (که در بازه −1 تا +1 قرار می‌گیرد) از رابطه زیر استفاده می‌شود:

=COVARIANCE.S(range1, range2) / (STDEV.S(range1) * STDEV.S(range2))

که STDEV.S انحراف معیار نمونه‌ای را محاسبه می‌کند. با داده مثال بالا، ضریب همبستگی حدود 0.94 است که نشان از وابستگی مثبت قوی دارد.

مثال با نام‌گذاری محدوده و جدول اکسل

برای خوانایی و نگهداری بهتر از نام‌گذاری محدوده‌ها یا جدول اکسل استفاده کنید:

=COVARIANCE.S(Returns[AssetA], Returns[AssetB])

با استفاده از جدول (Table) و نام ستون‌ها، فرمول‌ها امن‌تر و قابل‌فهم‌تر خواهند بود؛ مخصوصاً هنگام اضافه یا حذف داده‌ها.

موارد کاربرد واقعی

محاسبه ریسک پرتفوی و همبستگی بین بازده دارایی‌ها.
پیش‌پردازش داده در یادگیری ماشین: انتخاب ویژگی‌ها بر اساس همبستگی/کوواریانس.
تحلیل داده‌های اقتصادی و آماری برای بررسی وابستگی بین دو متغیر.

نکات فنی و خطاهای متداول

اندازه آرایه‌ها: دو آرایه باید طول یکسان داشته باشند؛ در غیر این صورت #N/A برگردانده می‌شود.
مقادیر غیرعددی: سلول‌های متنی یا خالی معمولاً نادیده گرفته می‌شوند؛ اگر تعداد مقادیر عددی در هر آرایه کمتر از 2 باشد، خطای #DIV/0! رخ می‌دهد.
تفاوت با COVARIANCE.P: تابع COVARIANCE.S برای نمونه (n−1) و COVARIANCE.P برای جامعه (n) استفاده می‌شود. انتخاب بستگی به این دارد که داده‌ها را «نمونه‌ای» از یک جمعیت بزرگ می‌دانید یا «تمامی» جمعیت.
دقت عددی: کوواریانس به واحدهای متغیر بستگی دارد؛ برای مقایسه باید از ضریب همبستگی استفاده شود.

بهترین روش‌ها و بهینه‌سازی

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

مثال محاسبه دستی (خلاصه مراحل)

مراحل محاسبه کوواریانس نمونه‌ای به‌صورت دستی:

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

با روش دستی می‌توانید صحت خروجی اکسل را نیز کنترل کنید.

خلاصه و جمع‌بندی

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

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

آیا این مطلب برای شما مفید بود ؟

خیر

بله