연령별 채권잔액 구하기(2) - SumIf + Offset

1. 문제

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

  
아래와 같이 월별(연령별) 채권잔액을 구하려고 합니다.

연령별 채권잔액(완성)1.xlsx

0.03MB

 
 

2. 해법

가. 논리

7월의 채권잔액은 7월까지의 매출액(이월액 포함)에서 미수금의 합계를 차감한 다음
6월까지의 채권잔액을 차감하면 되는데, 6월까지의 채권잔액은 6월까지의 매출액(이월액 포함)에서 미수금의 합계를 차감한 금액으로 7월의 채권잔액을 구할 때나 6월까지의 채권잔액을 구할 때나 미수금의 합계는 같습니다.
 

나. 수식 만들기

 
(1) 7월 채권잔액 구하기
 
㉮ 7월까지 매출액 계 구하기
'거래처수불현황'시트에서 7월까지의 매출액(이월액 포함) 합계를  구하는데,
'매출액'이라는 글자는 4행에 있고, 금액은 그 아래 있습니다.
 
따라서, 7월까지의 매출액 합계는 SumIf 함수를 이용해 구하는데,
이월액은 C열에 있는데 고정이고, 더할 범위는 D열부터 AJ열까지이므로
=거래처수불현황!$C5+SUMIF(거래처수불현황!$D$4:AJ$4,"매출액",거래처수불현황!$D5:AJ5)
가 됩니다.
 
서식을 유지하기 위해서는 위 수식을 복사한 후 
수식 입력줄에 붙여 넣어야 합니다.

 
위 수식에서 열 이름 D에는 $를 붙이고 AJ에 $를 붙이지 않은 것은
D는 시작열로 고정이고 AJ열은 오른쪽으로 복사할 때 범위가 변해야 하기 때문입니다.
 
또한 4행은 $를 붙이고, 5행은 붙이지 않은 것은
4행은 매출액이 있는지 비교하는 줄이기 때문에 고정이고,
5행은 더할 행으로서 거래처명에 따라 변해야 하기 때문입니다.
 
㉯ 7월까지 수금액 계 구하기
7월까지의 매출액 계 수식에서 이월액을 제거하고, 매출액을 수금액으로 수정하면 됩니다.
따라서 수식은
=SUMIF(거래처수불현황!$D$4:AJ$4,"수금액",거래처수불현황!$D5:AJ5)
가 됩니다.
 
㉰ 7월까지 누계 채권잔액 구하기
7월까지 채권잔액은 7월까지 매출액 계에서 7월까지 수금액 계를 빼면 되므로
=거래처수불현황!$C5+SUMIF(거래처수불현황!$D$4:AJ$4,"매출액",거래처수불현황!$D5:AJ5)-SUMIF(거래처수불현황!$D$4:AJ$4,"수금액",거래처수불현황!$D5:AJ5)
가 되는데,

 
㉱ 6월까지의 채권잔액 구하기
7월의 채권잔액은 7월까지의 채권잔액에서 6월까지의 채권잔액을 빼야 하므로
6월까지의 채권잔액을 구하는데, 수금액의 합계 수식은 동일하고,
매출액의 합계만 6월까지로 수정하면 됩니다.
 
따라서, 수식에서 매출액에 해당하는 범위를 AJ에서 AE로 수정하면 되는데,
수금액의 범위는 동일하므로 수금액 구하는 수식의 AJ앞에는 $를 붙입니다.
=거래처수불현황!$C5+SUMIF(거래처수불현황!$D$4:AE$4,"매출액",거래처수불현황!$D5:AE5)-SUMIF(거래처수불현황!$D$4:$AJ$4,"수금액",거래처수불현황!$D5:$AJ5)
 
㉲ 7월의 채권잔액 구하기
7월까지의 채권잔액에서 6월까지의 채권잔액을 빼면 되므로
=거래처수불현황!$C5+SUMIF(거래처수불현황!$D$4:AJ$4,"매출액",거래처수불현황!$D5:AJ5)-SUMIF(거래처수불현황!$D$4:$AJ$4,"수금액",거래처수불현황!$D5:$AJ5)-
(거래처수불현황!$C5+SUMIF(거래처수불현황!$D$4:AE$4,"매출액",거래처수불현황!$D5:AE5)-SUMIF(거래처수불현황!$D$4:$AJ$4,"수금액",거래처수불현황!$D5:$AJ5))
(혹시 엑셀 버전에 따라 중간에 엔터값이 있을 때 수식이 제대로 작동하지 않을 수 있습니다)
 
빼기 할 때 그대로 6월까지 채권잔액을 빼면 뒤의 -가 +가 돼서 계산이 맞지 않으므로
- 다음에서 괄호를 시작하고, 맨 뒤에서 괄호를 닫아야 합니다.
그러면 아래와 같이 수식이 되고, 

 
값 1,836,120이 맞게 구해졌습니다.

 
C5셀을 복사한 후 C6셀에 붙여 넣거나,
C5셀의 채우기 핸들을 6행까지 끌면
충무의 7월 채권잔액이 구해지는데,
941,600으로 맞습니다.
 
(2) 6월 채권잔액 구하기
 
㉮ 수식 복사 시 수식 맞지 않음
6월 채권잔액을 구하기 위해 C5셀의 채우기 핸들을 오른쪽으로 끌면
수식이 복사되는데 값이 달라져야 하는데 같은 값입니다.

 
D5셀의 수식을 살펴보니
=거래처수불현황!$C5+SUMIF(거래처수불현황!$D$4:AK$4,"매출액",거래처수불현황!$D5:AK5)-SUMIF(거래처수불현황!$D$4:$AJ$4,"수금액",거래처수불현황!$D5:$AJ5)-
(거래처수불현황!$C5+SUMIF(거래처수불현황!$D$4:AF$4,"매출액",거래처수불현황!$D5:AF5)-SUMIF(거래처수불현황!$D$4:$AJ$4,"수금액",거래처수불현황!$D5:$AJ5))
로 AJ가 AK로, AE가 AF로 오히려 범위가 넓어졌습니다.
 
왜냐하면 '거래처수불현황'시트는 1월부터 시작하고,
'채권현황'시트는 7월부터 시작하기 때문입니다.
 
㉯ 매출액 등 비교할 범위를 맞게 지정하기
범위를 월에 따라, 다시 말해 계산할 월의 개수에 따라 자동으로 지정되도록 Offset함수를 사용할 수 있습니다.
 
Offset함수의 구문은
OFFSET(reference, rows, cols, [height], [width])
로서 5번째 인수로 너비를 지정할 수 있기 때문입니다.
 

 
위 표를 보면 월에 해당하는 열의 개수가 5개로 동일하며,
월의 개수는 3행을 기준으로 월을 세면 됩니다.
 
따라서 수식은
=OFFSET(거래처수불현황!$B$4,,,,5*COUNT(C$4:$I$4))
이 됩니다.

 
당초에는 매출액을 비교할 범위를 D열부터로 했지만
개수가 5개씩이므로 '거래처수불현황'시트의 B4셀로 고정하고,
월의 개수는 '채권현황'시트에서 구하는데 4행에 월이 있고 1월이 맨 뒤에 있으므로 해당 월부터 1월까지의 개수를 구해야 하므로 1월인 i4셀을 고정했습니다

 
㉰ 더할 범위를 맞게 지정하기
더할 범위는 비교할 범위인 4행의 아래쪽에 있는데,
Offset으로 지정할 때 rows에 1과 2를 대입해야 하므로
row()-4로 지정하면 됩니다.
 
=OFFSET(거래처수불현황!$B$4,ROW()-4,,,5*COUNT(C$4:$I$4))
 
㉱ 수식 완성하기
7월의 채권잔액을 구하는 C5셀의 수식이 아래와 같은데
=거래처수불현황!$C5+SUMIF(거래처수불현황!$D$4:AJ$4,"매출액",거래처수불현황!$D5:AJ5)-SUMIF(거래처수불현황!$D$4:$AJ$4,"수금액",거래처수불현황!$D5:$AJ5)-
(거래처수불현황!$C5+SUMIF(거래처수불현황!$D$4:AE$4,"매출액",거래처수불현황!$D5:AE5)-SUMIF(거래처수불현황!$D$4:$AJ$4,"수금액",거래처수불현황!$D5:$AJ5))
 
거래처수불현황!$D$4:AJ$4를
OFFSET(거래처수불현황!$B$4,,,,5*COUNT(C$4:$I$4))로 수정하고,
 
거래처수불현황!$D$4:AE$4는 6월까지이므로 -1을 해서 한 달 적게
OFFSET(거래처수불현황!$B$4,,,,5*(COUNT(C$4:$I$4)-1)로 수정합니다.
 
또한 거래처수불현황!$D5:AJ5는 한 줄아래인데, 현재 행이 5행이므로 4를 빼서
OFFSET(거래처수불현황!$B$4,ROW()-4,,,5*COUNT(C$4:$I$4))로 수정하고,
 
거래처수불현황!$D5:AE5는 한 달 적으므로
OFFSET(거래처수불현황!$B$4,ROW()-4,,,5*COUNT(C$4:$I$4)-1)로 수정합니다.
 
그런데 수금액을 구할 때는 월에 따라 범위를 조절하지 않고 전체로 하므로
COUNT(C$4:$I$4)를 COUNT($C$4:$I$4)로 고정합니다.
 
따라서 수식은
=거래처수불현황!$C5+SUMIF(OFFSET(거래처수불현황!$B$4,,,,5*COUNT(C$4:$I$4)),"매출액",OFFSET(거래처수불현황!$B$4,ROW()-4,,,5*COUNT(C$4:$I$4)))-SUMIF(OFFSET(거래처수불현황!$B$4,,,,5*COUNT(C$4:$I$4)),"수금액",OFFSET(거래처수불현황!$B$4,ROW()-4,,,5*COUNT($C$4:$I$4)))-
(거래처수불현황!$C5+SUMIF(OFFSET(거래처수불현황!$B$4,,,,5*(COUNT(C$4:$I$4)-1)),"매출액",OFFSET(거래처수불현황!$B$4,ROW()-4,,,5*(COUNT(C$4:$I$4)-1)))-SUMIF(OFFSET(거래처수불현황!$B$4,,,,5*COUNT(C$4:$I$4)),"수금액",OFFSET(거래처수불현황!$B$4,ROW()-4,,,5*COUNT($C$4:$I$4))))
이 되고,

 
값 1,836,120 맞습니다.
 
㉲ 수식 수정 1 : 1월인 경우 이전월까지의 채권잔액을 0으로 함
C5셀의 수식을 C6셀까지 끌고, 다시 I6셀까지 끌면
i5셀의 1월 금액에서 #REF! 에러가 발생하는데,

 
이것은 1월의 이전월이 없어서 그렇습니다.
따라서, - 다음에
if함수를 이용해서 1월이면 0이 되도록 해야 합니다.
 
따라서 수식은 if(i4=$i$4,0을 앞에 추가하고, 뒤에 괄호를 추가하면 됩니다.
=거래처수불현황!$C5+SUMIF(OFFSET(거래처수불현황!$B$4,,,,5*COUNT(I$4:$I$4)),"매출액",OFFSET(거래처수불현황!$B$4,ROW()-4,,,5*COUNT(I$4:$I$4)))-SUMIF(OFFSET(거래처수불현황!$B$4,,,,5*COUNT(I$4:$I$4)),"수금액",OFFSET(거래처수불현황!$B$4,ROW()-4,,,5*COUNT(I$4:$I$4)))-
IF(I$4=$I$4,0,(거래처수불현황!$C5+SUMIF(OFFSET(거래처수불현황!$B$4,,,,5*(COUNT(I$4:$I$4)-1)),"매출액",OFFSET(거래처수불현황!$B$4,ROW()-4,,,5*(COUNT(I$4:$I$4)-1)))-SUMIF(OFFSET(거래처수불현황!$B$4,,,,5*COUNT(I$4:$I$4)),"수금액",OFFSET(거래처수불현황!$B$4,ROW()-4,,,5*COUNT(I$4:$I$4)))))

 
㉳ 수식 수정 2 : 이전월까지의 채권잔액이 0보다 작은 경우 0으로 만들기
i5셀의 수식을 C5셀까지 끌고 6행까지 내립니다.

그래도 D6셀부터는 오른쪽으로 채권잔액이 0이어야 하는데 양수가 나오고 1월은 음수가 나옵니다.
이것은 D6셀 다시 말해 6월까지의 채권잔액은 0인데, 5월까지의 채권잔액이 음수라서 -를 하니 양수가 된 것입니다.

 

 
따라서, 이전까지의 채권잔액이 음수인 경우는 0으로 만들도록 Max 함수를 이용할 수 있습니다.
 
if함수의 value_if_false 부분의 맨 앞에 max를 붙이고, 맨 뒤에 ,0)를 붙입니다.
그러면 수식은
=거래처수불현황!$C6+SUMIF(OFFSET(거래처수불현황!$B$4,,,,5*COUNT(D$4:$I$4)),"매출액",OFFSET(거래처수불현황!$B$4,ROW()-4,,,5*COUNT(D$4:$I$4)))-SUMIF(OFFSET(거래처수불현황!$B$4,,,,5*COUNT($C$4:$I$4)),"수금액",OFFSET(거래처수불현황!$B$4,ROW()-4,,,5*COUNT($C$4:$I$4)))-
IF(D$4=$I$4,0,MAX(거래처수불현황!$C6+SUMIF(OFFSET(거래처수불현황!$B$4,,,,5*(COUNT(D$4:$I$4)-1)),"매출액",OFFSET(거래처수불현황!$B$4,ROW()-4,,,5*(COUNT(D$4:$I$4)-1)))-SUMIF(OFFSET(거래처수불현황!$B$4,,,,5*COUNT($C$4:$I$4)),"수금액",OFFSET(거래처수불현황!$B$4,ROW()-4,,,5*COUNT($C$4:$I$4))),0))

 
 
㉴ 수식 수정 3 : 해당월까지의 채권잔액이 0보다 작은 경우 0으로 만들기
이제 D6셀의 수식을 복사한 다음
C5셀부터 i6셀까지 선택한 다음 붙여 넣기를 합니다.
여전히 음수가 있습니다.

 
이것은 당월까지의 누계 채권잔액이 음수라서 그렇습니다.
E6셀에서 당월까지의 누계 채권잔액을 구하는 수식을 마우스로 끌어서 선택한 후 마우스를 올려놓으면 값이 -7312624입니다. 이때 엑셀 버전이 다르면 F9를 눌러야 할 수도 있습니다.
 

 
마찬가지로 E6셀의 수식에서 = 다음에  max를 붙이고, 당월까지의 누계 채권잔액을 구하는 수식 맨 뒤에 ,0)을 추가합니다.
=MAX(거래처수불현황!$C6+SUMIF(OFFSET(거래처수불현황!$B$4,,,,5*COUNT(E$4:$I$4)),"매출액",OFFSET(거래처수불현황!$B$4,ROW()-4,,,5*COUNT(E$4:$I$4)))-SUMIF(OFFSET(거래처수불현황!$B$4,,,,5*COUNT($C$4:$I$4)),"수금액",OFFSET(거래처수불현황!$B$4,ROW()-4,,,5*COUNT($C$4:$I$4))),0)-
IF(E$4=$I$4,0,MAX(거래처수불현황!$C6+SUMIF(OFFSET(거래처수불현황!$B$4,,,,5*(COUNT(E$4:$I$4)-1)),"매출액",OFFSET(거래처수불현황!$B$4,ROW()-4,,,5*(COUNT(E$4:$I$4)-1)))-SUMIF(OFFSET(거래처수불현황!$B$4,,,,5*COUNT($C$4:$I$4)),"수금액",OFFSET(거래처수불현황!$B$4,ROW()-4,,,5*COUNT($C$4:$I$4))),0))

 
값이 0으로 바뀌었습니다.
 
이제 E6셀의 수식을 복사한 후 C5셀에서 i6셀까지 붙여 넣으면
모두 맞는 값이 구해집니다.
 
㉵ 합계 산식 만들기
채권잔액 합계가 숫자로 입력되어 있으므로
B5셀을 선택한 다음
홈 탭 - 편집 그룹에서 자동 합계를 누르고,
C5셀에서 i5셀까지 끌어 범위를 선택한 다음 엔터키를 누릅니다.
 
그리고, B5셀의 채우기 핸들을 6행까지 끌어 C5셀에도 합계 수식을 넣습니다.

 
설명하면서도 복잡하네요.
천천히 살펴보기 바랍니다.

연령별 채권잔액(완성)2.xlsx

0.03MB

 
 
다음에는 Microsoft 365 버전의 Reduce함수를 이용해 구해보겠습니다.