[엑셀]Match와 Index

카테고리 없음

by 하나하기 2022. 11. 20. 23:06

안녕하세요. 하나하기입니다.

엑셀을 사용할 때 가장 많이 쓰는 기능 중 하나가 데이터 추출이 아닐까 싶은데요. 많은 양의 데이터 중에서 내가 원하는 데이터를 찾거나 매칭 시키고 내가 원하는 자리에 값을 복사하는 방법을 살펴볼게요.

우선 원본 데이터 중 내가 찾는 값이 어디에 위치해 있는지 찾아볼 수 있는 함수로 MATCH가 있습니다. MATCH함수 구문은 아래와 같습니다.

MATCH(lookup_value, lookup_array, [match_type])

lookup_value : lookup_array에서 찾으려는 값으로 필수 요소입니다.
lookup_array : 검색할 셀 범위를 지정하는 인수로 필수 요소입니다.
match_type : -1, 0, 1 중 한 값을 선택할 선택할 수 있으며 선택 사항입니다.
- 1 or 생략 : lookup_value보다 작거나 같은 값 중에서 최대값을 찾습니다. 이 때 lookup_array인수 값을 오름차순으로 지정해줘야 합니다.
- 0 : lookup_value와 같은 첫째 값을 찾습니다. 동일한 값이 여러개면 첫 번째 같은 값의 상대 위치를 반환합니다.
- -1 : lookup_value보다 크거나 같은 값 중에서 최소값을 찾습니다. lookup_array인수 값을 내림차순으로 지정해 줘야 합니다.

MATCH함수를 이용해서 상대 위치를 한 번 찾아볼게요. 아래 데이터는 통계청에서 제공하는 인구동태율 추이 데이터입니다. 밑에 그래프를 보면 출산율이 꾸준히 떨어지고 있고 그 여파로 2020년부터는 자연증가율이 -로 돌아선 것을 확인할 수 있습니다. 그럼 위 인구동태건수 원본 데이터로부터 아래 표처럼 값을 추출하기 위해서는 어떻게 해야 할지 살펴볼게요.

<표1>에서 <표2>처럼 특정 연도의 데이터를 가져오기 위해서는 해당 연도의 상대 위치를 구한다음 거기에 해당하는 데이터를 "기본항복별"에 있는 항목에 맞추어 가져오면 됩니다.

그럼 먼저 연도의 상대 위치를 구해 볼게요.

=MATCH(C$24, $B$4:$BB$4, 0)

위 그림에서 전체 데이터가 보이지 않아 확인이 안되지만 실제 데이터는 2021년(BB4)까지 위치해 있습니다. <표2>의 셀 "C24"에 입력한 연도를 기준으로 데이터를 구할 것임으로 첫 번째 인수 lookup_value는 C24로 지정했습니다. 그리고 밑에셀로 복사해 나갈 때 값이 바뀌는 것을 막기 위해 "24"는 절대 위치로 지정해 두었습니다.

lookup_array는 전체 연도, match_type은 정확하게 일치하는 첫 번째 값으로 지정했습니다. 위 MATCH함수를 이용해 1971을 <표1>에서 찾으면 반환값은 3이 됩니다.

다음은 "기본항목별"의 각 항목에 대한 상대 위치를 구합니다.

=MATCH(B25, $B$4:$B$20, 0)

<표2>의 셀"B25"에 위치해 있는 "출생아수(명)"을 위 <표1>에서 범위를 지정해서 찾습니다. match_type은 위와 동일하게 정확하게 일치하는 첫 번째 값으로 지정했습니다. 위 함수로 "출생아수(명)"을 찾으면 실제 반환값은 2가 됩니다.

그럼 <표1>에서 찾고자 하는 값의 X, Y 좌표 즉 Column과 Row값을 얻었습니다. 그럼 이제 실제 값을 가져와야 하겠죠. 이 때 사용하는 함수가 INDEX입니다.

INDEX(array, row_num, [column_num])

array : 배열 상수나 셀 범위를 지정하는 인수로 필수 요소입니다.
row_num : 값을 반환할 배열의 행을 선택합니다. row_num과 column_num 중 적어도 하나는 입력되어야 합니다. 따라서 column_num이 없다면 row_num은 필수 요소가 욉니다.
column_num 값을 반환할 배열의 열을 선택합니다.

그럼 위에서 구한 연도의 상대 위치는 row_num에 "기본항목별"의 위치는 column_num에 넣으면 내가 원하는 값을 가져올 수 있겠네요.

=INDEX($B$4:$BB$20, MATCH(B25, $B$4:$B$20, 0), MATCH(C$24, $B$4:$BB$4, 0))

위와 같은 함수로 <표2>와 같이 데이터를 추출할 수 있습니다. 물론 각 항목의 상대 위치를 구하여 데이터를 가져옴으로 "기본항목별"의 항목별 위치가 바뀌거나 일부 항목의 데이터를 가져오는 것도 문제 없습니다.

설명하기 위해 만든 파일을 첨부 했습니다.

인구동태건수_및_동태율_추이_Match_Index.xlsx

0.03MB

한번에 하나씩 해보세요. 하나하기였습니다.