엑셀 동적 배열 함수 총정리 – 실무 자동화를 위한 최신 함수 가이드
엑셀은 단순한 계산 프로그램을 넘어, 보고서 자동화와 데이터 분석에 필수적인 도구로 자리잡았습니다. 특히 최근 업데이트된 동적 배열 함수(Dynamic Array Functions)는 기존의 고정된 수식 방식과 달리, 한 번의 수식 입력으로 여러 셀에 결과를 자동으로 확장(Spill)해주는 혁신적인 기능을 제공합니다. 오늘은 UNIQUE, FILTER, SORT, SORTBY, SEQUENCE, RANDARRAY, XLOOKUP, XMATCH 등 최신 동적 배열 함수를 총정리하여 실무 자동화에 어떻게 활용할 수 있는지 안내드립니다.
1) 동적 배열 함수란 무엇인가?
기존의 배열 수식은 Ctrl + Shift + Enter 로 입력해야 했고, 결과가 특정 셀 범위에만 제한적으로 출력되었습니다. 반면 동적 배열 함수는 하나의 셀에 수식을 입력하면 결과가 자동으로 주변 셀로 확장되어 표시됩니다. 이를 Spill 기능이라고 부릅니다. 즉, 데이터의 크기가 변하더라도 수식이 자동으로 조정되어, 보고서 자동화 및 대시보드 설계에서 큰 장점을 가집니다.
2) UNIQUE – 고유값 추출
UNIQUE 함수는 지정한 범위에서 중복을 제거하고 고유값만 추출합니다. 예를 들어 고객명단에서 중복된 이름을 제거하고, 자동으로 최신 명단을 관리할 때 활용할 수 있습니다.
=UNIQUE(A2:A100)보고서 자동화에서 가장 많이 쓰이는 기본 함수 중 하나입니다.
3) FILTER – 조건부 데이터 추출
FILTER는 특정 조건에 맞는 행만 걸러내어 반환합니다. 예를 들어 10만 원 이상 구매 고객만 추출하려면:
=FILTER(A2:B100, B2:B100>=100000)실무에서는 매출 보고서, 부서별 인원 현황 등 조건 기반 데이터 추출에 매우 유용합니다.
4) SORT, SORTBY – 정렬 자동화
SORT는 단순 정렬, SORTBY는 다른 열을 기준으로 정렬할 때 사용합니다.
=SORT(A2:A100) =SORTBY(A2:A100, B2:B100, -1)예를 들어 고객명단을 매출 기준으로 내림차순 정렬할 수 있습니다. 데이터가 추가될 때마다 자동 반영된다는 점이 큰 장점입니다.
5) SEQUENCE – 자동 번호 생성
SEQUENCE는 일정한 숫자 배열을 자동으로 생성합니다. 예를 들어 1부터 12까지 월 번호를 자동 생성하려면:
=SEQUENCE(12)이를 INDEX, MATCH, XLOOKUP 등과 결합하면 동적 참조가 가능합니다.
6) RANDARRAY – 무작위 데이터 생성
RANDARRAY는 난수를 생성합니다. 행 개수, 열 개수, 최소값, 최대값, 정수 여부를 지정할 수 있어 샘플 데이터를 빠르게 만들 때 유용합니다.
=RANDARRAY(10,1,1,100,TRUE)→ 1~100 사이의 랜덤 정수 10개 생성
7) XLOOKUP & XMATCH – 새로운 검색 함수
XLOOKUP은 VLOOKUP과 INDEX+MATCH를 대체하는 강력한 함수입니다. 왼쪽 검색이 가능하며, 기본값 처리 기능이 내장되어 있습니다.
=XLOOKUP("서울", 지역리스트, 매출리스트, "없음")XMATCH는 MATCH의 강화 버전으로, 검색 모드와 와일드카드 검색이 더 유연합니다.
8) 함수 조합으로 만드는 자동화 파이프라인
동적 배열 함수는 단독으로도 강력하지만, 조합했을 때 진가를 발휘합니다.
=SORTBY(UNIQUE(FILTER(A2:A100, B2:B100>100000)), UNIQUE(FILTER(A2:A100, B2:B100>100000)), 1)위 공식은 "10만 원 이상 구매 고객"을 조건으로, 중복 제거 + 고유값 추출 + 정렬까지 한 번에 처리합니다. 보고서 자동화 파이프라인 구축에 매우 적합합니다.
9) 실무 활용 사례: 보고서 & 대시보드 자동화
제가 경험한 사례 중 하나는 부서별 프로젝트 담당자 자동 리스트입니다. 부서명이 바뀌거나 새로운 인원이 추가되면, UNIQUE + FILTER + SORTBY 조합으로 담당자 명단이 자동 업데이트되고, 이를 차트와 연결해 대시보드가 실시간으로 갱신되었습니다.
또 다른 사례는 월별 매출 비교입니다. SEQUENCE로 월 번호를 자동 생성하고, XLOOKUP으로 전월 데이터를 참조해 증감률을 계산했습니다. 이 과정이 자동화되면서 팀 리더가 원하는 보고서를 매월 단 1분 만에 뽑을 수 있었습니다.
10) 파워쿼리와 동적 배열 함수 비교
파워쿼리는 대규모 데이터 전처리와 변환에 강력하지만, "실시간 반영"이 필요할 때는 동적 배열 함수가 더 적합합니다. 예를 들어 5만 건 이상 대량 데이터에는 파워쿼리가 안정적이지만, 수천 건 수준에서는 동적 배열 함수가 훨씬 가볍고 빠릅니다.
11) 자주 묻는 질문(FAQ)
Q1. 모든 엑셀 버전에서 동적 배열 함수를 쓸 수 있나요?
아닙니다. Office 365 및 최신 버전(Excel 2019 이후)에서 지원합니다.
Q2. 기존 배열 수식과의 가장 큰 차이는 무엇인가요?
별도의 Ctrl+Shift+Enter 입력이 필요 없고, 결과가 자동 확장(Spill)된다는 점입니다.
Q3. 파워쿼리와 병행해서 써도 되나요?
네, 가능합니다. 파워쿼리로 전처리 후 동적 배열 함수로 실시간 자동화하는 방식이 가장 효율적입니다.