Z-점수는 특정 값이 전체 데이터 세트의 평균에서 얼마나 많은 표준 편차가 발생하는지 알려주는 통계 값입니다. AVERAGE 및 STDEV.S 또는 STDEV.P 공식을 사용하여 데이터의 평균 및 표준 편차를 계산한 다음 해당 결과를 사용하여 각 값의 Z-점수를 결정할 수 있습니다.
목차
Z-점수란 무엇이며 AVERAGE, STDEV.S 및 STDEV.P 기능은 무엇을 합니까?
Z-점수는 서로 다른 두 데이터 세트의 값을 비교하는 간단한 방법입니다. 데이터 포인트가 있는 평균에서 표준 편차의 수로 정의됩니다. 일반 공식은 다음과 같습니다.
=(DataPoint-AVERAGE(DataSet))/STDEV(DataSet)
다음은 명확히 하는 데 도움이 되는 예입니다. 다른 교사가 가르치는 두 대수학 학생의 시험 결과를 비교하고 싶다고 가정해 보겠습니다. 첫 번째 학생은 한 수업의 기말고사에서 95%를 얻었고 다른 수업의 학생은 87%를 받았습니다.
얼핏 보면 95% 성적이 더 인상적이지만, 2학년 담임선생님이 더 어려운 시험을 봤다면? 각 클래스의 평균 점수와 각 클래스의 점수 표준편차를 기반으로 각 학생의 점수에 대한 Z-Score를 계산할 수 있습니다. 두 학생의 Z-점수를 비교하면 87% 점수를 받은 학생이 나머지 수업과 비교하여 98% 점수를 받은 학생보다 나머지 수업에 비해 더 나은 결과를 얻었음을 알 수 있습니다.
필요한 첫 번째 통계 값은 ‘평균’이며 Excel의 “AVERAGE” 함수는 해당 값을 계산합니다. 단순히 셀 범위의 모든 값을 더하고 그 합계를 숫자 값이 포함된 셀 수로 나눕니다(빈 셀은 무시).
우리에게 필요한 다른 통계 값은 ‘표준 편차’이며 Excel에는 약간 다른 방식으로 표준 편차를 계산하는 두 가지 다른 기능이 있습니다.
이전 버전의 Excel에는 데이터를 모집단의 ‘표본’으로 처리하면서 표준 편차를 계산하는 “STDEV” 기능만 있었습니다. Excel 2010은 이를 표준 편차를 계산하는 두 가지 함수로 나눴습니다.
STDEV.S: 이 기능은 이전 “STDEV” 기능과 동일합니다. 데이터를 모집단의 ‘표본’으로 취급하면서 표준편차를 계산합니다. 인구의 표본은 연구 프로젝트를 위해 수집된 특정 모기나 따로 보관되어 충돌 안전 테스트에 사용된 자동차와 같은 것일 수 있습니다.
STDEV.P: 이 함수는 데이터를 전체 모집단으로 취급하면서 표준 편차를 계산합니다. 전체 인구는 지구상의 모든 모기 또는 특정 모델의 생산 실행에 있는 모든 자동차와 같습니다.
선택하는 것은 데이터 세트를 기반으로 합니다. 차이는 일반적으로 작지만 “STDEV.P” 함수의 결과는 동일한 데이터 세트에 대한 “STDEV.S” 함수의 결과보다 항상 작습니다. 데이터에 더 많은 변동성이 있다고 가정하는 것이 더 보수적인 접근 방식입니다.
예를 살펴보겠습니다.
이 예에서는 “AVERAGE”, “STDEV.S” 및 “STDEV.P” 함수의 결과를 저장하기 위한 두 개의 열(“값” 및 “Z-점수”)과 세 개의 “도우미” 셀이 있습니다. “값” 열에는 500을 중심으로 하는 10개의 난수가 포함되어 있고 “Z-점수” 열은 ‘도우미’ 셀에 저장된 결과를 사용하여 Z-점수를 계산하는 곳입니다.
먼저 “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-점수” 열에서 첫 번째 셀을 선택합니다. 이 예에서는 “STDEV.S” 함수의 결과를 사용하지만 “STDEV.P”의 결과를 사용할 수도 있습니다.
다음 수식을 입력하고 Enter 키를 누릅니다.
=(E3-$G$3)/$H$3
또는 다음 단계를 사용하여 입력하는 대신 수식을 입력할 수 있습니다.
F3 셀을 클릭하고 =(
E3 셀을 선택합니다. (왼쪽 화살표 키를 한 번 누르거나 마우스를 사용할 수 있습니다)
빼기 기호 입력 –
셀 G3을 선택한 다음 F4를 눌러 셀에 대한 ‘절대’ 참조를 만들기 위해 “$” 문자를 추가합니다(“G3” > “$G$3″ > “G$3″ > “$G3″ > “G3” 순으로 순환합니다). ” 계속 F4 키를 누르면
유형 )/
셀 H3(또는 “STDEV.P”를 사용하는 경우 I3)을 선택하고 F4 키를 눌러 두 개의 “$” 문자를 추가합니다.
엔터 키를 치시오
Z-점수는 첫 번째 값에 대해 계산되었습니다. 평균보다 0.15945 표준 편차가 낮습니다. 결과를 확인하려면 표준편차에 이 결과(6.271629 * -0.15945)를 곱하고 결과가 값과 평균의 차이(499-500)와 같은지 확인할 수 있습니다. 두 결과 모두 동일하므로 값이 의미가 있습니다.
나머지 값의 Z-점수를 계산해 보겠습니다. 수식이 포함된 셀로 시작하는 전체 ‘Z-점수’ 열을 강조 표시합니다.
Ctrl+D를 눌러 선택한 다른 모든 셀을 통해 맨 위 셀의 수식을 아래로 복사합니다.
이제 수식은 모든 셀에 ‘채워져’ 있으며 각 셀은 “$” 문자로 인해 항상 올바른 “AVERAGE” 및 “STDEV.S” 또는 “STDEV.P” 셀을 참조합니다. 오류가 발생하면 뒤로 돌아가서 입력한 수식에 “$” 문자가 포함되어 있는지 확인하십시오.
‘도우미’ 셀을 사용하지 않고 Z-점수 계산하기
도우미 셀은 “AVERAGE”, “STDEV.S” 및 “STDEV.P” 함수의 결과를 저장하는 것과 같은 결과를 저장합니다. 그것들은 유용할 수 있지만 항상 필요한 것은 아닙니다. 대신 다음 일반 공식을 사용하여 Z-점수를 계산할 때 이 항목을 모두 건너뛸 수 있습니다.
다음은 “STDEV.S” 기능을 사용하는 것입니다.
=(Value-AVERAGE(Values))/STDEV.S(Values)
그리고 “STEV.P” 기능을 사용하는 것:
=(Value-AVERAGE(Values))/STDEV.P(Values)
함수에서 “값”에 대한 셀 범위를 입력할 때 절대 참조(F4를 사용하여 “$”)를 추가해야 ‘채울’ 때 다른 범위의 평균 또는 표준 편차를 계산하지 않습니다. 모든 수식의 셀 수.
데이터 세트가 큰 경우 도우미 셀을 사용하는 것이 “AVERAGE” 및 “STDEV.S” 또는 “STDEV.P” 함수의 결과를 매번 계산하지 않으므로 프로세서 리소스를 절약하고 도우미 셀을 사용하는 것이 더 효율적일 수 있습니다. 결과를 계산하는 데 걸리는 시간을 단축합니다.
또한 “$G$3″은 “AVERAGE($E$3:$E$12)”보다 저장하는 데 바이트 수가 적고 로드하는 데 RAM이 적습니다. 이는 표준 32비트 버전의 Excel이 2GB의 RAM으로 제한되어 있기 때문에 중요합니다(64비트 버전은 사용할 수 있는 RAM 양에 제한이 없습니다).