엑셀 SCAN 함수 실전 예제 총정리 | 셀 병합 채우기 + 누계 보고서
엑셀 SCAN 함수 에제 목차 바로가기
영상 강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [실무기초] 엑셀 SCAN 함수 실전 예제 완벽 정리예제파일
라이브 강의 전체영상도 함께 확인해보세요!
위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브강의 풀영상을 확인하실 수 있습니다.
왕초보도 쉽게 쓰는 SCAN 함수 기초 예제
엑셀 M365에 새로운 함수인 'SCAN' 함수가 업데이트 되었습니다. (2022년 9월, 베타 버전) 처음 듣는 이름에 다소 생소하게 느껴질 수 있지만, 프로그래밍에서 SCAN은 데이터를 순차적으로 탐색하며 누적 계산하는 과정을 뜻합니다.
프로그래밍에서 SCAN은 데이터를 순차적으로 탐색하는 과정을 의미합니다. 엑셀 SCAN 함수도 이와 동일하게 선택한 범위를 하나씩 순서대로 계산하면서 누적 합계, 최대값, 곱셈 등의 반복 계산을 자동화하는 함수입니다. 먼저 이 SCAN 함수의 기초 사용법을 익혀보겠습니다.
SCAN 함수 각 인수에 대한 자세한 설명은 아래 함수 게시글을 참고하세요!👇
- 누계 구하기 : 먼저 예제파일을 실행한 후, [SCAN] 함수 시트에서 기초 사용법을 알아보겠습니다. 첫번째로 왼쪽에 있는 표에서 각 날짜별 입고 수량의 누계를 계산하겠습니다. D10셀을 선택하고 아래와 같이 SCAN 함수를 입력합니다.
=SCAN(0,C10:C18,SUM)
날짜별 입고 수량의 누계를 구합니다. - 함수를 실행하면 그림과 같이 일자별 입고 수량의 누계가 계산됩니다.
입고 수량의 누계가 계산됩니다. - 항목별 최대값 구하기 : 이번에는 일자별 방문객 기록에서 그날까지 누적된 데이터 중 가장 높은 방문객 수를 확인해보겠습니다. 예제파일에서 H10셀을 선택하고 다음과 같이 SCAN 함수를 입력합니다.
=SCAN(0,G10:G22,MAX)
날짜별 방문객의 최대값을 구합니다. - 함수를 실행하면 다음과 같이 이전 날짜 기준 가장 높은 방문객 수가 계산됩니다.
이전 날짜 기준 가장 높은 값이 계산됩니다. - 예금 만기해지액 구하기 : 이번에는 1년마다 재예치하는 회전 예금의 년차별 만기 해지액을 계산해보겠습니다. L10셀에 다음과 같이 함수를 작성합니다.
=SCAN(L7,K10:K19,PRODUCT)
회차별 예금의 만기 해지액을 구합니다. - 함수를 실행하면 최초 납입액인 1,000,000원의 년차별 만기해지액이 한 번에 계산됩니다.
회차별 예금의 만기 해지액이 계산됩니다.
SCAN + LAMBDA 계산 예제
엑셀 SCAN 함수는 실무에서 꼭 필요로 하는 기본 계산 방식인 SUM, MAX, PRODUCT 등을 제공하지만, 실무에서는 좀 더 유연하고 복잡한 계산이 필요할 때가 있습니다. 그럴 때, LAMBDA 함수를 사용하면 SCAN 함수로 계산되는 수식을 자유롭게 커스터마이징해서 작성할 수 있습니다.
엑셀 LAMBDA 함수의 기초 사용법과 자세한 설명은 아래 LAMBDA 함수 사용법 강의를 참고하세요!👇
- SCAN + LAMBDA 기초 : 예제파일에서 SCAN + LAMBDA 시트로 이동합니다. 먼저 SCAN 함수의 수식으로 LAMBDA 가 동작하는 원리를 알아보겠습니다. SCAN 함수에서 LAMBDA는 2개의 인수를 사용하며, 각 단계의 초기값과 현재값으로 계산됩니다.
=SCAN(초기값,범위,LAMBDA(a,b,...))
// LAMBDA 함수의 첫번째 인수(a)는 초기값, 두번째 인수(b)는 현재값으로 계산됩니다. - D5셀을 선택하고 다음과 같이 SCAN 함수를 작성합니다.
=SCAN(0,C5:C13,LAMBDA(a,b,a+b))
누계를 구하는 SCAN + LAMBDA 공식을 작성합니다. - 함수를 실행하면 일자별 입고 수량의 합계가 계산됩니다. 이렇게 작성한 SCAN 함수는 수식으로 "SUM"을 입력한 것과 동일하게 동작합니다.
일자별 누계가 계산됩니다. - 병합된 셀 빈칸 채우기 : 이를 활용하면 셀병합 된 범위의 빈 칸을 채우는 기본 공식을 작성할 수 있습니다. [FILLDOWN] 시트로 이동한 후, E3셀에 다음과 같이 함수를 작성합니다.
=SCAN("",B3:B15,LAMBDA(a,b,IF(b="",a,b)))
// 현재 값을 출력하되 비어있는 경우 이전 값을 출력합니다.
병합된 셀의 빈 칸을 채우는 SCAN 함수를 작성합니다. - 함수를 실행하면 아래 그림과 같이 셀 병합된 범위에서 빈 칸이 채워진 범위가 반환됩니다.
병합된 셀의 빈 칸이 채워진 범위가 반환됩니다.
셀 병합 채우기, FILLDOWN 함수 만들기
엑셀로 작성된 보고서를 관리하다보면 셀 병합으로 인해 생긴 빈 칸으로 인해 종종 문제가 발생하곤 합니다. 이전에는 파워쿼리나 VBA를 써야 했지만, SCAN과 LAMBDA 함수를 활용하면 셀 병합된 범위의 빈 칸을 빠르게 채우는 사용자 함수를 직접 만들 수 있습니다.
- 셀 병합 채우기 공식 작성 : 예제파일의 [FILLDOWN] 시트에서 H2:K21 에 작성된 셀 병합된 범위를 빠르게 채워보겠습니다. 먼저 TOCOL 함수로 넓은 범위의 데이터를 세로로 긴 1열 데이터로 초기화 하겠습니다. M3셀을 선택하고, 아래 그림과 같이 TOCOL 함수를 작성합니다.
=TOCOL(H3:K20,0,TRUE)오빠두Tip : TOCOL 함수의 자세한 사용법은 아래 TOCOL 함수 기초 사용법 강의를 참고하세요!
- 함수를 실행하면 아래 그림과 같이 세로로 긴 1열 범위가 반환됩니다.
TOCOL 함수를 사용해 세로로 긴 1열 범위를 반환합니다. - 이제 1열로 정리된 데이터를 이전 단계에서 알아본 SCAN 함수를 활용하여 빈 칸을 채워보겠습니다. M3셀에 작성한 공식을 SCAN 함수로 묶어서 다음과 같이 수정합니다.
=SCAN("",TOCOL(H3:K20,0,TRUE),LAMBDA(a,b,IF(b="",a,b)))
세로로 반환된 범위의 빈 칸을 채웁니다. - 함수를 실행하면 세로로 긴 1열 범위의 빈 칸이 채워집니다.
- 마지막으로 WRAPCOLS 함수를 사용해서 범위를 열 기준으로 묶어줍니다. M3셀에 작성한 SCAN 함수를 WRAPPCOLS 함수로 묶어서 다음과 같이 작성합니다.
=WRAPCOLS(SCAN("",TOCOL(H3:K20,0,TRUE),LAMBDA(a,b,IF(b="",a,b))),18)
WRAPCOLS 함수로 세로로 긴 범위를 열 단위로 묶습니다. - 함수를 실행하면 아래 그림과 같이 병합된 셀의 빈 칸이 채워진 범위가 반환됩니다.
병합된 셀의 빈 칸이 채워진 범위가 반환됩니다. - FILLDOWN 함수 만들기 : 이제 마지막 단계 LAMBDA 함수를 사용해 커스텀 함수를 등록해보겠습니다. M3셀에 작성한 공식을 바탕으로 다음과 같이 LAMBDA 함수 공식을 완성합니다.
=LAMBDA(범위,WRAPCOLS(SCAN("",TOCOL(TRIMRANGE(범위),0,TRUE),LAMBDA(a,b,IF(b="",a,b))),ROWS(TRIMRANGE(범위))))오빠두Tip : TRIMRANGE 함수의 자세한 사용법은 아래 기초 영상 강의를 참고하세요!
- [수식] 탭 - [이름 관리자] - [새로 만들기] 를 클릭한 후, 이름과 설명, 범위를 다음과 같이 입력해서 함수를 등록합니다.
· 이름 : FILLDOWN
· 설명 : 병합된 셀의 빈 칸을 채운 범위를 반환합니다.
· 참조대상 : =LAMBDA(범위,WRAPCOLS(SCAN("",TOCOL(TRIMRANGE(범위),0,TRUE),LAMBDA(a,b,IF(b="",a,b))),ROWS(TRIMRANGE(범위))))
완성된 LAMBDA 함수를 이름관리자에 등록합니다. - [확인] 버튼을 클릭하면 FILLDOWN 함수가 등록됩니다.
FILLDOWN 함수가 등록되었습니다. - 이제 FILLDOWN 함수를 사용해서 그림과 같이 병합된 셀의 빈 칸을 빠르게 채울 수 있습니다.
FILLDOWN 함수로 병합된 셀의 빈 칸을 빠르게 채울 수 있습니다.
GROUPBY + SCAN 누계 보고서 만들기
GROUPBY 함수를 활용하면 피벗테이블을 사용하지 않고 함수만으로 실시간 월별 매출 및 누계가 집계되는 차트를 만들 수 있습니다. GROUPBY + SCAN 함수로 보고서와 차트를 만드는 과정은 아래 라이브 전체 강의를 참고하세요!👇
GROUPBY + SCAN 으로 실시간 누계 보고서를 만들 수 있습니다. - 누계 구하기 : 먼저 예제파일을 실행한 후, [SCAN] 함수 시트에서 기초 사용법을 알아보겠습니다. 첫번째로 왼쪽에 있는 표에서 각 날짜별 입고 수량의 누계를 계산하겠습니다. D10셀을 선택하고 아래와 같이 SCAN 함수를 입력합니다.
