[엑셀 활용] 엑셀 배열 수식 배우기 - 먼저 맛보기

컴퓨터활용능력 1급을 위해 필요한 엑셀(EXCEL)의 기능 중 하나이며 엑셀을 사용하는 많은 이가 있는지 조차 모르고 있는 엑셀 기능이 있습니다. 엑셀 배열 수식입니다. 물론 일반적인 엑셀 수식으로 충분하기 때문이기도 합니다. 또 어려워서라기보다 사용해 본 적이 없기에 멀어진 것이 엑셀 배열 수식입니다. 이를 익혀 두시면 보다 발전된 엑셀(EXCEL) 실력을 갖게 될 것입니다.

엑셀 배열 수식, 어렵게 생각하면 한 없이 어렵고 쉽게 생각하자면 너무도 쉬운 기능입니다. 생각을 어떻게 바꾸느냐가 이를 익히는 관건입니다(단일 셀 대신 범위의 사용이 이해되어야 합니다). 대부분의 엑셀 수식은 하나의 셀을 수식의 요소나 함수의 인수로 사용합니다. 또 범위를 사용하는 함수가 있다해도 단순 수식에서는 범위를 사용하지 않습니다. =A1:A3 + B1:B3 형태의 수식은 보통 사용하지 않는다는 것입니다.

또 하나의 예로 =SUM(A1:C2) 이라는 수식을 엑셀에서 사용합니다. 이는 A1 셀에서 C2 셀까지의 모든 셀의 합을 구합니다. 이런식의 참조가 아니고는 함수에서 범위(곧 배열)를 사용하는 방법을 많은 이가 잘 모를 것입니다. (예제1)

엑셀 배열 수식 - 맛보기

[그림1] 엑셀 배열 수식 맛보기 예제

자, 이제 엑셀 배열 수식입니다.
=SUM( IF(A1:C2>10, 1, 0) )

이런 수식이 있습니다. 이렇게 범위가 사용된 수식은 어떤 결과를 가져올까요? 엑셀에서 이 수식을 입력하고 그대로 엔터키(ENTER)를 친다면 이는 #VALUE! 오류를 반환합니다. IF 함수에서 10과의 비교로 사용된 인수가 범위이기 때문입니다. 10 이라는 하나의 값과 범위를 비교할 수 없기에 인수가 잘못 되었다는 오류인 #VALUE! 오류가 출력되는 것입니다. (예제2)

[그림2] 엑셀 배열 수식 - IF 함수의 셀 범위 비교 #VALUE! 오류

그러나 위의 수식을 입력하고 엑셀 배열 수식을 완성하는 CTRL+SHIFT+ENTER 를 입력한다면 경우는 달라집니다. 수식의 앞뒤에 중괄호( {} ) 가 삽입되면서 엑셀 배열 수식으로 적용됩니다. A1:C2 범위를 하나의 셀 마다 위의 IF 수식에 적용하는 것입니다. 그렇게 IF 함수의 결과가 배열로 반환되어 SUM함수의 인수로 전달 되면 전달된 배열의 요소들이 더해지는 것이지요.

[그림3] 엑셀 배열 수식 - IF 함수의 비교 결과값 배열

이는 10보다 큰 값의 수를 카운트하는 것이 됩니다. 이것이 엑셀에서 말하는 배열 수식입니다.  (예제3)
보다 자세한 예제는 다음번 게시물에서 살펴보고 이번 게시물에서는 왜 이런 결과를 반환하는지 이유를 살짝 배워 봅니다.

엑셀 배열 수식 - 셀 범위와 배열

먼저 배열을 배워봅니다. 엑셀에서의 배열은 여러 값들을 모아 두고 그 전체를 하나의 값으로 사용하는 것입니다. 셀 A1:A3 에 값이 들어 있습니다. A5에서 SUM 함수를 사용하여 이 합을 구합니다. =SUM(A1:A3) 수식입니다. 그 범위 값이 10,20,30 이라면 이 결과는 60 입니다.

[그림4] 엑셀 배열 수식 배우기 - 셀 범위와 배열

1) 이 수식이 입력된 셀에 포인트를 두고
2) 엑셀 함수 마법사 창을 엽니다.
3) 첫번째 인수의 결과를 보시면 {10;20;30} 입니다. 이것이 엑셀의 배열 형태입니다.

위 3)은 A1:A3 셀의 3개의 값이 하나의 인수(Number1) 안에 사용되기 위해 배열 형태를 갖는다는 것입니다. 배열은 범위의 값들이 분리되어 내부적으로 저장됩니다. 이것이 엑셀 배열 수식을 사용하게 하는 핵심입니다. 여러 셀을 계산에 사용할 수 있기때문이 아니라 이렇게 내부적으로 기억되기에 계산이 편리해 질 수 있어 엑셀 배열 수식을 사용합니다. 이해가 잘 안 될 수 있습니다. 엑셀 배열 수식 추가 포스팅을 예상하고 있습니다. 여기에서 [사용 예제]를 보시면 조금씩 이해 될 것입니다.

아, 그리고 엑셀(EXCEL) 배열의 형태는 중괄호( { } )로 묶이고 콤마( , ) 로 열 구분을 하고 세미콜론( ; ) 으로 행 구분을 합니다. A1:A3 는 행 방향이기에 세미콜론( ; )으로 구분되어 있는 것입니다.

첫번째 인수 A1:A3 대신 {10;20;30} 을 전달하여도 동일한 결과를 가져온다는 것입니다.
즉 =SUM(A1, A2:A3, 10, {20,30}) 의 형태의 수식으로 단일 값, 단일 셀이 하나의 인수로, 셀 범위 혹은 배열이 하나의 인수로 전달 될 수 있다는 것입니다. 이 범위는 배열의 요소로 분리되어 계산에 사용되는 것입니다.

엑셀 배열 수식의 이점

이 배열 수식의 이점은 몇 번의 수식 계산 과정을 거쳐야 결과를 얻을 수 있는 것을 한번의 수식으로 해결할 수 있다는 것입니다.  위[그림1] 예제3 의 수식을 배열 수식을 이용하지 않는다면 ( A9:C10 의 범위에 아래 1번 계산을 합니다 )

1) A1:C2 범위의 셀들을 각각 10 보다 큰지 비교하여야 합니다.  =IF( A1>10 , 1 , 0)
2) 채우기 핸들을 사용 수식 복사를 하던지 각각 수식을 입력하여야 합니다.
3) 1) 2) 번의 결과들의 합을 구합니다.  =SUM( A9:C10 )

물론 다른 함수를 사용한다면 쉽게 해결될 수 있는 물음입니다. 하지만 동일 함수를 사용한다는 가정하에 엑셀 배열 수식의 기능을 보는 것입니다.

이상은 엑셀 배열 수식에 대한 간략한 소개입니다. 말이 안 될 수 있지만 너무도 쉽게 말하자면 하나의 셀 대신 셀 범위를 계산에 이용하는 것이 엑셀 배열 수식입니다. 단일 값 대신 여러 값들을 배열로 묶어 계산식에 사용하는 것이 배열 수식이라는 것입니다.
다음 게시물에서 엑셀 배열 수식의 사용 예제를 살펴 봅니다.

마늘빵.