전체가 아닌 상위 그룹 안에서 항목별 비중을 구하려면?
각 지역에서 항목별 매출이 차지하는 비중을 구해 보세요. 지역·항목별 합계와 지역 전체 합계를 따로 저장한 뒤 연결해 계산합니다.
지역 안에서 각 항목이 차지하는 비중을 보려면 해당 지역의 합계로 나눠야 합니다. JOIN SHEET에서 지역·항목별 합계와 지역 전체 합계를 따로 저장한 뒤 연결하세요. 각 항목의 금액 옆에 그 지역의 합계를 붙여 비율을 계산할 수 있습니다.
North와 South의 분모가 다릅니다
아래는 가상의 판매 내역입니다. Region은 지역, Item은 항목, Amount는 금액이며 통화는 모두 같습니다. 같은 지역·항목에 여러 거래가 있고, Zero 지역에는 금액이 0인 기록이 있습니다.
| Region | Item | Amount |
|---|---|---|
| North | A | 20 |
| North | A | 30 |
| North | B | 150 |
| South | A | 60 |
| South | B | 40 |
| Zero | A | 0 |
North의 총액은 200, South는 100입니다. North의 A 매출 50을 전체 300으로 나누면 약 16.67%지만, North 안에서의 비중은 25%입니다. 비중을 구하기 전에 전체 매출과 지역 매출 중 어느 금액을 기준으로 삼을지 정하세요.
항목별 합계와 지역 합계를 따로 저장하세요
- Sales를 클릭하고 ‘결과 정리’에서 ‘요약’을 누릅니다. ‘그룹화 기준’에서 Region과 Item을 하나씩 선택하세요. ‘집계 방식’을 ‘합계’로 바꾸고 ‘항목’은 Amount, ‘결과 항목 이름’은 ItemSales로 지정합니다. ‘적용’을 누른 뒤 결과 5행을 ‘결과 저장’으로 저장합니다. 이 항목별 합계표를 이후 설명에서는 ItemTotals라고 부르겠습니다.
- ‘결과 정리’를 다시 열어 기존 요약의 ‘그룹화 기준’에서 Item을 제거하고 Region만 남깁니다. Amount의 합계는 유지하되 ‘결과 항목 이름’을 ParentSales로 바꿉니다. ‘적용’을 누르고 North=200, South=100, Zero=0인지 확인한 뒤 앞서 저장한 ItemTotals와 별도 파일로 저장합니다. 이 지역 합계표는 RegionTotals라고 부르겠습니다.
- 기간을 한정하거나 취소 거래를 제외한다면 두 합계를 만들 때 같은 조건을 적용합니다. 항목별 매출만 기간을 줄이고 지역 총액은 그대로 두면 비중이 달라집니다.
첫 번째 요약을 유지하고 그 아래에 두 번째 요약을 추가하는 방법도 있습니다. 이 경우 먼저 항목별 합계 파일을 저장한 뒤, Region으로 묶어 원본 Amount가 아닌 앞 단계의 ItemSales를 합산하세요. 위 절차는 기존 요약을 수정해 두 합계를 따로 저장하는 방법입니다. 지역 합계표에는 Region마다 정확히 한 행만 있어야 합니다.
각 항목에 자기 지역의 합계 붙이기
새 작업에 저장한 항목별 합계표 ItemTotals와 지역 합계표 RegionTotals를 불러옵니다. 두 표의 Region을 연결하고 연결 방식은 ‘일치하는 데이터만’을 선택하세요. ItemTotals를 결과 기준으로 선택하고, RegionTotals의 Region 칼럼은 중복되므로 숨깁니다.
‘결과 정리’에서 ‘계산’을 추가합니다. A는 ItemSales, B는 ParentSales로 지정하고 ‘비율 (%) · A ÷ B × 100’을 선택하세요. ‘결과 항목 이름’에는 SharePct를 입력합니다. ‘정렬’을 추가해 Region을 오름차순으로 지정하고 같은 정렬의 ‘정렬 기준 추가’로 Item도 오름차순으로 지정한 뒤 ‘적용’을 누릅니다.
각 지역 안에서 100%가 되는지 확인하세요
| Region | Item | ItemSales | ParentSales | SharePct |
|---|---|---|---|---|
| North | A | 50 | 200 | 25 |
| North | B | 150 | 200 | 75 |
| South | A | 60 | 100 | 60 |
| South | B | 40 | 100 | 40 |
| Zero | A | 0 | 0 | ∅ |
North는 25+75=100%, South는 60+40=100%입니다. 각 지역 안에서 구한 비중이므로 지역별로 더해 100%가 되는지 확인하세요. 표 전체의 비율을 더하는 것은 의미가 다릅니다. SharePct의 25는 이미 25%를 뜻하므로 다시 100을 곱하지 않습니다.
Zero의 0÷0은 빈 값(∅)입니다. 지역 합계가 0이면 비중을 계산할 수 없으므로 0%로 해석하지 말고 원본 금액과 집계 범위를 확인하세요. 서로 다른 시점에 만든 파일을 연결해 지역 합계가 누락되면 ‘일치하는 데이터만’으로 연결하는 과정에서 그 지역이 빠집니다. 두 파일을 같은 원본과 조건으로 만들었는지, 항목별 합계표의 5행이 결과에도 남아 있는지 확인합니다.
결과에서는 지역·항목별 비율을 세로로 나열해 볼 수 있습니다. 원문에 나온 피벗 메뉴를 사용하거나 항목을 가로로 펼치는 방식은 아닙니다.
내 파일로 시작해 보세요
JOIN SHEET에서 시작하기 ↗Excel·CSV 작업은 PC에서 이용해 주세요.
새 창에서 열리며 작업 중인 데이터를 바꾸지 않습니다.
예제 확인일:
출처
Mac Excel 2016 Pivot Table - How to show values as percent of parent의 질문을 참고해 별도의 가상 데이터로 구성했습니다.
