월별 미수금 발생/잔액표

	B	C	D	E	F	G	H
2	<발생>
3	업체	4월	5월	6월	7월	미수금계	수금
4	갑	10	20	30	20	80	50
5	을	30	10	20	50	110	100
6	병	40	20	10	10	80	30
7
8	<잔액>
9	업체	4월	5월	6월	7월	미수금계	판정
10	갑	0	0	10	20	30	TRUE
11	을	0	0	0	10	10	TRUE
12	병	10	20	10	10	50	TRUE

 

C10 =N(H4<C4)*(C4-H4)

D10 =IF((SUM($C4:C4)-SUM($C10:C10))=$H4,D4,N(($H4-(SUM($C4:C4)-SUM($C10:C10)))<D4)*(D4-($H4-(SUM($C4:C4)-SUM($C10:C10)))))

 ; D10 잡아 오른쪽으로 복사 (예시에서는 F10까지), D10:F10 잡아 아래로 데이타 행 만큼 복사

G10 =SUM(C10:F10)

H10 =(G4-G10=H4)  ; 수식이 잘못 되었는지 참고하기 위한 셀

 

간단한 설명 : 현재 수금된 잔액이 당월 미수금 발생액 보다 작으면 당월의 미수금 잔액은 (발생액-수금잔액)임. 따라서 첫째 열 수식은 C10 =N(H4<C4)*(C4-H4) 이 수식을 쓰면 H4>=C4이면 잔액은 자동으로 0이 됨. 다음 열 부터는 수금잔액을 (수금액(H4)-앞서 깐 금액 합계)로 계산하여 같은 로직을 거침. 앞서 깐 금액은 SUM(전월까지 발생액 합계)-SUM(전월까지 잔액 합계)임. 앞서 깐 금액 합계가 수금액(H4)와 같으면 수금액을 다 써버렸다는 뜻이므로 당월 발생액을 그대로 가져와 잔액으로 표시함. 그걸 모두 한 셀에 구겨 넣으면 D10이 됨.