개발 이야기

계약번호 하나가 비었는데, 금액까지 빠진 이유

계약번호 누락이 금액 집계까지 막던 문제를 고치고, 금액·계약 건수·품목 수의 기준을 나눴어요.

사이트 공개일
기록 수정일
기술과 주제
Excel 수식 · 집계 규칙 · 회귀 검증
이 글의 목차

필수값이 빠졌다는 표시가 금액까지 막고 있었어요

구매실적 관리 엑셀을 전달한 뒤, 고객이 며칠간 직접 입력한 파일과 문의 문서를 보내왔어요. 자료는 들어가 있는데 금액 실적과 점수가 기대대로 나오지 않는 항목이 있었어요.

원본 입력과 계산을 대조하니 계약번호가 비어 있는 행이 영향을 주고 있었어요. 번호가 없다는 이유로 금액 실적까지 집계에서 빠지고 있었죠. ‘완성된 입력인가’와 ‘이 금액을 실적으로 인정할 수 있는가’가 한 조건에 묶여 있었어요.

계약 건수를 세려면 계약번호가 필요해요. 하지만 금액 실적의 인정 여부는 조직, 연도, 구매 분류와 같은 해당 지표의 조건으로 판단해야 했어요. 계약번호 누락은 보완해야 할 문제였지만, 모든 계산을 막는 이유가 되어서는 안 됐어요.

그래서 금액 집계와 입력 확인을 분리했어요. 인정 조건을 충족한 금액은 반영하고, 계약번호가 필요한 계산에서는 해당 행을 제외하면서 ‘계약번호 확인’을 보여주도록 바꿨어요. 경고도 막연한 ‘필수값 확인’에서 실제 누락 항목을 알려주는 방식으로 정리했어요.

하나의 행도 지표마다 다르게 세어요

금액, 계약 건수, 품목 수는 서로 다른 단위를 사용해요. 아래는 차이를 설명하기 위해 새로 만든 데이터예요. 세 행 모두 해당 지표의 나머지 인정 조건을 충족했다고 가정해요.

계약번호품목지급금액
DEMO-A책상100,000원
DEMO-A책상50,000원
미입력의자200,000원

금액 합계는 350,000원이에요. 식별 가능한 계약은 DEMO-A 한 건이고, 번호가 없는 행은 확인 대상으로 남겨요. 품목 수는 같은 이름을 한 번씩 세면 두 종류예요. 지급 행은 세 개지만 계약도, 품목도 세 개는 아니죠.

계산 대상사용하는 단위빠진 정보의 처리
금액 실적조건을 충족한 지급 행의 금액계약번호 누락과 금액 인정 여부를 별도로 판단
전자대금 계약 건수조건을 충족한 고유 계약번호번호가 없으면 건수에서 제외하고 안내
다품목 실적조건을 충족한 고유 품목명품목명이 없으면 확인 표시

전자대금 사용 여부는 같은 계약의 지급 내역을 함께 살폈어요. 첫 행에 사용 표시가 없더라도 뒤의 지급 행에 표시가 있다면 그 계약의 사용 실적을 놓치면 안 됐거든요. 반대로 지급 행이 늘었다고 계약 수가 늘어서도 안 됐어요.

품목은 이름을 기준으로 중복을 구분해요. 같은 품목은 같은 이름으로 입력하도록 안내했고, 비어 있는 계약번호나 품목명을 임의로 만들어 채우지는 않았어요.

분모에서 빠져도 특정 실적에는 들어갈 수 있어요

후속 수정에는 또 다른 예외가 있었어요. 공통 구매금액에서는 제외하지만, 승인을 받으면 특정 구매 지표의 실적으로는 인정하는 항목이 있었죠.

이때 공통 집계 조건을 통째로 풀면 다른 지표의 분모까지 달라져요. 한 항목을 고치다가 관계없는 점수도 바뀔 수 있어요. 그래서 공통 분모와 지표별 인정 실적을 분리하고, 예외는 해당 지표의 분자에만 반영했어요.

조건도 ‘승인 여부’ 하나로 끝나지 않았어요. 조직 구분, 해당 연도, 지급 방식과 품목 예외를 함께 살펴야 했어요. 이미 일반 인정 경로에 포함된 금액이 예외 경로에서도 더해지지 않는지도 확인했고요.

지표별 조건을 나누니 변경의 범위도 분명해졌어요. 특정 승인 실적을 추가했을 때 해당 지표만 달라지는지, 공통 분모는 그대로인지 각각 검증할 수 있었어요.

요청한 숫자를 바꾸기 전에 기준을 다시 봤어요

고객이 가점 만점 구간을 다르게 이해해 수정을 요청한 일도 있었어요. 원래 평가기준과 계산을 다시 대조했고, 고객도 비율을 나누는 값을 잘못 이해했다고 확인해 주었어요. 이 부분은 기존 수식을 유지했어요.

대신 목표와 실적, 부족액을 함께 보여주고 원점수·배점·득점을 나눠 표시했어요. 계산을 바꾸는 일과 계산을 이해하기 쉽게 만드는 일은 구분할 필요가 있었죠!

부족액도 같은 관점에서 설명해야 했어요. 기본 형태는 MAX(0, 현재 기준액 × 목표비율 − 인정 실적)이지만, 추가 구매가 기준액에도 포함되면 분모가 달라져요. 지금 보이는 부족액을 그대로 구매하면 항상 목표에 도달한다고 해석할 수는 없어요.

또 종류별 목표가 있는 지표에서는 한 종류의 초과 실적이 다른 종류의 부족분을 가리지 않게 했어요. 전체 합계만 맞추는 계산으로는 표현할 수 없는 규칙이었어요.

경계값과 원본 보존을 함께 검증했어요

현재 파일의 점수 하나만 맞추면 우연히 맞는 수식도 통과할 수 있어요. 그래서 인정 여부와 연도를 바꾸고, 중복 자료를 추가하고, 목표 경계를 넘기는 식으로 조건을 바꿔 확인했어요.

조건을 바꾼 검증확인하려던 것
계약번호 반복·누락, 뒤쪽 지급의 사용 표시고유 계약 수와 입력 보완 안내
승인 여부·조직·연도·지급 방식 변경예외 적용 범위와 공통 분모 유지
일반 인정과 예외 조건이 겹침같은 금액의 이중 집계 방지
가점 시작점·만점·만점 초과경계 계산과 최대 점수 제한
같은 품목 반복·새 품목 추가중복 제외와 품목 수 단계 반영
실적 또는 기준값이 없음정해진 기본점수와 필요한 확인 표시

후속 검증에서는 최종 파일의 수식을 임시 계산 모델로 복사해 조건별 결과를 확인하고, 테스트 자료는 전달 파일에 남기지 않았어요. 기존 파일에서는 변경 대상 밖의 입력값·수식·서식과 표·차트·입력 규칙을 별도로 대조했어요. 계산 검증과 원본 보존 검증을 나눈 거예요.

이 일을 통해 다시 확인한 건, 누락 여부를 모든 지표의 공통 제외 조건으로 쓰면 안 된다는 점이었어요. 무엇을 세는지, 어떤 조건으로 인정하는지, 무엇을 보완해야 하는지를 나누면 계산 결과도 그 이유도 설명하기 쉬워져요.