마이크로소프트 엑셀(Microsoft Excel)은 다양한 수학적 계산을 손쉽게 처리할 수 있는 강력한 도구입니다. 이번 튜토리얼에서는 엑셀에서 Z-점수(Z-Score)를 계산하는 방법을 단계별로 자세히 알아보겠습니다.
엑셀을 처음 사용하시는 분이라면, 본격적인 작업에 앞서 기본 함수의 사용법, 단축키 탐색 방법, 워크북 생성, 데이터 서식 지정 등 초보자가 알아야 할 핵심 내용을 먼저 익혀두시는 것을 추천합니다. 기본기를 다진 후 아래 내용을 따라 하시면 훨씬 수월합니다.
Z-점수란 무엇인가?
Z-점수(표준 점수, Standard Score)는 데이터 분포 내 값들 간의 관계를 나타내는 지표입니다. 구체적으로 말하면, 특정 값이 데이터셋의 평균과 표준편차를 기준으로 어느 위치에 있는지를 보여줍니다. Z-점수를 활용하면 데이터셋 내 값들을 정확하게 측정하고 비교할 수 있습니다.
Z-점수를 계산하는 수학 공식은 (x-µ) / σ입니다. 여기서 x는 셀 값(개별 데이터), µ는 평균, σ는 표준편차를 의미합니다.
기업들은 Z-점수를 활용해 파산 가능성을 예측하거나 재무 상태를 진단하기도 합니다. 또한 연구자들은 서로 다른 표본이나 모집단에서 얻은 관찰 결과를 비교할 때 Z-점수를 유용하게 사용합니다.
엑셀에서 Z-점수 계산하기
Z-점수는 평균과 표준편차를 기반으로 산출되기 때문에, 먼저 데이터셋의 평균과 표준편차를 구해야 합니다. 평균과 표준편차는 어떤 셀에든 입력할 수 있지만, 이번 예제에서는 워크시트에 '평균', '표준편차', 'Z-점수' 전용 열을 만들어 진행하겠습니다.
예시 문서에는 한 종이 회사 직원 10명의 성과 평가 점수가 담겨 있습니다. 이제 직원들의 평가 점수에 대한 Z-점수를 계산해 보겠습니다.
1단계: 평균(Average) 계산
데이터셋의 평균을 구하려면 =AVERAGE(를 입력한 후, 범위의 첫 번째 값을 선택하고 콜론(:)을 입력한 뒤 마지막 값을 선택합니다. 그다음 닫는 괄호를 입력하고 Enter 키를 누르면 됩니다. 완성된 수식은 아래와 같습니다.
=AVERAGE(B2:B11)
수식을 입력한 셀에 해당 데이터셋의 평균값이 표시됩니다.
2단계: 표준편차(Standard Deviation) 계산
엑셀에서는 몇 번의 클릭만으로 표준편차도 쉽게 구할 수 있습니다.
'표준편차' 열의 셀을 선택하고 =STDEV.P(를 입력한 뒤, 범위의 첫 번째 값을 선택하고 콜론(:)을 입력합니다. 이어서 마지막 값을 선택하고 닫는 괄호를 입력한 후 Enter 키를 누르면 됩니다. 완성된 수식은 아래와 같습니다.
=STDEV.P(B2:B11)
3단계: Z-점수 계산 - 방법 1 (STANDARDIZE 함수 활용)
엑셀에는 분포의 Z-점수를 바로 구해주는 STANDARDIZE 함수가 내장되어 있습니다. Z-점수 열의 첫 번째 셀을 선택한 후 아래 단계를 따라 해 보세요.
- 상단 메뉴에서 수식(Formulas) 탭으로 이동한 후 함수 마법사 / 추가 함수를 클릭합니다.
- 통계(Statistical) 카테고리에서 STANDARDIZE 함수를 선택합니다.
- 함수 인수(Function Arguments) 창이 열리면, 'X' 필드에 첫 번째 값의 셀 참조를 입력합니다.
- 'Mean' 필드에 평균값의 셀 참조를 입력하고 F4 키를 눌러 셀 참조를 고정합니다.
- 'Standard_dev' 필드에 표준편차의 셀 참조를 입력하고 역시 F4 키로 고정합니다. 미리보기로 Z-값이 표시되면 확인(OK)을 눌러 완료합니다.
나머지 값들의 Z-점수도 구하려면, 해당 셀의 오른쪽 하단 모서리로 커서를 옮긴 뒤 더하기(+) 아이콘을 드래그하여 아래로 끌어내리면 됩니다. 엑셀이 수식을 자동으로 복사하며 각 행의 값에 대한 Z-점수를 한꺼번에 생성해 줍니다.
4단계: Z-점수 계산 - 방법 2 (수식 직접 입력)
앞서 설명한 것처럼, 개별 데이터 값에서 평균을 뺀 후 그 결과를 표준편차로 나누면 Z-점수를 구할 수 있습니다. (x-µ) / σ 공식을 활용해 수식을 직접 입력하는 방법입니다.
- Z-점수 열의 첫 번째 셀을 선택하고 등호(=)와 여는 괄호를 입력한 뒤, Z-점수를 구하려는 첫 번째 값을 클릭합니다. 이어서 빼기 기호(-)를 입력하고 평균 셀을 클릭한 후 F4 키를 눌러 절대 참조로 고정합니다. 닫는 괄호를 입력한 후 나누기 기호(/)를 입력하고, 표준편차 셀을 클릭한 뒤 F4 키로 셀 참조를 잠급니다.
완성된 최종 수식은 아래와 같은 형태가 됩니다. Enter 키를 눌러 실행하세요.
=(B2-$C$2)/$D$2
이 수식은 선택된 셀의 첫 번째 값에 대한 Z-점수만 계산합니다. 나머지 행에도 적용하려면 첫 번째 Z-점수 셀의 오른쪽 하단 모서리에 커서를 두고 더하기(+) 아이콘을 드래그해 아래로 끌어내리면 됩니다.
Z-점수 해석하는 방법
데이터셋에는 대부분 양수와 음수의 Z-점수가 섞여 있습니다. 양수(+)의 Z-점수는 해당 값이 데이터셋의 평균보다 높다는 의미이며, 음수(-)의 Z-점수는 반대로 평균보다 낮다는 것을 나타냅니다. 만약 Z-점수가 0이라면, 그 값이 산술 평균과 정확히 일치한다는 뜻입니다.
데이터 값이 클수록 Z-점수도 높아집니다. 워크시트를 살펴보면 작은 값일수록 Z-점수가 낮고, 평균보다 작은 값들은 음수의 Z-점수를 가진다는 것을 확인할 수 있습니다.
예시 워크시트에서 'Michael'은 가장 높은 평가 점수(78)와 함께 가장 높은 Z-점수(1.679659)를 기록했으며, 'Dwight'와 'Kevin'은 모두 가장 낮은 평가 점수(34)와 가장 낮은 Z-점수(-1.59047)를 기록했습니다.
마치며: 엑셀 실력 한 단계 끌어올리기
이제 데이터셋의 Z-점수를 계산하는 방법을 완전히 익히셨습니다. 엑셀에서 Z-점수를 계산하는 과정에서 궁금한 점이나 유용한 팁이 있다면 댓글로 남겨주세요.
엑셀 활용 능력을 더욱 높이고 싶다면 분산(Variance) 계산법, 데이터셋에서 중복 행 제거하기, 요약 함수(Summary Functions)를 활용한 데이터 집계 방법 등 관련 튜토리얼도 함께 살펴보시길 추천합니다.