통계 함수: AVERAGE, MEDIAN, STDEV

방대한 데이터 세트를 작업할 때 숫자 행만 쳐다보는 것은 의미 있는 통찰력을 제공하는 경우가 거의 없습니다. 판매 실적을 분석하거나 학생 성적을 평가하거나 분기별 지출을 검토할 때, 데이터를 요약하고 해석할 수 있는 신뢰할 만한 방법이 필요합니다. 이때 엑셀에 내장된 통계 함수가 유용하게 사용됩니다.

이 종합 가이드에서는 엑셀의 핵심 통계 함수인 AVERAGE, MEDIAN, MODE, STDEV에 대해 자세히 알아보겠습니다. 이러한 도구를 마스터하면 단순히 데이터를 저장하는 수준을 넘어, 종합적이고 실행 가능한 데이터 분석을 수행할 수 있게 됩니다.

중심경향성 지표 이해하기

중심경향성 지표는 데이터 세트의 중심이나 "전형적인" 값을 찾는 데 사용되는 통계 측정 항목입니다. 사람들은 일상적으로 "평균"이라는 단어를 자주 사용하지만, 통계 분석에서는 중심경향성을 평균(AVERAGE), 중간값(MEDIAN), 최빈값(MODE)이라는 세 가지 뚜렷한 개념으로 나눕니다.

AVERAGE 함수 (평균)

AVERAGE 함수는 숫자 그룹의 산술 평균을 계산합니다. 엑셀은 지정된 범위 내의 모든 숫자를 더한 후, 그 합을 숫자의 개수로 나눕니다.

구문: =AVERAGE(number1, [number2], ...)

• number1: 평균을 구할 첫 번째 숫자, 셀 참조 또는 범위입니다(필수).
• number2: 추가 숫자, 셀 참조 또는 범위입니다(선택 사항).

예를 들어, 셀 A1부터 A5까지 10, 20, 30, 40, 50의 값이 포함되어 있다면 수식 =AVERAGE(A1:A5)는 30을 반환합니다. AVERAGE 함수는 빈 셀과 텍스트 문자열을 자동으로 무시하므로, 비숫자 데이터로 인해 계산이 왜곡되지 않습니다.

MEDIAN 함수 (중간값)

MEDIAN 함수는 정렬된 숫자 목록에서 정확히 가운데에 있는 숫자를 찾습니다. 절반의 숫자는 중간값보다 크고, 나머지 절반은 작습니다.

구문: =MEDIAN(number1, [number2], ...)

AVERAGE 대신 MEDIAN을 사용하는 이유는 무엇일까요? AVERAGE 함수는 비정상적으로 높거나 낮은 극단적인 값, 즉 이상치(outlier)에 매우 민감합니다. 예를 들어, 작은 마을의 평균 소득을 계산할 때 억만장자 한 명이 이사 온다면, 다른 모든 사람의 생활 수준은 변하지 않았음에도 평균 소득은 급등할 것입니다. 그러나 MEDIAN(중간값)은 안정적으로 유지되며 "일반적인" 거주자의 모습을 보다 정확하게 반영합니다.

MODE 함수 (최빈값)

최빈값(mode)은 데이터 세트에서 가장 자주 발생하는 값을 나타냅니다. 최신 버전의 엑셀에서는 이를 위한 두 가지 별도의 함수를 제공합니다.

• MODE.SNGL: 단일 최빈값을 반환합니다. 빈도가 같은 값이 여러 개일 경우 처음 발견한 값을 반환합니다.
• MODE.MULT: 최빈값들의 배열을 반환합니다(최고 빈도를 가진 숫자가 여러 개일 때 유용합니다).

구문: =MODE.SNGL(number1, [number2], ...)

산포도 지표 이해하기

중심경향성이 데이터의 중심이 어디인지 알려준다면, 산포도 지표는 데이터가 그 중심을 기준으로 얼마나 넓게 퍼져 있는지를 나타냅니다. 두 데이터 세트의 평균이 정확히 같더라도 그 분포는 완전히 다를 수 있습니다.

STDEV 함수 (표준 편차)

표준 편차는 데이터가 평균으로부터 떨어져 있는 평균 거리를 측정합니다. 표준 편차가 낮다는 것은 데이터가 평균 주위에 밀집해 있음을 의미합니다(일관성이 높음). 표준 편차가 높다는 것은 데이터가 더 넓은 범위의 값으로 퍼져 있음을 나타냅니다(변동성이 높음).

엑셀에서는 데이터가 전체 모집단을 나타내는지 아니면 모집단의 표본일 뿐인지를 정의해야 합니다.

• STDEV.S (표본): 데이터 세트가 더 큰 모집단의 표본일 때 사용됩니다. 이는 가장 일반적인 상황입니다. 수식: =STDEV.S(range)
• STDEV.P (모집단): 데이터 세트에 대상 그룹의 모든 단일 구성원이 포함될 때 사용됩니다. 수식: =STDEV.P(range)

예를 들어, 길이가 정확히 10cm여야 하는 볼트를 생산하는 기계가 있다고 가정해 봅시다. 낮은 표준 편차는 정밀한 생산이 이루어지고 있음을 나타냅니다. 반면 높은 표준 편차는 기계가 예측할 수 없는 길이의 볼트를 생산하고 있음을 의미하며, 이는 유지보수가 필요하다는 신호입니다.

MIN 및 MAX 함수 (범위)

데이터가 퍼져 있는 전체적인 형태를 이해하려면 MAX 및 MIN 함수를 사용하여 각각 최고값과 최저값을 찾을 수 있습니다. MAX에서 MIN을 빼면 데이터 세트의 전체 "범위"를 구할 수 있습니다.

예시: =MAX(B2:B100) - MIN(B2:B100)

조건부 통계: AVERAGEIF 및 AVERAGEIFS

전체 열에 대한 통계를 계산하는 대신 특정 기준을 충족하는 행만 분석하고 싶을 때가 많습니다. 합계를 구할 때 SUMIF 및 SUMIFS를 사용하는 것과 마찬가지로, 엑셀은 조건부 평균을 위해 AVERAGEIF 및 AVERAGEIFS를 제공합니다.

AVERAGEIFS 함수를 사용하면 여러 기준을 충족하는 셀의 평균을 구할 수 있습니다. 예를 들어, "1분기" 동안 "동부" 지역의 판매 수익 평균만 구하는 경우입니다.

실전 예제: 직원 데이터 분석

이러한 통계 함수가 실제로 어떻게 작동하는지 보기 위해 실습을 해보겠습니다. 이 시나리오는 인사관리(HR)를 위한 엑셀: 직원 데이터 및 분석을 수행할 때 매우 흔하게 발생합니다.

직원 급여를 나타내는 다음과 같은 데이터 세트가 있다고 가정해 보겠습니다.

셀	직원 이름	부서	급여
A2 / B2 / C2	존 도	IT	$60,000
A3 / B3 / C3	제인 스미스	영업	$85,000
A4 / B4 / C4	밥 존슨	IT	$55,000
A5 / B5 / C5	앨리스 윌리엄스	임원	$250,000
A6 / B6 / C6	톰 데이비스	영업	$62,000

회사 내 급여 분포를 파악하고자 합니다. 수식을 작성해 보겠습니다.

=AVERAGE(C2:C6)      // Returns $102,400
=MEDIAN(C2:C6)       // Returns $62,000
=STDEV.S(C2:C6)      // Returns $83,383
=MAX(C2:C6)          // Returns $250,000
=MIN(C2:C6)          // Returns $55,000

결과 분석:
AVERAGE($102,400)와 MEDIAN($62,000)의 차이를 살펴보세요. 평균이 이렇게 높은 이유는 무엇일까요? 임원인 앨리스의 급여 $250,000가 평균을 크게 끌어올리는 이상치이기 때문입니다. 구직자가 "이곳의 일반적인 급여는 얼마입니까?"라고 묻는다면, $102,400라고 답하는 것은 오해를 불러일으킬 수 있습니다. $62,000라는 중간값이 일반적인 직원의 급여를 훨씬 더 솔직하게 보여줍니다.

또한 표준 편차가 매우 높게 나와($83,383), 눈으로 확인할 수 있는 사실을 수학적으로 뒷받침합니다. 즉, 직원들의 보상 방식에 엄청난 편차가 존재한다는 뜻입니다.

전문가의 팁: 이러한 수식으로 대시보드를 구축할 때 통계 수식을 여러 열에 복사하여 붙여넣을 계획이라면, 엑셀 셀 참조($ 기호를 사용하여 $C$2:$C$6처럼 범위를 고정하는 방법)를 확실히 이해하고 있어야 합니다.

빈 셀, 오류, 0값 처리하기

통계 함수를 작업할 때 잘못된 데이터는 의도하지 않은 결과를 초래할 수 있습니다. 엑셀에서 흔히 발생하는 데이터 입력 문제를 처리하는 방법은 다음과 같습니다.

• 빈 셀: AVERAGE, MEDIAN, STDEV는 빈 셀을 완전히 무시합니다. 즉, 분모 계산에 포함되지 않습니다.
• 0값: 빈 셀과 달리 숫자 0은 계산에 포함됩니다. 직원의 급여를 비워두지 않고 0으로 입력하면 평균이 인위적으로 낮아집니다. 0이 아닌 숫자의 평균만 구하려면 =AVERAGEIF(range, ">0")을 사용하세요.
• 텍스트: 표준 통계 함수는 텍스트 문자열을 무시합니다. 그러나 숫자가 실수로 텍스트 형식으로 지정된 경우(종종 셀 모서리에 녹색 삼각형으로 표시됨) 엑셀은 이를 무시합니다. 이 경우 먼저 해당 데이터를 숫자로 변환해야 합니다.
• 오류: 범위 내 단일 셀에 오류(#N/A 또는 #DIV/0! 등)가 포함되어 있으면 전체 AVERAGE 또는 STDEV 수식 결과도 오류가 됩니다. 범위 내 오류를 무시하려면 AGGREGATE 함수를 사용하세요.

복잡한 통계 분석에 AI 활용하기

데이터 세트가 커짐에 따라 통계 분석은 수학적으로 복잡해질 수 있습니다. 표준 편차 계산을 조건부 논리와 결합하는 작업(예: "0과 오류를 제외하고 IT 부서의 급여 표준 편차 구하기")은 전통적으로 어려운 배열 수식이나 복잡하게 중첩된 수식을 요구합니다.

이럴 때 최신 도구가 빛을 발합니다. 엑셀에서의 AI 기반 데이터 분석을 활용하면 복잡한 데이터 논리에 접근하는 방식이 완전히 달라집니다. STDEV.P를 써야 할지 STDEV.S를 써야 할지, AVERAGEIFS를 어떻게 올바르게 중첩할지 고민할 필요 없이, 원하는 내용을 일상 언어로 설명하기만 하면 GPTExcel가 즉시 정확한 수식을 생성해 줍니다. 구문, 괄호, 논리를 완벽하게 처리합니다.

인공지능이 수식을 작성하고 지표를 분석하는 방식을 어떻게 변화시키고 있는지 알아보려면 엑셀용 ChatGPT: AI로 수식 작성하기 가이드를 확인해 보세요.

자주 묻는 질문(FAQ)

AVERAGE 함수에서 #DIV/0! 오류가 반환되는 이유는 무엇인가요?

AVERAGE 함수에서 참조하는 범위에 숫자 값이 없을 때 #DIV/0! 오류가 발생합니다. 이는 엑셀이 합계를 0(숫자의 개수)으로 나누려고 시도하기 때문이며, 수학적으로 불가능합니다. 참조된 셀에 텍스트로 저장된 숫자가 아닌 실제 숫자가 포함되어 있는지 확인하세요.

STDEV.S와 STDEV.P 중 어느 것을 사용해야 하나요?

실제 상황의 95%는 STDEV.S(표본)를 사용해야 합니다. STDEV.P(모집단)는 분석하려는 그룹의 모든 단일 구성원에 대한 데이터를 수집한 경우에만 사용합니다. 더 큰 모집단의 표본을 분석하여 추론을 도출하려는 경우, 올바른 수학적 보정이 적용된 STDEV.S를 사용하세요.

MEDIAN 함수로 텍스트 값을 분석할 수 있나요?

아니요, MEDIAN은 순수한 수학 함수이므로 숫자 데이터가 필요합니다. 텍스트로만 구성된 범위의 중간값을 계산하려고 하면 엑셀은 #NUM! 오류를 반환합니다. 가장 빈번한 텍스트 문자열을 찾아야 하는 경우 INDEX 및 MATCH 함수를 MODE와 결합하여 사용할 수 있습니다.

0을 무시하고 평균을 계산하려면 어떻게 해야 하나요?

표준 AVERAGE 함수는 (빈 셀과 달리) 계산에 0을 포함하기 때문에 0을 제외하려면 AVERAGEIF 함수를 사용해야 합니다. 수식은 =AVERAGEIF(A1:A100, "<>0")입니다. 이 수식은 범위 내에서 0이 아닌 셀의 평균만 계산하도록 엑셀에 지시합니다.