본문 바로가기
컴퓨터

[엑셀]index, match 함수 사용방법

by Oh 선생 2019.09.16

#1. 올해 성적분석 업무 담당하면서 배운 것 중 하나가 인덱스 매치(index, match) 함수 사용방법. 

주로 어떨 때 쓰냐면, 데이터를 담은 시트를 하나 만들고, 다른 시트에서 그걸 불러올때 많이 썼다. 

보통은 모의고사, 내신, 생기부 정보 등을 데이터 시트에 집어넣고, 

상담용 시트에서 필요한 정보들을 불러와서 사용한다. 

 

vlookup도 사용하는 방법이 있긴 하지만 이건 이런저런 제약들이 좀 있어서 인덱스-매치를 섞어 쓰는 걸 주로 했다. 

그런데 쓸 때 마다 까먹어서...--; 

블로그에다가 정리해두는 글. 

 

#2. 

일단 data 시트에 그림과 같이 자료를 저장한다. 

최종적으로 만들고 싶은건, 학번을 입력하면 해당 학생의 그룹, 이름, 역할을 내놓는 시트. 

(실제로 학교에서 쓸 땐 학번을 입력해서 해당 학생의 내신, 모의고사 성적 등을 내놓게 된다)

이를 위해 필요한 건 일단 인덱스. 

인덱스는 지정범위에서 지정한 행, 열에 위치한 값을 리턴한다. Index(지정범위, 행, 열).

예를 들어 data 시트의 a2:d27 범위에서 2행 3열을 반환받으려면 index(data!a2:d27,2,3)

인덱스에서 범위를 지정할 때는 최종적으로 아웃풋을 내고 싶은 곳을 범위지정하는 거다. 

그룹을 내놓을 거면 그룹 부분을, 이름을 내놓을 거면 이름 부분을. 내신이라면 내신 부분을 범위지정한다. 

그런데 행, 열을 수동으로 지정하는 데에는 한계가 있으니까 매치를 통해 찾게 만든다. 

 

매치는 매치는 지정한 열/행에서 찾은 값이 몇 번째에 위치하는지를 리턴한다. Match(찾을값,찾을행/열,0)

예를 들어 data시트의 c2:c200에서 정예인이 몇 번째 위치했는지를 반환받으려면 match("정예인",data!c2:c200,0)

매치에서의 범위는 값을 찾을 범위가 된다. 

 

그러면 둘을 섞어서, 학번을 입력받아 이름, 그룹, 역할을 내놓도록 해보자. 

이름을 내놓을 셀에는 =INDEX(data!C2:C27,MATCH(B15,data!A2:A27,0))

  ->b15(=학번)을 입력받아 data시트의 a2:a27범위에서(학번이 들어있는 범위) 그게 몇 번째 인지를 알려주면, data시트의 c2:c27 범위에서(이름이 들어있는 범위) 그 행에 해당하는 값을 돌려준다. 나머지도 마찬가지. 

그룹을 내놓을 셀에는 =INDEX(data!B2:B27,MATCH(B15,data!A2:A27,0))

  ->b15(=학번)을 입력받아 data시트의 a2:a27범위에서(학번이 들어있는 범위) 그게 몇 번째 인지를 알려주면, data시트의 b2:b27 범위에서(그룹이 들어있는 범위) 그 행에 해당하는 값을 돌려준다.

역할을 내놓을 셀에는 =INDEX(data!D2:D27,MATCH(B15,data!A2:A27,0))

  ->b15(=학번)을 입력받아 data시트의 a2:a27범위에서(학번이 들어있는 범위) 그게 몇 번째 인지를 알려주면, data시트의 d2:d27 범위에서(역할이 들어있는 범위) 그 행에 해당하는 값을 돌려준다.

(이때 매치에서 범위를 A2:A27과 같이 1개 열(범위가 행으로 걸쳐지게)에서만 설정했으므로 match에서 내놓는 값은 A2:A27범위에서 몇 번째 에 있느냐이고, 인덱스에서도 범위를 C2:C27, D2:D27과 같이 설정했으므로 그 열(C면 C, D면 D...)의 몇 번째 행에 있는지를 찾아 해당 셀의 값을 리턴해 주는거다)

이제 학번만 바꾸면, 그에 해당하는 값을 자동으로 찾아 바꿔준다. 학번을 입력하면 이름, 그룹, 역할이 자동으로 뜬다. 

#3. 

고3담임 이야 엑셀로 이런저런 분석 프로그램을 사용하니 이게 필요한데,

다른 선생님들은 이게 과연 필요한가 싶긴 하다.

아무튼 내가 필요해서 만든 글이지만 비교적 간단하게 썼다고 생각하니 공유. 실습파일은 아래에 있다. 

index_match_test.xlsx
0.01MB

댓글0