Стандартно отклонение - Excel и Google Sheets

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

СТАНДАРТНО ОТХВЪРЛЯНЕ Обзор на функцията

Функцията STANDARD DEVIATION изчислява изчисляване на стандартното отклонение за цялата популация.

За да използвате функцията STANDARD DEVIATION Excel за работен лист, изберете клетка и въведете:

(Забележете как се появяват входовете на формулата)

Функция STANDARD DEVIATION Синтаксис и входове:

1	= STDEV (номер1, [номер2], …)

числа- Стойности за получаване на стандартна вариация

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

Когато се занимавате с данни, ще искате да изпълните някои основни тестове, които да ви помогнат да ги разберете. Обикновено ще започнете с изчисляване на средната стойност, като използвате функцията Excel AVERAGE <>.

Това ви дава представа къде е „средата“ на данните. И от там ще искате да погледнете колко разпределени са данните около тази средна точка. Тук идва стандартното отклонение.

Excel ви дава редица функции за изчисляване на стандартното отклонение - STDEV, STDEV.P, STDEV.S и DSTDEV. Ще стигнем до всички тях, но първо, нека научим какво е стандартното отклонение е, точно.

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

Стандартното отклонение ви дава представа колко далеч са вашите точки от данни от средната стойност. Вземете следния набор от данни от тестови резултати от 100:

1	48,49,50,51,52

Средната стойност на този набор от данни е 50 (добавете всички числа и разделете на n, където n е броят на стойностите в диапазона).

Сега погледнете следния набор от данни:

1	10,25,50,75,90

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

Но в първата група имаме 5 души, които имат много сходни, много посредствени резултати. А във втората група ние, няколко високопоставени летци, балансирахме от няколко бедни, с един човек в средата. The разпространение резултатите са много различни, което прави и вашата интерпретация на данните много различна.

Стандартното отклонение е мярка за този спред.

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

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

За да изчислите стандартното отклонение, преминавате през този процес:

1) Изчислете средната стойност

Да вземем първия ни набор от данни по -горе: 48,49,50,51,52

Вече знаем средната стойност (50), която потвърдих тук с функцията Excel AVERAGE <>:

1	= СРЕДНА (C4: C8)

2) Извадете средната стойност от всяка стойност в набора от данни

Направих това със следната формула:

1	= C4- $ H $ 4

Нашата средна стойност е в H4 и аз „заключих“ препратката към клетката, като поставих знаците за долар пред колоната и реда (чрез натискане на F4). Това означава, че мога да копирам формулата надолу в колоната, без да се актуализира препратката към клетката.

Резултатът:

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

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

3) Квадрат разликите

Можем да постигнем това чрез изравняване на различията. Така че, нека добавим нова колона и поставим в квадрат числата в колоната D:

1

= D4*D4

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

4) Изчислете вариацията - средната стойност на квадратните разлики

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

• Ако използвате данни за населението, просто приемате средната стойност (сумирате стойностите и разделяте на n)
• Ако използвате примерни данни, взимате сумите на стойностите и ги разделяте на n-1

Данните за населението означават, че имате „пълния набор“ от вашите данни, например имате данни за всеки ученик в даден клас.

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

Проучването на политическо мнение е добър пример за примерни данни - изследователите проучват, да речем, 1000 души, за да добият представа за това, което мисли цяла държава или държава.

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

1	= СРЕДНО (E4: E8)

Добре, значи имаме 2. Този резултат е известен като „вариация“ и е основната точка за много статистически тестове, включително стандартното отклонение. Можете да прочетете повече за вариацията на главната й страница: как да изчислите дисперсията в Excel <>.

5) Вземете квадратния корен на вариацията

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

1	= SQRT (H4)

И имаме нашия резултат: стандартното отклонение е 1.414

Тъй като имаме квадратни корени на нашите предишни на квадрат числа, стандартното отклонение, дадено в същите единици като първоначалните данни. Така че стандартното отклонение тук е 1.414 тестови точки.

Стандартно отклонение, когато данните са по-разпределени

По -рано имахме втори примерен диапазон от данни: 10,25,50,75,90

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

Всички формули са абсолютно същите като преди (имайте предвид, че общата средна стойност все още е 50).

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

Разбира се, тъй като имаме само 5 точки от данни, е много лесно да се види, че разпространението на резултатите е различно между двата набора. Но когато имате 100 или 1000 точки от данни, не можете да кажете това, като просто бързо сканирате данните. И точно затова използваме стандартното отклонение.

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

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

Excel има няколко функции за тази цел:

• P изчислява стандартното отклонение за данните за населението (използвайки точния метод, който използвахме в горния пример)
• С изчислява стандартното отклонение за примерни данни (използвайки метода n-1, който засегнахме по-рано)
• СТДЕВ е абсолютно същото като STDEV.S. Това е по -стара функция, заменена от STDEV.S и STDEV.P.
• СТДЕВА е много подобен на STDEV.S, с изключение на това, че включва текстови клетки и булеви (TRUE/FALSE) клетки при изчисляването му.
• STDEVPA е много подобен на STDEV.P, с изключение на това, че включва текстови клетки и булеви (TRUE/FALSE) клетки при изчисляването му.

Уау, много опции тук! Не се плашете - в по -голямата част от случаите ще използвате или STDEV.P, или STDEV.S.

Нека да преминем през всеки от тях на свой ред, като започнем със STDEV.P, тъй като това е методът, по който току-що работихме.

Функцията Excel STDEV.P

STDEV.P изчислява стандартното отклонение за данните за населението. Използваш го така:

1	= STDEV.P (C4: C8)

Дефинирате един аргумент в STDEV.P: диапазонът от данни, за който искате да изчислите стандартното отклонение.

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

Забележка STDEV.P игнорира всички клетки, съдържащи текст или булеви (TRUE/FALSE) стойности. Ако трябва да ги включите, използвайте STDEVPA.

Функцията Excel STDEV.S

STDEV.S изчислява стандартното отклонение за примерни данни. Използвайте го така:

1	= STDEV.S (C4: C8)

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

Преди да влезем в пример, нека обсъдим разликата между STDEV.S и STDEV.P.

Както вече обсъдихме, STDEV.S трябва да се използва за примерни данни - когато вашите данни са част от по -голям набор. Така че нека приемем сега, че в нашия пример по -горе, повече хора са преминали теста. Искаме да изчислим стандартното отклонение на всеки, който е участвал в теста, използвайки само тези пет точки. Сега използваме примерни данни.

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

Вместо да използваме нормалния метод - сумираме всички стойности и разделяме на n, бихме обобщили всички стойности и разделим на n-1:

1	= SUM (E4: E8) / (COUNT (E4: E8) -1)

В тази формула:

• SUM получава сумата от квадратните разлики
• COUNT връща нашето n, от което изваждаме 1
• След това просто разделяме нашата сума на нашия n-1

Този път средната стойност на квадратните разлики е 2,5 (може би си спомняте, че преди това е била 2, така че е малко по -висока).

Така че защо разделяме на n-1 вместо на n, когато се занимаваме с примерни данни?

Отговорът е доста сложен и ако просто се опитвате да проверите числата си, за да разберете данните си, това не е нещо, с което наистина трябва да се притеснявате. Просто се уверете, че използвате STDEV.S за примерни данни и STDEV.P за данни за населението и ще се оправите.

Ако наистина сте любопитни да разберете защо, вижте главната страница за това как да изчислите дисперсията в Excel <>.

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

1	= SQRT (H4)

Получаваме 1,581.

STDEV.S прави всички горни изчисления вместо нас и връща стандартното отклонение на извадката само в една клетка. Така че нека да видим какво ще излезе …

1	= STDEV.S (C4: C8)

Да, отново 1.581.

Функцията Excel STDEV

Функцията STDEV на Excel работи по абсолютно същия начин като STDEV.S - тоест изчислява стандартното отклонение за извадка от данни.

Използвате го по същия начин:

1	= STDEV (C4: C8)

Отново получаваме същия резултат.

Важна забележка: STDEV е „Функция за съвместимост“, което основно означава, че Microsoft се отървава от нея. Засега работи, така че всички по -стари електронни таблици ще продължат да работят нормално. Но в бъдещите версии на Excel Microsoft може да го откаже напълно, така че трябва да използвате STDEV.S вместо STDEV, когато е възможно.

Функцията Excel STDEVA

STDEVA се използва и за изчисляване на стандартното отклонение за извадка, но има няколко важни разлики, за които трябва да знаете:

• ИСТИНСКИ стойности се отчитат като 1
• FALSE стойностите се отчитат като 0
• Текстовите низове се броят като 0

Използвайте го както следва:

1	= STDEVA (C4: C8)

Още четирима приятели и членове на семейството са дали резултатите от тестовете си. Те са показани в колона C, а колона D показва как STDEVA интерпретира тези данни.

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

Функцията Excel STDEVPA

STDEVPA изчислява стандартното отклонение за популация по същия начин като STDEV.P. Той обаче включва и булеви стойности и текстови низове в изчислението, които се тълкуват, както следва:

• ИСТИНСКИ стойности се отчитат като 1
• FALSE стойностите се отчитат като 0
• Текстовите низове се броят като 0

Използваш го така:

1	= STDEVPA (C4: C12)

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

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

Можете да направите това много лесно с функциите на базата данни на Excel: DSTDEV (за проби) и DSTDEVP (за популации).

Тези функции ви позволяват да създадете таблица с критерии, в която можете да дефинирате всички филтри, от които се нуждаете. Функциите прилагат тези филтри зад кулисите, преди да върнат стандартното отклонение. По този начин не е нужно да докосвате автофилтър или да изваждате данни в отделен лист - DSTDEV и SDTDEVP могат да направят всичко това вместо вас.

Научете повече на главната страница за функциите Excel DSTDEV и DSTDEVP <>.

Функция СТАНДАРТНО ОТКАЗВАНЕ в Google Таблици

Функцията STANDARD DEVIATION работи абсолютно същото в Google Sheets, както и в Excel: