Как да изчислим Z-резултат с помощта на Microsoft Excel

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

Какво представлява 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-оценка“) и три „помощни“ клетки за съхраняване на резултатите от функциите „СРЕДЕН“, „STDEV.S“ и „STDEV.P“. Колоната „Стойности“ съдържа десет произволни числа, центрирани около 500, а колоната „Z-Score“ е мястото, където ще изчислим Z-Score, като използваме резултатите, съхранени в „помощните“ клетки.

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

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

= СРЕДНА (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. Можем да използваме проста формула, която препраща към клетките, съдържащи резултатите от функциите “СРЕДНО” и “STDEV.S” или “STDEV.P”.

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

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

= (E3- $ G $ 3) / $ H $ 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-резултатът е изчислен за първата стойност. Това е 0,15945 стандартни отклонения под средната стойност. За да проверите резултатите, можете да умножите стандартното отклонение по този резултат (6.271629 * -0.15945) и да проверите дали резултатът е равен на разликата между стойността и средната стойност (499-500). И двата резултата са равни, така че стойността има смисъл.

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

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

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

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

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

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

= (Стойност-СРЕДНА (Стойности)) / STDEV.S (Стойности)

И един, който използва функцията “STEV.P”:

= (Стойност-СРЕДНА (Стойности)) / STDEV.P (Стойности)

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

Ако имате голям набор от данни, може да е по-ефективно да използвате помощни клетки, защото той не изчислява резултата от функциите „СРЕДНО“ и „STDEV.S“ или „STDEV.P“, спестявайки ресурси на процесора и ускоряване на времето, необходимо за изчисляване на резултатите.

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