피벗테이블과 SUMIFS 비교, 반복 집계에 맞게 고르는 법

보고서 모양이 다른데 같은 도구를 쓰는 문제

예를 들어 물품 이동 기록을 구역과 이동 유형별로 집계한다고 가정한다. 어떤 날은 구역별 전체 수량을 보고, 다른 날은 이동 유형별 구성을 살펴야 한다. 반면 제출용 보고서는 늘 같은 위치에 같은 항목이 들어가야 할 수도 있다.

앞의 작업은 분류 배치를 바꾸는 일이 많다. 뒤의 작업은 출력 위치를 유지하는 일이 중요하다. 단순히 어느 도구가 더 빠른지를 묻기 전에 보고서의 형태가 바뀌는지부터 확인한다.

원본은 구역, 유형, 수량이라는 열로 구성한다. 데이터 영역을 선택해 삽입 메뉴의 표로 만들고, 표 이름을 기록으로 지정한다. 표 안에는 소계나 총계 행을 데이터처럼 끼워 넣지 않는다.

구역과 유형은 행마다 입력한다. 위 행과 같다는 뜻으로 셀을 비워 두면 빈 항목으로 집계될 수 있다. 병합 셀도 해제하고 각 열에 하나의 의미만 담는다.

같은 원본으로 피벗과 수식 구성하기

피벗테이블은 기록 표 안의 셀을 선택한 뒤 삽입 메뉴에서 만든다. 새 시트에 배치하고 행 영역에는 구역, 열 영역에는 유형, 값 영역에는 수량을 넣는다. 값 필드 설정에서 요약 방식이 합계인지 확인한다.

수량이 문자로 섞여 있으면 개수로 요약될 수 있다. 값 영역에 숫자가 표시된다는 이유만으로 수량 합계라고 생각하지 않는다. 제목과 값 필드 설정을 함께 본다.

고정 보고서에서는 F2에 구역, G2에 유형을 적고 결과 칸에 다음 수식을 넣는다.

=SUMIFS(기록[수량],기록[구역],F2,기록[유형],G2)

대괄호 안의 이름은 기록 표의 열 제목이다. 파일에 있는 실제 제목과 정확히 맞춰야 한다. 다른 시트에 결과를 두어도 표 이름과 열 이름이 올바르면 같은 원본을 참조할 수 있다.

원본 전체 수량을 확인할 검산식도 별도 칸에 둔다.

=SUM(기록[수량])

Microsoft 지원의 피벗테이블 안내는 표의 새 데이터와 수정 내용을 피벗 새로 고침에 반영하는 방식을 설명한다. 원본 표에 행이 포함되었는지와 피벗이 갱신되었는지는 각각 확인한다.

총합이 같아도 세부 항목을 비교해야 하는 이유

예를 들어 동쪽 구역의 입고 수량이 3과 5이고, 서쪽 구역의 입고 수량이 4라고 가정한다. 모두 예시 숫자다. 동쪽 입고는 8, 전체 수량은 12가 되어야 한다.

피벗테이블과 수식 결과가 같으면 집계 기준을 맞췄다는 근거가 된다. 다만 전체 합계만 같고 구역 배분은 틀릴 수도 있다. 대표 항목과 빈 구역, 새 유형을 따로 확인해야 한다.

피벗은 새로운 분류를 발견하는 데, 수식은 익숙한 양식을 유지하는 데 활용할 수 있다. 하나의 파일에서 피벗을 검토용으로 두고 수식을 제출용으로 쓰는 구성도 가능하다.

여기서 확인할 결과는 클릭 수나 가상의 절감률이 아니다. 분류 변경에 필요한 수정 지점과 새 항목이 누락되는 위치다. 시간 비교가 필요하면 동일한 원본과 보고서 요구를 기준으로 직접 기록한다.

새 항목과 갱신의 한계, 마무리 점검

고정 보고서의 가장 큰 빈틈은 조건 목록에 없는 새 분류다. 원본에 새 구역이 생겨도 기존 SUMIFS 결과 칸만 보면 존재를 놓칠 수 있다. 보고서에 실린 항목들의 합계와 원본 총합을 대조한다.

피벗테이블은 적용된 필터나 슬라이서 때문에 일부 항목이 숨겨질 수 있다. 총합이 다르면 원본 오류로 단정하기 전에 필터 상태와 원본 범위를 먼저 확인한다.

수식은 계산 옵션이 수동이면 수정 직후 값이 갱신되지 않을 수 있다. 피벗도 사용 환경과 설정에 따라 갱신 시점이 달라질 수 있으므로, 파일을 넘기기 전에 표시된 결과를 실제 원본과 대조한다.

수량에 반품이나 취소를 음수로 넣는 규칙이 있다면 두 방식에 동일하게 적용한다. 한쪽만 취소 행을 제외하면 도구가 아니라 기준이 다른 비교가 된다.

최종 선택은 보고서를 받는 사람이 무엇을 바꿀지에 맞춘다. 분류 탐색이 잦으면 피벗을 중심으로, 양식 유지가 중요하면 수식을 중심으로 구성하고 각각의 누락 지점을 점검한다.