엑셀 피벗 테이블 만들기: 단계별 사용법

피벗 테이블은 수식 없이 표의 행을 Region 같은 분류로 묶고 그룹마다 Sales 같은 숫자의 합계를 냅니다. 만들려면 데이터의 셀을 클릭하고 삽입 > 피벗 테이블로 가서 확인을 누른 뒤 Region을 행으로, Sales를 값으로 끌어 놓으세요. 아래 시트는 피벗 테이블이 아닙니다. 같은 요약을 수식으로 만들었으므로 합계가 바뀌는 것을 지켜볼 수 있습니다.

수식초기화

F2fx

=SUMIF($A$2:$A$9,E2,$C$2:$C$9)

	A	B	C	D	E	F	G
1	Region	Product	Sales		Region	Sales	% of total
2	North	Apple	120		North	455	49%
3	South	Pear	85		South	305	33%
4	North	Pear	240		East	170	18%
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

셀을 클릭하면 수식이 보입니다. 숫자나 수식을 바꾸면 시트가 다시 계산됩니다.

UNIQUE가 각 지역을 한 번씩 나열하고 SUMIF가 합계를 냅니다. North 455, South 305, East 170이며, 전체 930의 49%, 33%, 18%입니다. C3을 185로 바꾸면 South 합계와 세 비율이 한꺼번에 따라 바뀝니다. 피벗 테이블도 같은 숫자를 보여 주지만 새로 고친 뒤에야 보여 줍니다.

피벗 테이블 만드는 방법

시작하기 전에 원본 데이터를 확인하세요. 모든 열에 이름이 있는 머리글 행 하나, 행마다 레코드 하나, 중간에 빈 행이나 열이 없을 것, 부분합 행이 없을 것.

1. 데이터의 아무 셀이나 클릭합니다.
2. 삽입 > 피벗 테이블로 갑니다(일부 버전에서는 삽입 > 피벗 테이블 > 테이블/범위에서).
3. 엑셀이 범위를 채웁니다. 새 워크시트를 고르고 확인을 누릅니다.
4. 빈 피벗 테이블이 나타나고 오른쪽에 열 머리글을 나열한 피벗 테이블 필드 창이 나옵니다.
5. Region을 행 상자로, Sales를 값 상자로 끌어 놓습니다. 피벗 테이블이 각 지역을 한 번씩, 그 옆에 합계 : Sales를 보여 주고 총합계 행을 붙입니다.
6. 보이는 것을 바꾸려면 상자 사이로 필드를 끌거나 창 밖으로 끌어내세요.

어디서 시작할지 모르겠다면 삽입 > 추천 피벗 테이블이 데이터에 맞는 레이아웃 몇 개를 보여 줍니다. Mac에서도 메뉴는 같습니다: 삽입 > 피벗 테이블.

행, 열, 값, 필터

피벗 테이블 필드 창에는 상자가 네 개 있으며, 모든 피벗 테이블은 어떤 열을 어떤 상자에 넣느냐의 선택입니다:

• 행: 왼쪽 아래로 놓이는 분류로, 서로 다른 값마다 한 행입니다(Region).
• 열: 위쪽 가로로 놓이는 분류로, 서로 다른 값마다 한 열입니다(Product).
• 값: 조합마다 계산할 숫자입니다. 숫자 열의 기본값은 합계이며, 개수, 평균, 최대, 최소 등은 값 필드 설정에 있습니다.
• 필터: 피벗 테이블 전체를 필터링하는 필드로, 위에 드롭다운으로 표시됩니다.

Region을 행에, Product를 열에, Sales를 값에 넣으면 위 데이터의 피벗 테이블은 이렇게 보입니다:

Sum of Sales   Column Labels
Row Labels     Apple   Pear   Grand Total
East              60    110           170
North            215    240           455
South            150    155           305
Grand Total      425    505           930

이 레이아웃의 수식 버전은 UNIQUE로 지역을 아래로, TRANSPOSE(UNIQUE())로 제품을 옆으로 나열하고, 두 목록을 조건으로 받는 SUMIFS 하나로 격자의 모든 셀을 계산합니다:

수식초기화

F2fx

=SUMIFS(C2:C9,A2:A9,E2:E4,B2:B9,F1:G1)

	A	B	C	D	E	F	G
1	Region	Product	Sales			Apple	Pear
2	North	Apple	120		North	215	240
3	South	Pear	85		South	150	155
4	North	Pear	240		East	60	110
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

셀을 클릭하면 수식이 보입니다. 숫자나 수식을 바꾸면 시트가 다시 계산됩니다.

E2는 North, South, East를 아래로, F1은 Apple과 Pear를 옆으로 분산하고, F2의 SUMIFS가 그 사이의 3×2 격자를 채웁니다. 지역과 제품의 쌍마다 합계가 하나씩입니다. B5를 Apple에서 Pear로 바꾸면 East의 두 셀이 모두 바뀝니다. 여기서 순서는 값이 처음 나온 순서이며, 피벗 테이블은 레이블을 가나다(알파벳)순으로 정렬합니다.

합계 대신 개수, 평균, 비율

피벗 테이블에서 값 상자의 필드를 클릭하고 값 필드 설정을 고르세요. 값 요약 기준 탭에서 합계, 개수, 평균, 최대, 최소를 바꾸고, 값 표시 형식 탭에서 숫자를 총합계 비율, 열 합계 비율, 누계 등으로 바꿉니다. 각각에 대응하는 수식이 있습니다:

수식초기화

E2fx

=UNIQUE(A2:A9)

	A	B	C	D	E	F	G
1	Region	Product	Sales		Region	Orders	Average
2	North	Apple	120		North	3	151.7
3	South	Pear	85		South	3	101.7
4	North	Pear	240		East	2	85.0
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

셀을 클릭하면 수식이 보입니다. 숫자나 수식을 바꾸면 시트가 다시 계산됩니다.

North는 주문 3건에 평균 151.7, South는 3건에 평균 101.7, East는 2건에 평균 85.0입니다. 총합계 비율 열은 이 페이지의 첫 번째 시트에 있습니다.

제품 하나로 요약 필터링하기

필터 상자는 피벗 테이블 위에 드롭다운을 놓습니다. 수식 버전은 드롭다운 목록이 있는 셀과, SUMIF에 조건을 하나 더하는 SUMIFS입니다. F1에서 제품을 고르세요:

수식초기화

F1fx

	A	B	C	D	E	F
1	Region	Product	Sales		Product:	Apple▾ApplePear
2	North	Apple	120
3	South	Pear	85		Region	Sales
4	North	Pear	240		North	215
5	East	Apple	60		South	150
6	South	Apple	150		East	60
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

셀을 클릭하면 수식이 보입니다. 숫자나 수식을 바꾸면 시트가 다시 계산됩니다.

Apple을 고르면 North 215, South 150, East 60이 나옵니다. Pear를 고르면 240, 155, 110으로 바뀝니다. 더 많은 조건은 SUMIFS를, 엑셀에서 목록을 추가하는 방법은 드롭다운 목록 페이지를 참고하세요.

피벗 테이블 새로 고침

피벗 테이블은 원본 데이터의 사본(피벗 캐시)을 갖고 있어서 원본의 셀이 바뀌어도 다시 계산하지 않습니다. 데이터를 고친 뒤에는:

• 피벗 테이블의 아무 곳이나 오른쪽 클릭해 새로 고침을 고르거나, Windows에서 Alt+F5를 누르세요.
• 데이터 > 모두 새로 고침(Ctrl+Alt+F5)은 통합 문서의 모든 피벗 테이블을 새로 고칩니다.
• 파일을 열 때마다 새로 고치려면 피벗 테이블을 오른쪽 클릭해 피벗 테이블 옵션을 고르고, 데이터 탭에서 파일을 열 때 데이터 새로 고침에 체크하세요.

원본 범위 아래에 추가한 새 행은 새로 고쳐도 포함되지 않습니다. 피벗 테이블 분석 > 데이터 원본 변경에서 범위를 바꾸거나, 더 좋은 방법으로 피벗 테이블을 만들기 전에 원본을 표로 바꾸세요. 데이터를 선택하고 Ctrl+T를 누르거나 삽입 > 표를 쓰면 됩니다. 표는 행을 추가하면 늘어나고, 피벗 테이블은 다음 새로 고침에서 그 행을 가져옵니다.

수식 하나로 모든 지역 합계 구하기

수식초기화

F2fx

	A	B	C	D	E	F
1	Region	Product	Sales		Region	Sales
2	North	Apple	120		North
3	South	Pear	85		South
4	North	Pear	240		East
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

셀을 클릭하면 수식이 보입니다. 숫자나 수식을 바꾸면 시트가 다시 계산됩니다.

직접 해 보세요: F2에서 수식 하나로 E2:E4에 나열된 모든 지역의 매출 합계를 구하세요.

확인정답 보기

답은 455, 305, 170을 분산합니다. SUMIF에 목록 E2:E4 전체를 조건으로 주면 지역마다 합계 하나씩을 반환하므로 아래로 채울 것이 없습니다. Excel 2019 이하에는 UNIQUE도 분산도 없으므로, E2:E4에 지역을 입력하고 =SUMIF($A$2:$A$9,E2,$C$2:$C$9)를 아래로 채우세요. $ 기호가 없으면 범위가 행마다 아래로 움직여 합계가 틀리게 나옵니다.

GROUPBY와 PIVOTBY: 수식 하나로 만드는 피벗 테이블

Microsoft 365용 엑셀에는 수식 하나로 요약 전체를 만들고, 새로 고침 없이 다른 수식처럼 다시 계산되는 함수가 두 개 있습니다. 최신 Microsoft 365 구독이 필요합니다. 위 데이터에서는:

=GROUPBY(A2:A9,C2:C9,SUM)

East      170
North     455
South     305
Total     930

=PIVOTBY(A2:A9,B2:B9,C2:C9,SUM)

          Apple   Pear   Total
East         60    110     170
North       215    240     455
South       150    155     305
Total       425    505     930

GROUPBY는 행 필드, 값, 함수(SUM, COUNTA, AVERAGE, MAX, PERCENTOF)를 받습니다. PIVOTBY는 그 사이에 열 필드를 더합니다. 둘 다 피벗 테이블처럼 레이블을 정렬하고 합계 행을 붙입니다.

피벗 테이블과 수식, 무엇을 쓸까

데이터를 탐색하고 질문에 한 번 답할 때는 피벗 테이블을 쓰세요. 보고서에 들어가고, 다른 수식에 값을 넘기고, 항상 최신이어야 하는 요약에는 수식을 쓰세요. 피벗 테이블의 숫자를 확인하려면 셀 하나를 SUMIFS로 다시 만들어 보세요. 둘이 다르면 대개 피벗 테이블을 새로 고쳐야 하거나 원본 범위가 너무 짧습니다.