동적 배열 수식과 유출된(Spilled) 범위 연산자 #

1. 의미

가. 동적 배열 수식

전통적인 배열 수식의 경우는 Ctrl+Shift+Enter키로 배열 함수를 입력했지만, Microsoft 365 버전에서는 Ctrl+Shift+Enter키를 사용하지 않고 엔터 키 입력만으로, 인접 범위에 값을 반환할 수 있는데 이들 함수를 동적 배열 수식(Dynamic Array Formulas)라고 합니다.

또한 인접 범위에 넘치게 값이 반환된다고 해서 유출된 배열 수식(Spilled Array Formulas [Functions])라고도 합니다.

 

수식은 A1셀에만 있지만, 값은 D3셀까지 채워집니다.

 

나. 유출된 범위 연산자

위와 같이 수식은 한 셀에만 있지만 여러 셀에 걸쳐 값이 반환되는 경우 # 연산자를 이용해 유출된(Spilled) 범위를 반환받을 수 있습니다.

 

E1셀에 =sum(a1#)이라고 입력하면 A1셀의 수식 결괏값이 D3셀까지 반환되므로 A1:D3이 유출(또는 분산)된 범위가 됩니다.

 

 

다. Sum, Average 등 통계(수학) 함수와 동적 배열 함수의 결합

 

위와 같은 경우 A1셀에 

=SUM(RANDARRAY(3,4,1,12,TRUE))라고,

동적 배열 함수를 Sum함수의 인수로 넣으면

동적 배열 함수의 결괏값을 합산해서 하나의 결괏값만이 반환되므로

A1셀에만 값이 표시됩니다.

 

RandArray함수는 지정된 행과 열로 최솟값과 최댓값 사이의 숫자를 임의로 반환하기 때문에 결괏값이 수식이 입력될 때마다 값이 달라집니다.

 

 

2. 적용 범위

Microsoft 365용 Excel, Mac용 Microsoft 365용 Excel, 웹용 Excel, Excel 2021 Mac용, Excel 2021, Excel 2019, Excel 2016, iPad용 Excel, iPhone용 Excel, Android 태블릿용 Excel, Android 휴대폰용 Excel

단, 함수에 따라 적용 범위가 달라질 수 있습니다.

 

 

3. 동적 배열 함수의 특징

가. 분산 영역의 첫 번째 셀만 편집 가능

첫 번째 셀만 진하게 수식이 보이고 나머지 셀들의 수식은 희미하게 보입니다.

 

 

나. 유출된 범위의 주변에 파란색 선 테두리가 생김

 

다. 결괏값이 배치될 영역에 값이 있으면 #분산! 에러가 발생함

A1셀에서 D3셀까지 값이 채워져야 하는데, A2셀에 12가 있으므로 값을 채울 수 없어서 #분산! 에러가 발생합니다.

 

 

4. 동적 배열 함수의 종류

가. FILTER 함수

F8셀의 수식은 =FILTER(표1,표1[제품]=G6)이며,

J8셀의 수식은 =SUM(F8#)입니다.

 

이때 =sum(까지 입력한 후 마우스로 F8셀부터 i12셀까지 범위를 잡으면 자동으로 F8#이라고 입력됩니다.

 

만약에 판매량 계 수식을 i12셀에 만들면

지역이 새로 추가되는 경우 범위가 합계와 겹치기 때문에 #분산! 에러가 발생하므로 j8셀에 합계 수식을 만든 것입니다.

 

판매량 계를 구하는 수식을 끌어서 j8셀로 옮기면 문제없이 Filter함수의 결괏값과 Sum함수의 결괏값이 표시됩니다.

나. SEQUENCE 함수

SEQUENCE 함수의 구문은

=SEQUENCE(rows,[columns],[start],[step]) 입니다.

 

A29셀에 =SEQUENCE(3,4,11,2)라고 입력해서 11부터 2씩 떨어진 숫자를 3행 4열로 배치하면 아래와 같이 되는데,

F29셀에 

 

 

F29셀에 =AVERAGE(A29#)라고 입력하면 Sequece 함수의 결괏값 범위의 평균 22가 구해집니다.

다. SORT 함수

L8셀의 수식은 =SORT(D8:D24)이며,

M8셀의 수식은 =MAX(L8#)입니다.

라. SORTBY 함수

O8셀의 수식은 =SORTBY(표1[판매량],표1[판매량])이고,

P8셀의 수식은 =MEDIAN(O8#)입니다.

마. UNIQUE 함수

Unique 함수는 고윳값을 구해주는 함수로 

R8셀의 수식은 =UNIQUE(표1[지역])이고,

S8셀의 수식은 =COUNTA(R8#)입니다.

 

 

바. INDEX함수로 배열을 반환하는 경우

일반적으로 index 함수는 단일 값을 반환하지만

row_num나 column_num을 0으로 입력하면 1개 열 또는 1개 행이 유출된 배열로 반환됩니다.

 

U8셀의 수식은 =INDEX(표1,0,4)로 표1에서 네 번째 열을 반환하며

V8셀의 수식은 =MIN(U8#)으로 최솟값을 구해줍니다.

 

 

사. OFFSET함수로 배열을 반환하는 경우

offset함수도 일반적으로 상대적인 위치에 있는 값을 반환하지만, 높이와 너비를 지정하면 분산 배열이 반환됩니다.

X8셀의 수식은 =OFFSET(A8,0,2,10,2)으로 A8셀을 기준으로 행은 이동하지 않고, 열만 오른쪽으로 2칸 이동한 다음, 그곳을 기준으로 높이 10행, 너비 2열의 데이터를 가져오는 것입니다.

Z8셀의 수식은 =SUM(X8#)이지만 문자열은 0이므로 숫자의 합이 반환됩니다.

 

 

아. RANDARRAY 함수
위에서 살펴봤으므로 생략합니다.

동적 배열 수식.xlsx

0.01MB