Како израчунати З-оцену користећи Мицрософт Екцел

З-оцена је статистичка вредност која вам говори колико стандардних девијација одређена вредност се дешава из средње вредности целог скупа података. Можете да користите формуле АВЕРАГЕ и СТДЕВ.С или СТДЕВ.П да бисте израчунали средњу вредност и стандардну девијацију ваших података, а затим употребите те резултате да бисте одредили З-вредност сваке вредности.

Шта је З-Сцоре и шта раде АВЕРАГЕ, СТДЕВ.С и СТДЕВ.П функције?

З-оцена је једноставан начин да се упореде вредности из два различита скупа података. Дефинише се као број стандардних девијација далеко од средње тачке података. Општа формула изгледа овако:

= (ДатаПоинт-АВЕРАГЕ (ДатаСет)) / СТДЕВ (ДатаСет)

Ево примера који ће вам помоћи да разјасните. Рецимо да сте желели да упоредите резултате теста два ученика алгебре које су предавали различити наставници. Знате да је први ученик добио 95% на завршном испиту у једном разреду, а студент у другој класи освојио 87%.

На први поглед, оцјена од 95% је импресивнија, али шта ако је наставник другог разреда дао тежи испит? Можете израчунати З-скор бодова сваког ученика на основу просјечних резултата у сваком разреду и стандардне девијације резултата у сваком разреду. Упоређивање З-резултата два ученика могло би да открије да је ученик са 87% резултата постигао боље резултате у односу на остатак своје класе него што је ученик са 98% резултата урадио у поређењу са остатком своје класе..

Прва статистичка вриједност која вам је потребна је 'меан', а Екцелова “АВЕРАГЕ” функција израчунава ту вриједност. Једноставно додаје све вредности у опсегу ћелија и дели ту суму са бројем ћелија које садрже нумеричке вредности (игнорише празне ћелије).

Друга статистичка вредност која нам је потребна је "стандардна девијација", а Екцел има две различите функције за израчунавање стандардне девијације на мало другачији начин.

Претходне верзије Екцела имале су само “СТДЕВ” функцију, која израчунава стандардну девијацију, док податке третира као 'узорак' популације. Екцел 2010 је то разбио у две функције које израчунавају стандардну девијацију:

• СТДЕВ.С: Ова функција је идентична претходној функцији "СТДЕВ". Она израчунава стандардну девијацију док третира податке као 'узорак' популације. Узорак популације може бити нешто попут одређених комараца прикупљених за истраживачки пројекат или возила која су издвојена и кориштена за испитивање сигурности у судару.
• СТДЕВ.П: Ова функција израчунава стандардну девијацију док третира податке као цијелу популацију. Читава популација би била нешто попут свих комараца на Земљи или сваког аутомобила у производном циклусу одређеног модела.

Који сте изабрали базира се на вашем скупу података. Разлика је обично мала, али резултат функције “СТДЕВ.П” ће увијек бити мањи од резултата функције “СТДЕВ.С” за исти скуп података. Конзервативнији је приступ претпоставци да постоји већа варијабилност података.

Погледајмо примјер

За наш пример, имамо две колоне („Вредности“ и „З-Сцоре“) и три „помоћне“ ћелије за чување резултата „АВЕРАГЕ“, „СТДЕВ.С“ и „СТДЕВ.П“ функција. Ступац "Вредности" садржи десет случајних бројева са центром око 500, а колона "З-Сцоре" где ћемо израчунати З-оцену користећи резултате похрањене у ћелијама 'помоћника'.

Прво ћемо израчунати средњу вредност помоћу функције "АВЕРАГЕ". Изаберите ћелију у којој ћете похранити резултат функције “АВЕРАГЕ”.

Укуцајте следећу формулу и притисните ентер -или - користите мени "Формуле".

= АВЕРАГЕ (Е2: Е13)

Да бисте приступили функцији преко менија „Формуле“, изаберите падајући мени „Више функција“, изаберите опцију „Статистички“, а затим кликните на „АВЕРАГЕ“.

У прозору Аргументи функција изаберите све ћелије у колони „Вредности“ као улаз за поље „Број1“. Не морате бринути о пољу "Нумбер2".

Сада притисните "ОК".

Затим треба да израчунамо стандардну девијацију вредности користећи функцију "СТДЕВ.С" или "СТДЕВ.П". У овом примеру, показаћемо вам како да израчунате обе вредности, почевши са „СТДЕВ.С.“ Изаберите ћелију у којој ће резултат бити сачуван.

Да бисте израчунали стандардну девијацију помоћу функције "СТДЕВ.С", упишите ову формулу и притисните Ентер (или јој приступите кроз мени "Формуле").

= СТДЕВ.С (Е3: Е12)

Да бисте приступили функцији преко менија „Формуле“, изаберите падајући мени „Више функција“, изаберите опцију „Статистички“, померите се надоле, а затим кликните на команду „СТДЕВ.С“.

У прозору Аргументи функција изаберите све ћелије у колони „Вредности“ као улаз за поље „Број1“. Не морате бринути ни за поље "Нумбер2".

Сада притисните "ОК".

Затим ћемо израчунати стандардну девијацију помоћу функције "СТДЕВ.П". Изаберите ћелију у којој ће резултат бити сачуван.

Да бисте израчунали стандардну девијацију помоћу функције "СТДЕВ.П", упишите ову формулу и притисните Ентер (или јој приступите кроз мени "Формуле").

= СТДЕВ.П (Е3: Е12)

Да бисте приступили функцији преко менија „Формуле“, изаберите падајући мени „Више функција“, изаберите опцију „Статистички“, померите се надоле, а затим кликните на формулу „СТДЕВ.П“..

У прозору Аргументи функција изаберите све ћелије у колони „Вредности“ као улаз за поље „Број1“. Опет, нећете морати да бринете о пољу "Нумбер2".

Сада притисните "ОК".

Сада када смо израчунали средњу и стандардну девијацију наших података, имамо све што нам је потребно да израчунамо З-оцену. Можемо да користимо једноставну формулу која упућује на ћелије које садрже резултате функција „АВЕРАГЕ“ и „СТДЕВ.С“ или „СТДЕВ.П“..

Изаберите прву ћелију у колони „З-Сцоре“. Користићемо резултат функције "СТДЕВ.С" за овај пример, али такође можете користити резултат из "СТДЕВ.П."

Укуцајте следећу формулу и притисните Ентер:

= (Е3- $ Г $ 3) / $ Х $ 3

Алтернативно, можете да користите следеће кораке да бисте унели формулу уместо да куцате:

1. Кликните на ћелију Ф3 и откуцајте = (
2. Изаберите ћелију Е3. (Можете да притиснете тастер лева стрелица-тастер једном или користите миша)
3. Упишите знак минус -
4. Изаберите ћелију Г3, а затим притисните Ф4 да бисте додали знакове „$“ да бисте направили „апсолутну“ референцу на ћелију (она ће кружити кроз „Г3“>$Г$3 ">" Г$3 ">"$Г3 ">" Г3 "ако наставите да притискате Ф4)
5. Тип ) /
6. Изаберите ћелију Х3 (или И3 ако користите "СТДЕВ.П") и притисните Ф4 да бисте додали два "$" знака.
7. притисните ентер

З-оцена је израчуната за прву вредност. То је 0.15945 стандардних девијација испод средње вредности. Да бисте проверили резултате, можете помножити стандардну девијацију са овим резултатом (6.271629 * -0.15945) и проверити да ли је резултат једнак разлици између вредности и средње вредности (499-500). Оба резултата су једнака, тако да вредност има смисла.

Хајде да израчунамо З-оцјене осталих вредности. Означите целу колону 'З-Сцоре' почевши од ћелије која садржи формулу.

Притисните Цтрл + Д, који копира формулу у горњој ћелији кроз све остале изабране ћелије.

Сада је формула 'попуњена' на све ћелије, и свака од њих ће увек референцирати исправне “АВЕРАГЕ” и “СТДЕВ.С” или “СТДЕВ.П” ћелије због знакова “$”. Ако добијете грешке, вратите се и уверите се да су знакови "$" укључени у формулу коју сте унијели.

Израчунавање З-скора без употребе 'Хелпер' ћелија

Хелпер ћелије похрањују резултат, попут оних које похрањују резултате "АВЕРАГЕ", "СТДЕВ.С" и "СТДЕВ.П" функција. Оне могу бити корисне, али нису увијек потребне. Можете их потпуно прескочити када израчунавате З-оцену користећи следеће генерализоване формуле.

Ево коришћења функције "СТДЕВ.С":

= (Вредност-АВЕРАГЕ (вредности)) / СТДЕВ.С (вредности)

И један помоћу функције "СТЕВ.П":

= (Вредност-АВЕРАГЕ (вредности)) / СТДЕВ.П (вредности)

Када уносите опсеге ћелија за "Вредности" у функцијама, обавезно додајте апсолутне референце ("$" користећи Ф4) тако да када "попуните" не израчунавате просечно или стандардно одступање различитог опсега ћелија у свакој формули.

Ако имате велики скуп података, можда је ефикасније користити помоћне ћелије зато што не израчунава резултат функција "АВЕРАГЕ" и "СТДЕВ.С" или "СТДЕВ.П", штедећи процесорске ресурсе и убрзање времена потребног за израчунавање резултата.

Такође, “$ Г $ 3” захтева мање бајтова за складиштење и мање РАМ-а за учитавање од “АВЕРАГЕ ($ Е $ 3: $ Е $ 12).”. Ово је важно зато што је стандардна 32-битна верзија програма Екцел ограничена на 2 ГБ РАМ-а (64-битна верзија нема ограничења у погледу тога колико РАМ-а може да се користи).