З-Сцоре је статистичка вредност која вам говори колико је стандардних девијација одређена вредност од средње вредности целог скупа података. Можете да користите формуле АВЕРАГЕ и СТДЕВ.С или СТДЕВ.П да бисте израчунали средњу вредност и стандардну девијацију ваших података, а затим да користите те резултате да бисте одредили З-оцену сваке вредности.
Преглед садржаја
Шта је З-Сцоре и шта раде функције АВЕРАГЕ, СТДЕВ.С и СТДЕВ.П?
З-Сцоре је једноставан начин поређења вредности из два различита скупа података. Дефинише се као број стандардних девијација удаљених од средње вредности у којој се налази тачка података. Општа формула изгледа овако:
=(DataPoint-AVERAGE(DataSet))/STDEV(DataSet)
Ево примера који ће вам помоћи да разјаснимо. Рецимо да сте хтели да упоредите резултате тестова два ученика алгебре које су предавали различити наставници. Знате да је први ученик добио 95% на завршном испиту у једном разреду, а ученик у другом разреду 87%.
На први поглед оцена од 95% је импресивнија, али шта ако је учитељица другог разреда дала тежи испит? Можете израчунати З-скор сваког ученика на основу просечних резултата у сваком разреду и стандардне девијације резултата у сваком разреду. Упоређивање З-скора два ученика могло би открити да је ученик са 87% скором прошао боље у поређењу са остатком свог разреда него ученик са 98% скором у поређењу са остатком разреда.
Прва статистичка вредност која вам је потребна је ‘средња’ и Екцелова функција „ПРОСЕК“ израчунава ту вредност. Једноставно сабира све вредности у опсегу ћелија и дели тај збир бројем ћелија које садрже нумеричке вредности (занемарује празне ћелије).
Друга статистичка вредност која нам је потребна је ‘стандардна девијација’ и Екцел има две различите функције за израчунавање стандардне девијације на мало различите начине.
Претходне верзије Екцел-а имале су само функцију „СТДЕВ“, која израчунава стандардну девијацију док податке третира као „узорак“ популације. Екцел 2010 је то поделио на две функције које израчунавају стандардну девијацију:
СТДЕВ.С: Ова функција је идентична претходној функцији „СТДЕВ“. Он израчунава стандардну девијацију док третира податке као ‘узорак’ популације. Узорак популације може бити нешто попут одређених комараца прикупљених за истраживачки пројекат или аутомобила који су остављени по страни и коришћени за тестирање безбедности судара.
СТДЕВ.П: Ова функција израчунава стандардну девијацију док третира податке као целу популацију. Цела популација би била нешто као сви комарци на Земљи или сваки аутомобил у производњи одређеног модела.
Оно што изаберете је засновано на вашем скупу података. Разлика ће обично бити мала, али ће резултат функције „СТДЕВ.П“ увек бити мањи од резултата функције „СТДЕВ.С“ за исти скуп података. Конзервативнији је приступ претпоставити да постоји већа варијабилност у подацима.
Хајде да погледамо пример
За наш пример, имамо две колоне („Вредности“ и „З-Сцоре“) и три „помоћне“ ћелије за чување резултата функција „АВЕРАГЕ“, „СТДЕВ.С“ и „СТДЕВ.П“. Колона „Вредности“ садржи десет насумичних бројева са центром око 500, а колона „З-Сцоре“ је место где ћемо израчунати З-Сцоре користећи резултате ускладиштене у ћелијама „помоћника“.
Прво ћемо израчунати средњу вредност вредности користећи функцију „ПРОСЕК“. Изаберите ћелију у којој ћете сачувати резултат функције „ПРОСЕК“.
Унесите следећу формулу и притисните ентер -или користите мени „Формуле“.
=AVERAGE(E2:E13)
Да бисте приступили функцији преко менија „Формуле“, изаберите падајући мени „Још функција“, изаберите опцију „Статистички“, а затим кликните на „ПРОСЕК“.
У прозору Аргументи функције изаберите све ћелије у колони „Вредности“ као улаз за поље „Број1“. Не морате да бринете о пољу „Број2“.
Сада притисните „ОК“.
Затим треба да израчунамо стандардну девијацију вредности користећи функцију „СТДЕВ.С“ или „СТДЕВ.П“. У овом примеру ћемо вам показати како да израчунате обе вредности, почевши од „СТДЕВ.С.“ Изаберите ћелију у којој ће бити сачуван резултат.
Да бисте израчунали стандардну девијацију помоћу функције „СТДЕВ.С“, унесите ову формулу и притисните Ентер (или јој приступите преко менија „Формуле“).
=STDEV.S(E3:E12)
Да бисте приступили функцији преко менија „Формуле“, изаберите падајући мени „Више функција“, изаберите опцију „Статистички“, скролујте мало надоле, а затим кликните на команду „СТДЕВ.С“.
У прозору Аргументи функције изаберите све ћелије у колони „Вредности“ као улаз за поље „Број1“. Не морате да бринете ни о пољу „Број2“ овде.
Сада притисните „ОК“.
Затим ћемо израчунати стандардну девијацију користећи функцију “СТДЕВ.П”. Изаберите ћелију у којој ће бити сачуван резултат.
Да бисте израчунали стандардну девијацију помоћу функције „СТДЕВ.П“, унесите ову формулу и притисните Ентер (или јој приступите преко менија „Формуле“).
=СТДЕВ.П(Е3:Е12)
Да бисте приступили функцији преко менија „Формуле“, изаберите падајући мени „Више функција“, изаберите опцију „Статистички“, скролујте мало надоле, а затим кликните на формулу „СТДЕВ.П“.
У прозору Аргументи функције изаберите све ћелије у колони „Вредности“ као улаз за поље „Број1“. Опет, нећете морати да бринете о пољу „Број2“.
Сада притисните „ОК“.
Сада када смо израчунали средњу вредност и стандардну девијацију наших података, имамо све што нам је потребно да израчунамо З-сцоре. Можемо користити једноставну формулу која упућује на ћелије које садрже резултате функција „АВЕРАГЕ“ и „СТДЕВ.С“ или „СТДЕВ.П“.
Изаберите прву ћелију у колони „З-резултат“. Користићемо резултат функције „СТДЕВ.С“ за овај пример, али можете користити и резултат из „СТДЕВ.П“.
Унесите следећу формулу и притисните Ентер:
=(E3-$G$3)/$H$3
Алтернативно, можете користити следеће кораке да унесете формулу уместо да куцате:
Кликните на ћелију Ф3 и откуцајте =(
Изаберите ћелију Е3. (Можете једном притиснути тастер са стрелицом лево или користити миш)
Унесите знак минус –
Изаберите ћелију Г3, а затим притисните Ф4 да додате знакове „$“ да бисте направили „апсолутну“ референцу на ћелију (она ће се кретати кроз „Г3“ > „$Г$3″ > „Г$3″ > „$Г3″ > „Г3“ ” ако наставите да притискате Ф4)
Тип )/
Изаберите ћелију Х3 (или И3 ако користите „СТДЕВ.П“) и притисните Ф4 да додате два знака „$“.
притисните ентер
З-сцоре је израчунат за прву вредност. То је 0,15945 стандардних девијација испод средње вредности. Да бисте проверили резултате, можете помножити стандардну девијацију овим резултатом (6,271629 * -0,15945) и проверити да ли је резултат једнак разлици између вредности и средње вредности (499-500). Оба резултата су једнака, тако да вредност има смисла.
Хајде да израчунамо З-резове осталих вредности. Означите целу колону ‘З-Сцоре’ почевши од ћелије која садржи формулу.
Притисните Цтрл+Д, који копира формулу у горњој ћелији надоле кроз све остале изабране ћелије.
Сада је формула ‘попуњена’ у све ћелије и свака ће увек упућивати на тачне ћелије „ПРОСЕК“ и „СТДЕВ.С“ или „СТДЕВ.П“ због знакова „$“. Ако добијете грешке, вратите се и уверите се да су знакови „$“ укључени у формулу коју сте унели.
Израчунавање З-скора без употребе ћелија „помоћника“.
Помоћне ћелије чувају резултат, попут оних у којима се чувају резултати функција „АВЕРАГЕ“, „СТДЕВ.С“ и „СТДЕВ.П“. Они могу бити корисни, али нису увек неопходни. Можете их потпуно прескочити када израчунавате З-Сцоре користећи следеће генерализоване формуле.
Ево једног који користи функцију „СТДЕВ.С“:
=(Value-AVERAGE(Values))/STDEV.S(Values)
И један који користи функцију „СТЕВ.П“:
=(Value-AVERAGE(Values))/STDEV.P(Values)
Када уносите опсеге ћелија за „Вредности“ у функцијама, обавезно додајте апсолутне референце („$“ помоћу Ф4) тако да када „попуните“ не бисте израчунали просек или стандардну девијацију другог опсега ћелија у свакој формули.
Ако имате велики скуп података, можда би било ефикасније да користите помоћне ћелије јер не израчунава сваки пут резултат функција „ПРОСЕК“ и „СТДЕВ.С“ или „СТДЕВ.П“, штедећи ресурсе процесора и убрзавање времена потребног за израчунавање резултата.
Такође, „$Г$3“ захтева мање бајтова за складиштење и мање РАМ-а за учитавање него „ПРОСЕЧНО ($Е$3:$Е$12).“. Ово је важно јер је стандардна 32-битна верзија Екцел-а ограничена на 2 ГБ РАМ-а (64-битна верзија нема никаква ограничења колико РАМ-а може да се користи).