들어가며
엑셀은 30년 넘게 같은 방식으로 쓰여 왔습니다. VLOOKUP을 외우고, 보조 열을 만들고, 수식을 아래로 드래그하고, 피벗테이블을 새로 고치는 일상이죠. 그런데 Microsoft 365 버전의 엑셀은 그동안 조용히, 그러나 근본적으로 바뀌었습니다.
핵심은 **동적 배열(dynamic array)**입니다. 하나의 수식이 여러 셀로 자동으로 "흘러넘치고(spill)", 함수가 배열을 통째로 반환하며, 보조 열 없이도 복잡한 변환이 한 줄로 끝납니다. 여기에 `LAMBDA`, `LET`, `GROUPBY`, 정규식 함수까지 더해지면서, 이제 엑셀은 가벼운 함수형 데이터 처리 도구에 가까워졌습니다.
이 글에서는 2026년 기준 Microsoft 365에서 사용할 수 있는 최신 함수들을 실무 예제 중심으로 정리합니다. 각 함수마다 실제 수식과 결과 설명을 함께 제시하고, 구버전 호환성 주의점과 대시보드/리포트 시나리오까지 다룹니다.
> 이 글의 함수들은 대부분 Microsoft 365 구독 버전과 웹 버전 엑셀에서 동작합니다. 영구 라이선스(Excel 2019/2021/2024)는 일부만 지원하므로, 마지막의 호환성 표를 꼭 확인하세요.
1. 동적 배열과 spill — 패러다임의 전환
spill이란 무엇인가
전통적인 엑셀에서 함수는 한 셀에 하나의 값만 반환했습니다. 동적 배열 엔진이 도입되면서, 하나의 수식이 여러 결과를 인접한 셀로 **자동 확장**합니다. 이를 spill이라고 부릅니다.
예를 들어 A2:A6에 숫자가 있을 때, 한 셀에 다음을 입력해 봅시다.
=A2:A6*2
예전이라면 단일 값만 나오거나 `Ctrl+Shift+Enter`로 배열 수식을 만들어야 했지만, 지금은 입력한 셀 아래로 5개의 결과가 자동으로 흘러넘칩니다. 결과 범위 전체를 **spill 범위**라 하고, 시작 셀이 C2라면 `C2#`(해시 연산자)로 전체 범위를 참조할 수 있습니다.
=SUM(C2#)
위 수식은 C2에서 시작한 spill 범위 전체의 합을 구합니다. spill 범위 크기가 바뀌어도 참조가 자동으로 따라갑니다.
#SPILL! 오류
spill이 펼쳐질 자리에 다른 값이 있으면 `#SPILL!` 오류가 납니다. 해결책은 단순합니다. 결과가 펼쳐질 셀들을 비워 주면 됩니다. 동적 배열을 쓸 때는 "수식 한 줄이 아래/오른쪽 공간을 차지한다"는 감각을 익히는 것이 중요합니다.
A B C(=A2:A6*2)
10 20 <- 여기서 시작
20 40
30 60
40 80
50 100
보조 열이 사라진다
동적 배열의 가장 큰 실무적 이점은 **보조 열 제거**입니다. 예전에는 "단가 × 수량" 열을 만들어 두고 합산했지만, 이제는 한 수식 안에서 배열 곱을 처리합니다.
=SUM(단가범위 * 수량범위)
수식이 깔끔해지고, 행이 추가/삭제돼도 보조 열을 유지·관리할 필요가 없습니다.
2. XLOOKUP — VLOOKUP의 완전한 후계자
`VLOOKUP`의 고질적 문제는 세 가지였습니다. 왼쪽으로 조회 불가, 열 번호 하드코딩, 기본값이 근사 일치라는 점입니다. `XLOOKUP`은 이를 모두 해결합니다.
=XLOOKUP(조회값, 조회범위, 반환범위, "없음", 0)
- 첫 인수: 찾을 값
- 둘째 인수: 찾을 범위(어느 방향이든 가능)
- 셋째 인수: 반환할 범위
- 넷째 인수: 못 찾았을 때 반환값(기본 오류 대신 사용자 지정)
- 다섯째 인수: 일치 모드(0 = 정확히 일치, 기본값)
예를 들어 사번으로 이름을 찾되, 사번 열이 이름 열의 오른쪽에 있어도 문제없습니다.
=XLOOKUP("E1042", 사번열, 이름열, "미등록")
반환 범위를 여러 열로 지정하면 한 번에 여러 값을 spill로 가져올 수도 있습니다.
=XLOOKUP("E1042", 사번열, 이름부서급여범위, "없음")
이렇게 하면 이름, 부서, 급여가 한 행으로 흘러넘칩니다. VLOOKUP을 세 번 쓰던 작업이 한 줄이 됩니다.
3. FILTER / SORT / UNIQUE / SEQUENCE — 데이터 가공 4총사
FILTER
조건에 맞는 행만 골라 spill로 반환합니다.
=FILTER(데이터범위, 부서열="개발", "결과 없음")
부서가 "개발"인 행 전체를 그대로 펼칩니다. 조건을 곱(AND)·합(OR)으로 조합할 수도 있습니다.
=FILTER(데이터범위, (부서열="개발")*(급여열>5000), "없음")
`*`는 AND, `+`는 OR로 동작합니다.
SORT / SORTBY
=SORT(FILTER(데이터범위, 부서열="개발"), 3, -1)
FILTER 결과를 세 번째 열 기준 내림차순(-1)으로 정렬합니다. 함수를 중첩해 "필터 후 정렬"을 한 줄로 끝냅니다. 다른 열을 기준으로 정렬하려면 `SORTBY`가 편합니다.
=SORTBY(데이터범위, 급여열, -1)
UNIQUE
중복을 제거합니다.
=UNIQUE(부서열)
부서 목록을 고유값만 추려 펼칩니다. 드롭다운 목록이나 집계 기준 축을 만들 때 매우 유용합니다.
SEQUENCE
연속 숫자 배열을 만듭니다.
=SEQUENCE(12, 1, 1, 1)
1부터 1씩 증가하는 12행 1열 배열(1~12월)을 생성합니다. 날짜 축이나 인덱스 생성에 자주 씁니다.
4. LET — 수식 안에 변수 선언하기
긴 수식은 같은 계산을 반복하기 마련입니다. `LET`은 수식 내부에 **이름 있는 변수**를 만들어 가독성과 성능을 동시에 높입니다.
=LET(
세전, 단가 * 수량,
세금, 세전 * 0.1,
세전 + 세금
)
`세전`과 `세금`을 한 번만 계산하고 재사용합니다. 마지막 인수가 최종 반환값입니다. 변수가 많아질수록 효과가 커지며, 복잡한 리포트 수식을 사람이 읽을 수 있는 형태로 만들어 줍니다.
5. LAMBDA와 그 친구들 — 엑셀 속 함수형 프로그래밍
LAMBDA: 나만의 함수 만들기
`LAMBDA`는 재사용 가능한 사용자 정의 함수를 수식만으로 만들게 해 줍니다. 이름 관리자에 등록하면 워크북 전역에서 호출할 수 있습니다.
=LAMBDA(섭씨, 섭씨 * 9 / 5 + 32)(25)
위는 즉석 호출 예시로, 25를 화씨로 변환해 77을 반환합니다. 이름 관리자에 `섭씨화씨`로 등록하면 다음처럼 함수처럼 씁니다.
=섭씨화씨(25)
MAP: 배열의 각 원소에 함수 적용
=MAP(점수범위, LAMBDA(x, IF(x>=60, "합격", "불합격")))
점수 배열의 각 값에 람다를 적용해 합격/불합격 배열을 반환합니다.
REDUCE: 누적 집계
=REDUCE(0, 금액범위, LAMBDA(누적, 값, 누적 + 값))
초기값 0에서 시작해 모든 값을 누적합니다. 누적합, 누적곱 등 임의의 집계 로직을 표현할 수 있습니다.
SCAN: 누적 과정을 모두 반환
`REDUCE`가 최종값만 주는 반면, `SCAN`은 매 단계의 누적 결과를 배열로 펼칩니다.
=SCAN(0, 매출범위, LAMBDA(누적, 값, 누적 + 값))
월별 매출의 누적 합계(YTD)를 한 줄로 만듭니다.
BYROW / BYCOL: 행·열 단위 집계
=BYROW(점수표범위, LAMBDA(행, AVERAGE(행)))
각 행의 평균을 세로 배열로 반환합니다. 학생별 평균, 항목별 합계 등에 적합합니다. 열 단위로는 `BYCOL`을 씁니다.
6. GROUPBY / PIVOTBY — 수식으로 만드는 피벗
피벗테이블의 가장 큰 불편은 데이터가 바뀔 때마다 **새로 고침**을 해야 한다는 점입니다. `GROUPBY`와 `PIVOTBY`는 수식 기반 집계라 원본이 바뀌면 결과가 자동 반영됩니다. refresh가 필요 없습니다.
GROUPBY: 그룹별 집계
=GROUPBY(부서열, 매출열, SUM, , 1)
- 첫 인수: 그룹 기준(행 필드)
- 둘째 인수: 집계할 값
- 셋째 인수: 집계 함수(SUM, AVERAGE, COUNT 등)
- 다섯째 인수: 헤더 표시 옵션
부서별 매출 합계를 자동으로 펼칩니다. 집계 함수 자리에 람다를 넣어 **커스텀 집계**도 가능합니다.
=GROUPBY(부서열, 매출열, LAMBDA(v, MAX(v) - MIN(v)))
부서별 매출의 최대-최소 폭(범위)을 직접 정의해 계산합니다.
PIVOTBY: 행·열 교차 집계
=PIVOTBY(부서열, 분기열, 매출열, SUM)
부서를 행으로, 분기를 열로 하는 교차표를 만듭니다. 전통 피벗테이블과 달리 셀 수식이므로, 원본 데이터가 갱신되면 즉시 반영됩니다. 대시보드의 동적 요약표를 만들 때 강력합니다.
7. 정규식 함수 — REGEXTEST / REGEXEXTRACT / REGEXREPLACE
엑셀에 정규식이 정식 함수로 들어왔습니다. 텍스트 검증·추출·치환이 훨씬 깔끔해집니다.
REGEXTEST: 패턴 일치 여부
=REGEXTEST(A2, "^[0-9]{3}-[0-9]{4}$")
A2가 "숫자 3개-숫자 4개" 패턴이면 TRUE를 반환합니다. 전화번호·우편번호 형식 검증에 적합합니다.
REGEXEXTRACT: 일치 부분 추출
=REGEXEXTRACT(A2, "[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+")
문자열에서 이메일 주소를 뽑아냅니다.
REGEXREPLACE: 패턴 치환
=REGEXREPLACE(A2, "[^0-9]", "")
숫자가 아닌 모든 문자를 제거해 순수 숫자만 남깁니다. 전화번호 정규화에 자주 씁니다.
8. TRIMRANGE와 새로운 trim 참조
`TRIMRANGE`는 범위 가장자리의 빈 행·열을 잘라냅니다. 표가 커졌다 작아졌다 할 때 "끝까지 잡되 빈 부분은 빼는" 동적 범위를 만들 수 있습니다.
=TRIMRANGE(A1:A100000)
100,000행을 참조해도 실제 값이 있는 부분까지만 반환하므로, 과도한 범위로 인한 성능 저하를 줄입니다.
9. 텍스트 분해 — TEXTSPLIT / TEXTBEFORE / TEXTAFTER
TEXTSPLIT
구분자로 텍스트를 나눠 spill합니다. "텍스트 나누기" 마법사를 수식으로 대체합니다.
=TEXTSPLIT("서울,부산,대구", ",")
세 도시가 가로로 펼쳐집니다. 행 구분자까지 지정하면 2차원으로도 나눌 수 있습니다.
TEXTBEFORE / TEXTAFTER
=TEXTBEFORE("user@example.com", "@")
`@` 앞의 "user"를 반환합니다. `TEXTAFTER`는 뒤쪽 "example.com"을 반환합니다. 파일명에서 확장자 분리, 경로에서 파일명 추출 등에 편리합니다.
10. 데이터 가져오기 — IMPORTTEXT / IMPORTCSV
웹 기반 엑셀과 최신 데스크톱에 `IMPORTTEXT`, `IMPORTCSV`가 추가되어, 외부 텍스트/CSV를 수식으로 직접 불러올 수 있습니다.
=IMPORTCSV("https://example.com/data/sales.csv")
지정한 CSV를 셀에 spill로 가져옵니다. 간단한 자동 갱신 데이터 소스로 쓸 수 있으나, 대규모·복잡한 변환은 다음 글에서 다룰 Power Query가 더 적합합니다.
11. 실무 시나리오 — 동적 대시보드 만들기
함수들을 조합해 "새로 고침이 필요 없는" 부서별 매출 대시보드를 만들어 봅시다.
1. 고유 부서 목록 추출
=UNIQUE(부서열)
2. 부서별 매출 합계와 평균을 한 번에
=GROUPBY(부서열, 매출열, HSTACK(SUM, AVERAGE))
3. 상위 3개 부서만 필터·정렬
=TAKE(SORT(GROUPBY(부서열, 매출열, SUM), 2, -1), 3)
`SORT`로 매출 내림차순 정렬 후 `TAKE`로 상위 3행만 가져옵니다.
4. 특정 분기 교차표
=PIVOTBY(부서열, 월열, 매출열, SUM)
이 모든 셀은 원본 데이터가 바뀌면 자동 갱신됩니다. 입력 시트에 행을 추가하기만 하면 대시보드가 살아 있는 것처럼 따라옵니다.
12. 함수 요약표
| 함수 | 역할 | 핵심 포인트 |
| --- | --- | --- |
| XLOOKUP | 조회 | 양방향, 못 찾을 때 기본값 지정 |
| FILTER | 조건 추출 | 다중 조건 AND/OR 결합 |
| SORT / SORTBY | 정렬 | 다른 열 기준 정렬 가능 |
| UNIQUE | 중복 제거 | 목록·축 생성 |
| SEQUENCE | 연속 배열 | 날짜·인덱스 생성 |
| LET | 변수 선언 | 가독성·성능 향상 |
| LAMBDA | 사용자 함수 | 이름 관리자 등록 후 재사용 |
| MAP / REDUCE / SCAN | 함수형 집계 | 원소별·누적 처리 |
| BYROW / BYCOL | 행·열 집계 | 방향별 요약 |
| GROUPBY / PIVOTBY | 수식 피벗 | 새로 고침 불필요, 커스텀 람다 |
| REGEXTEST / EXTRACT / REPLACE | 정규식 | 검증·추출·치환 |
| TRIMRANGE | 범위 트림 | 빈 가장자리 제거 |
| TEXTSPLIT / BEFORE / AFTER | 텍스트 분해 | 구분자 기반 분리 |
| IMPORTTEXT / IMPORTCSV | 가져오기 | 외부 데이터 직접 로드 |
13. 구버전 호환성 주의
| 기능 | M365 / 웹 | Excel 2024 | Excel 2021 | Excel 2019 이하 |
| --- | --- | --- | --- | --- |
| 동적 배열 / spill | 지원 | 지원 | 지원 | 미지원 |
| XLOOKUP | 지원 | 지원 | 지원 | 미지원 |
| LET / LAMBDA | 지원 | 일부 | 미지원 | 미지원 |
| GROUPBY / PIVOTBY | 지원 | 미지원 | 미지원 | 미지원 |
| 정규식 함수 | 지원 | 미지원 | 미지원 | 미지원 |
구버전 사용자와 파일을 공유할 때는 동적 배열 결과가 `@` 암시적 교차 연산자로 깨질 수 있습니다. 값으로 붙여넣기(값 복사)로 정적 결과를 남기거나, 호환 함수로 대체하는 것이 안전합니다.
마치며
엑셀의 신함수들은 단순히 "편한 단축키"가 아니라 **작업 방식 자체를 바꾸는 도구**입니다. 보조 열과 드래그를 줄이고, 한 줄의 선언적 수식으로 데이터를 가공하면, 실수가 줄고 유지보수가 쉬워집니다.
처음에는 `XLOOKUP`, `FILTER`, `UNIQUE`부터 익히고, 익숙해지면 `LET`과 `LAMBDA`로 넘어가세요. 마지막으로 `GROUPBY`/`PIVOTBY`까지 손에 익으면, "새로 고침 없는 리포트"라는 새로운 작업 흐름이 열립니다.
다음 글에서는 마우스를 떠나 키보드로 엑셀을 다루는 단축키 체계를, 그다음에는 Power Query로 데이터 정제를 자동화하는 방법을 다룹니다.
참고 자료
- [XLOOKUP 함수 (Microsoft 지원)](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)
- [FILTER 함수 (Microsoft 지원)](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)
- [LAMBDA 함수 (Microsoft 지원)](https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67)
- [LET 함수 (Microsoft 지원)](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)
- [GROUPBY 함수 (Microsoft 지원)](https://support.microsoft.com/en-us/office/groupby-function-d58dbc52-9c12-4ec9-bcb3-a37c2c038a9b)
- [PIVOTBY 함수 (Microsoft 지원)](https://support.microsoft.com/en-us/office/pivotby-function-3e90a3d5-2cb6-4b8c-bb8a-2f7286ca665b)
- [동적 배열 및 spill 동작 (Microsoft 지원)](https://support.microsoft.com/en-us/office/dynamic-array-formulas-and-spilled-array-behavior-205c6b06-03ba-4151-89a1-87a7eb36e531)
- [엑셀의 정규식 함수 (Microsoft Tech Community)](https://techcommunity.microsoft.com/t5/excel-blog/regular-expressions-regex-in-excel/ba-p/4149144)
- [Excel 함수 (사전순) (Microsoft 지원)](https://support.microsoft.com/en-us/office/excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188)
현재 단락 (1/141)
엑셀은 30년 넘게 같은 방식으로 쓰여 왔습니다. VLOOKUP을 외우고, 보조 열을 만들고, 수식을 아래로 드래그하고, 피벗테이블을 새로 고치는 일상이죠. 그런데 Microsof...