تابع COVARIANCE.P در اکسل

تابع COVARIANCE.P در اکسل برای محاسبه کوواریانس جمعیت (population covariance) بین دو مجموعه داده عددی استفاده می‌شود. کوواریانس معیاری است که نشان می‌دهد چگونه دو متغیر با هم تغییر می‌کنند: مقادیر مثبت یعنی هر دو معمولاً با هم بالا یا پایین می‌روند و مقادیر منفی نشان‌دهنده حرکت برعکس هستند. تابع COVARIANCE.P نسخه‌ای است که قسمت مخرج را بر اساس اندازهٔ کل جامعه (n) تقسیم می‌کند، نه نمونه (n-1).

نحو (Syntax) و پارامترها

نحو پایه تابع به صورت زیر است:

=COVARIANCE.P(array1, array2)

توضیحات پارامترها:

• array1: محدوده یا آرایهٔ اول حاوی مقادیر عددی.
• array2: محدوده یا آرایهٔ دوم حاوی مقادیر عددی. طول دو آرایه باید یکسان باشد.

تفاوت COVARIANCE.P و COVARIANCE.S و COVAR

• COVARIANCE.P: کوواریانس براساس جمعیت کامل؛ تقسیم بر n.
• COVARIANCE.S: کوواریانس براساس نمونه؛ تقسیم بر n-1 (تصحیح بیزانس/نمونه).
• COVAR: تابع قدیمی که در نسخه‌های قبلی اکسل برای کوواریانس استفاده می‌شد؛ از نظر عملکرد نزدیک به COVARIANCE.P است اما نگهداری شده برای سازگاری.

مثال ساده در اکسل

اگر داده‌های بالا در بردارهای A2:A6 و B2:B6 قرار داشته باشند، فرمول به شکل زیر خواهد بود:

=COVARIANCE.P(A2:A6, B2:B6)

این فرمول کوواریانس جمعیت بین بازده‌های دارایی A و B را برمی‌گرداند. مقدار مثبت نشان می‌دهد که معمولاً بازده‌ها با هم حرکت می‌کنند.

محاسبه دستی — گام‌به‌گام

برای فهم بهتر، فرمول ریاضی کوواریانس جمعیت بین دو مجموعه {x_i} و {y_i} به صورت زیر است:

Cov(X,Y) = (1/n) * Σ (x_i - μ_x) * (y_i - μ_y)

که در آن μ_x و μ_y میانگین‌های جمعیت برای هر مجموعه هستند و n تعداد مشاهدات است. اکسل با تابع COVARIANCE.P همین محاسبه را برای شما انجام می‌دهد و نیازی به محاسبهٔ دستی نیست مگر برای آموزش یا بررسی گام‌به‌گام.

مثال پیشرفته: استفاده در تحلیل پرتفوی

در مدل‌سازی ریسک و بازده پرتفوی، ماتریس کوواریانس ورودی اصلی برای محاسبه واریانس پرتفوی است. برای دو دارایی با وزن‌های w1 و w2، واریانس پرتفوی به شکل زیر است:

Var(portfolio) = w1^2 * Var1 + w2^2 * Var2 + 2*w1*w2*Cov12

در اینجا Cov12 را با COVARIANCE.P محاسبه کنید تا از کوواریانس جمعیت بین بازده‌های دو دارایی استفاده شود. اگر داده‌ها نمایانگر کل جامعه مورد نظر (مثلاً کل بازه زمانی مورد نظر) باشند، استفاده از COVARIANCE.P منطقی است؛ اما اگر فقط نمونه‌ای از جامعه دارید، بهتر است COVARIANCE.S را استفاده کنید.

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

• طول دو آرایه باید یکسان باشد؛ در غیر این صورت اکسل خطا می‌دهد.
• اگر یکی از آرایه‌ها شامل مقادیر غیر عددی یا سلول‌های خالی شود، اکسل معمولاً آن‌ها را نادیده می‌گیرد یا خطاهای #DIV/0! یا #N/A بازمی‌گرداند. بهتر است داده‌ها را پاکسازی کنید.
• در نسخه‌های قدیمی‌تر اکسل ممکن است به جای COVARIANCE.P از COVAR استفاده شود؛ اما COVARIANCE.P و COVARIANCE.S از اکسل 2010 به بعد معرفی شده‌اند.
• برای جدول‌های ساختاری (Excel Tables) می‌توانید از مراجع ستونی استفاده کنید: =COVARIANCE.P(Table1[ReturnA], Table1[ReturnB])

مثال با آرایه‌های نامرتب — توجه به هم‌طول بودن

اگر A2:A6 پنج مقدار و B2:B5 چهار مقدار داشته باشد، تابع خطا می‌دهد. همیشه پیش از فراخوانی تابع تعداد مشاهدات را بررسی کنید:

=ROWS(A2:A6)=ROWS(B2:B6)

این چک ساده به شما کمک می‌کند تا اشتباه طول آرایه را قبل از اجرای COVARIANCE.P شناسایی کنید.

نمونه کد VBA برای محاسبه کوواریانس جمعیت

Function PopulationCovariance(rngX As Range, rngY As Range) As Variant
    Dim i As Long
    Dim n As Long
    If rngX.Count  rngY.Count Then
        PopulationCovariance = CVErr(xlErrRef)
        Exit Function
    End If
    n = rngX.Count
    Dim sumX As Double, sumY As Double
    For i = 1 To n
        sumX = sumX + rngX(i).Value
        sumY = sumY + rngY(i).Value
    Next i
    Dim meanX As Double, meanY As Double
    meanX = sumX / n
    meanY = sumY / n
    Dim s As Double
    For i = 1 To n
        s = s + (rngX(i).Value - meanX) * (rngY(i).Value - meanY)
    Next i
    PopulationCovariance = s / n
End Function

توضیح: این تابع VBA دو محدوده را می‌گیرد، میانگین‌ها را محاسبه می‌کند، سپس مجموع حاصل‌ضرب اختلاف‌ها را تقسیم بر n می‌کند تا کوواریانس جمعیت برگردانده شود. اگر اندازهٔ دو محدوده برابر نباشد، خطای ارجاع برگردانده می‌شود.

چه زمانی از COVARIANCE.P استفاده کنیم؟

• وقتی داده‌ها نشان‌دهنده کل جمعیت مورد مطالعه باشند (مثلاً همه مقادیر در بازهٔ زمانی موردنظر).
• در مدل‌های مالی که خواهان تخمین کوواریانس برای یک پنجرهٔ زمانی کامل هستید.
• برای تولید ماتریس کوواریانس در تحلیل پرتفو یا محاسبه ارزش در معرض ریسک (VaR) که از پارامترهای جمعیتی استفاده می‌شود.

خلاصه و پیشنهادات کاربردی

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

لطفاً از کمبود ها و مشکلات این محتوا برای ما بنویسید

ارسال