들어가기 전에
Excel의 기본 수식은 간단한 계산에는 적합하지만, 복잡한 데이터 분석을 다룰 때는 금방 어지러워지게 마련입니다. 이럴 때 배열 수식을 사용하면 단일 수식으로 전체 데이터 범위에 대한 계산을 수행할 수 있습니다. 보조열이나 여러 단계의 수식을 사용하지 않고 단 하나의 강력한 표현식으로 매우 빠른 조회, 필터링 및 정렬을 처리하는 방법을 소개합니다.
이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: I use these 6 Excel array formulas to crush complex calculations
- URL: https://www.makeuseof.com/use-excel-array-formulas-crush-complex-calculations/
1. XLOOKUP
요즘은 VLOOKUP 함수를 대신하여 XLOOKUP을 사용하는 경우가 많지만 XLOOKUP은 처음부터 존재했어야 할 조회 함수입니다. 열 개수를 세고 오른쪽으로만 검색하는 VLOOKUP과 달리, XLOOKUP은 모든 방향으로 작동하며 실제 열 참조를 사용합니다.
사용 구문은 다음과 같습니다.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
- lookup_value: 검색하려는 특정 값. 부품 번호, 제품 코드 또는 데이터세트의 식별자일 수 있음
- lookup_array: Excel에서 lookup_value를 검색할 범위. 일반적으로 검색 조건이 포함된 단일 열 또는 행
- return_array: 검색할 값이 포함된 범위. 단일 열, 여러 열 또는 전체 테이블 섹션일 수 있음
if_not_found(선택 사항): 일치하는 항목이 없을 때 표시할 사용자 지정 텍스트 또는 값. 귀찮은 #N/A 오류를 없애고 대신 "찾을 수 없음" 또는 "부품 번호 확인"을 표시할 수 있음 - match_mode(선택 사항): 일치 유형. 정확한 일치(기본값)는 0, 정확한 일치 또는 그 다음으로 작은 일치는 -1, 정확한 일치 또는 그 다음으로 큰 일치는 1, 와일드카드 일치는 2를 사용
- 검색 모드(선택 사항): 검색 방향. 1은 처음부터 마지막까지 검색(기본값), -1은 마지막부터 첫 번째까지 검색, 2는 정렬된 데이터에 대해 이진 검색 수행
기계 재고 스프레드시트를 예로 들어 보겠습니다. 다음 수식은 부품 ID 범위 내에서 부품 번호 "BRG-002"를 검색하여 해당 데이터를 반환합니다. 부품이 존재하지 않으면 오류 대신 "Part not found"이 표시됩니다.
=XLOOKUP("BRG-002", A:A, A:H, "Part not found")
XLOOKUP을 사용하면 VLOOKUP의 열 계산 문제 없이 여러 열에서 데이터를 가져올 수 있어 데이터를 빠르게 찾는 데 가장 중요한 Excel 함수 중 하나입니다.
2. SUMPRODUCT
SUMPRODUCT는 단순히 숫자를 더하는 것이 아니라, 배열을 곱한 후 그 결과를 더합니다. 따라서 여러 개의 보조 열이 필요한 복잡한 조건식 계산에 유용합니다.
구문은 다음과 같습니다.
=SUMPRODUCT(array1, [array2], [array3], ...)
array1은 곱할 첫 번째 값 범위로, 일반적으로 수량이나 비용과 같은 기본 데이터 열입니다. 그리고 array2는 곱셈을 위한 두 번째 범위로, 선택 사항이며, 비교 연산자를 사용하는 조건이나 조건 논리를 포함하는 경우가 많습니다.
배열 내에서 논리 연산자를 사용하면 더욱 유용합니다. 예를 들어, (Supplier="Siemens")와 같은 조건을 작성하면 Excel에서 TRUE/FALSE 결과를 1/0으로 변환하여 수학 연산을 수행할 수 있습니다.
다음 수식은 Siemens에서 공급한 부품의 총 재고 가치를 계산합니다. 이 수식은 수량을 단가로 곱하지만, 공급업체가 기준을 충족하는 행에 대해서만 계산합니다.
=SUMPRODUCT(D2:D100*H2:H100 * (G2:G100="Siemens"))
다음 수식은 충분히 재고가 있는 재고를 유지하는 데 드는 총 비용을 구합니다.
=SUMPRODUCT((C2:C100="Bearings") * (D2:D100>=15) * H2:H100)
이 방법은 두 가지 조건을 동시에 적용합니다. 즉, 카테고리는 "Bearings"이어야 하고 재고 수준은 15개 이상이어야 하며, 이를 통해 적절한 재고 범위를 갖춘 Bearings 카테고리를 파악하는 데 도움이 됩니다.
여러 조건을 사용하는 SUM 함수와 달리 SUMPRODUCT는 읽기 쉬운 단일 수식으로 여러 조건을 처리하므로 복잡한 중첩 구조가 필요하지 않습니다. SUMIF 및 SUMIFS와 같은 Excel의 SUM 함수는 기본적인 조건부 합계 계산에 적합하지만, SUMPRODUCT는 합계 계산 전에 값을 곱하거나 더 복잡한 논리 연산을 처리할 때 더욱 효과적입니다.
3. FILTER
FILTER는 사용자가 지정한 조건에 따라 데이터세트에서 행을 추출합니다. 수동 필터링과 달리, 이 함수는 원본 데이터가 변경될 때 자동으로 업데이트되는 동적 결과를 생성합니다.
FILTER 함수의 구문은 다음과 같습니다.
=FILTER(array, include, [if_empty])
- array: 필터링할 전체 데이터 범위. 기준 열뿐만 아니라 결과에 포함할 모든 열 포함
- include: 반환할 행을 결정하는 논리적 조건은 비교 연산자를 사용하여 각 행에 대한 TRUE/FALSE 배열 생성
- if_empty (선택 사항): 조건을 충족하는 행이 없을 때 사용자 지정 메시지 표시. #CALC! 오류를 방지하고 "일치하는 항목이 없습니다."와 같은 의미 있는 텍스트 표시
이 함수는 배열의 모든 행에 대해 조건을 평가하여 작동합니다. 조건이 TRUE를 반환하면 해당 행 전체가 필터링된 결과에 나타납니다. 다음은 기계 재고 스프레드시트의 예입니다.
=FILTER(A2:H101, (C2:C101="Bearings") * (G2:G101="Timken"))
이 수식은 공급업체가 "Timken"이고 카테고리가 "Bearings"인 모든 행을 추출합니다. 별표(*)는 논리 배열을 곱하여 AND 조건을 생성합니다.
원본 범위에 새 데이터를 추가할 때 Excel의 FILTER 함수를 사용하는 것이 수동 정렬이나 임시 테이블보다 더 효율적입니다. 필터링된 결과가 자동으로 업데이트되기 때문입니다. 따라서 실시간 대시보드와 보고서를 만드는 데 유용합니다.
4. UNIQUE
UNIQUE 함수는 데이터 범위에서 고유한 값을 가져와 중복을 자동으로 제거합니다. 이 함수는 드롭다운 목록을 만들고, 데이터 범주를 분석하고, 요약 보고서를 작성할 때 유용합니다.
사용 구문은 다음과 같습니다.
=UNIQUE(array, [by_col], [exactly_once])
- array: 중복을 제거하려는 데이터가 포함된 범위. 단일 열, 여러 열 또는 전체 테이블 섹션이 될 수 있음
- by_col (선택): FALSE는 고유성을 위해 행을 비교하고(기본값), TRUE는 열을 비교. 대부분의 경우 기본 행 비교를 사용
- exact_once(선택): FALSE는 여러 번 나타나는 값(기본값)을 포함하여 모든 고유한 값을 반환하고, TRUE는 데이터 세트에 정확히 한 번 나타나는 값만 반환
UNIQUE 함수는 배열의 각 행 또는 값을 평가하여 각 고유 항목의 첫 번째 항목만 반환합니다. 순서는 원래 데이터 시퀀스와 일치합니다. 다음은 예시입니다.
=UNIQUE(G2:G22)
이 수식은 공급업체 열 G에서 모든 고유 공급업체 이름을 추출하여 중복 없는 깔끔한 목록을 생성합니다. 저는 이 수식을 공급업체 드롭다운 메뉴나 요약 보고서를 만드는 데 사용합니다.
아래와 같이 전체 표에 사용할 수도 있습니다.
=UNIQUE(A2:F100)
모든 열(A~F)에 걸쳐 고유한 조합을 반환하여 서로 다른 재고 기록을 보여줍니다. 두 부품의 모든 열 값이 동일한 경우, 결과에는 하나만 표시됩니다.
대용량 데이터를 다룰 때 UNIQUE는 수동으로 중복을 제거하는 지루한 과정을 없애줍니다. 새 데이터가 도착하면 동적 결과가 업데이트되고, UNIQUE는 스필 배열을 생성하기 때문에 모든 고유 값을 수용하도록 자동으로 확장되어 테이블 크기를 조정하는 번거로움을 없앨 수 있습니다 . 저는 이 기능을 사용하여 깔끔한 참조 목록을 유지하고 신뢰할 수 있는 데이터 검증 범위를 구축합니다.
5. SORT 및 SORTBY
SORT 및 SORTBY 함수는 소스 데이터를 그대로 유지하면서 데이터를 동적으로 정리합니다. SORT는 열 위치를 기준으로 기본적인 정렬을 처리하는 반면, SORTBY는 여러 열의 값을 기준으로 정렬하므로 복잡한 정렬도 더욱 유연하게 처리할 수 있습니다.
SORT는 다음 구문을 사용합니다.
=SORT(array, [sort_index], [sort_order], [by_col])
- array: 정렬하려는 데이터 범위. 여기에는 정렬된 결과에 나타나야 하는 모든 열 포함
- sort_index(선택): 정렬 기준으로 사용할 배열 내 열 번호(기본값은 1)
- sort_order(선택): 오름차순(기본값)을 원하면 1, 내림차순을 원하면 -1 사용
- by_col(선택): FALSE는 행을 기준으로 정렬(기본값), TRUE는 열을 기준으로 정렬(대부분 행 정렬 사용)
SORTBY 함수의 구문은 다음과 같습니다.
=SORTBY(array, by_array1, [sort_order1], [by_array2], [sort_order2], ...)
- array: 정렬할 데이터 범위. SORT와 동일하며 결과에 포함할 모든 열 포함
- by_array1: 정렬 순서를 결정하는 값이 포함된 범위. 기본 배열 외부의 열도 될 수 있음
- sort_order1(선택): 오름차순은 1(기본값), 내림차순은 -1
- by_array2, sort_order2(선택): 다중 레벨 정렬을 위한 추가 정렬 기준
기계 재고 스프레드시트의 예를 살펴보면 이러한 기능은 실제 정렬 시나리오를 처리합니다.
=SORT(A2:H22, 4, -1)
이 수식은 전체 재고를 재고 수준을 기준으로 내림차순으로 정렬하여 재고가 가장 많은 품목부터 표시합니다. 이 수식은 모든 행 관계를 유지하면서 4열(재고 수준)을 기준으로 정렬합니다.
정렬 기준과 여러 정렬 수준을 더 효과적으로 제어하기 위해 SORT 대신 SORTBY 함수를 사용합니다. 예를 들어, 다음 수식은 먼저 범주별로 알파벳순으로 정렬한 다음, 각 범주 내에서 재고 수준을 기준으로 가장 높은 것부터 가장 낮은 것 순으로 정렬합니다.
=SORTBY(A2:H22, C2:C22, 1, D2:D22, -1)
마치며
배열 수식은 스프레드시트 유지 관리를 어렵게 만드는 복잡한 도우미 열과 중첩 함수를 제거합니다. 여러 작업을 처리하는 단일 수식을 사용하면 통합 문서를 더욱 깔끔하게 정리하고 더욱 전문적인 느낌을 줄 수 있습니다. 주목할 만한 장점은 동적 기능입니다. 원본 데이터가 변경되면 결과가 자동으로 업데이트됩니다. 따라서 더 이상 수동으로 새로 고침하거나 수식 체인을 손상시킬 필요가 없으며, 지속적인 분석을 위해 스프레드시트의 안정성이 더욱 향상됩니다.
'Excel' 카테고리의 다른 글
| Excel 표가 제대로 작동하지 않는 이유와 해결 방법 (0) | 2025.12.24 |
|---|---|
| 더 스마트하게 일하는 10가지 간단한 엑셀 활용법 (0) | 2025.12.22 |
| Excel에서 피벗 테이블 데이터 쉽게 검색하는 방법 (1) | 2025.12.19 |
| 내가 가장 많이 사용하는 Excel 함수(그리고 그 이유) (0) | 2025.12.16 |
| 이 6가지 함수를 쓰지 않는다면 Excel을 잘못 사용하고 있는 것 (1) | 2025.12.15 |
