이 글에서는 Excel 피벗 테이블(Pivot Table)에서 가중 평균(weighted average)을 계산하는 방법을 소개합니다. 일반 워크시트에서는 SUMPRODUCT와 같은 함수를 조합해 가중 평균을 쉽게 구할 수 있지만, 피벗 테이블에는 직접 함수를 적용할 수 없기 때문에 다소 복잡하게 느껴질 수 있습니다. 이럴 때 유용하게 사용할 수 있는 대안 기법을 단계별로 자세히 알아보겠습니다.
가중 평균이란?
가중 평균은 각 항목에 중요도(가중치)를 부여하여 계산하는 평균 방식입니다. 단순 산술 평균과 달리 데이터 집합 내 모든 숫자에 동일한 비중을 두지 않고, 각 값의 상대적 중요성을 반영하기 때문에 실제 상황을 더 정확하게 나타내는 지표로 평가됩니다.
일반적으로 Excel에서는 SUMPRODUCT 함수와 SUM 함수를 조합하여 가중 평균을 구하지만, 피벗 테이블에서는 함수를 직접 사용할 수 없으므로 보조 열(helper column)을 추가하는 방식으로 문제를 해결합니다.
예제 데이터셋 소개
이번 예제에서는 식료품 품목별 날짜 판매 데이터를 담은 데이터셋을 사용합니다. 이 데이터를 바탕으로 피벗 테이블에서 각 식료품의 가중 평균 가격을 계산해 보겠습니다.
1단계: 보조 열 추가하기
- 먼저 원본 데이터 테이블에 '판매 금액(Sales Amount)'이라는 이름의 보조 열을 새로 추가합니다.
- 새 열의 첫 번째 셀에 아래 수식을 입력합니다.
=D5*E5
- 수식 입력 후 결과가 표시되면, 채우기 핸들(Fill Handle)(+)을 이용해 나머지 셀까지 수식을 복사합니다.
- 모든 행에 수식이 적용되면 아래와 같은 결과를 얻을 수 있습니다.
2단계: 피벗 테이블 만들기
- 데이터셋(B4:F14) 범위 내 임의의 셀을 클릭하여 피벗 테이블 생성 준비를 합니다.
- 상단 메뉴에서 삽입(Insert) > 피벗 테이블(Pivot Table) > 테이블/범위에서(From Table/Range)를 차례로 선택합니다.
- '피벗 테이블 from table or range' 창이 나타나면, '테이블/범위(Table/Range)' 필드가 올바르게 설정되어 있는지 확인하고 확인(OK)을 누릅니다.
- 새 시트에 피벗 테이블이 생성됩니다. 이후 아래 스크린샷처럼 피벗 테이블 필드(PivotTable Fields)를 구성해 주세요.
- 설정이 완료되면 다음과 같은 피벗 테이블이 완성됩니다.
3단계: 계산된 필드로 가중 평균 분석하기
- 먼저 피벗 테이블을 선택합니다.
- 상단 리본 메뉴에서 피벗 테이블 분석(Pivot Table Analyze) > 필드, 항목 및 집합(Field, Items, & Sets) > 계산된 필드(Calculated Field)를 클릭합니다.
- '계산된 필드 삽입(Insert Calculated Field)' 창이 열리면 다음 작업을 진행합니다.
- '이름(Name)' 필드에 'Weighted Average(가중 평균)'를 입력합니다.
- 수식 입력란에 보조 열을 가중치로 나눈 '판매 금액/무게(Sales Amount/Weight)' 수식을 입력하여 가중 평균을 정의합니다.
- 확인(OK)을 클릭하면 완료됩니다.
- 최종적으로 피벗 테이블의 소계(Subtotal) 행에서 각 식료품별 가중 평균 가격을 확인할 수 있습니다.
마무리
지금까지 피벗 테이블에서 가중 평균을 계산하는 방법을 단계별로 살펴보았습니다. 피벗 테이블에서는 함수를 직접 사용할 수 없다는 점 때문에 막힐 수 있지만, 보조 열과 계산된 필드를 활용하면 생각보다 간단하게 해결할 수 있습니다. 이 방법이 여러분의 데이터 분석 작업에 도움이 되기를 바랍니다. 궁금한 점이 있다면 언제든지 문의해 주세요.
함께 읽으면 좋은 관련 글
- Excel에서 가중 점수 모델 만드는 방법 (4가지 실전 예제)
- Excel에서 변수에 가중치 할당하기 (3가지 유용한 예제)
- Excel에서 가중 이동평균 계산하기 (3가지 방법)
- 백분율로 Excel 가중 평균 계산하는 방법 (2가지 방식)