엑셀 배열수식 완전 정복: 사용법부터 실전 예제까지

엑셀(Excel)은 단순한 데이터 입력 도구를 넘어 강력한 계산 및 분석 기능을 제공하는 소프트웨어입니다. 특히 배열수식(Array Formula) 은 복잡한 데이터 연산을 효율적으로 수행할 수 있도록 도와줍니다. 본 글에서는 배열수식의 개념, 사용법, 실전 예제, 그리고 최신 엑셀 버전에서 개선된 기능까지 자세히 설명합니다.

1. 배열수식이란?

배열수식은 여러 개의 값을 동시에 처리하는 수식입니다. 일반적인 엑셀 수식은 단일 셀에 값을 반환하지만, 배열수식은 여러 개의 셀에서 값을 동시에 계산할 수 있습니다.

과거에는 Ctrl + Shift + Enter를 눌러야 배열수식을 입력할 수 있었지만, 엑셀 365 및 최신 버전에서는 자동으로 동작하는 동적 배열(Dynamic Array) 기능이 도입되어 사용이 더 간편해졌습니다.

2. 배열수식의 기본 사용법

배열수식을 사용할 때는 기존 수식과는 약간의 차이가 있습니다. 다음은 배열수식의 기본적인 사용법입니다.

(1) 다중 셀에서 결과 반환

1. 결과를 표시할 셀 범위를 선택합니다.
2. 배열수식을 입력합니다.
3. Ctrl + Shift + Enter를 눌러 수식을 입력합니다. (엑셀 365에서는 일반 Enter 입력 가능)

예제: A1:A5에 있는 숫자들의 제곱값을 구하려면 다음 수식을 입력하고 Ctrl + Shift + Enter를 누릅니다.

=A1:A5^2

(2) 단일 셀에서 다중 값 반환 (스필 기능 활용)

엑셀 365에서는 스필(Spill) 기능을 활용하여 자동으로 여러 셀에 결과를 출력할 수 있습니다.

=SEQUENCE(5,1,1,1)

위 수식을 입력하면 1부터 5까지의 숫자가 아래로 자동 입력됩니다.

3. 배열수식의 활용 예제

(1) 여러 개의 조건을 만족하는 값 합산

예를 들어, 특정 범위에서 두 개 이상의 조건을 만족하는 값만 합산하려면 배열수식을 사용할 수 있습니다.

=SUM((A2:A10="사과")*(B2:B10>50)*C2:C10)

위 수식은 A열이 "사과"이고 B열 값이 50보다 큰 경우, C열 값을 합산합니다.

(2) 중복 값 제거 후 고유 값 리스트 출력

엑셀 365 이상에서 UNIQUE 함수를 사용하면 중복 제거가 가능합니다.

=UNIQUE(A2:A100)

위 수식은 A열에서 중복을 제거한 값들의 리스트를 반환합니다.

(3) 특정 조건을 만족하는 데이터 개수 세기

배열수식을 사용하면 특정 조건을 만족하는 데이터 개수를 쉽게 계산할 수 있습니다.

=SUM((A2:A100>50)*(B2:B100="Yes"))

위 수식은 A열 값이 50보다 크고 B열 값이 "Yes"인 경우의 개수를 반환합니다.

(4) 행렬 곱 계산 (MMULT 함수 활용)

엑셀에서 행렬 곱셈을 수행하려면 MMULT 함수를 사용할 수 있습니다.

=MMULT(A1:B2, C1:D2)

위 수식은 A1:B2와 C1:D2 행렬을 곱한 결과를 반환합니다.

4. 배열수식 사용 시 주의할 점

1. 배열 크기 확인: 결과가 출력될 공간이 충분한지 확인하세요. 공간이 부족하면 #SPILL! 오류가 발생할 수 있습니다.
2. Ctrl + Shift + Enter 사용 여부: 엑셀 365 이전 버전에서는 반드시 Ctrl + Shift + Enter를 눌러야 합니다.
3. 배열 동작 방식 이해: 배열 내 각 요소가 독립적으로 계산된다는 점을 숙지해야 합니다.

5. 동적 배열 함수 활용 (엑셀 365 이상)

엑셀 365부터 지원하는 동적 배열 함수는 배열수식을 더욱 강력하게 만들어 줍니다.

• SORT : 데이터를 정렬
• FILTER : 특정 조건에 맞는 데이터 필터링
• UNIQUE : 중복 제거 후 고유 값 반환
• SEQUENCE : 연속된 숫자 생성

예제:

=FILTER(A2:C10, B2:B10="과일")

위 수식은 B열이 "과일"인 행만 필터링하여 반환합니다.

6. 결론

엑셀의 배열수식은 데이터 분석과 계산을 더욱 강력하고 효율적으로 만들어 줍니다. 엑셀 365 이상에서는 동적 배열 기능이 추가되어 더욱 쉽게 사용할 수 있으며, 기존 버전에서도 Ctrl + Shift + Enter를 활용하여 강력한 기능을 사용할 수 있습니다. 배열수식을 마스터하면 복잡한 데이터 분석을 더욱 빠르고 효과적으로 수행할 수 있습니다.