엑셀 파워 쿼리 열 피벗 해제로 월별 가로표를 세로 데이터로 바꾸는 방법

부서별 월 매출을 정리할 때 부서, 1월, 2월, 3월…처럼 월이 오른쪽으로 계속 늘어나는 표를 자주 만납니다. 사람이 읽기에는 익숙하지만 피벗 테이블, 차트, 데이터 모델에서 분석하기에는 불편한 구조입니다. 월을 하나의 월 열에, 금액을 하나의 매출액 열에 쌓아야 기간 필터와 집계가 단순해집니다.

Excel의 Power Query 열 피벗 해제는 이런 가로형 교차표를 세로형 열 데이터로 바꾸는 기능입니다. 셀을 복사해 수작업으로 쌓는 것과 달리 변환 단계가 쿼리에 기록되므로 다음 달 열이 추가된 원본도 새로 고침으로 다시 처리할 수 있습니다. 이 글에서는 원본 표 설계, 세 가지 피벗 해제 명령의 차이, 월별 예제, 오류 해결, 검수 기준까지 순서대로 설명합니다.

핵심 개념: 피벗 해제는 무엇을 바꾸는가

피벗 해제 전 표의 열 머리글에는 사실상 데이터 값인 2026-01, 2026-02, 2026-03이 들어 있습니다. 피벗 해제를 하면 이 머리글들이 특성 같은 새 열의 행 값으로 내려가고, 교차 지점의 매출은 값 열에 쌓입니다. Microsoft 공식 도움말은 이를 특성-값 쌍을 풀어 정규화된 열 형식으로 재배치하는 과정으로 설명합니다.

피벗 테이블과 피벗 해제는 이름이 비슷하지만 목적이 다릅니다. 피벗 테이블은 긴 원본 데이터를 사람이 읽기 좋은 요약표로 집계합니다. 피벗 해제는 이미 요약표처럼 가로로 펼쳐진 데이터를 분석 가능한 긴 원본 형태로 되돌립니다. 따라서 피벗 해제 결과를 다시 피벗 테이블의 원본으로 사용하는 흐름이 자연스럽습니다.

작업 전 준비사항: 원본 표를 안전하게 만들기

원본 범위에는 한 줄짜리 열 머리글이 있어야 합니다. 중간 합계 행, 제목 행, 병합 셀, 빈 열은 Power Query가 데이터 구조를 일관되게 해석하는 데 방해가 됩니다. 화면용 제목은 표 위쪽에 따로 두고, 실제 가져올 범위는 부서, 제품, 2026-01처럼 고유한 열 이름으로 시작하십시오.

원본 데이터를 클릭하고 Ctrl+T를 눌러 Excel 표로 변환합니다. 머리글 포함을 확인하고 표 이름을 tbl_monthly_sales처럼 의미 있게 지정합니다. 표로 만들면 행이 추가될 때 쿼리 원본 범위가 자동 확장되고, 다른 쿼리와 구분하기 쉽습니다. 작업 전 통합 문서 사본을 보관하고 결과를 원본 시트 위에 덮어쓰지 않습니다.

세 가지 열 피벗 해제 명령의 차이

Power Query에는 열 피벗 해제, 다른 열 피벗 해제, 선택한 열만 피벗 해제에 해당하는 명령이 있습니다. 화면 번역은 버전에 따라 조금 다를 수 있으므로 선택한 열과 선택하지 않은 열 중 어느 쪽을 세로로 내리는지 결과 미리 보기로 확인해야 합니다.

열 피벗 해제는 현재 선택한 월 열만 세로로 내립니다. 월 열 목록이 고정되어 있고 새 열을 자동 포함하면 안 될 때 적합합니다. 다른 열 피벗 해제는 부서·제품처럼 유지할 식별 열을 선택하고, 선택하지 않은 나머지 열을 모두 세로로 내립니다. Microsoft 공식 도움말에 따르면 이 방식은 새로 고침 때 추가된 새 열도 피벗 해제 대상에 포함할 수 있어 월이 계속 추가되는 보고서에 유용합니다.

선택한 열만 피벗 해제는 선택 열 집합의 의도를 명시하는 변형입니다. 공식 도움말은 원본의 열 개수를 알 수 없지만 특정 선택 열을 피벗 해제 상태로 유지하고 싶을 때 사용할 수 있다고 설명합니다. 명령 이름만 외우기보다 “앞으로 새 열이 들어오면 자동으로 세로화할 것인가”를 기준으로 고르십시오.

단계별 절차: 월별 가로표를 세로형으로 변환하기

예제 원본은 부서, 제품, 2026-01, 2026-02, 2026-03 열을 가진다고 가정합니다. 먼저 표 안의 셀을 선택하고 데이터 > 테이블/범위에서를 눌러 Power Query 편집기를 엽니다. Excel 버전에 따라 데이터 가져오기 및 변환 그룹에 표시됩니다.

1. Power Query 미리 보기에서 첫 행이 정확한 열 머리글인지 확인합니다.
2. 부서와 제품 열을 Ctrl을 누른 채 함께 선택합니다.
3. 변환 탭에서 선택하지 않은 열을 대상으로 하는 다른 열 피벗 해제를 실행합니다.
4. 생성된 특성 열의 이름을 월로, 값 열의 이름을 매출액으로 변경합니다.
5. 월 열의 데이터 형식을 텍스트 또는 날짜로 정합니다. 2026-01을 날짜로 바꿀 때는 로캘과 실제 날짜가 어떻게 생성되는지 확인합니다.
6. 매출액 열은 통화 기호와 쉼표가 없는 숫자인지 확인하고 통화 또는 정수 형식을 지정합니다.
7. 오른쪽 쿼리 설정의 적용된 단계에서 원본, 머리글 승격, 형식 변경, 다른 열 피벗 해제, 열 이름 변경 순서를 확인합니다.
8. 홈 > 닫기 및 로드 대상을 선택해 새 워크시트의 표로 로드합니다.

Microsoft는 Power Query 변환이 원본을 직접 바꾸지 않고 쿼리의 단계로 기록되며, 새로 고침 때 각 단계가 다시 실행된다고 설명합니다. 따라서 원본 월별 표는 그대로 두고 결과 시트만 분석용으로 사용할 수 있습니다. 단, 원본 열 이름이 바뀌면 기존 단계가 해당 열을 찾지 못할 수 있으므로 열 이름 변경도 관리 대상입니다.

구체적인 예시: 3개 월을 6개 행으로 바꾸기

원본에 두 제품과 세 달이 있으면 데이터 행은 2개지만 월별 값 셀은 6개입니다.

부서, 제품을 유지하고 다른 열을 피벗 해제하면 아래처럼 바뀝니다.

검수 기준은 행 수로 계산할 수 있습니다. 식별 행이 2개이고 피벗 해제한 월 열이 3개이며 빈값을 제거하지 않았다면 결과는 2 × 3 = 6행이어야 합니다. 원본 금액 합계 663과 결과 매출액 합계 663도 일치해야 합니다. 이 두 검사를 통과하면 구조 변환 중 값이 누락되거나 중복될 가능성을 빠르게 찾을 수 있습니다.

새 월 열이 추가될 때 새로 고침하는 방법

원본 표 오른쪽에 2026-04 열을 추가하고 값을 입력합니다. 결과 표 안의 셀을 선택한 뒤 데이터 > 모두 새로 고침을 실행합니다. 다른 열 피벗 해제를 사용했다면 새 월 열이 선택하지 않은 열로 간주되어 결과의 월 값에 함께 내려오는지 확인합니다.

새 열이 들어오지 않는다면 세 가지를 봅니다. 첫째, 새 월 열이 tbl_monthly_sales 표 범위 안에 포함되었는지 확인합니다. 둘째, 적용된 단계가 특정 월 열만 고정 선택한 열 피벗 해제인지 확인합니다. 셋째, 후속 단계에서 2026-04를 오류나 null로 바꾸는 형식 변환이 있는지 확인합니다.

새로 고침 성공을 화면 모양만으로 판단하지 마십시오. 기대 행 수는 식별 행 수 × 월 열 수로 다시 계산하고, 월별 합계와 전체 합계를 원본에 대조합니다. 데이터 양이 많으면 결과 표에 피벗 테이블을 연결해 월별 합계를 비교하면 검수가 빨라집니다.

데이터 형식과 빈 셀을 다루는 방법

피벗 해제 후 월 열이 텍스트인지 날짜인지 결정해야 합니다. 단순 레이블과 원본 열 이름 보존이 중요하면 2026-01 텍스트로 둡니다. 날짜 계산과 연속 축이 필요하면 매월 1일을 나타내는 실제 날짜로 변환하되, 2026-01이 지역 설정에 따라 잘못 해석되지 않는지 확인합니다. 안전한 방법은 텍스트에서 연도와 월을 분리한 뒤 날짜를 명시적으로 만드는 것입니다.

금액 열의 빈 셀은 의미를 정해야 합니다. 미입력과 0원은 다릅니다. 빈 셀을 모두 0으로 바꾸면 아직 보고되지 않은 금액이 실적 0으로 집계될 수 있습니다. 업무 정의에서 미입력을 0으로 간주한다는 합의가 있을 때만 값을 바꿉니다. -, 미정, N/A 같은 텍스트가 섞이면 숫자 변환 단계에서 오류가 생길 수 있으므로 상태 열로 분리하거나 변환 전에 규칙에 따라 정리합니다.

자주 발생하는 문제와 해결표

Microsoft의 데이터 원본 오류 안내는 변환을 시험할 때 쿼리를 복사해 변형을 검증하고, 데이터 형식과 머리글 단계를 명시적으로 관리하라고 권합니다. 결과 셀의 Error는 쿼리 전체가 멈춘 오류와 다를 수 있으므로 오류 셀을 선택해 메시지를 먼저 읽고 제거·대체 여부를 결정해야 합니다.

실무 팁: 변환을 유지보수하기 쉽게 설계하기

쿼리 이름을 qry_sales_unpivot처럼 목적이 드러나게 정하고, 적용된 단계도 가능하면 월 열 세로화, 매출액 숫자 형식처럼 읽을 수 있는 이름으로 바꿉니다. 원본 표 이름, 쿼리 이름, 결과 표 이름을 구분하면 여러 담당자가 파일을 인수인계할 때 연결 관계를 이해하기 쉽습니다.

중요한 변환을 바꾸기 전에는 쿼리를 복제해 소량 표본으로 시험합니다. Microsoft 공식 자료에 따르면 원본을 크게 바꾸지 않고 복사된 열에서 변환을 시험하는 방식이 새로 고침 오류를 줄이는 데 도움이 됩니다. 원본 부서 열을 직접 복잡하게 가공해야 한다면 먼저 열을 복제하고 후속 단계가 어느 열을 참조하는지 확인하십시오.

결과를 피벗 테이블이나 차트에 연결할 때는 원본 가로표가 아니라 피벗 해제 결과 표를 사용합니다. 그러면 월을 축이나 열에, 매출액을 값에 배치할 수 있고 새 월이 추가돼도 필드 구조가 바뀌지 않습니다. 다만 새로 고침 순서는 Power Query 결과를 먼저 갱신한 다음 피벗 테이블을 갱신하도록 확인합니다.

한계와 주의사항

피벗 해제는 잘못된 원본 값을 자동으로 정정하지 않습니다. 1월 열에 2월 값이 들어 있으면 결과에서도 잘못된 월로 남습니다. 병합 셀, 여러 줄 머리글, 소계와 본문이 섞인 보고서는 먼저 구조를 정리해야 합니다. 원본의 빈 셀 의미와 중복 행 정의를 정하지 않은 채 변환하면 모양만 깔끔한 잘못된 데이터가 됩니다.

Power Query 기능 범위와 메뉴는 Excel 버전, Windows·Mac·웹 환경, Microsoft 365 구독에 따라 다를 수 있습니다. Microsoft 공식 지원 문서는 열 피벗 해제 적용 대상으로 Microsoft 365용 Excel, Excel 2024, 2021, 2019, 2016을 안내하지만 실제 편집·새로 고침 기능은 환경별로 차이가 있을 수 있습니다. 팀 표준 환경에서 시험한 뒤 배포하고, 다른 환경 사용자는 결과 표만 소비할지 쿼리까지 새로 고칠지 역할을 정하십시오.

최종 체크리스트

• 원본 사본을 보관하고 데이터를 Excel 표로 만들었다.
• 열 머리글이 한 줄이며 중복 이름·병합 셀·중간 합계가 없다.
• 분석 기준이 되는 부서·제품 같은 식별 열을 구분했다.
• 새 월 열 자동 포함이 필요해 다른 열 피벗 해제를 선택했다.
• 특성과 값 열을 업무 의미에 맞게 월, 매출액으로 바꿨다.
• 월과 매출액의 데이터 형식을 명시적으로 확인했다.
• 빈 셀, 0, 미정의 업무 의미를 구분했다.
• 기대 행 수와 변환 결과 행 수를 비교했다.
• 변환 전후 전체 합계와 월별 합계를 대조했다.
• 새 월 열을 추가해 새로 고침 시험을 했다.
• 쿼리 결과 표를 직접 수정하지 않고 원본·매핑 표에서 값을 관리한다.
• 팀의 Excel 버전에서 편집과 새로 고침 가능 여부를 확인했다.

공식 참고 자료

• Microsoft Learn, 피벗 테이블 해제 열: https://learn.microsoft.com/ko-kr/power-query/unpivot-column
• Microsoft Support, Excel용 파워 쿼리 도움말: https://support.microsoft.com/ko-KR/Excel/power-query-for-excel-help
• 광양시, 2026년 ICT이노베이션스퀘어 데이터 교육 운영 계획(피벗·피벗 해제 활용 항목): https://gwangyang.go.kr/boardDownload.es?bid=0001&list_no=194474&seq=1
• 확인일: 2026-08-18

Power Query의 핵심 가치는 가로표를 한 번 세로로 바꾸는 데 그치지 않습니다. 어떤 열을 유지하고 어떤 열을 피벗 해제했는지 단계로 기록해 같은 규칙을 다음 달 데이터에도 재실행할 수 있다는 점에 있습니다. 원본 구조와 검수식을 함께 관리하면 반복적인 복사·붙여넣기 없이도 분석 가능한 월별 데이터를 안정적으로 유지할 수 있습니다.