강좌 > 엑셀중급강좌 > 배열 수식 > 엑셀의 새로운 기능, 동적 배열 수식

엑셀의 새로운 기능, 동적 배열 수식

Microsoft 365, Excel 2021 버전부터 기존의 배열 수식(레거시 배열수식이라고 함)과는 다른 동적 배열 수식을 사용할 수 있습니다.  동적 배열 수식은 사용하기 간편하고 오류의 가능성도 적고 여러 가지 면에서 장점이 많습니다.

실습용 엑셀파일 다운로드 : 엑셀의-새로운기능-동적배열수식.xlsx

 

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>
동적 배열 수식 사용시 SPILL 오류 주의

 

2. 단일 셀에 결과 구하기

기존의 배열 수식(레거시 배열) 사용하기

다음과 같이 모든 숫자에 2를 곱한 후 합계를 구해야 할 때 배열 수식으로 해결할 수 있습니다.

[F17] 셀에 =SUM((B17:D19)*2) 수식을 입력한 후 [Ctrl + Shift + Enter]를 누르면...

<Excel 2016>
레거시 배열 함수

 

[F17] 셀에 결과 2796이 반환됩니다.

수식 입력줄에 {=SUM((B17:D19)*2)}이 입력되어 있습니다. 배열 수식을 의미하는 중괄호({, })가 수식의 양쪽에 붙어 있습니다.

<Excel 2016>
레거시 배열 함수

 

동적 배열 수식 사용하기

위에서 했던 작업을 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)

UNIQUE 함수로 직위에서 중복 제거하기

 

[J4:J8] 범위에 동적 배열의 형태로 결과를 가져옵니다.

UNIQUE 함수로 직위에서 중복 제거하기

동적 배열 함수 중 UNIQUE함수만 간단히 살펴 보았습니다. 나머지 동적 배열 함수도 동적 배열의 형태로 결과를 가져오는 동일한 방식으로 작동합니다.

 

4. 동적 배열 수식의 장점

지금까지 살펴본 동적 배열 수식의 장점을 정리해 보면 다음과 같습니다.

  • 수식을 간편하게 입력할 수 있다. 배열 수식처럼 결과가 출력되는 범위가 아니라 첫 셀에만 입력하면 된다.
  • 배열 수식 입력 시 CSE[Ctrl + Shift + Enter]가 필요 없다. CSE로 입력된 수식은 누군가가 실수로 수정모드에서 다시 CSE로 입력하지 않으면 일반 수식으로 바뀌어 계산의 결과가 틀려지는 경우가 많아서 문제가 많았다.
  • 한번에 여러 개의 결과를 출력해야 할 때 유용한다. 위에서 언급한 동적 배열 함수는 한번에 여러 개의 결과를 반환하는 함수이다.

 

※ 동적 배열 수식의 또 다른 활용 예는 다음 글을 참고하세요.
[ 동적 배열 수식의 강력한 기능으로 수식을 단순하게 ]

 

 


엑셀 수식 강좌 

[ 수식 다루기 ]

[ 엑셀에서 입력과 표시방법 ]

[ 수식에서 셀과 범위 참조 방식 ]

[ 엑셀을 제대로 쓰는 데이터처리 ]

[ 엑셀 이름정의와 표기능 ]

[ 수식을 분석하고 오류 해결하기 ]

 

댓글 달기

이메일 주소는 공개되지 않습니다.

Scroll to Top
%d 블로거가 이것을 좋아합니다: