월말 데이터 값만 더해서 평균을 구하는 법

1. 문제

월말값 더하기.xlsx

0.01MB

 

 아래와 같이 일별 데이터가 있는데, 1월은 말일까지 있지만 

 

4월의 경우는 2023/4/30일이 일요일이라 근무일인 4/28까지의 데이터만 있는 특이점이 있습니다.

 

따라서, 단순히 EOMonth로 말일을 구할 수 없는 한계가 있으며, 5월은 2일까지뿐이 없어서 월말이라고 볼 수가 없습니다.

이와 같은 경우 해결책에 대해 알아보겠습니다.

 

2. Scan 함수의 구문

Microsoft 공식 홈페이지의 도움말을 보면

=SCAN ([initial_value], array, lambda(accumulator, value))

라고 되어 있는데, lambda의 인수로 세 번째에 Calculation이 있어야 하는데 누락된 것 같습니다.

​

맞는 구문은

=SCAN ([initial_value], array, lambda(accumulator, value, calculation))

이라 생각됩니다.

​

initial_value는 초기값으로 lambda함수의 첫 번째 인수인 accumulator가 받고,

array는 배열로서 lambda함수의 두 번째 인수인 value가 받습니다.

또한 calculation을 통해 최종 결과값을 계산해서 반환하는 것입니다.​​

​

3. 방법 1 : 달이 바뀔 때 수량을 더하기

월말값 더하기(Scan함수).xlsx

0.01MB

 

가. 수식

E4 : =MAX(SCAN(0,C4:C86,LAMBDA(a,b,IF(MONTH(b)<>IFERROR(MONTH(OFFSET(b,-1,0)),MONTH(b)),a+OFFSET(b,-1,1),a))))/(MAX(MONTH(C4:C86)-MIN(MONTH(C4:C86))))

​

나. Scan 수식 설명

위 수식에서 초기값은 0인데, Lambda함수의 첫 번째 인수인 a로 받고,

C4:D86은 배열인 셀 범위로서 Lambda함수의 두 번째 인수인 b로 받으며,

​

calculation(계산식)은

IF(MONTH(b)<>IFERROR(MONTH(OFFSET(b,-1,0)),MONTH(b)),a+OFFSET(b,-1,1),a))로서

​

If 함수의 조건식인 MONTH(b)<>IFERROR(MONTH(OFFSET(b,-1,0))은

: 범위 내 셀을 하나 이동할 때마다 "월을 구해서 이전 행의 월과 다르다면"인데,

첫 번째 셀인 C4셀의 이전 행이 날짜가 아니라서 에러가 나므로

에러가 날 때는 MONTH(b)로 같은 달로 만들었습니다.

​

참일 때 값은 MONTH(b)),a+OFFSET(b,-1,1)로서

초기값 a 또는 누적된 값에 OFFSET(b,-1,1), 다시 말해 처리 중인 셀의 한 줄 위 오른쪽 셀의 값을 더하므로

달이 바뀔 때마다, 누적된 값에 이전 달의 수량을 더하는 것입니다.

​

거짓일 때 값은 a로서 누적된 값(또는 초기값)을 계속 유지합니다.

​

다. Scan 수식의 결괏값

​

1월일 때는 달이 변하지 않으므로 0을 유지하다가

2/1로 달이 바뀌게 되면 1월의 마지막 행의 수량을 반환하며,

이런 식으로 달이 바뀔 때마다 누적된 값을 반환합니다.

 

예를 들어 3/1은 1월 말 87.33136 + 2월 말 87.633286(화면상은 87,63329) = 174.964646을 반환합니다.

특이한 점은 4월의 경우 4/30 데이터가 없으므로 4월의 마지막 데이터인 4/28의 수량을 더합니다.

​

따라서, 5월 날짜가 없으면 월이 변하지 않아 누적값을 구할 수 없는 문제점이 있으므로

5월 통계가 안 나왔더라도 5/1이란 날짜는 입력해야 하는 점을 주의해야 합니다.

​

​

라. 최종 합계값과 평균 구하기

​

위와 같이 Scan의 결과 값은 배열로 반환되므로,

누적 합계값을 구하려면

Max함수를 이용해 최댓값을 구해야 합니다.

​

그리고, 평균을 구하려면 월로 나눠야 하는데, 월의 최댓값을 구하면 5가 나오므로 1을 빼거나,

=MAX(SCAN(0,C4:D86,LAMBDA(a,b,IF(MONTH(b)<>IFERROR(MONTH(OFFSET(b,-1,0)),MONTH(b)),a+OFFSET(b,-1,1),a))))/(MAX(MONTH(C4:C86))-1)

​

아니면

=MAX(SCAN(0,C4:D86,LAMBDA(a,b,IF(MONTH(b)<>IFERROR(MONTH(OFFSET(b,-1,0)),MONTH(b)),a+OFFSET(b,-1,1),a))))/(MAX(MONTH(C4:C86))-MIN(MONTH(C4:C86)))

라고 최대월(5)-최소월(1) = 4로 해도 됩니다.

 

​

4. 방법 2 : 월의 최대 날짜일 때 수량을 더하기

​

가. 수식

F4 : =MAX(SCAN(0,C4:C86,LAMBDA(a,b,IF(AND((ROW(b)-3)=MATCH(EOMONTH(b,0),C4:C86,1),DAY(b)>25),a+OFFSET(b,0,1),a))))/SUM(--SCAN(0,C4:C86,LAMBDA(a,b,AND((ROW(b)-3)=MATCH(EOMONTH(b,0),C4:C86,1),DAY(b)>25))))

​

나. Scan 함수 수식에 대한 설명

Scan함수를 이용한 수식은

SCAN(0,C4:C86,LAMBDA(a,b,IF(AND((ROW(b)-3)=MATCH(EOMONTH(b,0),C4:C86,1),DAY(b)>25),a+OFFSET(b,0,1),a))) 로서

​

Lambda함수의 첫 번째 인수 a는 0을 받고,

b는 C4:C86을 받습니다.

​

그리고, a와 b를 이용해 계산식을 만드는데,

IF(AND((ROW(b)-3)=MATCH(EOMONTH(b,0),C4:C86,1),DAY(b)>25),a+OFFSET(b,0,1),a)

​

'날짜에 해당하는 행 수에서 3을 뺀 값(왜냐하면 4행부터 시작하기 때문에 1로 만들기 위해 3을 빼는 것임)'이

'Match함수를 이용해 구한 같은 달의 최대 일에 해당하는 위치'와 같을 때는

​

a(초기값 0 또는 누적값)에 같은 달 최종일의 오른쪽 첫 번째 열값인 수량을 더하고,

​

아니면 a값을 유지합니다.

​

방법 1과 다른 점은 같은 달의 최종일을 구하므로, 달이 바뀌지 않아도 된다는 것,

다시 말해 5월이 없어도 됩니다.

​

그러나, 5월도 Match함수로 구하면 5/2이 최종일이 되므로 최소한 25일 이상은 되도록

Day(b)>25라는 조건을 추가하여 And로 연결해서 제외했습니다.

​

다. 최종 누계 값과 평균 구하기

방법 1과 마찬가지로 수량이 계속 누적되므로, 최댓값이 최종 누계값이 되며,

​

평균을 구하려면 월 수를 알아야 하는데,

Scan함수에 적용된 부분을 발췌한 후 Sum을 해서

SUM(--SCAN(0,C4:C86,LAMBDA(a,b,AND((ROW(b)-3)=MATCH(EOMONTH(b,0),C4:C86,1),DAY(b)>25))))

라고 해서

​

조건에 부합하는 최대일수가 있는 월을 세서(0과 1이 있으므로 1이라는 숫자를 셀 때 Sum 함수 사용)

25일 이상이고, 월 최종일이 같은 달의 개수를 구합니다.

​

Scan함수로 구한 값은 true와 false이므로 숫자로 바꾸기 위해 --를 사용했으며

이렇게 하면 False는 0, True는 1이 반환됩니다.