• 업무 자동화
  • 기존 프로그램 개선

구매실적부터 평가점수까지, 엑셀 하나로

계약을 입력하면 종류별 목록, 평가점수, 목표까지 부족한 실적을 함께 확인하는 엑셀을 만들었어요.

프로젝트 한눈에 보기 30초 요약

01문제

실적은 엑셀에, 기준은 한글에.

입력한 실적이 평가점수로 제대로 이어지지 않았어요.

02해결

입력부터 점수까지 한 파일로.

계약 목록·인정 실적·점수 계산을 연결했어요.

03달라진 업무

더 채울 실적이 바로 보여요.

지표별 목표와 부족액을 따로 계산할 필요가 없어졌어요.

목표와 실적, 부족액을 한눈에

지표별로 얼마나 채웠고 무엇이 남았는지 같은 표에서 확인해요.

가상 금액으로 재구성한 지표별 집계 표. 평가항목별 만점기준액·실적·부족액·상태를 보여줘요.
일부 지표를 가상 금액으로 재구성한 설명용 화면이에요. 실제 Excel 캡처는 아니에요.
  • 만점기준액·실적 비교
  • 부족액 자동 계산
  • 달성·가점 상태 확인

업무가 이렇게 달라졌어요

기존 업무구현 후 업무
평가 문서와 엑셀 계산을 따로 대조입력 실적을 기준에 맞춰 점수로 환산
공사·용역·물품 자료를 나눠 확인한 곳에서 입력하고 종류별 목록으로 조회
목표까지 부족한 실적을 별도 계산지표별 목표·실적·부족액을 함께 확인
누락된 입력이 어디인지 다시 찾기계약번호·품목명 등 보완할 항목을 표시

구현 범위와 주요 규칙

입력은 한 곳에서
계약·지급 자료와 분류를 통합 시트에 입력해요. 공사·용역·물품 시트는 조회·출력에 사용해요.
지표마다 다른 계산 기준
금액, 계약 건수, 품목 수를 구분하고 해당 지표의 인정 조건을 적용해요.
판단에 필요한 정보는 사람이 확인
업체 분류와 승인 여부는 담당자가 입력해요. 평가점수는 입력값에 따른 예상치이며 제출 전 증빙과 대조해요.

담당 범위

평가기준 정리부터 실무 보완까지

핵심 구현
  • Excel 수식
결과물 형식
  • XLSX
제작·검증 도구
  • JavaScript시트·수식·서식 생성, 계산 검증
  • PythonOpen XML 패키징, 원본 보존 검증
설계
  • 평가기준·인정 실적 정리
  • 입력·조회·집계 구조 설계
구현
  • 통합 실적 입력·종류별 조회
  • 지표별 실적·부족액 계산
  • 가점·평가점수 환산
  • 누락 항목 표시·사용 안내
후속 개선
  • 실제 입력 자료의 집계 오류 수정
  • 인정 예외·상세 계산 보완
이 글의 목차

파일 정리보다 계산의 연결이 필요했어요

인수인계받은 엑셀에 실적은 쌓이는데, 평가점수를 믿고 확인하기는 어려웠어요. 서식이 빠진 곳이 있었고, 한글 문서에 적힌 평가기준과 엑셀 계산도 맞지 않았죠. 공공기관에서 구매실적을 관리하던 고객의 첫 문의는 이 파일을 정리해 달라는 요청이었어요.

상담에서 필요한 일을 다시 나눴어요. 지난 자료를 옮기는 것보다, 앞으로 계약을 입력했을 때 인정되는 실적과 부족한 금액을 바로 확인하는 구조가 먼저였어요. 12개 평가 지표와 가점 2개를 정리하고, 최종 점수까지 이어지는 관리 양식을 만들었어요.

상담 내용은 당시 대화를 요약했어요. 고객·기관명과 실제 계약 자료는 담지 않았어요.

한 번 입력한 자료로 다음 업무까지

담당자는 통합 실적관리 시트에 계약·지급 정보와 구매 분류를 입력해요. 빠진 정보가 있으면 확인 표시를 보고 보완하고, 종합현황에서 점수를 살펴봐요. 지표별 집계에는 목표·실적·부족액과 세부 계산이 이어져요.

업무 흐름

  1. 01통합 시트에 입력
  2. 02누락 항목 확인
  3. 03점수·부족액 확인
  4. 04종류별 조회·출력
업무 흐름 개념도

공사·용역·물품만 따로 보고 싶을 때는 각각의 조회 시트를 열어요. 원본에서 종류나 금액을 고치면 조회 목록도 함께 바뀌어요. 같은 내용을 여러 시트에서 다시 고칠 필요가 없어진 거예요!

매크로 없는 XLSX 파일 안에서 수식으로 연결했어요. 기존 자료를 자동으로 이관하거나 외부 시스템에서 계약을 가져오는 방식은 아니에요.

실무에서 헷갈리던 경계를 나눴어요

입력하는 곳과 보는 곳

처음에는 열 순서와 고정 영역을 바꾸는 요청이었어요. 이어서 종류별로 편하게 보고 싶다는 요구가 나왔죠. 입력 파일을 세 개로 나누면 같은 계약을 어디서 수정해야 할지 다시 헷갈릴 수 있었어요.

그래서 입력은 한 시트에 모으고, 종류별 시트는 조회와 출력에만 쓰도록 했어요. 넓은 표를 가로로 이동해도 계약 정보가 보이도록 업체명까지 고정했어요.

입력 한 곳에서 세 조회 시트를 만든 설계 읽기

빠진 정보와 집계할 수 있는 실적

실제 자료를 넣은 뒤 계약번호가 비어 있다는 이유로 금액까지 집계에서 빠지는 문제가 드러났어요. 계약 건수를 세려면 번호가 필요하지만, 금액 실적은 다른 인정 조건으로 판단해야 했어요.

두 계산을 분리하고, ‘필수값 확인’ 대신 어떤 항목이 빠졌는지 표시하도록 바꿨어요. 금액은 해당 조건에 따라 집계하면서 계약번호나 품목명 보완은 따로 안내해요.

누락값과 평가 적격성을 분리한 과정 읽기

점수만 보여주지 않고 이유까지

원점수·배점·최종 득점을 구분하고, 복합 지표는 세부 계산을 펼쳤어요. 특정 구매 실적이 한 지표에만 인정되는 예외도 공통 집계와 나눠 반영했어요.

부족액은 현재 집계 금액을 기준으로 보여줘요. 새 구매로 전체 금액도 달라지는 지표라면, 입력을 반영한 뒤 부족액을 다시 확인해야 해요. 숫자 하나를 고정된 추가 구매 목표로 오해하지 않도록 계산의 기준을 함께 두었어요.

실제 자료를 넣은 뒤에 보완한 것

고객이 며칠간 직접 입력한 파일과 문의 문서를 보내왔어요. 이를 바탕으로 계약 건수의 중복 처리, 승인 실적의 예외, 가점의 목표·부족액 표시를 보완했어요. 변경이 필요하다고 보였던 가점 구간도 원래 평가기준과 다시 대조했어요. 계산이 맞는 부분은 유지하고 설명을 더 분명하게 정리했죠.

수정할 때는 이미 입력한 자료와 기존 파일 구조를 함께 지켰어요. 변경 대상 밖의 값·수식·서식, 표와 입력 규칙을 대조하고, 계산에 영향을 주는 조건을 바꿔가며 결과를 확인했어요.

확인한 항목검수 내용
종류별 조회빈 행 사이 입력, 종류·금액 변경, 삭제, 마지막 입력행 반영
집계 단위같은 계약의 여러 지급 건, 같은 품목의 반복 입력 구분
인정 조건조직·연도·승인 여부에 따른 예외와 중복 집계 방지
점수 경계가점 시작·만점 구간, 상한, 실적·기준값이 없는 경우
기존 파일 보존입력값, 변경 대상 밖 수식·서식, 표·차트·입력 규칙 대조

비슷한 업무를
더 단순하게 만들고 싶다면.

현재 겪는 불편부터 함께 정리합니다.

프로젝트 상담