연령별 채권잔액 구하기(3) - Reduce 함수

1. 문제

1편에서는 월별 매출액, 수금액 시트에서 월별 채권잔액을 구하고,

 
2편에서는 월별 잔액을 집계표 형식으로 구했는데,

 
2편과 같은 값을 Reduce함수를 이용해 구해보겠습니다.
 

2. Reduce 함수와 Scan 함수

Reduce함수는 최종 결괏값만을 돌려주는데 비해서, Scan 함수는 계산 단계별 값을 반환해 주는 것이 다른 점이고 구문은 같습니다.
 

가. Reduce 함수

Reduce 함수의 구문은
=REDUCE([initial_value], array, lambda(accumulator, value, body))
로서 종전에는 구문이 lambda까지만 표시됐었는데 lambda의 인수까지 표시하는 것으로 바뀌었습니다.
그런데 더 헷갈리네요.
 
아래는 Reduce 함수의 예제로서

 
Reduce함수의 초기값이 비어 있으므로 0이며,
두 번째 값은 계산하기 위한 범위이고,
Lambda 함수의 인수는 첫 번째가 초기값, 두 번째는 계산하기 위한 범위를 받으며,
세 번째에서 a와 b를 이용해 제곱값을 누적해서 최종 누계값을 반환하는 수식입니다.
 
결괏값 91은
0 + 1^2 + 2^2 + 3^2 + 4^2 + 5^2 + 6^2
= 0 + 1 + 4 + 9 + 16 + 25 + 36
= 91로 구해진 것입니다.
 

나. Scan 함수

Scan함수의 인수는 Reduce 함수의 인수와 같습니다.
=SCAN([initial_value], array, lambda(accumulator, value, body))
 
아래는 Scan함수의 예제로서

 
단계별 결괏값을 보여줍니다.
첫 번째는 0+1^2, 
두 번째는 위 값 1에 2^2를 더한 5가 되고,
세 번째는 5+3^2=14,
네 번째는 14+4^2=30,
다섯 번째는 30+5^2=55,
여섯 번째는 55+6^2=91입니다.
 
따라서 Scan 함수의 최종값이 Reduce함수의 결괏값과 같습니다.
 

3. 해법

최종 결괏값만 알면 되므로 이번 경우의 경우에는 Reduce 함수를 사용하면 됩니다.
 

가. 7월 채권잔액 구하기

마찬가지로  7월까지의 채권잔액에서 6월까지의 채권잔액을 빼면 되며,
 
7월까지의 채권잔액은 7월까지의 매출액(이월액 포함)에서 수금액 총계를 차감해서 구하는데,
음수면 0으로 합니다.
 
6월까지의 채권잔액은 6월까지의 매출액(이월액 포함)에서 수금액 총계를 차감해서 구하므로 수금액 총계를 차감하는 것은 같으며, 음수면 0으로 합니다.
 
(1) 7월까지의 채권액 구하기
Reduce 함수의 초기값은 0인데 두 번째 인수로 문자열을 지정하므로 생략하면 안 됩니다.
두 번째 인수는 7월까지의 매출액이므로 7월까지 범위를 지정하는데 Offset 함수를 이용해 지정할 수 있습니다.
Lambda 함수는 초기값과 더할 범위를 첫 번째 인수와 두 번째 인수로 받고
세 번째 수식은 초기값+매출액입니다.
 
따라서, C5셀에
=거래처수불현황!$C5+REDUCE(0,OFFSET(거래처수불현황!$B$4,,,,5*COUNT(C$4:$I$4)),LAMBDA(a,b,IF(b="매출액",a+OFFSET(b,1,0),a)))이라고 입력합니다.

 
7월까지의 채권잔액 23,432,470이 구해집니다.
 
위 수식에서 
IF(b="매출액",a+OFFSET(b,1,0),a)이 중요한데
b가 "매출액"(문자)이면 누계값 a에 "매출액"(문자)의 아래 행의 매출액 값(숫자)을 더하고,
아니면 누계값 a를 그대로 유지하라는 의미입니다.
 
value_if_false인 a가 없으면, "b의 값이 매출액이 아닐 때"마다 초기화되므로 누계값을 구할 수 없어서 이월액 14,654,910이 반환됩니다.

 
그런데 줄이 바뀔 때마다 1이 2가 돼야 하므로
1을 row()-4로 수정합니다.

 

(수식 대체)

IF(b="매출액",a+OFFSET(b,1,0),a)는 Reduce 함수의 예제를 따르면 

a+ IF(b="매출액",OFFSET(b,1,0))라고 바꿀 수 있습니다.

이 때 value_if_false에 값을 0을 입력하지 않아도 됩니다.
 
(2) 수금액 총계 구하기
수금액은 이월액이 없으므로 
=REDUCE(0,OFFSET(거래처수불현황!$B$4,,,,5*COUNT(C$4:$I$4)),LAMBDA(a,b,IF(b="수금액",a+OFFSET(b,row()-4,0),a)))
라고 이월액 C$5를 제거하고, '매출액'을 '수금액'으로 수정하면 됩니다.
 
(3) 7월까지의 채권잔액 구하기
7월까지의 채권잔액에서 수금액 총계를 차감하면 됩니다.
 
따라서 수식은
=거래처수불현황!$C5+REDUCE(0,OFFSET(거래처수불현황!$B$4,,,,5*COUNT(C$4:$I$4)),LAMBDA(a,b,IF(b="매출액",a+OFFSET(b,row()-4,0),a)))-
REDUCE(0,OFFSET(거래처수불현황!$B$4,,,,5*COUNT($C$4:$I$4)),LAMBDA(a,b,IF(b="수금액",a+OFFSET(b,row()-4,0),a)))
이 됩니다.

 
(4) 6월까지의 채권잔액 구하기
6월까지의 채권잔액은
6월까지의 채권액에서 수금액 총계를 차감해서 구하는데, 수금액은 총계이므로 월에 관계없이 동일합니다.
따라서, count 다음의 C$4를 D$4로만 수정하면 됩니다.
 
=거래처수불현황!$C5+REDUCE(0,OFFSET(거래처수불현황!$B$4,,,,5*COUNT(D$4:$I$4)),LAMBDA(a,b,IF(b="매출액",a+OFFSET(b,row()-4,0),a)))-
REDUCE(0,OFFSET(거래처수불현황!$B$4,,,,5*COUNT($C$4:$I$4)),LAMBDA(a,b,IF(b="수금액",a+OFFSET(b, row()-4 ,0),a)))
이 됩니다.
 
(5) 7월의 채권잔액 구하기
7월의 채권잔액을 7월까지의 채권잔액에서 6월까지의 채권잔액을 빼서 구하면 되므로 아래와 같이
6월까지의 채권잔액을 빼는데, 6월까지의 채권잔액을 구할 때 -가 있으므로 - 다음에 괄호를 열고, 맨 뒤에서 괄호를 닫아야 합니다.
=거래처수불현황!$C5+REDUCE(0,OFFSET(거래처수불현황!$B$4,,,,5*COUNT(C$4:$I$4)),LAMBDA(a,b,IF(b="매출액",a+OFFSET(b, row()-4 ,0),a)))-
REDUCE(0,OFFSET(거래처수불현황!$B$4,,,,5*COUNT($C$4:$I$4)),LAMBDA(a,b,IF(b="수금액",a+OFFSET(b, row()-4 ,0),a)))-
(거래처수불현황!$C5+REDUCE(0,OFFSET(거래처수불현황!$B$4,,,,5*COUNT(D$4:$I$4)),LAMBDA(a,b,IF(b="매출액",a+OFFSET(b, row()-4 ,0),a)))-
REDUCE(0,OFFSET(거래처수불현황!$B$4,,,,5*COUNT($C$4:$I$4)),LAMBDA(a,b,IF(b="수금액",a+OFFSET(b, row()-4 ,0),a))))
 
또한 채권잔액이 0일 때는 0이 되어야 하므로 7월까지의 채권잔액에도 max(수식,0)식으로 max를 추가하고, 6월까지의 채권잔액에도 max(수식,0)식으로 max를 추가합니다.
 
=max(거래처수불현황!$C5+REDUCE(0,OFFSET(거래처수불현황!$B$4,,,,5*COUNT(C$4:$I$4)),LAMBDA(a,b,IF(b="매출액",a+OFFSET(b,1,0),a)))-
REDUCE(0,OFFSET(거래처수불현황!$B$4,,,,5*COUNT($C$4:$I$4)),LAMBDA(a,b,IF(b="수금액",a+OFFSET(b,1,0),a))),0)-
max(거래처수불현황!$C5+REDUCE(0,OFFSET(거래처수불현황!$B$4,,,,5*COUNT(D$4:$I$4)),LAMBDA(a,b,IF(b="매출액",a+OFFSET(b,1,0),a)))-
REDUCE(0,OFFSET(거래처수불현황!$B$4,,,,5*COUNT($C$4:$I$4)),LAMBDA(a,b,IF(b="수금액",a+OFFSET(b,1,0),a))),0)
 
6월까지의 채권잔액을 구할 때 앞, 뒤로 괄호를 입력했는데, max함수가 들어갔으므로 괄호를 제거하였습니다.
 
(6) 1월일 경우는 이전월까지의 채권잔액을 0으로 만들기
따라서, 위 수식에서 6월까지의 채권잔액을 구할 때
if(c$4=$i$4,0,max....)라고 if 함수를 추가합니다.

 
 

나. 다른 셀에 복사해서 붙여 넣기

C5셀의 수식을 복사한 후 C5셀에서 i6셀까지 붙여 넣으면
원하는 값이 모두 구해집니다.

 
수식의 길이는 2편과 3편이 비슷한데 이해하기는 3편이 쉽습니다.
 
C5셀의 수식을 비교하면 아래와 같습니다.
 
(2편의 수식)
=MAX(거래처수불현황!$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))),0)-
IF(C$4=$I$4,0,MAX(거래처수불현황!$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))),0))
 
(3편의 수식)
=MAX(거래처수불현황!$C5+REDUCE(0,OFFSET(거래처수불현황!$B$4,,,,5*COUNT(C$4:$I$4)),LAMBDA(a,b,IF(b="매출액",a+OFFSET(b, ROW()-4,0),a)))-REDUCE(0,OFFSET(거래처수불현황!$B$4,,,,5*COUNT($C$4:$I$4)),LAMBDA(a,b,IF(b="수금액",a+OFFSET(b, ROW()-4,0),a))),0)-
IF(C$4=$I$4,0,MAX(거래처수불현황!$C5+REDUCE(0,OFFSET(거래처수불현황!$B$4,,,,5*COUNT(D$4:$I$4)),LAMBDA(a,b,IF(b="매출액",a+OFFSET(b, ROW()-4,0),a)))-REDUCE(0,OFFSET(거래처수불현황!$B$4,,,,5*COUNT($C$4:$I$4)),LAMBDA(a,b,IF(b="수금액",a+OFFSET(b, ROW()-4,0),a))),0))
 

(3편의 Lambda 계산식 수정)

=MAX(거래처수불현황!$C5+REDUCE(0,OFFSET(거래처수불현황!$B$4,,,,5*COUNT(C$4:$I$4)),LAMBDA(a,b,a+IF(b="매출액",OFFSET(b, ROW()-4,0))))-
REDUCE(0,OFFSET(거래처수불현황!$B$4,,,,5*COUNT($C$4:$I$4)),LAMBDA(a,b,a+IF(b="수금액",OFFSET(b, ROW()-4,0)))),0)-
IF(C$4=$I$4,0,MAX(거래처수불현황!$C5+REDUCE(0,OFFSET(거래처수불현황!$B$4,,,,5*COUNT(D$4:$I$4)),LAMBDA(a,b,a+IF(b="매출액",OFFSET(b, ROW()-4,0))))-
REDUCE(0,OFFSET(거래처수불현황!$B$4,,,,5*COUNT($C$4:$I$4)),LAMBDA(a,b,a+IF(b="수금액",OFFSET(b, ROW()-4,0)))),0))

 

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

0.03MB

 

연령별 채권잔액(완성)3(수정).xlsx

0.03MB