연령별 채권잔액 구하기(1) - SumIf

1. 문제

아래와 같이 이월액과 월별 매출액, 수금액, 미수금이 있을 때

 
월별 채권 잔액을 7행과 8행에서 구해보겠습니다.

연령별 채권잔액(문제).xlsx

0.03MB

 

2. 해법

가. 논리

문제를 풀려면 어떤 식으로 값을 구해야 하는지 논리가 먼저 정립이 돼야 합니다.
 
(1) 1월의 채권잔액 구하기
1월의 채권잔액은 1월까지 발생한 채권액(이월액 포함)을 모두 회수했다면(초과한 경우 포함) 0이고, 아니면 채권액 - 수금액의 합계가 됩니다.
 
(2) 2월 이후의 채권잔액 구하기
1월은 간단하게 위와 같이 구할 수 있는데,
2월은 2월까지의 채권잔액에서 1월의 채권잔액을 차감해야 합니다. 왜냐하면 2월까지의 채권액에서 수금액 계를 빼면 2월까지의 채권잔액이 나오고, 이것은 1월의 채권잔액을 포함한 금액이기 때문입니다.
 
이런 식으로 3월 이후는 3월까지의 채권잔액에서 2월까지의 채권잔액을 차감해야 합니다.
 

나. 수식으로 구현하기

(1) 1월 채권잔액 구하기

 

 
① 1월까지의 채권잔액
이월액이 있으므로 이월액에 매출액을 더해서
=sum(c5:d5)가 됩니다.
 
② 수금액의 총합계
C5(첫 달)에서 AJ5(끝 달)까지의 수금액의 합계를 구하면 되므로
=sumif(c4:aj4,"수금액",c5:aj5)이 됩니다.
 
그런데 아래 줄로 복사해야 하므로
=sumif($c$4:$aj$4,"수금액",$c5:$aj5)
이 됩니다.
 
4행은 고정이므로 $를 붙여야 하고(절대참조),
금액은 줄에 따라 행이 바뀌어야 하므로 $를 붙이면 안 됩니다(혼합참조).
 
③ 1월의 채권잔액
=sum(c5:d5)- sumif($c$4:$aj$4,"수금액",$c5:$aj5)
 
그런데 sum(c5:d5)는
C5는 이월액으로 고정이고, D5는 매출액이므로
$C5+SUMIF($D$4:F$4,"매출액",$D5:F5)라고 바꿀 수 있습니다.
 
그런데 수금액이 채권액보다 크면 채권잔액은 0이 되므로
Max 함수를 이용해
=MAX($C5+SUMIF($D$4:F$4,"매출액",$D5:F5)- SUMIF($C$4:$AJ$4,"수금액",$C5:$AJ5),0)
라고 표현할 수 있습니다.

 
다시 말하면, 음수일 때는 0이 크고, 양수일 때는 양수가 크므로 원하는 결괏값이 나옵니다.
 
(2) 2월 채권잔액 구하기
F7셀의 수식을 K7셀에 복사하면 8,922,210이 나옵니다.
이것은 1월과 2월의 채권액에서 수금액의 총계를 뺀 금액으로 1월의 채권잔액이 포함되어 있으므로 1월의 채권잔액인 7,532,250을 빼야 합니다.
 
따라서, 수식 뒤에 - MAX($C5+SUMIF($D$4:F$4,"매출액",$D5:F5)- SUMIF($C$4:$AJ$4,"수금액",$C5:$AJ5),0)를 붙여서
=MAX($C5+SUMIF($D$4:K$4,"매출액",$D5:K5)- SUMIF($C$4:$AJ$4,"수금액",$C5:$AJ5),0)-MAX($C5+SUMIF($D$4:F$4,"매출액",$D5:F5)- SUMIF($C$4:$AJ$4,"수금액",$C5:$AJ5),0)
가 됩니다.

 
(3) '1월 채권잔액 구하기'까지 적용가능하도록 수식 수정
F7셀에도 동일하게 적용될 수 있도록 첫 달일 때는 0을 빼도록 if함수를 사용해서 수정해야 합니다.
 
따라서, 수식은
=MAX($C5+SUMIF($D$4:K$4,"매출액",$D5:K5)- SUMIF($C$4:$AJ$4,"수금액",$C5:$AJ5),0)-IF(G$3=DATE(2024,1,1),0,MAX($C5+SUMIF($D$4:F$4,"매출액",$D5:F5)- SUMIF($C$4:$AJ$4,"수금액",$C5:$AJ5),0))
이 됩니다.
 
K7셀의 수식을 복사한 후 F7셀에 붙여 넣으면
F7셀의 수식은
=MAX($C5+SUMIF($D$4:F$4,"매출액",$D5:F5)- SUMIF($C$4:$AJ$4,"수금액",$C5:$AJ5),0)-IF(B$3=DATE(2024,1,1),0,MAX($C5+SUMIF(A$4:$D$4,"매출액",A5:$D5)- SUMIF($C$4:$AJ$4,"수금액",$C5:$AJ5),0))
로 답이 변함이 없이 맞습니다.

 
(4) 다른 달에도 복사해서 붙여 넣기
F7셀의 수식을 복사해서 F8에 붙여 넣고, F7셀과 F8셀의 수식의 복사해서 나머지 달에 모두 붙여 넣고, 7월의 결괏값을 확인하니 1,836,210과 941,600로 맞습니다.

 
Aj7셀의 수식을 확인해 보면
=MAX($C5+SUMIF($D$4:AJ$4,"매출액",$D5:AJ5)- SUMIF($C$4:$AJ$4,"수금액",$C5:$AJ5),0)-IF(AF$3=DATE(2024,1,1),0,MAX($C5+SUMIF($D$4:AE$4,"매출액",$D5:AE5)- SUMIF($C$4:$AJ$4,"수금액",$C5:$AJ5),0))
에서
 
$C5+SUMIF($D$4:AJ$4,"매출액",$D5:AJ5)- SUMIF($C$4:$AJ$4,"수금액",$C5:$AJ5)은
최초 이월액에 해당 월까지의 매출액을 합산한 다음
수금액의 총합계를 빼서 당월까지의 채권잔액 누계를 구하는 것이고,
 
$C5+SUMIF($D$4:AE$4,"매출액",$D5:AE5)- SUMIF($C$4:$AJ$4,"수금액",$C5:$AJ5)
은 이월액에 전월까지의 매출액을 더한 다음 수금액 합계를 빼서
전월까지의 채권잔액을 구하는 수식입니다.
 
따라서, 당월까지의 채권잔액 누계에서 전월까지의 채권잔액을 빼니 
당월의 채권잔액이 되는 것입니다.
 

다. 검산

채권잔액 계가 맞는지 확인하기 위해 7행을 클릭하면
작업 표시줄에서 표시되는 합계가 15,634,410으로 7월의 미수금 잔액과 맞습니다.

 
8행도 클릭해 보고 작업 표시줄의 합계와 비교해 보면 값이 941,600원으로 같습니다.

0.00MB

 
 
다음에는 '채권현황'시트에서 월별 채권 잔액을 구해보겠습니다.