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

انحراف معیار و واریانس از مفاهیم مهم آماری‌ هستند که برای نشان دادن میزان فاصله داده‌ها از میانگین، کاربرد دارد. انحراف معیار بیانگر پراکندگی و تغییرپذیری داده‌ها است. هرچه انحراف معیار کمتر باشد، داده‌ها نزدیک‌تر به میانگین هستند؛ مثل (۱۲،۱۴،۱۸،۱۷) و هرچه انحراف معیار بیشتر باشد، داده‌ها پخش‌تر و نوسان‌بارتر هستند؛  مثلا اعداد (۸،۱۵،۳۰،۲۲). انحراف معیار می‌تواند بر اساس کل جمعیت یا بر اساس یک نمونه محاسبه شود. در این مقاله، ما به بررسی نحوه‌ محاسبه انحراف معیار در اکسل (standard deviation in Excel) با استفاده از توابع مختلف می‌پردازیم.

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

چندین تابع برای محاسبه‌ انحراف معیار در اکسل برای کاربران وجود دارد. این توابع بسته به نوع داده‌ها و ماهیت جمعیت یا نمونه متفاوت هستند. در ادامه، ما به معرفی و مقایسه‌ی این توابع می‌پردازیم.

دوره پیشنهادی:  آموزش مدیریت پروژه با اکسل

تابع STDEV.P

تابع STDEV.P (P مخفف Population) انحراف معیار را بر اساس کل جمعیت محاسبه می‌کند. این تابع زمانی استفاده می‌شود که ما دسترسی به تمام عناصر یک مجموعه داده داریم. به عنوان مثال، اگر ما نمرات آزمون همه‌ دانش‌آموزان یک کلاس را داشته باشیم، می‌توانیم از این تابع برای محاسبه‌ انحراف معیار نمرات استفاده کنیم. فرمول این تابع به شکل زیر است:

STDEV.P(number1, [number2], …)

که در آن:

• number1: عدد یا محدوده‌ای از اعداد که انحراف معیار آن‌ها مورد نظر است. این آرگومان اجباری است.
• ,…number2: اعداد یا محدوده‌های اضافی که انحراف معیار آن‌ها مورد نظر است. این آرگومان‌ها اختیاری هستند و حداکثر تا ۲۵۵ عدد یا محدوده می‌توانند باشند.

تابع STDEV.P از فرمول زیر برای محاسبه‌ انحراف معیار در اکسل استفاده می‌کند:

که در آن:

• σ: انحراف معیار جمعیت
• Xi: مقادیر تکی در مجموعه‌ی داده‌ها
• μ: میانگین تمام مقادیر x
• N: تعداد کل مقادیر x در مجموعه داده‌ها

به عنوان مثال، اگر ما نمرات آزمون ۵ دانش‌آموز را در سلول‌های A2 تا A6 داشته باشیم، می‌توانیم با استفاده از فرمول زیر انحراف معیار نمرات را محاسبه کنیم:

=STDEV.P(A2:A6)

نتیجه‌ی این فرمول برابر ۲.۳۶۸۵۴۳ خواهد بود. این بدان معنی است که نمرات دانش‌آموزان به طور متوسط ۲.۳۶۸۵۴۳ واحد از میانگین نمرات (۵) منحرف هستند.

دوره پیشنهادی: آموزش اکسل مقدماتی نسخه 2021

تابع STDEV.S

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

STDEV.S(number1, [number2], …)

که در آن:

• number1: عدد یا محدوده‌ای از اعداد که انحراف معیار آن‌ها مورد نظر است. این آرگومان اجباری است.
• number2, …: اعداد یا محدوده‌های اضافی که انحراف معیار آن‌ها مورد نظر است. این آرگومان‌ها اختیاری هستند.

تابع STDEV.S از فرمول زیر برای محاسبه‌ی انحراف معیار استفاده می‌کند:

که در آن:

• s: انحراف معیار نمونه
• xi: مقادیر تکی در نمونه
•  X بار: میانگین تمام مقادیر x در نمونه
• n: تعداد کل مقادیر x در نمونه

به عنوان مثال، اگر ما نمرات آزمون ۵ دانش‌آموز را در سلول‌های A2 تا A6 داشته باشیم، می‌توانیم با استفاده از فرمول زیر انحراف معیار نمونه را محاسبه کنیم:

=STDEV.S(A2:A6)

نتیجه‌ این فرمول برابر ۲.۶۵۹۲۳۵ خواهد بود. این بدان معنی است که نمرات دانش‌آموزان به طور متوسط ۲.۶۵۹۲۳۵ واحد از میانگین نمرات (۵) منحرف هستند.

توجه کنید که انحراف معیارِ نمونه، بیشتر از انحراف معیار جمعیت است. این به این دلیل است که تابع STDEV.S از n-1 به جای n در مخرج استفاده می‌کند. این کار برای اصلاح یک خطای آماری است که زمانی رخ می‌دهد که ما از یک نمونه برای تخمین یک جمعیت استفاده می‌کنیم. این روش به تصحیح بسلی معروف است و باعث می‌شود که انحراف معیار نمونه یک تخمین نااریب‌تر از انحراف معیار جمعیت باشد.

دوره پیشنهادی:  آموزش اکسل مقدماتی نسخه 2021

تابع STDEV

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

STDEV(number1, [number2], …)

که در آن:

• number1: عدد یا محدوده‌ای از اعداد که انحراف معیار آن‌ها مورد نظر است. این آرگومان اجباری است.
• number2, …: اعداد یا محدوده‌های اضافی که انحراف معیار آن‌ها مورد نظر است. این آرگومان‌ها اختیاری هستند و حداکثر تا ۲۵۵ عدد یا محدوده می‌توانند باشند.

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

که در آن:

• s: انحراف معیار نمونه
• xi: مقادیر تکی در نمونه
• X بار: میانگین تمام مقادیر x در نمونه
• n: تعداد کل مقادیر x در نمونه

به عنوان مثال، اگر ما نمرات آزمون ۵ دانش‌آموز را در سلول‌های A2 تا A6 داشته باشیم، می‌توانیم با استفاده از فرمول زیر انحراف معیار نمونه را محاسبه کنیم:

=STDEV(A2:A6)

نتیجه‌ این فرمول برابر ۲.۶۵۹۲۳۵ خواهد بود. این بدان معنی است که نمرات دانش‌آموزان به طور متوسط ۲.۶۵۹۲۳۵ واحد از میانگین نمرات (۵) منحرف هستند.

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

دوره پیشنهادی: مهارت‌های اکسل برای کسب‌وکار – مقدماتی

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

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

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

۲. جدول را انتخاب کنید و از منوی Insert گزینه Recommended Charts را انتخاب کنید. در پنجره باز شده، گزینه All Charts را انتخاب کنید و از بین نمودارهای موجود، نمودار Clustered Column را انتخاب کنید و روی دکمه OK کلیک کنید. این کار باعث می‌شود که یک نمودار میله‌ای بر اساس داده‌های جدول ساخته شود.

۳. روی نمودار کلیک راست کنید و گزینه Select Data را انتخاب کنید. در پنجره باز شده، روی دکمه Switch Row/Column کلیک کنید. این کار باعث می‌شود که محور افقی و عمودی نمودار جابجا شوند و میانگین قد به عنوان محور افقی و کلاس‌ها به عنوان محور عمودی نمایش داده شوند.

۴. در همان پنجره، روی دکمه Add کلیک کنید. در پنجره باز شده، در قسمت Series name عنوانی برای سری داده‌های انحراف معیار وارد کنید. برای مثال، می‌توانید «انحراف معیار قد» را وارد کنید. در قسمت Series values مقادیر انحراف معیار را وارد کنید. برای مثال، می‌توانید محدوده «C2:C5» را وارد کنید. سپس روی دکمه OK کلیک کنید. این کار باعث می‌شود که یک سری داده جدید به نمودار انحراف معیار در اکسل اضافه شود که نشان‌دهنده انحراف معیار قد هر کلاس است.

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

انحراف استاندارد نسبی یا همان ضریب تغییرات، نسبت انحراف معیار به میانگین است. این نسبت می‌تواند برای مقایسه پراکندگی داده‌هایی با مقیاس‌های مختلف مورد استفاده قرار گیرد. انحراف استاندارد نسبی معمولا به صورت درصد نشان داده می‌شود. برای مثال، اگر انحراف استاندارد نسبی قد دانش‌آموزان یک کلاس ۵ درصد باشد، به این معنی است که انحراف معیار قد دانش‌آموزان ۵ درصد از میانگین قد آن‌ها است.

برای محاسبه انحراف استاندارد نسبی در اکسل، می‌توانیم از توابعی که قبلا بررسی کردیم، استفاده کنیم. برای مثال، اگر بخواهیم انحراف استاندارد نسبی قد دانش‌آموزان یک کلاس را محاسبه کنیم، که داده‌های قد آن‌ها در محدوده «A2:A21» قرار دارند، می‌توانیم از فرمول زیر استفاده کنیم:

=STDEV.S(A2:A21)/AVERAGE(A2:A21)

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

=TEXT(STDEV.S(A2:A21)/AVERAGE(A2:A21),”0.00%”)

این فرمول انحراف استاندارد نسبی را با دو رقم اعشار و به همراه علامت درصد نشان می‌دهد.

مقایسه واریانس و انحراف معیار

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

دوره پیشنهادی: آموزش فرمول‌ها و توابع کاربردی اکسل 

سخن پایانی

در این مقاله به آموزش نحوه‌ محاسبه و نمایش انحراف معیار در اکسل پرداخته شد. گفتیم که انحراف معیار یک معیار آماری است که نشان می‌دهد داده‌ها چقدر از میانگین منحرف هستند. اکسل چندین تابع برای محاسبه انحراف معیار جمعیت یا نمونه ارائه می‌دهد. این توابع عبارتند از: STDEV.P، STDEV S، VAR.P و VAR.S. همچنین، می‌توان انحراف استاندارد نسبی را با تقسیم انحراف معیار به میانگین محاسبه کرد. در این مقاله، فقط یک نمودار (میله‌ای) برای نشان دادن انحراف معیار معرفی شد که البته نمودارهای دیگه‌ای هم می‌توانید متناسب با پروژه‌تان به کار بگیرید. مراحل رسم این نمودار با استفاده از توابع و ابزارهای اکسل آموزش داده شد. این مقاله می‌تواند برای کسانی که می‌خواهند از انحراف معیار در اکسل بهره ببرند، مفید باشد.

آموزش اکسل با مکتب خونه

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