엑셀 고급 피벗 테이블: 그룹화, 값 표시 형식, 계산 필드, 상위 N 필터

피벗 테이블(PivotTable)은 몇 번 끌어다 놓으면 만들어져요. 하지만 누군가 실제로 던진 질문에 답하게 하려면 더 세밀하게 다뤄야 해요. 날짜별이 아니라 분기별 매출, 지역별 금액이 아니라 지역별 비중, 원본 데이터에 없는 이익률, 실적이 가장 좋은 석 달만 보기 같은 것들이죠. 이 글에서는 이런 기능을 하나씩 익히고, 피벗 테이블이 왜 가끔 예전 숫자를 보여 주는지도 알아봐요.

예시는 2026년 1월부터 6월까지의 판매 18건이에요. 건마다 날짜, 지역, 제품, 매출, 원가가 있어요. 엑셀 표로 저장해 두어서 새로 고칠 때 새 행도 함께 들어가요.

fx

	A	B	C	D	E
1	날짜	지역	제품	매출	원가
2	2026-01-08	동부	Core	4,200	2,520
3	2026-01-15	서부	Plus	3,100	1,705
4	2026-01-22	중부	Elite	2,600	1,170
5	2026-02-08	동부	Plus	3,900	2,145
6	2026-02-15	서부	Core	2,800	1,680
7	2026-02-22	중부	Core	3,300	1,980

따라 하려면 데이터를 입력하거나 내 데이터를 쓰세요. 데이터 안을 클릭하고 삽입› then 표› then 피벗 테이블을 고른 다음 확인을 클릭하면 돼요.

날짜를 분기별로 그룹화하기

날짜마다 한 행씩 나오면 너무 자세해서 읽기 어려워요. 그룹화하면 날짜가 기간 단위로 묶여요.

1. 날짜 필드를 행 영역에 넣으세요

   날짜를 행 영역으로 끌어다 놓으세요. Microsoft 365에서는 엑셀이 날짜를 알아서 월별로 묶는 경우가 많고, 그렇지 않으면 날짜마다 한 행씩 나와요. 어느 쪽이든 기간은 직접 고를 수 있어요.

2. 그룹화 대화 상자를 여세요

   피벗 테이블의 아무 날짜나 월을 마우스 오른쪽 단추로 클릭하고 그룹(Group)을 고르세요.

3. 분기를 고르세요

   단위 목록에서 분기를 클릭해 선택하고, 월을 클릭해 선택을 해제하세요. 항목은 클릭할 때마다 선택과 해제가 바뀌어요. 확인을 클릭하세요.

   fx

   	A	B
   1
   2
   3	행 레이블	합계 : 매출
   4	1사분기	31,800
   5	2사분기	34,800
   6	총합계	66,600

단위를 여러 개 선택하면 계층 구조가 만들어져요. 연과 분기를 함께 고르면 연도마다 그 안에 분기가 나와요. 데이터가 1년을 넘는다면 반드시 연도 넣으세요. 그러지 않으면 2025년 1사분기와 2026년 1사분기가 하나로 합쳐져요. 다시 날짜별로 보려면 기간을 마우스 오른쪽 단추로 클릭하고 그룹 해제를 고르세요.

그룹화할 수 없다는 메시지가 나오면 날짜 열에 진짜 날짜가 아닌 값이 섞여 있는 거예요. 대개 텍스트로 저장된 날짜나 빈 셀이에요. 원본을 고치고 새로 고친 다음 다시 시도하세요.

값을 전체 대비 비중으로 표시하기

"동부는 얼마나 팔았어?"와 "매출 중 동부 비중은 얼마야?"는 서로 다른 숫자가 필요한 질문이에요. 값 표시 형식(Show Values As)을 쓰면 원본 데이터는 그대로 두고 값 필드를 보여 주는 방식만 바꿀 수 있어요.

1. 지역별 매출을 요약하세요

   행 영역에서 날짜 필드를 빼고(엑셀이 자동으로 그룹화하면서 추가한 월이나 분기 필드가 있다면 그것도 함께), 지역을 넣으세요. 매출을 값 영역으로 끌어다 놓고, 매출을 값 영역에 한 번 더 끌어다 놓으세요. 합계 : 매출과 합계 : 매출2가 나란히 생겨요.

2. 두 번째 열을 비율로 표시하세요

   합계 : 매출2 열의 아무 숫자나 마우스 오른쪽 단추로 클릭하고 값 표시 형식을 가리킨 다음 총합계 비율(% of Grand Total)을 고르세요.

3. 머리글 이름을 바꾸고 정렬하세요

   합계 : 매출2 머리글 셀을 클릭하고 비중을 입력한 다음 Enter를 누르세요. 그리고 합계 : 매출 열의 숫자를 마우스 오른쪽 단추로 클릭하고 정렬› then 숫자 내림차순 정렬을 고르세요.

   fx

   	A	B	C
   1
   2
   3	행 레이블	합계 : 매출	비중
   4	동부	27,800	41.74%
   5	서부	21,900	32.88%
   6	중부	16,900	25.38%
   7	총합계	66,600	100.00%

금액 옆에 비율을 함께 두면 독자가 규모와 비중을 모두 볼 수 있어요. 비율은 기본으로 소수 둘째 자리까지 나와요. 바꾸려면 열을 마우스 오른쪽 단추로 클릭하고 값 필드 설정을 고른 다음 표시 형식을 클릭하세요.

열마다 비중 보기

열 영역에도 필드를 넣으면 무엇을 기준으로 비율을 낼지가 중요해져요. 값 영역에서 비중을 빼고, 날짜(분기로 그룹화된 그대로)를 열 영역으로 끌어다 놓고, 지역은 행 영역에 그대로 두세요. 그리고 합계 : 매출을 값 표시 형식› then 열 합계 비율로 바꾸세요. 이제 분기마다 합계가 100%가 되어서, 한 지역의 비중이 분기마다 어떻게 달라졌는지 비교할 수 있어요.

fx

	A	B	C	D
1
2
3	합계 : 매출	열 레이블
4	행 레이블	1사분기	2사분기	총합계
5	동부	41.51%	41.95%	41.74%
6	서부	33.33%	32.47%	32.88%
7	중부	25.16%	25.57%	25.38%
8	총합계	100.00%	100.00%	100.00%

자주 쓰는 옵션은 다음과 같아요.

마지막 세 가지는 고를 때 기준 필드(경우에 따라 기준 항목도)를 물어봐요. 날짜처럼 비교하거나 누적할 기준이 되는 필드예요.

계산 필드 추가하기

원본에는 매출과 원가는 있지만 이익률은 없어요. 계산 필드(Calculated Field)를 쓰면 데이터에 열을 추가하지 않고도 피벗 테이블 안에서 이익률을 계산할 수 있어요.

1. 피벗 테이블을 준비하세요

   빈 배치에서 시작하세요. 피벗 테이블 분석› then 동작› then 지우기› then 모두 지우기를 클릭한 다음, 제품을 행 영역에, 매출과 원가를 값 영역에 넣으세요.

2. 계산 필드 삽입 대화 상자를 여세요

   피벗 테이블 안을 클릭하고 피벗 테이블 분석› then 계산› then 필드, 항목 및 집합› then 계산 필드를 클릭하세요.

3. 필드 이름과 수식을 입력하세요

   이름에 이익률을 입력하세요. 수식에서 등호 뒤의 0을 지우고 아래 수식을 입력하세요. 필드 이름은 직접 입력해도 되고, 필드 목록에서 두 번 클릭해 넣어도 돼요.

   =(매출-원가)/매출복사

4. 추가하세요

   추가를 클릭한 다음 확인을 클릭하세요. 값 영역에 합계 : 이익률이 나타나요. 이 필드의 값 필드 설정을 열고 표시 형식을 클릭한 다음, 백분율을 고르고 소수 자릿수를 1로 하세요.

   fx

   	A	B	C	D
   1
   2
   3	행 레이블	합계 : 매출	합계 : 원가	합계 : 이익률
   4	Core	21,100	12,660	40%
   5	Elite	20,000	9,000	55%
   6	Plus	25,500	14,025	45%
   7	총합계	66,600	35,685	46.4%

총합계를 보세요. 세 제품 이익률의 평균인 46.7%가 아니라 46.4%예요. 계산 필드는 언제나 피벗 테이블의 각 행에서 필드의 합계로 계산해요. 여기서는 (66,600 − 35,685) / 66,600이죠. 비율을 낼 때는 이게 맞는 방식이에요. 매출이 큰 제품이 더 큰 비중으로 반영되니까요.

같은 규칙 때문에 틀린 결과가 나오는 계산 필드도 있어요. 원본에 단가와 수량이 있을 때 =단가*수량이라는 필드를 만들면, 행마다 단가의 합계와 수량의 합계를 곱하게 돼요. 이건 매출이 아니에요. 이렇게 행 단위로 계산해야 하는 값은 원본 데이터에 열을 추가해서 계산하세요.

수식으로 검증하기

새 계산 필드를 믿기 전에 피벗 테이블 밖에서 숫자 하나를 확인해 보세요. 데이터가 A1:E19에 있다면 다음 수식이 Elite의 이익률을 구해요.

=(SUMIFS(D2:D19,C2:C19,"Elite")-SUMIFS(E2:E19,C2:C19,"Elite"))/SUMIFS(D2:D19,C2:C19,"Elite")복사

결과는 0.55예요. (20,000 − 9,000) / 20,000, 즉 55%로 피벗 테이블과 같아요.

상위 N개만 남기기

상위 10(Top 10) 필터는 값이 가장 큰(또는 가장 작은) 항목만 남겨요. 이름은 상위 10이지만 개수는 직접 정할 수 있어요.

1. 월별 매출을 표시하세요

   다시 모두 지우기를 클릭하고 날짜를 행 영역에, 매출을 값 영역에 넣으세요. 날짜가 월별로 표시되지 않으면 날짜를 마우스 오른쪽 단추로 클릭하고 그룹을 고른 다음 월만 선택하세요.

2. 값 필터를 여세요

   행 레이블 옆의 화살표를 클릭하고 값 필터› then 상위 10을 고르세요.

3. 필터를 설정하세요

   대화 상자에서 상위는 그대로 두고 10을 3으로 바꾸고, 항목도 그대로 두고, 마지막 상자가 합계 : 매출인지 확인하세요. 확인을 클릭하세요.

4. 결과를 정렬하세요

   필터는 정렬까지 해 주지 않아요. 숫자를 마우스 오른쪽 단추로 클릭하고 정렬› then 숫자 내림차순 정렬을 골라 가장 좋은 달이 맨 위에 오게 하세요.

   fx

   	A	B
   1
   2
   3	행 레이블	합계 : 매출
   4	5월	12,100
   5	3월	11,900
   6	4월	11,400
   7	총합계	35,400

총합계를 보세요. 66,600이 아니라 35,400이에요. 필터가 걸린 피벗 테이블은 보이는 항목만 합계를 내요. 독자에게 전체 합계도 필요하다면 따로 셀을 두거나 피벗 테이블을 하나 더 만드세요. 대화 상자에는 항목 대신 고를 수 있는 백분율(합계의 상위 N%를 이루는 항목)과 합계(더해서 특정 값에 이르는 항목)도 있어요.

필터를 없애려면 행 레이블 옆의 화살표를 클릭하고 필드 이름이 붙은 필터 해제 명령을 고르세요.

피벗 테이블이 예전 숫자를 보여 주는 이유

피벗 테이블은 원본 셀을 직접 읽지 않아요. 피벗 테이블을 만들 때 엑셀은 데이터를 통합 문서 안에 저장되는 스냅숏인 피벗 캐시(pivot cache)로 복사하고, 피벗 테이블은 이 캐시로 만들어져요. 그래서 빠르기도 하고, 원본을 고쳐도 새로 고치기 전까지 아무것도 바뀌지 않기도 해요.

• 피벗 테이블 하나 새로 고치기: 피벗 테이블을 마우스 오른쪽 단추로 클릭하고 새로 고침(Refresh)을 고르거나 피벗 테이블 분석› then 데이터› then 새로 고침을 클릭하세요.
• 모두 새로 고치기: Ctrl+Alt+F5(모두 새로 고침)를 누르세요. 파워 쿼리 쿼리와 다른 연결도 함께 새로 고쳐져요.
• 파일을 열 때 새로 고치기: 피벗 테이블을 마우스 오른쪽 단추로 클릭하고 피벗 테이블 옵션을 고른 다음, 데이터 탭에서 파일을 열 때 데이터 새로 고침에 체크하세요.

같은 표로 만든 피벗 테이블들은 캐시 하나를 함께 써요. 그래서 하나를 새로 고치면 같은 캐시를 쓰는 나머지도 함께 새로 고쳐져요. 표 안에 추가한 행은 다음 새로 고침 때 포함돼요. 원본이 일반 범위라면 그 아래에 입력한 행은 피벗 테이블 분석› then 데이터› then 데이터 원본 변경으로 범위를 늘리기 전까지 빠져요. 원본을 표로 만들어 두는 게 좋은 이유예요.

자주 하는 실수

• 새로 고치지 않은 피벗 테이블 읽기. 예전 스냅숏을 그대로 보여 줘요. 숫자를 읽거나 복사하거나 보내기 전에 모두 새로 고침을 하세요.
• 필터가 걸린 총합계를 전체로 착각하기. 상위 N 필터를 걸면 총합계는 보이는 항목만 더한 값이에요.
• 계산 필드에서 곱하기. =단가*수량은 행이 아니라 합계끼리 곱해요. 행 단위 계산은 원본 데이터에서 하세요.
• 연도 없이 월로만 그룹화하기. 서로 다른 해의 같은 달이 합쳐져요. 연도 함께 선택하세요.
• 아래로 복사가 안 되는 수식. 수식을 입력하다가 피벗 테이블 셀을 클릭하면 엑셀이 GETPIVOTDATA 수식을 만들어요. 이 수식은 복사해도 계속 같은 항목을 가리켜요. 셀 주소를 직접 입력하거나, 피벗 테이블 분석 탭의 옵션 옆 화살표를 클릭하고 GetPivotData 생성을 해제하세요.

연습

연습

내 데이터 요약하기

1. 날짜 열과 금액 열이 하나 이상 있는 판매, 지출, 활동 목록을 골라 Ctrl+T로 표로 만드세요.
2. 피벗 테이블을 만들고 날짜를 분기별로 그룹화하세요. 데이터가 1년을 넘으면 연과 월로 그룹화하세요.
3. 금액을 값 영역에 두 번 넣고 두 번째 것을 총합계 비율로 표시하세요. 머리글 이름도 바꾸세요.
4. 고객이나 제품 같은 다른 필드에 상위 10 값 필터를 걸어 상위 3개만 남기고 정렬하세요.
5. 이익률이나 비율 같은 계산 필드를 추가하고, 그 값 하나를 피벗 테이블 밖의 SUMIFS 수식으로 확인하세요.
6. 원본에서 금액 하나를 바꾸고 피벗 테이블이 바뀌지 않는 걸 확인한 다음 새로 고치세요.

실력 점검

• 피벗 테이블 안에서 날짜 필드를 분기별로 그룹화할 수 있나요?
• 합계 : 매출을 총합계 대비 비율로 표시되게 바꿀 수 있나요?
• 피벗 테이블을 값 기준 상위 3개 행만 남도록 필터링할 수 있나요?
• 계산 필드의 총합계가 평균이 아니라 합계끼리의 비율인 이유를 설명할 수 있나요?
• 피벗 테이블의 숫자가 새로 고치기 전까지 바뀌지 않는 이유를 설명할 수 있나요?