Array formula in Excel

Posted on by 솔천

와우!! 엑셀에서 이런게 되는지 몰랐어요. 기능이 좀 억지스럽긴 하지만 프로그래밍을 못하는 엑셀사용자에게는 어쩌면 이런 기능이 구세주가 되는 상황이 있을수도 있을거 같다는 생각이 들어서 여러분들께 소개해드리려고 가지고 와봤어요.

제가 오늘 소개해드리고자 하는 기능은 Array Formular라는 엑셀의 기능인데요, 랩탑에 엑셀이 없으신 분들이 많이 계실것 같아서 설명은 Google sheet으로 해드릴게요. 대신 엑셀에서는 연산을 하고자하는 해당셀에 대고 Control + Shift + Enter를 눌러서 Array formular를 하겠다고 명시하지만, Google sheet에서는 ARRAYFORMULA 라는 함수로 전체 formular를 한번 감싸므로써 해당 연산이 Array formular라는것을 명시합니다. 자세한것은 밑에서 설명드릴게요.

우선 Array Formular가 뭔지 모르시는 분들을 위해서 간단하게 설명을 드릴게요. 엑셀에서 함수를 호출하면 보통 반환되는 값이 하나의 정수이거나 아니면 Boolean값, 또는 문자열이 되는데요. Array Formular로 함수를 호출하면 함수가 단답형의 결과가 아닌 배열의 형태로 결과를 반환하게 되는데요, 그걸 응용해서 다양한 기능을 구현 할수가 있는거죠.

무작정 따라하기

예를 들어 아래와 같이 두개의 칼럼으로 구성된 원본 데이타가 있다고 합시다.

하나의 칼럼에는 그룹명이 적혀있고, 그룹 옆에는 사람이름이 나열되어있어요. 이걸 그룹별로 사람들을 묶어서 보여주고자 할때 Array Formular를 사용하는데요. 방법은 다음과 같습니다.

일단 위의 데이타를 Google sheet에 입력해주세요. 그리고 데이타구간을 변수로 선언하는데요. C5부터 C11는 names라는 변수로, B5부터 B11까지는 groups라는 변수로 선언해주세요. Google sheet에서 변수를 선언하는 방법은 상단메뉴 Data > Named ranges를 클릭하셔서 변수명과 해당 변수명에 할당할 data range를 아래와 같이 지정해주세요.

그리고 결과를 보여줄 E5에 아래과 같은 공식을 넣어주세요

=ARRAYFORMULA(IFERROR(INDEX(names,SMALL(IF(groups=E$4,ROW(names)-MIN(ROW(names))+1),ROWS($E$5:E5))),""))

그리고 해당 셀의 오른쪽 작은 파란 네모를 드래그해서 복사해주세요.

Array Formular로 간단하게 그룹별로 소속된 사람들을 한눈에 볼수가 있게 되었네요.

원리 이해하기

자 그럼 지금부터 이 formular가 왜 이렇게 동작하는지 원리를 차근차근 설명해드릴게요.

=ARRAYFORMULA(IFERROR(INDEX(names,SMALL(IF(groups=E$4,ROW(names)-MIN(ROW(names))+1),ROWS($E$5:E5))),""))

이 formular에서 가장 눈여겨 보셔야할 포인트는 바로 SMALL함수 입니다. SMALL함수는요 원래 주어진 데이타 range안에서 가장 작은 값부터 순서를 매겨서 지정한 순위의 값을 가져오도록 하는 함수인데요. 그래서 인자로는 data range와 작은순서대로 몇번째 데이타를 가져올지 nth라는 변수를 인자로 받습니다. 그런데 여기서는 값이 아니라 라인번호를 넘겨주고 있기때문에 값을 가져올 행번호를 리턴하게 되는데 그걸 INDEX함수가 받아서 열은 고정이니까 행번호만 알면 INDEX가 데이타를 딱 가져올수 있겠죠.

여기서 헷갈리는 부분이 바로 SMALL이 배열을 반환한다는 사실인데요. 그 배열은 IF문에 의해서 동적으로 생성이 됩니다. 

IF(groups=E$4,ROW(names)-MIN(ROW(names))+1)

위의 연산자는 groups이라는 데이타 range에서 값이 E4와 같으면 (E4는 Fox로 고정된 값이죠), E4와 같으면, 그 앞의 연산자(아래)를 통해서 생성된 데이타 range에서 행번호를 가져오게 됩니다. 

ROW(names)-MIN(ROW(names))+1

IF문 안에서 가장 먼저 나오는 ROW(names)는 행번호를 배열로 반환합니다. 

{5, 6, 7, 8, 9, 10, 11}

이거 우리가 formular 맨 처음에 ARRAYFORMULA로 처리하겠다고 명시했기때문에 이렇게 배열을 반환하는 거지 만약에 그냥 ROW(names)만 넣으셨으면 정수 5를 반환합니다.

그리고 MIN(ROW(names))는 그 안에서 가장 작은 방번호 5를 반환합니다.

그러면 ROW(names)-MIN(ROW(names))를 연산하면 아래와 같이 각 방에서 5를 뺀값이 됩니다. 이것도 마찬가지로 ARRAYFORMULA를 명시하지 않았다면 결과가 정수 0이었을거에요. 하지만 ARRAYFORMULA로 처리하라고 명시 했기때문에 배열방의 값들에서 5씩 뺀 결과 배열을 반환 받습니다.

{0, 1, 2, 3, 4, 5, 6}

그런데 지금 우리가 INDEX에서 접근할때 인자로 range랑 행번호, 열번호를 넘길때 첫번째 방번호는 1이되야한단 말이에요. 그래서 나중에 계산하기 쉽게 방번호에 1을 더해줍니다. 

ROW(names)-MIN(ROW(names))+1

그러면 배열에 1이 각각 더해져서 아래와 같은 배열이 되요

{1, 2, 3, 4, 5, 6, 7}

위의 배열을 가지고 나오면 바로 다음 IF문이 기다리고 있죠? IF문은 배열에 조건을 겁니다. 보통 IF문이라면 조건, TRUE일때 값, FALSE일때 값 이런식으로 움직이겠지만 지금 우리는 이 formular를 Array formular로 한다고 명시했기때문에 IF문도 다르게 동작합니다. 이거 맨 마지막에 ARRAYFORMULA함수 없으면 오류나요. 왜냐면 보통 IF문은 함수의 인자를 3개 넘겨줘야하거든요. 함수의 인자를 2개 넘겨주는건 오직 ARRAYFORMULA일때만 가능합니다. 

IF(groups=E$4,ROW(names)-MIN(ROW(names))+1)

첫번째 인자로 groups데이타와 비교할 값 E4가 조건문으로 들어갔어요. E4의 값은 Fox죠? groups데이타를 잠깐 보고 갈까요?

{Fox, Bear, Bear, Bear, Moose, Fox, Moose}

IF문이 위의 배열에서 “Fox인것만 True로 반환해라”라고 한거에요 그러면 조건문을 지나면 아래와 같은 결과가 나올거에요.

{True, False, False, False, False, True, False}

위의 조건을 방금전에 ROW(names)-MIN(ROW(names))+1을 통해서 만들어낸 배열과 연산해볼게요

{   1,     2,     3,     4,     5,    6,     7}
{True, False, False, False, False, True, False}
------------------------------------------------
{1,    False, False, False, False, 6,    False}

이제 이 배열을 가지고 만날 바로 다음 함수가 바로 SMALL인데요.

SMALL(IF(groups=E$4,ROW(names)-MIN(ROW(names))+1),ROWS($E$5:E5))

ROWS($E$5:E5)를 잠시 보시면요, range의 앞쪽은 고정이고, 복사를 해서 늘릴수록 뒷쪽은 해당 셀로 변경이 된다는 거죠? 다시말해서 현재 셀이 E5인데 이걸 복사해서 바로 아래칸 E6에 붙여넣게 되면 ROWS($E$5:E6) 시작점은 여전히 E5이지만 끝나는 점은 E6인 range가 되는거죠? 그리고 만약에 이번에는 ROWS($E$5:E5)를 오른쪽에 갖다 붙였다고 쳐보세요. 그러면 이번에는 ROWS($E$5:F5)와 같이 시작점은 그대로고, 행번호도 그대로 이지만 열이 하나 늘어나게 되는거죠? 붙여 넣을때마다 해당 셀로 range가 조정이 되는거에요. 그러면 range가 조정됨에 따라서 ROWS가 반환하는 값은 어떻게 바뀔까요? 처음에는 range안에 cell하나였는데, 아래로 한칸 늘어날때마다 행번호가 늘어나겠죠? 이해를 돕기위해서 제가 아래와 같이 ROWS($E$5:E5)만 따로 떼서 아래로, 그래고 옆으로 복사해봤어요. 결과가 왜 이렇게 나오는지 이해가 가시나요? ROWS는 인자로 넘겨준 range가 몇줄짜리 데이타인지를 반환하기 때문에 아래와 같은 결과가 나옵니다.

SMALL함수에서 받을 인자가 2개다 만들어 졌으니 이제 SMALL함수를 실행해 볼까요? 

SMALL(IF(groups=E$4,ROW(names)-MIN(ROW(names))+1),ROWS($E$5:E5))

현재 첫번째 인자에는 배열, {1, False, False, False, False, 6, False}가 들어가있고, 두번째 인자에는 현재 첫번째 결과값은 E5한개니까 줄의 개수는 총 1개가 되서 1이 두번째 인자로 들어가겠죠? 

SMALL({1, False, False, False, False, 6, False}, 1)

위와 같은 인자가 들어갔을때 SMALL은 우선 배열방을 작은 순서로 아래와 같이 정렬을 합니다. 두번째 인자는 제가 아까 뭐라고 했죠? n번째 라고 했잖아요.

SMALL({1, 6}, 1)

그럼 위와 같이 SMALL함수를 호출하면 두개의 배열방에서 첫번째 방의 값을 반환합니다. 결과는 1이 됩니다.

그러면, 해당 셀을 아래로 복사한 경우에는 range가 2개의 셀이 되어서 총 ROWS의 갯수가 2가 되는데 이런 경우에는 어떤 값을 반환할까요?

SMALL({1, 6}, 2)

네 맞아요. 2번째 값인 6을 반환하겠죠.

그런데 예를 들어서 우리가 복사를 더 밑에까지 했다고 칩시다. 그러면 range의 행수가 3이 되어서 3번째 데이타를 가져오려고 할텐데 배열에는 배열방이 2개 밖에 없죠 이런 경우에는 어떻게 될까요? 네 그 셀에서는 에러가 납니다.

그래서 나중에 함수를 다 끝내고 나가기 전에 에러처러를 해주어야하는 이유입니다. 일단 SMALL에서 받아온 행번호를 가지고 INDEX를 통해서 값을 가져와 볼까요?

INDEX(names,SMALL(IF(groups=E$4,ROW(names)-MIN(ROW(names))+1),ROWS($E$5:E5)))

INDEX함수의 첫번째 인자는 데이타를 가져올 range이고요, 두번째는 행번호, 그리고 세번째 인자는 열번호 입니다. 보통 data range의 범위가 넓을때 아래와 같이 행과 열을 지정해서 값을 가져옵니다. 이때 행, 열은 절대값이 아니고, 해당 range에서의 상대적인 행,열번호를 의미합니다. 

  INDEX(B5:E13, 5, 3)
  row 5, column 3

그런데 우리는 현재 range의 열이 1개밖에 없기때문에 행번호만 지정해주면 됩니다. SMALL에서 받아온 값이 첫번째 셀에서는 1, 두번째 셀에서는 6이었으니까 한번 대체해 볼게요.

INDEX(names,1)
INDEX(names,6)

그러면 첫번째 행에는 names의 첫번째 이름인 Doug, 그리고 두번째 행에는 names의 6번째 이름인 Cindy가 들어가겠죠. 그러면 이렇게 결과가 나오죠.

여기까지만 하고 나가면 에러가 난 셀들이 안이뿌니까 에러가 났다면 그냥 공백처리를 하도록 IFERROR로 감싸줄게요.

에러가 안보이니까 한결 좋으네요. 오늘배운 Fomular를 정리해서 보면 아래와 같습니다. 

ARRAY_CONSTRAIN(
  ARRAYFORMULA(
    IF(
      J$2="",
      "",
      IFERROR(
        INDEX(
          Discounts!$F$2:$F$999,
          SMALL(
            IF(
              Discounts!$B$2:$B$1393=J$2,
              ROW(Discounts!$F$2:$F$999)-MIN(ROW(Discounts!$F$2:$F$999))+1,
              ""
            ),
            ROW(Discounts!B1)
          )
          ,1
        )
        ,""
      )
    )
  )
  ,1
  ,1
)

오늘 배운 내용을 가지고 응용해 볼수 있는게 뭐가 있는지 생각해보고 다른 결과를 한번 구현해보는것도 나쁘지 않을것 같아요. 여기까지 따라 오시느라 수고 많으셨습니다. 좋은 하루 되세요.

References: