병합 셀 때문에 망가진 원본표, Power Query로 보고서용 데이터로 바꾸는 법

거래처에서 받은 엑셀 파일이나 현장 담당자가 입력한 관리대장을 열어 보면, 보기에는 그럴듯한데 분석에는 바로 못 쓰는 표가 꽤 많습니다. 대표적인 게 병합 셀, 중간 제목 행, 소계 행, 빈칸으로 이어지는 분류값입니다. 화면에서 볼 때는 깔끔하지만 피벗 테이블, SUMIFS, Power Query에 넣는 순간 기준 열이 비어 있어서 집계가 틀어집니다.

이번 글은 이런 원본을 두고 실무자가 자주 묻는 질문에 답하는 방식으로 정리해 보겠습니다. 핵심은 간단합니다. 보여주기용 표를 계산용 데이터로 바꾸는 과정입니다. 한 번만 정리해 두면 다음 달 파일도 같은 방식으로 반복 처리할 수 있습니다.

Q. 병합된 셀은 왜 실무 집계에서 문제가 되나요?

병합 셀 자체가 나쁜 것은 아닙니다. 보고용 화면에서는 분류명이 한 번만 보이니 깔끔합니다. 문제는 엑셀이 데이터를 읽는 방식입니다. 병합된 셀은 겉으로는 여러 행에 값이 있는 것처럼 보여도 실제 값은 맨 위 셀 하나에만 들어 있습니다. 나머지 행은 빈칸입니다.

예를 들어 아래처럼 보이는 표가 있다고 가정해 보겠습니다.

눈으로 보면 두 번째, 세 번째 행도 사무용품입니다. 하지만 피벗 테이블은 그렇게 추측하지 않습니다. 분류가 빈칸인 행으로 처리합니다. 그래서 분류별 금액을 집계하면 일부 금액이 빈 항목으로 빠지거나, 필터에서 누락됩니다.

Q. 원본 파일에서 병합을 먼저 풀면 끝 아닌가요?

병합을 푸는 것만으로는 부족합니다. 병합 해제를 하면 첫 행에만 값이 남고 아래 행은 빈칸이 됩니다. 따라서 병합 해제 → 빈칸에 위 값 채우기까지 해야 계산용 표가 됩니다.

수작업으로 처리한다면 순서는 이렇습니다.

• 분류 열 선택
• 병합하고 가운데 맞춤 해제
• 분류 열의 빈 셀 선택
• 위쪽 셀 값을 참조하도록 입력
• 수식 결과를 값으로 붙여넣기

빈 셀 선택은 홈 탭 > 찾기 및 선택 > 이동 옵션 > 빈 셀을 쓰면 빠릅니다. 빈 셀이 선택된 상태에서 =를 입력하고 바로 위 셀을 클릭한 뒤 Ctrl + Enter를 누르면 선택된 모든 빈칸에 위쪽 값을 채울 수 있습니다.

=A2

다만 이 방식은 원본이 매달 바뀌면 계속 반복해야 합니다. 파일이 한두 개면 괜찮지만, 거래처별 파일을 매주 받는 상황이라면 Power Query로 규칙을 만들어 두는 편이 훨씬 안정적입니다.

Q. Power Query에서는 어떤 기능을 써야 하나요?

가장 많이 쓰는 기능은 채우기 아래로입니다. Power Query 편집기에서 분류 열을 선택한 뒤 변환 탭 > 채우기 > 아래로를 누르면 빈칸에 바로 위의 값이 채워집니다.

작업 흐름은 보통 이렇게 잡습니다.

• 원본 범위를 표로 변환하거나, 데이터 범위를 Power Query로 불러오기
• 필요 없는 상단 제목 행 제거
• 첫 행을 머리글로 승격
• 분류 열에 채우기 아래로 적용
• 소계, 합계, 빈 행 제거
• 수량과 금액 열의 데이터 형식 확인
• 닫기 및 로드로 결과표 생성

여기서 중요한 포인트는 채우기 아래로를 너무 빨리 적용하지 않는 것입니다. 상단 제목 행이나 안내 문구가 남아 있는 상태에서 채우기 아래로를 하면 엉뚱한 문구가 데이터 행까지 내려올 수 있습니다. 먼저 불필요한 행을 걷어낸 뒤 적용하는 편이 안전합니다.

Q. 소계 행까지 같이 섞여 있으면 어떻게 걸러내나요?

실무 원본에는 거의 항상 소계 행이 있습니다. 예를 들면 품목 열에 사무용품 소계, 비품 소계처럼 들어 있거나, 거래일은 비어 있고 금액만 있는 행입니다. 이 행을 그대로 두면 집계할 때 금액이 두 번 잡힙니다.

Power Query에서는 조건을 정해서 제거합니다. 가장 단순한 기준은 품목 열에 소계라는 문자가 포함된 행을 제외하는 것입니다.

품목 열 필터 → 텍스트 필터 → 포함하지 않음 → 소계

거래일이 비어 있는 행을 제거하는 방법도 좋습니다. 단, 실제 거래일이 누락된 정상 데이터가 있을 수 있다면 먼저 원본 규칙을 확인해야 합니다. 무조건 빈 거래일 제거를 적용했다가 누락 데이터를 조용히 버리는 경우가 있습니다.

Q. Power Query에서 자동으로 만들어지는 코드는 어느 정도 이해해야 하나요?

버튼만 눌러도 대부분 처리되지만, 나중에 오류가 났을 때는 단계 이름과 코드 흐름을 조금 아는 게 도움이 됩니다. 예를 들어 분류 열을 아래로 채우는 단계는 대략 아래와 비슷한 형태로 기록됩니다.

= Table.FillDown(#"이전 단계", {"분류"})

소계 행을 제거하는 단계는 이런 식으로 만들어질 수 있습니다.

= Table.SelectRows(#"이전 단계", each not Text.Contains([품목], "소계"))

여기서 자주 생기는 오류는 열 이름이 바뀌었을 때입니다. 원본 파일의 머리글이 품목에서 품목명으로 바뀌면 기존 단계는 [품목] 열을 찾지 못해 오류가 납니다. 따라서 거래처 파일을 받을 때 열 제목이 자주 바뀐다면, 처음부터 열 이름을 표준화하는 단계를 넣어 두는 것이 좋습니다.

Q. 함수로도 비슷하게 처리할 수 있나요?

가능합니다. Power Query를 쓰기 어려운 환경이거나 결과를 바로 옆 열에 확인하고 싶을 때는 보조 열을 만들어 분류값을 채울 수 있습니다.

예를 들어 A열에 원본 분류가 있고, F열에 정리된 분류를 만들고 싶다면 F2에 아래 수식을 넣고 아래로 복사합니다.

=IF(A2="",F1,A2)

이 수식은 A2가 비어 있으면 바로 위에서 확정된 분류값을 가져오고, 값이 있으면 그 값을 사용합니다. 단순하지만 실무에서 꽤 많이 씁니다. 다만 첫 데이터 행 위에 제목이나 빈 행이 있으면 F1 참조가 꼬일 수 있으니 시작 행을 정확히 잡아야 합니다.

금액 열이 숫자로 보이는 텍스트라면 함께 점검해야 합니다. 쉼표가 포함된 텍스트 금액은 계산 결과가 이상해질 수 있으므로 별도 열에서 숫자로 바꿔 확인합니다.

=VALUE(SUBSTITUTE(E2,",",""))

함수 방식은 빠른 임시 정리에 좋고, Power Query 방식은 반복 업무에 좋습니다. 특히 매달 같은 형태의 파일을 받는다면 함수보다 Power Query가 관리하기 쉽습니다.

Q. 정리 후에는 무엇을 꼭 확인해야 하나요?

데이터 정리에서 가장 위험한 순간은 결과표가 너무 멀쩡해 보일 때입니다. 표가 깔끔하다고 해서 맞는 것은 아닙니다. 최소한 아래 항목은 확인해 보세요.

• 원본 총금액과 정리 후 총금액이 같은가?
• 소계 행을 제거했다면 원본 총액 비교 기준을 어떻게 잡을 것인가?
• 분류 빈칸이 완전히 사라졌는가?
• 날짜 열이 실제 날짜 형식인가?
• 수량, 금액 열이 숫자 형식인가?
• 품목명 앞뒤 공백 때문에 같은 품목이 분리되지 않는가?

특히 원본에 소계가 포함되어 있었다면 총금액 비교를 조심해야 합니다. 원본 전체 금액에는 상세 행과 소계 행이 같이 들어 있어 두 배처럼 보일 수 있습니다. 이럴 때는 원본에서도 상세 행만 필터링한 뒤 합계를 비교해야 합니다.

Q. 흔한 실수는 어떤 게 있나요?

첫 번째는 채우기 아래로를 모든 열에 적용하는 실수입니다. 분류 열에는 맞지만 거래일, 품목, 수량까지 아래로 채우면 원래 비어 있어야 할 값이 생겨 버립니다. 채우기 아래로는 보통 상위 분류, 부서, 프로젝트명, 거래처 그룹처럼 반복 의미가 있는 열에만 적용합니다.

두 번째는 합계 행 제거 전에 데이터 형식을 숫자로 바꾸는 실수입니다. 합계 행에 합계 같은 텍스트가 섞여 있으면 숫자 변환 단계에서 오류가 생길 수 있습니다. 보통은 불필요한 행 제거 후 형식 변환을 하는 편이 깔끔합니다.

세 번째는 원본 파일을 직접 고쳐 놓고 나중에 추적하지 못하는 경우입니다. 원본은 되도록 그대로 보관하고, 정리 결과를 별도 시트나 쿼리 결과로 만드는 습관이 좋습니다. 나중에 금액 차이가 발생했을 때 어디서 바뀌었는지 확인할 수 있습니다.

Q. 피벗 보고서까지 바로 연결하려면 어떻게 구성하면 좋나요?

정리된 결과표는 피벗 테이블의 원본으로 쓰기 좋습니다. 추천 구조는 아래처럼 단순합니다.

피벗 보고서까지 생각한다면 병합 셀, 빈 분류, 중간 제목 행은 모두 제거 대상입니다. 보고서 화면에서 예쁘게 보이는 것보다, 한 행이 하나의 거래를 정확히 의미하는 구조가 더 중요합니다.

이럴 때 이렇게 쓰면 된다

파일이 한 번만 필요하고 양이 적다면 병합 해제 후 빈 셀 선택, Ctrl + Enter 방식으로 빠르게 처리해도 충분합니다. 옆 열에서 검산하면서 처리해야 한다면 =IF(A2="",F1,A2) 같은 보조 열 수식이 편합니다.

반대로 같은 양식의 파일을 매달 받거나, 거래처별 파일을 반복해서 정리해야 한다면 Power Query로 가져오기, 채우기 아래로, 소계 행 제거, 형식 변환까지 단계로 저장해 두는 편이 낫습니다. 원본이 바뀌어도 새로 고침 한 번으로 결과표를 다시 만들 수 있기 때문입니다.

기준은 간단합니다. 한 번 쓰고 끝이면 수작업과 보조 수식, 반복해서 쓸 데이터라면 Power Query입니다. 병합 셀이 보이는 순간 바로 꾸미기 문제가 아니라 데이터 구조 문제로 보고, 분류값을 채운 뒤 상세 행만 남기는 순서로 정리하면 된다.