Excel 피벗 테이블에서 가중 평균을 계산하는 방법은 무엇입니까?
Excel에서 데이터의 가중 평균을 계산하는 것은 특히 데이터 포인트가 최종 결과에 균등하지 않게 기여할 때 일반적인 요구사항입니다. 간단한 범위의 경우, SUMPRODUCT 및 SUM 함수는 빠른 해결책을 제공합니다. 그러나 피벗 테이블로 작업할 때 계산된 필드가 이러한 함수를 기본적으로 지원하지 않는다는 것을 알 수 있습니다. 이로 인해 피벗 테이블 내에서 직접 가중 평균을 계산하려고 할 때 문제가 복잡해질 수 있습니다. 이러한 제한 사항을 이해하고 대체 방법을 배우면 다양한 시나리오에서 데이터를 효율적으로 요약하는 데 도움이 될 수 있습니다. 이 문서에서는 피벗 테이블에서 가중 평균을 계산하는 다양한 방법을 다루며, 고전적인 솔루션과 Excel에서 사용 가능한 새 기능을 모두 포함합니다.
Excel 피벗 테이블에서 가중 평균 계산하기
VBA 코드 - 피벗 테이블에서 가중 평균 계산 자동화하기
Power Pivot (데이터 모델) - DAX를 사용하여 피벗 테이블에서 가중 평균 계산하기
Excel 피벗 테이블에서 가중 평균 계산하기
다양한 과일의 판매 데이터를 보여주는 표가 있다고 가정하고, 열에는 다음과 같은 항목이 있습니다: 과일, 무게그리고 단위당 가격그리고 아래와 같이 이러한 값을 요약하는 피벗 테이블을 작성했습니다.
각 과일의 가중 평균 가격을 계산해야 하는 경우 — 즉, 각 데이터 포인트의 무게에 따른 정확한 기여도를 반영하고 싶다면 — 피벗 테이블은 계산된 필드에서 SUMPRODUCT 또는 유사한 고급 함수를 직접 사용할 수 없습니다. 다음의 수동 접근 방식은 소스 데이터에 도우미 열을 추가하고 피벗 테이블의 기본 제공 옵션을 통해 가중 평균을 도출하여 이 제한 사항을 해결합니다.
1소스 데이터에 금액 이라는 이름으로 도우미 열을 추가하는 것으로 시작하세요.
새로운 빈 열을 삽입하고, 그 제목을 금액이라고 지정한 다음, 첫 번째 행(예: C2)에 공식을 입력합니다. =D2*E2 (여기서 D2 는 무게이고 E2 는 단위당 가격입니다 — 필요에 따라 제목에 맞게 조정). 그런 다음 채우기 핸들을 드래그하여 모든 행에 걸쳐 공식을 적용합니다. 이 단계는 각 항목의 무게와 가격을 곱하여 해당 항목의 총 가중 가격을 얻습니다. 스크린샷 참조:
팁:
- 소스 테이블에 병합된 셀이 없는지 확인하세요. 병합된 셀은 수식 오류를 일으킬 수 있습니다.
- 대규모 데이터 세트를 다룰 때는 수식이 모든 관련 행에 적용되었는지 두 번 확인하세요.
- 열 할당이 변경된 경우 수식을 적절히 업데이트하세요.
2다음으로, 새로 추가된 도우미 열을 반영하도록 피벗 테이블을 업데이트합니다. 피벗 테이블 내의 아무 셀이나 선택하면 피벗 테이블 도구 컨텍스트 탭이 나타납니다. 클릭하세요 분석 (또는 옵션, Excel 버전에 따라 다름) > 새로 고침. 이 단계는 새로운 금액 필드가 피벗 테이블 필드 목록에 나타나도록 합니다.
3. 가중 평균 필드를 추가하려면 분석 > 필드, 항목 및 집합 > 계산된 필드로 이동합니다. 그러면 삽입 계산 필드 대화 상자가 열리고 여기에서 사용자 정의 계산을 설정할 수 있습니다.
참고: 계산된 필드는 이미 데이터에 정의된 필드를 사용합니다. 이 단계 전에 필요한 모든 열이 추가되고 새로 고침되었는지 확인하세요.
4삽입 계산 필드 대화 상자에서 가중 평균 (또는 다른 명확한 이름)을 이름 상자에 입력하세요. 그리고 공식 필드에 =금액/무게라고 입력합니다. 소스 데이터에서 정확한 필드 이름을 반드시 사용하세요 — 대소문자를 구분하며 정확히 일치해야 합니다. 그런 다음 확인 을 클릭하여 계산된 가중 평균 필드를 추가합니다.
문제 해결:
- #DIV/0! 오류가 나타난다면 무게 값에 0이 포함되어 있지 않은지 확인하세요.
- 계산된 필드가 나타나지 않는다면 필드 이름의 철자와 대소문자가 정확한지 확인하세요.
이제 각 과일 종류의 가중 평균 가격이 피벗 테이블의 소계 행에 나타납니다. 결과는 평균 가격 계산이 각 항목의 무게의 영향을 실제로 반영함을 보장합니다.
장점: 레거시 Excel 버전과 호환되며 추가 기능이나 고급 기능이 필요 없습니다.
단점: 소스 데이터를 도우미 열을 통해 수정해야 하며, 데이터 업데이트 시 재계산이 덜 동적일 수 있습니다.
실용 팁: 반복되는 보고서의 경우, 도우미 열 공식을 동적으로 유지하거나 매크로를 사용하여 새로 고침을 자동화하는 것을 고려하세요.
Power Pivot (데이터 모델) - DAX를 사용하여 피벗 테이블에서 가중 평균 계산하기
최신 버전의 Excel에서는 Power Pivot 추가 기능(데이터 모델로도 알려짐)이 DAX 공식(Data Analysis Expressions)을 사용하여 새로운 계산 옵션을 해제합니다. 이를 통해 기본 데이터에 추가 도우미 열을 만들지 않고도 피벗 테이블에서 직접 가중 평균을 계산할 수 있습니다.
적용 시나리오: 큰 데이터 세트 또는 연결된 테이블로 작업할 때, 그리고 데이터와 함께 자동으로 새로 고침되는 계산을 원할 때 이상적입니다. 이 방법은 깨끗한 소스 테이블을 유지하는 것이 선호되는 비즈니스 분석 및 대시보드에서 특히 유용합니다.
지침:
- Power Pivot 추가 기능 활성화 파일 > 옵션 > 추가 기능으로 이동합니다. 관리 드롭다운에서 COM 추가 기능을 선택하고, 이동을 클릭한 후 Power Pivot을 체크합니다.
- Power Pivot에 데이터 추가하기 워크시트에서 테이블을 선택한 후 Power Pivot > 관리를 클릭하여 Power Pivot 창을 엽니다.
- Power Pivot에서 피벗 테이블 생성하기 Power Pivot 창에서 홈 > 피벗 테이블로 이동합니다.
그런 다음 삽입 위치를 선택합니다(예: 기존 워크시트)하고 확인을 클릭합니다.
- 피벗 테이블 작성 및 측정값 추가하기 새로 생성된 피벗 테이블 필드 목록에서 필드를 적절한 영역으로 드래그합니다. 그런 다음 테이블 이름을 마우스 오른쪽 버튼으로 클릭하고 측정값 추가를 선택합니다.
- 측정값 정의하기 측정값 대화 상자에서:
- 측정값을 이름 지정합니다(예: 가중 평균 가격).
- 다음 DAX 표현식을 입력하여 가중 평균을 계산합니다.
=SUMX(Table1, Table1[Weight] * Table1[Price]) / SUM(Table1[Weight])(Table1, [Weight], [Price]를 실제 테이블 및 필드 이름으로 교체하세요.) - 확인을 클릭하여 추가합니다.
- 피벗 테이블에서 측정값 사용하기 새로 추가된 측정값은 필드 목록에 나타나며 다른 필드처럼 값 영역으로 드래그할 수 있습니다.
팁 및 문제 해결:
- DAX 공식은 대소문자를 구분하지 않지만 필드/테이블 이름은 모델과 일치해야 합니다.
- 기본 데이터를 변경하면 피벗 테이블의 측정값이 자동으로 새로 고쳐집니다.
- 빈칸이나 예상치 못한 결과가 나오는 경우, 무게 값이 0이거나 누락되지 않았는지 확인하고 데이터 모델이 적절하게 새로 고쳐지는지 확인하세요.
장점: 소스 데이터를 수정할 필요가 없으며, 데이터 변경 시 계산이 즉시 업데이트되고 고급 요약이 가능합니다.
단점: Power Pivot는 모든 Excel 버전에서 사용할 수 없으며 초기 설정이 필요할 수 있으며, DAX에 익숙하지 않은 사용자는 학습 곡선을 겪을 수 있습니다.
Kutools AI로 엑셀의 마법을 풀다
- 스마트 실행: 셀 작업 수행, 데이터 분석 및 차트 생성 - 간단한 명령어로 모든 것을 처리합니다.
- 사용자 정의 수식: 작업을 간소화하기 위한 맞춤형 수식을 생성합니다.
- VBA 코딩: 손쉽게 VBA 코드를 작성하고 실행합니다.
- 수식 해석: 복잡한 수식도 쉽게 이해할 수 있습니다.
- 텍스트 번역: 스프레드시트 내 언어 장벽을 허물어 보세요.
관련 기사:
최고의 오피스 생산성 도구
| 🤖 | Kutools AI 도우미: 데이터 분석에 혁신을 가져옵니다. 방법: 지능형 실행 | 코드 생성 | 사용자 정의 수식 생성 | 데이터 분석 및 차트 생성 | Kutools Functions 호출… |
| 인기 기능: 중복 찾기, 강조 또는 중복 표시 | 빈 행 삭제 | 데이터 손실 없이 열 또는 셀 병합 | 반올림(수식 없이) ... | |
| 슈퍼 LOOKUP: 다중 조건 VLOOKUP | 다중 값 VLOOKUP | 다중 시트 조회 | 퍼지 매치 .... | |
| 고급 드롭다운 목록: 드롭다운 목록 빠르게 생성 | 종속 드롭다운 목록 | 다중 선택 드롭다운 목록 .... | |
| 열 관리자: 지정한 수의 열 추가 | 열 이동 | 숨겨진 열의 표시 상태 전환 | 범위 및 열 비교 ... | |
| 추천 기능: 그리드 포커스 | 디자인 보기 | 향상된 수식 표시줄 | 통합 문서 & 시트 관리자 | 자동 텍스트 라이브러리 | 날짜 선택기 | 데이터 병합 | 셀 암호화/해독 | 목록으로 이메일 보내기 | 슈퍼 필터 | 특수 필터(굵게/이탤릭/취소선 필터 등) ... | |
| 15대 주요 도구 세트: 12 가지 텍스트 도구(텍스트 추가, 특정 문자 삭제, ...) | 50+ 종류의 차트(간트 차트, ...) | 40+ 실용적 수식(생일을 기반으로 나이 계산, ...) | 19 가지 삽입 도구(QR 코드 삽입, 경로에서 그림 삽입, ...) | 12 가지 변환 도구(단어로 변환하기, 통화 변환, ...) | 7 가지 병합 & 분할 도구(고급 행 병합, 셀 분할, ...) | ... 등 다양 |
Kutools for Excel과 함께 엑셀 능력을 한 단계 끌어 올리고, 이전에 없던 효율성을 경험하세요. Kutools for Excel은300개 이상의 고급 기능으로 생산성을 높이고 저장 시간을 단축합니다. 가장 필요한 기능을 바로 확인하려면 여기를 클릭하세요...
Office Tab은 Office에 탭 인터페이스를 제공하여 작업을 더욱 간편하게 만듭니다
- Word, Excel, PowerPoint에서 탭 편집 및 읽기를 활성화합니다.
- 새 창 대신 같은 창의 새로운 탭에서 여러 파일을 열고 생성할 수 있습니다.
- 생산성이50% 증가하며, 매일 수백 번의 마우스 클릭을 줄여줍니다!
모든 Kutools 추가 기능. 한 번에 설치
Kutools for Office 제품군은 Excel, Word, Outlook, PowerPoint용 추가 기능과 Office Tab Pro를 한 번에 제공하여 Office 앱을 활용하는 팀에 최적입니다.
- 올인원 제품군 — Excel, Word, Outlook, PowerPoint 추가 기능 + Office Tab Pro
- 설치 한 번, 라이선스 한 번 — 몇 분 만에 손쉽게 설정(MSI 지원)
- 함께 사용할 때 더욱 효율적 — Office 앱 간 생산성 향상
- 30일 모든 기능 사용 가능 — 회원가입/카드 불필요
- 최고의 가성비 — 개별 추가 기능 구매 대비 절약