엑셀 자동화: 월간 보고서에서 누락 파일·바뀐 열을 잡는 Power Query 설계

AI Editter

월말 보고서의 새로 고침이 성공했는데 지점 하나의 매출이 빠졌다면, 오류 메시지를 고치는 방법만으로는 문제를 해결할 수 없습니다. Power Query는 폴더에 들어 있는 파일을 처리할 수 있지만, 들어와야 했는데 도착하지 않은 파일까지 자동으로 알지는 못합니다.

이 글은 폴더 결합을 이미 사용하는 보고서를 대상으로 합니다. 파일을 합치는 절차 위에 제출 목록 대조, 열 구조 검사, 오류 행 분리, 합계 검산을 추가합니다. 예시는 가상의 3개 지점이며 실제 매출 자료가 아닙니다.

자동으로 끝내면 안 되는 실패를 정한다

예를 들어 거래처 8곳의 자료를 합치는데 7개 파일만 들어왔다면, 새로 고침 자체는 성공해도 월간 보고서를 배포하면 안 됩니다. 제출 대상 목록과 실제 수신 파일을 비교한 결과가 비어 있을 때만 배포하는 기준을 두세요. 주문번호 형식이 바뀌거나 금액 열이 사라진 경우도 같은 중단 대상입니다. 실행 버튼을 줄이는 것보다 누락을 드러내는 검증 단계가 먼저입니다.

파일 개수보다 먼저 확인할 것은 제출 대상

10월 제출 대상이 서울·부산·대전 세 곳이라고 가정합니다. 폴더에 파일이 세 개 있어도 서울.xlsx, 서울_수정.xlsx, 부산.xlsx라면 대전은 누락됐습니다. “파일 수 3개”라는 검사만으로는 잡히지 않습니다.

이 제출 대상표는 실제 자료와 별개로 관리합니다. 파일 목록에서 지점코드·대상월을 추출해 대상표와 병합하면 미제출과 중복 제출을 구별할 수 있습니다. 버전 선택은 파일 수정 시각만으로 결정하지 않는 편이 좋습니다. 복사·다운로드 과정에서 시각이 달라질 수 있으므로, 제출자가 확정한 버전이나 승인 목록을 사용합니다.

정상적으로 거래가 0건인 지점도 “0건 제출” 기록이 있어야 합니다. 파일이 없다는 이유로 매출 0원으로 처리하면 미제출과 실제 무매출을 구분할 수 없습니다.

결합 쿼리를 네 갈래로 나누는 구조

검사 쿼리는 연결 전용으로 숨겨 버리기보다 담당자가 볼 수 있는 점검 시트에 필요한 결과를 로드합니다. 문제가 있는 파일을 제외하고 집계를 만들 수는 있지만, 그때 보고서는 확정이 아닌 일부 누락 상태여야 합니다. 전월 결과가 그대로 남아 있는데 새로 고침 시각만 최신처럼 보이는 상황도 피해야 합니다.

필수 열이 사라지면 명확한 이유로 중단하기

예를 들어 모든 파일에 거래일, 지점코드, 주문번호, 금액 열이 필요하다고 정합니다. 머리글을 정리한 직후, 형식을 변환하기 전에 아래 M 코드를 넣을 수 있습니다. 원본은 해당 파일의 실제 머리글 승격 단계 이름으로 바꿉니다.

let
    필수열 = {"거래일", "지점코드", "주문번호", "금액"},
    누락열 = List.Difference(필수열, Table.ColumnNames(원본)),
    검사 = if List.IsEmpty(누락열) then 원본
        else error Error.Record(
            "필수열누락",
            Text.Combine(누락열, ", "),
            [필요한열 = 필수열]
        )
in
    검사

새 열이 추가되는 것은 허용할지, 반드시 확인할지 따로 정합니다. 열 순서 변경과 열 이름 변경도 다릅니다. 이름으로 열을 선택하면 순서 변경에 대응할 수 있지만, 금액이 결제금액으로 바뀌었을 때 두 항목의 의미까지 같다고 추정해서는 안 됩니다.

MissingField.UseNull을 넣으면 없는 열을 null로 채울 수 있습니다. 선택 입력인 비고에는 적합할 수 있지만 필수 금액 열에 적용하면 누락이 눈에 잘 띄지 않게 됩니다. 옵션의 동작은 Table.SelectColumns 공식 문서를 참고합니다.

오류를 없애기 전에 오류 행을 남기기

금액을 숫자로 바꾸는 단계에서 1O000처럼 영문 O가 들어간 값은 오류가 됩니다. 이때 “오류 제거”를 누르면 해당 주문 자체가 사라집니다. 원본을 참조하는 별도 쿼리에서 try Number.FromText([금액], "ko-KR") 결과의 오류 여부를 확인하고, 실패한 원본 값·파일명·행 번호를 보관합니다.

동일한 값이라도 지역 설정에 따라 소수점과 천 단위 구분자의 해석이 달라질 수 있습니다. 원본 시스템의 숫자 형식을 확인한 뒤 변환 문화를 고정합니다. 숫자 변환에 성공했다는 사실만으로 금액의 단위가 원인지 천 원인지까지 확인되는 것은 아닙니다.

필수 식별자가 빈 행, 대상월 밖의 거래, 거래번호 중복도 각각 분리합니다. 오류 건수와 제외 금액을 보고서에 표시하고, 수정 파일을 받은 뒤 전체 쿼리를 다시 실행합니다. 이전 예외 목록의 행을 손으로 삭제해 완료 처리하지 않습니다.

이번 달 결과를 확정할 수 있는 검산

가상의 원본 통제값이 서울 120행·12,000,000원, 부산 80행·8,000,000원, 대전 0행·0원이라면 결과도 이 값으로 돌아와야 합니다. 대전의 0건 제출 확인까지 있어야 세 지점 모두 완료입니다.

실제 운영 전에는 복사본 폴더에서 이 시험을 수행합니다. 정상 파일만 새로 고침해 보는 것보다, 잘못된 파일을 넣었을 때 보고서가 어떻게 멈추는지 확인하는 편이 사고 예방에 도움이 됩니다.

새로 고침 담당자가 바뀌어도 같은 결과를 얻으려면

입력 폴더, 확정 파일 선택 기준, 대상월, 원본 열 정의, 예외 처리 담당자를 통합 문서의 안내 시트에 남깁니다. 원본 파일을 보관한 날짜와 보고서 확정 시각도 구분합니다. 새로 고침 시각은 자료가 최신이라는 보증이 아닙니다.

매달 마지막으로 확인할 문장은 구체적이어야 합니다. “새로 고침 성공” 대신 “제출 대상 3곳 중 3곳 확인, 미확정 버전 0개, 필수 열 오류 0개, 금액 변환 오류 0행, 원본 대조 차이 0원”처럼 기록합니다. 각 수치가 실제 검사 결과에서 나온 경우에만 보고서를 확정합니다.

관련 절차: 엑셀 파일 합치기: Power Query 폴더 연결·새로 고침 방법