엑셀 SCAN 함수 실전 예제 총정리 | 셀 병합 자동 채우기+누계 보고서 만들기

실습 가이드

라이브 강의 전체영상도 함께 확인해보세요!

위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브강의 풀영상을 확인하실 수 있습니다.

왕초보도 쉽게 쓰는 SCAN 함수 기초 예제

엑셀 M365에 새로 추가된 SCAN 함수는 2022년 9월 베타 버전으로 공개된 함수입니다. 이름이 다소 생소하게 들릴 수 있지만, 프로그래밍에서 SCAN은 데이터를 순차적으로 탐색하면서 누적 계산을 수행하는 과정을 의미합니다.

엑셀 SCAN 함수도 같은 원리로 동작합니다. 선택한 범위를 하나씩 순서대로 탐색하며 누적 합계, 최대값, 곱셈 등 반복 계산을 자동화해주는 함수입니다. 먼저 기본 사용법부터 차근차근 익혀보겠습니다.

SCAN 함수의 각 인수에 대한 자세한 설명은 아래 함수 게시글을 참고하세요!👇

1. 누계 구하기 : 예제파일을 실행한 후 [SCAN] 함수 시트에서 기초 사용법을 확인해보겠습니다. 첫 번째 예제로 왼쪽 표에서 각 날짜별 입고 수량의 누계를 계산해보겠습니다. D10셀을 선택한 뒤 아래와 같이 SCAN 함수를 입력합니다.
   =SCAN(0,C10:C18,SUM)

   

2. 함수를 실행하면 그림과 같이 일자별 입고 수량의 누계가 자동으로 계산됩니다.

   

3. 항목별 최대값 구하기 : 이번에는 일자별 방문객 기록에서 그날까지 누적된 데이터 중 가장 높은 방문객 수를 확인해보겠습니다. 예제파일에서 H10셀을 선택한 뒤 다음과 같이 SCAN 함수를 입력합니다.
   =SCAN(0,G10:G22,MAX)

   

4. 함수를 실행하면 다음과 같이 해당 날짜까지의 가장 높은 방문객 수가 단계별로 계산됩니다.

   

5. 예금 만기해지액 구하기 : 이번에는 1년마다 재예치하는 회전 예금의 연차별 만기 해지액을 계산해보겠습니다. L10셀에 다음과 같이 함수를 작성합니다.
   =SCAN(L7,K10:K19,PRODUCT)

   

6. 함수를 실행하면 최초 납입액 1,000,000원의 연차별 만기해지액이 한 번에 계산됩니다.

   

SCAN + LAMBDA 계산 예제

엑셀 SCAN 함수는 SUM, MAX, PRODUCT 같은 기본 계산을 손쉽게 처리할 수 있지만, 실무에서는 좀 더 유연하고 복잡한 계산이 필요한 경우가 많습니다. 이때 LAMBDA 함수를 함께 사용하면 SCAN 함수의 계산 로직을 자유롭게 커스터마이징할 수 있습니다.

엑셀 LAMBDA 함수의 기초 사용법과 자세한 설명은 아래 LAMBDA 함수 사용법 강의를 참고하세요!👇

1. SCAN + LAMBDA 기초 : 예제파일에서 [SCAN + LAMBDA] 시트로 이동합니다. 먼저 SCAN 함수 안에서 LAMBDA가 동작하는 원리부터 살펴보겠습니다. SCAN 함수에서 LAMBDA는 두 개의 인수를 사용하며, 각 단계의 초기값과 현재값을 받아 계산됩니다.
   =SCAN(초기값,범위,LAMBDA(a,b,...))
   // LAMBDA 함수의 첫번째 인수(a)는 초기값, 두번째 인수(b)는 현재값으로 계산됩니다.
2. D5셀을 선택하고 다음과 같이 SCAN 함수를 작성합니다.
   =SCAN(0,C5:C13,LAMBDA(a,b,a+b))

   

3. 함수를 실행하면 일자별 입고 수량의 합계가 계산됩니다. 이렇게 작성한 SCAN 함수는 수식에 "SUM"을 입력한 것과 동일하게 동작합니다.

   

4. 병합된 셀 빈칸 채우기 : 이 원리를 응용하면 셀 병합으로 생긴 빈 칸을 채우는 기본 공식을 만들 수 있습니다. [FILLDOWN] 시트로 이동한 후 E3셀에 다음과 같이 함수를 작성합니다.
   =SCAN("",B3:B15,LAMBDA(a,b,IF(b="",a,b)))
   // 현재 값을 출력하되 비어있는 경우 이전 값을 출력합니다.

   

5. 함수를 실행하면 아래 그림과 같이 셀 병합된 범위의 빈 칸이 채워진 결과가 반환됩니다.

   

셀 병합 채우기, FILLDOWN 함수 만들기

엑셀로 작성된 보고서를 관리하다 보면 셀 병합으로 인해 생긴 빈 칸 때문에 종종 문제가 발생합니다. 이전에는 파워쿼리나 VBA를 사용해야 했지만, SCAN과 LAMBDA 함수를 활용하면 셀 병합 범위의 빈 칸을 빠르게 채워주는 사용자 정의 함수를 직접 만들 수 있습니다.

1. 셀 병합 채우기 공식 작성 : 예제파일의 [FILLDOWN] 시트에서 H2:K21에 작성된 셀 병합 범위를 빠르게 채워보겠습니다. 먼저 TOCOL 함수로 넓은 범위의 데이터를 세로로 긴 1열 형태로 변환하겠습니다. M3셀을 선택하고 아래 그림과 같이 TOCOL 함수를 작성합니다.
   =TOCOL(H3:K20,0,TRUE)
   오빠두Tip : TOCOL 함수의 자세한 사용법은 아래 TOCOL 함수 기초 사용법 강의를 참고하세요!
   
2. 함수를 실행하면 아래 그림과 같이 세로로 긴 1열 범위가 반환됩니다.

   

3. 이제 1열로 정리된 데이터를 앞에서 알아본 SCAN 함수로 빈 칸을 채워보겠습니다. M3셀에 작성한 공식을 SCAN 함수로 감싸 다음과 같이 수정합니다.
   =SCAN("",TOCOL(H3:K20,0,TRUE),LAMBDA(a,b,IF(b="",a,b)))

   

4. 함수를 실행하면 세로로 긴 1열 범위의 빈 칸이 모두 채워집니다.
5. 마지막으로 WRAPCOLS 함수를 사용해서 1열 범위를 원래 형태로 다시 묶어줍니다. M3셀에 작성한 SCAN 함수를 WRAPCOLS 함수로 감싸 다음과 같이 작성합니다.
   =WRAPCOLS(SCAN("",TOCOL(H3:K20,0,TRUE),LAMBDA(a,b,IF(b="",a,b))),18)

   

6. 함수를 실행하면 아래 그림과 같이 병합된 셀의 빈 칸이 모두 채워진 범위가 반환됩니다.

   

7. FILLDOWN 함수 만들기 : 이제 마지막 단계로 LAMBDA 함수를 활용해 사용자 정의 함수를 등록해보겠습니다. M3셀에 작성한 공식을 토대로 다음과 같이 LAMBDA 함수 공식을 완성합니다.
   =LAMBDA(범위,WRAPCOLS(SCAN("",TOCOL(TRIMRANGE(범위),0,TRUE),LAMBDA(a,b,IF(b="",a,b))),ROWS(TRIMRANGE(범위))))
   오빠두Tip : TRIMRANGE 함수의 자세한 사용법은 아래 기초 영상 강의를 참고하세요!
   
8. [수식] 탭 - [이름 관리자] - [새로 만들기]를 클릭한 뒤 이름과 설명, 참조대상을 다음과 같이 입력해서 함수를 등록합니다.
   · 이름 : FILLDOWN
   · 설명 : 병합된 셀의 빈 칸을 채운 범위를 반환합니다.
   · 참조대상 : =LAMBDA(범위,WRAPCOLS(SCAN("",TOCOL(TRIMRANGE(범위),0,TRUE),LAMBDA(a,b,IF(b="",a,b))),ROWS(TRIMRANGE(범위))))

   

9. [확인] 버튼을 클릭하면 FILLDOWN 함수가 등록됩니다.

   

10. 이제 등록한 FILLDOWN 함수만으로 그림과 같이 병합된 셀의 빈 칸을 빠르게 채울 수 있습니다.

    

GROUPBY + SCAN 누계 보고서 만들기

GROUPBY 함수를 활용하면 피벗테이블 없이도 함수만으로 실시간 월별 매출과 누계가 집계되는 차트를 만들 수 있습니다. GROUPBY와 SCAN 함수를 조합해 보고서와 차트를 작성하는 자세한 과정은 아래 라이브 전체 강의를 참고하세요!👇