[엑셀 함수] 중복값 찾기 1(두 열의 데이터를 비교하여 중복값 찾기)

이번 포스팅에서는 엑셀 함수 중복값 찾기, 두 열의 데이터를 비교하여 중복값 찾는 방법에 대해 설명하겠습니다. 엑셀에서 데이터를 다룰 때 가장 오랜 시간과 노력을 들여야 하는 것이 데이터를 사용하기 좋게 가공하고 정리하는 것입니다.

예를 들어 HR에서 사용하는 '주소'라는 데이터를 분석할 경우, '띄어쓰기(서울특별시 - 서울특별시)', '지명(제주도-제주특별자치도)', '세부사항(1001동 1203호 - 1001-1203)' 등 모두 다를 수 있기에 이를 일치시키는 작업이 반드시 필요합니다. 특히, '한글'의 경우에는 조금만 달라도 결과값이 크게 달라질 수가 있습니다.

 

 

이렇게 가공을 할 때 가장 귀찮고 까다로운 부분이 중복값을 찾는 것입니다. 중복값을 찾는 방법은 '찾기', '중복된 항목 제거', 'Vlookup함수', '피벗' 등 수도 없이 많습니다. 위의 방법들은 차례로 블로그하도록 하겠으며 이번 시간에는 두 열의 데이터를 비교하여 중복값을 찾는 함수에 대해서 설명드리겠습니다.

 

중복값 찾기 관련 함수 이해하기

이를 위해서는 총 3개의 함수('IF', 'IS', 'MATCH')를 사용해야 합니다.

• IF함수는 조건에 따라 True 또는 False인지 결과를 반환하는 함수로 엑셀 함수 중 가장 기본함수입니다.
• IS함수는 값의 유형에 따라 True 또는 False를 반환하는 함수입니다. 예를 들어, 빈 셀을 참조하는 경우(ISBLANK), 값이 임의의 오류값(#VALUE!, #REF! 등)을 참조하는 경우(ISERROR) 등입니다.
• MATCH함수는 지정된 범위에서 검색하여 해당 항목이 있는 상대적 위치를 반환하는 함수입니다. 예를 들어, A1:A3범위에 10, 20, 30이 있는 경우 MATCH(20, A1:A3, 0)을 하면 20이 두 번째 항목이기에 숫자 2를 반환합니다.

 

 

두 열의 데이터를 비교하여 중복값 찾기

B열의 이름이 D열에 있을 경우, 해당 이름을 C열에 표시하는 중복값 찾는 방법입니다. 

B열 이름이 D열에 중복될 경우 C열에 표시하겠습니다

 우선 적용한 함수는 아래와 같습니다.

=IF(ISERROR(MATCH(B2, $D$1:$D$11, 0)), "", B2)

굉장히 복잡해 보이지만 하나하나 뜯어보면 이해하실 수 있을 것 같습니다.

• MATCH(B2, $D$1:$D$11,0) : 유재석이 해당 영역(D1:D11)에서 정확하게 일치하는 값이 있는 위치는 항목에서 세번째에 있기에 3이 반환됩니다.
• ISERROR(MATCH(B2, $D$1:$D$11,0)) : MATCH에서 3이라는 숫자가 반환되었기에 'False'가 반환되었습니다. 만약 MATCH에서 아무런 숫자가 반환되지 않았다면 'True'가 반환이 됩니다.
• IF(ISERROR(MATCH(B2, $D$1:$D$11,0)), "", B2) : IF를 사용하여 중복된 값을 반환하였습니다. 만약 'True'가 나올 경우 빈칸("")을 반환하고, 'True'가 나올 경우 해당 이름(유재석)이 나오도록 하였습니다.

'유재석', '하하', '이이경'이 중복이 되어 C열에 표시되었습니다

 

이렇게 차근차근 풀어보면 이렇게 긴 함수도 쉽게 이해하실 수 있지만... 자격증 취득이나 학습의 목적이 아니라면 해당 함수를 복사하여 해당 부분만 수정하는 것도 나쁘지 않을 것 같습니다. 이상 두열의 데이터를 비교하여 중복값을 찾는 방법을 설명드렸고, 다음 포스팅에서도 다른 중복값 찾는 방법에 대해 설명드리겠습니다.

감사합니다.