[엑셀] 최초로 0이 아닌 값이 나오는 곳 찾기 INDEX() MATCH()

다음과 같은 자료가 있다고 할 때

다음과 같이 처음으로 0이 아닌 값이 나오는 날짜를 얻기 위해서는 어떻게 해야 할까?

수식부터 미리 공개하면 다음과 같다.

=INDEX($B$1:$H$1,MATCH(TRUE,INDEX(B2:H2<>0,),0))

=INDEX($날짜행,MATCH(TRUE,INDEX(값행<>0,),0))

수식의 원리를 아래에서 생각해 보자.

---

INDEX() 는 잘 아는 것처럼 특정 범위에서 어떤 값을 반환하라는 명령이다.

=INDEX(A1:C3, 3, 2)

위와 같이 입력할 경우 주어진 범위(A1:C3) 에서 3행 2열의 값인 8을 반환하게 된다.

범위가 2차원이 아니라 1차원일 경우, 값을 하나만 지정해도 된다. 예를 들어

=INDEX(A2:C2, 2)

한 행을 범위로 지정할 경우 뒤의 인자를 2 하나만 주어도 무방하다. 마찬가지로

=INDEX(B1:B3, 2)

한 열만 범위로 지정하는 것도 성립한다.

그런데 여기서 뒤의 인자를 아예 생략하면 어떻게 될까?

=INDEX(B1:B3,)

와 같이 입력하면? (괄호 안의 수식이 콤마로 끝난다는 점에 주의하자)

수식 자체가 성립하지 않을 것 같은데, 놀랍게도 어떤 값이 나오는 것을 볼 수 있다.

하지만 이 경우 값이 아니라 이 수식이 B1:B3 에 해당하는 내부 배열을 생성하게 된다는 점이 훨씬 중요하다.

해당 셀에서 F2에 이어 F9를 눌러 보자.

해당 범위의 값으로 배열을 만들고 있는 것이 보인다.

여기서 알 수 있는 것은 

INDEX() 함수에 1차원 범위를 지정할 경우 일단 해당 범위의 값을 자체적으로 배열로 만들어 저장하고,

주어진 두 번째 인자에 따라 그 배열의 첫 번째 값, 두 번째 값... 과 같이 판단하여 반환한다는 것이다.

그리고 위에서처럼 두 번째 인자를 아예 생략하면 '배열 전체'로 생각하여 그대로 배열을 담고 있게 된다.

(두 번째 인자를 0으로 주어도 같은 효과를 볼 수 있다.)

INDEX()의 이러한 성질은, 조건을 이용해 배열을 만들 수 있다는 데에서 훨씬 더 유용하다.

예를 들어 다음과 같이 입력할 경우

=INDEX(A1:A10>5,)

수식은 FALSE를 반환하는데, 위에서 보았듯 실제 반환되는 값보다 어떤 배열을 만들고 있는지가 훨씬 의미가 있다.

F2 - F9 을 통해 확인해 보면

A1:A10의 범위를, 우리가 준 조건에 비교하여 진리값의 배열로 저장하고 있는 것을 볼 수 있다.

이러한 성질을 이용하여 최초로 0이 아닌 값이 나오는 열을 찾는 것이다.

다시 처음의 예로 돌아가 보자.

처음으로 해야할 일은 B2:H2 의 각 값이 0인지 아닌지를 판단하여 해당 진리값들을 배열로 만드는 것이다.

=INDEX(B2:H2<>0,)

위 수식이 그러한 역할을 담당한다. F9를 눌러 보면

이와 같이 진리값에 대한 배열을 생성한 것을 볼 수 있다.

첫 번째와 두 번째 값은 0이므로 FALSE이고, 세 번째 값부터는 0이 아니므로 TRUE이다.

그럼 이제 이 '세 번째' 라는 것을 숫자로 바꾸어 줄 차례다. 

이를 위해 MATCH() 가 사용된다. 만들어진 배열에서 TRUE가 어디 있는지 찾는 것이다.

=MATCH(TRUE, 배열, 0)

MATCH() 함수 끝에 붙는 0에 주의한다. 

이는 일종의 옵션으로, 0은 '완전일치'를 찾으라는 의미이다. 1이나 -1은 근사값을 찾는 옵션이며, 생략하면 1로 간주된다.

이제 3이라는 숫자를 얻었으므로, 날짜 행을 지정하여 INDEX로 세 번째 값을 불러오면 되겠다.

=INDEX($B$1:$H$1, 앞의수식)

여기서 날짜가 지정된 행(B1:H1)은 수식을 복사하여도 유지되어야 하므로 절대참조$B$1:$H$1)으로 바꾸어 준다.

이제 아래로 복사하면 완성이다.

중간에 배열을 사용하고는 있으나, 배열수식으로 취급받지는 않으므로

CTRL + SHIFT + ENTER 를 누를 필요는 없다.

여기까지의 논의를 보면 알겠지만

이와 같은 INDEX와 MATCH의 조합은 VLOOKUP 의 대용으로도 훌륭히 사용할 수 있다.

해당 내용은 다음 포스팅에서 알아보자.

2018/08/28 - [엑셀] - [엑셀] VLOOKUP 대신 INDEX, MATCH 사용하기