Как да се изчисли Z-резултат, използвайки Microsoft Excel

Z-Score е статистическа стойност, която ви показва колко стандартни отклонения дадена стойност е от средната стойност на целия набор от данни. Можете да използвате формули AVERAGE и STDEV.S или STDEV.P, за да изчислите средната стойност и стандартното отклонение на вашите данни и след това да използвате тези резултати, за да определите Z-резултата за всяка стойност.

Какво е Z-Score и какво правят функциите AVERAGE, STDEV.S и STDEV.P?

Z-Score е прост начин за сравняване на стойности от два различни набора от данни. Тя се дефинира като броя на стандартните отклонения, далеч от средната точка на данните. Общата формула изглежда така:

= (Точка от данните-СРЕДНО (DataSet)) / СтОткл (DataSet)

Ето един пример, който да помогне за изясняване. Да речем, че искате да сравните резултатите от тестовете на двама студенти по алгебра, преподавани от различни учители. Знаете, че първият ученик е получил 95% от крайния изпит в един клас, а ученикът в другия клас е на 87%.

На пръв поглед, 95% клас е по-впечатляващ, но какво, ако учителят от втория клас е дал по-труден изпит? Можете да изчислите Z-резултата от оценката на всеки ученик въз основа на средните оценки във всеки клас и стандартното отклонение на оценките във всеки клас. Сравняването на Z-точките на двамата ученици може да разкрие, че ученикът с 87% резултат е по-добър в сравнение с останалата част от класа си, отколкото студентът с 98% резултат е направил в сравнение с останалата част от класа си.

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

Другата статистическа стойност, от която се нуждаем, е "стандартното отклонение" и Excel има две различни функции за изчисляване на стандартното отклонение по малко по-различен начин.

Предишните версии на Excel имаха само функцията „STDEV“, която изчислява стандартното отклонение, като третира данните като „извадка“ от населението. Excel 2010 счупи това в две функции, които изчисляват стандартното отклонение:

• STDEV.S: Тази функция е идентична с предишната функция "STDEV". Тя изчислява стандартното отклонение, като третира данните като „извадка“ от населението. Извадка от населението може да е нещо като специфичните комари, събрани за изследователски проект или коли, които са били оставени настрана и използвани за изпитване за безопасност при сблъсък..
• STDEV.P: Тази функция изчислява стандартното отклонение, докато третира данните като цялото население. Цялото население ще бъде нещо като всички комари на Земята или всеки автомобил в производствен цикъл на конкретен модел.

Което избирате се основава на набора от данни. Разликата обикновено е малка, но резултатът от функцията “STDEV.P” винаги ще бъде по-малък от резултата от функцията “STDEV.S” за същия набор от данни. Това е по-консервативен подход, за да се предположи, че има повече вариабилност в данните.

Да разгледаме един пример

За нашия пример имаме две колони ("Стойности" и "Z-Score") и три "помощни" клетки за съхраняване на резултатите от функциите "AVERAGE", "STDEV.S" и "STDEV.P". Колоната „Стойности“ съдържа десет случайни числа, центрирани около 500, а колоната „Z-Score“ е мястото, където ще изчислим Z-Score, използвайки резултатите, съхранени в клетки „помощник“..

Първо ще изчислим средната стойност на стойностите, като използваме функцията “AVERAGE”. Изберете клетката, в която ще се съхранява резултатът от функцията “AVERAGE”.

Въведете следната формула и натиснете въведете -или - използвайте менюто „Формули“.

= Средна (Е2: E13)

За да осъществите достъп до функцията чрез менюто „Формули“, изберете падащото меню „Още функции“, изберете опцията „Статистически“ и след това кликнете върху „СРЕДНО“.

В прозореца Аргументи на функциите изберете всички клетки в колоната „Стойности“ като вход за поле „Номер 1“. Не е нужно да се притеснявате за полето "Number2".

Сега натиснете „OK“.

След това трябва да изчислим стандартното отклонение на стойностите, като използваме функцията “STDEV.S” или “STDEV.P”. В този пример ще ви покажем как да изчислявате и двете стойности, като започнете с „STDEV.S“. Изберете клетката, в която ще се съхранява резултата..

За да изчислите стандартното отклонение, като използвате функцията „STDEV.S“, въведете тази формула и натиснете Enter (или влезте в менюто „Формули“).

= STDEV.S (E3: E12)

За да осъществите достъп до функцията чрез менюто „Формули“, изберете падащото меню „Още функции“, изберете опцията „Статистически“, превъртете надолу малко и след това кликнете върху командата „STDEV.S“..

В прозореца Аргументи на функциите изберете всички клетки в колоната „Стойности“ като вход за поле „Номер 1“. Не е нужно да се притеснявате и за полето „Number2“ тук.

Сега натиснете „OK“.

След това ще изчислим стандартното отклонение, използвайки функцията “STDEV.P”. Изберете клетката, в която ще се съхранява резултатът.

За да изчислите стандартното отклонение, като използвате функцията “STDEV.P”, въведете тази формула и натиснете Enter (или я влезте в менюто “Формули”)..

= STDEV.P (E3: E12)

За достъп до функцията чрез менюто „Формули“ изберете падащото меню „Още функции“, изберете опцията „Статистически“, превъртете надолу малко и след това щракнете върху формулата „STDEV.P“..

В прозореца Аргументи на функциите изберете всички клетки в колоната „Стойности“ като вход за поле „Номер 1“. Отново, няма нужда да се притеснявате за полето „Number2“.

Сега натиснете „OK“.

Сега, след като сме изчислили средното и стандартното отклонение на нашите данни, имаме всичко необходимо, за да изчислим Z-Score. Можем да използваме проста формула, която се позовава на клетките, съдържащи резултатите от функциите „AVERAGE“ и „STDEV.S“ или „STDEV.P“..

Изберете първата клетка в колоната „Z-Score“. Ще използваме резултата от функцията „STDEV.S“ за този пример, но можете да използвате и резултата от „STDEV.P“.

Въведете следната формула и натиснете Enter:

= (E3- $ G $ 3) / $ Н $ 3

Освен това можете да използвате следните стъпки, за да въведете формулата, вместо да въвеждате:

1. Щракнете върху клетка F3 и въведете = (
2. Изберете клетка E3. (Можете да натиснете бутона лява стрелка ключ веднъж или с мишката
3. Въведете знака минус -
4. Изберете клетка G3, след това натиснете F4 да добавите символите „$“, за да направите „абсолютна“ препратка към клетката (тя ще премине през „G3“> „“$G$3 ">" G$3 ">"$G3 ">" G3 ", ако продължите да натискате F4)
5. Тип ) /
6. Изберете клетка H3 (или I3, ако използвате “STDEV.P”) и натиснете F4 , за да добавите двата символа "$".
7. Натиснете Enter

Z-Score е изчислен за първата стойност. Това е 0.15945 стандартни отклонения под средната стойност. За да проверите резултатите, можете да умножите стандартното отклонение с този резултат (6.271629 * -0.15945) и да проверите дали резултатът е равен на разликата между стойността и средната стойност (499-500). И двата резултата са еднакви, така че стойността има смисъл.

Нека изчислим Z-точките на останалите стойности. Маркирайте цялата колона „Z-Score“, започвайки с клетката, съдържаща формулата.

Натиснете Ctrl + D, който копира формулата в горната клетка надолу през всички останали избрани клетки.

Сега формулата е „запълнена“ до всички клетки и всеки от тях винаги ще се позовава на правилните клетки „AVERAGE“ и „STDEV.S“ или „STDEV.P“ поради символите „$“. Ако получите грешки, върнете се и се уверете, че символите "$" са включени във въведената от вас формула.

Изчисляване на Z-резултата без използване на 'Helper' клетки

Хелперните клетки съхраняват резултат, подобен на този, който съхранява резултатите от функциите „AVERAGE“, „STDEV.S“ и „STDEV.P“. Те могат да бъдат полезни, но не винаги са необходими. Можете да ги пропуснете напълно, когато изчислявате Z-Score, като използвате вместо това следните обобщени формули.

Ето един, използващ функцията „STDEV.S“:

= (Средна стойност (стойности)) / STDEV.S (стойности)

И с помощта на функцията "STEV.P":

= (Средна стойност (стойности)) / STDEV.P (стойности)

Когато въвеждате диапазоните на клетките за „Стойности“ в функциите, не забравяйте да добавите абсолютни препратки („$“, използвайки F4), така че когато „попълните“, не изчислявате средното или стандартното отклонение на различен диапазон на клетки във всяка формула.

Ако имате голям набор от данни, може да е по-ефективно да използвате помощни клетки, защото не изчислява резултата от функциите „AVERAGE“ и „STDEV.S“ или „STDEV.P“ всеки път, като спестява ресурси на процесора и ускоряване на времето, необходимо за изчисляване на резултатите.

Също така, "$ G $ 3" ​​отнема по-малко байта за съхранение и по-малко RAM за зареждане от "AVERAGE ($ E $ 3: $ E $ 12).". Това е важно, защото стандартната 32-битова версия на Excel е ограничена до 2GB RAM (64-битовата версия няма ограничения за това колко RAM може да се използва).