무역 통계 엑셀 교차분석 자동화 소개 영상
문제
이 고객사는 국가 통계기관이 분기마다 공개하는 ICT 서비스 수출입 무역통계를 받아 보고서용 교차분석표로 가공하는 일을 합니다. 문제는 원본 엑셀의 생김새였습니다. 하나의 워크북 안에 연전체, 상반기, 하반기, 1분기부터 4분기까지 시트가 나뉘어 있고 보고서를 만들려면 이 값들을 유형별, 지역별, 기관형태별로 다시 피벗해 옮겨 적어야 했습니다. 담당자가 밝힌 기준으로 매 분기 네 시간 남짓이 이 옮겨 적기에 들어가고 있었습니다.
시트마다 헤더 행의 위치가 다르다는 점이 첫 번째 골칫거리였습니다. 어떤 시트는 제목과 단위 안내가 위에 몇 줄 붙어 있고 어떤 시트는 바로 표가 시작됩니다. 사람이 매번 눈으로 헤더를 찾아 확인한 뒤에야 값을 옮길 수 있었습니다. 여기에 백만달러, 억달러, 비중(%) 단위 변환이 시트마다 다시 계산으로 붙었고 6년 추이를 보려면 연도별 파일을 여섯 개 모두 열어 수치를 모아야 했습니다.
가장 위험한 것은 검증이었습니다. 사람이 손으로 만든 교차표는 셀 하나가 틀려도 눈으로 잡아내기가 사실상 불가능합니다. 그렇게 만들어진 표가 그대로 보고서가 되면 오류는 발견되지 않은 채 배포됩니다. 반복 노동을 줄이는 것만큼이나, 결과가 맞다는 것을 사람 없이 확인해 주는 장치가 필요했습니다.
접근
기능
시트마다 다른 헤더를 자동으로 찾는 감지기
원본 워크북의 각 시트를 위에서부터 스캔해 표준 헤더 아홉 개 가운데 여섯 개 이상이 일치하는 행을 진짜 헤더로 판정합니다. 제목이나 단위 안내가 앞에 붙어 있어도, 반대로 헤더 없이 데이터가 바로 시작해도 흡수합니다. 표로 볼 수 없는 시트는 건너뛰고 그 사실을 리포트에 남깁니다.
헤더를 찾은 뒤에는 값을 하나의 공통 스키마로 정규화합니다. 년도, 기간, 유형, ICT산업, 기관형태, 지역과 수출, 수입, 수지까지 아홉 개 열로 통일하고 엑셀이 콤마를 섞어 내보낸 숫자를 숫자로 되돌리며 텍스트의 앞뒤 공백을 정리합니다. 이렇게 여러 워크북과 여러 시트가 하나의 통합 데이터프레임으로 모이면, 그다음부터는 사람이 시트를 오갈 일이 없어집니다.
기능
유형·지역·기관형태 × 산업 교차표를 한 번에 생성
통합된 데이터 위에서 유형별, 지역별, 기관형태별로 ICT 산업과 교차한 표를 자동으로 피벗합니다. 수출, 수입, 수지 세 측정값이 각각 별도 시트로 나뉘고 연도와 반기, 분기 합계에 더해 6년 추이와 억달러, 비중 시트까지 한 번의 실행으로 만들어집니다. 사람이 손으로 하던 단위 변환 계산이 통째로 사라집니다.
교차표를 만드는 것으로 끝내지 않았습니다. 고객이 이전에 사람 손으로 만들어 둔 참고용 엑셀을 기준으로, 자동 생성된 값을 셀 단위로 대조하는 검증 단계를 붙였습니다. 합계는 1.0 백만달러, 비중은 0.001, 억달러 환산은 0.01까지를 허용 오차로 두고 그 범위를 넘는 셀만 골라냅니다.
기능
참고 엑셀과 셀 단위로 대조하는 무오차 검증
자동 결과와 참고 엑셀을 셀 하나 단위로 비교해 허용 오차를 넘는 곳이 있으면 어느 시트의 어느 셀이 얼마나 다른지를 검증 상세 시트에 그대로 기록합니다. 자기 정합성 검사(합계가 세부의 합과 맞는지)와 외부 대조를 함께 돌려 통과와 실패 건수를 요약 시트 한 장으로 보여 줍니다.
마지막은 전달 방식이었습니다. 통계 담당자는 개발 환경을 다루지 않고 데이터는 내부 망 밖으로 나가면 안 되는 성격이었습니다. 그래서 파이썬이나 라이브러리 설치가 전혀 필요 없는 단일 실행 파일로 묶고 클릭으로 쓰는 화면과 자동화용 명령줄을 함께 넣었습니다.
기능
설치가 필요 없는 단일 실행 파일과 명령줄
PySide6로 만든 한글 데스크탑 화면은 폴더를 고르고 버튼을 누르면 진행률 바와 단계별 로그로 어디까지 처리됐는지 보여 줍니다. 같은 엔진을 명령줄로도 제공해 예약 작업이나 배치 스크립트에 연결할 수 있습니다. PyInstaller로 하나의 실행 파일로 패키징되어 완전히 오프라인으로 돕니다.
전체 흐름은 다음과 같습니다.
글로 요약하면 이렇습니다. 원본 폴더의 여러 엑셀이 헤더 감지와 정규화를 거쳐 하나의 통합 데이터가 되고 그 위에서 교차표가 자동으로 만들어진 뒤 참고 엑셀과 셀 단위로 대조됩니다. 교차표와 추이, 검증 요약과 상세가 모두 하나의 결과 엑셀로 저장됩니다. 이 모든 과정을 설치가 필요 없는 데스크탑 앱이 오프라인에서 처리합니다.
결과
담당자가 시트를 하나씩 열어 값을 옮기고 피벗을 다시 만들던 반복이 사라졌습니다. 원본과 참고 엑셀이 든 폴더를 지정하고 버튼을 누르면 1분 안팎에 결과 엑셀 한 개가 나옵니다. 단위 변환과 6년 추이처럼 손이 많이 가던 부분도 시트로 자동 산출되어 담당자는 결과를 검산하는 대신 결과를 읽는 일에 집중할 수 있게 됐습니다.
검증이 자동으로 붙었다는 점이 특히 컸습니다. 사람 눈으로는 잡기 어려운 셀 단위 오차를 프로그램이 대조해 주므로, 어느 시트의 어느 셀이 틀렸는지가 리포트에 그대로 남습니다. 값이 맞다는 확신이 사람의 집중력이 아니라 정해진 규칙에서 나오게 된 셈입니다. 설치가 필요 없는 단일 실행 파일이라 통계 담당자가 개발자의 도움 없이 분기마다 그대로 돌려 쓰고 있습니다.
이 프로젝트에서 다룬 기술적 결정들은 아래 글에서 더 자세히 풀었습니다.
- 엑셀 헤더 자동 감지 설계: 시트마다 위치가 다른 헤더를 사람 없이 찾는 방법
- 이기종 엑셀 정규화: 콤마 숫자와 단위 변환을 흡수하는 공통 스키마
- 교차분석표 자동 피벗: 유형·지역·산업 교차표와 6년 추이를 한 번에
- 셀 단위 무오차 검증: 참고 엑셀과 대조해 틀린 셀을 집어내기
- 데스크탑 앱 단일 exe 배포: 설치 없이 오프라인으로 도는 GUI와 명령줄
- 엑셀 자동화 외주 전 체크리스트: 발주 전에 정리해 두면 좋은 것들
자주 묻는 질문
엑셀 서식이 분기마다 조금씩 바뀌면 다시 손봐야 하나요?
헤더 위치가 흔들리는 정도는 자동 감지가 흡수합니다. 시트를 위에서부터 스캔해 표준 헤더와 여섯 개 이상 맞는 행을 찾으므로, 제목이나 단위 안내가 몇 줄 붙거나 헤더 없이 데이터가 바로 시작해도 처리됩니다. 열 구성 자체가 완전히 달라지는 큰 개편이 아니라면 사람이 다시 손볼 일은 거의 없습니다.
결과가 맞다는 것을 어떻게 믿을 수 있나요?
기존에 사람이 만들어 둔 참고 엑셀을 기준으로 셀 단위 대조 검증을 함께 돌립니다. 허용 오차를 넘는 셀이 있으면 어느 시트의 어느 셀이 얼마나 다른지 검증 상세 시트에 기록되고 전체 통과와 실패 건수는 요약 시트로 정리됩니다. 값이 맞다는 판단이 사람의 눈이 아니라 정해진 규칙에서 나옵니다.
데이터를 외부로 보내지 않고 쓸 수 있나요?
네. 설치가 필요 없는 단일 실행 파일로 완전히 오프라인에서 돕니다. 파이썬이나 라이브러리를 따로 깔 필요가 없고 네트워크 연결도 요구하지 않으므로, 통계 원본을 내부 환경 밖으로 내보내지 않고 그대로 처리할 수 있습니다.