목표 달성 소요일수 구하기 (1) - Scan 함수

1. 문제

달성 소요 일수 계산.xlsx

0.01MB

 

 

위와 같이 C열에 매장별 입고량이 있고, D열에는 판매량이 있으며, 판매량 목표는 입고량의 80%입니다.

이에 따른 달성 여부가 G열에 있는데,

목표가 언제 달성되었는지, 다시 말해 H열부터의 일자별 판매량 누계가 목표량을 넘어가는 일수 또는 날짜를 구하려고 하는 것입니다.

 

2. 해법 1 - Scan 함수 이용

 

가. 누계 판매량 구하기

 MIcrosoft 365 버전이라면 Scan 함수를 이용할 수 있습니다.

Scan 함수의 사용법에 대해서는 아래 글을 참고 바랍니다.

 

https://lsw3210.tistory.com/484

선입선출법에 따른 재고월 구하기

 

판매량 누계가 목표량 F영을 초과하는 경우 누계 일수를 반환하면 되므로

 

먼저 Scan 함수를 이용해 누계 판매량을 구합니다.

 

인수는 초기값과 일별 판매량이며, 수식은

=SCAN(,I3:AP3,LAMBDA(init_value,day_product,init_value+day_product))

이 됩니다. 

 

 

초기 누계 판매량이 0이어야 하므로 첫 번째 인수로 0을 입력하여야 하는데, 비워둬도 됩니다.

두 번째 인수는 일자별 판매량으로 i3에서 AP3까지 범위를 입력합니다.

 

그리고, 이 2개 변수를 Lambda 함수에 차례대로, intitial_value와 day_product란 이름으로 넣고,

init_value+day_product로 누계 판매량을 구합니다.

 

그런데 결괏값이 #분산!라고 표시되는 것은 누계 판매량이 H열부터 기록돼야 하는데 숫자가 들어가 있기 때문입니다.

 

 

SCAN(,I3:AP3,LAMBDA(init_value,day_product,init_value+day_product))까지 범위를 잡고 F9키를 누르면

아래와 같이 0부터 중간 이후에는 2,8,11식으로 누계가 표시됩니다.

 

 

Microsoft 365 버전이라면 f9키를 누를 필요 없이 누게 값을 확인할 수 있습니다. 수식 입력 줄 위에 중괄호 안에 표시된 것이 Microsoft 365 버전에서 보여주는 결괏값입니다.

 

원 수식으로 돌리기 위해 Esc키를 누릅니다.

 

나. Mactch 함수로 근삿값 찾기(1)

Match 함수의 구문은

MATCH(lookup_value, lookup_array, [match_type])

으로서 첫 번째 인수가 찾을 값, 두 번째 인수가 찾을 범위, 세 번째 인수가 일치 유형인데,

일치 유형은 정확히 일치하는 값을 찾으려면 0, 근삿값 중에서 작은 값을 찾으려면 1인데, 오름차순으로 정렬되어 있어야 하고, 근삿값 줄에서 큰 값을 찾을 때는 -1인데, 내림차순으로 정렬되어 있어야 합니다.

 

Scan함수의 결괏값은 오름차순으로 정렬되어 있으므로 1을 사용합니다.

 

위 수식 앞에 Match 함수를 넣고, 찾을 값으로 F3셀을 입력하고 끝에 1을 추가하고 괄호를 닫으면

=MATCH(F3,SCAN(,I3:AP3,LAMBDA(init_value,day_product,init_value+day_product)),1)

라는 수식이 만들어집니다.

 

결괏값은 34입니다.

 

 

그런데 우리는 작은 값이 아니라 작은 값을 초과할 때는 찾아야 하므로 1을 더해야 합니다.

그리고, H3셀의 채우기 핸들을 더블 클릭하면 35,30, 33, 31,33이란 값이 구해집니다.

 

그런데 첫 번째 값 35는 판매일수인 34보다 크기 때문에 목표 미달입니다.

 

위에서 (MATCH(F3,SCAN(,I3:AP3,LAMBDA(init_value,day_product,init_value+day_product)),1)+1)로 구한 목표 달성 일수가

COUNT(I3:AP3)로 구한 판매일수보다 크다면 '목표량 미달"이라고 하고, 작거나 같다면 위에서 구한 값으로 하면  되므로

아래 수식이 됩니다.

 

=IF((MATCH(F3,SCAN(,I3:AP3,LAMBDA(init_value,day_product,init_value+day_product)),1)+1)>COUNT(I3:AP3),"목표량 미달",MATCH(F3,SCAN(,I3:AP3,LAMBDA(init_value,day_product,init_value+day_product)),1)+1)

 

 

다. Mactch 함수로 근삿값 찾기(2)

근삿값을 찾지 않고 Scan 함수 다음에 F3보다 큰지 비교하는 구문을 넣으면 True와 False를 반환하므로 True를 찾도록 하면 1을 더하지 않더라고 처음으로 커지는 값의 위치를 알 수 있습니다.

 

그리고, 이렇게 하는 것의 장점은 True값을 찾기때문에 True가 없으면 에러가 발생하므로 IfError함수로 간단하게 처리할 수 있다는 것입니다.

 

수식은 =IFERROR(MATCH(TRUE,SCAN(,I3:AP3,LAMBDA(init_value,day_product,init_value+day_product))>F3,0),"목표량 미달")이 됩니다.

 

 

H3셀의 수식을 F7셀까지 복사한 후 H4셀에서 SCAN(,I4:AP4,LAMBDA(init_value,day_product,init_value+day_product))>F4를을 범위로 잡고 F9키를 누르면 False와 True값이 반환되는 것을 알 수 있습니다.

 

수식 입력줄 위에 표시되는 결괏값은 일부만 표시되어 True가 보이지 않습니다.

 

달성 소요 일수 계산(완성)1.xlsx

0.01MB