무료 매출채권 연령분석 엑셀 템플릿: 구간별 미수금, 거래처 요약, 회수 우선순위 (수식 설명 포함)

구성

통합 문서에는 시트가 6개 있어요. 입력하는 시트는 몇 개뿐이고 나머지는 계산돼요.

시트	용도	입력하는 것	자동 계산되는 것
Start Here	기준일, 구간별 합계, DSO, 최대 잔액 거래처, 차트	기준일, 최근 90일 외상 매출액	총 미수금, 구간 합계 5개, 60일 초과 비중, 미결 청구서 수, DSO, 최대 잔액 거래처
Invoices	청구서당 한 행(200건)	청구서 번호, 거래처, 청구일, 결제 조건, 금액, 입금액, 메모	만기일, 잔액, 연체 일수, 구간, 상태, 우선순위 점수
Summary	거래처당 한 행(30곳)	없음: 거래처명은 Lists에서 가져와요	미결 건수, 구간별 금액, 합계, 연체 비중, 최장 연체 일수, 다음 조치
Priority	먼저 연락할 청구서 10건	없음	순위, 청구서, 거래처, 잔액, 연체 일수, 구간, 점수, 권장 조치
Lists	드롭다운 선택지	거래처명, 결제 조건	없음
Checks	데이터 점검 6개	없음	OK 또는 Review

시작하기 전에

청구 시스템에서 청구일, 결제 조건, 금액, 입금액이 있는 미수 청구서를 내보내세요.
Invoices 드롭다운과 Summary 탭이 인식하도록 거래처명을 먼저 Lists 탭에 모두 추가하세요.
기준일을 정하세요. 보통 월말 또는 매주 월요일에 연령분석을 해요.

단계별 사용법

기준일 설정
Start Here B6이 모든 계산의 기준 날짜예요. 날짜를 입력하거나 =TODAY()를 넣어 파일을 열 때마다 최신 상태로 유지하세요. B7(최근 90일 외상 매출)은 DSO 추정에만 쓰여요.
Lists 탭에 거래처 추가
Lists의 A열이 거래처 드롭다운이자 Summary 탭의 행 목록이에요. 한 행에 하나씩, 표시하고 싶은 철자 그대로 입력하세요.
청구서 입력
Invoices 탭에 청구서 번호를 입력하고, 드롭다운에서 거래처를 고르고, 청구일을 입력하고, 결제 조건(일)을 고르고, 금액을 입력하세요. 입금되면 Paid에 입력하세요. 완납된 청구서는 모든 합계에서 자동으로 빠져요.
계산 열 읽기
만기일은 청구일 + 결제 조건. 잔액은 금액 − 입금액. 연체 일수는 만기일부터 기준일까지(음수 없음). 구간은 Current, 1-30, 31-60, 61-90, 90+. 상태는 Open 또는 Paid. 우선순위 점수는 잔액에 연체 기간 가중치를 준 값이에요.
Summary 탭 확인
거래처마다 미결 건수, 구간별 금액, 합계, 연체 비중, 가장 오래된 연체 일수, 연체 기간에 따라 강도가 올라가는 권장 다음 조치가 한 행에 있어요.
Priority 탭으로 회수 업무
우선순위 점수 순으로 10행. 1위부터 시작하세요. 권장 조치 열이 청구서 연령에 맞춰 리마인더 이메일, 전화, 분할 상환 약정, 최종 통지 중 무엇이 맞는지 알려 줘요.
Checks 탭 검토
금액보다 많이 입금된 청구서, 빠진 거래처·날짜·조건, Lists에 없는 거래처, 구간 5개 합계가 총 미수금과 맞는지를 잡아 줘요.

수식 지도

통합 문서의 모든 수식을 시트와 열별로 정리했어요. 복사된 수식 열은 한 번만 세고, 적용 범위를 함께 표시해요.

시트	셀	함수	계산 내용	읽는 값	쓰이는 곳
Invoices	`E5` 204행까지 아래로 복사됨	IF	만기일	C5, D5	연체 일수(I열)가 이 날짜부터 세요.
Invoices	`H5` 204행까지 아래로 복사됨	IF	잔액	F5, G5	Start Here와 Summary의 모든 SUMIF, 우선순위 점수가 사용해요.
Invoices	`I5` 204행까지 아래로 복사됨	IF, OR, MAX	연체 일수	E5, H5	J열의 구간과 L열의 우선순위 점수가 모두 이 값을 읽어요.
Invoices	`J5` 204행까지 아래로 복사됨	IF	연령 구간	H5, I5	Start Here가 이 단어와 정확히 일치하는 잔액을 구간별로 더하므로 Summary와 Start Here의 SUMIF가 같은 철자를 써요.
Invoices	`L5` 204행까지 아래로 복사됨	IF, OR, ROW	우선순위 점수	H5, I5	Priority 탭이 이 열에 LARGE를 써서 상위 10건을 고르고 MATCH로 각 행을 찾아요.
Start Here	`B10`	SUMIF	구간별 미수금	—	B15가 61-90과 90+ 셀로 60일 초과 비중을 계산하고, Checks 탭이 구간 5개가 B9와 맞는지 확인해요.
Start Here	`B17`	IF	매출채권 회수기간(DSO)	B7, B9	표준 결제 조건과 비교하세요. 30일 조건에서 DSO 45는 고객이 평균 2주 늦게 낸다는 뜻이에요.
Start Here	`B18`	INDEX, MATCH, MAX	최대 잔액 거래처	—	가장 중요한 거래처를 빠르게 가리켜요.
Summary	`B5` 34행까지 아래로 복사됨	IF, COUNTIFS	거래처별 미결 청구서 수	—	구간 금액 옆에 표시돼요.
Summary	`C5` 34행까지 아래로 복사됨	IF, SUMIFS	거래처별 구간 금액	—	H5가 구간 5개를 합계하고 I5가 연체 비중을 계산해요.
Summary	`J5` 34행까지 아래로 복사됨	IF, MAXIFS	가장 오래된 연체(일)	—	K5가 이 숫자를 다음 조치로 바꿔요.
Summary	`K5` 34행까지 아래로 복사됨	IF	권장 다음 조치	—	Priority 탭이 청구서별로 같은 단계를 써요.
Priority	`G5` 14행까지 아래로 복사됨	IFERROR, LARGE	N번째로 높은 우선순위 점수	—	B5–F5가 이 점수를 가진 청구서를 찾아요.
Priority	`B5` 14행까지 아래로 복사됨	IF, INDEX, MATCH	그 점수의 청구서 정보	—	이것이 전화 목록이에요.
Checks	`B6`	IF, SUMPRODUCT	초과 입금 없음	—	초과 입금은 보통 Paid의 오타예요.
Checks	`B10`	IF, SUMPRODUCT, ISNA, MATCH	청구서의 모든 거래처가 Lists에 있음	—	Lists에 이름을 추가하거나 청구서의 철자를 고쳐 해결하세요.

모든 수식 설명

아래 수식은 파일에서 그대로 가져온 것이라 여기서 읽는 내용이 셀에 들어 있는 내용과 정확히 같아요. 엑셀에서 셀을 클릭하면 수식 입력줄에서 볼 수 있어요.

만기일Invoices!E5 · 204행까지 아래로 복사됨

=IF(C5="", "", C5+D5)

청구일에 결제 조건 일수를 더해요. 청구일을 입력하기 전에는 비어 있어요.

부분별 설명

C5="": 청구일이 아직 없음: 아무것도 표시하지 않아요.
C5+D5: 날짜 + 일수. 엑셀 날짜는 일 단위 숫자라 30을 더하면 30일 뒤 날짜가 돼요.

예시 데이터로 보면: C5 = 2026년 6월 17일, D5 = 30 → E5 결과 2026년 7월 17일

어떻게 쓰이나: 연체 일수(I열)가 이 날짜부터 세요.

바꾸는 방법: "월말 후 30일" 같은 조건이면 C5+D5를 EOMONTH(C5,0)+30으로 바꾸세요.

배우기: 엑셀 텍스트 함수와 날짜 함수 사용법 →

잔액Invoices!H5 · 204행까지 아래로 복사됨

=IF(F5="", "", F5-G5)

금액에서 입금액을 뺀 값. 다른 탭의 모든 합계가 더하는 숫자예요.

부분별 설명

F5="": 금액이 아직 없음: 아무것도 표시하지 않아요.
F5-G5: 금액 − 입금액. 빈 Paid 셀은 0으로 계산돼요.

예시 데이터로 보면: F5 = 4,800, G5 = 0 → H5 결과 4,800

어떻게 쓰이나: Start Here와 Summary의 모든 SUMIF, 우선순위 점수가 사용해요.

바꾸는 방법: 바꿀 것 없음.

배우기: 엑셀 기본 수식 작성법: 더하기, 빼기, 곱하기, 나누기 →

연체 일수Invoices!I5 · 204행까지 아래로 복사됨

=IF(OR(F5="", H5<=0), "", MAX(0, 'Start Here'!$B$6-E5))

만기일부터 기준일까지의 일수. 아직 만기가 안 된 청구서는 0이 되도록 하한을 0으로 둬요.

부분별 설명

OR(F5="", H5<=0): 빈 행과 완납 청구서는 건너뛰어요.
'Start Here'!$B$6-E5: 기준일 − 만기일(일). 기준일 참조는 아래로 복사해도 고정되도록 절대 참조예요.
MAX(0, …): 음수(미도래)는 0이 돼요.

예시 데이터로 보면: E5 = 2026년 7월 17일, H5 = 4,800 → I5 결과 108

어떻게 쓰이나: J열의 구간과 L열의 우선순위 점수가 모두 이 값을 읽어요.

바꾸는 방법: 바꿀 것 없음. Start Here의 기준일을 고치세요.

배우기: 엑셀 MIN, MAX 함수 사용법 →

연령 구간Invoices!J5 · 204행까지 아래로 복사됨

=IF(F5="", "", IF(H5<=0, "Paid",
    IF(I5=0, "Current", IF(I5<=30, "1-30", IF(I5<=60, "31-60", IF(I5<=90, "61-90", "90+"))))))

작은 연령부터 차례로 검사하는 IF 연쇄예요. 순서가 중요해요. 앞의 검사가 실패했을 때만 다음 검사가 실행되므로 "<=60"은 실제로 31–60을 뜻해요.

부분별 설명

F5="": 빈 행: 표시 없음.
H5<=0: 남은 금액 없음: "Paid".
I5=0: 미도래: "Current".
I5<=30, I5<=60, I5<=90: 1-30, 31-60, 61-90 구간을 순서대로 검사.
"90+": 그보다 오래된 것 전부.

예시 데이터로 보면: H5 = 4,800, I5 = 108 → J5 결과 90+

어떻게 쓰이나: Start Here가 이 단어와 정확히 일치하는 잔액을 구간별로 더하므로 Summary와 Start Here의 SUMIF가 같은 철자를 써요.

바꾸는 방법: 구간 경계를 바꾸려면(예: 45와 90) 여기 숫자와 Start Here, Summary의 라벨을 함께 고치세요.

배우기: 엑셀 IF 함수에 AND, OR, IFS 함께 쓰는 법 →

우선순위 점수Invoices!L5 · 204행까지 아래로 복사됨

=IF(OR(F5="", H5<=0), 0, H5*(1+I5/30)+ROW()/1000000)

연체 기간으로 가중한 잔액이에요. 30일 연체마다 잔액의 100%가 더해져요. 행 번호 기반의 아주 작은 값을 더해 모든 점수를 고유하게 만들어서 Priority 탭이 충돌 없이 행을 찾을 수 있어요.

부분별 설명

OR(F5="", H5<=0): 빈 행이나 완납 행은 0점이라 순위에 오르지 않아요.
H5*(1+I5/30): 잔액 × (1 + 연체 개월 수).
ROW()/1000000: 행 번호의 백만분의 일을 더해요. 순위에는 보이지 않지만 두 점수가 완전히 같아지는 일을 막아요.

예시 데이터로 보면: H5 = 4,800, I5 = 108 → L5 결과 22,080

어떻게 쓰이나: Priority 탭이 이 열에 LARGE를 써서 상위 10건을 고르고 MATCH로 각 행을 찾아요.

바꾸는 방법: 30을 15로 바꾸면 연체 기간 가중치가 두 배가 돼요.

배우기: 엑셀 기본 수식 작성법: 더하기, 빼기, 곱하기, 나누기 →

구간별 미수금Start Here!B10

=SUMIF(Invoices!$J$5:$J$204, "Current", Invoices!$H$5:$H$204)

구간 단어가 일치하는 청구서의 잔액을 더해요. B11–B14는 다른 구간 이름으로 같은 수식이에요.

부분별 설명

Invoices!$J$5:$J$204: 구간 열: 검사할 범위.
"Current": 더할 구간.
Invoices!$H$5:$H$204: 잔액 열: 더할 범위.

예시 데이터로 보면: B10 결과 31,750

어떻게 쓰이나: B15가 61-90과 90+ 셀로 60일 초과 비중을 계산하고, Checks 탭이 구간 5개가 B9와 맞는지 확인해요.

바꾸는 방법: 구간 이름을 바꾸지 않는 한 바꿀 것 없음.

배우기: 엑셀 SUMIF, SUMIFS 함수 사용법 →

매출채권 회수기간(DSO)Start Here!B17

=IF(B7=0, 0, B9/(B7/90))

미수금을 일평균 외상 매출로 나눠요. 90은 "최근 90일" 입력과 맞춘 값이에요.

부분별 설명

B7=0: 매출액이 없을 때 0으로 나누는 것을 막아요.
B9/(B7/90): 총 미수금 ÷ (90일 외상 매출 ÷ 90일).

예시 데이터로 보면: B7 = 412,000, B9 = 72,550 → B17 결과 15.85

어떻게 쓰이나: 표준 결제 조건과 비교하세요. 30일 조건에서 DSO 45는 고객이 평균 2주 늦게 낸다는 뜻이에요.

바꾸는 방법: 1년치 외상 매출을 입력한다면 90을 365로 바꾸세요.

배우기: 엑셀 기본 수식 작성법: 더하기, 빼기, 곱하기, 나누기 →

최대 잔액 거래처Start Here!B18

=INDEX(Summary!$A$5:$A$34,
       MATCH(MAX(Summary!$H$5:$H$34),
       Summary!$H$5:$H$34, 0))

Summary 탭에서 가장 큰 합계를 찾고 같은 행의 거래처명을 돌려줘요.

부분별 설명

MAX(Summary!$H$5:$H$34): 가장 큰 총 미수금.
MATCH(…, Summary!$H$5:$H$34, 0): 그 값이 있는 행.
INDEX(Summary!$A$5:$A$34, …): 그 행의 거래처명.

예시 데이터로 보면: B18 결과 Harbor Logistics

어떻게 쓰이나: 가장 중요한 거래처를 빠르게 가리켜요.

바꾸는 방법: 바꿀 것 없음.

배우기: 엑셀 INDEX와 MATCH 함수 사용법 →

거래처별 미결 청구서 수Summary!B5 · 34행까지 아래로 복사됨

=IF(A5="", "", COUNTIFS(Invoices!$B$5:$B$204,
    A5, Invoices!$K$5:$K$204,
    "Open"))

그 거래처의 것이면서 아직 미결인 청구서를 세요.

부분별 설명

$A5="": 이 행에 거래처가 없음: 표시 없음. 같은 검사가 행의 모든 열에서 작동하도록 열을 $로 고정했어요.
Invoices!$B$5:$B$204, A5: 거래처 열이 이 행의 거래처와 같음.
Invoices!$K$5:$K$204, "Open": 상태 열이 Open.

예시 데이터로 보면: B5 결과 1

어떻게 쓰이나: 구간 금액 옆에 표시돼요.

바꾸는 방법: 바꿀 것 없음.

배우기: 엑셀 COUNTIF, COUNTIFS 함수 사용법 →

거래처별 구간 금액Summary!C5 · 34행까지 아래로 복사됨

=IF($A5="", "", SUMIFS(Invoices!$H$5:$H$204,
    Invoices!$B$5:$B$204, $A5,
    Invoices!$J$5:$J$204, "Current"))

조건 둘: 거래처 일치, 구간 일치. D–G열은 다른 구간 이름으로 반복해요.

부분별 설명

Invoices!$H$5:$H$204: 더할 잔액.
Invoices!$B$5:$B$204, $A5: 거래처 조건. $A5는 오른쪽으로 복사할 때 거래처 열을 고정해요.
Invoices!$J$5:$J$204, "Current": 구간 조건.

예시 데이터로 보면: C5 결과 3,150

어떻게 쓰이나: H5가 구간 5개를 합계하고 I5가 연체 비중을 계산해요.

바꾸는 방법: 구간 이름을 바꾸지 않는 한 바꿀 것 없음.

배우기: 엑셀 SUMIF, SUMIFS 함수 사용법 →

가장 오래된 연체(일)Summary!J5 · 34행까지 아래로 복사됨

=IF(A5="", "", MAXIFS(Invoices!$I$5:$I$204,
    Invoices!$B$5:$B$204, A5,
    Invoices!$K$5:$K$204, "Open"))

그 거래처의 미결 청구서 중 가장 큰 연체 일수예요. MAXIFS는 Excel 2019와 Microsoft 365에서 쓸 수 있어요.

부분별 설명

Invoices!$I$5:$I$204: 연체 일수: 최댓값을 구할 값.
Invoices!$B$5:$B$204, A5: 거래처 조건.
Invoices!$K$5:$K$204, "Open": 미결 청구서만.

예시 데이터로 보면: J5 결과 0

어떻게 쓰이나: K5가 이 숫자를 다음 조치로 바꿔요.

바꾸는 방법: Excel 2016에서는 =MAX(IF((Invoices!$B$5:$B$204=A5)*(Invoices!$K$5:$K$204="Open"),Invoices!$I$5:$I$204))를 Ctrl+Shift+Enter로 입력하세요.

배우기: 엑셀 MIN, MAX 함수 사용법 →

권장 다음 조치Summary!K5 · 34행까지 아래로 복사됨

=IF(A5="", "", IF(H5=0, "Nothing open",
    IF(J5>90, "Final notice or collections", IF(J5>60, "Call and payment plan", IF(J5>30, "Call", "Reminder email")))))

가장 오래된 연체 청구서의 연령에 따라 회수 조치를 단계적으로 올려요.

부분별 설명

H5=0: 미수금 없음: "Nothing open".
J5>90, J5>60, J5>30: 최종 통지, 전화와 분할 약정, 전화의 기준.
"Reminder email": 30일 이하 전부.

예시 데이터로 보면: K5 결과 Reminder email

어떻게 쓰이나: Priority 탭이 청구서별로 같은 단계를 써요.

바꾸는 방법: 회사의 회수 정책에 맞게 문구를 고치세요.

배우기: 엑셀 IF 함수에 AND, OR, IFS 함께 쓰는 법 →

N번째로 높은 우선순위 점수Priority!G5 · 14행까지 아래로 복사됨

=IFERROR(LARGE(Invoices!$L$5:$L$204, A5), 0)

LARGE는 k번째로 큰 값을 돌려주고, k는 A열의 순위예요. 순위보다 미결 청구서가 적으면 IFERROR가 0을 표시해 행이 비어 있어요.

부분별 설명

Invoices!$L$5:$L$204: 모든 우선순위 점수.
A5: 순위: 1이 최고, 2가 그다음.
IFERROR(…, 0): k번째 청구서가 없을 때.

예시 데이터로 보면: G5 결과 35,280

어떻게 쓰이나: B5–F5가 이 점수를 가진 청구서를 찾아요.

바꾸는 방법: 20행을 보려면 14행을 아래로 복사하고 순위 11–20을 입력하세요.

배우기: 엑셀 MIN, MAX 함수 사용법 →

그 점수의 청구서 정보Priority!B5 · 14행까지 아래로 복사됨

=IF(G5=0, "", INDEX(Invoices!$A$5:$A$204,
    MATCH(G5, Invoices!$L$5:$L$204, 0)))

순위의 점수와 같은 점수를 가진 행을 찾아 청구서 번호를 돌려줘요. C–F열은 같은 행의 거래처, 잔액, 연체 일수, 구간을 돌려줘요.

부분별 설명

G5=0: 이 순위에 청구서 없음: 표시 없음.
MATCH(G5, Invoices!$L$5:$L$204, 0): 그 점수의 행 위치. 고유값 처리 덕분에 항상 올바른 청구서를 찾아요.
INDEX(Invoices!$A$5:$A$204, …): 그 행의 청구서 번호.

예시 데이터로 보면: B5 결과 INV-1043

어떻게 쓰이나: 이것이 전화 목록이에요.

바꾸는 방법: 바꿀 것 없음.

배우기: 엑셀 INDEX와 MATCH 함수 사용법 →

초과 입금 없음Checks!B6

=IF(SUMPRODUCT((Invoices!$G$5:$G$204>Invoices!$F$5:$F$204)*1)=0,
    "OK", "Review")

TRUE/FALSE 배열에 1을 곱해 더하는 방식으로 입금액이 금액보다 큰 행을 세요.

부분별 설명

Invoices!$G$5:$G$204>Invoices!$F$5:$F$204: 행마다 TRUE/FALSE 하나인 배열.
*1: TRUE를 1, FALSE를 0으로.
SUMPRODUCT(…): 더하기: 초과 입금 행의 수.

예시 데이터로 보면: B6 결과 OK

어떻게 쓰이나: 초과 입금은 보통 Paid의 오타예요.

바꾸는 방법: 바꿀 것 없음.

배우기: 엑셀 SUMIF, SUMIFS 함수 사용법 →

청구서의 모든 거래처가 Lists에 있음Checks!B10

=IF(SUMPRODUCT((Invoices!$B$5:$B$204<>"")*ISNA(MATCH(Invoices!$B$5:$B$204, Lists!$A$5:$A$34, 0)))=0,
    "OK", "Review")

Invoices 탭에 입력했지만 Lists에 없는 거래처를 표시해요. 이런 거래처는 Summary에서 사라지기 때문이에요.

부분별 설명

Invoices!$B$5:$B$204<>"": 거래처가 있는 행만.
ISNA(MATCH(…, Lists!$A$5:$A$34, 0)): Lists에서 이름을 못 찾으면 TRUE.
SUMPRODUCT(…): 문제 행을 세요.

예시 데이터로 보면: B10 결과 OK

어떻게 쓰이나: Lists에 이름을 추가하거나 청구서의 철자를 고쳐 해결하세요.

바꾸는 방법: 바꿀 것 없음.

배우기: 엑셀 INDEX와 MATCH 함수 사용법 →

팁과 한계

용량: 청구서 200건, 거래처 30곳. 늘리려면 Ctrl+H를 누르고 범위를 통합 문서, 찾는 위치를 수식으로 두고 $204를 $1004로 바꾸세요(거래처는 $34를 더 큰 행으로).
부분 입금은 들어올 때마다 Paid 열에 입력하세요. 잔액과 구간이 갱신되고 완납될 때만 목록에서 빠져요.
기준일에 =TODAY()를 넣으면 항상 최신이지만, 월말 사본을 저장하기 전에는 고정 날짜를 입력해 스냅샷이 나중에 바뀌지 않게 하세요.
여기의 DSO는 간단한 추정치예요. 회계 시스템은 같은 식을 일정 기간에 걸쳐 계산해요.

도움이 되었나요? 잘못된 내용이 있나요? 의견 보내기 · admin@arumsoft.com

매출채권 연령분석과 회수 우선순위 엑셀 템플릿

이 템플릿으로 할 수 있는 일

구성

시작하기 전에

단계별 사용법

기준일 설정

Lists 탭에 거래처 추가

청구서 입력

계산 열 읽기

Summary 탭 확인

Priority 탭으로 회수 업무

Checks 탭 검토

수식 지도

모든 수식 설명

팁과 한계

이 템플릿에 쓰인 기능 배우기