엑셀 피벗 계산 필드 총합계가 일반 수식과 다른 이유
매장 A에서 1,000원짜리 물건을 2개 팔고(2,000원), 매장 B에서 3,000원짜리 물건을 1개 팔았다면(3,000원) 전체 매출 합계는 당연히 5,000원입니다. 그런데 만약 두 매장의 단가를 먼저 합쳐 4,000원을 만들고, 수량도 먼저 합쳐 3개를 만든 뒤 마지막에 4,000원 × 3개를 곱해 버리면 12,000원이라는 엉뚱한 숫자가 튀어나옵니다.
실무에서 원본 시트를 건드리지 않으려고 피벗 테이블 안에 엑셀 피벗 계산 필드(=단가 * 수량)를 넣었다가, 개별 줄은 맞는데 맨 아래 총합계 행이 터무니없이 크게 나와 당황하는 경우가 많습니다. 이는 버그가 아니라 피벗 테이블이 수식을 처리하는 고유한 연산 순서 때문입니다.
피벗 계산 필드를 넣었더니 총합계 숫자가 이상하게 커지는 현상
엑셀 피벗 계산 필드는 피벗 테이블 분석 → 필드, 항목 및 집합 → 계산 필드에서 원본에 없는 새 계산 열을 피벗 안에 바로 추가하는 편리한 기능입니다.
문제는 =단가 * 수량처럼 두 열을 곱하는 식을 계산 필드로 넣었을 때 벌어집니다. 품목별 단일 거래 행에서는 숫자가 맞게 나오다가도, 품목이 여러 건 묶이는 소계(부분합)나 맨 아래 총합계 칸에서는 위 행들의 금액을 단순히 아래로 더한 값이 나오지 않습니다. 겉보기에 총합계 행이므로 당연히 위 셀들의 합(SUM)이 나올 것이라 기대하기 쉽지만, 실제로는 피벗이 자체 수식을 총합계 칸에서 새로 돌립니다.
엑셀 피벗 계산 필드의 ‘선 합산 후 연산’ 규칙 원리
일반 엑셀 표의 수식 열과 엑셀 피벗 계산 필드는 덧셈(합산)과 곱셈·나눗셈(수식)의 순서가 정반대입니다.
일반 수식 열: 행별 연산 후 합산 (Row-by-Row)
원본 데이터 옆에 일반 열로 =C2*D2를 써 내려가면, 1행의 단가×수량을 구하고 2행의 단가×수량을 구한 뒤 피벗에서 그 결과를 더합니다. 수식으로 표현하면 (단가1×수량1) + (단가2×수량2)입니다.
피벗 계산 필드: 각 필드 합산 후 수식 적용 (Aggregate First)
반면 피벗 계산 필드는 해당 영역(예: 총합계)에 모인 모든 행의 단가를 먼저 전부 더하고, 수량도 먼저 전부 더한 뒤 마지막에 두 합계를 곱합니다((단가1+단가2) × (수량1+수량2)). 그래서 단가×수량, 할인액 계산 같은 곱셈식에서는 총합계가 기하급수적으로 부풀어 오릅니다. 반대로 =이익/매출 같은 비율이나 가중평균은 합계 이익을 합계 매출로 나눈 값이 되므로 오히려 계산 필드를 써야 정확한 전체 비율이 나옵니다. 매장 예시와 다른 점은, 엑셀은 사용자가 계산 필드 창에 SUM을 안 적어도 내부적으로 각 필드에 SUM()을 먼저 감싸서 계산한다는 점입니다.
원본 수식 열 vs 피벗 계산 필드 vs 파워 피벗 비교
| 구분 | 원본 표에 일반 수식 열 추가 | 엑셀 피벗 계산 필드 | 파워 피벗 DAX 측정값(SUMX) |
|---|---|---|---|
| 연산 순서 | 행마다 먼저 계산 후 합산 | 각 필드를 먼저 합산 후 수식 적용 | 행별 계산 후 합산·집계 모두 제어 가능 |
단가 × 수량 총합계 |
정확함 (개별 금액의 합) | 틀림 (합계 단가 × 합계 수량) | 정확함 (SUMX 반복 함수 지원) |
이익 ÷ 매출 (비율·마진율) |
피벗 합계 시 단순 합산되어 틀림 | 정확함 (총이익 ÷ 총매출) | 정확함 (DIVIDE 측정값 지원) |
| 파일 크기 및 난이도 | 원본 열이 늘어남 (가장 쉬움) | 원본 변경 없음 (쉬움) | 원본 변경 없음 (데이터 모델 학습 필요) |
비율·단가×수량 총합계를 정확히 맞추는 실무 해결 순서
내가 구하려는 지표가 ‘곱셈의 합계(매출액)’인지 ‘나눗셈의 비율(이익률)’인지 먼저 가른 뒤 아래 순서대로 배치하면 총합계 왜곡 없이 정확한 보고서를 뽑을 수 있습니다.
피벗 계산 필드·총합계 오류 해결 순서
- 곱셈(단가×수량)이나 조건식 결과라면 원본 표에 보조 열 추가:
단가 × 수량,IF조건별 가산점처럼 행마다 따로 계산한 뒤 더해야 하는 값은 피벗 계산 필드를 지우고, 원본 표(Ctrl+T) 맨 오른쪽에=단가*수량수식 열을 추가한 뒤 피벗을 새로 고쳐 값 영역에 넣습니다. - 비율(이익률·달성률)이라면 피벗 계산 필드 활용: 반대로 “총이익 ÷ 총매출”이나 “목표 달성률”을 구할 때는 원본에 비율 열을 만들어 피벗으로 합치면 비율끼리 더해져 300%가 넘는 오류가 납니다. 이때야말로 엑셀 피벗 계산 필드에
=이익/매출을 넣어 각 구간과 총합계가 정확한 가중 비율로 나오게 합니다. - 계산 필드의 곱셈이 꼭 필요할 때 총합계 오해 차단: 구조상 계산 필드를 유지해야 한다면,
피벗 테이블 분석 → 디자인 → 총합계에서 오해를 부르는 열/행 총합계를 끄거나 파워 피벗의SUMX측정값으로 전환합니다. #DIV/0!오류 방어: 나눗셈 계산 필드를 쓸 때 매출이0인 품목이 있으면 에러가 뜨므로=IFERROR(이익/매출, 0)또는 피벗 테이블 옵션의오류 값 표시체크를 활용해 깔끔하게 가려 줍니다.
정리하자면 “행별로 구해서 더하는 값(금액)”은 원본 열에서, “먼저 더한 뒤 나누는 값(비율·단가 평균)”은 계산 필드에서 처리한다는 원칙 하나만 기억해도 피벗 테이블 보고서의 90% 이상 숫자가 어긋나지 않습니다.
핵심 정리
엑셀 피벗 계산 필드는 모든 셀에서 개별 필드의 합계(SUM)를 먼저 구한 뒤에 사칙연산을 실행합니다. 이 원리 때문에 이익 ÷ 매출 같은 전체 비율을 구할 때는 가장 정확한 도구가 되지만, 단가 × 수량 같은 곱셈 총합계는 크게 왜곡되므로 곱셈·조건식 지표는 원본 시트 열에서 먼저 계산한 뒤 피벗으로 집계해야 합니다.
재발 방지 체크리스트
재발 방지 체크리스트
- 피벗 계산 필드에 곱셈(
*) 수식을 넣었을 때 맨 아래총합계행이 개별 행들의 합과 일치하는지 검산하기 단가 × 수량이나IF조건 계산은 계산 필드가 아닌 원본 표의 일반 수식 열로 먼저 계산하기마진율,달성률같은 비율 지표는 원본 열을 합산하지 말고 피벗 계산 필드로총이익 ÷ 총매출구하기- 분모가 0일 때의
#DIV/0!오류를 막기 위해 피벗 옵션의 오류 값 빈칸 표시 설정 확인하기









