배열과 배열 수식 이해하기

11.9K

엑셀에서 가장 흥미롭고 강력한 기능 중의 하나가 바로 배열(Array)입니다. 엑셀을 제법 하는 사람들도 배열을 완벽하게 이해하고 사용하는 경우는 드물 정도로 어려운 부분이기도 하구요. 여러분들이 엑셀의 배열을 이해하고 활용할 수 있다면 남들은 흉내 내지도 못할 마술 같은 수식을 만들 수 있답니다. 이번 강좌는 조금 길긴 하지만 차례 차례 따라오세요. 그 어디에서도 배우지 못한 배열에 대해 쉽고 명확하게 알려드리겠습니다.

배열이란게 도대체 뭘까?

배열(Array)은 여러 항목을 담아둔 집합체입니다. 일반적으로 같은 성격의 정보들을 한 곳에 모아 둔 것으로 이해하면 되는데, 배열에 포함된 각 항목(정보)들은 서로 관련이 있을 수도 있지만 아무 상관이 없을 수도 있습니다. 엑셀에서는 1차 배열, 2차 배열을 사용할 수 있고 각각 행과 열을 참조해서 배열이 만들어지게 됩니다. 1차 배열은 하나의 행이나 하나의 열에 저장되고, 2차 배열은 행과 열의 범위에 모두 저장됩니다. 엑셀에서는 3차 배열은 지원하지 않습니다.

Tip
배열이 반드시 셀에 저장되는 값일 필요는 없습니다. 배열 수식을 이용할 때 메모리에 임시로 올려놓고 사용할 수도 있답니다. 그리고 워크시트 수식에서는 3차원 배열을 사용할 수 없지만 VBA 프로그래밍에서는 3차원 배열을 사용할 수 있습니다.

배열 수식은 이러한 배열을 수식에 활용하고 원하는 값을 찾아내는 수식입니다. 그런데 왜 배열을 사용할까요? 뒤에서 하나씩 배우겠지만 배열을 이용하면 여러 단계를 거쳐야하는 계산을 한 번에 끝낼 수 있고, 경우에 따라서는 배열 수식이 아니면 해결할 수 없는 경우도 있습니다. 하나씩 별개로 계산해야하는 것을 전체 집합체에 담아서 한 번에 계산을 할 수 있으니 당연하겠죠?

이러한 배열 수식은 한 셀에 입력될 수도 있고 여러 셀에 입력될 수도 있습니다.

여러 셀에 입력되는 배열 수식

이제 배열에 대해 실제 예를 보면서 알아보도록 합시다. 아래 그림에서 배열 수식을 사용하지 않는 경우 각 제품의 판매액 합계를 구하려면 어떻게 하면 될까요? 앞에서 배운대로 D2셀에 =B2*C2를 입력한 후 자동 채우기로 값을 채우면 되겠죠?

배열 수식을 이용하면 이렇게 각 셀에 수식을 입력하는 방식이 아니라 하나의 수식으로 각각의 합계를 한 번에 구할 수 있습니다. 다음 과정을 따라해 보세요.

① 결과를 구할 셀들(D2:D7)을 먼저 선택 선택합니다. 한 셀에 하나 이상의 값을 표시할 수 없기 때문에 합계를 구할 셀들을 미리 선택하고, 이 셀에 배열의 계산 값을 표시하도록 하는 것입니다.

② 다음 수식을 입력합니다. =B2:B7*C2:C7

③ CTRL+SHIFT+ENTER를 입력합니다. 일반적인 수식은 ENTER를 입력해서 수식을 완성하지만, 배열 수식은 CTRL+SHIFT+ENTER를 입력한다는 것을 명심하세요.

D2:D7에 원하는 합계 값이 구해졌습니다. 수식입력줄을 보면 {=B2:B7*C2:C7}와 같이 수식이 집합기호로 둘러싸인 것을 확인할 수 있습니다. 엑셀은 일반 수식과 구분하기 위해서 배열 수식은 이렇게 {} 집합기호로 표시한다는 것도 꼭 명심해 두세요.

한 셀에 입력되는 배열 수식

이번에는 한 셀에 입력되는 배열 수식에 관해 알아보겠습니다. 우선 아래 그림을 한번 보시죠. 앞에서 다룬 예제와 비슷하지만 각 제품별 합계를 구한 D열이 없고, C9셀에 총 판매액을 한번에 구한 것이 차이가 있습니다.

이런 자료구조가 있다고 할 때 여러분들은 어떻게 총 판매액을 구하시겠어요? 여러 단계를 거치는 계산 방법도 있겠지만 배열 수식을 만들면 C9셀에 하나의 수식만 입력해서 원하는 값을 찾을 수 있습니다. B2:B7을 하나의 배열로, 그리고 C2:C7을 또 하나의 배열로 생각해서 곱하고 합계를 구하면 한 번에 계산이 되겠죠? C9셀에 =SUM(B2:B7*C2:C7) 수식을 입력한 후 CTRL+SHIFT+ENTER를 입력해서 배열 수식으로 입력해보세요. 다음과 같이 원하는 값이 한 번에 계산이 된답니다.

원리 이해

위 예제의 수식은 B2:B7에 저장된 배열과 C2:C7에 저장된 배열을 각각 곱해서 새로운 배열을 만드는데 이 배열은 셀에 저장된 것이 아니라 메모리에 임시로 저장되게 됩니다. 마지막으로 SUM 함수가 메모리에 있는 이 배열의 값을 모두 더해서 원하는 값을 구하게 되는 것입니다.

배열을 이용하지 않는 수식의 계산

다음 그림과 같은 데이터가 있습니다. 이 데이터 구조에서 서울 지역의 전체 판매량의 합계를 구해보도록 하겠습니다. 지금까지 배운 엑셀의 기능만으로도 매우 쉽게 합계를 구할 수 있지만, 배열 수식의 장점을 더 이해하기 위해서 가장 기본적인 방법으로 수식을 만들어보겠습니다.

① D2 셀을 선택한 후 =IF(A2=”서울”,C2,””)를 입력합니다. 이 정도 수식은 이제 바로 이해가 되시죠? A2셀의 내용이 “서울”이면 C2의 값을 반환하고, 아닐 경우 아무것도 표시하지 않는 수식입니다. 그 후 자동 채우기를 통해 D7까지의 값을 모두 구합니다.

② D8 셀에 =SUM(D2:D7)을 입력해서 합계를 구합니다.

원하는 합계는 구했지만 굉장히 비효율적이라는 생각이 드시죠? 물론 SUMIF나 필터링 등을 통해 계산하는 방법도 있지만 간단하게 배열식을 이용해 한 번에 계산을 끝낼 수 있습니다.

배열식을 이용한 계산

실제로 엑셀은 내부적으로 계산을 할 때 모두 배열로 처리를 합니다. 엑셀 워크시트의 구조 자체가 행/열로 이루어져 있으므로 이를 계산에 응용할 때 배열로 처리를 하는 것이죠. 중급-1장에서 간단히 배우기는 했지만 실제 확인을 해볼까요?

① 위에서 값을 구한 D8셀을 선택한 후 수식입력줄을 봅니다. =SUM(D2:D7)라는 수식이 보이시죠? 이 수식 중에서 D2:D7 부분만 마우스로 범위를 선택하고 F9 키를 눌러보세요. F9키는 수식의 결과 값을 볼 때 사용하는 단축키입니다. 다음 그림처럼 수식의 결과 값이 보여집니다.

자 이제 위와 같은 여러 단계를 거치지 않고 바로 배열식을 입력해서 원하는 값을 바로 구해봅시다.

② E2 셀을 선택한 후 =SUM(IF(A2:A7=”서울”,C2:C7,””))를 입력합니다. 이 수식은 앞에서 계산한 수식을 하나로 합친 수식인데, IF함수의 인자로 단일 셀이 들어가는 것이 아니라 배열 인자가 들어가는 것이 차이가 있습니다.

③ 수식을 입력한 후 그냥 ENTER를 누르시면 안되겠죠? 배열 수식을 만들었으므로 CTRL+SHIFT+ENTER를 같이 눌러서 식을 완성합니다.

배열식을 이용하면 훨씬 간단하게 결과 값을 찾을 수 있다는 것을 알 수 있죠? 그렇다면 이번에도 F9키를 이용해서 내부적으로 어떤 계산 결과 값이 있는 지 확인해보죠.

④ 수식 입력줄에서 A2:A7을 선택한 후 F9키를, 그리고 C2:C7을 선택한 후 F9키를 눌러보세요. 아래와 같이 엑셀 내부적으로 배열 값을 인자로 참고해서 계산한 것을 알 수 있습니다.

참고(Note)
아래와 같은 배열이 있을 때 D2셀을 선택하고 DEL을 눌러 셀의 내용을 지워보겠습니다.  “배열의 일부분을 변경할 수 없습니다.”라는 오류가 표시되면서 D2셀의 내용을 지울 수가 없습니다. 배열이기 때문이겠죠? 현재 C1:F1에 입력된 “1234”라는 값은 각각 별개의 값이 아니라 모두 한 그릇에 담겨져 있는 배열이기 때문에 배열의 일부분만을 지울 수 없는 것입니다.

배열 상수

일반적으로 엑셀에서의 배열 수식은 셀의 범위를 이용해서 계산하는 경우가 많습니다. 배열이 반드시 셀의 범위에 저장될 필요는 없는데, 계산을 위해서 메모리에 배열로 선언해서 계산식에 포함할 수도 있습니다. 이렇게 셀에 저장되지 않고 메모리에 임시로 저장하는 배열을 배열 상수라고 하고, {}로 묶어서 수식의 인자로 사용할 수 있습니다. 다음 수식을 한번 보실까요?

=SUM({1,2,1,2,1})

위 수식은 SUM함수의 인자로 배열상수를 이용했습니다. 결과 값은 7이 나오겠죠? 앞서 배운 배열 수식과는 다르게 수식 전체가 {}로 둘러싸인 것인 아니라 수식의 인자만 배열로 선언된 것입니다. 따라서 위 수식은 배열 수식은 아니고 배열 상수를 인자로 사용한 수식입니다. 그런데 위 수식은 우리가 앞에서 배운 =SUM(1,2,1,2,1)과 결과가 똑 같이 나오는데 왜 저렇게 사용할까 하는 생각이 드시죠? 그렇다면 아래 수식을 한번 보시죠.

=SUM({1,2,3,4}*{5,6,7,8})

이 수식은 메모리에 새로운 배열을 임시로 만들고 이것은 SUM 함수의 인자로 넘깁니다. 새로 만들어진 배열은 {5,12,21,32} 이고, 만약 배열을 사용하지 않는다면 =SUM(15, 26, 37, 48) 이라는 수식을 만들어야 하겠죠. 배열 상수는 수식에서 셀 범위와 조합해서 사용할 때 더 효과를 발휘합니다. 아래 수식은 A1:D1의 셀에 각각 1,2,3,4를 곱해주는 수식입니다.

=SUM((A1:D1)*{1,2,3,4})

만약 여러분들이 배열과 배열 상수를 모른다면 =SUM(A11, B12, C13, D14)와 같이 수식을 만드셔야 합니다. 차이가 확연하게 느껴지시죠?

Tip
배열 수식을 직접 만들지 않아도 간단한 형식의 배열을 인수로 계산하는 함수를 이용하는 방법도 있습니다. 위에서 배운 수식도 =SUMPRODUCT({1,2,3,4},{5,6,7,8})로 같은 값을 구할 수 있습니다만, 함수는 정해진 인자의 형식을 따라야 하기 때문에 다양한 방법으로 배열 수식을 만드는 데는 한계가 있습니다. 배열을 인자로 가지는 함수가 있는 반면 결과 값을 배열로 돌려주는 함수도 있습니다. 이런 함수에는 FORECAST, FREQUENCY, GROWTH, LINEST, LOGEST, MINVERSE, MMULT, TREND가 있습니다.

어떠세요? 조금 아리송 한 부분도 있지만 배열이 무엇이고 왜 사용하는지 아셨죠? 다른 강좌 에서는 배열 함수를 다시 배우게 될겁니다. 이 정도의 기본 원리와 배열 수식을 사용하는 방법에 대해 알고 계신다면 크게 어렵지 않게 배열함수도 사용할 수 있답니다.