대출 및 주택담보대출 상환 계산 시트

시트 구성 — '청구서' 탭

열	머리글	입력
A	청구서 번호	직접 입력
B	거래처	직접 입력('거래처' 탭에서 드롭다운)
C	발행일	직접 입력
D	금액	직접 입력
E	결제 기한(일)	수식
F	결제 예정일	수식
G	입금일	직접 입력(입금 전까지 비워 둠)
H	상태	수식
I	연체 일수	수식

수식(10개 중 6개 발췌)

E2 — 거래처 목록에서 결제 기한 가져오기 =IFERROR(VLOOKUP(B2, '거래처'!A:C, 3, FALSE), 30) '거래처' 탭에서 해당 거래처의 결제 기한을 찾고, 등록되지 않은 거래처라면 30일로 계산합니다.

F2 — 결제 예정일 =IF(C2="", "", C2 + E2) 발행일에 결제 기한을 더합니다. 빈 행에서는 비어 있습니다.

H2 — 상태 =IF(C2="", "", IF(G2<>"", "입금완료", IF(TODAY()>F2, "연체", "미결제"))) 입금일이 있으면 '입금완료', 없으면 예정일이 지난 뒤 '연체', 그 전에는 '미결제'.

I2 — 연체 일수 =IF(H2="연체", TODAY() - F2, "")

대시보드!B2 — 미수금 합계 =SUMIFS('청구서'!D:D, '청구서'!H:H, "<>입금완료", '청구서'!C:C, "<>")

대시보드!A5 — 거래처별 미수금(수식 하나로 표 전체 생성) =QUERY('청구서'!B:H, "select B, sum(D) where H <> '입금완료' and B is not null group by B order by sum(D) desc label sum(D) '미수금'", 1)

붙여 넣는 위치

• E2, F2, H2, I2는 '청구서' 탭 2행에 넣고 500행까지 끌어서 복사
• 대시보드 수식 두 개는 한 번만 붙여 넣으면 알아서 갱신됩니다
• H2:H500에 조건부 서식: 텍스트가 '연체'이면 연한 빨간색 채우기

미리 고려한 경우

• 일부만 입금된 청구서: 남은 금액으로 새 행을 만들고 INV-041b처럼 번호를 붙이세요
• 거래처 이름 변경: '거래처' 탭에서만 바꾸면 드롭다운 덕분에 모든 행이 일관되게 유지됩니다
• 엑셀에서 쓸 경우: QUERY를 뺀 나머지 수식은 모두 작동합니다. 대신 B, D, H열로 피벗 테이블을 만드세요

확인 예시

청구서 INV-038, 거래처 북쪽치과, 9월 1일 발행, 결제 기한 14일 → 예정일 9월 15일. 오늘이 10월 20일이고 미입금 → 연체, 35일.