Microsoft Excel을 사용하여 Z- 점수를 계산하는 방법
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 % 성적이 더 인상적이지만, 2 급 선생님이 더 어려운 시험을 치르면 어떨까요? 각 클래스의 평균 점수와 각 클래스의 점수 표준 편차를 기반으로 각 학생의 점수에 대한 Z- 점수를 계산할 수 있습니다. 두 학생의 Z- 점수를 비교해 보면 87 % 점수를받은 학생이 나머지 학급에 비해 98 % 점수를받은 학생보다 더 잘한 것으로 나타났습니다.
필요한 첫 번째 통계 값은 '평균'이며 Excel의 "AVERAGE"함수는 해당 값을 계산합니다. 단순히 셀 범위의 모든 값을 더하고 그 합계를 숫자 값을 포함하는 셀 수로 나눕니다 (빈 셀은 무시 함).
필요한 다른 통계 값은 '표준 편차'이며 Excel에는 약간 다른 방식으로 표준 편차를 계산하는 두 가지 다른 기능이 있습니다.
이전 버전의 Excel에는 데이터를 모집단의 '표본'으로 처리하면서 표준 편차를 계산하는 "STDEV"함수 만있었습니다. Excel 2010은이를 표준 편차를 계산하는 두 가지 함수로 나눕니다.
- STDEV.S : 이 기능은 이전 "STDEV"기능과 동일합니다. 데이터를 모집단의 '표본'으로 취급하면서 표준 편차를 계산합니다. 인구의 표본은 연구 프로젝트를 위해 수집 된 특정 모기 나 충돌 안전 테스트에 사용 된 자동차와 같은 것일 수 있습니다.
- STDEV.P : 이 함수는 데이터를 전체 모집단으로 취급하면서 표준 편차를 계산합니다. 전체 인구는 지구상의 모든 모기 또는 특정 모델을 생산하는 모든 자동차와 같습니다.
선택하는 것은 데이터 세트를 기반으로합니다. 차이는 일반적으로 작지만 "STDEV.P"함수의 결과는 항상 동일한 데이터 세트에 대한 "STDEV.S"함수의 결과보다 작습니다. 데이터에 더 많은 변동성이 있다고 가정하는 것은보다 보수적 인 접근 방식입니다.
예를 살펴 보겠습니다.
이 예에서는 "AVERAGE", "STDEV.S"및 "STDEV.P"함수의 결과를 저장하기위한 두 개의 열 ( "Values"및 "Z-Score")과 3 개의 "도우미"셀이 있습니다. "Values"열에는 500을 중심으로하는 10 개의 난수가 포함되며 "Z-Score"열은 'helper'셀에 저장된 결과를 사용하여 Z-Score를 계산하는 곳입니다.
먼저 "AVERAGE"함수를 사용하여 값의 평균을 계산합니다. "AVERAGE"함수의 결과를 저장할 셀을 선택하십시오.
다음 수식을 입력 하고 Enter 키를 누르 거나 "수식"메뉴를 사용합니다.
= AVERAGE (E2 : E13)
"공식"메뉴를 통해 기능에 액세스하려면 "추가 기능"드롭 다운을 선택하고 "통계"옵션을 선택한 다음 "평균"을 클릭합니다.
함수 인수 창에서 "값"열의 모든 셀을 "숫자 1"필드의 입력으로 선택하십시오. “Number2”필드에 대해 걱정할 필요가 없습니다.
이제 "확인"을 누르십시오.
다음으로 "STDEV.S"또는 "STDEV.P"함수를 사용하여 값의 표준 편차를 계산해야합니다. 이 예에서는 "STDEV.S"로 시작하는 두 값을 모두 계산하는 방법을 보여줍니다. 결과가 저장 될 셀을 선택하십시오.
"STDEV.S"기능을 사용하여 표준 편차를 계산하려면이 공식을 입력하고 Enter를 누르십시오 (또는 "공식"메뉴를 통해 액세스하십시오).
= STDEV.S (E3 : E12)
"공식"메뉴를 통해 기능에 액세스하려면 "추가 기능"드롭 다운을 선택하고 "통계"옵션을 선택한 다음 약간 아래로 스크롤 한 다음 "STDEV.S"명령을 클릭합니다.
함수 인수 창에서 "값"열의 모든 셀을 "숫자 1"필드의 입력으로 선택하십시오. 여기에서도 "Number2"필드에 대해 걱정할 필요가 없습니다.
이제 "확인"을 누르십시오.
다음으로 "STDEV.P"함수를 사용하여 표준 편차를 계산합니다. 결과가 저장 될 셀을 선택하십시오.
"STDEV.P"기능을 사용하여 표준 편차를 계산하려면이 공식을 입력하고 Enter를 누르십시오 (또는 "공식"메뉴를 통해 액세스하십시오).
= STDEV.P (E3 : E12)
"공식"메뉴를 통해 함수에 액세스하려면 "추가 기능"드롭 다운을 선택하고 "통계"옵션을 선택하고 약간 아래로 스크롤 한 다음 "STDEV.P"공식을 클릭합니다.
함수 인수 창에서 "값"열의 모든 셀을 "숫자 1"필드의 입력으로 선택하십시오. 다시 말하지만 "Number2"필드에 대해 걱정할 필요가 없습니다.
이제 "확인"을 누르십시오.
이제 데이터의 평균과 표준 편차를 계산 했으므로 Z- 점수를 계산하는 데 필요한 모든 것이 있습니다. "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"> F4 키 를 계속 누르면 "G3" )
- 유형
)/ - 셀 H3 (또는 "STDEV.P"를 사용하는 경우 I3)을 선택하고 F4 를 눌러 두 개의 "$"문자를 추가하십시오.
- 엔터 키를 치시오
Z-Score는 첫 번째 값에 대해 계산되었습니다. 평균보다 낮은 0.15945 표준 편차입니다. 결과를 확인하려면 표준 편차에이 결과 (6.271629 * -0.15945)를 곱하고 결과가 값과 평균의 차이 (499-500)와 같은지 확인할 수 있습니다. 두 결과가 동일하므로 값이 합리적입니다.
나머지 값의 Z- 점수를 계산해 봅시다. 수식이 포함 된 셀로 시작하는 전체 'Z-Score'열을 강조 표시합니다.
Ctrl + D를 누르면 선택한 다른 모든 셀을 통해 맨 위 셀의 수식이 아래로 복사됩니다.
이제 수식이 모든 셀에 '채워져'있으며 각 셀은 항상 "$"문자로 인해 올바른 "AVERAGE"및 "STDEV.S"또는 "STDEV.P"셀을 참조합니다. 오류가 발생하면 돌아가서 입력 한 수식에 "$"문자가 포함되어 있는지 확인하십시오.
'도우미'셀을 사용하지 않고 Z- 점수 계산
도우미 셀은 "AVERAGE", "STDEV.S"및 "STDEV.P"함수의 결과를 저장하는 것과 같은 결과를 저장합니다. 유용 할 수 있지만 항상 필요한 것은 아닙니다. 대신 다음 일반화 된 공식을 사용하여 Z- 점수를 계산할 때 모두 건너 뛸 수 있습니다.
다음은 "STDEV.S"기능을 사용하는 것입니다.
= (값 -AVERAGE (값)) / STDEV.S (값)
그리고 "STEV.P"기능을 사용하는 것 :
= (값 -AVERAGE (값)) / STDEV.P (값)
함수에서 "값"에 대한 셀 범위를 입력 할 때 절대 참조 (F4를 사용하여 "$")를 추가하여 '채우기'시 다른 범위의 평균 또는 표준 편차를 계산하지 않도록하십시오. 모든 수식의 셀 수.
큰 데이터 세트가있는 경우에는 "AVERAGE"및 "STDEV.S"또는 "STDEV.P"함수의 결과를 매번 계산하지 않기 때문에 도우미 셀을 사용하는 것이 더 효율적일 수 있으므로 프로세서 리소스와 결과를 계산하는 데 걸리는 시간을 단축합니다.
또한 "$ G $ 3"은 "AVERAGE ($ E $ 3 : $ E $ 12)"보다 저장하는 데 더 적은 바이트와로드하는 데 더 적은 RAM을 사용합니다. 이는 표준 32 비트 버전의 Excel이 2GB RAM으로 제한되기 때문에 중요합니다 (64 비트 버전에는 사용할 수있는 RAM 크기에 대한 제한이 없음).
