Microsoft 365, Excel 2021 버전부터 기존의 배열 수식(레거시 배열수식이라고 함)과는 다른 동적 배열 수식을 사용할 수 있습니다. 동적 배열 수식은 사용하기 간편하고 오류의 가능성도 적고 여러 가지 면에서 장점이 많습니다.
1. 한 번에 여러 셀에 결과 구하기
기존의 배열 수식(레거시 배열) 사용하기
Microsoft 365, Excel 2021 이전 버전의 엑셀에서 여러 셀에 결과를 가져오려면 다음과 같이 배열 수식을 사용해야 합니다.
예를 들어 다음과 같이 직원의 월급여에 12를 곱해서 연봉을 계산해야 할 때 배열 수식으로 해결할 수 있습니다.
① 결과를 가져올 범위를 먼저 선택하고 ② =D4:D13*12 수식을 입력한 후 [Ctrl + Shift + Enter]를 누르면
<Excel 2016>
[F4:F13] 범위에 결과가 표시됩니다.
수식 입력줄에 {=D4:D13*12}이 입력되어 있습니다. 배열 수식을 의미하는 중괄호({, })가 수식의 양쪽에 붙어 있습니다.
<Excel 2016>
동적 배열 수식 사용하기
위에서 했던 작업을 Microsoft 365에서 할 때는 ① [F4] 셀에 =D4:D13*12 수식을 입력한 후 [Enter]만 누르면 됩니다.
<Microsoft 365 Excel>
수식을 [F4] 셀에 한 번만 입력했는데 ② [F4:F13] 범위에 결과가 한 번에 표시됩니다.
<Microsoft 365 Excel>
이렇게 수식을 한 번만 입력하고 결과는 여러 셀에 반환되는 것을 동적 배열이라고 합니다. 동적 배열은 가장자리가 파란색 테두리선으로 표시됩니다.
수식 입력줄을 확인해 보면 중괄호 없이 =D4:D13*12가 입력되어 있습니다. 동적 배열 수식은 [Ctrl + Shift + Enter]를 누를 필요가 없습니다.
'#' 기호로 결과 범위 참조하기
[F4:F13] 범위에 동적 배열의 형태로 결과가 구해진 후 이 범위를 참조하려면 범위의 첫번째 셀 주소에 '#'을 붙이면 됩니다.=F4#
<Microsoft 365 Excel>
F4# 수식은 범위를 참조하므로
=SUM(F4#) 형태의 수식도 가능합니다.
※ 동적 배열 수식 사용시 주의
동적배열은 아래와 같이 결과를 가져올 범위에 이미 다른 값이 존재한다면 결과를 가지오지 못하고 다음과 같이 #SPILL! 오류가 발생합니다. 동적배열 형태로 결과를 가져올 때는 가져올 범위가 비어 있어야 합니다.
<Microsoft 365 Excel>
2. 단일 셀에 결과 구하기
기존의 배열 수식(레거시 배열) 사용하기
다음과 같이 모든 숫자에 2를 곱한 후 합계를 구해야 할 때 배열 수식으로 해결할 수 있습니다.
[F17] 셀에 =SUM((B17:D19)*2) 수식을 입력한 후 [Ctrl + Shift + Enter]를 누르면 [F17] 셀에 결과 2796이 반환됩니다.<Excel 2016>
수식 입력줄에 {=SUM((B17:D19)*2)}이 입력되어 있습니다. 배열 수식을 의미하는 중괄호({, })가 수식의 양쪽에 붙어 있습니다.
동적 배열 수식 사용하기
위에서 했던 작업을 Microsoft 365에서 할 때는 =SUM((B17:D19)*2) 수식을 입력한 후 [Enter]를 누릅니다.
동적 배열 수식이므로 [Ctrl + Shift + Enter]를 누를 필요가 없습니다.
<Microsoft 365 Excel>
3. 동적 배열 함수
Microsoft 365, Excel 2021 버전에는 동적 배열 형태로 결과를 구해주는 동적 배열 함수가 다음과 같이 추가되었습니다.
| 함수 | 기능 |
| FILTER | 원하는 조건으로 데이터 조회(필터링)하기 |
| RANDARRAY | 배열 형태로 난수 구하기 |
| SEQUENCE | 연속된 숫자목록 만들기 |
| SORT | 데이터 정렬하기 |
| SORTBY | 범위의 값을 기준으로 데이터 정렬하기 |
| UNIQUE | 중복 제거하기 |
동적 배열 함수 사용 예
동적 배열 함수 중 UNIQUE함수는 범위에서 중복을 제거한 결과를 동적 배열 형태로 가져옵니다.
[J4] 셀에 다음 수식을 입력합니다.=UNIQUE(C4:C13)
[J4:J8] 범위에 동적 배열의 형태로 결과를 가져옵니다.
동적 배열 함수 중 UNIQUE함수만 간단히 살펴 보았습니다. 나머지 동적 배열 함수도 동적 배열의 형태로 결과를 가져오는 동일한 방식으로 작동합니다.
4. 동적 배열 수식의 장점
지금까지 살펴본 동적 배열 수식의 장점을 정리해 보면 다음과 같습니다.
- 수식을 간편하게 입력할 수 있다. 배열 수식처럼 결과가 출력되는 범위가 아니라 첫 셀에만 입력하면 된다.
- 배열 수식 입력 시 CSE[Ctrl + Shift + Enter]가 필요 없다. CSE로 입력된 수식은 누군가가 실수로 수정모드에서 다시 CSE로 입력하지 않으면 일반 수식으로 바뀌어 계산의 결과가 틀려지는 경우가 많아서 문제가 많았다.
- 한번에 여러 개의 결과를 출력해야 할 때 유용한다. 위에서 언급한 동적 배열 함수는 한번에 여러 개의 결과를 반환하는 함수이다.
※ 동적 배열 수식의 또 다른 활용 예는 다음 글을 참고하세요.
[ 동적 배열 수식의 강력한 기능으로 수식을 단순하게 ]
엑셀 수식 강좌
[ 수식 다루기 ]
[ 엑셀에서 입력과 표시방법 ]
[ 수식에서 셀과 범위 참조 방식 ]
[ 엑셀을 제대로 쓰는 데이터처리 ]
- 엑셀에서 잘못된 날짜, 숫자 찾고 변환하기
- 수식으로 한 번에 텍스트 나누기
- 엑셀 고급필터로 다양하게 자료 필터링하기
- 정렬의 또 다른 방법, 사용자 지정목록/색으로 정렬
- 엑셀 오류데이터 처리하기
- 파워 쿼리(Power Query) 소개
- 파워쿼리로 외부 데이터 가져오기
- 엑셀에서 중복 데이터 제거하기
- 엑셀 텍스트 나누기의 모든 것
- 이동옵션으로 원하는 셀 한번에 선택하고 처리하기
- 데이터 유효성 검사로 상위목록에 종속되는 하위목록 만들기
- 사용자 지정 수식으로 데이터 유효성 검사하기
- 데이터 유효성 검사로 허용된 데이터만 입력
- 엑셀 데이터 다루기 첫 단추는 데이터 구조화
[ 엑셀 이름정의와 표기능 ]
[ 수식을 분석하고 오류 해결하기 ]
- 엑셀 순환참조 문제 해결하기
- 수식 계산 단계 알아보기
- 엑셀 수식 오류 검사하고 추적하기
- 엑셀에서 참조 셀 추적하기
- 엑셀 오류 유형(#DIV/0!, #N/A, #NAME?, #REF! 등)과 해결방법
