엑셀 자동화, 파일보다 먼저 정할 것

1. “정리해 주세요”를 입력과 출력으로 바꾸기

요청을 “거래처 파일을 합쳐 주세요”로만 적으면 서로 다른 결과를 떠올리기 쉽습니다. 어떤 폴더의 어떤 시트를 읽고, 어느 날짜 범위를 처리하며, 무엇을 기준으로 합계를 낼지 적어보세요. 화면 디자인보다 이 규칙이 먼저입니다.

예를 들어 “매주 받은 주문 파일에서 취소 건을 제외하고, 주문일·상품코드·수량·금액으로 통일한 뒤 상품별 합계와 오류 목록을 각각 저장한다”는 설명은 입력과 결과를 함께 확인할 수 있습니다. 아래 예시는 설명을 위한 합성 자료이며 실제 고객 주문이 아닙니다.

• 입력 예시: A사 주문일/상품번호/수량/합계 → 표준 날짜/상품코드/수량/금액
• 출력 예시: 통합.csv, 상품별집계.xlsx, 확인필요.xlsx
• 처리 범위: 이번 주 파일만 포함하고 지난 실행 결과 파일은 제외

2. 정상 파일과 깨지는 파일을 함께 준비하기

평소 잘 정리된 파일 한 개만으로는 예외를 발견하기 어렵습니다. 서로 다른 거래처의 양식, 빈 시트, 필수 열이 없는 파일, 합계 행이 끼어 있는 파일을 함께 봐야 합니다. 파일 이름과 폴더 위치가 달라져도 처리할지, 정해진 위치만 허용할지도 정합니다.

Microsoft의 Power Query 파일 결합 기능은 같은 구조의 여러 파일을 모으는 용도를 안내합니다. 구조가 다른 파일은 먼저 공통 열과 변환 규칙을 정해야 합니다. 고객명·연락처·실제 주문번호는 임의 값으로 바꾸되, 문제가 발생하는 셀 형식과 빈칸은 남겨두세요.

• 정상 예시 1개와 서로 다른 양식 예시 1개
• 담당자가 손으로 고쳐야 했던 오류 예시
• 사람이 확인한 최종 결과 파일과 그때 사용한 입력 파일

3. 숫자처럼 보여도 숫자로 바꾸면 안 되는 값

상품코드 00125를 숫자로 읽으면 앞의 0이 사라질 수 있습니다. 반면 금액 “12,000원”은 문자를 제거하고 숫자로 해석해야 집계할 수 있습니다. 날짜 03/04는 작성 환경에 따라 의미가 달라질 수 있으므로 날짜 형식도 명시합니다. Microsoft 데이터 형식 문서처럼 열의 유형과 변환 기준을 따로 다루는 이유입니다.

계산식이 들어 있는 셀도 확인해야 합니다. 화면에 보이는 계산 결과를 읽을지, 계산식을 보존할지, 프로그램이 다시 계산할지를 구분하세요. 도구가 엑셀 프로그램과 똑같이 모든 수식을 계산한다고 가정하지 않는 것이 좋습니다.

• 상품코드·우편번호: 문자로 보존할 항목인지 확인
• 금액·수량: 통화, 소수점, 반품 음수 처리 기준 정의
• 날짜: 표준 출력 형식과 해석할 입력 형식 정의

4. 중복 제거와 오류 삭제는 다른 작업

같은 상품이 두 줄 있다고 해서 중복 주문은 아닙니다. 주문번호가 같아도 분할 배송이나 옵션 차이로 정상인 경우가 있습니다. “주문번호+상품코드+옵션이 모두 같을 때”처럼 중복 판단 기준을 정하고, 충돌 시 최신 행을 남길지 확인 목록으로 보낼지 합의합니다.

숫자로 바꿀 수 없는 금액을 0으로 바꾸면 처리는 끝나도 매출 합계가 틀릴 수 있습니다. Microsoft는 처리 단계 전체의 오류와 셀 단위 오류를 구분합니다. 업무에서도 파일 전체 중단, 해당 행 보류, 경고 후 진행 중 무엇을 선택할지 오류 종류별로 정하면 좋습니다.

• 필수 열 없음 → 파일 중단, 파일명과 사유 표시
• 금액 해석 실패 → 해당 행 보류, 원본 행 번호 표시
• 선택 항목 빈칸 → 허용 여부를 정한 뒤 경고 또는 통과

5. 완료 여부는 합계와 처리 내역으로 검수하기

검수용 합성 입력에 5행이 있고 정상 4행·오류 1행이라면, 결과에서 그 관계를 설명할 수 있어야 합니다. 행을 합치는 집계와 중복 제거가 있다면 “읽은 행 → 제외된 행 → 집계에 사용한 행”을 단계별로 남기세요. 단순히 결과 행 수가 원본과 같아야 하는 것은 아닙니다.

수량·금액 합계뿐 아니라 표본 행을 직접 대조합니다. 처음과 끝 행, 음수 반품, 날짜 경계, 상품코드의 앞자리 0 등 위험한 부분을 골라 확인하세요. 새롱 공개 엑셀 데모의 5행·수량 10·122,000원은 이처럼 결과를 대조할 수 있도록 만든 합성 예시입니다.

6. 원본 보존과 재실행 방법까지 납품 범위에 넣기

자동화 결과는 원본과 다른 폴더에 저장하고 실행 시각이나 실행 번호로 구분하는 방식을 검토하세요. 중간에 실패했을 때 불완전한 파일을 정상 결과로 사용하지 않도록 완료 상태를 표시해야 합니다.

같은 파일을 두 번 실행하면 결과가 중복 누적되는지도 확인합니다. 파일을 덮어쓸지 새 결과를 만들지, 이전 실행을 취소할 수 있는지에 따라 설계가 달라집니다. 설치 권한, 엑셀 버전, 공유 폴더 접근 권한도 실제 사용 PC에서 확인할 항목입니다.

• 입력 원본과 출력 폴더가 분리되어 있는가?
• 실패한 파일과 오류 이유를 담당자가 찾을 수 있는가?
• 동일 입력의 재실행 결과를 설명할 수 있는가?
• 양식 변경 시 누가 규칙을 수정하고 검수하는가?

7. 자동화가 아직 이른 경우도 있습니다

한 번만 처리하는 작은 파일이고 사람이 바로 확인할 수 있다면 기존 엑셀 기능으로 끝내는 편이 나을 수 있습니다. 매주 열과 계산 기준이 바뀌거나 담당자마다 합계의 정의가 다르다면 업무 규칙 정리가 먼저입니다.

의뢰할 때는 개인정보 없는 입력 예시, 원하는 결과, 예외 세 가지를 준비하면 충분합니다. 처음부터 모든 거래처와 기능을 넣기보다 가장 자주 반복되는 한 양식으로 검수 기준을 확정하고 확장하면 변경 범위를 판단하기 쉽습니다.