تابع انحراف استاندارد در اکسل. انحراف معیار چیست - با استفاده از تابع انحراف استاندارد برای محاسبه انحراف استاندارد در اکسل

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

بیایید بلافاصله تعیین کنیم که انحراف معیار چیست و فرمول آن چگونه است. این کمیت جذر میانگین حسابی مجذورات اختلاف بین تمام کمیت های سری و میانگین حسابی آنها است. یک نام یکسان برای این شاخص وجود دارد - انحراف استاندارد. هر دو نام کاملاً معادل هستند.

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

محاسبه در اکسل

شما می توانید مقدار مشخص شده در اکسل را با استفاده از دو تابع خاص محاسبه کنید STDEV.V(بر اساس جامعه نمونه) و STDEV.G(بر اساس جمعیت عمومی). اصل عملکرد آنها کاملاً یکسان است، اما می توان آنها را به سه روش فراخوانی کرد که در ادامه به آنها خواهیم پرداخت.

روش 1: Function Wizard

روش 2: برگه فرمول ها

روش 3: وارد کردن فرمول به صورت دستی

همچنین راهی وجود دارد که به هیچ وجه نیازی به فراخوانی پنجره آرگومان ها نخواهید داشت. برای این کار باید فرمول را به صورت دستی وارد کنید.

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

واریانس معیاری از پراکندگی است که انحراف مقایسه ای بین مقادیر داده و میانگین را توصیف می کند. این معیار پر استفاده ترین معیار پراکندگی در آمار است که با جمع و مجذور کردن انحراف هر مقدار داده از میانگین محاسبه می شود. فرمول محاسبه واریانس در زیر آمده است:

s 2 - واریانس نمونه;

x av—میانگین نمونه;

n — اندازه نمونه (تعداد مقادیر داده)،

(x i – x avg) انحراف از مقدار متوسط ​​برای هر مقدار از مجموعه داده است.

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

اولین مرحله در محاسبه واریانس تعیین میانگین نمونه است که در مثال ما 7.8 بار در ماه است. بقیه محاسبات را می توان با استفاده از جدول زیر ساده تر کرد.

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

برای کسانی که دوست دارند تمام محاسبات را یکجا انجام دهند، معادله به این صورت خواهد بود:

استفاده از روش شمارش خام (مثال آشپزی)

روش کارآمدتری برای محاسبه واریانس وجود دارد که به عنوان روش شمارش خام شناخته می شود. اگرچه ممکن است این معادله در نگاه اول کاملاً دست و پا گیر به نظر برسد، اما در واقع آنقدرها هم ترسناک نیست. می توانید از این موضوع مطمئن شوید و سپس تصمیم بگیرید که کدام روش را بیشتر دوست دارید.

مجموع هر مقدار داده پس از مربع کردن است،

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

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

همانطور که می بینید، نتیجه همان روش استفاده از روش قبلی بود. مزایای این روش با افزایش حجم نمونه (n) آشکار می شود.

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

همانطور که احتمالا قبلاً حدس زده اید، اکسل فرمولی دارد که به شما امکان می دهد واریانس را محاسبه کنید. علاوه بر این، با شروع اکسل 2010، می توانید 4 نوع فرمول واریانس را پیدا کنید:

1) VARIANCE.V - واریانس نمونه را برمی‌گرداند. مقادیر بولی و متن نادیده گرفته می شوند.

2) DISP.G - واریانس جمعیت را برمی‌گرداند. مقادیر بولی و متن نادیده گرفته می شوند.

3) VARIANCE - واریانس نمونه را با در نظر گرفتن مقادیر بولی و متنی برمی گرداند.

4) VARIANCE - واریانس جمعیت را با در نظر گرفتن مقادیر منطقی و متنی برمی‌گرداند.

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

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

تفاوت در فرمول محاسبه واریانس بین نمونه و جامعه، مخرج است. جایی که برای نمونه برابر با (n-1) و برای جمعیت عمومی فقط n خواهد بود.

حالا بیایید به توابع محاسبه واریانس با پایان نگاه کنیم آ،که در توضیح آن آمده است که متن و مقادیر منطقی در محاسبه در نظر گرفته شده است. در این حالت، هنگام محاسبه واریانس یک مجموعه داده خاص که مقادیر غیر عددی رخ می دهد، اکسل متن و مقادیر بولی نادرست را برابر با 0 و مقادیر بولی واقعی را برابر با 1 تفسیر می کند.

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

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

پراکندگی نشانگر تغییرات است که مجذور میانگین انحرافات از انتظارات ریاضی است. بنابراین، گسترش اعداد در اطراف مقدار متوسط ​​را بیان می کند. محاسبه واریانس را می توان هم برای جامعه عمومی و هم برای نمونه انجام داد.

روش 1: محاسبه بر اساس جمعیت

برای محاسبه این اندیکاتور در اکسل برای عموم مردم، از تابع استفاده کنید DISP.G. نحو این عبارت به صورت زیر است:

DISP.G(Number1; Number2;…)

در مجموع از 1 تا 255 آرگومان می توان استفاده کرد. آرگومان ها می توانند مقادیر عددی یا ارجاع به سلول هایی باشند که در آنها قرار دارند.

بیایید ببینیم چگونه این مقدار را برای یک محدوده با داده های عددی محاسبه کنیم.

روش 2: محاسبه با نمونه

بر خلاف محاسبه یک مقدار بر اساس جمعیت، در محاسبه نمونه، مخرج تعداد کل اعداد را نشان نمی دهد، بلکه یک عدد کمتر را نشان می دهد. این کار به منظور تصحیح خطا انجام می شود. Excel این تفاوت های ظریف را در یک تابع ویژه که برای این نوع محاسبه طراحی شده است - DISP.V در نظر می گیرد. نحو آن با فرمول زیر نمایش داده می شود:

DISP.B (شماره 1؛ شماره 2؛…)

تعداد آرگومان ها، مانند تابع قبلی، می تواند از 1 تا 255 متغیر باشد.

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

مفهوم درصد انحراف به تفاوت بین دو مقدار عددی به عنوان درصد اشاره دارد. بیایید یک مثال خاص بیاوریم: فرض کنید یک روز 120 تبلت از یک انبار عمده فروشی فروخته شد و روز بعد - 150 قطعه. تفاوت در حجم فروش مشخص است؛ روز بعد 30 تبلت دیگر فروخته شد. وقتی عدد 120 را از 150 کم می کنیم، انحرافی به دست می آید که برابر با عدد 30+ است. این سوال مطرح می شود: درصد انحراف چیست؟

نحوه محاسبه درصد انحراف در اکسل

درصد انحراف با کم کردن مقدار قدیمی از مقدار جدید و سپس تقسیم نتیجه بر مقدار قدیمی محاسبه می شود. نتیجه این محاسبه فرمول در اکسل باید در قالب درصد سلول نمایش داده شود. در این مثال، فرمول محاسبه به صورت زیر است (150-120)/120=25%. بررسی فرمول آسان است: 120+25%=150.

توجه داشته باشید!اگر اعداد قدیمی و جدید را با هم عوض کنیم، فرمولی برای محاسبه نشانه گذاری خواهیم داشت.

شکل زیر نمونه ای از نحوه ارائه محاسبات فوق به صورت فرمول اکسل را نشان می دهد. فرمول موجود در سلول D2 درصد انحراف بین مقادیر فروش را برای سال جاری و سال گذشته محاسبه می کند: =(C2-B2)/B2

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

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



فرمول جایگزین برای محاسبه درصد انحراف در اکسل

در یک فرمول جایگزین که انحراف نسبی ارزش فروش را از سال جاری محاسبه می کند، بلافاصله بر ارزش های فروش سال قبل تقسیم می شود و تنها پس از آن یکی از نتیجه کم می شود: =C2/B2-1.

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

تنها اشکال این فرمول جایگزین عدم امکان محاسبه درصد انحراف برای اعداد منفی در شمارنده یا جایگزین است. حتی اگر از تابع ABS در فرمول استفاده کنیم، اگر عدد موجود در جایگزین منفی باشد، فرمول نتیجه اشتباهی را برمی‌گرداند.

از آنجایی که در اکسل به صورت پیش فرض اولویت عملیات تقسیم از عمل تفریق بیشتر است، نیازی به استفاده از پرانتز در این فرمول نیست.

برنامه اکسل هم توسط حرفه ای ها و هم برای آماتورها بسیار ارزشمند است، زیرا کاربران با هر سطح مهارتی می توانند با آن کار کنند. به عنوان مثال، هر کسی با حداقل مهارت های "ارتباطاتی" در اکسل می تواند یک نمودار ساده ترسیم کند، یک صفحه مناسب و غیره بسازد.

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

آنچه هست

بیایید با تئوری شروع کنیم. انحراف معیار را معمولاً جذر می نامند که از میانگین حسابی همه مجذور اختلاف بین مقادیر موجود و همچنین میانگین حسابی آنها به دست می آید. به هر حال، این مقدار معمولاً حرف یونانی "سیگما" نامیده می شود. انحراف استاندارد با استفاده از فرمول STANDARDEVAL محاسبه می شود؛ بر این اساس، برنامه این کار را برای خود کاربر انجام می دهد.

ماهیت این مفهوم شناسایی درجه تغییرپذیری یک ابزار است، یعنی به روش خود شاخصی است که از آمار توصیفی به دست آمده است. این تغییرات در نوسانات یک ابزار را در یک دوره زمانی مشخص مشخص می کند. فرمول های STDEV را می توان برای تخمین انحراف استاندارد یک نمونه، بدون توجه به مقادیر بولی و متن استفاده کرد.

فرمول

فرمولی که به طور خودکار در اکسل ارائه می شود به محاسبه انحراف معیار در اکسل کمک می کند. برای پیدا کردن آن، باید بخش فرمول را در اکسل پیدا کنید، و سپس یکی به نام STANDARDEVAL را انتخاب کنید، بنابراین بسیار ساده است.

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

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

ویدیو برای کمک