엑셀배우기 - 배열수식

엑셀의 배열수식

배열수식이란 일반 수식에서 표현하지 못하는 식을 계산하는 수식의 형태를 말합니다.

배열수식의 입력하기 위해서는 Ctrl + Shift + Enter를 이용하여 입력해아 합니다.

단일셀 배열 수식은 수식 입력완료를 Enter 대신 Ctrl + Shift + Enter 즉, Ctrl과 Shift를 누른 상태에서 손을 안떼고 Enter를 입력해야 합니다.

입력을 하시고 나서 수식 앞뒤로 중괄호{}가 보이신다면 정상적으로 입력이 된 것입니다.

 

그림1. 왼쪽그림 : 배열수식의 표현 오른쪽그림 : 배열수식 미적용과 차이

위의 그림1은 배열수식을 표현한 건데 처음에 =SUM(A1:A3*B1:B3)의 상태에서 Ctrl + Shift + Enter를 누르시면 배열상수가 됩니다.

둘의 차이는 왼쪽의 배열수식은 A1와 B1, A2와 B2, A3와 B3가 곱해져서 더해진 값으로 20을 표시하지만, 배열수식으로 표현하지 않을경우에는 오른쪽그림은 값인 6을 표현하게 됩니다.

위 수식에서 A1:A3*B1:B3를 선택하고 미리 결과값을 확인하는 F9키를 누르면 둘다 똑같이 {12;6;2}가 뜨지만 결과값이 다릅니다. 또, SUMPRODUCT함수라고 배열수식과 같은 기능을 하는 함수가 있는데, 이 함수는 함수자체적으로 범위관 곱하기를 해서 그 결과값들을 마지막에 모두 더해주는 기능을 가지고 있습니다.

그림2처럼 그림1과 그림2의 결과값은 같다는걸 확인하실 수 있으실 겁니다.

그림2. SUMPRODUCT 함수의 결과값

하지만 SUMPRODUCT함수에서도 배열 수식임을 선언해야 할 때가 있는데요 SUMPRODUCT 함수와 IF함수가 같이 있을 경우 입니다. IF함수의 첫 번째 인수 값은 TRUE 또는 FALSE 중 하나의 결과만 내야하는데요

 

그림3. SUMPRODUCT와 IF함수 적용

A1:A3>2의 조건은 {4(TRUE);3(TRUE);FALSE}의 세 개의 결과를 나타 내고 있고 두 번째 인수 값도 세 개의 결과를 내고 있습니다. 하나가 아닌 여러 개의 값을 내는 것은 변칙이므로 수식이 이해가 가능하게 Ctrl + Shift + Enter를 입력하여 배열상수를 선언해주셔야 합니다.

그림3의 왼쪽그림은 F9를 눌러 결과값을 확인하기 전인데요 A1,A2,A3의 값이 4,3,2로 2보다 큰수는 A1(4)와 A2(3)뿐이므로 결과값에 {4(TRUE);3(TRUE);FALSE}가 나타나는겁니다.

A3셀의 값인 2가 FALSE로 나왔으므로 결과값은 4x3의값인 12, 3x2의 값인 6을 더한 12+6의 18입니다.

2보다 크다는 조건을 걸었으므로 FALSE가 나온 A3셀의 값인 2는 더하지 않습니다. 

그림4. 배열상수 적용과 미적용의 값차이

또, 배열상수를 미적용한 경우 20이라는 오류값이 나오기 때문에 배열상수를 적용해 정확한 값인 18을 찾으실 수 있습니다. 위에 설명드린 것과 같이 하나가 아닌 여러 개의 값을 내는 것은 변칙이므로 수식이 이해 가능하게 배열상수로 입력하는 것입니다.

내가 작성한 수식이 배열수식인지 아닌지 알 수 있는 대략적인 방법은 해당 함수의 각 인수로 올 수 있는게 한 개인지 여러 개인지를 알면 되는데 VLOOKUP함수를 예로들었을 때 VLOOKUP함수의 인수로는 다음과 같이 구성되는데

=VLOOKUP(한 개, 여러 개, 한 개, 한 개)이 함수인수의 '한 개'자리에 여러개의 자료가 오면 이때는 배열 수식이고 Ctrl + Shift + Enter로 입력을 완료해야 합니다.

※ '한개'란 하나의 값이나 셀이 되며 '여러 개'란 배열이나 셀의 범위가 됨

단순하게는 어느 정도 배열 수식을 활용할 수 있다고 생각할 때 Enter로 입력했는데 값이 제대로 나오지 않는다면 

Ctrl + Shift + Enter를 눌러 배열 수식의 형태로 입력해보실 수 있으실 겁니다.