엑셀을 배울 때 고수가 되기 위해서는 단계마다 넘어야 할 허들(hurdle)이 있습니다. 배열도 그 중 하나입니다.
배열Array이란 '비슷한 성격을 가진 데이터를 하나로 그룹화' 해 놓은 것을 말합니다. 학창시절 수학시간이 떠오르기도 하고 이유없이(?) 거부감이 드는 분도 있으리라 생각합니다. 배열을 어렵게 생각할 필요는 없습니다. 알고 보면 엑셀의 워크시트도 배열입니다. 그것도 수많은 행과 열로 이루어진 거대한 '2차원' 배열입니다.
그런가 하면 배열 상수Array Constant라는 것도 있습니다. 워크시트에서 수식을 작성할 때 셀 주소를 인수로 전달하는 경우가 많습니다. 이 때 엑셀은 전달받은 셀 주소를 내부적으로는 배열 상수로 처리합니다. 도무지 무슨 말인지 모르시겠다고요? 그래서 예제를 준비했습니다.(^^;)
열 방향 배열
아래 그림과 같이 C3:I3 영역에 있는 데이터(회색)를 수식을 사용하여 다른 곳(노란색)으로 옮겨 가려면 어떻게 해야 할까요?
이런 순서로 하면 됩니다.
- 회색 영역과 같은 크기로 범위를 지정한다(C5:I5).
- 등호(=)를 입력하고 C3:I3 영역을 드래그한다.
- CTRL+SHIFT+ENTER 키를 함께 눌러 마무리한다.
그러면 결과는 이렇게 됩니다.
수식 입력줄에서 C3:I3 부분을 범위로 지정하고 F9 키를 눌러 보세요.
열 방향 데이터는 콤마(,)로 구분
어, 이상하죠? 분명히 셀 범위를 지정했는데 결과를 보니 셀에 들어 있는 값이 표시됩니다. 이것을 두고 앞에서 '셀 주소를 배열 상수로 처리'한다고 했던 겁니다. 여기서 눈여겨 봐야 할 것은 각 숫자가 콤마(,)로 구분되어 있다는 사실입니다. 열 방향 데이터는 배열 상수로 처리할 때 콤마로 구분한다는 사실을 반드시 기억해 두세요. 매우 중요합니다(별표 세 개!!!).
엑셀 최근 버전을 사용한다면 앞의 01 단계에서 가져올 영역과 같은 크기로 범위를 지정할 필요도 없습니다. 예를 들어 C7셀 하나만 선택하고 값을 가지고 올 영역을 드래그합니다.
그리고 CTRL+SHIFT+ENTER가 아니라 그냥 ENTER 키를 누릅니다.
엑셀 최근 버전은 이전보다 똑똑해져서, CTRL+SHIFT+ENTER를 누르지 않아도 자동으로 배열로 인식하고 처리해줍니다. 하지만 배열 형태로 정보를 주고 받을 때에는 1) 원래 데이터와 같은 크기로 범위를 지정해야 한다는 것, 2) CTRL+SHIFT+ENTER 키를 함께 눌러 마무리하는 것이 원칙이라는 것을 기억해 두시기 바랍니다.
행 방향 배열
이번에는 행 방향 데이터에 대해 앞의 01 ~ 03 과정을 진행합니다.
수식 입력줄에서 C9:C15 부분을 범위로 지정하고 F9 키를 눌러 보세요.
행 방향 데이터는 세미콜론(;)으로 구분
비슷하면서도 뭔가 다른 결과가 나타났습니다. 이번에는 각 숫자가 세미콜론(;)으로 구분되어 있죠?행 방향 데이터는 배열 상수에서 세미콜론으로 구분한다는 것에 유의하시기 바랍니다.
이번에는 행과 열 방향으로 되어 있는 2차원 데이터를 살펴볼까요? 1부터 9까지 숫자가 아래와 같은 형태로 입력되어 있습니다. 마찬가지로 이 데이터를 다른 곳으로 옮겨 봅니다.
수식 입력줄에서 H10:J12 부분을 범위로 지정하고 F9 키를 눌러서 배열 상수가 어떻게 이루어져 있는지 보세요.
열 방향에 있는 1, 2, 3은 콤마로 구분되어 있습니다. 4는 행이 바뀌므로 세미콜론으로 구분되고 4, 5, 6은 열 방향으로 배치되어 있으모로 콤마로 구분됩니다.
배열 상수의 특징에 대해 이해를 하셨나요? 그렇다면 배열 상수는 어떤 곳에 활용할 수 있을까요? 간단한 예제를 하나 보도록 하죠.
배열 상수 활용 (1)
그림과 같은 데이터가 있습니다. 상위 3개의 합계를 구하려면 어떻게 하면 될까요?(RANK는 알아보기 쉽도록 넣어둔 겁니다) 엑셀에는 '상위 n개의 합계'를 구해주는 함수는 따로 없으므로 Large 함수를 반복하면 아쉬운 대로 결과를 얻을 수는 있습니다. 썩 마음에 들지는 않지만.
하지만 만약 '상위 10개의 합계'를 구해야 한다면 Large 함수를 10번 반복할 수는 없겠죠? 배열 상수를 이용하면 간단히 해결할 수 있습니다.
상위 10개 값의 합계를 구하려면 배열 상수값만 바꿔주면 됩니다.
배열 상수 활용 (2)
어느 분이 이런 질문을 하셨습니다(질문 내용 일부 편집 처리).
안녕하세요 좋은 내용 잘 보고 듣고 배우고 있습니다. 아래와 같은 수식이 있습니다.
=VLOOKUP(--MID(A4,3,1),{1,"행정";2,"경영";3,"경제";4,"전자"},2,0)
이것은 C4 셀의 수식으로, 학번 6자리 숫자에서 3번째 숫자 한 글자가 학과를 추출하는 식입니다. 1: 행정, 2: 경영, 3: 경제, 4: 전자,... 이런 식으로요.
...(중략)... 수식에 대한 설명 부탁드립니다. 감사합니다.
수식에서 MID 함수 사용법은 다들 아시리라 생각합니다. 앞에 '--' 부호를 붙인 것은 MID로 추출한 문자열 형태의 숫자를 온전한 숫자로 바꾸기 위한 여러 가지 방법 중 하나입니다. 이것과 관련해서는 다음 포스트를 참고하세요. 숫자 형태의 문자열 데이터를 처리하는 다양한 방법을 알 수 있습니다.
그 뒤에 나오는 중괄호 { } 안에 있는 것이 바로 배열 상수입니다. 1과 "행정"은 열 방향 배열임을 알 수 있습니다. 그 다음에 행을 달리 해서(;를 사용하였으므로) 2와 "경영"이 이어집니다. 열 방향 배열과 행 방향 배열의 이해하셨다면 이것은 같은 연장선상이죠? 문자열이면 인용 부호로 묶어 준다는 것만 추가되었습니다.
수식에서 본 것처럼, 배열 상수를 이용하면 VLOOKUP 함수에서 참조 테이블이 워크시트에 없더라도 작동합니다. 뒤에 있는 2와 0은 VLOOKUP 함수의 일반적인 사용법과 같습니다. 배열 중에서 두 번째 컬럼에서 찾되, 정확하게 일치하는 값을 가지고 오라는 뜻이지요.
참조 테이블이 복잡하지 않을 경우, 이런 식으로 배열 상수를 이용하여 수식을 구성해 놓으면 모르는 사람들은 아마, "어, 참조 테이블이 없는 데도 작동하네?" 하면서 신기해 할 겁니다.
자, 정리 들어갑니다. 오늘 강의에서 다른 것은 다 잊어버려도 이것만은 반드시 기억하세요.
"배열 상수에서 열 방향은 콤마(,)로, 행 방향은 세미콜론(;)으로 구분된다."
배열 상수를 어떻게 스마트하게 활용할 수 있는지에 대해서는 앞으로 기회가 될 때마다 다뤄보겠습니다. 아, 지난 강의에서 배웠던 만년 달력 기억 나시나요? 배열 상수 개념을 이해하고 있으면 만년 달력도 손쉽게 만들 수 있습니다. 연구해 보세요.
곧 설 연휴가 시작됩니다. 고향 다녀오시는 분, 못 가시는 분, 그 밖에 분들 모두 즐거운 명절 보내시기 바랍니다.
#배열 #배열상수 #VLOOKUP #Array #ArrayConstant