엑셀 SCAN 함수로 누적합계, 누적평균, 월별 누계 완벽 정리

1. 누적이익 구하기

기존에 C열의 누적매출을 구하기 위해 일반 엑셀 범위에서는 IF와 ISNUMBER 함수를 중첩하여 아래와 같은 수식을 사용했습니다.

C4: =IF(ISNUMBER(C4),SUM($C$4:C4),"")

참고 영상:

최신 동적 배열 함수인 SCAN 함수를 사용해서 해결할 수 있습니다. F열에 '누적이익' 금액을 구해보겠습니다.

F4: =SCAN(0,E4:E33,LAMBDA(a,b,a+b))

SCAN 함수는 initial_value, array, function 이렇게 3개의 인수를 갖습니다.

LAMBDA(a,b,a+b) 수식의 의미는 다음과 같습니다.

SCAN 함수를 사용하면 기존 데이터 구조가 변경되더라도(예: 중간에 행을 삽입하여 데이터를 추가하더라도) 기존 수식을 변경할 필요 없이 자동으로 업데이트된다는 강력한 장점이 있습니다.

2. 누적평균 구하기

단순한 누적 합계라면 기존 수식을 사용해도 큰 차이가 없을 수 있습니다. 이번에는 '누적평균'을 구해보겠습니다. 예를 들어 12/2일의 누적 평균은 1일과 2일 실적을 더해 2로 나누고, 12/3일은 1~3일 실적을 더해 3으로 나누는 방식입니다.

기존 방식 (SUM, ROW 활용)

H4: =SUM($C$4:C4)/(ROW(C4)-3)

SUM과 ROW 함수를 사용하여 직설적으로 표현한 수식입니다. 아래로 복사하면 결과는 구해지지만, 데이터 행만큼 수식을 복사해 넣어야 한다는 단점이 있습니다.

SCAN 함수 활용

I4: =SCAN(0,C4:C33,LAMBDA(a,b,a+b))/SEQUENCE(ROWS(C4:C33))

SCAN, LAMBDA, SEQUENCE, 그리고 ROWS 함수를 조합했습니다.

이 두 가지 결과값을 나누면 하나의 셀에 입력한 하나의 수식으로 누적평균이 한 번에 구해집니다.

3. 월별 누적매출 구하기 (월 변경 시 리셋)

일반적인 누적 매출은 아래와 같이 구하면 됩니다.

J4: =SCAN(0,C4:C33,LAMBDA(a,b,a+b))

하지만 데이터가 12월에서 1월로 넘어가는 경우처럼, 월이 바뀔 때 이전 누적값은 무시하고 새롭게 누적해야 할 필요가 있습니다. 기존 방식으로는 수동으로 수식을 끊어서 새로 작성해야 했습니다.

1월이 되면 리셋하는 수식

J4: =SCAN(0,SEQUENCE(ROWS(C4:C33)),
    LAMBDA(a,i,IF(MONTH(INDEX(B4:B33,i))=1,
    INDEX(C4:C33,i),a+INDEX(C4:C33,i)))
)

이 수식의 핵심 논리는 다음과 같습니다.

하지만 이 수식은 완전하지 않습니다. 아래 수식을 사용하세요.

매월(월이 바뀔 때마다) 리셋하는 수식

만약 특정 월(1월)뿐만 아니라, 월이 바뀔 때마다 자동으로 값을 새로 누적하고 싶다면 아래 수식을 사용합니다.

=SCAN(0,SEQUENCE(ROWS(C4:C33)),LAMBDA(a,i,
    IF(i=1,INDEX(C4:C33,i),
        IF(MONTH(INDEX(B4:B33,i))<>MONTH(INDEX(B4:B33,i-1)),
        INDEX(C4:C33,i),
        a+INDEX(C4:C33,i)
        )
    ))
)

이전 행(i-1)과 현재 행(i)의 월(Month)이 같은지 비교하여 리셋 여부를 결정하는 방식입니다.

4. 사용자 지정 함수(모든 엑셀 버전)

SCAN 함수는 강력하지만 Office 365 등 최신 버전에서만 사용할 수 있습니다. 이를 해결하기 위해 모든 엑셀 버전에서 사용 가능한 VBA 사용자 지정 함수를 준비했습니다.

자주 묻는 질문 (FAQ)

마치며

누적 합계는 데이터의 흐름을 파악하는 데 중요한 지표입니다. 상황에 따라 SCAN 함수를 활용하거나, VBA 사용자 정의 함수를 활용하여 업무 효율을 높여보시기 바랍니다.

멤버십 회원이 되시면 강의에 사용된 예제 파일과 전체 코드를 다운로드하실 수 있습니다.