[Excel] 엑셀 동적 배열 & LAMBDA 심화 함수 완전 정복

동적 배열 & LAMBDA — FILTER·UNIQUE·SORT·SEQUENCE·LET·LAMBDA 완전 정복

 

📋 목차 1. 동적 배열이란? — 수식 하나가 여러 셀을 채운다 2. FILTER — 조건에 맞는 행만 자동 추출 3. UNIQUE — 중복 없는 고유값 목록 자동 생성 4. SORT & SORTBY — 동적 정렬 5. SEQUENCE — 번호·날짜 자동 생성 6. LET — 수식 안에 변수 선언하기 7. LAMBDA — 나만의 함수 만들기 8. 함수 조합 실전 패턴 — 강력한 자동화

 

Excel 365(Microsoft 365)에서 추가된 동적 배열 함수들은 엑셀의 패러다임을 바꿨다. 기존에는 결과가 항상 하나의 셀에 담겼지만, 이제는 수식 하나가 자동으로 여러 셀에 결과를 쏟아낸다.   ⚠️ 주의: 이 편의 함수들은 Excel 365(Microsoft 365) 또는 Excel 2021에서만 사용 가능하다. Excel 2019 이하에서는 지원하지 않는다.

 

1	동적 배열이란? — 수식 하나가 여러 셀을 채운다

 

기존 엑셀에서 수식 하나는 결과 하나만 반환했다. 동적 배열 함수는 결과가 여러 개일 때 자동으로 인접 셀에 퍼져나간다. 이를 '스필(Spill)'이라고 한다.

 

구분	기존 방식	동적 배열
결과 범위	수식을 넣은 셀 1개	결과 수만큼 자동 확장
복사 필요	수식을 모든 셀에 복사	수식 하나로 끝
데이터 추가	수식 범위 수동 확장	자동으로 범위 확장
참조 방법	각 셀 개별 참조	범위 전체를 # 기호로 참조

 

▸ 스필 범위 참조

=FILTER(A:C, B:B="영업팀")  → B2:B100에 결과가 자동으로 퍼짐   스필 범위 전체 참조: =SUM(B2#)   → B2 수식의 스필 범위 전체 합계 =COUNTA(A2#) → A2 수식의 스필 범위 행 수   스필 방해: 인접 셀에 데이터가 있으면 #SPILL! 오류 발생 → 인접 셀을 비워야 함

 

💡 Excel 버전 확인 방법 =FILTER(1,1) 입력 후 결과가 1이 나오면 동적 배열 지원 버전이다. #NAME? 오류가 나오면 구버전이므로 이 편의 함수를 쓸 수 없다.

 

 

2	FILTER — 조건에 맞는 행만 자동 추출

 

FILTER는 조건에 맞는 행을 자동으로 추출해서 결과를 스필한다. 고급 필터나 VBA 없이 수식만으로 동적 필터링이 가능하다.

 

=FILTER(배열, 조건, [빈값처리])   기본: =FILTER(A2:D100, B2:B100="영업팀") → B열이 '영업팀'인 행 전체 추출   여러 조건 (AND): =FILTER(A2:D100, (B2:B100="영업팀")*(C2:C100="서울")) → 영업팀 AND 서울인 행   여러 조건 (OR): =FILTER(A2:D100, (B2:B100="영업팀")+(B2:B100="마케팅팀")) → 영업팀 OR 마케팅팀인 행   못 찾을 때 처리: =FILTER(A2:D100, B2:B100="없는부서", "해당 없음") → 조건 미충족 시 '해당 없음' 표시

 

▸ FILTER 실전 패턴

상위 N개 추출 (SORT+FILTER 조합): =FILTER(A2:D100, D2:D100>=LARGE(D2:D100,5)) → D열 상위 5개 행 추출   날짜 범위 필터: =FILTER(A2:D100, (C2:C100>=DATE(2025,1,1))*(C2:C100<=DATE(2025,6,30))) → 2025년 1~6월 데이터만 추출   텍스트 포함 필터: =FILTER(A2:D100, ISNUMBER(SEARCH("김",A2:A100))) → 이름에 '김'이 포함된 행 추출

 

 

 

3	UNIQUE — 중복 없는 고유값 목록

 

UNIQUE는 범위에서 중복을 제거한 고유값 목록을 자동으로 반환한다. 드롭다운 목록 만들기, 고유 항목 집계에 특히 유용하다.

 

=UNIQUE(배열, [열기준], [정확히1번만])   기본 (행 기준 고유값): =UNIQUE(A2:A100)   → A열의 중복 제거 목록   여러 열 기준: =UNIQUE(A2:B100)   → A+B 조합이 유일한 행만   정확히 1번만 등장하는 값: =UNIQUE(A2:A100, FALSE, TRUE) → 2회 이상 등장한 항목 제외   정렬된 고유값 (UNIQUE+SORT): =SORT(UNIQUE(A2:A100)) → 중복 제거 + 오름차순 정렬

 

▸ 드롭다운 목록 자동화

① UNIQUE로 고유 목록 추출:    E2 셀: =SORT(UNIQUE(B2:B100))   → 부서 목록 자동 생성   ② 데이터 유효성 검사:    F열 → 데이터 유효성 → 원본: =E2#    → 새 부서가 추가되면 드롭다운도 자동 갱신!

 

💡 UNIQUE의 강력함 기존에는 중복 제거한 목록을 만들려면 데이터 탭의 '중복 제거' 기능을 수동으로 실행해야 했다. UNIQUE는 원본 데이터가 바뀌면 목록도 자동으로 갱신된다.

 

 

4	SORT & SORTBY — 동적 정렬

 

SORT와 SORTBY는 원본을 건드리지 않고 정렬된 결과를 별도 위치에 자동으로 출력한다.

 

▸ SORT — 기본 정렬

=SORT(배열, [정렬열], [정렬순서], [열기준])   정렬순서: 1=오름차순(기본), -1=내림차순   예: =SORT(A2:D100)           → A열 기준 오름차순 =SORT(A2:D100, 4)        → 4번째 열(D열) 기준 오름차순 =SORT(A2:D100, 4, -1)    → D열 기준 내림차순 (매출 높은 순) =SORT(A2:D100, {2,4}, {1,-1})  → 2열 오름차순, 같으면 4열 내림차순

 

▸ SORTBY — 외부 배열 기준 정렬

=SORTBY(배열, 기준배열1, [정렬순서1], [기준배열2], ...)   예: 이름(A열)을 매출(D열) 기준으로 정렬 =SORTBY(A2:A100, D2:D100, -1)   → 매출 내림차순으로 이름 정렬   여러 기준: =SORTBY(A2:D100, B2:B100, 1, D2:D100, -1) → 부서 오름차순, 같으면 매출 내림차순

 

 

5	SEQUENCE — 번호·날짜 자동 생성

 

SEQUENCE는 연속된 숫자 배열을 자동으로 생성한다. 번호 매기기, 날짜 목록 생성, 달력 만들기에 활용된다.

 

=SEQUENCE(행, [열], [시작], [증가])   기본: =SEQUENCE(10)            → 1~10 세로 배열 =SEQUENCE(1, 12)         → 1~12 가로 배열 =SEQUENCE(5, 4)          → 5행 4열의 1~20 배열 =SEQUENCE(10, 1, 0, 10)  → 0, 10, 20, ... 90   날짜 생성: =SEQUENCE(12, 1, DATE(2025,1,1), 30) → 2025-01-01부터 30일 간격으로 12개 날짜   월의 첫째 날 목록: =DATE(2025, SEQUENCE(12), 1) → 2025년 1~12월의 1일 목록   번호 자동 부여 (데이터 추가 시 자동 확장): =SEQUENCE(COUNTA(A2:A100))

 

 

 

6	LET — 수식 안에 변수 선언하기

 

LET은 수식 내부에 이름(변수)을 정의할 수 있다. 같은 계산이 수식 안에서 여러 번 반복될 때 LET으로 한 번만 계산하고 이름으로 재사용할 수 있다.

 

=LET(이름1, 값1, 이름2, 값2, ..., 반환식)   예: TRIM+CLEAN을 여러 번 쓰는 경우 기존: =VLOOKUP(TRIM(CLEAN(A1)), TRIM(CLEAN(B:B)), 2, 0)   LET 사용: =LET(   검색값, TRIM(CLEAN(A1)),   범위,   TRIM(CLEAN(B:B)),   VLOOKUP(검색값, 범위, 2, 0) )   예: 복잡한 계산 분해 =LET(   총합,    SUM(B2:B100),   목표,    C1,   달성률,  총합/목표,   IF(달성률>=1, "달성", TEXT(달성률,"0%")) )

 

LET 장점	설명
가독성 향상	수식을 이름 붙인 단계로 분해해서 읽기 쉬움
성능 향상	같은 계산을 1번만 수행하므로 파일 속도 향상
디버깅 용이	각 이름의 값을 단계별로 확인 가능
재사용	같은 계산 결과를 여러 번 참조 가능

 

💡 LET 활용 원칙 같은 계산식이 수식에서 두 번 이상 반복된다면 LET으로 빼내자. 수식이 훨씬 짧아지고 계산 속도도 빨라진다.

 

 

7	LAMBDA — 나만의 함수 만들기

 

LAMBDA는 엑셀에서 직접 사용자 정의 함수를 만들 수 있는 혁신적인 기능이다. VBA 없이도 수식만으로 나만의 함수를 정의하고, 이름 관리자에 등록해 어디서든 호출할 수 있다.

 

=LAMBDA(매개변수1, [매개변수2], ..., 반환식)   즉시 실행: =LAMBDA(x, x*x)(5)    → 25 (5의 제곱)   이름 등록 (수식→이름 관리자): 이름: 부가세포함 참조 대상: =LAMBDA(금액, 금액*1.1) → 어디서든 =부가세포함(A1) 형태로 사용 가능!   여러 인수: 이름: 달성률계산 참조 대상: =LAMBDA(실적, 목표, IF(목표=0,0,실적/목표)) → =달성률계산(B2, C2) 형태로 호출

 

▸ LAMBDA 실전 예시

예1: 한국식 나이 계산 이름: 한국나이 참조 대상: =LAMBDA(생년, YEAR(TODAY())-YEAR(생년)+1) 사용: =한국나이(A2)   → A2 생년월일로 한국식 나이 반환   예2: 단위 변환 이름: 평방미터to평 참조 대상: =LAMBDA(m2, ROUND(m2/3.3058,1)) 사용: =평방미터to평(B2)   → 예) 99m²→30평   예3: 요금 계산 이름: 택배요금 참조 대상: =LAMBDA(무게, IF(무게<=1,3000,IF(무게<=3,4000,5000))) 사용: =택배요금(C2)

 

▸ 재귀 LAMBDA (LAMBDA 안에서 자기 자신 호출)

=LET(   팩토리얼,   LAMBDA(n, 계산,     IF(n<=1, 1, n * 계산(n-1, 계산))   ),   팩토리얼(5, 팩토리얼) ) → 5! = 120

 

 

 

8	함수 조합 실전 패턴

 

동적 배열 함수들은 조합할 때 진짜 위력이 발휘된다. 아래 패턴들을 익히면 복잡한 업무를 수식 한 줄로 해결할 수 있다.

 

▸ 패턴 1 — 조건 필터 + 정렬 + 상위 N개

=TAKE(SORT(FILTER(A2:D100, B2:B100="영업팀"), 4, -1), 5) → 영업팀 중 매출 상위 5명 추출   (TAKE: 배열의 앞 N행 반환, 365 전용)

 

▸ 패턴 2 — 동적 드롭다운 연동 집계

E1: 드롭다운 선택 (=SORT(UNIQUE(B2:B100)) 기반) E2: =SUMIF(B2:B100, E1, D2:D100) → 드롭다운 선택 즉시 합계 자동 변경

 

▸ 패턴 3 — FILTER + UNIQUE 조합

특정 부서의 고유 고객사 목록: =UNIQUE(FILTER(C2:C100, B2:B100="영업팀")) → 영업팀 담당 고객사 중 중복 없는 목록

 

▸ 패턴 4 — SEQUENCE + 날짜 달력

=DATE(2025, 1, SEQUENCE(31)) → 2025년 1월 1일~31일 자동 생성   주차별 날짜: =DATE(2025,1,1) + SEQUENCE(52,7,0) → 2025년 52주의 월요일 날짜 자동 생성

 

함수 조합	수식 예	결과
FILTER+SORT	=SORT(FILTER(A:D,B:B="팀"),4,-1)	팀 필터 후 매출 내림차순
UNIQUE+SORT	=SORT(UNIQUE(B2:B100))	중복 제거 + 정렬 목록
FILTER+UNIQUE	=UNIQUE(FILTER(C:C,B:B="팀"))	특정 팀의 고유 항목
SEQUENCE+DATE	=DATE(2025,SEQUENCE(12),1)	월별 첫째 날 목록
LET+FILTER	=LET(팀,"영업",FILTER(A:D,B:B=팀))	변수 활용 필터
LAMBDA+MAP	=MAP(A2:A100, LAMBDA(x, 부가세포함(x)))	범위 전체에 LAMBDA 적용