복잡한 계산을 단 한 줄로! 꼭 알아야 할 엑셀 배열 수식 5가지

1. 배열 수식이란 무엇인가? (완전 기초부터)

엑셀의 기본 수식은 '한 칸을 계산해서 한 칸에 결과를 내는' 방식입니다. 예를 들어 =A2*B2는 A2와 B2를 곱해 그 자리에 답 하나를 냅니다. 간단한 계산에는 더없이 좋지만, 데이터가 수백·수천 줄로 늘어나고 조건까지 복잡해지면 한계가 옵니다. 이런 계산을 일반 수식으로 처리하려면 보조 열(도우미 열)을 여러 개 만들고, 그 열들을 다시 합치는 식으로 단계를 잘게 쪼개서 해결하곤 합니다.

이런 방식을 대체할 수 있는 것이 배열 수식(Array Formula)입니다. 배열 수식을 한마디로 정의하면, 여러 칸(범위) 전체를 한꺼번에 계산해, 필요하면 여러 칸에 결과를 한 번에 표시하는 수식입니다. 칸 하나가 아니라 '범위 덩어리(배열)'를 통째로 다룬다고 생각하시면 됩니다.

'스필(Spill)' 개념 이해하기

요즘 엑셀(Microsoft 365, 엑셀 2021 이상)에서는 배열 수식을 쓰기가 훨씬 쉬워졌습니다. 예전에는 Ctrl + Shift + Enter라는 특별한 입력법이 필요했지만, 지금은 그냥 Enter만 눌러도 됩니다. 결과가 한 칸을 넘으면, 엑셀이 알아서 아래·오른쪽 빈 칸으로 결과를 흘려보냅니다. 이 동작을 '스필(Spill: 유출·흘러넘침)'이라고 부릅니다. 스필과 관련하여 반드시 알고 있어야 할 세 가지 사항을 정리하면 다음과 같습니다.

이 글에서 소개할 다섯 함수는 대부분 이 스필 기능 위에서 동작합니다. '단 한 칸에 수식을 입력하면, 결과가 알아서 펼쳐지고, 원본이 바뀌면 자동으로 갱신된다'라는 큰 그림을 기억하고 출발합니다!

이 글에서 사용할 예제 데이터

설명을 통일하기 위해, 아래와 같은 기계 부품 재고표를 예시로 사용하겠습니다. A열부터 H열까지 한 줄에 부품 한 종류씩, 머리글을 제외하고 2행부터 35행까지(A2:H35) 기록되어 있다고 가정합니다.

2. XLOOKUP - 모든 방향으로 똑똑하게 찾는 조회 함수

특정 값을 찾아서 그에 해당하는 정보를 가져오는 작업을 '조회(Lookup)'라고 합니다. 예전에는 VLOOKUP을 많이 썼지만, VLOOKUP은 '찾는 값이 반드시 맨 왼쪽 열에 있어야 하고, 오른쪽 방향으로만 찾을 수 있다'는 결정적인 불편함이 있었습니다. 게다가 "결과는 왼쪽에서 몇 번째 열?"이라며 열 개수를 사람이 직접 세어야 했죠.

XLOOKUP은 이런 불편을 없앤, VLOOKUP의 업그레이드 버전 함수라고 할 수 있습니다. 열 개수를 셀 필요 없이 '찾을 범위'와 '가져올 범위'를 직접 지정하면 되고, 왼쪽이든 오른쪽이든 방향을 가리지 않습니다.

함수 구문 파헤치기

=XLOOKUP(찾을값, 찾을범위, 가져올범위, [없을때표시], [일치방식], [검색방향])

다음 수식은 부품 ID 열(A열)에서 "BRG-002"를 찾아, 그 행의 A~H열 정보를 통째로 가져옵니다. 해당 부품이 없으면 오류 대신 "Part not found"가 표시됩니다.

=XLOOKUP("BRG-002", A2:A35, A2:H35, "Part not found")

여기서 '가져올범위'를 A2:H35처럼 여러 열로 지정한 점에 주목하세요. VLOOKUP은 한 번에 한 열만 가져올 수 있었지만, XLOOKUP은 여러 열을 한 번에 펼쳐서 가져옵니다(스필). 부품 정보를 한 줄로 쭉 불러올 때 매우 편리합니다.

💡 TIP: VLOOKUP을 아직 쓰고 계시다면

XLOOKUP은 VLOOKUP의 상위 호환이라고 봐도 무방합니다. 다만 엑셀 2019 이하 버전에서는 XLOOKUP이 없으니, 이 경우에는 INDEX/MATCH 조합을 쓰거나 버전 업그레이드를 검토하시면 됩니다.

3. SUMPRODUCT - 조건부 계산의 만능 일꾼

이름만 보면 'SUM(합계) + PRODUCT(곱)'입니다. 말 그대로 여러 범위를 짝끼리 곱한 뒤, 그 결과를 모두 더하는 함수입니다. 단순히 더하는 SUM과 달리, '곱하고 나서 더하는' 두 단계를 한 번에 처리하기 때문에 보조 열 없이도 복잡한 조건부 계산을 깔끔하게 해냅니다.

함수 구문 파헤치기

=SUMPRODUCT(배열1, [배열2], [배열3], ...)

가장 단순한 형태는 =SUMPRODUCT(수량범위, 단가범위)처럼, 두 범위를 행끼리 곱한 뒤 전부 더하는 것입니다. 즉 '총 재고 금액'을 한 줄로 구할 수 있습니다.

핵심 원리: TRUE/FALSE는 1과 0이다

SUMPRODUCT가 강력한 이유는 조건식을 곱셈에 끼워 넣을 수 있기 때문입니다. 비밀은 간단합니다. 엑셀에서 조건식의 결과인 TRUE는 숫자 1로, FALSE는 숫자 0으로 취급됩니다.

예를 들어 (G2:G35="Siemens")라는 조건은, 공급업체가 Siemens인 행에서는 1(TRUE), 아닌 행에서는 0(FALSE)으로 이루어진 배열을 만듭니다. 여기에 다른 값을 곱하면, 조건을 만족하는 행(1)의 값만 살아남고, 아닌 행(0)은 0이 되어 사라집니다. 이것이 조건부 계산의 핵심 원리입니다.

다음 수식은 'Siemens가 공급한 부품'의 총 재고 가치를 계산합니다. 수량(D열)에 단가(H열)를 곱하되, 공급업체가 Siemens인 행만 살립니다.

=SUMPRODUCT(D2:D35 * H2:H35 * (G2:G35="Siemens"))

조건을 두 개 이상 지정할 수도 있습니다. 다음 수식은 카테고리가 "Bearings"이면서(AND) 재고가 15개 이상인 부품들의 총 재고 금액을 구합니다.

=SUMPRODUCT((C2:C35="Bearings") * (D2:D35>=15) * H2:H35)

💡 TIP: 곱하기(*)는 AND, 더하기(+)는 OR

조건을 여러 개 지정할 때 규칙이 있습니다. 조건과 조건을 *(곱하기)로 이으면 '그리고(AND)', +(더하기)로 이으면 '또는(OR)'이 됩니다. 1과 0의 곱셈·덧셈을 떠올려 보면 왜 그런지 자연스럽게 이해됩니다.

SUMIF·SUMIFS도 실무에서 조건부 합계를 구할 때 많이 사용되지만, '더하기 전에 곱셈이 필요하거나' 조건 논리가 복잡할 때는 SUMPRODUCT 한 줄이 훨씬 읽기 쉽고 강력합니다.

4. FILTER - 조건에 맞는 행만 추출하기

FILTER 함수는 데이터 범위에서 내가 지정한 조건을 만족하는 행만 추출해 줍니다. '자동 필터' 기능과 비슷해 보이지만 결정적 차이가 있습니다. 자동 필터는 원본을 직접 숨기는 방식이라 매번 손으로 다시 걸어야 하지만, FILTER는 원본은 그대로 둔 채 결과만 따로 뽑아내며, 원본이 바뀌면 자동으로 갱신됩니다.

함수 구문 파헤치기

=FILTER(배열, 조건, [없을때표시])

다음 수식은 공급업체가 "Timken"이고 카테고리가 "Bearings"인 모든 행을 통째로 뽑아냅니다.

=FILTER(A2:H35, (C2:C35="Bearings") * (G2:G35="Timken"), "일치 항목 없음")

여기서도 별표(*)가 두 조건을 'AND(그리고)'로 묶어 줍니다. 새 데이터를 원본에 추가하면 필터 결과가 즉시 따라 늘어나므로, 실시간 대시보드나 자동 보고서를 만들 때 특히 빛을 발합니다.

5. UNIQUE - 중복을 없애고 고유 목록 만들기

UNIQUE 함수는 데이터 범위에서 중복을 제거하고 고유한 값만 뽑아 줍니다. 드롭다운 목록의 재료를 만들거나, "공급업체가 총 몇 군데인가" 같은 요약을 할 때 아주 유용합니다.

함수 구문 파헤치기

=UNIQUE(배열, [열기준여부], [딱한번나온값만])

다음 수식은 공급업체 열(G열)에서 중복을 없앤 깔끔한 공급업체 목록을 만듭니다.

=UNIQUE(G2:G35)

여러 열에 걸쳐 고유 '조합'을 뽑을 수도 있습니다. 아래 수식은 A~F열 값이 모두 같은 행을 하나로 합쳐, 서로 다른 기록만 남깁니다.

=UNIQUE(A2:F35)

UNIQUE 역시 스필 함수라서 새 데이터가 들어오면 결과 목록이 자동으로 늘어납니다. 그래서 데이터 유효성 검사(드롭다운)의 원본 목록으로 연결해 두면, 손볼 필요 없이 항상 최신 상태가 유지되는 다이나믹 참조 목록이 됩니다.

💡 TIP: UNIQUE + SORT 콤보

고유 목록을 오름차순으로 정리하고 싶다면 =SORT(UNIQUE(G2:G35))처럼 두 함수를 겹쳐 쓰면 됩니다. '중복 제거 & 정렬'이 한 줄로 끝납니다.

6. SORT / SORTBY — 원본을 건드리지 않는 동적 정렬

엑셀의 [정렬] 버튼은 원본 데이터의 순서 자체를 물리적으로 변경합니다. 반면 SORT와 SORTBY 함수는 원본 데이터는 그대로 둔 채, 정렬된 '복사본'을 만들어 줍니다. 원본이 바뀌면 정렬 결과도 자동으로 업데이트됩니다.

SORT: 기본 정렬

=SORT(배열, [정렬기준열], [정렬방향], [열기준여부])

다음 수식은 전체 재고표를 4번째 열(재고 수량) 기준 내림차순으로 정렬해, 재고가 많은 품목부터 보여 줍니다. 행끼리의 관계(같은 부품의 다른 정보)는 그대로 유지됩니다.

=SORT(A2:H35, 4, -1)

SORTBY: 다중 기준 정렬

=SORTBY(배열, 기준범위1, [방향1], 기준범위2, [방향2], ...)

SORTBY는 '몇 번째 열'이라는 번호 대신 정렬 기준이 될 범위 자체를 직접 지정합니다. 덕분에 기준이 여러 개인 복잡한 정렬을 더 유연하게 처리할 수 있습니다. 다음 수식은 ① 카테고리(C열)를 오름차순(알파벳순)으로 1차 정렬하고, ② 같은 카테고리 안에서는 재고 수량(D열)이 많은 순으로 정렬합니다.

=SORTBY(A2:H35, C2:C35, 1, D2:D35, -1)

MVP TIP — 한눈에 보는 다섯 함수 비교

앞에서 소개한 함수들의 '주특기'를 표로 정리했습니다. 상황에 맞는 함수를 고르는 길잡이로 활용하세요.

함수	언제 쓰나요?
XLOOKUP	특정 값을 찾아 관련 정보를 가져올 때 (조회)
SUMPRODUCT	여러 조건이 걸린 곱셈·합계를 한 줄로 계산할 때
FILTER	조건에 맞는 '행 전체'를 뽑아낼 때
UNIQUE	중복을 없앤 고유 목록을 만들 때
SORT / SORTBY	원본을 건드리지 않고 정렬된 복사본이 필요할 때

자주 묻는 질문 (FAQ)

마치며

지금까지 복잡한 계산을 한 줄로 줄여 주는 배열 수식 다섯 가지를 살펴보았습니다. 이들의 진짜 가치는 단순히 '수식이 짧아진다'는 데 있지 않습니다. 보조 열과 중첩 수식이 사라져 통합 문서가 깔끔해지고, 원본 데이터가 바뀌면 결과가 알아서 갱신되는 '살아 있는 보고서'를 만들 수 있다는 점이 핵심입니다.

처음에는 낯설어도, 오늘 배운 함수를 실제 업무 파일에 하나씩 적용해 보시길 권합니다. 수동으로 새로 고치고, 도우미 열을 만들고, 수식 체인이 깨질까 노심초사하던 일이 사라지는 경험을 하실 겁니다. 엑셀을 단순한 '데이터 나열 도구'에서 '스스로 갱신되는 분석 시스템'으로 끌어올리는 첫걸음이 바로 이 배열 수식입니다.