معرفی تابع DSTDEVP در اکسل

تابع DSTDEVP در اکسل یک تابع از دسته توابع «پایگاه داده» (Database) است که انحراف معیار جامعه (population standard deviation) را برای یک فیلد مشخص در رکوردهایی که با شرایط داده‌شده مطابقت دارند محاسبه می‌کند. این تابع در مواقعی مفید است که داده‌ها در قالب یک جدول با سِرِ ستون (header) سازمان‌دهی شده و بخواهیم به‌صورت شرطی انحراف معیار را محاسبه کنیم.

کاربردهای رایج

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

نحو (Syntax) و آرگومان‌ها

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

=DSTDEVP(database, field, criteria)

توضیح آرگومان‌ها:

• database: محدوده‌ای که شامل جدول داده‌ها است؛ باید اولین ردیف شامل عناوین ستون باشد (headers).
• field: نام ستون موردنظر (به‌صورت متن بین نقل‌قول مانند “Amount”) یا شماره‌ای که نشان‌دهندهٔ موقعیت ستون در جدول است (مثلاً 3 برای ستون سوم).
• criteria: محدوده‌ای که شرایط (criteria) در آن تعریف شده‌اند؛ این محدوده نیز باید شامل هدرهای ستون باشد و می‌تواند شامل عملگرها مثل >, <= و کاراکترهای جایگزین مثل * باشد.

مثال ساده — محاسبه انحراف معیار مبلغ برای یک محصول

فرض کنید جدول داده‌های فروش به صورت زیر است:

اگر این جدول در محدوده A1:D5 قرار داشته باشد و بخواهیم انحراف معیار جامعه مقدار (Amount) برای محصول “Widget” را پیدا کنیم، می‌توانیم از یک محدوده criteria به شکل زیر استفاده کنیم (مثلاً F1:F2):

=DSTDEVP(A1:D5, "Amount", F1:F2)

توضیح: این فرمول انحراف معیار جامعه ستون Amount را فقط برای رکوردهایی محاسبه می‌کند که مقدار ستون Product برابر “Widget” است (در مثال بالا، رکوردهای 1001 و 1003 و 1004).

استفاده از شماره ستون به‌جای نام

به‌جای “Amount” می‌توانیم عدد 4 (چون Amount ستون چهارم است) بنویسیم:

=DSTDEVP(A1:D5, 4, F1:F2)

این روش در کپی/پیست یا زمانی که نام ستون پویا است کاربردی است، اما نام ستون خواناتر است و کمتر مستعد خطاست.

مثال پیشرفته — چندین شرط و عملگرها

اگر بخواهیم انحراف معیار Amount را برای محصولات Widget در منطقه North و با مقدار بیش از 1000 محاسبه کنیم، می‌توانیم یک محدوده criteria دو-ستونه ایجاد کنیم:

=DSTDEVP(A1:D5, "Amount", H1:I2)

توضیح: هر ردیف در محدوده criteria نشان‌دهنده مجموعه‌ای از شرط‌ها است که با هم AND عمل می‌کنند (یعنی Region=”North” AND Amount>1000). اگر چند ردیف criteria داشته باشید، بین ردیف‌ها OR اعمال می‌شود.

مثال استفاده از wildcard و متن

می‌توان از * و ? برای جستجوی متنی استفاده کرد. مثلاً برای محاسبه انحراف معیار برای محصول‌هایی که نامشان با “Wid” شروع می‌شود:

Criteria:
Product
Wid*

=DSTDEVP(A1:D5, "Amount", J1:J2)

در این حالت رکوردهایی که نام محصول‌شان مانند “WidgetX” باشد نیز در محاسبه وارد خواهند شد.

نکات مهم و خطاهای رایج

• اولین ردیف محدوده database باید شامل هدرهای ستون باشد؛ در غیر این صورت نتیجه نادرست خواهد بود.
• اگر criteria هدر نداشته باشد یا نام هدر با هدر دیتابیس مطابقت نداشته باشد، شرط اعمال نخواهد شد.
• DSTDEVP مقدار انحراف معیار جامعه را محاسبه می‌کند. اگر به انحراف معیار نمونه نیاز دارید از DSTDEV یا توابع جدیدتر مثل STDEV.S استفاده کنید.
• در نسخه‌های جدید اکسل (Excel 2010 به بالا) توابعی مانند STDEV.P و STDEV.S وجود دارند و در ترکیب با FILTER یا توابع آرایه‌ای در Excel 365 می‌توان جایگزین انعطاف‌پذیرتری ساخت.
• اگر هیچ رکوردی شرط‌ها را برآورده نکند، DSTDEVP خطا برمی‌گرداند یا ممکن است 0 نشان دهد؛ بهتر است با IFERROR پوشش داده شود.

جایگزین‌ها و کارایی در نسخه‌های جدید اکسل

در Excel 365 یا نسخه‌هایی که توابع آرایه‌ای دارند، معمولاً استفاده از FILTER همراه با STDEV.P خواناتر و سریع‌تر است:

=STDEV.P(FILTER(D2:D100, (B2:B100="North")*(C2:C100="Widget")))

توضیح: این فرمول محدوده Amount را فیلتر می‌کند بر اساس دو شرط (Region و Product) و سپس انحراف معیار جامعه را حساب می‌کند. مزیت این روش عبارت است از خوانایی، انعطاف‌پذیری و عموماً کارایی بهتر نسبت به توابع قدیمی پایگاه داده.

بهترین روش‌ها و توصیه‌های عملی

• اگر داده‌ها را جدول (Insert > Table) کنید، استفاده از نام‌های ساختاری (structured references) همراه با FILTER یا توابع جدول خواناتر خواهد بود.
• برای گزارش‌های تکرارشونده بهتر است criteria را در یک شیت جداگانه قرار داده و با نام محدوده آن را مدیریت کنید.
• همیشه نوع انحراف معیار را مشخص کنید: جامعه (population) یا نمونه (sample)، تا تحلیل‌های آماری‌تان دقیق باشد.
• برای کاهش خطا هنگام وارد کردن نام ستون در آرگومان field، بهتر است از آدرس سلول حاوی هدر یا از نام ستون استفاده کنید تا تایپ دستی کاهش یابد.

نتیجه‌گیری

تابع DSTDEVP ابزار قدیمی اما مفیدی برای محاسبات شرطی انحراف معیار جامعه در اکسل است، به‌ویژه وقتی داده‌ها در قالب جدول‌های ساده با هدر مرتب ذخیره شده‌اند. با این حال در اکسل‌های جدید، ترکیب توابع مدرن‌تر مانند FILTER و STDEV.P اغلب جایگزین قوی‌تری است که خوانایی، انعطاف و عملکرد بهتری ارائه می‌دهد. بسته به نسخه اکسل و نیاز گزارش‌دهی‌تان، انتخاب مناسب بین DSTDEVP یا روش‌های جدید می‌تواند به دقت و سادگی کار کمک کند.

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

ارسال