Microsoft Excel을 사용하여 Z- 점수를 계산하는 방법

Z- 점수는 전체 데이터 세트의 평균으로부터 특정 값이 얼마나 많이 발생 하는지를 나타내는 통계 값입니다. AVERAGE 및 STDEV.S 또는 STDEV.P 수식을 사용하여 데이터의 평균 및 표준 편차를 계산 한 다음이 결과를 사용하여 각 값의 Z- 점수를 결정할 수 있습니다.

Z-score 란 무엇이며 AVERAGE, STDEV.S 및 STDEV.P 함수는 무엇을합니까??

Z 점수는 두 개의 다른 데이터 세트의 값을 비교하는 간단한 방법입니다. 이것은 데이터 포인트가 의미하는 표준 편차의 수로 정의됩니다. 일반 수식은 다음과 같습니다.

= (DataPoint-AVERAGE (DataSet)) / STDEV (DataSet)

다음은 명확히하는 데 도움이되는 예제입니다. 다른 교사가 가르치는 두 대수 학생들의 시험 결과를 비교하기를 원한다고 가정 해보십시오. 첫 번째 학생이 한 학급에서 최종 시험에서 95 %를 받았고 다른 클래스의 학생이 87 %.

언뜻 보면 95 % 급이 더 인상적이지만, 2 학년 교사가 더 어려운 시험을 치른다면 어떨까요? 각 반의 평균 점수와 각 반의 점수의 표준 편차에 따라 각 학생의 점수의 Z- 점수를 계산할 수 있습니다. 두 학생의 Z 점수를 비교하면 87 % 점수를받은 학생이 나머지 반과 비교했을 때 98 % 점수를받은 학생이 나머지 반과 비교했을 때 점수가 더 좋은 점수를 얻었음을 알 수 있습니다.

필요한 첫 번째 통계 값은 'mean'이며 Excel의 'AVERAGE'함수는 해당 값을 계산합니다. 단순히 셀 범위의 모든 값을 더하고 그 합을 숫자 값을 포함하는 셀 수로 나눕니다 (빈 셀을 무시함).

우리가 필요로하는 다른 통계적 값은 '표준 편차'이며 Excel에는 표준 편차를 약간 다른 방식으로 계산하는 두 가지 다른 함수가 있습니다.

이전 버전의 Excel에는 데이터를 모집단의 '샘플'로 취급하면서 표준 편차를 계산하는 "STDEV"기능 만있었습니다. Excel 2010에서는이를 표준 편차를 계산하는 두 가지 함수로 나눴습니다.

STDEV.S : 이 기능은 이전의 "STDEV"기능과 동일합니다. 모집단의 '표본'으로 데이터를 처리하면서 표준 편차를 계산합니다. 모집단의 샘플은 조사 프로젝트를 위해 수집 된 특정 모기 나 충돌 안전 테스트에 사용 된 자동차와 같을 수 있습니다.
STDEV.P : 이 함수는 데이터를 전체 모집단으로 취급하면서 표준 편차를 계산합니다. 전체 인구는 지구상의 모든 모기 또는 특정 모델의 생산 실행중인 모든 모기와 유사합니다.

선택한 것은 데이터 세트를 기반으로합니다. 차이는 일반적으로 작지만 "STDEV.P"기능의 결과는 항상 동일한 데이터 세트에 대한 "STDEV.S"기능의 결과보다 작습니다. 데이터에 더 많은 변동성이 있다고 가정하는 것이 더 보수적 인 방법입니다.

예제를 보자.

이 예에서는 "AVERAGE", "STDEV.S"및 "STDEV.P"함수의 결과를 저장하기위한 두 개의 열 ( "Values"및 "Z-Score")과 세 개의 "도우미"셀이 있습니다. "값"열은 약 500 개의 중심에있는 10 개의 난수를 포함하며 "Z- 점수"열은 '도우미'셀에 저장된 결과를 사용하여 Z- 점수를 계산하는 곳입니다.

먼저 "AVERAGE"기능을 사용하여 값의 평균을 계산합니다. "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- 점수를 계산하는 데 필요한 모든 것이 있습니다. "AVERAGE"및 "STDEV.S"또는 "STDEV.P"함수의 결과를 포함하는 셀을 참조하는 간단한 수식을 사용할 수 있습니다.

"Z-Score"열의 첫 번째 셀을 선택하십시오. 이 예제에서는 "STDEV.S"함수의 결과를 사용하지만 "STDEV.P"의 결과를 사용할 수도 있습니다.

다음 수식을 입력하고 Enter 키를 누릅니다.

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

또는 입력하는 대신 다음 단계를 수행하여 수식을 입력 할 수 있습니다.

셀 F3을 클릭하고 입력하십시오. = (
E3 셀을 선택하십시오. (당신은 왼쪽 화살표 키 한 번 또는 마우스 사용)
마이너스 기호 입력 -
셀 G3을 선택하고를 누릅니다. F4 "$"문자를 추가하여 셀에 대한 '절대'참조를 만듭니다 ( "G3"> "$지$3 ">"G$3 ">"$G3 ">"G3 "을 계속 누르면 F4)
유형 ) /
셀 H3 (또는 "STDEV.P"를 사용하는 경우 I3)을 선택하고를 누릅니다. F4 두 개의 "$"문자를 추가하려면.
엔터 키를 치시오

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"함수를 사용하는 예제입니다.

= (값 - 평균 (값)) / STDEV.S (값)

그리고 "STEV.P"기능을 사용하는 경우 :

= (값 - 평균 (값)) / STDEV.P (값)

함수의 "값"에 대한 셀 범위를 입력 할 때는 절대 참조 (F4를 사용하여 "$")를 추가하여 채우기가 완료되면 다른 범위의 평균 또는 표준 편차를 계산하지 않도록하십시오 모든 수식의 세포 수.

대용량 데이터 세트가있는 경우 "AVERAGE"및 "STDEV.S"또는 "STDEV.P"함수의 결과를 매번 계산하지 않고 프로세서 리소스를 절약 할 수 있으므로 헬퍼 셀을 사용하는 것이 더 효율적일 수 있습니다. 결과를 계산하는 데 걸리는 시간 단축.

또한 "$ G $ 3"은 "AVERAGE ($ E $ 3 : $ E $ 12)"보다 적은 바이트의 저장 공간과로드 할 RAM이 필요합니다. " Excel의 표준 32 비트 버전은 2GB의 RAM으로 제한되어 있기 때문에 중요합니다 (64 비트 버전에는 사용 가능한 RAM 용량에 대한 제한이 없습니다).