Z-점수는 데이터 세트 내에서 특정 값이 평균으로부터 표준 편차 단위로 얼마나 떨어져 있는지를 나타내는 통계적 지표입니다. 데이터의 평균과 표준 편차는 각각 AVERAGE 및 STDEV.S 또는 STDEV.P 함수를 활용하여 산출할 수 있으며, 이러한 결과값을 바탕으로 각 데이터 값의 Z-점수를 계산할 수 있습니다.
Z-점수의 정의와 AVERAGE, STDEV.S, STDEV.P 함수의 역할
Z-점수는 서로 다른 데이터 집합에 속한 값들을 비교하는 데 유용한 도구입니다. 이는 데이터 포인트가 평균으로부터 표준 편차의 몇 배수만큼 떨어져 있는지를 보여줍니다. 기본적인 계산 공식은 다음과 같습니다.
=(데이터 값 - AVERAGE(데이터 세트))/STDEV(데이터 세트)
예를 들어, 두 수학 교사가 가르치는 학생들의 기말고사 점수를 비교한다고 가정해 봅시다. 한 학생은 95점을, 다른 학생은 87점을 받았습니다. 언뜻 보면 95점 학생이 더 뛰어난 것처럼 보일 수 있지만, 만약 87점 학생이 더 어려운 시험을 치렀다면 어떨까요? 각 학급의 평균 점수와 표준 편차를 이용하여 Z-점수를 계산함으로써 두 학생의 상대적인 성적을 더욱 정확하게 평가할 수 있습니다. Z-점수를 비교해 보면, 87점 학생이 본인 학급의 평균과 비교했을 때 95점 학생보다 더 우수한 성적을 거두었음을 알 수 있습니다.
여기서 필요한 첫 번째 통계값은 ‘평균’이며, Excel의 “AVERAGE” 함수를 통해 쉽게 구할 수 있습니다. 이 함수는 주어진 셀 범위의 모든 값들을 더한 후, 해당 값들의 개수로 나눕니다(빈 셀은 무시합니다).
다음으로 필요한 통계값은 ‘표준 편차’입니다. Excel에서는 표준 편차를 계산하는 두 가지 함수를 제공하며, 이들은 약간 다른 방식으로 계산합니다.
이전 Excel 버전에서는 “STDEV” 함수만이 제공되었으며, 이는 데이터를 모집단의 ‘표본’으로 간주하여 표준 편차를 계산했습니다. 하지만 Excel 2010부터는 표준 편차를 계산하는 두 가지 함수로 분리되었습니다.
STDEV.S: 이 함수는 이전의 “STDEV” 함수와 동일하게, 주어진 데이터를 모집단의 ‘표본’으로 취급하여 표준 편차를 계산합니다. 여기서 ‘표본’은 전체 집단에서 추출된 일부 데이터 세트를 의미합니다. 예를 들어, 연구 프로젝트를 위해 수집된 특정 모기 집단이나 충돌 테스트에 사용된 자동차 등이 될 수 있습니다.
STDEV.P: 이 함수는 데이터를 전체 모집단으로 간주하여 표준 편차를 계산합니다. 전체 모집단의 예로는 특정 지역의 모든 모기나 특정 모델 생산 라인에서 생산된 모든 자동차 등이 있습니다.
어떤 함수를 선택할지는 분석하는 데이터 세트에 따라 결정됩니다. 일반적으로 두 함수의 결과 차이는 미미하지만, “STDEV.P” 함수의 결과는 동일한 데이터 세트에 대한 “STDEV.S” 함수의 결과보다 항상 작게 나타납니다. 이는 데이터를 모집단 전체로 간주할 때 데이터 변동성이 더 낮다는 가정에 기반합니다. 데이터를 표본으로 취급하는 것이 좀 더 보수적인 접근 방식이라고 할 수 있습니다.
예시를 통한 설명
이제 실제 예를 통해 Z-점수 계산 과정을 자세히 살펴보겠습니다. “값”과 “Z-점수”라는 두 개의 열을 만들고, 평균과 표준 편차를 계산하는 데 사용될 세 개의 “보조” 셀을 추가합니다. “값” 열에는 500을 중심으로 분포된 10개의 난수가 포함되어 있으며, “Z-점수” 열에서는 보조 셀에 저장된 평균 및 표준 편차 값을 사용하여 각 값에 대한 Z-점수를 계산할 것입니다.
가장 먼저 “AVERAGE” 함수를 사용하여 값들의 평균을 계산합니다. 평균값을 저장할 셀을 선택합니다.
선택한 셀에 다음 수식을 입력하고 Enter 키를 누르거나 Excel의 “수식” 메뉴를 사용합니다.
=AVERAGE(E2:E13)
만약 “수식” 메뉴를 통해 함수에 접근하려면 “함수 삽입” 드롭다운 메뉴에서 “통계”를 선택한 다음 “AVERAGE”를 클릭하면 됩니다.
함수 인수 창에서 “값” 열의 모든 셀을 “Number1” 필드에 대한 입력값으로 선택합니다. “Number2” 필드는 비워두어도 됩니다.
이제 “확인”을 누릅니다.
다음으로, “STDEV.S” 또는 “STDEV.P” 함수를 사용하여 값들의 표준 편차를 계산합니다. 이 예시에서는 두 함수를 모두 사용하여 표준 편차를 구하는 방법을 보여드리겠습니다. 우선 “STDEV.S” 함수의 결과값을 저장할 셀을 선택합니다.
“STDEV.S” 함수를 사용하여 표준 편차를 계산하려면 다음 수식을 입력하고 Enter 키를 누릅니다(또는 “수식” 메뉴를 통해 접근합니다).
=STDEV.S(E3:E12)
“수식” 메뉴를 통해 함수에 접근하려면 “함수 삽입” 드롭다운에서 “통계”를 선택하고, 스크롤하여 “STDEV.S”를 클릭하면 됩니다.
함수 인수 창에서 “값” 열의 모든 셀을 “Number1” 필드에 대한 입력값으로 선택합니다. “Number2” 필드는 비워두어도 됩니다.
이제 “확인”을 누릅니다.
다음으로, “STDEV.P” 함수를 사용하여 표준 편차를 계산합니다. 결과를 저장할 셀을 선택합니다.
“STDEV.P” 함수를 사용하여 표준 편차를 계산하려면 다음 수식을 입력하고 Enter 키를 누릅니다(또는 “수식” 메뉴를 통해 접근합니다).
=STDEV.P(E3:E12)
“수식” 메뉴에서 함수에 접근하려면 “함수 삽입” 드롭다운에서 “통계”를 선택하고, 스크롤하여 “STDEV.P”를 클릭하면 됩니다.
함수 인수 창에서 “값” 열의 모든 셀을 “Number1” 필드에 대한 입력값으로 선택합니다. “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” 순으로 순환합니다.
) / 를 입력합니다.
H3 셀을 선택하고 F4 키를 눌러 셀 참조를 ‘절대’ 참조로 변경합니다.
Enter 키를 누릅니다.
첫 번째 값에 대한 Z-점수가 계산되었습니다. 이 값은 평균보다 약 0.15945 표준 편차만큼 작다는 것을 의미합니다. 결과의 유효성을 확인하려면 표준 편차에 이 결과값을 곱하고 (6.271629 * -0.15945) 그 결과가 값과 평균의 차이(499-500)와 같은지 확인할 수 있습니다. 두 결과가 동일하므로 Z-점수가 올바르게 계산되었음을 알 수 있습니다.
이제 나머지 값들에 대한 Z-점수를 계산해 봅시다. 수식이 입력된 첫 번째 셀을 시작으로 “Z-점수” 열 전체를 강조 표시합니다.
Ctrl+D를 눌러 선택된 다른 모든 셀에 맨 위 셀의 수식을 복사합니다.
이제 수식이 모든 셀에 복사되었으며, 각 셀은 절대 참조 때문에 항상 정확한 “AVERAGE”, “STDEV.S”, “STDEV.P” 셀을 참조하게 됩니다. 만약 오류가 발생한다면, 수식 내에 “$” 문자가 정확하게 포함되어 있는지 확인하십시오.
보조 셀 없이 Z-점수 계산하기
보조 셀은 “AVERAGE”, “STDEV.S”, “STDEV.P” 함수 결과와 같은 중간값을 저장하는 데 사용됩니다. 이러한 보조 셀이 유용할 때도 있지만, 항상 필요한 것은 아닙니다. 다음의 일반적인 수식을 사용하여 Z-점수를 계산할 때는 이러한 보조 셀들을 생략할 수 있습니다.
다음은 “STDEV.S” 함수를 사용하는 경우입니다.
=(값-AVERAGE(값들))/STDEV.S(값들)
다음은 “STDEV.P” 함수를 사용하는 경우입니다.
=(값-AVERAGE(값들))/STDEV.P(값들)
함수에 “값들”에 해당하는 셀 범위를 입력할 때, 절대 참조(F4 키를 사용하여 “$”)를 추가하여 수식을 다른 셀로 복사했을 때 각각 다른 셀 범위의 평균 또는 표준 편차를 계산하지 않도록 해야 합니다.
만약 데이터 세트가 큰 경우, 보조 셀을 사용하는 것이 효율적일 수 있습니다. 이는 매번 “AVERAGE”, “STDEV.S” 또는 “STDEV.P” 함수 결과를 계산할 필요가 없어 프로세서 리소스를 절약하고, 계산 시간을 단축할 수 있기 때문입니다. 또한, “$G$3″은 “AVERAGE($E$3:$E$12)” 보다 저장하는 데 필요한 바이트 수가 적고, 로드하는 데 필요한 RAM도 적습니다. 이는 표준 32비트 버전의 Excel이 2GB의 RAM으로 제한되어 있다는 점에서 중요합니다 (64비트 버전에는 RAM 제한이 없습니다).