تابع DSTDEV در اکسل

تابع DSTDEV یکی از توابع بانک اطلاعاتی (Database functions) در اکسل است که انحراف معیار نمونه (sample standard deviation) را از مجموعه‌ای از رکوردها محاسبه می‌کند که با معیارهای مشخص شده مطابق باشند. از DSTDEV زمانی استفاده می‌کنیم که بخواهیم انحراف معیار یک زیرمجموعه از داده‌ها — نه کل جدول — را بر اساس شرط‌ها به دست بیاوریم.

سینتکس تابع

فرم کلی:

• DSTDEV(database, field, criteria)
• database: ناحیه (شامل سطر سرستون‌ها) که داده‌ها در آن قرار دارد.
• field: نام ستون (مانند “Salary”) یا شماره ستون در ناحیه (1 برای ستون اول) که می‌خواهیم انحراف معیار آن را محاسبه کنیم.
• criteria: ناحیه‌ای که شامل سطر سرستون با همان نام(ها) و زیر آن شرط/شرط‌هاست.

مثال عملی با جدول نمونه

فرض کنید این جدول در محدوده A1:D6 قرار دارد. حال می‌خواهیم انحراف معیار حقوق (Salary) برای کارکنان بخش Sales را محاسبه کنیم.

فرمول در سلولی مانند H1:

• =DSTDEV(A1:D6, “Salary”, F1:F2)

این فرمول فقط رکوردهایی را که در ستون Dept مقدار “Sales” دارند انتخاب می‌کند و انحراف معیار نمونه مقادیر Salary آن‌ها را برمی‌گرداند (در مثال بالا سه رکورد: 4200، 5500، 6000).

نکات مهم و کاربردی

• DSTDEV مقدار انحراف معیار نمونه را بازمی‌گرداند؛ اگر بخواهید انحراف معیار جمعیت را محاسبه کنید از DSTDEVP یا توابع جدیدتر STDEV.P استفاده کنید.
• نام ستون در آرگومان field باید دقیقاً مانند سرستون جدول نوشته شود (حساس به فاصله و کاراکترها نیست اما متن باید یکسان باشد).
• برای field می‌توانید عدد اندیس ستون در محدوده database را نیز استفاده کنید: مثلاً 3 به معنی ستون سوم database است.
• اگر هیچ رکوردی مطابق criteria پیدا نشود، معمولاً خروجی خطای #DIV/0! یا خطای معنادار دیگری خواهد بود — در این حالت بهتر است از تابع IFERROR استفاده کنید.
• criteria می‌تواند شامل عملگرهای مقایسه‌ای و wildcardها باشد؛ مثال: “>5000” یا “*Sales*” برای متن‌ها (جایگزین کاراکتر).
• برای اعمال شرط‌های AND، معیارها را در یک سطر (زیر هدرها) قرار دهید؛ برای شرط‌های OR، معیارها را در سطرهای جداگانه قرار دهید.

نمونه معیارهای ترکیبی (AND و OR)

مثال: می‌خواهیم انحراف معیار حقوق برای کارکنان بخش Sales که سابقه کاری بیش از 4 سال دارند:

فرمول: =DSTDEV(A1:D6, “Salary”, F1:G2)

برای OR (مثلاً Dept = Sales یا Dept = HR) معیار را به شکل زیر قرار دهید:

فرمول: =DSTDEV(A1:D6, “Salary”, F1:F3)

خطاها و مشکلات رایج

• استفاده از نام ستون نادرست یا فاصله اضافی در header باعث عدم تطابق و برگشت نتایج اشتباه یا خطا می‌شود.
• قرار دادن criteria در بخش‌هایی از جدول database می‌تواند باعث همپوشانی و نتایج اشتباه شود؛ معیار باید ناحیه‌ای جدا و خارج از محدوده داده‌ها باشد.
• DSTDEV تنها اعداد را در محاسبه لحاظ می‌کند؛ سلول‌های متنی یا خالی نادیده گرفته می‌شوند.

مقایسه با توابع جدیدتر

در نسخه‌های جدید اکسل توابعی مثل STDEV.S و STDEV.P مرجع بهتری برای محاسبات استاندارد هستند. DSTDEV از نظر مفهوم با STDEV.S معادل است اما مزیت DSTDEV این است که با ساختار criteria کار می‌کند؛ یعنی فیلتر کردن بر پایه شرایط را مستقیم انجام می‌دهد. در برخی سناریوها استفاده از ترکیب FILTER + STDEV.S (در اکسل‌های جدید با تابع FILTER) یا استفاده از توابع جدول‌های پویا کارآمدتر و خواناتر است.

استفاده از VBA برای فراخوانی DSTDEV

Sub CalcDStDev()
    Dim rngDB As Range
    Dim rngCrit As Range
    Dim result As Double

    Set rngDB = ThisWorkbook.Worksheets("Sheet1").Range("A1:D6")
    Set rngCrit = ThisWorkbook.Worksheets("Sheet1").Range("F1:F2")

    result = Application.WorksheetFunction.DStDev(rngDB, "Salary", rngCrit)
    MsgBox "DSTDEV result: " & result
End Sub

این کد در VBA محدوده‌های database و criteria را تعیین می‌کند، سپس از WorksheetFunction.DStDev برای محاسبه استفاده می‌کند و نتیجه را در یک پیام نشان می‌دهد. اگر هیچ رکوردی مطابق نباشد، اجرای کد باعث بروز خطا خواهد شد؛ بهتر است قبل از فراخوانی از Error Handling استفاده شود.

نکات حرفه‌ای و توصیه‌ها

• برای گزارش‌گیری پیشرفته، نام‌گذاری محدوده‌ها (Named Ranges) و استفاده از آن‌ها در DSTDEV خوانایی فرمول‌ها را زیاد می‌کند.
• اگر داده‌ها به‌طور مداوم تغییر می‌کنند، از جدول (Insert → Table) استفاده کنید؛ سپس از نام ستون جدول در field و ناحیه جدول به‌عنوان database بهره بگیرید تا فرمول خودبه‌خود ناحیه را دنبال کند.
• برای تحلیل حساسیت، ترکیب DSTDEV با ابزارهایی مثل PivotTable یا Power Query نیز می‌تواند مفید باشد، هرچند در موارد پیچیده‌تر Power Query یا توابع داینامیک جدید ساده‌تر و سریع‌تر خواهند بود.

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

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

ارسال