엑셀 피벗테이블 평균, 산술평균의 함정 :: 계산필드 응용
바로가기 목차
영상강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [실무기초] 피벗테이블 계산필드로 가중평균 구하기예제파일
강의 설명
엑셀 피벗테이블은 간단하면서도 다양한 기능을 제공하는 매우 강력한 도구입니다. 반면, 잘못 사용되면 큰 문제를 일으킬수도 있는 양날의 검이 될 수도 있는데요... 몇주전 어느 한 중견기업의 부장님께 메일을 한통 받았습니다.
...(중략)... 자사의 월 평균판가를 이전 년도와 비교하여 데이터분석 후 익월/익익월 판매가격을 수립하고 있습니다..
...(중략)... 재작년까지는 주문수량에 따라 고정가로 판매되었지만, 작년부터 테스트 고객을 도입하고 각 고객규모별 등급을 반영하여 판가를 책정했습니다.. 그러다보니 수량, 단가 기준이 모두 바뀌어 데이터분석에 문제가 생겼고... (중략) ... 샘플고객을 고려하여 가격예측을 했어야 하는데 피벗테이블로 판매가격의 평균을 계산하니 수량은 고려되지 않고 산술평균으로 계산이 됩니다..요약하자면, 작년부터 판가를 책정하는 규칙과 물품을 제공하는 고객 관리규칙이 바뀌었고, 따라서 고객별 판매된 수량과 가격을 모두 반영하여 판가의 평균을 계산해야하는데 피벗테이블로는 '산술평균'으로만 계산되어 데이터에 오차가 생긴다는 것이였습니다.
평균에는 산술평균, 가중평균, 절삭평균, 기하평균, 조화평균, 이동평균 등등.. 많은 종류가 있습니다. 그리고 우리가 실생활에서 가장 많이 쓰는 평균은 '산술평균' 인데요. 아쉽게도 피벗테이블에서 기본으로 제공되는 '평균'은 모두 '산술평균'으로 계산됩니다.
따라서, '가중평균'으로 평균을 계산해야 한다면 피벗테이블의 '계산필드'로 문제를 쉽게 해결할 수 있습니다.
평균, 그냥 더해서 나눠주면 끝(!) 아니였나요?!...
평소에 사용하는 산술평균의 치명적인 오류
평균은 참 간단해보이지만, 때로 보는 시각에따라 다양하게 해석이 되는 아주 어려운 통계값입니다.
평균에는 다양한 종류가 있고, 일상생활에서 말하는 평균은 '산술평균'을 뜻합니다. 하지만 실제 업무환경에서는 산술평균 말고도 더 자주 사용되는 평균이 2가지 더 있는데요.
(제 경혐에 비춘 주관적인 견해입니다...^_^;;)그거슨 바로! 가중평균과 기하평균입니다.
- 가중평균 : 더 많은 비중을 가진 항목에 높은 가중치를 적용합니다.
예를들어, 사과를 100원에 10개를 팔고 200원에 100개를 팔았다면, 200원에 더 높은 가중치를 두어 평균을 계산합니다. - 기하평균 : 합의 평균이 아닌 '곱의 평균'에 사용됩니다.
예를들어, 3년동안의 연도별 성장률이 10%, 12%, 15% 였다면, 평균성장률은 3√10%*12%*15% 로 계산합니다.
그렇다면, 비즈니스에서 사용되는 산술평균이 왜 문제가 될까요? 아래 예제를 통해 알아보겠습니다.
- 가중평균 : 더 많은 비중을 가진 항목에 높은 가중치를 적용합니다.
위 매출기록에서 제품의 평균판가는 750원이됩니다. 500원과 1000원이라는 가격에 각각 100개씩 판매하였으므로, 동일한 비중으로 평균이 계산됩니다.
= 750
잘못된 산술평균의 예제
두번째 예제를 살펴볼까요? 이번에는 500원에 100개, 1000원에 200개를 판매했습니다. 이 경우에는 어떻게 평균이 계산되어야 할까요?
1,000원이라는 가격에 200개, 즉 더 많은 개수가 판매되었으므로 더 높은 가중치를 두어 평균을 계산해야 합니다. 이런 평균을 '가중평균' 이라고 하는데요. 위 예제에서 가중평균을 구하는 공식은 아래와 같습니다.
= { (100*500) + (200*1000) } / (100+200)
= { 250,000 } / 300
= 833
다시말해, 총금액(=개수*판가의 합계)에서 총개수(=개수의 합계)를 나눠주면 가중평균이 계산됩니다. 엑셀에서는 SUMPRODUCT 함수와 SUM 함수로 아주 쉽게 가중평균을 계산할 수 있습니다.
= SUMPRODUCT(개수범위, 판가범위) / SUM(개수범위)
= 총금액 / 총개수
우리가 잘못 사용하고 있는 '피벗테이블 평균'의 함정
피벗테이블에는 다양한 값 필드 요약 방식이 있습니다. 기본값으로 '합계'를 표시하지만, 필요에 따라 '개수', '평균', '최대값' 등 선택한 항목을 다양한 방식으로 표현할 수 있는데요.
아래 예제를 통해 피벗테이블로 '평균'을 계산시 어떤 오류가 발생할 수 있는지 알아보겠습니다.
- 예제파일의 T4:X11 범위를 선택한 뒤, 단축키 ALT + N + V 로 피벗테이블을 생성합니다.
예제파일의 실습표로 피벗테이블을 생성합니다. - 피벗테이블의 '필드 설정'에서 '판가'를 값으로 끌어옵니다.
[판가]를 '값' 필드로 끌어서 이동합니다. - 값 필드의 '판가'를 클릭한 뒤, [값 필드 설정]을 선택합니다.
값 필드 설정을 클릭합니다. - [필드의 계산 유형]을 '평균'으로 선택한 뒤 [확인]을 버튼을 클릭합니다.
필드의 계산 유형에서 '평균'을 선택합니다. - 다시 피벗테이블의 '필드 목록'에서 '날짜' 필드를 행으로 끌어옵니다.
[날짜] 필드를 '행'으로 이동합니다. - 계산결과를 보면 모든 월의 '평균 판가'가 750원으로 계산된 것을 볼 수 있습니다.
계산결과가 모두 750원으로 동일하게 계산됩니다. - 2월의 경우를 보겠습니다. 항목별 판매된 개수가 다르므로 '산술평균'으로 계산하면 옳지 않은 평균이 계산됩니다. 따라서 '가중평균'으로 평균판가를 계산해줘야 합니다.
항목별 판매된 개수가 다르므로, 가중치를 다르게 두어 평균을 계산합니다.
왜 이런 결과가 나올까요?
그 이유는 바로 피벗테이블 '필드 계산 유형'의 평균은 '산술평균'으로 계산하기 때문입니다.
따라서, 상황에 따라 '가중평균'으로 계산해야 할 경우 피벗테이블의 '계산필드'를 응용하면 간단하게 가중평균을 계산할 수 있습니다.
피벗테이블에서 가중평균을 계산하는 방법을 단계별로 살펴보겠습니다.
- 해당 피벗테이블을 선택한 뒤, [피벗테이블 분석] - [필드 항목 및 집합] - [계산필드] 를 선택합니다.
피벗테이블 분석의 '계산 필드'로 이동합니다. - 판가는 '금액 ÷ 개수' 입니다. 따라서 아래 수식으로 '가중평균' 이라는 이름의 계산필드를 추가합니다.
금액 / 개수
'가중평균' 을 계산하는 계산필드를 추가합니다. - 피벗테이블에 가중평균이 추가되었습니다.
가중평균이 추가 되었습니다. - 이후 '가중평균' 필드를 우클릭 한 뒤, [필드표시형식]으로 이동하여 아래의 사용자지정서식으로 표시형식을 변경하면 더욱 깔끔하게 정리됩니다.
[필드 표시 형식]에서 표시형식을 깔끔하게 정리합니다.
