[EXCEL/엑셀] INDEX, MATCH 함수 중복값 제거 (COUNTIF 활용)

INDEX, MATCH 활용 3번째

 

INDEX, MATCH 함수로 중복값 제거하기!!

 

엑셀에서의 중복값 제거는 데이터 도구에 있는 중복된 항목 제거를 주로 사용하지만 수식으로도 이를 구현할 수 있다.

데이터 도구

 

일단 공식은

{=INDEX(중복값제거할영역,MATCH(0,COUNTIF( 중복값제거된값의입력범위★, 중복값제거할영역),0),)}

 

+ Ctrl + Shift + Enter (배열수식)

 

이렇게 되는데

공식을 가지고 아래의 데이터에 적용하면서 보자.

 

참고로 배열수식이긴 한데 내가 사용하고 있는 엑셀 365버전에서는 배열수식이 자동적용되기 때문에

그 보다 이전의 버전을 사용하는 환경에서는 배열수식으로 입력해주어야 한다.

 

배열수식을 사용하면 수식 앞과 뒤에 { } 괄호가 표시된다.

 

INDEX,MATCH_3.xlsx

0.01MB

 

위 파일의 INDEX, MATCH 중복값제거 시트에 아래와 같은 데이터가 있다.

모의고사 문항데이터

왼쪽의 데이터로 오른쪽의 데이터 값을 만들어보자.

 

F3=INDEX($C$2:$C$46,MATCH(0,COUNTIF($F$2:F2,$C$2:$C$45),0), ) .. 중복값 제거할 데이터의 범위 여기에서는 내용영역의 전체 데이터

F3=INDEX($C$2:$C$46,MATCH(0,COUNTIF($F$2:F2,$C$2:$C$45),0), ) .. MATCH의 첫 인수는 0, 뒤의 COUNTIF문의 데이터 값과 일치하는 수가 0인 데이터를 찾아옴

F3=INDEX($C$2:$C$46,MATCH(0,COUNTIF($F$2:F2,$C$2:$C$45),0), ) .. COUNTIF(범위,조건) 범위는 입력하는 F3의 바로 윗행으로 설정 $F$2:F2 이렇게 잡아주는 이유는 아래로 데이터를 복사했을 때 $F$2:F3..... 이런 식으로 변경되기 때문에 

F3=INDEX($C$2:$C$46,MATCH(0,COUNTIF($F$2:F2,$C$2:$C$45),0), ) .. COUNTIF(범위,조건) 조건은 내용영역 전체 데이터 

F3=INDEX($C$2:$C$46,MATCH(0,COUNTIF($F$2:F2,$C$2:$C$45),0), ) .. 정확히 일치하는 값

F3=INDEX($C$2:$C$46,MATCH(0,COUNTIF($F$2:F2,$C$2:$C$45),0), ) .. INDEX 마지막 인수는 생략가능

 

이렇게 설정하면 

MATCH(0,COUNTIF($F$2:F2,$C$2:$C$45),0)

내용영역명 전체범위($C$2:$C$45) 중에 $F$2:F2 범위에 맞는 데이터가 0인 첫 번째 데이터의 위치를 가져옴

 

INDEX(내용영역명전체범위, 위의 데이터위치(행번호), 생략(열번호))

이런 식으로 풀리는 것이다.

 

그래서 F3의 값은 MATCH(0,COUNTIF($F$2:F2,$C$2:$C$45),0) 값이 1이기 때문에

내용영역명 전체범위인 $C$2:$C$46의 첫 행 데이터인 "독서"가 출력되는 것이다.

 

F4의 값도 참고로 보면

F4=INDEX($C$2:$C$46,MATCH(0,COUNTIF($F$2:F3,$C$2:$C$45),0),) 인데

 

F3과 다른 부분은 COUNTIF의 첫 인수인 $F$2:F3

내용영역명 전체범위($C$2:$C$45) 중에 $F$2:F3 범위에 맞는 데이터가 0인 첫 번째 데이터의 위치를 가져오기 때문에

$F$2:F3의 데이터에는 F3에서 출력된 "독서"가 있으므로 "독서"가 없는 첫번째 데이터인 "문학"이 있는 18번째 행의 18이 출력된다.

 

그래서 F4는 INDEX(내용영역명의전체범위,18,0)으로 "문학"이 출력된다.