Стандартно отклонение в excel. Как да намерите средното аритметично в Excel

В тази статия ще говоря за как да намерите стандартното отклонение. Този материал е изключително важен за пълното разбиране на математиката, така че учителят по математика трябва да отдели отделен урок или дори няколко за изучаването му. В тази статия ще намерите връзка към подробен и разбираем видео урок, който обяснява какво е стандартното отклонение и как да го намерите.

стандартно отклонениедава възможност да се оцени разпространението на стойностите, получени в резултат на измерване на определен параметър. Обозначава се със символ (гръцката буква "сигма").

Формулата за изчисление е доста проста. За да намерите стандартното отклонение, трябва да вземете Корен квадратенот дисперсия. Така че сега трябва да попитате „Какво е дисперсия?“

Какво е дисперсия

Определението за дисперсия е следното. Дисперсията е средната аритметична стойност на квадратните отклонения на стойностите от средната стойност.

За да намерите дисперсията, извършете следните изчисления последователно:

Определете средната стойност (проста средна стойност аритметична сериястойности).
След това извадете средната стойност от всяка от стойностите и повдигнете получената разлика на квадрат (получихме разлика на квадрат).
Следващата стъпка е да изчислим средноаритметичната стойност на квадратите на получените разлики (Можете да разберете защо са точно квадратите по-долу).

Нека разгледаме един пример. Да приемем, че вие и вашите приятели решите да измерите височината на вашите кучета (в милиметри). В резултат на измерванията получихте следните измервания на височината (при холката): 600 mm, 470 mm, 170 mm, 430 mm и 300 mm.

Нека изчислим средната стойност, дисперсията и стандартното отклонение.

Нека първо намерим средната стойност. Както вече знаете, за това трябва да добавите всички измерени стойности и да ги разделите на броя на измерванията. Напредък на изчислението:

Средно мм.

И така, средната (средноаритметична) е 394 mm.

Сега трябва да дефинираме отклонение на височината на всяко от кучетата от средната:

накрая за изчисляване на дисперсията, всяка от получените разлики се повдига на квадрат и след това намираме средноаритметичното на получените резултати:

Дисперсия mm 2 .

Така дисперсията е 21704 mm 2 .

Как да намерите стандартното отклонение

И така, как сега да изчислим стандартното отклонение, знаейки дисперсията? Както помним, вземете корен квадратен от това. Тоест стандартното отклонение е:

mm (закръглено до най-близкото цяло число в mm).

Използвайки този метод, открихме, че някои кучета (напр. ротвайлери) са много големи кучета. Но има и много малки кучета (например дакели, но не трябва да им казвате това).

Най-интересното е, че стандартното отклонение носи полезна информация. Сега можем да покажем кои от получените резултати от измерването на растежа са в интервала, който получаваме, ако отделим от средното (от двете му страни) стандартното отклонение.

Тоест, използвайки стандартното отклонение, получаваме „стандартен“ метод, който ви позволява да разберете коя от стойностите е нормална (статистическа средна) и коя е изключително голяма или, обратно, малка.

Какво е стандартно отклонение

Но ... нещата ще бъдат малко по-различни, ако анализираме вземане на пробиданни. В нашия пример разгледахме общото население.Тоест нашите 5 кучета бяха единствените кучета в света, които ни интересуваха.

Но ако данните са примерни (стойности, избрани от голям население), тогава изчисленията трябва да се извършват по различен начин.

Ако има стойности, тогава:

Всички други изчисления се правят по същия начин, включително определянето на средната стойност.

Например, ако нашите пет кучета са само извадка от популация от кучета (всички кучета на планетата), трябва да разделим на 4 вместо 5а именно:

Дисперсия на извадката = mm 2 .

В този случай стандартното отклонение за извадката е равно на mm (закръглено до най-близкото цяло число).

Можем да кажем, че направихме известна "корекция" в случай, че нашите стойности са само малка извадка.

Забележка. Защо точно квадратите на разликите?

Но защо вземаме квадратите на разликите, когато изчисляваме дисперсията? Да приемем, че при измерване на някакъв параметър сте получили следния набор от стойности: 4; четири; -четири; -четири. Ако просто добавим абсолютните отклонения от средната стойност (разликата) помежду си... отрицателни стойностивзаимно се компенсират с положителните:

Оказва се, че тази опция е безполезна. Тогава може би си струва да опитате абсолютните стойности на отклоненията (т.е. модулите на тези стойности)?

На пръв поглед се оказва, че не е лошо (резултантната стойност, между другото, се нарича средно абсолютно отклонение), но не във всички случаи. Нека опитаме друг пример. Нека резултатът от измерването е следният набор от стойности: 7; един; -6; -2. Тогава средното абсолютно отклонение е:

по дяволите! Отново получихме резултат 4, въпреки че разликите са с много по-голям спред.

Сега нека видим какво се случва, ако повдигнем на квадрат разликите (и след това извадим корен квадратен от тяхната сума).

За първия пример получавате:

За втория пример получавате:

Сега е съвсем друг въпрос! Средноквадратичното отклонение е толкова по-голямо, колкото по-голямо е разпространението на разликите ... към което се стремихме.

Всъщност в този методизползва се същата идея като при изчисляването на разстоянието между точките, само че се прилага по различен начин.

И със математическа точкаот гледна точка използването на квадрати и квадратни корени е по-полезно, отколкото бихме могли да получим въз основа на това абсолютни стойностиотклонения, поради което стандартното отклонение е приложимо и за други математически задачи.

Сергей Валериевич ви каза как да намерите стандартното отклонение

Сред многото показатели, които се използват в статистиката, е необходимо да се подчертае изчисляването на дисперсията. Трябва да се отбележи, че ръчното извършване на това изчисление е доста досадна задача. За щастие в Excel има функции, които ви позволяват да автоматизирате процедурата за изчисление. Нека да разберем алгоритъма за работа с тези инструменти.

Дисперсията е индикатор за вариация, която е среден квадратотклонения от математическо очакване. По този начин той изразява разпространението на числата около средната стойност. Изчисляването на дисперсията може да се извърши както за генералната съвкупност, така и за извадката.

Метод 1: изчисление върху генералната съвкупност

За изчисляване на този показател в Excel за общата съвкупност се използва функцията ДИСП.Г. Синтаксисът за този израз е както следва:

DISP.G(Число1;Число2;…)

Общо могат да се прилагат от 1 до 255 аргумента. Аргументите могат да бъдат числови стойности, както и препратки към клетките, в които се съдържат.

Нека видим как да изчислим тази стойност за диапазон от числови данни.

Метод 2: примерно изчисление

За разлика от изчисляването на стойността за генералната съвкупност, при изчислението за извадката знаменателят не се посочва обща сумачисла, но с едно по-малко. Това се прави, за да се коригира грешката. Excel взема предвид този нюанс в специална функция, която е предназначена за този тип изчисление - DISP.V. Синтаксисът му е представен със следната формула:

VAR.B(Число1;Число2;…)

Броят на аргументите, както в предишната функция, също може да варира от 1 до 255.

Както можете да видите, програмата Excel е в състояние значително да улесни изчисляването на дисперсията. Това статистикаможе да се изчисли от приложението както за генералната съвкупност, така и за извадката. В този случай всички потребителски действия всъщност се свеждат само до посочване на диапазона от числа, които да бъдат обработени, а Excel върши основната работа сам. Разбира се, това ще спести значително време на потребителите.

Стандартното отклонение е едно от тях статистически терминив корпоративния свят, което ви позволява да издигнете авторитета на хора, които са успели да го прецакат успешно по време на разговор или презентация, и оставя неясно недоразумение за тези, които не знаят какво е, но се колебаят да попитат. Всъщност повечето мениджъри не разбират концепцията за стандартното отклонение и ако сте един от тях, време е да спрете да живеете в лъжата. В днешната статия ще ви покажа как тази подценена статистика може да ви помогне да разберете по-добре данните, с които работите.

Какво измерва стандартното отклонение?

Представете си, че сте собственик на два магазина. И за да се избегнат загуби, е важно да има ясен контрол на складовите наличности. В опит да разберете кой е най-добрият борсов мениджър, решавате да анализирате акциите от последните шест седмици. Средната седмична себестойност на наличността и на двата магазина е приблизително еднаква и е около 32 условни единици. На пръв поглед средната стойност на акциите показва, че и двамата мениджъри работят по един и същ начин.

Но ако погледнете по-отблизо дейността на втория магазин, можете да видите, че въпреки че средната стойност е правилна, променливостта на акциите е много висока (от 10 до 58 USD). Следователно може да се заключи, че средната стойност не винаги оценява правилно данните. Тук идва стандартното отклонение.

Стандартното отклонение показва как стойностите са разпределени спрямо средната стойност в нашия . С други думи, можете да разберете колко голям е оттокът от седмица на седмица.

В нашия пример сме използвали Функция на Excel STDEV за изчисляване на оценката на стандартното отклонение заедно със средната стойност.

В случая на първия мениджър стандартното отклонение е 2. Това ни казва, че всяка стойност в извадката се отклонява средно с 2 от средната стойност. Добро е? Нека погледнем въпроса от друг ъгъл - стандартно отклонение от 0 ни казва, че всяка стойност в извадката е равна на средната си стойност (в нашия случай 32,2). Например, стандартно отклонение от 2 не се различава много от 0, което показва, че повечето от стойностите са близки до средните. Колкото по-близо е стандартното отклонение до 0, толкова по-надеждна е средната стойност. Освен това, стандартно отклонение, близко до 0, показва малка променливост в данните. Тоест стойността на потъване със стандартно отклонение от 2 показва невероятната последователност на първия мениджър.

В случая на втория магазин стандартното отклонение е 18,9. Тоест цената на оттока се отклонява средно с 18,9 от средната стойност от седмица на седмица. Лудо разпространение! Колкото по-далеч е стандартното отклонение от 0, толкова по-малко точна е средната стойност. В нашия случай цифрата от 18,9 показва, че на средната стойност ($32,8 на седмица) просто не може да се вярва. Това също ни казва, че седмичният отток е силно променлив.

Това е концепцията за стандартно отклонение накратко. Въпреки че не дава представа за други важни статистически мерки (режим, медиана...), всъщност стандартното отклонение играе решаваща роляв повечето статистики. Разбирането на принципите на стандартното отклонение ще хвърли светлина върху същността на много процеси във вашата дейност.

Как да изчислим стандартното отклонение?

И така, сега знаем какво казва цифрата на стандартното отклонение. Да видим как се брои.

Помислете за набор от данни от 10 до 70 на стъпки от 10. Както можете да видите, вече изчислих стандартното отклонение за тях с помощта на функцията STDEV в клетка H2 (оранжева).

По-долу са стъпките, които Excel предприема, за да стигне до 21.6.

Моля, имайте предвид, че всички изчисления са визуализирани, за по-добро разбиране. Всъщност в Excel изчислението е мигновено, оставяйки всички стъпки зад кулисите.

Excel първо намира средната стойност на извадката. В нашия случай средната стойност се оказа 40, която се изважда от всяка примерна стойност в следващата стъпка. Всяка получена разлика се повдига на квадрат и се сумира. Получихме сумата, равна на 2800, която трябва да бъде разделена на броя на примерните елементи минус 1. Тъй като имаме 7 елемента, се оказва, че трябва да разделим 2800 на 6. От резултата намираме квадратния корен, тази цифра ще бъде стандартното отклонение.

За тези, които не са напълно ясни относно принципа на изчисляване на стандартното отклонение с помощта на визуализация, давам математическа интерпретация на намирането на тази стойност.

Функции за изчисляване на стандартно отклонение в Excel

В Excel има няколко разновидности на формули за стандартно отклонение. Просто трябва да напишете =STDEV и ще се убедите сами.

Струва си да се отбележи, че функциите STDEV.V и STDEV.G (първата и втората функция в списъка) дублират съответно функциите STDEV и STDEV (петата и шестата функция в списъка), които бяха оставени за съвместимост с повече ранни версии Excel.

Като цяло разликата в окончанията В и G функциите показват принципа на изчисляване на стандартното отклонение на извадка или популация. Вече обясних разликата между тези два масива в предишния.

Характеристика на функциите STDEV и STDEVPA (третата и четвъртата функция в списъка) е, че при изчисляване на стандартното отклонение на масив се вземат предвид логически и текстови стойности. Текстът и истинските булеви стойности са 1, а фалшивите булеви стойности са 0. Трудно ми е да си представя ситуация, в която ще имам нужда от тези две функции, така че мисля, че могат да бъдат игнорирани.

Трябва да се справим с изчисляването на такива стойности като дисперсия, средно стандартно отклонениеи, разбира се, коефициентът на вариация. Трябва да се даде изчислението на последното Специално внимание. Много е важно всеки начинаещ, който току-що започва да работи с редактор на електронни таблици, да може бързо да изчисли относителното разсейване на стойностите.

Какъв е коефициентът на вариация и защо е необходим?

Така че, струва ми се, че би било полезно да се направи кратко теоретично отклонение и да се разбере природата на коефициента на вариация. Този индикатор е необходим за отразяване на обхвата на данните спрямо средната стойност. С други думи, показва отношението на стандартното отклонение към средната стойност. Обичайно е коефициентът на вариация да се измерва в процентно изражение и да се използва за показване на хомогенността на динамичните редове.

Коефициентът на вариация ще стане незаменим помощник в случай, че трябва да направите прогноза въз основа на данни от дадена извадка. Този индикатор ще подчертае основните диапазони от стойности, които ще бъдат най-полезни за последващо прогнозиране, както и ще изчисти извадката от незначителни фактори. Така че, ако видите, че стойността на коефициента е 0%, тогава декларирайте с увереност, че серията е хомогенна, което означава, че всички стойности в нея са равни една на друга. Ако коефициентът на вариация приеме стойност над 33%, това означава, че имате работа с хетерогенна серия, в която индивидуални ценностизначително се различава от средното за извадката.

Как да намерим стандартното отклонение?

Тъй като трябва да използваме стандартното отклонение, за да изчислим индикатора за вариация в Excel, би било съвсем подходящо да разберем как изчисляваме този параметър.

От училищния курс по алгебра знаем, че стандартното отклонение е квадратният корен, извлечен от дисперсията, тоест този показател определя степента на отклонение на определен показател от общата извадка от средната му стойност. С него можем да измерваме абсолютна мяркафлуктуации на изследвания признак и ясно да го интерпретирате.

Изчислете коефициента в Excel

За съжаление Excel няма стандартна формула, която да ви позволи автоматично да изчислите индикатора за вариация. Но това не означава, че трябва да правите изчисленията наум. Липсата на шаблон в "Формулната лента" по никакъв начин не намалява възможностите на Excel, така че можете лесно да принудите програмата да извърши изчислението, от което се нуждаете, като ръчно въведете съответната команда.

За да изчислите индикатора за вариация в Excel, трябва да запомните училищен курсматематика и разделете стандартното отклонение на средната стойност на извадката. Тоест, всъщност формулата изглежда така - STDEV(посочен диапазон от данни) / AVERAGE(посочен диапазон от данни). Трябва да въведете тази формула в клетката на Excel, в която искате да получите необходимото изчисление.

Имайте предвид, че тъй като коефициентът е изразен като процент, клетката с формулата ще трябва да бъде съответно форматирана. Можете да направите това по следния начин:

Отворете раздела Начало.
Намерете категорията в нея " Форматиране на клетки"И изберете необходимата опция.

Като алтернатива можете да зададете процентния формат на клетката, като щракнете с десния бутон на мишката върху активираната клетка на таблицата. В контекстното меню, което се появява, подобно на горния алгоритъм, трябва да изберете категорията „Формат на клетката“ и да зададете необходимата стойност.

Изберете „Процент“ и по избор въведете броя на десетичните знаци

Може би горният алгоритъм ще изглежда сложен за някого. Всъщност изчисляването на коефициента е толкова просто, колкото добавянето на две естествени числа. След като изпълните тази задача в Excel, никога няма да се върнете към досадните многосрични решения в тетрадка.

Все още не мога качествено сравнениестепен на разсейване на данните? Изгубени в размера на извадката? Тогава веднага се заемете с работата и овладейте на практика всичко теоретичен материалкоето беше описано по-горе! Позволявам Статистически анализи развитието на прогноза вече не ви причинява страх и негативизъм. Спестете енергия и време с

Програмата Excel е високо ценена както от професионалисти, така и от аматьори, тъй като потребител на всяко ниво на обучение може да работи с нея. Например, всеки с минимални умения за "комуникация" с Excel може да начертае проста графика, да направи приличен знак и т.н.

В същото време тази програма дори ви позволява да извършвате различни видове изчисления, например изчисления, но това вече изисква малко по-различно ниво на обучение. Ако обаче току-що сте започнали близко запознаване с тази програма и се интересувате от всичко, което ще ви помогне да станете по-напреднал потребител, тази статия е за вас. Днес ще ви кажа каква е формулата за стандартно отклонение в Excel, защо изобщо е необходима и всъщност кога се прилага. Отивам!

Какво е

Да започнем с теорията. Стандартното отклонение обикновено се нарича квадратен корен, получено от средната аритметична стойност на всички квадратни разлики между наличните стойности, както и тяхната средна аритметична стойност. Между другото, тази стойност обикновено се нарича гръцка буква "сигма". Стандартното отклонение се изчислява по формулата STDEV, съответно програмата го прави за самия потребител.

Въпросът е тази концепцияе да разкрие степента на волатилност на инструмента, т.е. той по свой начин е индикатор Описателна статистика. Той разкрива промени в волатилността на инструмента във всеки период от време. С помощта на формули STDEV можете да оцените стандартното отклонение на извадка, докато булевите и текстовите стойности се игнорират.

Формула

Помага за изчисляване на стандартното отклонение в формула на ексел, който се предоставя автоматично в програма Excel. За да го намерите, трябва да намерите секцията с формули в Excel и вече там да изберете тази, която има името STDEV, така че е много проста.

След това пред вас ще се появи прозорец, в който ще трябва да въведете данни за изчислението. По-специално, две числа трябва да бъдат въведени в специални полета, след което програмата автоматично ще изчисли стандартното отклонение за извадката.

Несъмнено математически формулии изчисленията е доста сложен въпрос и не всички потребители могат да се справят с него веднага. Въпреки това, ако копаете малко по-дълбоко и разберете проблема малко по-подробно, се оказва, че не всичко е толкова тъжно. Надявам се, на примера за изчисление стандартно отклонениети се увери в това.