انحراف معیار در اکسل

در این مطلب اساس انحراف معیار و این‌که چه فرمولی برای محاسبه انحراف معیار در اکسل بهتر است، توضیح داده شده‌است.

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

• انحراف معیار چیست؟
• نحوه‌ی محاسبه‌ی انحراف معیار در اکسل
  • توابع محاسبه‌ی انحراف معیار نمونه در اکسل
  • توابع محاسبه‌ی انحراف معیار جمعیت در اکسل
• مثال‌های فرمول انحراف معیار اکسل
• نحوه‌ی اضافه کردن میله‌های انحراف معیار در اکسل

انحراف معیار چیست؟

انحراف معیار (Standard Deviation) سنجه‌ای است که نشان می‌دهد چه مقدار مقادیر مجموعه‌ی داده‌ها از میانگین منحرف شده‌اند. به عبارتی دیگر، انحراف معیار نشان می‌دهد که داده‌های شما به میانگین نزدیک هستند یا نوسان زیادی دارند.

هدف از انحراف معیار این است که به شما در درک این‌که آیا میانگین داده “معمولی” را برمی‌گرداند یا خیر، کمک کند. هرچه انحراف معیار به صفر نزدیک‌تر باشد، تغییرپذیری داده‌ها پایین‌تر و قابل اتکاتر است. انحراف معیار برابر 0 نشان می‌دهد که هر مقداری در مجموعه داده دقیقاً برابر با میانگین است. هرچه انحراف معیار بالاتر باشد، تنوع بیشتری در داده‌ها مشاهده می‌شود و اتکاپذیری آن نیز کمتر است.

انحراف معیار نمونه (Sample SD) در مقابل انحراف معیار جمعیت (Population SD)

در رابطه با انحراف معیار، اغلب می‌توانید اصطلاحات “نمونه” و “جمعیت” را بشنوید، که به تمامیت داده‌هایی که با آنها کار می‌کنید اشاره دارد. تفاوت اصلی به شرح زیر است:

• جمعیت (Population) شامل همه‌ی عناصر یک مجموعه داده‌است. به عنوان مثال، شما در حال تدریس برای یک گروه ۵ نفره از دانش‌آموزان هستید. شما نمرات آزمون همه‌ی دانش‌آموزان را دارید. در این‌جا کل جمعیت از ۵ نقطه داده تشکیل شده‌است.
• نمونه (Sample) زیرمجموعه ای از داده‌ها است که شامل یک یا چند عنصر از جمعیت است. به عنوان مثال، شما در حال آموزش گروه بزرگی از دانش آموزان هستید. اما شما فقط نمرات آزمون ۵ دانشجو را دارید. در این‌جا اندازه‌ی نمونه برابر ۵ است.

دلیل این‌که ماهیت داده‌ها اهمیت دارد این است که انحراف معیار جمعیت و انحراف معیار نمونه با فرمول‌های کمی متفاوت محاسبه می‌شوند:

انحراف معیار نمونه	انحراف معیار جمعیت

که در آن:

• x_i مقادیر تکی در مجموعه‌ی داده‌ها است
• x̅ میانگین تمام مقادیر x است
• n تعداد کل مقادیر x در مجموعه داده‌ها است

نحوه‌ی محاسبه‌ی انحراف معیار در اکسل

در کل، شش تابع مختلف برای یافتن انحراف معیار در اکسل وجود دارد. این‌که کدام‌یک استفاده شود، در درجه‌ی اول به ماهیت داده‌هایی که با آن‌ها کار می‌کنید بستگی دارد – این‌که کل جمعیت باشد یا یک نمونه.

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

برای محاسبه‌ی انحراف معیار بر اساس یک نمونه، از یکی از فرمول‌های زیر استفاده کنید (همه‌ی آن‌ها براساس روش “n-۱” که در بالا توضیح داده شده‌است هستند).

تابع STDEV اکسل

STDEV(number1,[number2],…) قدیمی‌ترین تابع اکسل برای تخمین انحراف معیار بر اساس نمونه است و در کلیه‌ی نسخه‌های اکسل ۲۰۰۳ تا ۲۰۱۹ در دسترس است.

در اکسل ۲۰۰۷ به بعد، STDEV می‌تواند ۲۵۵ آرگومان را بپذیرد که می‌توانند با عدد، آرایه‌ها، بازه‌های نامگذاری‌شده یا ارجاع به سلول‌های حاوی عدد ارائه شوند. در اکسل ۲۰۰۳، این تابع فقط می‌تواند ۳۰ آرگومان را بپذیرد.

مقادیر منطقی و متن‌هایی که عددی هستند و مستقیماً در لیست آرگومان‌ها نوشته می‌شوند، شمرده می‌شوند. در آرایه‌ها و ارجاع‌ها، فقط عددها شمرده می‌شوند. سلولهای خالی، مقادیر منطقی TRUE و FALSE، مقادیر متنی و خطا نادیده گرفته می‌شوند.

توجه: تابع STDEV یک تابع منسوخ است، که در نسخه‌های جدید اکسل فقط برای سازگاری با عقب نگه داشته می‌شود. با این حال، مایکروسافت هیچ قولی در مورد نسخه‌های آینده نمی‌دهد؛ بنابراین، در اکسل 2010 و بعد از آن، توصیه می‌شود به جای STDEV از STDEV.S استفاده کنید.

تابع STDEV.S اکسل

STDEV.S(number1,[number2],…) نسخه‌ی بهبودیافته‌ی STDEV است که در اکسل 2010 معرفی شده‌است.

مانند STDEV، تابع STDEV.S انحراف معیار نمونه‌ای از مجموعه‌ای از مقادیر را بر اساس فرمول انحراف معیار نمونه مورد بحث در بخش قبلی محاسبه می‌کند.

تابع STDEVA اکسل

STDEVA(value1, [value2], …) تابع دیگری برای محاسبه‌ی انحراف میعار یک نمونه در اکسل است. عملکرد آن تنها با نحوه‌ی برخورد با مقادیر منطقی و متنی با دو مورد بالا تفاوت دارد:

• تمام مقادیر منطقی شمرده می‌شوند، خواه در آرایه‌ها یا منابع موجود باشد، یا مستقیماً در لیست آرگومان‌ها تایپ می‌شوند (TRUE را به عنوان 1، FALSE به عنوان 0 ارزیابی می‌شود).
• مقادیر متنی در آرایه‌ها یا آرگومان‌های ارجاعی 0 محاسبه می‌شوند، از جمله رشته‌های خالی (“”)، متن‌هایی که عددی هستند و هر متن دیگر. متن‌هایی که عددی هستند اگر مستقیماً در لیست آرگومان‌ها نوشته شوند، به صورت عددی که آن‌ها نشان می‌دهند شمرده می‌شوند (مثال آن در پایین آمده است).
• سلول‌های خالی نادیده گرفته می‌شوند.

توجه: برای اینکه فرمول انحراف معیار به درستی کار کند، آرگومان‌های ارائه شده باید حداقل دارای دو مقدار عددی باشند، در غیر این صورت خطای #DIV/0! برگردانده می‌شود.

توابع محاسبه‌ی انحراف معیار جمعیت در اکسل

اگر با کل جمعیت سر و کار دارید، برای انجام انحراف معیار در اکسل از یکی از توابع زیر استفاده کنید. این توابع براساس روش “n” ساخته شده‌اند.

تابع STDEVP اکسل

STDEVP(number1,[number2],…) تابع قدیمی اکسل برای یافتن انحراف معیار از جمعیت است.

در نسخه‌های جدید اکسل ۲۰۱۰، ۲۰۱۳، ۲۰۱۶ و ۲۰۱۹ با تابع بهبودیافته‌ی STDEV.P جایگزین شده‌است، اما هنوز هم برای سازگاری به عقب نگه داشته می‌شود.

تابع STDEV.P اکسل

STDEV.P(number1,[number2],…) نسخه‌ی مدرن تابع STDEVP است که دقت بهبود یافته‌ای را ارائه می‌دهد. در نسخه‌های Excel ۲۰۱۰ و نسخه‌های بعدی موجود است.

مانند همتای انحراف معیار نمونه، در آرایه‌ها یا آرگومان‌های ارجاعی، توابع STDEVP و STDEV.P فقط عدد را محاسبه می‌کنند. در لیست آرگومان‌ها، آن‌ها هم‌چنین مقادیر منطقی و متن‌هایی که عددی هستند را شمارش می‌کنند.

تابع STDEVPA اکسل

STDEVPA(value1, [value2], …) انحراف معیار جمعیت، شامل متن و مقادیر منطقی را محاسبه می‌کند. عملکرد آن دقیقاً مانند تابع STDEVA است اما برای جمعیت.

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

کدام یک از توابع انحراف معیار اکسل را باید استفاده کرد؟

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

• آیا انحراف معیار یک نمونه یا جمعیت را محاسبه می‌کنید؟
• از چه نسخه‌ای از اکسل استفاده می‌کنید؟
• آیا مجموعه داده‌های شما فقط شامل اعداد است یا هم‌چنین مقادیر منطقی و متن؟

در جدول زیر یک نمای کلی از توابع انحراف معیار موجود در اکسل ارائه شده‌است تا در انتخاب فرمول مناسب برای نیازهای شما کمک کند.

نام	ورژن اکسل	نوع داده	مقادیر متنی و منطقی
STDEV	2003 – 2019	نمونه	نادیده می‌گیرد
STDEV.S	2010 – 2019	نمونه	نادیده می‌گیرد
STDEVA	2003 – 2019	نمونه	ارزیابی می‌کند
STDEVP	2003 – 2019	جمعیت	نادیده می‌گیرد
STDEV.P	2010 – 2019	جمعیت	نادیده می‌گیرد
STDEVPA	2003 – 2019	جمعیت	ارزیابی می‌کند

STDEV.S در برابر STDEVPA و STDEV.P در برابر STDEVPA

STDEVA و STDEVPA فقط با رفتاری که با مقادیر منطقی و متنی را در ارجاع‌ها دارند، با دیگر توابع انحراف معیار تفاوت دارند. در جدول زیر خلاصه‌ای از نحوه‌ی ارزیابی اعداد به صورت متنی و مقادیر منطقی ارائه شده‌است.

نوع آرگومان	STDEV, STDEV.S, STDEVP, STDEV.P	STDEVPA و STDEVA
مقادیر منطقی در آرایه‌ها و ارجاع‌ها	نادیده می‌گیرد	ارزیابی می‌کند (TRUE=1, FALSE=0)
اعداد به صورت متنی در آرایه‌ها و ارجاع‌ها	نادیده می‌گیرد	به صورت صفر ارزیابی می‌کند
مقادیر منطقی و اعداد به صورت متنی که مستقیماً در آرگومان‌ها تایپ می‌شوند	ارزیابی می‌کند (TRUE=1, FALSE=0)
سلول‌های خالی	نادیده می‌گیرد

مثال‌های فرمول انحراف معیار اکسل

پس از انتخاب تابعی که با نوع داده شما مطابقت داشته باشد ، نباید در نوشتن فرمول مشکلی وجود داشته باشد – سینتکس آنقدر ساده و شفاف است که جایی برای خطا باقی نمی‌گذارد! نمونه‌های زیر چند فرمول انحراف معیار اکسل را به صورت عملی نشان می‌دهد.

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

بسته به ماهیت داده‌های خود ، یکی از فرمول‌های زیر را استفاده کنید:

• برای محاسبه‌ی انحراف معیار بر اساس کل جمعیت، یعنی لیست کامل مقادیر (B2:B50 در این مثال)، از تابع STDEV.P استفاده کنید:=STDEV.P(B2:B50)
• برای پیدا کردن انحراف معیار بر اساس یک نمونه که بخش یا زیرمجموعه‌ای از جمعیت را تشکیل می‌دهد (B2:B10 در این مثال)، از عملکرد STDEV.S استفاده کنید:=STDEV.S(B2:B10)

همان‌طور که در تصویر زیر مشاهده می‌کنید فرمول‌ها اعدادی متفاوت را نشان می‌دهند (هرچه نمونه کوچک‌تر باشد، تفاوت آن بزرگ‌تر است):

در اکسل ۲۰۰۷ و پایین‌تر، به جای آن از توابع STDEVP و STDEV استفاده می‌کنید:

• برای یافتن انحراف معیار جمعیت:=STDEVP(B2:B50)
• برای محاسبه‌ی انحراف معیار نمونه:=STDEV(B2:B10)

نحوه‌ی اضافه کردن میله‌های انحراف معیار در اکسل

برای نمایش بصری اختلاف انحراف معیار، می‌توانید میله‌های انحراف معیار را به نمودار اکسل خود اضافه کنید. روش کار در این‌جا آمده‌است:

• یک نمودار به روش معمول ایجاد کنید (تب Insert > گروه Charts).
• برای انتخاب نمودار، در هر نقطه از نمودار کلیک کنید، سپس بر روی دکمه‌ی Chart Elements کلیک کنید.
• روی پیکان کنار Error Bars کلیک کرده و Standard Deviation را انتخاب کنید.

با این کار میله‌های انحراف معیار برای همه‌ی نقاط داده وارد می‌شوند.

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