엑셀 피벗테이블

원본은 열 이름 한 줄과 거래 한 건당 한 행으로 만든다

피벗테이블이 읽기 좋은 원본은 사람이 보기 좋게 꾸민 보고서와 다르다. 부서 이름을 세로로 병합하거나 중간에 ‘10월 소계’를 끼우면 집계할 때 손질이 필요하다. 부서·채널·날짜는 각 행에 모두 적고, 소계와 총계 행은 원본에서 뺀다. 이미 더해 놓은 합계를 거래 금액과 함께 넣으면 같은 매출을 다시 더하게 된다.

아래는 설명을 위해 만든 가상 거래 6건이다. 첫 셀부터 마지막 셀까지 드래그해 복사한 뒤 빈 시트의 A1에 붙이면 된다. 금액 단위는 원이며 실제 매출 자료가 아니다. 이후의 모든 결과는 이 6건을 기준으로 계산했다.

자기 자료를 쓸 때도 머리글은 한 행, 열 이름은 빈칸 없이 서로 다르게, 한 열에는 같은 종류의 값을 넣는다. 원본 가운데 비어 있는 행·열과 병합 셀을 없애면 범위를 잘못 잡는 일을 줄일 수 있다. ‘금액’에는 ‘12만원’ 같은 설명 대신 120000이라는 숫자를 넣고 원 표시는 셀 서식으로 붙인다.

붙여넣은 금액이 숫자인지 보려면 빈 셀에서 =ISNUMBER(D2)를 입력한다. TRUE면 숫자, FALSE면 숫자로 바꿔야 할 텍스트다. 날짜도 =ISNUMBER(A2)가 TRUE여야 Excel의 날짜 값으로 집계할 수 있다. 이 준비 원칙은 Microsoft의 피벗테이블 원본 안내와 Exceljet의 원본 정리 예제에 따른다.

삽입 → 표를 만든 뒤 피벗테이블을 새 시트에 놓는다

예제에서는 A1:E7을 ‘표’로 바꾸고, 그 표를 원본으로 피벗테이블을 만든다. Excel의 표는 열 이름과 범위를 관리하는 기능이다. 셀에 테두리와 배경색만 입히는 것과 달리, 새 거래를 추가할 때 원본 범위도 늘려 쓸 수 있다.

1. A1:E7을 선택한다. Windows에서는 Ctrl+T, 메뉴로는 삽입 → 표를 누른다.
2. 범위에 머리글이 포함됐는지 보고 머리글 포함을 선택한 뒤 확인한다.
3. 표 안을 클릭하고 테이블 디자인 → 테이블 이름에서 이름을 SalesData로 바꾼다. 버전에 따라 ‘표 디자인’으로 표시된다.
4. 표 안의 셀을 선택한 채 삽입 → 피벗 테이블 → 테이블/범위에서를 누른다. 단추를 바로 누르면 만들기 창이 열리는 버전도 있다.
5. 표/범위에 SalesData가 들어 있는지 보고 새 워크시트 → 확인을 누른다.

새 시트에 빈 피벗테이블과 필드 목록이 나타나면 생성은 끝났다. 다음 절에서 필드를 배치하면 숫자가 나온다. 이 예제는 한 표만 쓰므로 ‘데이터 모델에 이 데이터 추가’는 선택하지 않고 진행한다. 여러 표의 관계를 연결하거나 중복을 뺀 고유 개수를 구하는 작업은 데이터 모델을 쓰는 별도 구성이다.

어떤 배치부터 시작할지 모르겠다면 삽입 → 추천 피벗 테이블에서 미리보기를 고르는 방법도 있다. 만들어진 뒤에는 아래의 행·열·값 영역을 바꿔 같은 보고서로 다듬으면 된다.

피벗테이블은 원본을 집계한 결과를 보여 주며, 행·열 배치를 바꿔도 원본 거래의 순서는 그대로다. 만드는 메뉴는 Microsoft의 Windows·Mac·웹별 생성 절차를 기준으로 했다.

행은 세로 분류, 열은 가로 비교, 값은 계산할 숫자다

‘부서별로 채널 매출을 비교한다’는 보고서라면 부서를 행, 채널을 열, 금액을 값에 놓는다. 필드 목록 위쪽의 이름을 아래쪽 네 영역으로 끌어 놓는다. 체크 표시만 하면 Excel이 영역을 골라 넣으므로, 처음에는 직접 끌어 놓는 편이 결과를 이해하기 쉽다.

이 배치의 결과는 다음과 같다. 열의 순서는 정렬에 따라 바뀌어도 각 부서와 채널이 만나는 금액은 같다.

영업1팀·온라인의 210,000원은 S001의 120,000원과 S004의 90,000원을 더한 값이다. 이처럼 피벗테이블의 각 숫자는 행 조건과 열 조건에 동시에 맞는 원본들을 집계한다. 채널을 열에서 행의 부서 아래로 옮기면 ‘영업1팀 → 온라인·매장’ 식으로 세로로 펼쳐진다. 필요 없는 필드는 아래 영역 밖으로 끌어낸다.

필드 창이 사라졌다면 피벗테이블 안을 클릭하고 피벗 테이블 분석 → 필드 목록을 누른다. 이 동작과 영역 간 이동은 Microsoft의 필드 배열·레이아웃 안내에서도 설명한다.

합계·개수·평균은 값 필드 설정에서 바꾼다

매출액이 필요하면 금액의 합계, 거래 건수가 필요하면 거래번호의 개수를 쓴다. 피벗테이블 숫자를 마우스 오른쪽 버튼으로 눌러 값 필드 설정 → 값 요약 기준에서 계산을 고른다. 아래쪽 값 영역의 필드 화살표로도 같은 창을 열 수 있다.

‘개수’는 해당 필드에서 비어 있지 않은 값을 센다. 이 예제는 거래마다 번호가 하나씩 있고 빈칸이 없어 거래 건수와 같다. 한 주문이 상품별로 여러 행에 나뉜 자료라면 주문번호의 단순 개수는 상품 행 수가 된다. 중복을 뺀 주문 수가 필요할 때는 데이터 모델의 ‘고유 개수’를 쓰거나 먼저 주문 단위로 자료를 정리한다. 요약 함수별 차이는 Microsoft 값 요약 기준 안내에 나와 있다.

금액을 넣었는데 ‘개수: 금액’이 나온다면 금액 열에 텍스트가 섞였는지 원본부터 살핀다. ‘합계’로 메뉴만 바꿔도 텍스트 숫자가 실제 숫자로 바뀌지는 않는다. 원본을 숫자로 변환한 뒤 새로고침하고, 값 요약 기준을 합계로 맞춘다. 숫자의 천 단위 쉼표는 같은 설정 창의 표시 형식 → 숫자 → 1000 단위 구분 기호에서 지정하면 필드 전체에 적용된다.

금액과 구성비를 나란히 보려면 금액을 두 번 넣는다

열 영역의 채널을 빼고 행에는 부서만 남긴 뒤, 금액을 값에 두 번 넣는다. 두 번째 금액의 값 필드 설정 → 값 표시 형식 → 총합계 비율을 고르면 금액과 비중이 함께 나온다. 첫 번째 금액은 합계 그대로 둔다.

필터가 없는 예제에서 영업1팀은 290,000 ÷ 600,000 = 48.33%, 영업2팀은 51.67%다. 채널을 필터 영역에 놓고 온라인만 선택하면 전체 합계도 460,000원으로 줄어든다. 그때 영업1팀 비중은 210,000 ÷ 460,000 = 45.65%가 된다. 보고서에 비중을 옮길 때는 어떤 필터를 적용했는지 함께 적는다.

가로로 채널을 비교하는 표에서 ‘열 합계 비율’을 고르면 각 채널 안의 부서 비중, ‘행 합계 비율’을 고르면 각 부서 안의 채널 비중이다. Exceljet의 동일 필드 중복 배치 예제처럼, 같은 금액을 여러 번 넣고 계산 방식을 다르게 지정할 수 있다.

월별 매출은 날짜를 ‘연’과 ‘월’로 함께 묶는다

부서별 월 매출을 보려면 행에는 부서, 열에는 날짜, 값에는 금액 합계를 놓는다. 날짜가 하루씩 나오면 피벗테이블의 날짜 항목을 마우스 오른쪽 버튼으로 눌러 그룹 → 연·월 → 확인을 선택한다. 월만 고르면 여러 해의 같은 월이 함께 집계되므로, 연도를 구분하는 보고서는 연도 같이 고른다.

1. 필터를 모두 해제한다. 앞 절에서 값에 두 번 넣었던 금액은 하나만 남기고, 남은 금액의 값 요약 기준은 합계, 값 표시 형식은 계산 없음으로 되돌린다.
2. 열 영역에 있던 채널은 빼고 날짜를 열로 끌어 넣는다.
3. 날짜 항목에서 그룹을 열고 연·월을 고른다. 이미 자동 그룹화됐다면 열 영역에 생긴 연·분기·월 중 필요한 연·월을 남긴다.

위 표는 연도·월 머리글을 읽기 쉽게 합쳐 쓴 예상 집계다. Excel은 연도와 월을 두 줄 머리글로 나눠 표시하기도 한다. 그룹 메뉴와 해제 방법은 Microsoft 그룹화 안내, 날짜를 열로 쓰는 예는 Exceljet의 월별 집계 예제에서 볼 수 있다.

‘선택한 내용을 그룹으로 묶을 수 없습니다’가 나오면 날짜 열에 텍스트·오류 값이 섞였는지 살핀다. 보이는 모양이 2026-11-01이어도 텍스트면 날짜 그룹화가 막힌다. 잘못된 날짜를 고치거나 집계 대상에서 제외한 뒤 새로고침한다. 빈 날짜도 정리한다. TrumpExcel의 현행 예제에서는 빈 날짜가 별도 구간으로 묶여 남는다. 빈칸을 임의의 날짜로 채우면 그 달 매출에 잘못 포함되므로, 실제 거래일을 복원하는 것이 먼저다.

필터는 볼 대상을 고르고, 보고서 레이아웃은 표 모양을 바꾼다

월별 표에서 온라인 매출만 보려면 채널을 필터 영역으로 옮긴 뒤, 피벗테이블 위쪽의 채널 드롭다운에서 온라인을 선택한다. 이 상태의 총합계는 460,000원이다. 여러 사람이 함께 볼 보고서는 피벗 테이블 분석 → 슬라이서 삽입 → 채널로 버튼을 만들어 두면 선택 조건이 눈에 잘 보인다. 전체로 되돌릴 때는 드롭다운의 ‘모두’ 또는 슬라이서의 필터 지우기 단추를 누른다. 두 기능의 경로는 Microsoft 피벗테이블 필터 안내에 따른다.

매출이 큰 부서를 위에 두려면 총합계 열의 금액 하나를 마우스 오른쪽 버튼으로 누르고 정렬에서 내림차순을 고른다. 이름 셀에서 정렬하면 가나다순이 되므로, 크기를 비교할 때는 숫자 셀에서 실행한다. Microsoft 정렬 안내도 총합계 숫자에서 크기순으로 정렬하는 예를 제시한다.

행에 부서와 채널을 함께 놓았는데 둘이 한 열에 섞여 보인다면 디자인 → 보고서 레이아웃 → 테이블 형식으로 표시를 고른다. 각 필드가 별도 열에 놓여 다른 문서로 옮기기 쉬워진다. Ablebits의 레이아웃 예제처럼 기본 ‘행 레이블’ 대신 필드 이름도 드러난다. 필요하면 같은 메뉴의 모든 항목 레이블 반복으로 부서 이름을 각 행에 표시한다. 보고서에 중간 소계가 필요 없으면 디자인 → 부분합 → 부분합 표시 안 함을 선택한다.

회사 양식의 특정 셀에 결과를 계속 연결해야 한다면, 자유롭게 크기가 변하는 피벗테이블을 그대로 붙이는 것보다 피벗테이블과 SUMIFS의 월별 보고서 차이를 보고 구성을 고르는 편이 낫다. 항목을 바꿔 가며 분석하는 일과 고정된 양식에 매달 값을 채우는 일은 필요한 작업이 다르다.

새 거래를 원본 표에 추가한 뒤 피벗테이블을 새로고침한다

원본이 표라면 추가된 행을 포함해 다시 집계할 수 있다. 예제의 SalesData 바로 아래에 2026-11-06 / 영업1팀 / 매장 / 50000 / S007을 한 행 추가한다. 표의 테두리가 새 행까지 늘어났는지 보고, 피벗테이블 안에서 오른쪽 버튼 → 새로 고침을 누른다.

필터를 모두 해제한 보고서의 총합계는 650,000원이 되고 영업1팀은 340,000원, 11월은 300,000원이 된다. 온라인만 선택한 필터가 남아 있으면 이번에 추가한 매장 거래는 보이지 않고 합계도 460,000원 그대로다. 새 행을 넣었는데 숫자가 그대로라면 원본 범위와 함께 현재 필터도 살피는 이유다.

표 아래에 썼어도 새 행이 표에 포함되지 않았다면 표 안을 누르고 테이블 디자인 → 표 크기 조정에서 범위를 늘린다. 피벗테이블의 원본이 SalesData가 아니라 $A$1:$E$7 같은 고정 주소라면 피벗 테이블 분석 → 데이터 원본 변경에서 표 이름으로 바꾼다. Microsoft의 원본 변경 절차와 같은 모양의 범위와 표를 비교한 사용자 사례가 이 차이를 보여 준다.

통합 문서에 피벗테이블이 여러 개면 데이터 → 모두 새로 고침을 쓴다. Microsoft의 현행 새로고침 문서는 일부 최신 Excel의 자동 새로고침도 안내하면서 Microsoft 365 참가자 프로그램 조건을 함께 적고 있다. 배포 채널과 버전이 다른 PC에서도 쓸 보고서라면 원본 수정 후 수동 새로고침하는 순서로 작업하면 된다.

기존 보고서에서 추가한 행만 계속 빠진다면 피벗테이블 새로고침 후 새 행이 누락될 때의 원본 범위 수정에서 기존 배치를 유지하며 고치는 순서를 볼 수 있다.

합계가 다르거나 그룹이 안 되면 원본 값·범위·필터로 돌아간다

텍스트 숫자는 셀의 녹색 오류 표시를 눌렀을 때 나오는 숫자로 변환을 쓸 수 있다. 해당 표시가 없으면 보조 열에 =VALUE(D2)를 넣고 나머지 행까지 채운 뒤 결과를 복사해 원본 금액 열에 값으로 붙여넣는다. ‘원’ 같은 문자가 포함된 값은 그 문자부터 정리한다. 셀 표시 형식을 ‘숫자’로 고르는 것만으로 저장된 텍스트가 모두 변환되지는 않는다. 경고 메뉴와 VALUE 방식은 Microsoft 숫자 변환 안내에 나와 있다.

날짜가 텍스트라면 보조 열에서 =DATEVALUE(A2)로 변환할 수 있다. 나머지 행까지 채우고 결과를 원본 날짜 열에 값으로 붙여넣은 뒤 날짜 서식을 적용한다. 지역 설정이 해석하지 못하는 표기는 변환 중 오류가 나므로, 예제처럼 연-월-일이 분명한 값으로 고쳐 입력한다. 이 방법은 Microsoft의 텍스트 날짜 변환 절차에 따른다.

원본 시트에서 일부 행을 필터로 숨겨 놓아도 피벗테이블은 원본으로 지정한 범위를 집계한다. 화면에 보이는 거래만 보고 싶다면 피벗테이블 자체의 필터를 쓴다. 원본 표 아래에서 보이는 행의 합계만 구하는 작업은 필터 합계를 구하는 SUBTOTAL로 처리할 수 있다.

피벗테이블 결과의 숫자를 직접 덮어써서 맞추려고 하면 다음 집계의 근거가 사라진다. 잘못된 금액은 원본에서 고친 뒤 새로고침한다. 제출 당시 숫자를 고정해 전달할 때는 결과 범위를 복사해 별도 시트에 값으로 붙여넣기하고, 기준일과 적용 필터를 함께 적는다. 이 사본은 원본 변경을 따라 갱신되지 않는다.

집계표 자체를 지우려면 피벗테이블 안을 클릭하고 피벗 테이블 분석 → 선택 → 전체 피벗 테이블로 전체를 선택한 뒤 Delete를 누른다. 일부 숫자만 지우려 하면 피벗테이블의 일부를 변경할 수 없다는 메시지가 나온다. 피벗테이블만 있는 별도 시트는 시트 자체를 삭제해도 된다. 원본 거래 시트는 남겨 두면 다른 집계를 다시 만들 수 있다. 이 삭제 경로는 Ablebits의 전체 선택·삭제 절차에 따른다.

Mac·웹에서도 만들 수 있고, 월 그룹 메뉴가 없으면 연월 열을 쓴다

Mac용 Excel도 원본 안의 셀에서 삽입 → 피벗 테이블로 시작한다. 행·열·값·필터의 역할은 같다. 오른쪽 클릭 메뉴 대신 Control을 누른 채 클릭해 새로고침을 고를 수도 있다. Windows용 Ctrl 단축키 대신 메뉴를 따라가면 키 배열 차이를 피할 수 있다.

웹용 Excel에서는 표나 범위를 선택하고 삽입 → 피벗 테이블을 누른 뒤, 옆에 열린 창에서 새 시트 또는 기존 시트를 고른다. 합계·개수는 값 셀의 오른쪽 클릭 메뉴에서 ‘값 요약 기준’으로 바꾼다. 데스크톱용 설명과 창 모양이 달라도 기본 집계는 계속할 수 있다. 플랫폼별 절차는 Microsoft 생성 안내의 웹·macOS 탭에 나뉘어 있다.

현재 쓰는 환경에 날짜 ‘그룹’ 메뉴가 없거나 그룹된 월 이름 대신 2026-11처럼 표시하고 싶다면, 원본 표에 연월 열을 추가해 =TEXT([@날짜],"yyyy-mm")를 넣는다. 새로고침 후 이 필드를 열 영역에 놓으면 연도가 다른 11월도 따로 집계된다. 날짜가 실제 Excel 날짜 값으로 들어 있다는 것이 전제다. 이 열은 글자 형태의 월 구분이므로, 일별 시간 흐름을 다룰 때는 원본 날짜 열도 보관한다. 날짜 그룹 대신 보조 열을 쓰는 방식은 Exceljet 월별 예제에서도 제시한다.

첫 보고서에서는 필터를 모두 푼 상태의 총합계와 원본 거래 합계가 같은지 보고 파일을 저장한다. 예제는 거래 6건·600,000원, S007까지 넣었다면 7건·650,000원이다. 이 값이 맞으면 원하는 부서·채널 필터를 적용해 보고서로 옮긴다.