엑셀-배열수식이란?

[미루나무]

엑셀에서의 배열수식이란 한마디로 잘라 말하기 어렵지만, 데이터를 나열(정렬하여 펼쳐 놓음.)해 놓고 정해진 규칙에 따라 일괄 계산하여 그 결과를 돌려주는 수식...이라고 나름대로 정리를 해 볼 수가 있겠군요.

하여간, 엑셀에서 일반 수식으로는 계산해낼 수 없는 정보를 셀 간의 배열과 조합을 통해 계산해 낼 수 있는, 기능은 우수하지만 사용하기가 조금 복잡하고 까다로운...알고 보면 그다지 어렵지 않은...수식을 사용하는 방법이 있다는 겁니다.

배열에 대해서 저도 나름대로 공부해 보려고 책도 찾아보고, 자료도 찾아봤지만 알파에서 오메가까지 정리된 내용을 찾을 수가 없었습니다. 그래서 토막 지식들과 사용 예들을 접하면서 나름대로 알게 된 내용을 기초로 정리를 해 봅니다. 하지만 어디까지나 이것은 제가 알고 있는 범위 내에서의 체계적 정리이지 역시 하나의 토막지식에 불과할 수도 있습니다. 이점을 미리 말씀 드립니다. 그럼 본론으로 들어가 보겠습니다.

먼저, 배열 수식을 설명 드리기 전에 몇 가지 예비지식이 필요합니다. 그것은....

1. 배열수식은 함수가 아니라 수식이다.

   엑셀을 웬만큼 사용하시는 분 들도 수식(Formula)과 함수(Function)를 혼동하는 경우가 있으신데요...수식은 등호, 부등로, 상수, 변수, 함수, 셀주소 등으로 조합된 전체를 의미하는 것이고, 함수는 특정(단위) 기능을 수행하는 말 그대로 Function입니다.(SUM(), AVERAGE(),IF() 등)...따라서 배열 수식은 수식 내에 다양한 함수 기능을 포함할 수 있는 수식이라는 것입니다.

물론 함수 중에는 배열의 원리를 이용하여 기능에 포함시킨 함수도 있지요. 그 예가 바로 SUMPRODUCT() 함수입니다. 어떤 사람들은 이것을 "배열함수"라고 하는데, 엄격히 정의하면 배열함수라기 보다는 "배열의 특성이 반영된 기능을 수행하는 함수"라는 표현이 맞겠습니다.

2. 배열수식을 표현(입력)하는 방법 ; 중괄호-{...}- 를 사용한다.

   ...웬 중괄호?....어떻게 사용?...하지만 그냥 중괄호라고 하는 게 있다고만 알고 넘어가세요..

3. Ctrl + Shift + Enter 키를 사용한다.

   일반 수식을 입력할 때는 수식 입력 줄에다 수식을 입력하고 Enter키를 치면 입력이 되지만, 배열 수식을 입력할 때는 Ctrl + Shift 키를 누른 상태에서 Enter 키를 칩니다.

   ...왜 그래야 되는데?...그래야 배열수식이라고 엑셀이 인식 하니까...

4. 배열에는 행(가로)과 열(세로)이 있으며, 행 혹은 열의 수가 서로 맞아야 한다.

   이건 이렇습니다. 학교 다닐 때 아침조회를 하기 위해서는 운동장에 정렬을 하잖아요....초등학교 때는 "앞으로 나란히!"를 했었고...중,고등학교 때는 "좌우로 나란히!"를 했던 기억이 나는군요...군사문화에서부터 비롯된...하여간...정렬에는 오와 열이 필요하다는 거죠...

그리고, 행이나 열이 배열 내에서 중복될 때, 행은 행 끼리 숫자가 맞아야 하고, 열은 열끼리 숫자가 맞아야 합니다....맞지 않으면 에러가 발생됩니다.

5. 배열의 연산은 논리연산의 원리가 적용된다.

   논리연산은 또 뭐야?.....논리 값은 참(TRUE)과 거짓(FALSE)이 있습니다. 전산에서 이 논리 값이 참은 1, 거짓은 0이라는 사실은 알고 계신가요?....해서 논리연산이라고 하는 것은 논리합과 논리곱이 있는데...참과 거짓을 연산할 때 1과 0의 연산 논리에 따른다는 것입니다. 배열수식에서는  논리곱이 주로 사용됩니다. 즉 TRUE*TRUE=TRUE, TRUE*FALSE=FALSE, FALSE*FALSE=FALSE 등이 논리 곱이며, TRUE+TRUE=TRUE, TRUE+FALSE=TRUE, FALSE+FALSE=FALSE 등이 논리합입니다.

자, 이제부터 본격적으로 사례를 보면서 배열 수식을 설명해 보겠습니다...

그림처럼 A1~C3 (9셀)을 드래그하여 선택하신 후 수식 입력 줄에

={"1","2","3";"4","5","6";"7","8","9"}

를 입력(복사)하시고 Ctrl + Shift + Enter 키를 쳐 보세요.

어떻습니까? 아래 그림처럼 입력이 되었는지요?

이렇게 해 놓고 보니 9개의 각 셀에는 수식은 모두 같은 것이 입력되어 있는데

결과는 모두 다르게 보이는 것을 알 수 있습니다.

이상하게도 자세히 보니 중괄호가 하나 더 생겼지요?

{={"1","2","3";"4","5","6";"7","8","9"}}

제가 처음 이 현상을 경험했을 때는 고압전류에 감전된 듯한 느낌을 받았던 기억이 나는군요. 여러 분은 어떤 느낌을 받으셨는지요?

사실 이 내용 속에는 많은 의미가 내포되어 있습니다.

{={"1","2","3";"4","5","6";"7","8","9"}} - 바깥 중괄호, 배열수식으로 입력(Ctrl+Shift+Enter) 되었음을 의미

{={"1","2","3";"4","5","6";"7","8","9"}} - 안의 중괄호, 수식 내에 배열이 있음을 의미

{={"1","2","3";"4","5","6";"7","8","9"}} - ;(세미콜론)이 행을 구분함 - 세로

{={"1","2","3";"4","5","6";"7","8","9"}} - ,(콤마)이 셀을 구분함 - 가로

이 때 중요한 것이 가로로 3개, 세로로 3개씩의 배열이 있는데...각 배열마다 3개씩이 맞아야 한다는 것입니다. 이 중 하나라도 이가 빠지면 에러가 발생됩니다.

물론 한 열이나 한 행이 모두 빠져서 2*3열의 배열이나 3*2열의 배열이 되는 것은 문제가 없습니다.

그래서 뭘, 이 복잡한 걸 어디에다 써 먹는데?..

이제 좀 실용적인 예를 한가지 들어볼까요?

지식인에 보면 엑셀 관련해서 이런 질문들이 가끔 올라옵니다.

학점을 산출할 때 다음과 같은 기준에 따라 자동으로 산출하는 수식이 어떻게 됩니까?

     점수>=90 ----- A

90> 점수 >=80 ---- B

80> 점수 >=70 ---- C

70> 점수 >=60 ---- D

60> 점수 ---------- F

여러 분은 어떻게 하시겠습니까?

초급 사용자라면, 위 학점(1)의 방법을 주로 사용하시겠지만, 보시는 바와 같이 수식이 복잡하고 산만합니다. 또한 기준이 변경되었을 때 수정이 어려운 문제가 있지요. 조금 더 경험이 있는 사용자라면 아시겠지만, IF()함수는 중복해서 사용할 수 있는 한계가 있습니다....학점의 종류가 달랑 5가지 뿐이기 때문에 불편하나마 그럭저럭 사용할 수가 있지만, 만약 종류가 20가지, 30가지 되는 분류를 IF()함수로 구분해서 연산하기 위해서는 그 중복 사용 한계(아마...8번인가...) 때문에 대분류를 정해서 이중구조로 만들어 비교하는 등의 복잡한 방법을 사용해야 합니다. 또한 IF()함수의 경우 데이터 처리 건수가 많을 때는 연산하는데 시간도 많이 걸리는 단점이 있습니다.

그런데...학점(2)의 경우는 어떻습니까? 매우 간단하지요.....VLOOKUP() 함수를 알고 계신다면 말입니다. 구분해야 할 종류가 많을 때에 더욱 적합하지요...그런데 한가지 문제가....기준표를 별도로 만들어야 한다는 것입니다. 물론 이것이 기준 변경시 수정이 용이한 등의 장점이 될 수도 있지만, 이런 표를 만들기가 곤란하거나 귀찮을 경우도 있지 않겠습니까?

이럴 때 쉽게 해결하는 것이 바로 배열수식입니다.

학점(3)의 방법은 VLOOKUP() 함수의 사용법이 학점(2)의 방법과 동일합니다. 다만,

기준표를 별도로 만들지 않고, 수식 내에 기준 테이블(배열)을 가지고 있다는 것이지요...B2셀에다 =VLOOKUP(A2,{0,"F";60,"D";70,"C";80,"B";90,"A"},2) 를 입력 후 아래로 채우기 하시면 됩니다. 이제 중괄호 속의 테이블이 머리 속에 그려 지시지요?...가로 2줄, 세로....5줄의 테이블이요...아니라구요? 그러시면 위에 1,2,3...9 까지 입력하는 내용이 있는 곳으로 다시 올라가 보시기 바랍니다.

이 수식 역시 수식 내에 배열(테이블)을 가지고 있을 뿐 배열수식으로 입력한 것은 아닙니다. 아래를 보시지요....

어떻습니까?

학점(4)의 경우 모든 셀에 입력된 수식({=VLOOKUP(A2:A8,{0,"F";60,"D";70,"C";80,"B";90,"A"},2)})은 동일하지만 결과는 제각각 이지요? 이게 제대로 된 배열수식이지요....바깥쪽의 중괄호가 있으니...당연히 (Ctrl+Shift+Enter) 로 입력되었음을 아시겠지요?...또한 입력하기 전에 입력하고자 하는 범위 전체(B2:B8)를 선택 후 동시에 입력해야 합니다.

그렇게 하시면....{=VLOOKUP(A2:A8,{0,"F";60,"D";70,"C";80,"B";90,"A"},2)}...즉 A2:A8 부분 중 각 셀에서 참조 값을 하나씩(A2, A3, A4...) 저절로 알아서 참조하게 되는 것이지요. 이런 것이 배열 수식입니다. 즉 동일한 수식으로 동시에 계산을 해서 셀마다 적절한 값을 알아서 취하는 형식...얼마나 편리합니까? 데이터가 엄청 많을 때에도 컴퓨터가 빠르게 계산을 할 수 있는 겁니다.

자, 다음은 좀 더 실용적이고 유용한 사용 예를 보겠습니다. MT를 가기위해 회비를 걷는 대장을 만들었을 때, 그 대장을 기준으로 회비를 낸 인원수와 금액을 집계하는 표를 만드는데 배열 수식을 사용한 사례입니다.

위 예처럼 인원이 10여명이면 집계고 뭐고 그냥 암산으로 하면 끝나겠지만, 과별 인원이 수 십명 씩, 전체가 100단위를 넘어가면 암산으로 한계가 좀 있겠지요? 이럴 때 배열수식을 알면 아주 편리합니다. 위 B18셀에는

{=SUM(($A$2:$A$13=$A18)*($C$2:$C$13=B$17)*($D$2:$D$13>0))}

이 들어있습니다.

즉, 각 셀의 조건을 취합하고자 하는 조건과 비교하여, 참이냐, 거짓이냐를 따져서 논리곱과 논리합을 연산하여 그 결과를 돌려주는 것이지요.

즉, B18 셀은 연극과이면서 여학생이고 회비를 낸(>0) 사람의 인원 수를 구하는 것이니까요....

즉...........학과=연극....................남여=여.....................회비납부>0

{=SUM(($A$2:$A$13=$A18)*($C$2:$C$13=B$17)*($D$2:$D$13>0))}

를 각각 의미합니다. 당연히 (Ctrl+Shift+Enter)로 입력한 것이구요.

이 수식이 가지고있는 논리 연산을 좀 더 자세히 설명 드리면...

위 수식은 결국,

=SUM(({TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE})*({FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE})*({TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE}))

를 의미하고 있습니다.

이것은 즉,

=SUM(({1;0;0;1;0;0;1;0;0;1;0;0})*({0;1;0;0;1;1;0;1;0;1;1;1})*({1;1;1;0;1;1;0;0;1;1;1;0}))를 의미하는데...이것을 다르게 표현(위에서 가로세로 말씀 드렸죠?)하면 이렇습니다...

1	0	1	0
0	1	1	0
0	0	1	0
1	0	0	0
0	1	1	0
0	1	1	0
1	0	0	0
0	1	0	0
0	0	1	0
1	1	1	1
0	1	1	0
0	1	0	0
			1

각 행 별로 곱하기(논리곱)이니까...0이 하나라도 있는 행은 0이 되고..모두 1인 행 만 1이 되지요...

그 열을 모두 합하면(SUM() 함수) 최종적으로 1이 되는 겁니다.

뭔 소리인지 도무지...에라, 여기서 포기........하고 접어 버리시는 분은 영원히 상급자로 올라가실 수 없다는 사실...

하지만 이 내용을 이해하셨다면, 여러 분도 이제 엑셀에 대해서는 상급자로 인정받으실 수 있는 날이 멀지 않았습니다.

이제 마지막으로 위 예에서 학과별, 남여별 금액을 합산하는 방법을 보겠습니다.

B18셀의 수식을 보면,

=SUM(($A$2:$A$13=$A18)*($C$2:$C$13=B$24)*($D$2:$D$13))

이 들어갑니다.

위 인원수를 집계하는 수식과 다른 부분이 빨간색 부분인데, 논리가 없이 그냥...숫자들을 가져다 놓는 겁니다. 즉 논리연산 마지막 부분에서...

1	0	20000	0
0	1	10000	0
0	0	20000	0
1	0		0
0	1	10000	0
0	1	10000	0
1	0		0
0	1		0
0	0	20000	0
1	1	10000	10000
0	1	10000	0
0	1		0
			10000

이렇게 되는 것이지요....

위 배열수식 대신에 SUMPRODUCT() 함수를 사용하여 개수(인원수) 및 금액을 취합할 수도 있습니다만, 배열에 관한 설명이므로 생략하겠습니다. 하지만 SUMPRODUCT() 함수는 기능이 뛰어나서 상당부분의 배열식을 대체할 수가 있다는 사실을 염두에 두시고 별도로 공부를 하셔서 필요에 따라 적절히 사용하시기 바랍니다.

휴~이제 제 설명은 이정도로 마칠까 합니다...

자세히 설명을 드린다고 했는데...잘 전달이 되었는지 궁금합니다...

사실 배열 수식의 활용 사례는 무궁무진 합니다. 더욱 복잡한 경우도 많이 있고요..

하지만 기본 원리는 제가 말씀 드린 내용을 크게 벗어나지는 않습니다.

모쪼록 여러 분의 엑셀 실력 향상에 많은 보탬이 되길 바랍니다...