میانگین و انحراف معیار در اکسل. محاسبه انحراف معیار در مایکروسافت اکسل
آندری لیپوف
به عبارت ساده، انحراف معیار نشان می دهد که قیمت یک ابزار در طول زمان چقدر در نوسان است. یعنی هر چه این شاخص بالاتر باشد، نوسان یا تغییرپذیری تعدادی از مقادیر بیشتر است.
انحراف معیار می تواند و باید برای تجزیه و تحلیل مجموعه ای از مقادیر استفاده شود، زیرا دو مجموعه با میانگین ظاهراً یکسان ممکن است در توزیع مقادیر کاملاً متفاوت باشند.
مثال
بیایید دو ردیف اعداد را در نظر بگیریم.
الف) 1،2،3،4،5،6،7،8،9. میانگین - 5. St. انحراف = 2.7386
ب) 20،1،7،1،15،-1،-20،4،18،5. میانگین - 5. St. انحراف = 12.2066
اگر کل سری اعداد را جلوی چشم خود نگه ندارید، نشانگر انحراف استاندارد نشان می دهد که در حالت "b" مقادیر بسیار بیشتر در اطراف مقدار متوسط خود پراکنده می شوند.
به طور کلی، در سری "b" مقدار 5 به علاوه یا منهای 12 (به طور متوسط) است - دقیق نیست، اما معنی را نشان می دهد.
نحوه محاسبه انحراف معیار
برای محاسبه انحراف استاندارد، می‌توانید از فرمولی که از محاسبه انحراف استاندارد بازده صندوق‌های سرمایه‌گذاری وام گرفته شده است استفاده کنید:
در اینجا N تعداد کمیت ها است،
DOHaverage - میانگین همه مقادیر،
دوره DOH - مقدار N.
در اکسل، تابع مربوطه STANDARDEVAL (یا STDEV در نسخه انگلیسی برنامه) نامیده می شود.
دستورالعمل های گام به گام به شرح زیر است:
- میانگین یک سری اعداد را محاسبه کنید.
- برای هر مقدار، تفاوت بین میانگین و آن مقدار را تعیین کنید.
- مجموع مجذورات این تفاوت ها را محاسبه کنید.
- مجموع حاصل را بر تعداد اعداد سری تقسیم کنید.
- جذر عددی را که در مرحله آخر به دست آوردید، بگیرید.
دوستان شما این اطلاعات را مفید خواهند یافت. با آنها به اشتراک بگذارید!
ضریب تغییرات مقایسه پراکندگی دو مقدار تصادفی است. کمیت ها دارای واحدهای اندازه گیری هستند که منجر به نتیجه قابل مقایسه می شود. این ضریب برای تهیه تحلیل آماری مورد نیاز است.
با آن، سرمایه گذاران می توانند محاسبه شاخص های ریسکقبل از سرمایه گذاری در دارایی های منتخب زمانی مفید است که دارایی های انتخاب شده بازده و درجه ریسک متفاوتی داشته باشند. به عنوان مثال، یک دارایی ممکن است درآمد بالا و درجه ریسک بالایی داشته باشد، در حالی که دیگری، برعکس، ممکن است درآمد پایین و به نسبت ریسک کمتری داشته باشد.
محاسبه انحراف استاندارد
انحراف معیار یک مقدار آماری است. با محاسبه این مقدار، کاربر اطلاعاتی در مورد میزان انحراف داده ها در یک جهت نسبت به مقدار متوسط دریافت می کند. انحراف استاندارد در اکسل در چند مرحله محاسبه می شود.
داده ها را آماده کنید: صفحه ای را که در آن محاسبات انجام می شود باز کنید. در مورد ما، این یک تصویر است، اما می تواند هر فایل دیگری باشد. نکته اصلی جمع آوری اطلاعاتی است که در جدول برای محاسبه استفاده خواهید کرد.
داده ها را در هر ویرایشگر صفحه گسترده (در مورد ما اکسل) وارد کنید و سلول ها را از چپ به راست پر کنید. باید شروع شوداز ستون "الف". عناوین را در خط بالا وارد کنید و نام‌هایی را در همان ستون‌هایی که به عنوان‌ها مربوط می‌شوند، فقط در زیر وارد کنید. سپس تاریخ و داده ها در سمت راست تاریخ محاسبه می شود. 
این سند را ذخیره کن. 
حالا بیایید به خود محاسبات برویم. یک سلول را با مکان نما انتخاب کنیدبعد از آخرین مقدار وارد شده در زیر. 
علامت "=" را وارد کرده و فرمول زیر را وارد کنید. علامت مساوی مورد نیاز است. در غیر این صورت، برنامه داده های پیشنهادی را محاسبه نخواهد کرد. فرمول بدون فاصله وارد می شود. 
ابزار نام چندین فرمول را نمایش می دهد. انتخاب کنید " انحراف معیار" این فرمول برای محاسبه انحراف معیار است. دو نوع محاسبه وجود دارد:
- با محاسبه نمونه;
- با محاسبه بر اساس جمعیت عمومی.
با انتخاب یکی از آنها محدوده داده را مشخص کنید. کل فرمول وارد شده به این صورت خواهد بود: "=STDEV (B2: B5)." 
سپس بر روی دکمه ” کلیک کنید وارد" داده های دریافتی در مورد علامت گذاری شده ظاهر می شوند. 
محاسبه میانگین حسابی
زمانی محاسبه می شود که کاربر نیاز به ایجاد گزارشی داشته باشد، به عنوان مثال، در مورد دستمزد در شرکت خود. این کار به صورت زیر انجام می شود:
- فقط وجود خواهد داشت محدوده را انتخاب کنیدو بر روی دکمه "Enter" کلیک کنید. و سلول اکنون نتیجه را از داده های گرفته شده در بالا نمایش می دهد.
محاسبه ضریب تغییرات
فرمول محاسبه ضریب تغییرات:
V= S/X که S انحراف معیار و X میانگین است.
برای محاسبه ضریب تغییرات در اکسل، باید انحراف معیار و میانگین حسابی را پیدا کنید. یعنی با تکمیل دو محاسبه اول که در بالا نشان داده شد، می توانید به کار بر روی ضریب تغییرات بروید.
برای انجام این کار، اکسل را باز کنید، دو فیلد را پر کنید که در آن باید اعداد حاصل از انحراف استاندارد و مقدار میانگین را وارد کنید. 
حالا سلولی را انتخاب کنید که به عدد اختصاص داده شده است تا تغییرات محاسبه شود. باز کردن برگه " خانه"اگر باز نباشد. روی ابزار کلیک کنید " عدد" فرمت درصد را انتخاب کنید. 
به سلول مشخص شده بروید و روی آن دوبار کلیک کنید. سپس علامت مساوی را وارد کرده و موردی را که انحراف استاندارد کل وارد شده است برجسته کنید. سپس بر روی دکمه "slash" یا "split" روی صفحه کلید خود کلیک کنید (به نظر می رسد: "/"). مورد را انتخاب کنید، جایی که میانگین حسابی وارد شده است و روی دکمه "Enter" کلیک کنید. می بایست شبیه به این باشه: 
و در اینجا نتیجه پس از فشار دادن "Enter" است: 
همچنین می توانید از ماشین حساب های آنلاین برای محاسبه ضریب تغییرات استفاده کنید، برای مثال planetcalc.ru و allcalc.ru. کافی است اعداد لازم را وارد کرده و محاسبه را شروع کنید و پس از آن اطلاعات لازم را دریافت خواهید کرد.
انحراف معیار
انحراف استاندارد در اکسل با استفاده از دو فرمول حل می شود: 
به عبارت ساده، ریشه واریانس استخراج می شود. نحوه محاسبه واریانس در زیر مورد بحث قرار گرفته است. 
انحراف معیار مترادف با انحراف معیار است و دقیقاً نیز محاسبه می شود. سلول برای نتیجه زیر اعدادی که باید محاسبه شوند برجسته شده است. یکی از توابع نشان داده شده در شکل بالا درج شده است. دکمه “کلیک می شود وارد" نتیجه دریافت شده است.
ضریب نوسان
نسبت دامنه تغییرات به میانگین را ضریب نوسان می گویند. هیچ فرمول آماده ای در اکسل وجود ندارد، بنابراین باید مونتاژ شوندچندین عملکرد در یک 
توابعی که باید در کنار هم قرار گیرند، فرمول های میانگین، حداکثر و حداقل هستند. این ضریب برای مقایسه مجموعه ای از داده ها استفاده می شود.
پراکندگی
واریانس تابعی است که توسط آن مشخص کردن گسترش داده هاحول انتظارات ریاضی با استفاده از معادله زیر محاسبه می شود: 
متغیرها مقادیر زیر را می گیرند: 
اکسل دو تابع دارد که واریانس را تعیین می کند:
برای انجام یک محاسبه، یک سلول زیر اعدادی که باید محاسبه شوند برجسته می شود. به تب درج تابع بروید. دسته را انتخاب کنید " آماری" یکی از عملکردها را از لیست کشویی انتخاب کنید و روی دکمه "Enter" کلیک کنید.
حداکثر و حداقل
حداکثر و حداقل مورد نیاز است تا به صورت دستی در بین تعداد زیادی اعداد برای حداقل یا حداکثر تعداد جستجو نشود.
برای محاسبه حداکثر، کل محدوده را انتخاب کنیداعداد مورد نیاز در جدول و یک سلول جداگانه، سپس بر روی "Σ" یا "" کلیک کنید. اتوسام" در پنجره ای که ظاهر می شود، "Maximum" را انتخاب کرده و با فشار دادن دکمه "Enter" مقدار مورد نظر را دریافت می کنید.
شما هم همین کار را می کنید تا حداقل ها را به دست آورید. فقط تابع "حداقل" را انتخاب کنید. 
برنامه اکسل هم توسط حرفه ای ها و هم برای آماتورها بسیار ارزشمند است، زیرا کاربران با هر سطح مهارتی می توانند با آن کار کنند. به عنوان مثال، هر کسی با حداقل مهارت های "ارتباطاتی" در اکسل می تواند یک نمودار ساده ترسیم کند، یک صفحه مناسب و غیره بسازد.
در عین حال، این برنامه حتی به شما امکان می دهد انواع مختلفی از محاسبات را انجام دهید، به عنوان مثال، محاسبات، اما این نیاز به یک سطح آموزشی کمی متفاوت دارد. با این حال، اگر به تازگی شروع به آشنایی نزدیک با این برنامه کرده اید و به هر چیزی که به شما کمک می کند کاربر پیشرفته تری شوید علاقه مند هستید، این مقاله برای شما مناسب است. امروز به شما خواهم گفت که فرمول انحراف استاندارد در اکسل چیست، چرا اصلاً مورد نیاز است و به طور دقیق، چه زمانی از آن استفاده می شود. برو!
آنچه هست
بیایید با تئوری شروع کنیم. معمولاً انحراف معیار را جذر می گویند که از میانگین حسابی همه مجذور اختلاف بین مقادیر موجود و همچنین میانگین حسابی آنها به دست می آید. به هر حال، این مقدار معمولاً حرف یونانی "سیگما" نامیده می شود. بر این اساس انحراف استاندارد با استفاده از فرمول STANDARDEVAL محاسبه می شود، برنامه این کار را برای خود کاربر انجام می دهد.
ماهیت این مفهوم شناسایی درجه تغییرپذیری یک ابزار است، یعنی در نوع خود شاخصی است که از آمار توصیفی به دست می آید. این تغییرات در نوسانات یک ابزار را در یک دوره زمانی مشخص مشخص می کند. فرمول های STDEV را می توان برای تخمین انحراف استاندارد یک نمونه، بدون توجه به مقادیر بولی و متن استفاده کرد.
فرمول
فرمولی که به طور خودکار در اکسل ارائه می شود به محاسبه انحراف معیار در اکسل کمک می کند. برای پیدا کردن آن، باید بخش فرمول را در اکسل پیدا کنید و سپس یکی به نام STANDARDEVAL را انتخاب کنید، بنابراین بسیار ساده است.
پس از این، پنجره ای در مقابل شما ظاهر می شود که در آن باید داده هایی را برای محاسبه وارد کنید. به طور خاص، دو عدد باید در فیلدهای خاص وارد شود، پس از آن برنامه خود انحراف استاندارد را برای نمونه محاسبه می کند.
بدون شک، فرمول ها و محاسبات ریاضی موضوعی نسبتاً پیچیده هستند و همه کاربران نمی توانند بلافاصله با آن کنار بیایند. با این حال، اگر کمی عمیق‌تر بگردید و کمی جزئی‌تر به موضوع نگاه کنید، معلوم می‌شود که همه چیز آنقدر غم‌انگیز نیست. امیدوارم با استفاده از مثال محاسبه انحراف معیار در این مورد قانع شده باشید.
ویدیو برای کمک
آمار از تعداد زیادی شاخص استفاده می کند و یکی از آنها محاسبه واریانس در اکسل است. اگر خودتان این کار را به صورت دستی انجام دهید، زمان زیادی می برد و می توانید اشتباهات زیادی مرتکب شوید. امروز به نحوه تجزیه فرمول های ریاضی به توابع ساده خواهیم پرداخت. بیایید به برخی از ساده‌ترین، سریع‌ترین و راحت‌ترین روش‌های محاسبه نگاه کنیم که به شما امکان می‌دهد همه چیز را در عرض چند دقیقه انجام دهید.
محاسبه واریانس
واریانس یک متغیر تصادفی انتظار ریاضی مجذور انحراف یک متغیر تصادفی از انتظار ریاضی آن است.
ما بر اساس جمعیت عمومی محاسبه می کنیم
برای محاسبه تشک. در حال انتظار برای استفاده برنامه از تابع DISP.G، و نحو آن به این صورت است: "=DISP.G(Number1;Number2;…)".
حداکثر 255 آرگومان قابل استفاده است، نه بیشتر. آرگومان ها می توانند اعداد اول یا ارجاع به سلول هایی باشند که در آنها مشخص شده اند. بیایید نحوه محاسبه واریانس در مایکروسافت اکسل را بررسی کنیم:
1. اولین قدم این است که سلولی را انتخاب کنید که در آن نتیجه محاسبه نمایش داده می شود و سپس روی دکمه "درج تابع" کلیک کنید.
2. پوسته مدیریت عملکرد باز می شود. در آنجا باید به دنبال تابع "DISP.G" بگردید که می تواند در دسته "آماری" یا "لیست کامل الفبایی" باشد. وقتی پیدا شد، آن را انتخاب کنید و روی «OK» کلیک کنید.
3. پنجره ای با آرگومان های تابع باز می شود. در آن باید خط "شماره 1" را انتخاب کنید و در ورق محدوده سلول ها را با سری اعداد انتخاب کنید.
4. پس از این، نتایج محاسبه در سلولی که تابع وارد شده است نمایش داده می شود.
به این ترتیب می توانید به راحتی واریانس را در اکسل پیدا کنید.
ما محاسبات را بر اساس نمونه انجام می دهیم
در این حالت، واریانس نمونه در اکسل با مخرج محاسبه می شود که تعداد کل اعداد را نشان نمی دهد، بلکه یک عدد کمتر را نشان می دهد. این کار برای یک خطای کوچکتر با استفاده از تابع ویژه DISP.V انجام می شود که نحو آن =DISP.V(Number1;Number2;...) است. الگوریتم اقدامات:
- مانند روش قبلی، باید یک سلول را برای نتیجه انتخاب کنید.
- در Function Wizard، باید "DISP.B" را در زیر دسته "Full Alphabetical List" یا "Statistical" بیابید.
- در مرحله بعد، پنجره ای ظاهر می شود و باید به همان روش قبلی ادامه دهید.
ویدئو: محاسبه واریانس در اکسل
نتیجه
واریانس در اکسل بسیار ساده‌تر، بسیار سریع‌تر و راحت‌تر از انجام دستی محاسبه می‌شود، زیرا تابع انتظارات ریاضی بسیار پیچیده است و محاسبه آن می‌تواند زمان و تلاش زیادی را ببرد.
بیایید محاسبه کنیمام‌اسبرتری داشتنواریانس نمونه و انحراف معیار ما همچنین واریانس یک متغیر تصادفی را در صورتی که توزیع آن مشخص باشد محاسبه خواهیم کرد.
بیایید ابتدا در نظر بگیریم پراکندگی، سپس انحراف معیار.
واریانس نمونه
واریانس نمونه (واریانس نمونه،نمونهواریانس) گسترش مقادیر در آرایه را نسبت به .
هر 3 فرمول از نظر ریاضی معادل هستند.
از فرمول اول مشخص است که واریانس نمونهمجموع مجذور انحرافات هر مقدار در آرایه است از متوسطتقسیم بر حجم نمونه منهای 1.
واریانس ها نمونه هاتابع DISP() انگلیسی استفاده می شود. نام VAR، یعنی واریانس از نسخه MS EXCEL 2010، توصیه می شود از آنالوگ DISP.V()، انگلیسی آن استفاده کنید. نام VARS، یعنی. واریانس نمونه علاوه بر این، با شروع از نسخه MS EXCEL 2010، یک تابع DISP.Г()، انگلیسی وجود دارد. نام VARP، یعنی. Population VARiance که محاسبه می کند پراکندگیبرای جمعیت. کل تفاوت به مخرج برمی گردد: به جای n-1 مانند DISP.V()، DISP.G() فقط n در مخرج دارد. قبل از MS EXCEL 2010، از تابع VAR() برای محاسبه واریانس جامعه استفاده می شد.
واریانس نمونه
=QUADROTCL(نمونه)/(COUNT(نمونه)-1)
=(SUM(Sample)-COUNT(Sample)*AVERAGE(Sample)^2)/ (COUNT(Sample)-1)- فرمول معمولی
=SUM((Sample -AVERAGE(Sample))^2)/ (COUNT(Sample)-1) –
واریانس نمونهبرابر 0 است، فقط در صورتی که همه مقادیر با یکدیگر برابر و بر این اساس برابر باشند مقدار متوسط. معمولاً هر چه مقدار آن بزرگتر باشد واریانس ها، گسترش مقادیر در آرایه بیشتر است.
واریانس نمونهتخمین نقطه ای است واریانس هاتوزیع متغیر تصادفی که از آن ساخته شده است نمونه. در مورد ساخت و ساز فاصله اطمینانهنگام ارزیابی واریانس هارا می توان در مقاله خواند.
واریانس یک متغیر تصادفی
برای محاسبه پراکندگیمتغیر تصادفی، شما باید آن را بدانید.
برای واریانس هامتغیر تصادفی X اغلب Var(X) نشان داده می شود. پراکندگیبرابر مربع انحراف از میانگین E(X): Var(X)=E[(X-E(X)) 2 ]
پراکندگیبا فرمول محاسبه می شود:
که در آن x i مقداری است که یک متغیر تصادفی می تواند بگیرد، و μ مقدار متوسط ()، p(x) احتمال این است که متغیر تصادفی مقدار x را بگیرد.
اگر یک متغیر تصادفی دارای پراکندگیبا فرمول محاسبه می شود:
بعد، ابعاد، اندازه واریانس هامربوط به مربع واحد اندازه گیری مقادیر اصلی است. به عنوان مثال، اگر مقادیر در نمونه نشان دهنده اندازه گیری وزن قطعه (به کیلوگرم) باشد، بعد واریانس کیلوگرم 2 خواهد بود. تفسیر این می تواند دشوار باشد، بنابراین برای مشخص کردن گسترش ارزش ها، مقداری برابر با ریشه دوم است واریانس ها – انحراف معیار.
برخی از خواص واریانس ها:
Var(X+a)=Var(X)، که در آن X یک متغیر تصادفی و a یک ثابت است.
Var(aХ)=a 2 Var(X)
Var(X)=E[(X-E(X)) 2 ]=E=E(X 2)-E(2*X*E(X))+(E(X)) 2 =E(X 2)- 2*E(X)*E(X)+(E(X)) 2 =E(X 2)-(E(X)) 2
این خاصیت پراکندگی در مقاله در مورد رگرسیون خطی.
Var(X+Y)=Var(X) + Var(Y) + 2*Cov(X;Y)، که در آن X و Y متغیرهای تصادفی هستند، Cov(X;Y) کوواریانس این متغیرهای تصادفی است.
اگر متغیرهای تصادفی مستقل باشند، پس آنها کوواریانسبرابر 0 است و بنابراین Var(X+Y)=Var(X)+Var(Y). این خاصیت پراکندگی در اشتقاق استفاده می شود.
اجازه دهید نشان دهیم که برای مقادیر مستقل Var(X-Y)=Var(X+Y). در واقع، Var(X-Y)= Var(X-Y)= Var(X+(-Y))= Var(X)+Var(-Y)= Var(X)+Var(-Y)= Var(X)+(- 1) 2 Var(Y)= Var(X)+Var(Y)= Var(X+Y). این ویژگی پراکندگی برای ساخت استفاده می شود.
انحراف استاندارد نمونه
انحراف استاندارد نمونهاندازه گیری میزان پراکندگی گسترده مقادیر در یک نمونه نسبت به آنها است.
الف مقدماتی، انحراف معیاربرابر با جذر واریانس ها:
انحراف معیاربزرگی مقادیر موجود در آن را در نظر نمی گیرد نمونه، اما فقط درجه پراکندگی مقادیر در اطراف آنها میانگین. برای روشن شدن این موضوع مثالی می زنیم.
بیایید انحراف معیار را برای 2 نمونه محاسبه کنیم: (1؛ 5؛ 9) و (1001؛ 1005؛ 1009). در هر دو مورد s=4. بدیهی است که نسبت انحراف استاندارد به مقادیر آرایه بین نمونه ها به طور قابل توجهی متفاوت است. برای چنین مواردی استفاده می شود ضریب تغییرات(ضریب تغییرات، CV) - نسبت انحراف معیاربه میانگین حسابی، به صورت درصد بیان می شود.
در MS EXCEL 2007 و نسخه های قبلی برای محاسبه انحراف استاندارد نمونهتابع =STDEVAL() انگلیسی استفاده می شود. نام STDEV، یعنی. انحراف معیار. از نسخه MS EXCEL 2010، توصیه می شود از آنالوگ آن = STANDDEV.B() ، انگلیسی استفاده کنید. نام STDEV.S، یعنی. نمونه انحراف استاندارد.
علاوه بر این، با شروع از نسخه MS EXCEL 2010، یک تابع STANDARDEV.G()، انگلیسی وجود دارد. نام STDEV.P، یعنی. انحراف استاندارد جمعیت، که محاسبه می کند انحراف معیاربرای جمعیت. کل تفاوت به مخرج برمی گردد: به جای n-1 مانند STANDARDEV.V()، STANDARDEVAL.G() فقط n در مخرج دارد.
انحراف معیارهمچنین می توان مستقیماً با استفاده از فرمول های زیر محاسبه کرد (به فایل نمونه مراجعه کنید)
=ROOT(QUADROTCL(نمونه)/(COUNT(نمونه)-1))
=ROOT((SUM(Sample)-COUNT(Sample)*AVERAGE(Sample)^2)/(COUNT(Sample)-1))
سایر معیارهای پراکندگی
تابع SQUADROTCL() با محاسبه می شود مجموع مجذور انحراف مقادیر از آنها میانگین. این تابع همان نتیجه را با فرمول =DISP.G( نمونه)*بررسی( نمونه) ، جایی که نمونه- ارجاع به محدوده ای حاوی آرایه ای از مقادیر نمونه (). محاسبات در تابع QUADROCL() طبق فرمول انجام می شود:
تابع SROTCL() نیز اندازه گیری گسترش یک مجموعه داده است. تابع SROTCL() میانگین مقادیر مطلق انحراف مقادیر از میانگین. این تابع همان نتیجه فرمول را برمی گرداند =SUMPRODUCT(ABS(Sample-AVERAGE(Sample)))/COUNT(Sample)، جایی که نمونه- پیوندی به یک محدوده حاوی آرایه ای از مقادیر نمونه.
محاسبات در تابع SROTCL () طبق فرمول انجام می شود:
