엑셀 피벗테이블, 산술평균의 함정을 피하는 방법

강의 설명

엑셀 피벗테이블은 간단하면서도 다양한 기능을 제공하는 매우 강력한 분석 도구입니다. 그러나 잘못 사용하면 데이터 해석에 큰 오류를 일으킬 수 있는 양날의 검이 되기도 합니다. 몇 주 전, 한 중견기업의 부장님께 다음과 같은 메일을 한 통 받았습니다.

...(중략)... 자사의 월 평균 판가를 전년도와 비교하여 데이터 분석 후 익월·익익월 판매 가격을 수립하고 있습니다..
...(중략)... 재작년까지는 주문수량에 따라 고정가로 판매했지만, 작년부터 테스트 고객을 도입하고 고객 규모별 등급을 반영하여 판가를 책정했습니다.. 그러다 보니 수량과 단가 기준이 모두 바뀌어 데이터 분석에 문제가 생겼고... (중략) ... 샘플 고객을 고려하여 가격을 예측해야 하는데, 피벗테이블로 판매가격의 평균을 계산하니 수량은 반영되지 않고 산술평균으로만 계산됩니다..

요약하면, 작년부터 판가 책정 규칙과 고객 관리 규칙이 바뀌어 고객별 판매 수량과 가격을 모두 반영한 평균을 구해야 하는 상황인데, 피벗테이블에서는 '산술평균'으로만 계산되어 데이터에 오차가 발생한다는 내용이었습니다.

평균에는 산술평균, 가중평균, 절삭평균, 기하평균, 조화평균, 이동평균 등 다양한 종류가 있습니다. 일상에서 가장 많이 사용하는 평균은 '산술평균'이지만, 아쉽게도 피벗테이블에서 기본으로 제공되는 '평균' 역시 모두 '산술평균'으로 계산됩니다.

따라서 '가중평균'을 구해야 하는 상황이라면, 피벗테이블의 '계산필드' 기능을 활용하여 문제를 손쉽게 해결할 수 있습니다.

평균, 그냥 더해서 나눠주면 끝(!) 아니였나요?!...

평소에 사용하는 산술평균의 치명적인 오류

평균은 간단해 보이지만, 보는 시각에 따라 다양하게 해석되는 매우 까다로운 통계값입니다.

앞서 말씀드린 것처럼 평균에는 여러 종류가 있고, 일상에서 말하는 평균은 일반적으로 '산술평균'을 가리킵니다. 그러나 실제 업무 환경에서는 산술평균 외에도 자주 사용되는 평균이 두 가지 더 있습니다.

바로 가중평균과 기하평균입니다.

그렇다면 비즈니스 현장에서 산술평균이 왜 문제가 될까요? 아래 예제를 통해 살펴보겠습니다.

위 매출 기록에서 제품의 평균 판가는 750원입니다. 500원과 1,000원이라는 가격에 각각 100개씩 판매했으므로, 두 가격이 동일한 비중으로 평균에 반영됩니다.

잘못된 산술평균의 예제

이번에는 두 번째 예제를 살펴보겠습니다. 500원에 100개, 1,000원에 200개를 판매한 경우, 평균은 어떻게 계산되어야 할까요?

1,000원짜리가 200개로 더 많이 판매되었으므로, 더 높은 가중치를 두어 평균을 계산해야 합니다. 이렇게 비중을 반영해 구하는 평균을 '가중평균'이라고 합니다. 위 예제에서 가중평균을 구하는 공식은 다음과 같습니다.

다시 말해, 총금액(=개수×판가의 합계)을 총개수(=개수의 합계)로 나누면 가중평균이 계산됩니다. 엑셀에서는 SUMPRODUCT 함수와 SUM 함수를 활용하면 가중평균을 손쉽게 구할 수 있습니다.

우리가 잘못 사용하고 있는 '피벗테이블 평균'의 함정

피벗테이블에는 다양한 값 필드 요약 방식이 있습니다. 기본값은 '합계'로 표시되지만, 필요에 따라 '개수', '평균', '최대값' 등 선택한 항목을 다양한 방식으로 표현할 수 있습니다.

아래 예제를 통해 피벗테이블로 '평균'을 계산할 때 어떤 오류가 발생할 수 있는지 살펴보겠습니다.

1. 예제파일의 T4:X11 범위를 선택한 뒤, 단축키 ALT + N + V를 눌러 피벗테이블을 생성합니다.

   

2. 피벗테이블의 '필드 설정'에서 '판가'를 값 영역으로 끌어옵니다.
   
3. 값 필드의 '판가'를 클릭한 뒤 [값 필드 설정]을 선택합니다.

   

4. [필드의 계산 유형]에서 '평균'을 선택한 뒤 [확인] 버튼을 클릭합니다.

   

5. 다시 피벗테이블의 '필드 목록'에서 '날짜' 필드를 행 영역으로 끌어옵니다.
   
6. 계산 결과를 보면 모든 월의 '평균 판가'가 750원으로 계산된 것을 확인할 수 있습니다.

   

7. 2월의 경우를 살펴보겠습니다. 항목별 판매 개수가 다르므로 '산술평균'으로 계산하면 올바른 평균이 나오지 않습니다. 따라서 '가중평균'으로 평균 판가를 계산해야 합니다.

   

왜 이런 결과가 나올까요?

그 이유는 피벗테이블의 '필드 계산 유형'에서 평균이 '산술평균'으로 계산되기 때문입니다.
따라서 상황에 따라 '가중평균'으로 계산해야 한다면, 피벗테이블의 '계산필드' 기능을 활용하여 가중평균을 손쉽게 구할 수 있습니다.

이제 피벗테이블에서 가중평균을 계산하는 방법을 단계별로 살펴보겠습니다.

1. 해당 피벗테이블을 선택한 뒤, [피벗테이블 분석] - [필드 항목 및 집합] - [계산필드]를 클릭합니다.

   

2. 판가는 '금액 ÷ 개수'로 계산됩니다. 따라서 아래 수식으로 '가중평균'이라는 이름의 계산필드를 추가합니다.
   금액 / 개수

   

3. 피벗테이블에 가중평균 필드가 추가되었습니다.

   

4. 마지막으로 '가중평균' 필드를 우클릭한 뒤 [필드 표시 형식]으로 이동하여 아래 사용자지정서식으로 표시 형식을 변경하면 결과를 더욱 깔끔하게 정리할 수 있습니다.
   #,##0
   이 외에 다양한 사용자지정서식 목록은 관련 포스트를 참고하세요.

   

[참고 링크] MS 공식 문서 — 피벗테이블에서 값 계산하는 방법 자세히 보기