엑셀 수식 분석기 만들기 ② 수식 목록을 업무 규칙서로 바꾸기

먼저 결론

• 보고서 맨 앞에 한 장 요약·면담 질문·계산 규칙서·이관 설계 초안을 두었습니다.
• 시트를 따라 하는 작업 순서와, 쓰이지 않는 열·간소화할 수식 같은 수정 방향을 자동으로 뽑게 했습니다.
• "조용히 0이 됩니다" 같은 기계적인 문구 36곳을 사람이 읽는 말로 바꿨습니다.
• 수식 묶음을 pandas 코드로 옮겨 저장값과 대조했고, 한 묶음은 끝내 원인을 찾지 못했습니다.

셀 주소를 열 제목으로 바꾸자 규칙서가 됐다

보고서 순서를 컨설팅 순서로 바꿨습니다. 맨 앞은 한 장 요약입니다. 원천 → 계산 → 결과를 한 줄로 씁니다. 그 아래 매번 하는 일, 사람이 판단하거나 입력하는 곳, 먼저 고칠 것을 둡니다.

그다음이 면담 질문입니다. 입력 칸·원천·기준값·예외 칸·숨김 시트·결과 전달처처럼 파일만 봐서는 알 수 없는 것들이 있습니다. 이것을 분석 결과에서 자동으로 질문으로 만들고, 정리표에 답변 칸을 뒀습니다.

핵심은 계산 규칙서입니다. 수식 원문 SUMIFS(매출!C:C, 매출!A:A, B5)는 담당자에게 아무 뜻이 없습니다. 그래서 셀 주소를 그 열의 제목으로 바꿉니다. "[표.매출] 합계 — 조건 [표.지역] = [이 행의 지역]" 같은 업무 문장이 됩니다.

이때 제목 줄의 글자만 쓰고 데이터 행은 읽지 않게 했습니다. 규칙서에 셀 값이 새어 들어가지 않게 하려는 것입니다.

규칙서 뒤에는 이관 설계 초안이 붙습니다. 원천 테이블, 설정·기준 테이블, 집계 규칙의 SQL 초안, 화면·보고서, 사람이 하는 단계로 나눕니다.

버전 비교도 같이 넣었습니다. 이전 파일을 함께 넣으면 실제로 바뀐 입력 칸·수식·원천·쿼리·피벗만 보여 줍니다.

이 작업 중에 xlsb의 표 참조에 "무효" 플래그가 따로 있다는 것을 알게 됐습니다. 그 칸을 깨진 참조로 처리했더니, 실제 파일에서 깨진 참조가 전보다 여러 배 더 잡혔습니다.

전에는 멀쩡해 보이던 칸이었습니다.

작업 순서는 시트 참조 관계에서 나왔다

"이 파일을 받으면 무엇부터 하나"에 답하는 작업 순서를 만들었습니다. 시트 순서는 시트끼리의 참조 관계를 위상 정렬해 정합니다. 시트 안의 계산 단계는 수식 묶음의 의존 관계에서 가장 긴 경로로 정합니다.

각 단계에는 입력 칸을 읽는 수식, 목록에서 고르는 칸, 결과 칸, 확인할 것이 붙습니다.

수정 방향은 세 가지를 자동으로 뽑습니다. 첫째는 쓰이지 않는 열입니다. VLOOKUP처럼 열 번호를 고정해 읽는 함수는 조회 열과 반환 열 두 열만 읽는 것으로 셉니다. 표 구조적 참조는 열 단위로 셉니다.

둘째는 수식 간소화입니다. 같은 뜻의 수식을 Excel 2016·2019·365 버전별로 따로 제안합니다. 회사마다 쓰는 버전이 달라 365 전용 함수를 권하면 쓸 수 없기 때문입니다.

셋째는 차트와 피벗 점검입니다. 차트 XML과 피벗의 행·열·필터 필드를 읽어, 피벗 칸을 수식이 직접 참조하는 곳을 찾습니다.

보고서에 코드 말투가 그대로 나와 있었다

보고서를 읽은 뒤 가장 먼저 지적한 것은 문구였습니다. "조용히 0이 됩니다", "패턴 이탈", "4층", "끼어들면" 같은 표현이 들어 있었습니다. 코드를 설명하는 말투가 그대로 보고서에 나온 것입니다.

현재 코드에서 어색한 문구 36곳을 찾아 바꿨습니다. 위험 항목 '패턴 이탈'은 '같은 열 규칙과 다른 칸'으로 바꾸고, 정리표 시트 이름도 같이 바꿨습니다.

버전 비교의 '칸 묶음 N곳'은 '값이 바뀐 열 N개'로 고쳤습니다. 화면 제목도 '엑셀 파일 구조 분석'으로 정리했습니다.

25칸은 끝내 저장값과 맞지 않았다

목표는 "수식을 파이썬으로 옮긴 코드가 엑셀이 저장해 둔 값과 칸마다 같아야 한다"였습니다. 엑셀 파일에는 마지막 계산 결과가 값으로 저장돼 있습니다. 변환한 코드를 돌려 그 값과 비교하면 변환이 맞았는지 바로 알 수 있습니다.

보고서 8절에 이 파일에 맞춘 자동화 방법 표를 넣었습니다. 바로 실행되는 자동화_초안.py도 같이 저장하게 했습니다.

생성되는 스크립트는 원본을 임시 사본으로 읽고, 수식 묶음마다 pandas 계산을 합니다. 그 뒤 저장값과 비교한 결과를 엑셀로 씁니다. 같은 수식을 여러 열에 채운 묶음은 열마다 참조를 옮겨 보고, 모양이 같으면 반복문 하나로 씁니다.

합성 시험 파일에서는 xlsx·xlsm·xls가 모두 통과했고, xlsb는 14묶음 중 13이 일치했습니다. 남은 1개는 시험 파일을 만든 엑셀에 XLOOKUP이 없어 저장값 자체가 #NAME?인 칸이었습니다.

실제 업무용 xlsb 한 개로 돌렸을 때는 상황이 달랐습니다. 묶음 대부분이 변환됐고, 그중 하나만 빼고 전부 저장값과 일치했습니다.

불일치는 AVERAGE 한 묶음의 25칸이었습니다.

저는 먼저 수식을 잘못 읽었다고 봤습니다. 그 묶음의 수식 복원 결과를 다시 확인했는데 맞았습니다. 첫 가설은 틀렸습니다.

다음으로 입력 칸을 잘못 읽었다고 봤습니다. 그 묶음이 참조하는 칸의 읽기도 다시 확인했지만 맞았습니다. 두 번째 가설도 틀렸습니다.

세 번째 가설은 마지막 계산 뒤에 입력이 바뀐 채 저장됐다는 것입니다. 수동 계산 모드이거나 저장 직전에 값을 붙여 넣으면 그렇게 됩니다. 이것은 확인하지 못했습니다.

틀린 가설을 검증하는 동안 다른 문제들을 찾아 고쳤습니다. xlsb 읽기 라이브러리가 오류값을 0x2a 같은 글자로 돌려주고 있어 #N/A 등으로 바꿨습니다. 수식이 돌려준 빈 글자를 빈 칸으로 보던 문제도 고쳤습니다.

합계 범위에 오류가 있으면 오류를 전파해야 하는 규칙도 반영했습니다. SUMIFS가 날짜처럼 생긴 글자를 날짜로 맞추는 동작도 넣었습니다. 실제 파일의 한 묶음은 이 마지막 처리 뒤에야 일치했습니다.

변환하지 못한 묶음도 있습니다. 깨진 참조, GETPIVOTDATA, LARGE와 IF를 겹친 배열 수식, RANK, 상대 위치 이름입니다. 피벗은 데이터 모델이 원본이라 아예 변환 대상에서 뺐습니다.

바꾼 것과 아직 못 한 것

바꾼 것은 보고서의 중심입니다. 수식 건수 대신 한 장 요약과 계산 규칙서가 앞에 옵니다. 작업 순서와 수정 방향이 자동으로 나오고, 수식을 파이썬으로 옮긴 초안이 저장값 대조 결과와 함께 나옵니다.

못 한 것은 AVERAGE 한 묶음의 불일치 원인입니다. 파일을 만든 담당자에게 저장 당시 상황을 물어야 알 수 있는 일이라 분석기 안에서는 풀 수 없었습니다. 변환하지 못하는 함수 목록도 그대로 남아 있습니다.

수식 목록을 규칙서로 바꾸는 일은 코드보다 말을 고르는 일에 가까웠습니다. 한 묶음은 맞지 않았고, 그 이유는 파일 밖에 있었습니다.

다음 편에서는 수백 MB에서 GB 단위 파일을 받으면서 겪은 멈춤과 메모리 문제를 다룹니다. AI가 만든 HTML 시연물을 거꾸로 읽어 실제 구조로 바꾸는 기능도 함께 적습니다.