두 열을 비교하고 Excel 에서 중복 데이터 찾기(전체 가이드)

Excel 에서 두 열 간의 중복 값을 식별하는 것은 데이터 분석에서 흔히 수행되는 작업입니다。 이를 통해 데이터 입력 오류， 중복 레코드를 파악하거나 데이터 정제 목적으로 활용할 수 있습니다。 이 글에서는 두 열에서 중복 또는 일치 항목을 효율적이고 정확하게 식별하는 방법을 안내합니다。

두 열을 비교하여 중복 값 찾기

• 조건부 서식 사용로 두 열에서 중복 항목 강조하기
• 강력한 도구 – Kutools 로 두 열에서 중복 선택 및 강조 표시
• 수식으로 두 열에서 중복 항목 찾아 추출하기
• VBA 코드로 두 열에서 중복 선택

두 열을 행 단위로 일치 여부 비교하기

• 수식으로 동일 행의 두 열 비교하기
• 유용한 도구 - Kutools 로 동일 행에서 일치 항목 선택 및 강조하기
• 두 열을 비교하여 동일 행에서 일치 항목 강조하기

두 열을 비교하여 일치하는 데이터 가져오기

두 열을 비교하여 중복 값 찾기

두 열 간의 중복 값을 찾기 위해 시각적 개요를 위한 중복 강조 또는 심층 분석을 위한 추출 등 요구사항에 따라 다양한 방법을 사용할 수 있습니다. 이 섹션에서는 Excel 에서 이 작업을 수행하는 몇 가지 빠른 팁을 소개합니다。

조건부 서식 사용로 두 열에서 중복 항목 강조하기

Excel 에서 두 열 간의 중복 항목을 강조하는 것은 특히 수동 검토가 어려운 대규모 데이터셋에서 반복 데이터를 식별하는 효율적인 방법입니다。 이 경우 조건부 서식 사용 기능이 이 작업을 해결하는 데 유용합니다。

1 단계1: 중복 항목을 강조할 열의 데이터 선택하기

이 예제에서는 A2:A10 을 선택합니다(스크린샷 참조)。

2 단계2: 조건부 서식 사용 기능 적용하기

1. 「홈」 > 「조건부 서식 사용」 > 「새 규칙」을 클릭하세요(스크린샷 참조)。
   
2. 「새 서식 지정 규칙」 대화 상자에서 다음 작업을 수행하세요：
   • 2.1 「규칙 유형 선택」 목록 상자에서 「수식을 사용하여 서식을 지정할 셀 결정」을 선택하세요；
   • 2.2 「다음 수식이 TRUE인 경우 값에 서식 지정」 텍스트 상자에 다음 수식을 입력하세요；

     =COUNTIF($B$2:$B$10, A2)>0

   • 참고: 위 수식에서 "B2:B10"은 비교하고자 하는 데이터 목록을 나타내며, "A2"는 중복 항목을 강조 표시하려는 열의 첫 번째 셀입니다. 이 수식은 A2 셀의 값이 B 열 어디에도 있는지 확인합니다。 데이터에 맞게 셀 참조를 수정하세요。
   • 2.3 그런 다음 「서식」 버튼을 클릭하세요。
   • 
3. 팝업된 「셀 형식 설정」 대화 상자에서 중복 항목을 강조할 색상을 지정한 후 「확인」을 클릭하세요。
   
4. 「새 서식 지정 규칙」 대화 상자로 돌아오면 「확인」 버튼을 클릭하세요。

결과：

이제 열 A 와 B 모두에 중복된 값이 아래 스크린샷과 같이 열 A 에서 강조 표시됩니다：

강력한 도구 – Kutools 로 두 열에서 중복 선택 및 강조 표시

때로는 강조 표시뿐 아니라 다른 위치로 복사하여 붙여넣기 위해 중복 선택해야 할 수도 있습니다。 이 경우 「Kutools for Excel」의 「동일한/다른 셀 선택」 기능이 이상적인 선택입니다。 이 기능은 중복 또는 고유 값을 식별하여 필요한 셀을 강조하고 선택할 수 있습니다。

「Kutools」 > 「선택」 > 「동일한/다른 셀 선택」을 클릭하세요。 「동일한/다른 셀 선택」 대화 상자에서 다음 작업을 수행하세요：

1. 「값 찾기 위치」 및 「기준」 상자에서 각각 비교할 원본 데이터과 비교 대상 데이터를 선택하세요；
2. 「기준」 섹션에서 「각 행」을 선택하세요；
3. 「찾기」 섹션에서 「동일한 값」을 선택하세요；
4. 「선택한 결과 처리」 섹션에서 중복 값을 강조할 배경색을 지정하세요；
5. 마지막으로 「확인」 버튼을 클릭하세요。
   

결과：

이제 열 A 와 B 모두에 중복된 값이 열 A 에서 강조되고 선택되어 원하는 셀로 복사하여 붙여넣을 준비가 되었습니다(스크린샷 참조)。

수식으로 두 열에서 중복 항목 찾아 추출하기

두 열 간의 중복 항목을 찾아 추출하려면 수식을 사용하여 중복 항목을 식별하고 추출할 수 있습니다。

결과를 표시할 빈 셀에 다음 수식을 복사하여 붙여넣은 후 채우기 핸들을 아래로 드래그하여 다른 셀에도 이 수식을 적용하세요。

=IF(ISERROR(MATCH(A2,$B$2:$B$10,0)),"",A2)

참고： 위 수식에서 “A2"는 중복 항목을 찾고자 하는 열의 첫 번째 셀이며， “B2:B10"은 비교 대상이 되는 데이터 목록을 나타냅니다。

결과：

보시다시피, A 열의 데이터가 B 열에 존재하면 해당 값이 표시되고， 그렇지 않으면 셀이 공백으로 남습니다。

VBA 코드로 두 열에서 중복 선택

이 섹션에서는 두 열 간의 중복 값을 식별하고 선택하는 VBA 코드를 작성하는 단계를 안내합니다。

단계 1: VBA 모듈 편집기를 열고 코드를 복사하세요

1. 「Alt + F11」 키를 눌러 「Microsoft Visual Basic for Applications」 창을 엽니다。
2. 열린 창에서 「삽입」 > 「모듈」을 클릭하여 새 빈 모듈을 만듭니다。
3. 그런 다음 아래 코드를 복사하여 빈 모듈에 붙여넣으세요。
   VBA 코드： 두 열 간의 중복 값 찾기 및 선택하기

   Sub Compare()
   'Update by Extendoffice
   Dim Range1 As Range, Range2 As Range, Rng1 As Range, Rng2 As Range, outRng As Range
   xTitleId = "KutoolsforExcel"
   On Error Resume Next
   Set Range1 = Application.Selection
   Set Range1 = Application.InputBox("Range1 :", xTitleId, Range1.Address, Type:=8)
   Set Range2 = Application.InputBox("Range2:", xTitleId, Type:=8)
   Application.ScreenUpdating = False
   For Each Rng1 In Range1
       xValue = Rng1.Value
       For Each Rng2 In Range2
           If xValue = Rng2.Value Then
               If outRng Is Nothing Then
                   Set outRng = Rng1
               Else
                   Set outRng = Application.Union(outRng, Rng1)
               End If
           End If
       Next
   Next
   outRng.Select
   Application.ScreenUpdating = True
   End Sub
   

단계 2: 이 VBA 코드를 실행하세요

1. 이 코드를 붙여넣은 후 “F5" 키를 눌러 코드를 실행하세요。 첫 번째 프롬프트 상자에서 중복 선택할 데이터 목록을 선택한 후 「확인」을 클릭하세요。
   
2. 두 번째 프롬프트 상자에서 비교 대상 데이터 목록을 선택한 후 「확인」을 클릭하세요(스크린샷 참조)。
   

결과：

이제 A 열과 B 열의 중복 값이 A 열에서 선택되므로 필요에 따라 셀에 색상을 채우거나 복사하여 붙여넣을 수 있습니다。

두 열을 행 단위로 일치 여부 비교하기

Excel 에서는 레코드 확인이나 데이터 추세 분석과 같은 작업을 위해 두 열을 행 단위로 비교하여 일치 여부를 확인해야 할 경우가 많습니다. Excel 에는 간단한 수식부터 특수 기능까지 다양한 방법이 있어 데이터 요구 사항에 가장 적합한 방법을 선택할 수 있습니다。 이제 이러한 작업을 효과적으로 수행할 수 있는 몇 가지 간단한 방법을 살펴보겠습니다。

수식으로 동일 행의 두 열 비교하기

Excel 수식은 열 간 데이터 비교를 위한 간단하면서도 강력한 방법을 제공합니다. 다음은 이를 활용하는 방법입니다. 예를 들어, A 열과 B 열에 데이터가 있을 때 두 열의 데이터가 일치하는지 확인하려면 다음 수식을 사용할 수 있습니다：

• 「같음 연산자(=) 사용하기」： 두 셀을 비교하는 가장 간단한 방법
• 「IF 함수」： 비교 결과를 더 유익하게 만들기 위해
• 「EXACT 함수」： 대소문자를 구분하는 비교

등호 연산자(=) 사용：

다음 수식을 입력하거나 복사한 후 「Enter」 키를 누르고 채우기 핸들을 아래로 드래그하여 모든 결과를 얻으세요. A 열과 B 열의 동일한 행에 있는 값이 동일하면 TRUE 를 반환하고, 그렇지 않으면 FALSE 를 반환합니다。 스크린샷 참조：

=A2=B2

IF 함수：

비교 결과를 더 직관적으로 만들려면 IF 함수를 사용하여 사용자 정의 메시지를 표시할 수 있습니다。

아래 수식을 입력하거나 복사한 후 「Enter」 키를 누르고 채우기 핸들을 아래로 드래그하여 모든 결과를 얻으세요。 값이 동일하면 「Match」를 반환하고， 다르면 「No Match」를 반환합니다。 스크린샷 참조：

=IF(A2=B2, "Match", "No Match")

EXACT함수：

대소문자를 구분하는 비교가 필요하다면 「EXACT 함수」를 사용하세요。

다음 수식을 입력하거나 복사한 후 「Enter」 키를 누르고 채우기 핸들을 아래로 드래그하여 모든 결과를 얻으세요。 값이 정확히 일치하면 「Match」를 반환하고， 다르면 「No Match」를 반환합니다。 스크린샷 참조：

=IF(EXACT(A2,B2), "Match", "No match")   

유용한 도구 - Kutools 로 동일 행에서 일치 항목 선택 및 강조하기

결과를 별도의 열에 표시하지 않고 두 열을 행 단위로 비교하여 일치 항목을 직접 선택하고 강조 표시하려면 Kutools for Excel 의 셀 비교 기능이 탁월한 선택입니다。 이를 통해 각 행 내에서 값이 일치하거나 다른 셀을 신속하게 선택하고 채울 색상를 적용할 수 있습니다。

「Kutools」 > 「셀 비교」를 클릭하고， 「셀 비교」 대화 상자에서 다음 작업을 수행하세요：

1. 「값 찾기 위치」 및 「기준」 상자에서 두 열의 데이터를 각각 선택하세요；
2. 「찾기」 섹션에서 「동일한 셀」을 선택하세요；
3. 「선택한 결과 처리」 섹션에서 일치 항목을 강조할 배경색을 지정하세요；
4. 마지막으로 「확인」 버튼을 클릭하세요。
   

결과：

이제 동일한 행의 일치 항목이 A 열에서 강조 표시되고 선택되어 원하는 셀로 복사하여 붙여넣을 수 있습니다。 스크린샷 참조：

두 열을 비교하여 동일 행에서 일치 항목 강조하기

두 열을 비교하여 동일한 행의 일치 항목을 강조 표시하는 작업은 Excel 의 조건부 서식 사용를 사용하면 효율적으로 수행할 수 있습니다。 다음은 행 단위 일치 항목을 식별하고 강조 표시하는 방법에 대한 안내입니다：

단계 1: 데이터 범위 선택

데이터 범위에서 행 단위 일치 항목을 강조 표시할 범위를 선택하세요。

단계 2: 조건부 서식 사용 기능 적용

1. 「홈」 > 「조건부 서식 사용」 > 「새 규칙」을 클릭하세요。 「새 서식 지정 규칙」 대화 상자에서 다음 작업을 수행하세요：
   • 2.1 「규칙 유형 선택」 목록 상자에서 「수식을 사용하여 서식을 지정할 셀 결정」을 선택하세요；
   • 2.2 「다음 수식이 TRUE인 경우 값에 서식 지정」 텍스트 상자에 아래 수식을 입력하세요；

     =$B2=$A2

   • 2.3 그런 다음 「서식」 버튼을 클릭하세요。
   • 
2. 팝업된 「셀 형식 설정」 대화 상자에서 일치 항목을 강조할 색상을 지정한 후 「확인」을 클릭하세요。
   
3. 「새 서식 지정 규칙」 대화 상자로 돌아오면 「확인」 버튼을 클릭하세요。

결과：

이제 동일한 행의 일치하는 값들이 한 번에 강조 표시됩니다。 스크린샷 참조：

두 열을 비교하여 일치하는 데이터 가져오기

Excel 에서 두 개의 데이터 집합을 다루면서 한 목록에서 다른 목록의 공통 항목을 찾아야 할 경우， 조회 수식이 이러한 일치 항목을 검색하는 데 적합한 해결책입니다。

Excel 에서 A 열에 과일 목록이 있고 B 열에 해당 과일의 판매 수치가 있다고 가정해 보겠습니다. 이제 D 열에 있는 과일 선택 목록과 비교하여 해당 판매 수치를 찾고자 합니다. 이때 B 열의 상대적 값을 어떻게 반환할 수 있을까요？

필요한 다음 수식 중 하나를 적용한 후 채우기 핸들을 아래로 드래그하여 나머지 셀에도 수식을 적용하세요。

• 모든 Excel 버전:

  =VLOOKUP(D2, $A$2:$B$6, 2, FALSE)

• Excel 365 및 Excel 2021:

  =XLOOKUP(D2, $A$2:$A$6, $B$2:$B$6)

결과：

일치 항목이 있으면 모든 상응하는 값이 표시되고， 그렇지 않으면 #N/A 오류가 반환됩니다。 스크린샷 참조：

관련 문서：