수식으로 피벗 스타일 보고서 만들기

피벗 테이블은 데이터를 교차표로 정리합니다. 한 범주는 행으로, 다른 범주는 열로 배치하고 격자를 합계로 채웁니다. 대표적인 예로 왼쪽에 지역을 세로로 배치하고, 위쪽에 분기를 가로로 배치하며, 각 셀에 매출을 표시하는 방식이 있습니다.

피벗 테이블은 유용하지만 수동으로 새로 고쳐야 하고 고정된 블록에 배치됩니다. 수식 기반 피벗 테이블은 데이터가 변경될 때마다 실시간으로 스스로 다시 구성됩니다.

이 레슨에서는 행 헤더와 열 헤더를 배치하고, 모든 교차 지점을 자동으로 계산하는 SUMIFS 수식으로 본문을 채워 보겠습니다.

Sales라는 이름의 시트에서 다음 열을 사용합니다. A열에는 지역, B열에는 분기, C열에는 금액이 있고, 데이터는 2행부터 500행까지 있습니다.

만들려는 보고서는 다음과 같은 모습입니다:

• 행 레이블: E열에 각 고유 지역을 세로로 표시합니다.
• 열 레이블: 1행의 F열부터 I열까지 1분기, 2분기, 3분기, 4분기를 가로로 표시합니다.
• 본문: 각 지역과 분기 조합의 금액 합계를 표시합니다.

본문의 각 셀은 하나의 질문에 답합니다. 이 지역은 이 분기에 얼마를 판매했을까요?

행 헤더는 고유 지역입니다. UNIQUE를 SORT와 함께 사용하여 E열 아래로 스필되게 하고 정렬된 상태를 유지하세요.

E2 셀에 다음 수식을 입력합니다:

이제 지역 목록이 E2부터 아래쪽으로 자동으로 채워집니다. 요약 표와 마찬가지로 이 목록이 전체 격자가 참조하는 기준점입니다.

열 헤더는 한 행에 가로로 펼쳐지는 분기입니다. 1분기, 2분기, 3분기, 4분기를 직접 입력하거나 UNIQUE를 TRANSPOSE로 감싸 가로 방향으로 스필할 수 있습니다.

F1 셀에 입력하면 고유한 분기가 위쪽에 가로로 배치됩니다:

TRANSPOSE는 세로 목록을 가로 목록으로 뒤집습니다. 따라서 분기 열이 헤더 행으로 바뀝니다. 이제 격자의 두 축이 모두 준비되었습니다.

이제 본문을 채워 보겠습니다. 각 셀에는 해당 행의 지역과 해당 열의 분기에 대한 합계가 필요합니다. SUMIFS는 두 가지 조건을 쉽게 처리합니다.

첫 번째 본문 셀인 F2에 다음과 같이 입력합니다:

이 수식은 왼쪽 헤더와 지역이 같고 위쪽 헤더와 분기가 같은 금액을 계산합니다. 피벗 테이블의 하나의 교차 지점을 계산하는 수식입니다.

달러 기호를 사용하면 하나의 수식을 복사하여 전체 격자를 채울 수 있습니다. 다음 혼합 참조를 살펴보세요:

• $E2는 열을 E로 고정하지만 행은 이동하게 하므로 각 행이 해당 지역을 읽습니다.
• F$1은 행을 1로 고정하지만 열은 이동하게 하므로 각 열이 해당 분기를 읽습니다.
• $C$2:$C$500은 데이터 범위가 이동하지 않으므로 완전히 고정합니다.

F2를 모든 분기 방향으로 복사한 다음 모든 지역 방향으로 아래까지 복사하세요. 각 셀이 스스로 정확하게 조정됩니다.

F2를 올바르게 작성했으면 셀을 선택하고 분기 열을 따라 오른쪽으로 채우기 핸들을 끈 다음, 지역 행을 따라 아래로 끌어 내리세요. 엑셀이 상대 참조 부분을 대신 다시 작성합니다.

• G2 셀은 지역이 $E2이고 분기가 G$1인 수식이 됩니다.
• F3 셀은 지역이 $E3이고 분기가 F$1인 수식이 됩니다.

그 결과 모든 교차 지점의 합계가 계산된 완전한 교차표가 만들어집니다. 피벗 마법사가 필요하지 않으며 Sales 데이터가 변경되는 즉시 다시 계산됩니다.

실제 피벗 테이블에는 총합계가 표시됩니다. 마지막 분기 오른쪽에 총합계 열을 추가하고, 아래쪽에 총합계 행을 추가하세요. 각 행이나 열에 일반적인 SUM을 사용하면 됩니다.

첫 번째 지역의 행 합계를 계산하려면 마지막 분기 다음 열에 다음 수식을 입력합니다:

열 합계를 계산하려면 해당 분기의 본문 셀을 행 아래쪽으로 더하세요. 이러한 가장자리 합계가 보고서를 완성해 주며, 독자가 한눈에 숫자가 맞는지 확인할 수 있게 해 줍니다.

사용 중인 도구가 이 기능을 지원한다면 스필 참조를 SUMIFS에 직접 전달하여 복사 작업을 피할 수 있습니다. 스필된 헤더를 조건으로 사용하세요.

이 하나의 수식으로 모든 지역과 분기의 교차 지점 합계를 계산합니다:

여기서 E2#는 세로 지역 목록이고 F1#은 가로 분기 목록입니다. 엑셀이 이 둘을 한 번에 조합하여 전체 격자를 만듭니다. 끌어 복사하는 방식이 호환성은 더 좋지만, 이것은 우아한 현대적 방식입니다.

금액뿐 아니라 비중도 표시하면 보고서에서 더 많은 정보를 얻을 수 있습니다. 각 지역의 합계를 총합계 대비 백분율로 나타내는 열을 추가하세요.

지역의 행 합계가 J2에 있고 총합계가 J10에 있다면 다음과 같이 입력합니다:

$J$10으로 총합계를 고정하면 수식을 모든 지역에 아래로 채워도 항상 같은 분모로 나눌 수 있습니다. 열의 형식을 백분율로 지정하면 어느 지역의 비중이 큰지 한눈에 알 수 있습니다.

몇 가지 습관을 지키면 수식 기반 피벗 테이블을 안정적으로 유지할 수 있습니다:

• 2행부터 500행까지처럼 충분히 넓은 범위를 참조하여 새 행이 포함되도록 하세요.
• 전체 $ 앵커로 데이터 범위를 고정하고, 헤더 참조만 이동하도록 하세요.
• 스필된 헤더와 합계가 들어갈 공간을 확보할 수 있도록 아래쪽과 오른쪽에 빈 공간을 남겨 두세요.

이렇게 만들면 보고서에 유지 관리가 전혀 필요하지 않습니다. 새 판매 내역을 입력하면 격자, 합계, 레이블이 모두 자동으로 업데이트됩니다.

수식 기반 피벗 테이블을 작동하게 하는 혼합 참조를 제대로 이해했는지 확인해 보세요.

수식만 사용하여 피벗 테이블을 다시 만들었습니다:

• UNIQUE와 SORT를 함께 사용하여 스필되는 열에 행 헤더를 만들었습니다.
• TRANSPOSE를 사용하여 열 헤더를 한 행에 가로로 배치했습니다.
• 혼합 참조 $E2와 F$1을 사용한 SUMIFS로 끌어 복사하거나 E2#와 F1# 같은 스필 참조를 사용하여 모든 교차 지점을 채웠습니다.
• SUM으로 총합계 가장자리를 추가했습니다.

전체 격자가 실시간으로 다시 계산됩니다. 다음에는 지표를 제어하는 드롭다운을 사용하여 대시보드를 대화형으로 만들어 보겠습니다.