Функция стандартно отклонение в excel. Какво е стандартно отклонение - използване на функцията за стандартно отклонение за изчисляване на стандартно отклонение в Excel

Един от основните инструменти на статистическия анализ е изчисляването на стандартното отклонение. Този индикатор ви позволява да оцените стандартното отклонение за извадка или за популация. Нека научим как да използваме формулата за стандартно отклонение в Excel.

Нека веднага да определим какво е стандартното отклонение и как изглежда формулата му. Това количество е корен квадратен от средната аритметична стойност на квадратите на разликата между всички количества в редицата и тяхната средна аритметична стойност. Има идентично наименование за този показател - стандартно отклонение. И двете имена са напълно равностойни.

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

Изчисляване в Excel

Можете да изчислите посочената стойност в Excel с помощта на две специални функции STDEV.V(въз основа на извадката) и STDEV.G(въз основа на общата съвкупност). Принципът на тяхното действие е абсолютно същият, но те могат да бъдат извикани по три начина, които ще разгледаме по-долу.

Метод 1: Съветник за функции

Метод 2: Раздел Формули

Метод 3: Ръчно въвеждане на формулата

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

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

Дисперсията е мярка за дисперсия, която описва сравнителното отклонение между стойностите на данните и средната стойност. Това е най-използваната мярка за дисперсия в статистиката, изчислена чрез сумиране и повдигане на квадрат на отклонението на всяка стойност на данните от средната стойност. Формулата за изчисляване на дисперсията е дадена по-долу:

s 2 – дисперсия на извадката;

x av—извадкова средна стойност;

н — размер на извадката (брой стойности на данните),

(x i – x avg) е отклонението от средната стойност за всяка стойност от набора от данни.

За да разберем по-добре формулата, нека разгледаме един пример. Не обичам много да готвя, така че рядко го правя. Въпреки това, за да не гладувам, от време на време трябва да ходя до печката, за да изпълня плана за насищане на тялото си с протеини, мазнини и въглехидрати. Наборът от данни по-долу показва колко пъти Ренат готви всеки месец:

Първата стъпка в изчисляването на дисперсията е да се определи средната стойност на извадката, която в нашия пример е 7,8 пъти на месец. Останалите изчисления могат да бъдат улеснени с помощта на следната таблица.

Последната фаза на изчисляване на дисперсията изглежда така:

За тези, които обичат да правят всички изчисления наведнъж, уравнението ще изглежда така:

Използване на метода за броене на суровини (пример за готвене)

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

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

е квадратът на сумата от всички стойности на данните.

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

Както можете да видите, резултатът беше същият като при използване на предишния метод. Предимствата на този метод стават очевидни с увеличаване на размера на извадката (n).

Изчисляване на дисперсия в Excel

Както вероятно вече се досещате, Excel има формула, която ви позволява да изчислите дисперсията. Освен това, започвайки с Excel 2010, можете да намерите 4 вида формула за дисперсия:

1) VARIANCE.V – Връща дисперсията на извадката. Булевите стойности и текстът се игнорират.

2) DISP.G – Връща дисперсията на съвкупността. Булевите стойности и текстът се игнорират.

3) VARIANCE - Връща дисперсията на извадката, като взема предвид булевите и текстовите стойности.

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

Първо, нека разберем разликата между извадка и популация. Целта на описателната статистика е да обобщава или показва данни, така че бързо да получите общата картина, общ преглед, така да се каже. Статистическото заключение ви позволява да правите изводи за популация въз основа на извадка от данни от тази популация. Популацията представлява всички възможни резултати или измервания, които са от интерес за нас. Извадката е подмножество от популация.

Например, интересуваме се от група студенти от един от руските университети и трябва да определим средния резултат на групата. Можем да изчислим средното представяне на учениците и тогава получената цифра ще бъде параметър, тъй като цялото население ще бъде включено в нашите изчисления. Ако обаче искаме да изчислим GPA на всички ученици у нас, тогава тази група ще бъде нашата извадка.

Разликата във формулата за изчисляване на дисперсията между извадка и популация е знаменателят. Където за извадката ще бъде равно на (n-1), а за генералната съвкупност само n.

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

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

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

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

Метод 1: изчисление въз основа на населението

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

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

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

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

Метод 2: изчисление по извадка

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

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

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

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

Концепцията за процентно отклонение се отнася до разликата между две числени стойности като процент. Нека дадем конкретен пример: да кажем, че един ден от склад на едро са продадени 120 таблетки, а на следващия ден - 150 броя. Разликата в обема на продажбите е очевидна, на следващия ден бяха продадени още 30 таблетки. При изваждане на числото 120 от 150 получаваме отклонение, което е равно на числото +30. Възниква въпросът: какво е процентно отклонение?

Как да изчислим процентно отклонение в Excel

Процентното отклонение се изчислява чрез изваждане на старата стойност от новата стойност и след това резултатът се раздели на старата стойност. Резултатът от това изчисление по формула в Excel трябва да се покаже в процентен формат на клетките. В този пример формулата за изчисление е както следва (150-120)/120=25%. Формулата е лесна за проверка: 120+25%=150.

Забележка!Ако разменим старите и новите числа, тогава ще имаме формула за изчисляване на надценката.

Фигурата по-долу показва пример как да представите горното изчисление като формула на Excel. Формулата в клетка D2 изчислява процентното отклонение между стойностите на продажбите за текущата и миналата година: =(C2-B2)/B2

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

Въведете формулата правилно със скоби в клетка D2 и след това просто я копирайте в останалите празни клетки от диапазона D2:D5. За да копирате формулата по най-бързия начин, просто преместете курсора на мишката до маркера на курсора на клавиатурата (в долния десен ъгъл), така че курсорът на мишката да се промени от стрелка на черен кръст. След това просто щракнете два пъти с левия бутон на мишката и Excel автоматично ще попълни празните клетки с формулата и ще определи диапазона D2:D5, който трябва да бъде попълнен до клетка D5 и не повече. Това е много удобен лайфхак за Excel.



Алтернативна формула за изчисляване на процентно отклонение в Excel

В алтернативна формула, която изчислява относителното отклонение на стойностите на продажбите от текущата година, незабавно разделете на стойностите на продажбите от предходната година и едва тогава едно се изважда от резултата: =C2/B2-1.

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

Единственият недостатък на тази алтернативна формула е невъзможността да се изчисли процентното отклонение за отрицателни числа в числителя или в заместителя. Дори ако използваме функцията ABS във формулата, формулата ще върне грешен резултат, ако числото в заместителя е отрицателно.

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

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

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

Какво е

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

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

Формула

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

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

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

Видео в помощ