Z-Score е статистическа стойност, която ви казва колко стандартни отклонения е дадена стойност от средната стойност на целия набор от данни. Можете да използвате формулите AVERAGE и STDEV.S или STDEV.P, за да изчислите средната стойност и стандартното отклонение на вашите данни и след това да използвате тези резултати, за да определите Z-резултата на всяка стойност.
Съдържание
Какво е Z-Score и какво правят функциите AVERAGE, STDEV.S и STDEV.P?
Z-Score е прост начин за сравняване на стойности от два различни набора от данни. Дефинира се като броя на стандартните отклонения от средната стойност на точката от данни. Общата формула изглежда така:
=(DataPoint-AVERAGE(DataSet))/STDEV(DataSet)
Ето един пример за изясняване. Да речем, че искате да сравните резултатите от теста на двама студенти по алгебра, преподавани от различни учители. Знаете, че първият ученик получи 95% на последния изпит в единия клас, а ученикът в другия клас получи 87%.
На пръв поглед оценката от 95% е по-впечатляваща, но какво ще стане, ако учителят от втори клас даде по-труден изпит? Можете да изчислите Z-резултата на всеки ученик въз основа на средните резултати във всеки клас и стандартното отклонение на резултатите във всеки клас. Сравняването на Z-резултатите на двамата студенти може да разкрие, че ученикът с резултат 87% се е справил по-добре в сравнение с останалата част от своя клас, отколкото ученикът с резултат 98% в сравнение с останалата част от класа.
Първата статистическа стойност, от която се нуждаете, е „средната“ и функцията „СРЕДНО“ на 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-резултата, като използваме резултатите, съхранени в „помощните“ клетки.
Първо, ще изчислим средната стойност на стойностите, използвайки функцията “СРЕДНА”. Изберете клетката, в която ще съхранявате резултата от функцията „СРЕДНО“.
Въведете следната формула и натиснете enter -или използвайте менюто „Формули“.
=AVERAGE(E2: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)/$H$3
Като алтернатива можете да използвате следните стъпки, за да въведете формулата, вместо да пишете:
Щракнете върху клетка F3 и въведете =(
Изберете клетка E3. (Можете да натиснете веднъж лявата стрелка или да използвате мишката)
Въведете знака минус –
Изберете клетка G3, след което натиснете F4, за да добавите символите „$“, за да направите „абсолютна“ препратка към клетката (тя ще премине през „G3“ > „$G$3″ > „G$3″ > „$G3“ > „G3“ ” ако продължите да натискате F4)
Тип )/
Изберете клетка H3 (или I3, ако използвате „STDEV.P“) и натиснете F4, за да добавите двата знака „$“.
Натиснете Enter
Z-резултатът е изчислен за първата стойност. Това е 0,15945 стандартни отклонения под средното. За да проверите резултатите, можете да умножите стандартното отклонение по този резултат (6,271629 * -0,15945) и да проверите дали резултатът е равен на разликата между стойността и средната стойност (499-500). И двата резултата са равни, така че стойността има смисъл.
Нека изчислим Z-резултатите на останалите стойности. Маркирайте цялата колона „Z-Score“, започвайки с клетката, съдържаща формулата.
Натиснете Ctrl+D, което копира формулата в горната клетка надолу през всички останали избрани клетки.
Сега формулата е „попълнена“ за всички клетки и всяка винаги ще препраща към правилните клетки „СРЕДНО“ и „STDEV.S“ или „STDEV.P“ поради знаците „$“. Ако получите грешки, върнете се и се уверете, че символите „$“ са включени във формулата, която сте въвели.
Изчисляване на Z-резултата без използване на „помощни“ клетки
Помощните клетки съхраняват резултат, като тези, съхраняващи резултатите от функциите „AVERAGE“, „STDEV.S“ и „STDEV.P“. Те могат да бъдат полезни, но не винаги са необходими. Можете да ги пропуснете напълно, когато изчислявате Z-Score, като вместо това използвате следните обобщени формули.
Ето един, използващ функцията “STDEV.S”:
=(Value-AVERAGE(Values))/STDEV.S(Values)
И един, използващ функцията “STEV.P”:
=(Value-AVERAGE(Values))/STDEV.P(Values)
Когато въвеждате диапазоните на клетките за „Стойности“ във функциите, не забравяйте да добавите абсолютни препратки („$“ с помощта на F4), така че когато „попълвате“, да не изчислявате средното или стандартното отклонение на различен диапазон от клетки във всяка формула.
Ако имате голям набор от данни, може да е по-ефективно да използвате помощни клетки, тъй като не изчислява всеки път резултата от функциите „AVERAGE“ и „STDEV.S“ или „STDEV.P“, спестявайки ресурси на процесора и ускоряване на времето, необходимо за изчисляване на резултатите.
Освен това „$G$3“ отнема по-малко байтове за съхранение и по-малко RAM за зареждане от „СРЕДНО($E$3:$E$12).“. Това е важно, тъй като стандартната 32-битова версия на Excel е ограничена до 2GB RAM (64-битовата версия няма никакви ограничения за това колко RAM може да се използва).