엑셀 피벗테이블 평균, 그냥 계산하면 '절대로' 안되는 이유!

피벗테이블 평균 계산 시 반드시 주의해야 되는 '산술평균'과 '가중평균'의 차이점과 그 해결방법을 살펴봅니다.

홈페이지 » 엑셀 피벗테이블 평균, 그냥 계산하면 '절대로' 안되는 이유!

엑셀 피벗테이블 평균, 산술평균의 함정 :: 계산필드 응용

강의 설명

엑셀 피벗테이블은 간단하면서도 다양한 기능을 제공하는 매우 강력한 도구입니다. 반면, 잘못 사용되면 큰 문제를 일으킬수도 있는 양날의 검이 될 수도 있는데요... 몇주전 어느 한 중견기업의 부장님께 메일을 한통 받았습니다.

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

요약하자면, 작년부터 판가를 책정하는 규칙과 물품을 제공하는 고객 관리규칙이 바뀌었고, 따라서 고객별 판매된 수량과 가격을 모두 반영하여 판가의 평균을 계산해야하는데 피벗테이블로는 '산술평균'으로만 계산되어 데이터에 오차가 생긴다는 것이였습니다.

평균에는 산술평균, 가중평균, 절삭평균, 기하평균, 조화평균, 이동평균 등등.. 많은 종류가 있습니다. 그리고 우리가 실생활에서 가장 많이 쓰는 평균은 '산술평균' 인데요. 아쉽게도 피벗테이블에서 기본으로 제공되는 '평균'은 모두 '산술평균'으로 계산됩니다.

따라서, '가중평균'으로 평균을 계산해야 한다면 피벗테이블의 '계산필드'로 문제를 쉽게 해결할 수 있습니다.

예제파일 다운로드

바로가기 목차
영상강의

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

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

평균은 참 간단해보이지만, 때로 보는 시각에따라 다양하게 해석이 되는 아주 어려운 통계값입니다.

평균에는 다양한 종류가 있고, 일상생활에서 말하는 평균은 '산술평균'을 뜻합니다. 하지만 실제 업무환경에서는 산술평균 말고도 더 자주 사용되는 평균이 2가지 더 있는데요. (제 경혐에 비춘 주관적인 견해입니다...^_^;;)

그거슨 바로! 가중평균기하평균입니다.

  • 가중평균 : 더 많은 비중을 가진 항목에 높은 가중치를 적용합니다.
    예를들어, 사과를 100원에 10개를 팔고 200원에 100개를 팔았다면, 200원에 더 높은 가중치를 두어 평균을 계산합니다.
  • 기하평균 : 합의 평균이 아닌 '곱의 평균'에 사용됩니다.
    예를들어, 3년동안의 연도별 성장률이 10%, 12%, 15% 였다면, 평균성장률은 3√10%*12%*15% 로 계산합니다.

그렇다면, 비즈니스에서 사용되는 산술평균이 왜 문제가 될까요? 아래 예제를 통해 알아보겠습니다.

올바른 산술평균의 예제
1 평균판가 계산
500원/1000원에 각각 100개씩 판매했을 경우

위 매출기록에서 제품의 평균판가는 750원이됩니다. 500원과 1000원이라는 가격에 각각 100개씩 판매하였으므로, 동일한 비중으로 평균이 계산됩니다.

= (500+1000) / 2
= 750
잘못된 산술평균의 예제

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

2 평균판가 산술평균 오류
500원에 100개, 1000원에 200개를 판매한 경우

1,000원이라는 가격에 200개, 즉 더 많은 개수가 판매되었으므로 더 높은 가중치를 두어 평균을 계산해야 합니다. 이런 평균을 '가중평균' 이라고 하는데요. 위 예제에서 가중평균을 구하는 공식은 아래와 같습니다.

= 총금액 / 총개수
= { (100*500) + (200*1000) } / (100+200)
= { 250,000 } / 300
= 833

다시말해, 총금액(=개수*판가의 합계)에서 총개수(=개수의 합계)를 나눠주면 가중평균이 계산됩니다. 엑셀에서는 SUMPRODUCT 함수SUM 함수로 아주 쉽게 가중평균을 계산할 수 있습니다.

[ 가중평균을 구하는 함수공식 ]
= SUMPRODUCT(개수범위, 판가범위) / SUM(개수범위)
= 총금액 / 총개수

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

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

5 피벗테이블 값 필드 요약 목록
피벗테이블은 다양한 값 필드 표시형식을 제공합니다.

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

피벗테이블 평균 계산하기 (산술평균)
  1. 예제파일의 T4:X11 범위를 선택한 뒤, 단축키 ALT + N + V 로 피벗테이블을 생성합니다.

    6 피벗테이블 만들기
    예제파일의 실습표로 피벗테이블을 생성합니다.
  2. 피벗테이블의 '필드 설정'에서 '판가'를 값으로 끌어옵니다.
    7 피벗테이블 값 필드 설정
    [판가]를 '값' 필드로 끌어서 이동합니다.
  3. 값 필드의 '판가'를 클릭한 뒤, [값 필드 설정]을 선택합니다.

    8 피벗테이블 값 필드 설정 선택
    값 필드 설정을 클릭합니다.
  4. [필드의 계산 유형]을 '평균'으로 선택한 뒤 [확인]을 버튼을 클릭합니다.

    9 필드 데이터에서 평균 선택
    필드의 계산 유형에서 '평균'을 선택합니다.
  5. 다시 피벗테이블의 '필드 목록'에서 '날짜' 필드를 행으로 끌어옵니다.
    10 날짜필드 행으로 이동
    [날짜] 필드를 '행'으로 이동합니다.
  6. 계산결과를 보면 모든 월의 '평균 판가'가 750원으로 계산된 것을 볼 수 있습니다.

    11 모든 월의 평균판가가 750원으로 계산
    계산결과가 모두 750원으로 동일하게 계산됩니다.
  7. 2월의 경우를 보겠습니다. 항목별 판매된 개수가 다르므로 '산술평균'으로 계산하면 옳지 않은 평균이 계산됩니다. 따라서 '가중평균'으로 평균판가를 계산해줘야 합니다.

    피벗테이블 가중평균 계산 필요
    항목별 판매된 개수가 다르므로, 가중치를 다르게 두어 평균을 계산합니다.

왜 이런 결과가 나올까요?

그 이유는 바로 피벗테이블 '필드 계산 유형'의 평균은 '산술평균'으로 계산하기 때문입니다.
따라서, 상황에 따라 '가중평균'으로 계산해야 할 경우 피벗테이블의 '계산필드'를 응용하면 간단하게 가중평균을 계산할 수 있습니다.

피벗테이블 가중평균 계산하는 방법 (계산필드 응용)

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

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

    13 피벗테이블 계산필드 추가
    피벗테이블 분석의 '계산 필드'로 이동합니다.
  2. 판가는 '금액 ÷ 개수' 입니다. 따라서 아래 수식으로 '가중평균' 이라는 이름의 계산필드를 추가합니다.
    금액 / 개수

    14 피벗테이블 가중평균 계산필드 만들기
    '가중평균' 을 계산하는 계산필드를 추가합니다.
  3. 피벗테이블에 가중평균이 추가되었습니다.

    15 피벗테이블 가중평균 생성 완료
    가중평균이 추가 되었습니다.
  4. 이후 '가중평균' 필드를 우클릭 한 뒤, [필드표시형식]으로 이동하여 아래의 사용자지정서식으로 표시형식을 변경하면 더욱 깔끔하게 정리됩니다.
    #,##0
    이외에 다양한 사용자지정서식 목록관련포스트를 참고하세요.

    16 필드 표시형식 변경
    [필드 표시 형식]에서 표시형식을 깔끔하게 정리합니다.

링크] MS 홈페이지 피벗테이블에서 값 계산하는 방법 상세설명 보러가기

4.8 22 votes
게시글평점
현재 페이지 댓글알림 신청
알림 설정
guest
29 Comments
Inline Feedbacks
View all comments
진준구
진준구
2019년 12월 26일 9:52 오후
게시글평점 :
     

항상 좋은 강의 감사드립니다.
만약 제품별 그리고 월별 가중평균 흐름을 보고 싶으면 어떻게 해야하나요?

준구빵야
준구빵야
2019년 12월 27일 12:51 오전
Reply to  진준구

필드를 가로세로로 정렬하니 해결되었습니다. 감사합니다~~~~ ^^

바람따라22
바람따라22
2020년 1월 5일 10:11 오후
게시글평점 :
     

표를 작성하여 피벗테이블까지 만들었는데..
표에있는 데이터를 수정하여도 피벗테이블상에 데이터는 수정이 안되더라고요
기존 피벗테이블을 삭제하고 다시 피벗테이블을 작성을 했는데.
제가 잘못을 한건지??? 아니면 원래 이렇게 해야하는건지??? 궁금합니다

바람따라22
바람따라22
2020년 1월 6일 1:27 오후

그렇게 하니까 업데이트 되네요...
고맙습니다. ~~^^

바람따라1222
바람따라1222
2020년 1월 9일 4:24 오후
게시글평점 :
     

피벗테이블에서 일자별로 작성된 데이타를 년도별로 합계금액을 보려고 하는데.... 일자를 필터로 지정을 하니 모든 일자가 다 나와서 합계를 보는게 어려워요... 혹시 년도별로 볼 수 있게 하는 방법이 있나요?? 아니면 해당 내용에… 더보기 »

yu hyun
yu hyun
2020년 3월 14일 5:42 오후
게시글평점 :
     

예제 파일을 다운받아 똑 같이 따라 하는데 이런 메세지가 뜨네요 어떻게 해결해야 하나요? 이 명령을 실행하려면 적어도 두 행의 원본 데이터가 있어야 합니다.... 고급 필터를 사용하고 있으면 적어도 두 행의… 더보기 »

이선표
이선표
2020년 3월 20일 6:34 오후
게시글평점 :
     

강의 감사합니다. ^^

전상용
전상용
2020년 3월 24일 11:17 오전
게시글평점 :
     

완벽합니다.!!

leesh0630
leesh0630
2020년 3월 26일 1:40 오후
게시글평점 :
     

정말 유용한 강의에요~~~ 다른 강의들과는 컬리티가 달라요!!

갓쩌리
갓쩌리
2020년 4월 3일 2:00 오후
게시글평점 :
     

유튜브에서 우연히 보다 알게된건데 정말 감사하게 잘 보고 있습니다.

추억나무
추억나무
2020년 4월 7일 9:04 오전
게시글평점 :
     

감사드립니다. 깔끔한 강의 항상 응원합니다.

용감한 형들
용감한 형들
2020년 4월 9일 1:49 오후
게시글평점 :
     

등업은 언제되나요? ㅡㅡ

루카대디
루카대디
2020년 4월 12일 9:06 오전
게시글평점 :
     

좋은 강의 항상 감사합니다~~

엑린이
엑린이
2020년 4월 12일 1:28 오후
게시글평점 :
     

좋은 강의 감사합니다

곰돌이 푸후
곰돌이 푸후
2020년 4월 13일 1:05 오전
게시글평점 :
     

좋은 강의 감사합니다.

엑셀고고
엑셀고고
2020년 4월 13일 10:13 오전
게시글평점 :
     

생각지도 못한 부분이었어요....! 감사합니다 ^^

Bethegeek
Bethegeek
2020년 5월 6일 1:53 오후
게시글평점 :
     

안녕하세요 피벗테이블 “요약” 에서 “값 필드 설정”의 서식을 새로 추가 할수 있나요? 합계나 개수 평균등이 아닌 새로운 수식으로 이윤율을 나타내고 싶은데 “요약”에서 합계나 평균등 밖에 없어 혹시 가능한지 알고 싶네요… 더보기 »

초코우유
초코우유
2020년 6월 11일 10:59 오후
게시글평점 :
     

감사합니다~^^

MOHOLY
MOHOLY
2020년 6월 30일 5:39 오후
게시글평점 :
     

유용한 강의 유튜브에서도 열강 중입니다 ㅎㅎ 질문) 종으로 떨어지는 DATA에서도 피벗 계산필드 추가가 가능한가요? 아무리해봐도 안됩니다 ㅎㅎ EX) 날짜 항목 금액 0605 사과판매액 5000원 0610 사과판매액 1000원 피벗 후 사과판매액의… 더보기 »

미스터릭
미스터릭
2020년 7월 2일 10:06 오후
게시글평점 :
     

정말 많은 도움이 되고 항상 배우고 있습니다^^

뿌까뿌가
뿌까뿌가
2020년 7월 10일 8:23 오후
게시글평점 :
     

언제나 좋은 강의 감사합니다.

07년회사원
07년회사원
2020년 7월 11일 1:44 오후
게시글평점 :
     

도움이 많이 되었습니다.
감사합니다~!^^

Jun
Jun
2020년 7월 27일 2:34 오후
게시글평점 :
     

현업에 많은 도움을 받고 있습니다.
감사합니다.^^

Samuel Ry
Samuel Ry
2020년 7월 29일 9:26 오전
게시글평점 :
     

역시 이런건 oppadu 밖에 안 알려줌...긋긋긋

29
0
여러분의 생각을 댓글로 남겨주세요.x