선입선출 재고금액 구하기(Let, Scan 함수 등 활용)

1. 문제

아래와 같이 전기 재고가 있고, 입고와 출고가 되었을 때 먼저 들어온 것이 먼저 나가도록 한 후 재고를 구하는 것이 선입선출법(FIFO : First In First Out)입니다.

 

입고 순서는 아래와 같습니다.

 

예를 들어 A품목의 경우 전기 재고가 20개 있고, 출고가 140개 되었으므로 입고 물량을 120개 차감해야 하는데,

선입선출이므로 1월 물량부터 순서대로 차감합니다.

 

1월 물량이 20, 2월 물량이 30, 3월 물량이 30, 4월 물량 30을 더하면 110이 되므로

5월 물량 20개 중 10개가 남고, 6월 물량 10개는 모두 남아서

재고 금액은 5월 10개 * 53 + 6월 10개 * 54 = 1,070이 됩니다.

선입선출재고금액산출방법.xlsx

0.01MB

 

 

이와 반대되는 것으로 나중에 들어온 것부터 차감하는 것을 후입선출법(LIFO : Last In First Out)이라고 합니다.

 

2. Microsoft 365 버전 기준 풀이

Microsoft 365 버전용 함수인 Let과 Scan 함수 등을 이용한 해법입니다.

기존 함수를 이용할 경우 함수식이 훨씬 길어지기 때문에, Microsoft 365 버전용 함수를 이용하는 것이 훨씬 좋은 방법이지만 처음 접하는 것이기 때문에 낯설 것입니다.

​

Microsoft 365 버전용 함수를 이용한 J4셀 수식 : =LET(a,MATCH(G5,SCAN(B5,$L5:$AC5,LAMBDA(s,c,s+IF(MOD(COLUMN(c),3)=0,c))),1),

total_col,COLUMNS(L5:AC5),

(SUM(IF(OFFSET($L$4,,,,a+1)="수량",OFFSET(L5,,,,a+1)))+B5-G5)*OFFSET(L5,,a+1)

+SUM(IF(OFFSET($L$4,,a+3,,total_col-(a+3))="금액",OFFSET(L5,,a+3,,total_col-(a+3))))

)

​

기존 함수를 이용한 수식 J4셀 수식 :

=IF(INDEX($L5:$ZZ5,1,LARGE(INDEX(($L$4:$ZZ$4="수량")*($L5:$ZZ5<>"")*($L5:$ZZ5>0)*COLUMN($L5:$ZZ5),),1)-11)=I5,INDEX($L5:$ZZ5,1,LARGE(INDEX(($L$4:$ZZ$4="금액")*($L5:$ZZ5<>"")*($L5:$ZZ5>0)*COLUMN($L5:$ZZ5),),1)-11),IF(INDEX($L5:$ZZ5,1,LARGE(INDEX(($L$4:$ZZ$4="수량")*($L5:$ZZ5<>"")*($L5:$ZZ5>0)*COLUMN($L5:$ZZ5),),1)-11)>I5,INDEX($L5:$ZZ5,1,LARGE(INDEX(($L$4:$ZZ$4="단가")*($L5:$ZZ5<>"")*($L5:$ZZ5>0)*COLUMN($L5:$ZZ5),),1)-11)*I5,INDEX($L5:$ZZ5,1,LARGE(INDEX(($L$4:$ZZ$4="금액")*($L5:$ZZ5<>"")*($L5:$ZZ5>0)*COLUMN($L5:$ZZ5),),1)-11)+(I5-INDEX($L5:$ZZ5,1,LARGE(INDEX(($L$4:$ZZ$4="수량")*($L5:$ZZ5<>"")*($L5:$ZZ5>0)*COLUMN($L5:$ZZ5),),1)-11))*INDEX($L5:$ZZ5,1,LARGE(INDEX(($L$4:$ZZ$4="단가")*($L5:$ZZ5<>"")*($L5:$ZZ5>0)*COLUMN($L5:$ZZ5),),2)-11)))

 

3. 함수 설명

​가. Let 함수

Let 함수의 구문은 =LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])로

이름1, 값1, 계산식 또는 이름2, 값2, [계산식 또는 이름3...]의 인수를 가지며

방정식처럼 사용합니다.

​

다시 말해 =Let(x,1,y,2,x+y)라고 하면 x에 1을 대입하고, y에 2를 대입한 후 x+y의 값을 반환합니다.

​

나. Scan 함수

Scan 함수의 구문은 =SCAN ([initial_value], array, lambda(accumulator, value, calculation))으로서

인수로 시작값, 배열, 그리고, lambda식이 나오는데, lambda 함수의 인수로는 누적값(accumulator), 값(value)과 계산식(calculation)이 있습니다.

​

아래와 같은 경우 Scan 다음의 1은 a에 할당되고, A1:C2는 b에 할당되는데 a*b값이 다시 a에 할당되어 반복계산을 수행합니다.

 

따라서, 1*1=1, 1*2=2, 2*3=6, 6*4=24, 24*5=120, 120*6=720이 반환됩니다.

​

다. 기타 함수

Mod는 나머지를 구하는 함수이고,

Column은 열 수, 다시 말해 A열은 1, B열은 2를 반환하는 함수이며,

Columns는 열의 개수, 다시 말해 Columns(A:C)하면 A열부터 C열까지 열의 개수 3을 반환하는 함수입니다.

​

그리고, Match함수는 범위 내에서 일치하거나 근접한 값의 위치를 구해주는 함수이며,

Offset함수는 상대적인 위치의 셀 또는 범위를 구해주는 함수입니다.

​

​

4. 수식 설명

가. Let 함수를 이용한 수식

=LET(a,MATCH(G5,SCAN(B5,$L5:$AC5,LAMBDA(s,c,s+IF(MOD(COLUMN(c),3)=0,c))),1),

total_col,COLUMNS(L5:AC5),

(SUM(IF(OFFSET($L$4,,,,a+1)="수량",OFFSET(L5,,,,a+1)))+B5-G5)*OFFSET(L5,,a+1)

+SUM(IF(OFFSET($L$4,,a+3,,total_col-(a+3))="금액",OFFSET(L5,,a+3,,total_col-(a+3))))

)

​

위 수식은 전체적으로는 Let 함수를 이용한 수식입니다.

​

a라는 변수에 MATCH(G5,SCAN(B5,$L5:$AC5,LAMBDA(s,c,s+IF(MOD(COLUMN(c),3)=0,c))),1)값을 대입하고,

total_col이라는 변수에 COLUMNS(L5:AC5)를 할당한 다음

a와 total_col를 이용한 계산식이 (SUM(IF(OFFSET($L$4,,,,a+1)="수량",OFFSET(L5,,,,a+1)))+B5-G5)*OFFSET(L5,,a+1)

+SUM(IF(OFFSET($L$4,,a+3,,total_col-(a+3))="금액",OFFSET(L5,,a+3,,total_col-(a+3))))입니다.

​

나. Scan 함수를 이용한 수식

SCAN(B5,$L5:$AC5,LAMBDA(s,c,s+IF(MOD(COLUMN(c),3)=0,c)))

는 B5값인 전기 재고를 초기값으로 해서 Lambda 함수의 s값에 할당하고,

$L5:$AC5를 배열로 사용해서 c값에 대입한 다음

(Lambda함수에서) s와 c를 이용해서

초기값 s에 IF(MOD(COLUMN(c),3)=0,c)를 더하는데,

​

IF(MOD(COLUMN(c),3)=0,c)란 L5에서 AC5셀까지의 열 수를 3으로 나눴을 때 나머지가 0인 셀의 값을 구하는, 다시 말해 수량의 합을 계속 누적해 가는 것입니다.

 

처음에는 20+20=40, 그리고, 단가와 금액은 나머지가 0이라는 조건에 부합하지 않으므로 0이 돼서 40이 세 번 유지되고,

그다음은 40 + 30 = 70, 그다음은 70 + 30 = 100. 그다음은 100 + 30 = 130

그다음은 130 + 20 = 150, 그다음은 150 + 10 = 160이 됩니다.

​

Scan에 해당하는 수식을 마우스로 끌어서 범위를 잡고 수식 위에 커서를 놓으면 아래와 같이 누적된 값이 표시됩니다.

​

다. 출고 수량보다 작은 누계값에 해당하는 열 수 구하기

이제 출고 수량에 도달하는 시점을 구하기 위해 Match 함수를 이용합니다.

 

출고 수량은 G5셀에 있으며 140이라는 값과 같거나 작은 값 중 가장 큰 값을 구하기 위해 세 번째 인수로 1을 사용하며, 그러면 12라는 값이 구해집니다.

 

12는 140을 넘지 않는 L5셀부터의 최대 열 수입니다.

​

라. 초과하는 시점의 잔여 수량 * 단가를 곱해 잔여 금액 구하기

초과하는 시점의 잔여 수량을 구하려면 전기재고 + "1부터 (12+1)까지의 수량 합계"를 구한 후 출고 수량을 빼면 됩니다.

​

수식은 (SUM(IF(OFFSET($L$4,,,,a+1)="수량",OFFSET(L5,,,,a+1)))+B5-G5)이며,

SUM(IF(OFFSET($L$4,,,,a+1)="수량",OFFSET(L5,,,,a+1)))은 4행의 "수량"에 해당하는 5행의 값(수량)의 합계를 구하는 것이며,

 

그러고 나서 B5의 전기 재고를 더하고, 출고 수량 G5를 빼는 것입니다.

​

*OFFSET(L5,,a+1)는 L5셀에서 (12+1)번째 열(자신의 위치가 0이므로 13번째가 됨)인 단가를 곱하는 것입니다.

​

따라서, 초과 시점의 잔여 금액 10*53=530이 나옵니다.

​

​

마. 초과하는 시점 다음부터의 금액 합계 구하기

SUM(IF(OFFSET($L$4,,a+3,,total_col-(a+3))="금액",OFFSET(L5,,a+3,,total_col-(a+3))))

금액에 해당하는 값의 합계를 구하는 것인데,

​

더할 범위를 구하기 위해

L5셀을 기준으로 (a+3)만큼 오른쪽으로 이동한 다음

total_col-(a+3)으로 잔여 열 수를 구합니다.

total_col은 18이고, a+3은 15이므로 3이 됩니다.

​

따라서, AA5셀에서 AC5셀 중에서 금액의 합계를 구하므로 540이 구해지며,

총재고는 530 + 540 = 1,070이 됩니다.

​

바. 6행과 7행의 수식 결과

6행의 a값은 12이며, 7행의 a값은 6이 구해집니다.

​

=LET(a,MATCH(G7,SCAN(B7,$L7:$AC7,LAMBDA(s,c,s+IF(MOD(COLUMN(c),3)=0,c))),1),a)는

a에 해당하는 값을 구한 후 a값을 반환하는 수식입니다.

​

따라서 12와 6을 기준으로 해서 잔여 금액을 구분해서 구한 후 합계를 구하면 재고 금액이 됩니다.

​

적응하려면 정말 시간이 많이 걸립니다.

그러나, 자꾸 하다 보면 익숙해질 겁니다.

 

선입선출재고금액산출방법(let_scan_lambda).xlsx

0.01MB