엑셀 | 피벗이 이상하면 원본부터 의심하자

지점별 매출 합계 같은 걸 SUMIFS로 한참 짜다가, 조건이 셋쯤 되면 피벗테이블 생각이 난다. 삽입 → 피벗 테이블, 혹은 추천 피벗 테이블을 누르면 몇 초 만에 나오니까.

그런데 처음 만들어 보면 값이 이상하게 나오거나, 아예 필드 목록이 텅 비거나 한다. 대체로 원본 표 탓이다.

피벗은 평평한 표만 읽는다

사람이 보기 좋게 정리한 표와 피벗이 읽을 수 있는 표는 다르다. 지점명을 첫 줄에만 쓰고 아래는 비워 두거나, 중간에 소계 행을 끼워 넣거나, 구분하려고 빈 줄을 넣은 표가 딱 안 되는 쪽이다.

왼쪽 표로 피벗을 만들면 소계 360000이 데이터 한 건으로 잡혀 합계가 두 배가 되고, 빈 지점 칸은 “(비어 있음)” 항목이 된다. 오른쪽처럼 한 행이 한 건이고 모든 칸이 채워져 있어야 한다.

정리하면 원본에서 지켜야 할 건 넷이다.

• 첫 행은 제목, 둘째 행부터 데이터
• 한 행이 한 건, 빈 행 없음
• 소계·합계 행은 원본에 넣지 않는다 (그건 피벗이 할 일)
• 병합된 셀 없음

원본을 만들면서 Ctrl + T로 표로 지정해 두면 하나 더 얻는다. 나중에 행이 늘어나도 피벗 원본 범위가 자동으로 따라온다. 안 그러면 데이터를 추가할 때마다 피벗 원본 범위를 손으로 다시 잡아야 한다.

영역 넷에 무엇을 넣나

필드 목록에서 아래 네 칸으로 끌어다 놓는 구조다. 행에 넣으면 세로로 펼쳐지고, 열에 넣으면 가로로 펼쳐지고, 값에 넣으면 집계되고, 필터에 넣으면 위쪽에 선택 상자가 생긴다.

지점을 행에, 담당을 열에, 금액을 값에 넣어 봤다.

목록에서 숫자 필드를 체크하면 알아서 값 영역으로, 문자 필드를 체크하면 행 영역으로 들어간다. 위치가 마음에 안 들면 그때 끌어 옮기면 되고.

결과는 지점 × 담당 교차표가 된다.

SUMIFS를 여섯 번 짜서 만들 표를 끌어다 놓기로 대신한 셈이다. 원본에 행이 추가되면 이 표는 새로 고침 한 번으로 따라온다.

값에 넣은 숫자가 합계가 아니라 개수로 잡히는 경우가 있다. 열에 빈칸이나 텍스트가 하나라도 섞여 있으면 엑셀이 숫자 열로 안 본다. 값 필드 설정에서 합계로 바꿔도 되지만, 원본 열을 고치는 쪽이 맞다.

그룹화와 슬라이서

날짜를 행에 넣으면 일자별로 주르륵 펼쳐지는데, 아무 날짜 칸에서 오른쪽 클릭 → 그룹을 누르면 월·분기·연 단위로 접힌다. 숫자도 구간으로 묶인다. 나이나 금액을 10단위로 나눌 때 쓸 만하다.

슬라이서는 필터를 버튼으로 빼 놓은 것이다. 피벗을 클릭한 상태에서 슬라이서 삽입을 고르면 된다. 피벗 여러 개에 같은 슬라이서를 연결하면 버튼 하나로 다 같이 움직인다.

비율이 필요하면 값 필드 설정 → 값 표시 형식에서 총합계 대비 비율을 고른다. 옆에 계산 열을 만들 필요가 없다.

원본을 고쳤는데 피벗이 그대로다

이게 제일 자주 걸리는 것 같다. 피벗은 만들 때 원본을 캐시로 떠 두기 때문에, 원본을 고쳐도 새로 고침을 눌러야 반영된다. 피벗 안을 클릭하고 데이터 → 새로 고침.

행이 늘어난 경우라면 새로 고침만으로는 부족하다. 앞에서 말한 Ctrl + T로 표를 만들어 두지 않았다면 피벗 분석 → 데이터 원본 변경에서 범위를 다시 잡아 줘야 한다.

결론. 원본 한 장, 피벗 여러 장

원본 시트는 평평하게 한 장만 두고, 보고 싶은 각도마다 피벗을 새로 뽑는 식으로 쓰는 게 편한 것 같다. 원본에 소계를 넣기 시작하면 그때부터 꼬인다.

다음엔 이걸 매번 손으로 새로 고치는 것도 귀찮아서 매크로로 넘어가 볼까 한다.