Excel 엑셀에서 두 열을 비교하여 중복되는 값 나열 방법

엑셀 작업을 하다 보면 다양한 방법으로 두 열을 비교하는 작업을 하는 경우가 있습니다.

가장 흔하게 사용하는 방법 중에 =A1=B1과 같이 비교 연산자를 사용하거나 =IF(A1=B1,”일치”,”불일치”)와 같은 수식을 사용하여 두 열이 서로 일치하는지 비교하는 방법을 사용하게 됩니다.

이렇게 단순하게 같은 열에서 두 셀의 값만 비교하는 것이 아니라 두 열 전체의 목록에서 서로 중복되는 값이 있는지 비교해서 중복되는 값만 별도로 나열하고자 한다면 어떻게 하면 될까요?

이번 글에서는 Excel 엑셀에서 두 열을 비교하여 중복되는 값 나열 방법에 대해 알아보겠습니다.
즉 두 열에 모두 존재하는 데이터 값들을 나열하는 방법에 대한 설명입니다.

Excel에서 두 목록에 모두 존재하는 데이터를 나열하는 방법

1. 아래 화면과 같이 A열과 C열에 모두 존재하는 중복 값을 나열하는 방법에 대해 설명해 보겠습니다.

2. 이 수식에는 FILTER, ISNUMBER, MATCH 이렇게 세 가지 함수를 사용하고 있습니다.

• FILTER 함수 : 범위 또는 배열 필터링합니다.
• ISNUMBER 함수 : 검사하려는 값이 숫자이면 TRUE를 돌려줍니다.
• MATCH 함수 : 배열에서 지정된 순서상의 값에 일치하는 항목의 상대 위치 값을 찾습니다.

3. A열과 C열의 목록 데이터에서 중복되는 목록을 찾는 수식입니다.

• =FILTER(A2:A14,ISNUMBER(MATCH(A2:A14,C2:C11,0)))
• MATCH(A2:A14,C2:C11,0)
  • A2:A14 : 검색할 값
  • C2:C11 : 검색할 범위
  • 일치 유형은 되돌릴 값을 표시하는 숫자로 1, 0 또는 -1 입니다.
  • 결과 : {#N/A, 2, 9, #N/A, 8, 1, #N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A}
• ISNUMBER(MATCH(A2:A14,C2:C11,0))
  • MATCH 결과에 대한 숫자 여부 체크
  • 결과 : {FALSE, TRUE, TRUE, FALSE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE}
• FILTER(A2:A14,ISNUMBER(MATCH(A2:A14,C2:C11,0)))
  • A2:A14 : 필터링 할 범위
  • 결과 : {청포도, 자두, 참외, 단감} 즉, C열의 2번째, 9번째, 8번째, 1번째 나열

마무리

이상으로 두 열을 비교해서 두 열 모두에 존재하는 중복 된 값들만 나열하는 방법에 대해 알아보았습니다.

만약 검색할 값(A열) 목록에 중복된 데이터가 있는 경우 필터링 결과에도 중복이 될 수 있는데 이렇게 중복 필터링 되는 것을 막기 위해서는 UNIQUE 함수를 사용해서 중복을 제거할 수 있습니다. 예: =UNIQUE(FILTER(A2:A14,ISNUMBER(MATCH(A2:A14,C2:C11,0))))

여러 사람이 정리한 데이터를 정리할 경우 중복된 데이터를 제거하기 위한 방법으로 여기에서 설명한 방법을 활용하면 많은 도움이 되리라 생각됩니다.

♥ 이 글이 도움이 되었기를 바랍니다. ^-^
댓글로 흔적을 남겨 주세요.