엑셀 월마감 업무를 줄이는 프로그램 제작하기

핵심 요약

1. 지점별 엑셀을 공통 양식으로 합쳐 월마감 집계표와 확인이 필요한 항목을 만듭니다. 2. pandas로 자료를 읽고, 직접 작성한 계산 코드와 검사 규칙으로 금액과 누락을 확인합니다. 3. 열 연결, 상품코드 보존, 단위 환산을 구현하고 AI의 역할은 매핑 후보 제시로 제한합니다.

월마감에서 시간이 걸리는 부분은 파일을 합치는 일보다 각 지점의 숫자가 같은 의미인지 확인하는 과정입니다. 프로그램 제작을 검토할 때는 ‘강남점_10월매출.xlsx’ 같은 실제 원본과 ‘상품코드·기준단위·환산계수’가 담긴 상품 기준표를 준비하면 구현 범위를 구체적으로 정할 수 있습니다. 아래는 이 자료를 처리하는 업무 자동화 프로그램의 제작 방법입니다.

pandas로 엑셀을 읽고 지점별 열 연결하기

pandas의 read_excel은 엑셀 파일의 한 개 또는 여러 시트를 DataFrame이라는 표 형태의 데이터로 읽을 수 있습니다. 파일 업로드 화면에서는 지점과 마감월을 지정하고, 읽어 온 시트의 열 이름을 미리 보여주도록 만듭니다. 담당자는 ‘판매액’과 ‘매출금액’처럼 이름이 다른 열을 공통 항목에 연결합니다.

이 연결 규칙을 컬럼 매핑이라고 하며, 지점별로 저장하고 다시 적용하는 로직은 직접 개발합니다. ‘매핑 적용’ 버튼을 누르면 원본 열과 변환 결과를 나란히 표시합니다. 필수 열이 모두 연결되는지 검사하고, 처음 등장하거나 의미가 불분명한 열만 담당자의 확인 대상으로 보냅니다.

문자 상품코드와 원본 행 보존하기

상품코드는 계산할 숫자가 아니라 상품을 구분하는 문자로 취급하도록 처리 규칙을 정합니다. ‘00123’처럼 앞에 0이 있는 코드는 읽기부터 저장과 결과 파일 출력까지 같은 표기를 유지하는지 확인합니다. 각 행에는 원본 파일명과 시트, 행 번호를 연결해 잘못된 결과의 출처를 찾을 수 있게 합니다.

원본에 ‘123’만 들어 있다면 임의로 0을 붙이지 않고 상품 기준표와 대조하도록 만듭니다. 검수에는 별도 상품인 ‘00123’과 ‘123’이 같은 코드로 합쳐지는 실패 사례를 넣습니다. 두 코드의 집계가 분리되고, 결과 엑셀을 다시 열었을 때도 코드가 유지되어야 통과하도록 합니다.

통화와 수량 단위를 계산 전에 통일하기

입력 자료에는 금액뿐 아니라 통화, 수량 단위, 적용할 환산 기준이 필요합니다. 예를 들어 상품 기준표에 ‘1상자=12개’가 등록된 품목은 수량을 개 단위로 바꾸고, 원래 수량도 함께 남기도록 만듭니다. 외화 금액은 담당자가 정한 기준일의 환율표를 적용하는 계산 로직으로 처리합니다.

환산 화면에는 원래 값, 적용 계수, 변환 값을 표시해 계산 근거를 확인하게 합니다. 통화나 환산계수가 없는 행은 임의로 처리하지 않고 확정 집계에서 제외하며 사유를 보여줍니다. 소수점 처리 위치와 반올림 기준도 미리 정하고, 승인한 계산 예시와 결과가 일치하는지 검수합니다.

dbt data tests로 합계 차이와 오류 행 찾기

dbt data tests에서는 SQL이라는 데이터 조회문으로 잘못된 행을 반환하는 검사를 작성할 수 있습니다. 이를 적용하려면 정리된 자료를 데이터베이스에 저장하고, 지점별 합계와 원본의 마감 합계를 대조하는 검사 규칙을 직접 작성합니다. 통화와 단위가 같은 기준으로 바뀐 값끼리 비교하도록 조건을 맞춥니다.

결과 화면에는 원본 합계, 계산 합계, 차액과 관련 행을 함께 표시합니다. 상세 내역에 합계 행까지 섞여 금액이 두 번 더해지는 경우를 막도록 집계 대상 판별 규칙도 넣습니다. 정상 예시의 차액이 정한 허용 범위 안에 들어오고, 오류 예시에서는 해당 행이 검출되는지 확인합니다.

제출 대상 목록으로 누락 점포 확인하기

누락 점포는 업로드된 파일만 보고 판단하지 않도록 만듭니다. 마감월별 제출 대상 지점 목록을 따로 받아 실제 접수 자료와 비교하고, 미제출 지점과 같은 지점의 중복 파일을 화면에 표시합니다. 휴점이나 폐점처럼 제출 대상에서 빠지는 조건도 월별 목록에 반영합니다.

‘마감 검토’ 버튼을 누르면 제출 대상 수, 접수 지점 수, 누락 지점명을 함께 보여주도록 구현합니다. 파일은 있어도 해당 월의 상세 내역이 없는 경우는 별도로 구분합니다. 매출이 없는 지점의 정상 제출 여부는 합계 0만으로 결정하지 않고, 사전에 정한 무실적 제출 표시로 확인합니다.

Celery beat로 정기 실행하고 마감 결과 확정하기

Celery beat는 정해진 주기로 작업을 보내고 워커라는 실행 프로그램이 처리하도록 합니다. 이를 활용해 정해진 시각에 접수 자료를 검사하되, 같은 마감월의 작업이 겹치지 않게 실행 잠금을 직접 구현합니다. 스케줄러 중복 실행을 통제하고, 재실행해도 같은 자료가 집계에 다시 더해지지 않도록 처리합니다.

AI를 연결한다면 낯선 열 이름에 맞는 공통 항목 후보를 제시하는 범위로 제한합니다. 합계와 환산은 확정된 규칙에 따라 코드가 계산하고, 매핑 예외만 사람이 확인하도록 만듭니다. 최종 화면에서는 검사를 통과한 월마감 집계표와 오류 목록을 내려받게 하며, 같은 입력으로 다시 실행한 결과가 일치하는지 검수합니다.

지점별 원본 엑셀과 상품 기준표, 현재 사용하는 월마감 결과 양식을 보내주시면 필요한 매핑 규칙과 합계 대조 범위를 정해 개발을 문의하실 수 있습니다. 트램스 AI 제작 문의 (https://trams.co.kr/ai-si/contact/#inquiry)