지난 시간에는 각각의 행이나 열에 있는 여러 데이터 중 비어있지 않은 가장 마지막 값을 불러오는 방법에 대해 알아보았는데요. 오늘은 반대로 빈칸이 아닌 첫번째 값을 가져오는 방법에 대해 말씀드리겠습니다.
오늘 사용할 함수 명령어는 INDEX와 MATCH의 조합으로 이루어집니다. 두 함수가 각각 어떤 의미인지와 기본적인 사용법 간략히만 짚고 넘어가겠습니다.
INDEX 함수
먼저 index 함수는 몇 번째에 위치하는 값을 가져와서 보여줘라 이런 의미입니다. index 함수가 가지는 인수들과 각각의 의미는 아래와 같습니다.
=INDEX(array, row_num, column_num)array : 배열 형태의 범위를 지정합니다. 값을 찾고자 하는 범위입니다. 배열이라는 건 행과 열이 여러개씩 있는 4*5와 같은 영역을 말합니다.
row_num : 행 번호를 뜻합니다.
column_num : 열 번호를 뜻합니다.
바둑판 형태의 자료 범위에서 몇 행 몇 열의 데이터가 뭔지 알려줘라 이런 뜻으로 보시면 됩니다.
MATCH 함수
match 함수는 찾으려는 값이 범위 내에서 ‘몇 번째에 있는지’ 순서를 반환해줍니다. match 함수가 가지는 인수와 의미는 아래와 같습니다.
=MATCH(lookup_value, lookup_array, match_tyle)lookup_value : 범위에서 찾고자 하는 값입니다.
lookup_array : 값을 찾으려는 범위를 지정합니다. MATCH 함수에서 범위는 열 또는 행 한 줄이어야 합니다. 즉, 10*1 또는 1*6 이런 식으로 범위를 지정해주어야 하며, 3*5 이런 매트리스 형태로 지정하면 오류가 발생합니다. (#N/A 반환)
match_type : 선택 옵션으로 입력하는 인수로, 일치 옵션을 지정합니다. 입력하지 않을 시 기본값 1로 설정됩니다. 1은 지정한 lookup_value 보다 같거나 작은 값 중에 가장 큰 값을 찾습니다. 이 때 데이터는 오름차순 정렬되어 있어야 합니다.
0은 정확히 일치하는 경우에만 찾았다고 생각합니다. -1은 반대로 찾으라는 값보다 크거나 같은 값 중 최소값을 찾아냅니다. 이 때는 데이터를 내림차순 정렬하여야 합니다.
빈칸 아닌 첫번째 값 가져오기
그럼 match index 이 두 함수를 응용해서 어떤 데이터 배열에서 빈칸이 아닌 첫번째로 들어있는 데이터 값을 가져오는 방법을 해보겠습니다.
마지막 데이터 가져올 때와 비슷하게 듬성듬성 이빨빠진 데이터를 가지고 해보겠습니다.
=INDEX(B2:F2,MATCH(TRUE,INDEX((B2:F2<>0),0),0))함수부터 말씀드리자면 위와 같은 조합으로 넣으시면 됩니다. 굳이 이해하고 싶지 않다, 빨리 결과만 있으면 된다 하시는 분은 위 함수 명령어를 카피해서 쓰시기 바랍니다.
큰 구성을 보시면 INDEX가 있고 데이터 범위에서 MATCH 함수의 결과값을 찾으라고 되어 있습니다. 즉 MATCH 함수를 통해 몇 번째가 처음으로 빈칸이 아닌 데이터인지를 추출하고 INDEX에 넣어서 그 위치에 있는 데이터를 가져오라는 것이죠.
이제 제일 안쪽의 INDEX((B2:F2<>0),0) 이 부분을 보겠습니다. 데이터 범위를 0이 아닌지에 대해서로 배열을 새로 만들었습니다. 빈칸이 아닌지로 하려면 B2:F2<>”” 이렇게 해도 됩니다.
만약 {빈칸 데이터 데이터 빈칸 데이터} 이런 형태였다면 <>0 에 대해서 {FALSE TRUE TRUE FALSE TRUE}와 같은 배열을 반환하게 됩니다. 여기서 굳이 INDEX로 참거짓 배열을 만들 필요는 없이 <>0만 해도 배열은 나오는데요.
굳이 함수를 이렇게 만든 이유는 이렇게 가로형 데이터가 아닌 세로형 데이터에서도 사용할 수 있게 하기 위함입니다. 세로로 나열된 데이터 형태에서는 위와 같이 INDEX에 row_num:0 을 이용해서 전체 열의 값을 행 형태의 배열로 반환시킬 수 있습니다.
다음으로 MATCH 함수로 TRUE를, 생성한 참거짓 배열에서, 정확히 일치하는 곳을(0) 찾습니다. 첫번째로 TRUE가 뜨는 곳이 정확히 일치하면 그 위치의 순서가 몇 번째인지 반환하는 함수입니다.
마지막으로 그렇게 반환된 순번을 다시 INDEX 함수로 데이터 범위에서 몇 번째 값을 가져오라고 하면 완성입니다.
입력칸을 드래그해서 각 행에 함수를 복사해보면 위와 같이 빈칸이 아닌 첫번째 값을 찾아주는 것을 볼 수 있습니다.