تابع DEVSQ در اکسل — محاسبه جمع مجذورات انحرافات

تابع DEVSQ در اکسل مجموع مربع‌های اختلاف هر مقدار نسبت به میانگین مجموعه داده را محاسبه می‌کند. این مقدار در تحلیل‌های آماری پایه‌ای مثل محاسبه واریانس، آنالیز واریانس (SST) و بررسی پراکندگی داده‌ها کاربرد دارد. در ادامه به شکل دقیق، مثال‌های عملی و نکات فنی مربوط به کاربرد این تابع پرداخته شده است.

فرمول و نحوه استفاده

• number1, number2, … : مقادیر عددی یا ارجاع به سلول/محدوده‌ای که شامل اعداد هستند.
• اگر آرگومان‌ها محدوده باشند، فقط مقادیر عددی در آن محدوده استفاده می‌شوند؛ سلول‌های خالی، متن و معمولاً مقادیر منطقی نادیده گرفته می‌شوند.

توضیح ریاضی

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

SUM((xi – mean)^2)

که xi هر مقدار در مجموعه و mean میانگین حسابی آن مجموعه است. بنابراین DEVSQ برابر است با مجموع مربع انحرافات نسبت به میانگین نمونه.

مثال عملی ساده

=DEVSQ(A1:A5)

فرض کنید در سلول‌های A1 تا A5 مقادیر زیر قرار دارند: 10, 12, 15, 9, 14

میانگین = (10+12+15+9+14) / 5 = 12

انحرافات: -2, 0, 3, -3, 2

مربعات: 4, 0, 9, 9, 4 → مجموع = 26

بنابراین =DEVSQ(A1:A5) مقدار 26 را برمی‌گرداند.

چگونه با واریانس و انحراف معیار مرتبط است

• نمونه واریانس (VAR.S): s^2 = DEVSQ(range) / (n – 1)
• واریانس جامعه (VAR.P): σ^2 = DEVSQ(range) / n
• انحراف معیار نمونه = SQRT(DEVSQ(range) / (n – 1))

با استفاده از مثال بالا: n = 5، DEVSQ = 26 → واریانس نمونه = 26 / (5-1) = 6.5، واریانس جامعه = 26 / 5 = 5.2.

موارد کاربرد و مثال‌های واقعی

• محاسبه SST در رگرسیون یا ANOVA: جمع مربعات کل نسبت به میانگین کل داده برابر است با DEVSQ(کل داده‌ها).
• ارزیابی پراکندگی در کنترل کیفیت: هنگام مقایسه تغییرپذیری بین دسته‌ها، DEVSQ به‌عنوان مبنایی برای محاسبه نسبت‌ها استفاده می‌شود.
• پیش‌پردازش در محاسبات دستی و مقایسه با SSQ یا SUMXMY2 (برای مقایسه بین دو آرایه).

تفاوت با توابع مشابه

نکات فنی و محدودیت‌ها

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

نمونه کد VBA برای فراخوانی DEVSQ

Sub ExampleDevSq()
    Dim rng As Range
    Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A1:A5")
    Dim result As Double
    result = Application.WorksheetFunction.DevSq(rng)
    MsgBox "DEVSQ = " & result
End Sub

توضیح: این ماکرو مقدار محدوده A1:A5 را گرفته و با استفاده از متد WorksheetFunction.DevSq مقدار DEVSQ را محاسبه می‌کند و نتیجه را در یک پیام نمایش می‌دهد. کاربرد: اگر بخواهید محاسبات آماری را در VBA انجام دهید و از توابع اکسل بهره ببرید.

مثال ترکیبی: محاسبه واریانس نمونه با DEVSQ در اکسل

=DEVSQ(A1:A5)/(COUNT(A1:A5)-1)

این فرمول DEVSQ را تقسیم بر (n-1) می‌کند تا واریانس نمونه (VAR.S مشابه) به‌دست آید. اگر می‌خواهید همان مقدار را با توابع داخلی اکسل بگیرید، =VAR.S(A1:A5) نتیجه مشابهی می‌دهد.

نکات کاربردی و توصیه‌های تخصصی

• برای گزارش‌دهی آماری، به‌جای ارسال مستقیم DEVSQ به دیگران، بهتر است همراه آن n، میانگین و واریانس/انحراف معیار را نیز گزارش کنید تا تفسیر آسان‌تر باشد.
• در آنالیزهای مدل‌سازی (مثلاً رگرسیون)، DEVSQ مجموعه کل (SST) و DEVSQ باقی‌مانده‌ها (SSE) معیارهای مهمی برای محاسبه R^2 و تحلیل مدل‌اند.
• برای مجموعه‌های بزرگ داده، استفاده از توابع مرجع‌دار و پردازش بلوکی در VBA یا Power Query می‌تواند کارایی را بالا ببرد و از مشکلات دقت جلوگیری کند.

خلاصه

تابع DEVSQ ابزاری ساده ولی پُرکاربرد برای محاسبه مجموع مربع انحرافات نسبت به میانگین است. این تابع پایه‌ای برای محاسبه واریانس، آنالیزهای آماری و ارزیابی پراکندگی داده‌ها محسوب می‌شود. با درک روابط بین DEVSQ و توابعی مانند VAR.S، VAR.P، SSQ و SUMXMY2 می‌توانید محاسبات آماری دقیق‌تری در اکسل انجام دهید.

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

ارسال