엑셀 LAMBDA 함수와 동적 배열로 재사용 수식 만들기

LET은 수식 하나 안에서 값에 이름을 붙여 줘요. LAMBDA는 한 걸음 더 나아가요. 입력을 받는 계산 전체에 이름을 붙이고, 통합 문서 어디에서나 기본 제공 함수처럼 호출할 수 있게 해 줘요. 여기에 MAP, SCAN, REDUCE, BYROW, BYCOL을 함께 쓰면 한곳에 한 번 쓴 규칙이 열 전체를 처리해요. 수식을 500행 아래로 복사해 놓고 347행만 누가 손으로 고치지 않았기를 바랄 필요가 없어져요.

예제는 A2:A7에 있는 거래 금액 여섯 개를 써요. 180, 920, 45, 610, 300, 75예요.

LET에서 LAMBDA로

LAMBDA에는 하나 이상의 매개 변수(입력을 대신하는 자리 표시자 이름)가 있고, 그 뒤에 매개 변수를 쓰는 계산이 와요. 마지막 인수는 언제나 계산이에요.

x가 매개 변수이고 IF(…)가 계산이에요

=LAMBDA(x, IF(x>=500,"고액","소액"))

이걸 셀에 그대로 입력하면 엑셀은 #CALC!를 돌려줘요. LAMBDA 자체는 결과가 아니라 레시피라서 x에 넣을 값이 필요하거든요. 테스트하려면 바로 뒤에 괄호를 붙이고 그 안에 값을 넣으세요.

=LAMBDA(x, IF(x>=500,"고액","소액"))(A3)

A3에는 920이 있으니 결과는 고액이에요. (A3)을 (A2)로 바꾸면 180은 500보다 작으니 소액이 나와요.

LET으로 값에 이름을 붙이듯이, 수식 하나 안에서 LAMBDA에 이름을 붙일 수도 있어요. 여기서는 classify에 LAMBDA를 담고 마지막 줄에서 호출해요.

=LET(classify, LAMBDA(x, IF(x>=500,"고액","소액")), classify(A5))

A5는 610이니 결과는 고액이에요. 다만 LET 이름은 그 수식 안에서만 존재해요. 통합 문서 전체에서 규칙을 다시 쓰려면 이름 관리자(Name Manager)에 저장하세요.

LAMBDA를 이름 있는 함수로 저장하기

새 이름 대화 상자를 여세요
수식 then 정의된 이름 then 이름 정의를 선택하세요. 수식 then 정의된 이름 then 이름 관리자를 열고 새로 만들기를 클릭해도 돼요.
함수 이름을 정하고 LAMBDA를 붙여 넣으세요
이름에 Classify를 입력하세요. 범위는 통합 문서로 두세요. 설명에는 "500 이상이면 고액, 아니면 소액을 돌려줌"처럼 무슨 일을 하는지 적으세요. 참조 대상에는 뒤에 값을 붙이지 않은 LAMBDA를 입력하세요.
```
=LAMBDA(x, IF(x>=500,"고액","소액"))
```
확인을 클릭하세요.
다른 함수처럼 호출하세요
아무 셀에나 이렇게 입력하세요.
=Classify(A3)
결과는 고액이에요. 입력하는 동안 수식 자동 완성(Formula AutoComplete) 목록에 Classify가 기본 제공 함수들과 함께 나타나요.

진짜 이점은 나중에 드러나요. 기준이 600으로 바뀌면 이름 관리자에서 정의를 한 번만 고치면 되고, Classify를 호출하는 모든 수식이 함께 업데이트돼요. 아래로 복사해 둔 수식을 하나하나 찾아다닐 필요가 없어요.

매개 변수를 두 개 이상 쓰기

정의 안에 직접 입력한 기준값은 여전히 숨은 가정이에요. 대신 두 번째 매개 변수로 만들고, 함수 이름을 ClassifyBy로 정하세요.

=LAMBDA(amount, limit, IF(amount>=limit,"고액","소액"))

이제 =ClassifyBy(A3, 1000)은 소액을 돌려주고, 기준은 호출하는 쪽이 정해요. 이름 있는 입력 셀에서 가져오면 가장 좋아요. 매개 변수는 위치로 짝이 맞춰지니 인수도 매개 변수와 같은 순서로 넣으세요. 인수 개수가 틀리면 엑셀은 #VALUE!를 돌려줘요.

MAP으로 모든 행에 LAMBDA 적용하기

MAP은 배열의 값마다 LAMBDA를 한 번씩 실행하고, 결과를 같은 모양으로 분산해요. B2를 클릭하고 입력하세요.

=MAP(A2:A7, Classify)

결과 여섯 개가 B2:B7로 분산돼요. 이름 있는 함수를 바로 넘겨도 되고, =MAP(A2:A7, LAMBDA(x, IF(x>=500,"고액","소액")))처럼 LAMBDA를 수식 안에 직접 써도 결과는 같아요.

	A	B	C
1	금액	분류	누적 합계
2	180	소액	180
3	920	고액	1,100
4	45	소액	1,145
5	610	고액	1,755
6	300	소액	2,055
7	75	소액	2,130

B2의 수식 하나가 금액 여섯 개를 모두 분류해요. 고액은 920과 610뿐이에요.

수식은 B2에만 있어요. 나머지 셀은 분산된 값을 보여 줄 뿐이라, 누군가 5행만 다른 규칙으로 조용히 덮어쓸 수 없어요. B3:B7에 이미 무언가 입력되어 있으면 그 셀을 비울 때까지 수식에 #SPILL!이 나와요.

크기가 같은 배열을 여러 개 넘기면, MAP은 배열마다 값을 하나씩 꺼내 같은 개수의 매개 변수를 가진 LAMBDA에 넘겨요. 예를 들어 D2:D7에 거래별 세율이 있다면 =MAP(A2:A7, D2:D7, LAMBDA(amt, rate, amt*rate))는 금액마다 같은 행의 세율을 곱해요.

#으로 분산 범위를 바탕으로 수식 만들기

분산된 결과 전체는 첫 셀 뒤에 #을 붙여 참조해요. B2#은 "B2가 분산된 범위 전체"라는 뜻이라, 이걸 쓰는 수식은 데이터에 따라 함께 늘고 줄어요.

=COUNTIF(B2#,"고액")

결과는 2예요. 분산 범위를 바탕으로 하는 패턴을 몇 가지 더 볼게요.

수식	결과
`=FILTER(A2:A7, B2#="고액")`	고액 거래 금액인 920과 610
`=HSTACK(A2:A7, B2#)`	금액 옆에 분류가 붙은 두 열짜리 배열
`=ROWS(B2#)`	분류한 행 수인 6

원본이 엑셀 표라면 A2:A7 대신 Sales[Amount] 같은 구조적 참조를 쓰고, MAP 수식은 표 바깥에 두세요(분산되는 수식은 표 안에서 동작하지 않아요). 그러면 표에 추가한 행을 MAP이 알아서 포함하고, B2#도 자동으로 따라와요.

SCAN으로 누적 합계, REDUCE로 최종 합계

SCAN과 REDUCE는 둘 다 배열을 값 하나씩 차례로 훑으면서, 단계에서 단계로 누적값을 넘겨요. 둘 다 시작값, 배열, 그리고 매개 변수가 두 개인 LAMBDA를 받아요. 매개 변수는 지금까지의 누적값(acc)과 현재 값(v)이고, LAMBDA는 새 누적값을 돌려줘요.

차이는 무엇을 남기느냐예요.

SCAN은 단계마다 누적값을 남겨서 모두 분산해요. 값 여섯 개가 들어가면 여섯 개가 나와요.
REDUCE는 마지막 누적값만 남겨요. 값 여섯 개가 들어가면 하나가 나와요.

C2에 입력하세요.

=SCAN(0, A2:A7, LAMBDA(acc, v, acc+v))

누적 합계 180, 1,100, 1,145, 1,755, 2,055, 2,130이 분산돼요. 각 값은 바로 앞 합계에 현재 금액을 더한 거예요.

	A	B	C
1	금액	분류	누적 합계
2	180	소액	180
3	920	고액	1,100
4	45	소액	1,145
5	610	고액	1,755
6	300	소액	2,055
7	75	소액	2,130

SCAN은 누적 합계의 모든 단계를 남겨요. 마지막 값 2,130이 REDUCE가 돌려줄 값이에요.

SCAN을 REDUCE로 바꾸면 최종 합계만 나와요.

=REDUCE(0, A2:A7, LAMBDA(acc, v, acc+v))

결과는 2,130이에요. 물론 단순 덧셈이라면 SUM이 더 쉬워요. REDUCE가 제값을 하는 건 단계마다 조건을 따져야 할 때예요. 아래 수식은 현재 값이 조건에 맞을 때만 1을 더해서 500 이상인 금액의 개수를 세요.

=REDUCE(0, A2:A7, LAMBDA(acc, v, IF(v>=500, acc+1, acc)))

결과는 2예요.

행 단위, 열 단위로 계산하기

MAP은 값을 하나씩 봐요. BYROW와 BYCOL은 표의 행 하나 또는 열 하나를 통째로 LAMBDA에 넘기고, LAMBDA는 그걸 값 하나로 줄여야 해요.

	A	B	C	D	E	F	G
1	지역	Q1	Q2	Q3	Q4	합계	최고 분기
2	East	120	135	128	150	533	Q4
3	West	98	110	125	117	450	Q3
4	Central	143	121	131	139	534	Q1
5	합계	361	366	384	406

지역별 분기 매출이에요. 테두리를 두른 셀은 분산 수식 세 개의 결과예요. F열은 합계, G열은 최고 분기, 5행은 열 합계예요.

F2의 행 합계: =BYROW(B2:E4, LAMBDA(region, SUM(region)))는 533, 450, 534를 분산해요.
B5의 열 합계: =BYCOL(B2:E4, LAMBDA(quarter, SUM(quarter)))는 361, 366, 384, 406을 가로로 분산해요.
G2의 최고 분기:

=BYROW(B2:E4, LAMBDA(region, XLOOKUP(MAX(region), region, B1:E1)))

지역마다 MAX가 가장 높은 분기 값을 찾고, XLOOKUP이 B1:E1에서 그 머리글을 돌려줘요. East는 Q4(150), West는 Q3(125), Central은 Q1(143)이에요.

격자를 처음부터 만들고 싶다면 MAKEARRAY를 쓰세요. 행 번호와 열 번호를 LAMBDA에 넘겨 줘요. =MAKEARRAY(3, 4, LAMBDA(r, c, r*c))는 3행 4열짜리 곱셈표를 돌려줘요.

믿고 쓰기 전에 LAMBDA 테스트하기

이름 있는 함수는 여러 곳에서 쓰이니, 함수에 실수가 있으면 모든 곳으로 퍼져요. 함수를 배포하기 전에 테스트하듯이 테스트하세요. 빈 연습용 셀에서, 답을 이미 아는 경우와 경계 사례를 넣어 보는 거예요.

테스트	기대하는 결과	Classify의 결과
`=Classify(499)`	소액	소액
`=Classify(500)`	고액(500은 고액에 포함)	고액
`=Classify("N/A")`	오류나 경고	고액

마지막 테스트가 실패해요. 엑셀 비교에서는 텍스트가 어떤 숫자보다도 크다고 보기 때문에 "N/A">=500이 TRUE가 되거든요. 원본 데이터에 텍스트가 섞일 수 있다면 정의에서 막아 두세요.

=LAMBDA(x, IF(ISNUMBER(x), IF(x>=500,"고액","소액"), "확인"))

이제 =Classify("N/A")는 확인을 돌려주고, 나머지 두 테스트도 그대로 통과해요. 테스트 셀은 QA 시트 같은 곳에 남겨 두고, 정의를 고칠 때마다 다시 확인하세요. 예전에 아래로 복사해 쓰던 열을 바꾸기 전에는 새 MAP 결과를 옆에 두고 모든 행이 일치하는지 확인하세요.

자주 하는 실수

호출하지 않은 LAMBDA. 셀에 =LAMBDA(x, x*2)만 쓰면 #CALC!가 나와요. =LAMBDA(x, x*2)(5)처럼 값을 넣어 호출하거나, 이름을 붙이고 그 이름을 호출하세요.
틀린 인수 개수. 매개 변수가 두 개인 LAMBDA를 인수 하나로 호출하면 #VALUE!가 나와요.
셀 주소처럼 보이는 매개 변수 이름. A1이나 x1 같은 이름은 매개 변수로 쓸 수 없어요. amount, acc, region처럼 평범한 단어를 쓰세요.
값을 여러 개 돌려주는 BYROW, BYCOL용 LAMBDA. LAMBDA(region, region*2)는 행마다 값을 네 개 돌려주기 때문에 BYROW가 #CALC!를 표시해요. SUM, MAX, XLOOKUP 등으로 행마다 값 하나로 줄이세요.
분산 범위 대신 고정 범위 쓰기. =COUNTIF(B2:B7,"고액")은 데이터가 늘어도 7행에서 멈춰요. B2#을 쓰세요.
이름 있는 함수에 숨은 기준값. 이름 관리자의 정의 안에 묻힌 값은 셀 수식 안의 값보다도 찾기 어려워요. 매개 변수로 넘기거나, 이름의 설명에 적어 두세요.

연습

아래로 복사한 수식을 이름 있는 LAMBDA로 바꾸기

A2:A7에 금액 180, 920, 45, 610, 300, 75를 입력하세요.
빈 셀에서 =LAMBDA(x, IF(x>=500,"고액","소액"))(A3)을 테스트하세요. 고액이 나와야 해요.
이 LAMBDA를 설명과 함께 이름 관리자에 Classify로 저장하세요.
B2에 =MAP(A2:A7, Classify)를 입력하고, 920과 610만 고액인지 확인하세요.
C2에 SCAN 누적 합계를 입력하세요. 마지막 값이 2,130이어야 해요.
=COUNTIF(B2#,"고액")으로 고액 결과를 세어 보세요. 그다음 A8에 일곱 번째 금액을 추가하고, B2와 C2의 A2:A7을 A2:A8로 바꾼 뒤 개수와 합계가 업데이트되는지 보세요.
=Classify("N/A")를 테스트한 다음, ISNUMBER로 막는 부분을 추가하고 다시 테스트하세요.

그다음 내 업무에 적용해 보세요. 열 아래로 복사해 쓰는 계산(배송 등급, 성적 구간, 연체료 규칙 등)을 하나 찾아 이름 있는 LAMBDA로 한 번만 쓰고, MAP으로 적용한 다음 모든 행이 예전 버전과 일치하는지 확인하세요.

직접 해 보기4개 중 0개 완료

D2에 500 이상이면 고액, 아니면 소액을 돌려주는 LAMBDA를 쓰고, 바로 호출해서 A3로 테스트해 보세요.
D3에 MAP으로 A2:A7의 금액을 500 이상이면 1, 아니면 0으로 바꾼 다음 결과를 모두 더해 보세요.
D4에 REDUCE로 0부터 시작해서 금액 여섯 개를 모두 더해 보세요.
D5에 SCAN으로 A2:A7의 누적 합계를 만들고, INDEX로 그중 4번째 값을 꺼내 보세요.

점선으로 표시된 셀을 클릭하고, 입력줄에 수식을 쓴 다음 Enter를 누르세요.

	A	C	D
1	금액	검사 항목	결과
2	180	920 분류하기
3	920	고액 건수
4	45	총합계
5	610	4번째 누적 합계
6	300
7	75

실력 점검

LET을 써 본 사람에게 LAMBDA가 무엇인지 한 문장으로 설명할 수 있나요?
입력 하나를 받아 분류 결과를 돌려주는 LAMBDA를 쓰고, 셀에서 테스트할 수 있나요?
수식을 아래로 복사하지 않고 MAP으로 범위 전체에 그 LAMBDA를 적용할 수 있나요?
SCAN은 돌려주고 REDUCE는 돌려주지 않는 것이 무엇인지 설명할 수 있나요?
데이터가 늘어나도 계속 동작하도록 #으로 분산 범위를 참조하는 수식을 만들 수 있나요?

도움이 되었나요? 틀린 부분을 발견하셨나요? 피드백 보내기 · admin@arumsoft.com

엑셀 LAMBDA 함수와 동적 배열로 재사용 수식 만들기

배울 내용

시작하기 전에

예제 파일