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

강의 설명

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

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

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

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

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

예제파일 다운로드

파일구분마지막수정일파일형식파일다운로드
2예제파일2020-01-13XLSX엑셀프레소 파일다운로드
1예제파일2019-12-23XLSX1엑셀프레소 파일다운로드
바로가기 목차
  1. 올바른 / 잘못된 산술평균의 예제
  2. 피벗테이블로 산술평균 계산하기 (필드 표시형식)
  3. 피벗테이블로 가중평균 계산하기 (계산필드 응용)
영상강의

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

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

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

평균에는 다양한 종류가 있고, 일상생활에서 말하는 평균은 '산술평균'을 뜻합니다. 하지만 실제 업무환경에서는 산술평균 말고도 더 자주 사용되는 평균이 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.67 / 3)

7
댓글 남기기

avatar
3 Comment Thereads
4 Thread replies
4 팔로워
 
가장 좋아요가 많은 댓글
가장 인기많은 댓글
3 작성자
바람따라22 바람따라22 오빠두엑셀 준구빵야 준구빵야 최근 작성자
  현재 페이지 댓글알림 신청  
알림 설정
준구빵야
손님
진준구
별점주기 :
     

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

준구빵야
구독자
준구빵야

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

바람따라22
구독자
바람따라22
별점주기 :
     

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

바람따라22
손님
바람따라1222
별점주기 :
     

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