تابع AVEDEV در اکسل — میانگین قدر مطلق انحراف‌ها

تابع AVEDEV در اکسل میانگین قدر مطلق اختلاف هر مقدار نسبت به میانگین کل مجموعه را محاسبه می‌کند. به عبارت ساده‌تر، AVEDEV نشان می‌دهد داده‌ها به طور میانگین چقدر از مقدار میانگین فاصله دارند. این معیار پراکندگی (dispersion) جایگزینی ساده و قابل‌فهم برای انحراف معیار است و در بسیاری از تحلیل‌های توصیفی و کنترل کیفیت کاربرد دارد.

نحوۀ استفاده (Syntax)

نحوۀ تابع به شکل زیر است:

=AVEDEV(number1, [number2], ...)

هر آرگومان می‌تواند یک عدد، یک بازه (مثل A1:A10) یا آرایه باشد. مقادیر غیرعددی داخل ارجاع‌ها نادیده گرفته می‌شوند.

مثال ساده

فرض کنید مقادیر 10، 12، 14 و 16 داریم. میانگین این اعداد 13 است و قدر مطلق اختلاف‌ها به ترتیب 3، 1، 1 و 3 است. میانگین این قدر مطلق‌ها برابر 2 است؛ بنابراین:

=AVEDEV(10,12,14,16)

نتیجه 2 خواهد بود.

توضیح: تابع فوق میانگین قدر مطلق اختلاف‌ها نسبت به میانگین را مستقیماً محاسبه می‌کند، بدون نیاز به ستون کمکی.

مثال کاربردی با بازه

در عمل معمولاً داده‌ها در یک ستون هستند. مثلاً اگر داده‌ها از A2 تا A11 باشند:

=AVEDEV(A2:A11)

این فرمول میانگین قدر مطلق انحراف هر سلول از میانگین محدوده A2:A11 را برمی‌گرداند. مقادیر متنی یا خالی در محدوده نادیده گرفته می‌شوند.

نحوۀ محاسبه مرحله‌ای (با ستون کمکی)

در برخی موارد می‌خواهیم مراحل محاسبه را ببینیم. مثال جدول زیر را در نظر بگیرید:

سپس میانگین قدر مطلق‌ها (ستون آخر) 2 خواهد بود. در اکسل می‌توانید میانگین را با =AVERAGE(A2:A5) و قدر مطلق‌ها را با =ABS(A2-$B$1) محاسبه کنید و در نهایت میانگین آن‌ها را بگیرید یا ساده‌تر از =AVEDEV(A2:A5) استفاده کنید.

تفاوت AVEDEV با انحراف معیار (STDEV)

• AVEDEV میانگین قدر مطلق فاصله‌ها از میانگین را محاسبه می‌کند؛ STDEV انحراف معیار را بر اساس مربع اختلاف‌ها (و سپس جذر) می‌سنجد.
• AVEDEV تفسیر ساده‌تری دارد (فاصلهٔ متوسط از میانگین به واحد داده‌ها) و نسبت به تجمع خطاها کمتر حساس است؛ STDEV برای تحلیل‌های آماری و فرضیه‌سازی که مبتنی بر واریانس هستند مناسب‌تر است.
• برای داده‌هایی که شامل مقادیر پرت (outliers) هستند، AVEDEV معمولاً تأثیر کمتری نسبت به STDEV نشان می‌دهد، اما اگر به معیار مقاوم‌تر نیاز دارید، ممکن است از مقیاس‌های مقاوم‌تر مانند MAD (میانگین قدر مطلق نسبت به میانه) استفاده شود.

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

• کنترل کیفیت: بررسی ثبات تولید یا پراکندگی اندازه‌ها نسبت به هدف.
• تحلیل توصیفی: نمایش ساده میزان پراکندگی در گزارش‌های مدیریتی.
• مقایسه دو مجموعه: اگر دو مجموعه میانگین یکسانی داشته باشند، AVEDEV نشان می‌دهد کدام مجموعه همگن‌تر است.
• ترکیب با فیلترها: برای محاسبه AVEDEV مشروط از فرمول‌های آرایه‌ای یا توابع LET و IF استفاده کنید.

فرمول مشروط (مثال پیشرفته)

اگر بخواهیم AVEDEV تنها برای ردیف‌هایی محاسبه شود که شرطی را ارضا می‌کنند (مثلاً ستون B برابر “Yes” باشد)، می‌توان از فرمول آرایه‌ای استفاده کرد. در اکسل‌های جدید (با Dynamic Array) نیازی به Ctrl+Shift+Enter نیست، ولی در نسخه‌های قدیمی باید با CSE وارد شود.

=AVERAGE(ABS(IF(B2:B100="Yes", A2:A100 - AVERAGE(IF(B2:B100="Yes",A2:A100)) )))

توضیح: این فرمول ابتدا فقط مقادیر A2:A100 را که شرط B=”Yes” دارند انتخاب می‌کند، میانگین آن‌ها را می‌یابد، سپس قدر مطلق اختلاف‌ها را محاسبه و در نهایت میانگین این اختلاف‌ها را برمی‌گرداند. در اکسل قدیمی لازم است این فرمول به صورت آرایه‌ای تأیید شود (Ctrl+Shift+Enter).

بهینه‌سازی با LET (در Excel 365/2021)

=LET(m, AVERAGE(A2:A100),
     AVERAGE(ABS(A2:A100 - m)))

توضیح: تابع LET میانگین را یک‌بار محاسبه و در متغیر m ذخیره می‌کند؛ سپس از آن برای محاسبه قدر مطلق اختلاف‌ها استفاده می‌شود. این روش کارایی را بالا می‌برد و فرمول را خواناتر می‌کند.

نکات عملی و مواردی که باید مراقب باشید

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

خلاصه

تابع AVEDEV یک ابزار ساده و کاربردی برای اندازه‌گیری میانگین فاصله اعضای یک مجموعه از میانگین آن مجموعه است. نسبت به انحراف معیار تفسیر مستقیم‌تری دارد و برای گزارش‌های مدیریتی و کنترل کیفیت بسیار مفید است. با ترکیب با توابعی مانند IF، LET و AVERAGE می‌توان محاسبات پیچیده‌تر و کارآمدتری انجام داد.

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

ارسال