관리비나 이사정산 업무를 하다 보면 금액을 청구하는 것보다 그 금액이 실제로 입금되었는지 확인하는 일이 더 번거롭게 느껴질 때가 있습니다.
처리해야 할 건수가 적을 때는 통장을 확인하면서 엑셀에 입금 여부를 표시하는 정도로도 충분합니다. 하지만 데이터가 계속 쌓이면 이야기가 달라집니다.
어떤 건은 전액 입금되고, 어떤 건은 아직 입금되지 않았으며, 일부 금액만 입금되는 경우도 있습니다. 정산금액보다 많은 금액이 들어오는 경우도 있습니다.
저 역시 전입·전출 정산 업무를 관리하면서 이런 내용을 쉽게 확인하기 위해 엑셀의 데이터 구조를 조금씩 정리하게 되었습니다.
이번 글에서는 복잡한 VBA나 자동화 기능을 사용하기 전에 미입금 건을 관리하기 위한 기본 데이터 구조를 어떻게 만들 수 있는지부터 정리해 보겠습니다.
미입금 관리를 위해 먼저 데이터 구조를 정리해야 하는 이유
처음에는 입금 여부만 표시해도 충분하다고 생각할 수 있습니다.
예를 들어 정산금액 옆에 입금완료라고 직접 입력하는 방식입니다.
하지만 이렇게 관리하면 나중에 부분입금이 발생했을 때 문제가 생깁니다.
정산금액이 420,000원인데 300,000원만 입금되었다면 단순히 ‘입금됨’이라고 표시할 수는 없습니다. 아직 120,000원을 더 받아야 하기 때문입니다.
반대로 정산금액보다 많은 금액이 들어오는 경우도 있습니다.
그래서 저는 입금 여부를 사람이 직접 판단해서 입력하기보다 정산금액과 실제 입금액을 각각 기록하고, 두 금액의 차이를 엑셀이 계산하도록 하는 구조가 더 관리하기 편하다고 생각했습니다.
미입금 관리를 위한 기본 데이터 구조
이번 예제에서는 다음 항목을 사용했습니다.
건물명 / 호 / 전입전출구분 / 기준날짜 / 이사정산금액 / 입금액 / 차액 / 입금날짜 / 입금자명 / 입금여부 / 비고
아래는 예제 데이터입니다.
실제 업무 데이터를 공개할 수는 없기 때문에 건물명과 금액 등은 설명을 위해 만든 예제 데이터입니다.
여기서 이사정산금액은 받아야 할 금액이고, 입금액은 통장에서 확인한 실제 받은 금액입니다.
예제에는 일부러 여러 상황을 넣었습니다.
101호와 305호는 정산금액 전액이 입금된 경우이고, 102호는 아직 입금되지 않은 경우입니다.
203호는 정산금액 420,000원 중 300,000원만 입금된 부분입금 사례입니다.
307호는 정산금액보다 20,000원이 더 입금되었습니다. 예제에서는 열쇠비 20,000원을 추가로 받은 경우로 가정하고 그 내용을 비고에 기록했습니다.
이렇게 전액입금, 미입금, 부분입금, 추가금 수납 사례를 함께 놓고 보면 각 열이 왜 필요한지 조금 더 쉽게 이해할 수 있습니다.
차액은 ‘입금액 – 정산금액’으로 계산했습니다
차액을 계산할 때는 어떤 금액에서 어떤 금액을 빼느냐에 따라 양수와 음수의 의미가 달라집니다.
이번 예제에서는 제가 데이터를 볼 때 좀 더 직관적으로 이해할 수 있도록 다음 기준을 사용했습니다.
- 차액 = 입금액 – 이사정산금액
예를 들어 정산금액이 420,000원이고 실제 입금액이 300,000원이라면 차액은 -120,000원이 됩니다.
저는 마이너스를 보면 받아야 할 금액보다 부족하게 입금되었다고 바로 인식할 수 있기 때문에 이 방식을 사용했습니다.
반대로 정산금액 190,000원에 210,000원이 입금되었다면 차액은 +20,000원입니다. 정산금액보다 더 받은 금액이 있다는 것을 숫자만 보고도 확인할 수 있습니다.
엑셀 수식
=IF(F2="","",F2-E2)
입금액이 아직 입력되지 않았다면 차액도 공백으로 두고, 입금액이 있을 때만 입금액 – 이사정산금액을 계산하도록 했습니다.
따라서 차액을 보면 다음과 같이 판단할 수 있습니다.
차액 해석
- 음수 → 정산금액보다 부족하게 입금
- 0 → 정산금액 전액입금
- 양수 → 정산금액보다 많은 금액 입금
정산금액보다 많이 입금된 사유는 비고에 기록했습니다.
정산금액보다 돈이 더 들어왔다고 해서 반드시 잘못 입금된 것은 아닙니다.
실제 업무에서는 열쇠비 등 정산금액 외에 추가로 받아야 하는 비용이 생길 수도 있습니다.
이런 경우가 자주 발생하지 않는다면 사유별로 별도의 열을 계속 만드는 것보다 실제 입금액 전체를 기록하고 추가로 받은 이유를 비고에 남기는 방법이 더 간단할 수 있습니다.
예제의 307호가 그런 경우입니다.
정산금액은 190,000원이지만 실제 입금액은 210,000원이고 차액에는 20,000원이 표시됩니다. 비고에는 열쇠비 20,000원 추가 수납이라고 기록했습니다.
다만 잘못 입금되어 반환해야 하는 금액까지 같은 방식으로 처리해서는 안 됩니다. 그런 사례를 별도로 관리해야 하는 업무라면 과입금 확인이나 반환 여부 등을 관리하는 항목을 추가하는 것이 좋습니다.
엑셀은 실제 업무 방식에 맞춰 필요한 항목을 정하는 것이 중요하다고 생각합니다.
입금여부는 사람이 아니라 엑셀이 판단하도록 했습니다
정산금액과 입금액, 차액까지 기록했다면 입금여부를 사람이 다시 O와 X로 입력할 필요는 없습니다.
예제에서는 다음 수식을 사용했습니다.
=IF(F2="","X",IF(G2>=0,"O","X"))
- 입금액 없음 → X
- 차액 음수 → X
- 차액 0 → O
- 차액 양수 → O
입금액이 없으면 X, 차액이 음수여도 아직 받아야 할 금액이 남아 있으므로 X입니다.
차액이 0이면 정산금액을 모두 받았으므로 O입니다.
차액이 양수인 경우에도 받아야 할 정산금액은 모두 받은 상태이므로 이 예제에서는 O로 처리했습니다. 추가로 받은 금액의 이유는 앞에서 설명한 것처럼 비고를 확인하면 됩니다.
여기에서 입금여부의 의미는 단순히 돈이 한 번이라도 들어왔는가가 아닙니다.
받아야 할 정산금액을 모두 받았는가를 확인하는 검증값으로 사용하는 것입니다.
입금날짜와 입금자명을 함께 저장하는 이유
입금 여부만 확인하는 것이 목적이라면 입금날짜와 입금자명이 꼭 필요하지 않다고 생각할 수도 있습니다.
하지만 실제 통장을 확인하다 보면 입금자명이 항상 예상한 형태로 들어오지는 않습니다.
건물명과 호수로 입금하기도 하고, 입주자 개인 이름이나 사업자명 등으로 입금하기도 합니다.
그래서 입금이 확인되었을 때 입금액뿐 아니라 입금날짜와 실제 통장에 표시된 입금자명을 같이 기록해 두면 나중에 다시 확인해야 할 일이 생겼을 때 추적하기가 편합니다.
특히 여러 건물이나 여러 세대를 함께 관리한다면 이러한 기록이 쌓일수록 도움이 됩니다.
이 구조는 전입·전출 업무에만 사용할 수 있을까?
이번 예제는 제가 실제로 하는 전입·전출 이사정산 업무를 기준으로 만들었습니다.
하지만 기본적인 원리는 다른 업무에도 적용할 수 있습니다.
거래처 미수금, 관리비 입금, 공사대금, 각종 청구금처럼 받아야 할 금액과 실제 받은 금액을 비교해야 하는 업무라면 비슷한 구조를 사용할 수 있습니다.
업무에 따라 건물명이나 호 대신 거래처명, 청구번호 등의 항목을 사용하면 됩니다.
중요한 것은 특정 열 이름을 그대로 따라 하는 것이 아니라,
받아야 할 금액 → 실제 받은 금액 → 두 금액의 차이 → 검증 결과
가 연결되도록 데이터를 구성하는 것입니다.
다음 단계에서는 미입금 데이터만 빠르게 찾아보겠습니다
지금까지 만든 구조만으로도 입금 여부를 관리할 수 있지만 데이터가 수백 건, 수천 건 쌓이면 X가 표시된 행을 눈으로 찾아다니는 것도 비효율적입니다.
그래서 다음 단계에서는 이번에 만든 입금여부 검증열을 이용해 미입금 건만 필터로 빠르게 확인하는 방법을 정리해 보겠습니다.
그 다음에는 매번 필터 조건을 선택하는 작업도 줄일 수 있도록 VBA 버튼을 이용해 미입금 건을 한 번에 조회하는 방법까지 이어갈 예정입니다.
← 이전 글 | 조건부서식을 줄이고 검증열을 만든 이유
다음글 → | 엑셀에서 미입금 건만 필터로 빠르게 확인하는 방법
감사합니다.