피벗 테이블 없이도 대시보드를 만들 수 있다. 단일 표에서 정해진 KPI 카드와 그래프를 반복해 보여 준다면 수식 표를 먼저 검토할 만하다. 사용자가 기간이나 범주를 바꾸고 정렬·확장하며 원인을 찾아야 한다면 피벗을 남긴다. 여러 파일을 같은 방식으로 합친다면 Power Query를 앞단에 두고, 여러 표의 관계와 공통 측정값이 필요할 때 데이터 모델을 검토한다.
피벗을 빼면 화면 배치는 자유로워진다. 대신 피벗의 필터·정렬·확장 같은 탐색 흐름은 수식 표에 그대로 남지 않는다. 따라서 화면 모양보다 이 대시보드를 보는 사람이 값을 탐색해야 하는지부터 정해야 한다.
조건별로 고르는 선택표
| 현재 조건 | 먼저 고를 구조 | 처음부터 피할 선택 |
|---|---|---|
| 단일 Excel 표에서 같은 KPI 카드와 그래프를 반복해 보여 준다 | 수식 표 | 배치만 맞추려고 피벗 결과의 셀 주소를 직접 참조하는 방식 |
| 사용자가 범주를 바꿔 집계하고 슬라이서·정렬·확장으로 내용을 탐색한다 | 피벗 테이블 | 같은 탐색 기능을 수식만으로 모두 재현하려는 방식 |
| 여러 파일·폴더·원본에서 같은 정리와 결합을 반복한다 | Power Query를 앞단에 추가 | Power Query만으로 슬라이서 반응형 집계까지 해결하려는 방식 |
| 여러 표의 관계와 공통 측정값이 여러 보고서에서 반복된다 | 데이터 모델 검토 | 관계가 없는 단일 표 보고서에 복잡한 모델을 먼저 만드는 방식 |
Microsoft의 피벗 테이블 안내는 피벗을 비교·패턴·추세를 찾는 요약·분석 도구로 설명한다. 여러 표, 사용자 지정 측정값, 매우 큰 데이터 집합은 데이터 모델 기반 피벗을 검토할 조건으로 든다. 다만 모든 파일에 통하는 행 수 기준은 제시하지 않는다. 파일 크기만 보고 구조를 바꾸기보다 표 사이의 관계와 반복 계산이 실제로 필요한지부터 살피는 편이 낫다.
고정 보고에 수식 표가 맞는 경우
월간 매출, 목표 대비 차이, 상위 항목처럼 값이 들어갈 칸이 정해져 있다면 수식 표로 화면을 구성하기가 수월하다. 원하는 위치에 집계값을 놓고 일반 차트를 연결할 수 있어 피벗의 행·열 배치에 맞출 필요가 없다.
Excel Campus의 수식 표와 피벗 비교는 원본을 Excel 표로 두면 새 행이 추가될 때 수식 기반 요약 범위도 확장될 수 있다고 설명한다. 다만 수식 표를 골랐다고 데이터 정리 문제가 없어지는 것은 아니다. 매달 여러 파일을 합치고 열 형식을 고치는 작업이 반복된다면 수식 표 앞에 Power Query를 둘 수 있다.
탐색이 목적이면 피벗을 남긴다
대시보드를 보는 사람이 “어느 상품군 때문인가”, “어느 기간에 바뀌었는가”를 그때그때 바꿔 확인해야 한다면 피벗이 더 자연스럽다. 슬라이서, 정렬, 항목 펼치기가 화면의 주요 기능이라면 고정 레이아웃의 자유도만 보고 피벗을 제거할 이유가 약하다.
원본에 새 데이터가 들어오면 피벗을 새로 고쳐야 한다. 원본 표에 행을 더했다고 대시보드 값이 바로 달라질 것으로 가정하면 수식 표와 피벗의 갱신 경로를 혼동하기 쉽다.
맞춤형 화면과 피벗 집계가 모두 필요할 때
카드는 자유롭게 배치하되 뒤에서는 피벗 집계와 슬라이서를 쓰고 싶을 수 있다. 이때는 별도 시트의 피벗을 계산원으로 두고, 대시보드 시트에서 GETPIVOTDATA로 필요한 값만 가져오는 혼합 구조를 쓸 수 있다.
Microsoft의 피벗 셀 수식 변환 안내에 따르면 피벗에서 수식으로 값을 가져와 맞춤형 보고서를 구성할 수 있다. 참조 필드가 피벗 보고서에 남아 있으면 GETPIVOTDATA는 레이아웃 변경이나 새로 고침 뒤에도 값을 가져오지만, 필드가 사라지면 오류가 날 수 있다. 피벗 필드 구성을 바꾼 뒤에는 카드 값도 함께 확인한다.
여러 파일은 Power Query, 표 관계는 데이터 모델
지점별 파일을 합치고 열을 나누며 날짜 형식을 맞추는 작업은 Power Query가 맡을 수 있다. Microsoft의 Power Query와 Power Pivot 역할 설명은 Power Query를 원본 연결·변환·결합·로드 단계로 구분한다. 결과는 워크시트에 올려 수식 표로 보여 줄 수도 있고, 피벗으로 탐색하게 할 수도 있다. 여러 파일을 합친다는 이유만으로 피벗이 필수가 되지는 않는다.
주문 표, 고객 표, 상품 표처럼 분리된 표를 연결하고 표를 넘나드는 계산을 반복한다면 데이터 모델을 검토한다. 같은 Microsoft 문서는 Power Pivot을 데이터 모델의 관계, 계산 열, 측정값, KPI를 다루는 단계로 설명한다. 단일 표의 고정 월간 보고서라면 관계와 측정값을 설계하는 부담이 먼저 늘 수 있다. Power Query와 데이터 모델, 동적 배열 함수의 사용 범위는 Excel 버전·플랫폼·추가 기능 상태에 따라 달라질 수 있으므로 파일을 열 사람의 환경도 확인한다.
선택한 뒤에는 갱신 경로를 점검한다
단일 표·고정 레이아웃이면 수식 표, 탐색과 슬라이서가 핵심이면 피벗, 반복 통합이면 Power Query, 여러 표의 관계와 공통 측정값이면 데이터 모델을 검토한다. 이 구조들은 한 파일에서 함께 쓸 수도 있다. 구조를 정한 뒤 새 행이나 쿼리 결과가 화면에 반영되지 않는다면, 엑셀 대시보드 자동 갱신 점검 순서로 원본·쿼리·피벗·화면 가운데 멈춘 지점을 확인하면 된다.