배열수식

지난번 엑셀 강좌의 마지막은 배열수식으로 하려 했습니다만, 저도 잘 모르고, 이것을 실제 써 먹을 수 있는 일이 얼마나 있을까해서 망설였습니다.
그러다 뜻밖에 시간이 나게 된 오늘 배열수식에 대해 잠시 알아보는 시간을 가질까 합니다.

위 그림은 연이율5%일때 빌린 돈(원금)의 액수와 빌린 기간(햇수)에 따른 이자를 계산해주는 표입니다. 원금X햇수X이율 하면  총 지불해야할 이자가 나오겠지요?
그런데 이 수식을 c3셀에 넣고 아래로 끌기하면 그 아래 셀에서는 0으로 나옵니다. 오, 그럼 이자를 지불하지 않아도 되는 건가요? ㅋㅋ

C6셀을 클릭해보시면 끌어채우기를 하면서 수식이 변경된 것을 알 수 있습니다. 지난 번에 살펴보았던 대로, 절대주소와 상대주소를 적절히 사용하여 수식을 다시 써보면,

위와 같이 됩니다. 햇수는 가로로 변하고(C,D,E...) 세로는 2열에서 변하지 않아야 하므로 C$2와 같이 써야하고
원금은 세로로 변하고 (1,2,3..) 가로는 B열에서 변하지 않아야 하므로 $B3과 같이 써야합니다.
이율은 항상 B1을 가리키야하기 때문에 $B$1과 같이 써야합니다.

그런데, 상대주소와 절대주소가 아무래도 헷갈립니다. 생각을 잘 하셔야지, 틀리기 쉽습니다.

이제 배열수식을 이용하는 방법을 살펴보겠습니다. 값을 구할 범위 전체를 선택하고, 등호(=)를 입력하여 수식을 다음과 같이 입력합니다.

그리고 CTRL+SHIFT+ENTER를 누릅니다. 선택했던 모든 셀에 값이 입력되고, 수식창을 보시면 입력한 수식의 앞뒤에 중괄호({})표시가 들어가 있는 것을 볼 수 있습니다. 이 중괄호 표시가 바로 이 수식은 배열수식으로 입력되었다는 것을 말해주는 표시입니다. 배열수식은 반드시 CTRL+SHIFT+ENTER를 눌러야 제대로 동작합니다.

배열수식의 특징은 어느 셀에서나 수식이 같다는 것입니다.
E7셀을 클릭해보시면,C3셀에 들어있는 수식과 똑같은 수식이라는 것을 알 수 있습니다. 따라서 상대주소와 절대주소를 섞어쓰는 신경을 쓸 필요가 없어서 편리합니다.


다음의 배열 수식을 풀이해보면,
C2:G2*B3:B12*B1

C2:G2의 배열과
B3:B12의 배열의 각 항목끼리 서로 곱하고
그 값에 B1의 값을 곱하라

(참고: 전에 살펴보았던 SUMPRODUCT함수는 배열을 이용한 함수로서, C2:G2의 배열과 B3:B12의 배열을 서로 곱하고 난 뒤 결과를 모두 합한 값을 구하는 것입니다.)

즉, 간단히 말하면 배열수식이란 배열을 수식에 집어 넣어서 배열의 각 항목의 값 전체를 계산하도록 지정해 주는 것이라고 할 수 있습니다.


하지만 그런 설명이 좀 부족하기도 합니다. 배열수식을 이용하면 이것과는 상관없어보이는 다른 어려운 일을 하는데도 많이 사용되고 있기 때문입니다.

[week시트]

이런 예를 들어봅시다.  자율학습을 할 학생들에게서 신청을 받았습니다. 어느 학생은 월화수, 어느 학생은 화목금 등등, 학생마다 자율학습을 하겠다는 요일이 다릅니다. 이때, 요일별로 어느 학생이 신청을 했는지를 한눈에 보이는 표로 만들면 위와 같습니다.

[mon시트]

그런데 각 요일별로 위와 같은 출석부를 만든다고 생각해 봅시다. 월요일 출석부에는 월요일에 자율학습신청을 한 학생만 명단에 나와야합니다. 다른 요일에 신청한 학생이 월요일 출석부에 나타나면 안됩니다. 어떻게 만들 수 있을까요?

제일 먼저 생각해 볼 수 있는 방법은 week시트에서 월요일을 기준으로 정렬을하고, 정렬된 순서대로 월요일출석부(mon시트)에 붙이는 것입니다.

복잡하게 생각할 필요가 없는 것이 가장 큰 장점입니다만, 만약 중간에 학생의 변동이 생기면 잔손이 많이 갑니다. 예를 들어, 어떤 학생이 월수금에 신청하였는데 월수는 빼고 화목을 추가하고 싶다면, 월~금의 출석부 5개를 모두 수정해야합니다.

그리고 출석부를 반별로 정리하기위해 중간에 집어넣고 빼고 앞뒤로 끝선을 맞추고 하는 작업이 학생의 변동이 생길때마다 해야하는 작업이니 처음 만들때 들었던 시간보다 나중에 수정하는데 시간이 더 많이 걸리게 됩니다.


배열수식으로 출석부를 만들려면, 다음 수식을 출석부의 학번이 출력되어야할 A3셀에 넣고 CTRL+SHIFT+ENTER한 후,  아래로 끌어채우기합니다.

=IFERROR(OFFSET(week!$A$1,SMALL(IF(week!$C$2:$C$13<>"",ROW(week!$C$2:$C$13)),ROW(A1))-1,0),"")

맨 앞에 있는 iferror함수는 오류가 발생했을때 공백을 출력시키기 위한 것이므로, 그것은 제외하고 그 안에 있는 수식만 살펴보면 다음과 같습니다.
 

=OFFSET(week!$A$1,SMALL(IF(week!$C$2:$C$13<>"",ROW(week!$C$2:$C$13)),ROW(A1))-1,0)

복잡한 수식이 어떻게 동작하는지 알기 위해서는 수식계산을 이용하면 편합니다. 원하는 셀에 커서를 놓고 "수식"탭--수식분석--수식계산을 클릭합니다.

현재 셀에 있는 수식이 표시되고, 맨 처음 계산될 수식에 밑줄이 쳐져 있습니다. "계산"을 누르면 밑줄친 부분이 계산 된 결과가 보입니다.

다른 방법으로 볼 수도 있습니다. 수식창에서 원하는 부분만 마우스로 드래그하여 선택한 후, F9를 누르면

선택된 부분이 계산된 결과가 나타납니다. 수식계산 기능을 이용하시면 오류가 발생했을때 어느 부분이 잘못되었는지를 쉽게 발견할 수 있습니다.


아래는 위의 배열수식이 수식계산 기능에서 보여지는 계산단계를 정리한 것입니다.

밑줄친 부분은 이번단계에서 계산 될 부분이며,

기울어진 글자체는 앞 단계에서 계산된 결과입니다.

파일을 첨부해 드리니, week시트의 형태도 잘 보시면서 수식이 계산되는 과정을 살펴보시기 바랍니다.

배열수식예제_자율학습출석부.xlsx

=OFFSET(week!$A$1,SMALL(IF(week!$C$2:$C$13<>"",ROW(week!$C$2:$C$13)),ROW(A1))-1,0)

week시트의 c2:c13 범위(배열)에서 빈칸이 아닌 것을 계산합니다.

=OFFSET(week!$A$1,SMALL(IF({FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE},ROW(week!$C$2:$C$13)),ROW(A1))-1,0)

보시면 c3,c4,c5,c9셀에 숫자 1이 들어있고, 나머지는 빈칸입니다. 그래서 c2:c13범위에서는 두번째,세번째,네번째,여덟번째 값이 빈칸이 아니기 때문에, 위와 같이 false, true, true, true, false .... 처럼 값이 나옵니다.

week시트의 c2:c13의 배열의 행값을 구합니다.

=OFFSET(week!$A$1,SMALL(IF({FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE},{2;3;4;5;6;7;8;9;10;11;12;13}),ROW(A1))-1,0)

c2:c13배열은 2행부터 13행까지 존재하기때문에 2~13의 배열이 출력됩니다.

이제 밑줄친 if문의 의미를 살펴보면,
c2:c13의 배열에서 빈칸이 아니라면 c2:c13배열의 행값을 주라는 의미입니다.

=OFFSET(week!$A$1,SMALL({FALSE;3;4;5;FALSE;FALSE;FALSE;9;FALSE;FALSE;FALSE;FALSE}),ROW(A1))-1,0)

그 결과 true값이 들어있는 부분에 그 행의 숫자가 들어간 배열이 만들어졌습니다.

a1셀의 행 숫자를 구합니다. 이 부분은 아래로 끌어당기기 했을때 a2,a3과 같이 변하게 하기 위해서 쓰는 부분입니다. 지금 이 수식이 들어가는 a3셀에는 월요일 신청한 학생중 첫번째 학생이 들어가야겠지만 그 아래인 a4셀에는 두번째 학생이 들어가야겠지요. 그래서 이 부분은 아래로 끌었을때 a2,a3,a4처럼 변하면서 숫자가 달라져야합니다.

=OFFSET(week!$A$1,SMALL({FALSE;3;4;5;FALSE;FALSE;FALSE;9;FALSE;FALSE;FALSE;FALSE}),{1})-1,0)

A1셀의 행숫자는 당연히 1입니다. ㅋㅋ

small함수는
주어진 배열중에서 몇번째로 작은 값을 구하라는 의미입니다. 따라서 위의 {}속에 들어있는 값중에서 첫 번째로 작은 값을 구합니다.

위에서 언급했듯이, 숫자1이라고 쓰지 않고 굳이 ROW(A1)이라는 수식을 쓴 이유는, 현재위치 (a3셀)에서는 월요일 신청 학생중 첫번째 학생의 학번이 나타나야 하므로 첫번째 작은 값을 구하도록 1이 들어가야합니다. 하지만 다음 셀인 a4셀에서는 두번째 학생의 학번을 구해야하므로 3,4,5,9의 배열중 두번째로 작은 값을 구하도록 2가 들어가야 합니다. 따라서 행이 하나씩 증가할때마다 두번째로 작은 값, 세번쨰로 작은 값, 네번쨰로 작은 값등 구하는 값이 하나씩 커지도록 하지 위해 row(a1)이라고 표시한 것입니다.

=OFFSET(week!$A$1,{3}-1,0)

주어진 배열에서 첫번째로 작은 값은 3입니다.
거기서 1을 뺍니다.

=OFFSET(week!$A$1,{2},0)

2가 됩니다.

offset함수는
주어진 위치에서 아래쪽으로 몇칸, 오른쪽으로 몇 칸 간 위치의 셀에 있는 값을 구하라는 의미입니다. week시트의 a1셀에서 아래로 2칸간 위치는 a3입니다. 오른쪽으로는 움직이지 않음(0이므로)

=10102

a3셀에 있는 값은 10102입니다.


이 수식을 말로 정리해보겠습니다. 주어진 배열(c열:월요일신청현황)에서 신청자가 있는 셀만을 뽑아내서, 각각의 행번호를 구한 다음, 행번호중에서 작은 순서대로 출력해줍니다.
위에서는 3,4,5,9행에서 값이 있었으므로, 3,4,5,9중에서 첫번째로 작은 값은 3이므로 그 값만큼 a1셀에서 아래쪽으로 이동하면 10102가 나오는 것입니다.


참고로, small함수를 이용하여 주어진 배열에서 해당 조건에 맞는 값만을 순서대로 뽑아내는 이러한 방법은 과거 1999년인가에 엑셀 수식 대회에서 수상한 작품(!)이라는 군요. 저도 엑셀 수식대회란게 있는지 처음 알았습니다...ㅋㅋ