З-оцена је статистичка вредност која вам говориколико стандардних одступања се деси од средње вриједности читавог скупа података. Можете користити АВЕРАГЕ и СТДЕВ.С или СТДЕВ.П формуле за израчунавање средњег и стандардног одступања података, а затим помоћу тих резултата одредити З-оцену сваке вредности.
Шта је З-оцена и шта раде функције АВЕРАГЕ, СТДЕВ.С и СТДЕВ.П?
З-оцена је једноставан начин поређења вредностииз два различита скупа података. Дефинише се као број стандардних одступања у односу на средњу тачку података. Општа формула изгледа овако:
=(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-битна верзија нема ограничења у томе колико РАМ-а се може користити).
