중복에 대해 두 개의 Excel 시트를 비교하는 방법: 5가지 빠른 방법

Excel에서 대규모 데이터 세트로 작업할 때 유사한 데이터가 포함된 여러 워크시트가 있는 것이 일반적입니다. 중복 레코드에 대해 이러한 시트를 비교하는 것은 특히 데이터가 다양한 열과 행에 흩어져 있는 경우 상당히 어려울 수 있습니다.

두 개의 Excel 시트에서 중복 항목을 찾는 가장 일반적인 방법은 다음을 사용하는 것입니다.

1. VLOOKUP, COUNTIF 또는 EXACT 함수

2. 조건부 서식

3. 파워 쿼리

4. 외부 도구 및 추가 기능

5. 중복에 대한 육안 검사

이 문서에서는 여러 워크시트에서 중복 항목을 정확히 찾아 처리하는 5가지 방법을 단계별로 안내합니다. 마지막에는 중복 값을 관리하는 것이 얼마나 쉬운지 알게 될 것입니다.

시작하자!

목차

• 샘플 워크시트 및 데이터
• 1. VLOOKUP, COUNTIF 또는 EXACT 함수를 사용하여 중복 찾기
  • A. VLOOKUP 기능을 사용하여 두 시트에서 중복을 찾는 방법은 무엇입니까?
    • 다른 통합 문서를 처리하는 경우 어떻게 합니까?
  • B. COUNTIF 함수를 사용하여 워크시트에서 중복 항목을 찾는 방법은 무엇입니까?
  • C. EXACT 함수를 사용하여 워크시트에서 중복 항목을 찾는 방법은 무엇입니까?
• 2. 중복 행에 조건부 서식을 사용하는 방법
  • 조건부 서식 규칙 관리자를 사용하는 방법
• 3. 파워 쿼리를 사용하여 워크시트에서 중복 항목을 찾는 방법
• 4. 워크시트 전체에서 중복을 식별하는 도구 및 추가 기능
• 5. 두 장의 중복 여부를 육안으로 확인하는 방법
• Excel 워크시트 준비를 위한 3가지 팁
• 오류 및 불일치를 처리하는 방법
• 마지막 생각들

샘플 워크시트 및 데이터

이 문서의 예제를 따르려면 첫 번째 시트의 A 열에 있는 이 데이터를 사용하여 새 Excel 통합 문서를 만듭니다.

• 사과

• 주황색

• 배

• 딸기

이 데이터를 두 번째 시트의 A열에 넣습니다.

• 배

• 딸기

• 사과

• 파인애플

워크시트는 다음과 같습니다.

워크시트를 설정한 후에는 VLOOKUP, COUNTIF 및 EXACT 함수부터 시작하여 두 시트에서 중복을 찾는 다양한 방법을 살펴볼 수 있습니다.

갑시다!

1. VLOOKUP, COUNTIF 또는 EXACT 함수를 사용하여 중복 찾기

Excel에는 VLOOKUP , COUNTIF 및 EXACT 의 세 가지 기본 제공 함수가 있어 중복 항목을 쉽게 찾을 수 있습니다 .

이러한 기능은 스프레드시트 내에서 데이터를 찾고 계산하고 비교하는 데 도움이 되도록 설계되어 중복 항목을 찾는 데 이상적인 도구입니다.

각 기능은 고유한 용도로 사용되며 사용 방법을 이해하면 데이터 분석 프로세스를 크게 간소화할 수 있습니다.

다음 섹션에서는 VLOOKUP, COUNTIF 및 EXACT 함수를 사용하여 Excel 워크시트에서 중복 항목을 효과적으로 식별하는 방법을 살펴봅니다.

A. VLOOKUP 기능을 사용하여 두 시트에서 중복을 찾는 방법은 무엇입니까?

VLOOKUP은 수직 조회를 나타냅니다. 이 함수를 사용하여 두 열 사이의 중복 값을 찾습니다. 구문은 다음과 같습니다.

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value : table_array의 첫 번째 열에서 검색하려는 값입니다.

• table_array : 검색하려는 데이터가 포함된 셀 범위입니다.

• col_index_num : 값을 반환하려는 table_array의 열 번호입니다.

• range_lookup : 선택 사항입니다. TRUE(대략 일치) 또는 FALSE(완전 일치)입니다. 기본값은 참입니다.

Excel 파일의 두 워크시트에서 함수를 사용하려면 수식에서 별도의 시트를 참조하는 방법을 알아야 합니다. 이렇게 하려면 시트 이름 뒤에 느낌표(!)와 셀 또는 셀 범위를 입력합니다.

예를 들어 다음은 동일한 통합 문서의 시트 2에서 A2에서 A5까지의 셀을 참조합니다. Sheet 2!$A$2:$A$5.

샘플 스프레드시트에서 VLOOKUP 함수를 사용하려면 다음 단계를 따르세요.

1. B2 셀을 선택하여 첫 번째 비교 결과를 표시합니다.

2. 이 수식을 입력합니다: =VLOOKUP(A2,Sheet2!$A$2:$A$5, 1, FALSE).

3. 비교 결과를 표시하려면 Enter 키를 누르십시오.

4. 수식을 작성하여 첫 번째 시트의 나머지 행 값을 비교합니다.

결과는 아래 그림과 같습니다.

중복이 없을 때 오류 대신 사용자에게 친숙한 메시지를 표시하여 표시를 정리할 수 있습니다.

예를 들어 이 수식은 찾은 값과 찾을 수 없는 값에 대해 각각 "예" 또는 "아니오"를 표시합니다.

=IF(ISNA(VLOOKUP(A2, 시트 2!$A$2:$A$5, 1, FALSE)), "아니요", "예")

VLOOKUP을 사용하여 두 열을 비교하는 방법 에 대한 문서에는 이 함수에 대한 더 많은 예제가 있습니다 .

다른 통합 문서를 처리하는 경우 어떻게 합니까?

워크시트가 별도의 통합 문서에 있는 경우 함수 사용법은 두 개의 Excel 파일에서 동일합니다. 그러나 두 번째 워크시트를 참조하는 것은 조금 더 복잡합니다.

다음을 수행해야 합니다.

• Excel 통합 문서의 이름을 괄호로 묶습니다.

• 워크시트의 이름을 따릅니다.

• 워크북과 워크시트를 따옴표로 묶습니다.

예를 들어 셀이 "WB 2.xlsx"라는 통합 문서의 Sheet2라는 시트에 있는 경우 형식은 다음과 같습니다.

'[WB 2.xlsx]시트2'!$A$2:$A$5

수식을 입력하기 전에 두 번째 통합 문서를 닫습니다. 그렇지 않으면 오류가 발생합니다.

B. COUNTIF 함수를 사용하여 워크시트에서 중복 항목을 찾는 방법은 무엇입니까?

Excel의 COUNTIF 함수는 지정된 기준을 충족하는 지정된 범위 내의 셀 수를 계산하는 데 사용됩니다.

여러 시트를 비교하기 위해 첫 번째 워크시트의 셀과 일치하는 두 번째 워크시트의 셀 수를 계산할 수 있습니다.

다음은 함수의 구문입니다.

=COUNTIF(범위, 기준)

• 범위 : 지정된 기준에 따라 계산할 셀 범위입니다.

• 조건 : 셀이 계산되기 위해 충족되어야 하는 조건입니다.

샘플 데이터와 함께 함수를 사용하려면 다음 단계를 따르십시오.

1. B2 셀을 선택하여 첫 번째 비교 결과를 표시합니다.

2. 다음 수식을 입력합니다. =COUNTIF(Sheet2!$A$2:$A$5, A2)

3. 비교 결과를 표시하려면 Enter 키를 누르십시오.

4. 수식을 작성하여 첫 번째 시트의 나머지 행 값을 비교합니다.

이 함수는 일부 셀에 대해 하나의 일치 항목을 찾고 다른 셀에 대해서는 일치하지 않습니다. 비교 셀에 개수가 표시됩니다. 다음은 샘플 데이터의 결과입니다.

COUNTIF 함수는 Excel에서 비어 있지 않은 셀 계산 과 같은 다른 유용한 작업에 사용할 수 있습니다 .

C. EXACT 함수를 사용하여 워크시트에서 중복 항목을 찾는 방법은 무엇입니까?

Excel의 EXACT 함수를 사용하여 두 개의 서로 다른 Excel 워크시트의 동일한 셀 내에서 중복 항목을 찾을 수도 있습니다. 구문은 다음과 같습니다.

=EXACT(텍스트1, 텍스트2)

• text1 은 비교할 첫 번째 텍스트 문자열입니다.

• text2 는 비교할 두 번째 텍스트 문자열입니다.

다음과 같이하세요:

1. 셀 B2를 선택합니다.

2. 수식 입력 =EXACT(A2, Sheet2!A2)

3. 비교 결과를 표시하려면 Enter 키를 누르십시오. 수식은 두 값이 동일하면 TRUE를 반환하고 그렇지 않으면 FALSE를 반환합니다.

4. 수식을 작성하여 첫 번째 시트의 나머지 행 값을 비교합니다.

이 방법은 셀 범위에서 중복 항목을 검색하지 않습니다. 대신 다른 시트의 동일한 셀을 기반으로 일치 항목만 찾습니다.

이는 몇 가지 예외만 예상되는 정렬된 데이터에 유용할 수 있습니다.

VLOOKUP, COUNTIF 및 EXACT 함수는 중복을 찾는 데 유용하지만 Excel은 다목적 프로그램이며 다른 옵션이 있습니다. 다음 섹션에서는 조건부 서식을 사용하여 두 시트에서 중복을 식별하는 방법을 살펴봅니다.

2. 중복 행에 조건부 서식을 사용하는 방법

이 섹션에서는 조건부 서식을 사용하여 두 Excel 워크시트에서 중복 행을 찾고 강조 표시하는 방법을 알아봅니다.

조건부 서식 규칙을 만들려면 다음 단계를 따르세요.

1. 데이터가 포함된 셀 범위를 선택합니다(이 경우 A2:A5).

2. Excel 리본에서 "홈" 탭을 클릭합니다.

3. "스타일" 그룹에서 "조건부 서식"을 클릭합니다.

4. 드롭다운 메뉴에서 "새 규칙"을 선택합니다.

다음 작업은 사용할 규칙에 대한 공식을 제공하는 것입니다. 다음과 같이하세요:

1. 대화 상자에서 "수식을 사용하여 서식을 지정할 셀 결정"을 선택하십시오.

2. 다음 수식을 입력합니다. =COUNTIF(Sheet2!$A$2:$A$5, A2) > 0

마지막으로 중복 셀에 대해 선호하는 서식을 적용합니다.

1. 서식 버튼을 클릭하여 셀 서식 대화 상자를 엽니다.

2. 형식을 선택하십시오(예: 노란색 배경색으로 중복 채우기).

3. 확인을 클릭합니다.

이제 중복 데이터가 노란색으로 강조 표시됩니다.

조건부 서식 규칙 관리자를 사용하는 방법

조건부 서식 규칙을 만든 후에는 조건부 서식 규칙 관리자를 사용하여 관리할 수 있습니다.

관리자에 액세스하려면:

1. 홈 탭으로 이동합니다.

2. 조건부 서식을 클릭합니다.

3. "규칙 관리"를 선택합니다.

선택한 시트에 적용된 모든 조건부 서식 규칙 목록이 표시됩니다. 규칙을 선택하고 해당 버튼을 클릭하여 규칙의 순서를 편집, 삭제 또는 변경할 수 있습니다.

다른 시트에 동일한 규칙을 적용하려면 다음 단계를 따르십시오.

1. 두 번째 시트에서 비교하려는 범위를 선택합니다.

2. 조건부 서식 규칙 관리자로 이동합니다.

3. 규칙을 선택하고 규칙 복제를 클릭한 다음 규칙 편집을 누르십시오.

4. "Sheet2"를 비교할 첫 번째 시트의 이름으로 바꿉니다.

두 시트에 조건부 서식 규칙을 적용했으므로 선택한 서식에 따라 중복 항목이 강조 표시됩니다.

비교하려는 모든 데이터를 포함하도록 필요에 따라 수식에서 범위 및 셀 참조를 조정해야 합니다.

조건부 서식은 약간 원시적으로 보일 수 있습니다. 더 세밀한 제어를 원한다면 파워 쿼리가 해답이 될 수 있습니다! 다음 섹션에서는 파워 쿼리를 사용하여 중복 항목을 찾는 방법에 대해 설명합니다.

3. 파워 쿼리를 사용하여 워크시트에서 중복 항목을 찾는 방법

파워 쿼리는 Microsoft Excel의 데이터 변환 및 데이터 준비 도구입니다. 동일한 값을 식별하는 것은 도구로 수행할 수 있는 많은 분석 작업 중 하나일 뿐입니다.

이렇게 하려면 먼저 두 워크시트의 데이터를 별도의 테이블로 가져와야 합니다. 각 시트 내에서 다음 단계를 따르세요.

1. 셀 범위를 마우스 오른쪽 단추로 클릭합니다.

2. "테이블/범위에서 데이터 가져오기"를 선택합니다.

3. 테이블 이름을 적절하게 수정하십시오.

두 시트를 모두 가져온 후 첫 번째 작업은 데이터를 병합하는 것입니다.

1. 데이터 탭으로 이동합니다.

2. "데이터 가져오기"를 클릭합니다.

3. "쿼리 결합"을 선택합니다.

4. "병합"을 선택하고 두 테이블을 선택합니다.

5. 두 개의 키 열을 클릭합니다.

6. "조인 종류"로 "내부"를 선택하고 확인을 클릭합니다.

Excel 시트의 두 테이블에서 결합된 데이터와 함께 Power Query 편집기가 열립니다. 각 테이블에서 하나씩 두 개의 열이 표시됩니다. 중복 값에만 관심이 있으므로 두 번째 열을 제거할 수 있습니다.

Power Query 편집기에서 "닫기 및 로드"를 클릭하여 복제본을 새 워크시트에 로드할 수 있습니다.

이 강력한 기능의 더 많은 측면을 살펴보려면 Excel에서 파워 쿼리를 사용하는 방법 에 대한 문서의 예제를 따르십시오 .

Excel에는 중복 항목을 원활하게 찾는 기능을 추가하는 타사 도구 및 추가 기능도 있으므로 다음 섹션에서 이러한 도구 중 일부를 살펴보겠습니다.

4. 워크시트 전체에서 중복을 식별하는 도구 및 추가 기능

외부 도구 및 추가 기능은 기본 Excel 기능에서 사용할 수 없는 고급 기능을 제공할 수 있습니다. 이러한 도구를 사용하면 시트가 중복되었는지 비교하는 프로세스를 더욱 간소화할 수 있습니다.

스프레드시트 비교는 두 개의 통합 문서를 나란히 비교하여 차이점을 강조 표시하고 중복을 쉽게 식별할 수 있는 Microsoft 도구입니다. Microsoft 웹 사이트에서 다운로드 할 수 있습니다 .

중복 검색 프로세스를 자동화하기 위해 설치할 수 있는 몇 가지 추가 기능이 있습니다. 한 가지 예는 " Duplicate Remover "입니다. 추가 기능을 설치하려면:

1. 삽입 탭으로 이동합니다.

2. "추가 기능 가져오기"를 클릭합니다.

3. "중복"을 검색합니다.

4. 선택한 도구에서 "추가"를 클릭합니다.

5. 두 장의 중복 여부를 육안으로 확인하는 방법

다른 모든 방법이 실패하면 눈을 사용하십시오! Excel의 창 정렬 대화 상자를 사용하면 여러 워크시트 또는 통합 문서를 나란히 볼 수 있습니다.

중복 항목을 직접 찾지는 않지만 워크시트 또는 통합 문서에서 데이터를 시각적으로 비교하여 중복 항목을 찾는 데 도움이 될 수 있습니다. 다음과 같이하세요:

1. Excel 리본에서 "보기" 탭을 클릭합니다.

2. "창" 그룹에서 "모두 정렬"을 클릭합니다.

3. 정렬 옵션을 선택합니다(예: "수직" 또는 "수평").

이렇게 하면 두 시트가 나란히 또는 다른 시트 위에 표시됩니다. 이제 각 시트의 데이터를 수동으로 비교하여 중복을 식별할 수 있습니다.

일치하는 항목을 찾으려면 데이터를 스크롤하고 각 값을 시각적으로 검사해야 합니다.

이 방법은 수동 비교가 필요하므로 대규모 데이터 세트에는 효율적이지 않습니다. 이 문서의 다른 방법을 사용하면 더 큰 데이터 세트에서 중복 항목을 찾는 데 더 효과적입니다.

이것이 Excel 시트에서 중복 값을 찾는 일반적인 방법 중 마지막 방법입니다! 다음 섹션에서는 워크시트 준비에 대한 몇 가지 팁을 제공합니다.

Excel 워크시트 준비를 위한 3가지 팁

여러 시트를 비교하기 전에 데이터세트의 열과 행이 제대로 정렬되어 있는지 확인하세요.

두 Excel 시트 의 구조와 헤더 이름이 같은지 확인하십시오 . 필요한 경우 두 시트의 열을 서로 일치하도록 재정렬할 수 있습니다.

다음은 정확한 비교를 위한 세 가지 제안입니다.

1. 두 시트에서 동일한 순서로 데이터를 정렬합니다. 이렇게 하면 Excel 기능이 효과적으로 작동하기가 더 쉬워집니다.

2. 일관된 형식, 대소문자 및 데이터 유형을 사용하여 데이터를 정규화합니다 . 이렇게 하면 사소한 차이로 인해 일치하지 않는 항목을 방지할 수 있습니다.

3. 비교 프로세스를 방해할 수 있으므로 불필요한 빈 행이나 열을 제거하십시오.

고유한 값을 세어 데이터 세트에 얼마나 많은 중복이 있는지 평가할 수 있습니다. 이 비디오는 여러 가지 방법을 안내합니다.

오류 및 불일치를 처리하는 방법

데이터의 불일치는 비교 프로세스에 영향을 줄 수 있습니다. 다음은 불일치를 해결하기 위한 네 가지 팁입니다.

1. 동일한 열에서 텍스트와 숫자 값을 혼합하는 것과 같은 데이터 유형의 불일치를 확인합니다.

2. 날짜, 숫자 및 기타 데이터 유형에 일관된 형식이 사용되는지 확인합니다.

3. 누락되거나 잘못된 항목이 있는지 데이터를 검사하고 필요한 경우 업데이트하십시오.

4. 데이터 세트 내에서 약어 또는 일관되지 않은 명명 규칙을 표준화합니다.

마지막 생각들

두 개의 Excel 워크시트에서 중복 항목을 찾는 것은 데이터 관리 및 분석을 위한 필수 작업으로 데이터 무결성과 정확성을 보장합니다. Excel은 고유한 장점과 제한 사항이 있는 중복 항목을 식별하는 여러 기술을 제공합니다.

방법의 선택은 사용자의 요구, 데이터 세트의 크기 및 복잡성, 원하는 결과에 따라 다릅니다. 더 작은 데이터 세트와 간단한 비교의 경우 VLOOKUP , COUNTIF 또는 조건부 서식을 사용하는 것으로 충분할 수 있습니다.

더 큰 데이터 세트 또는 더 복잡한 데이터 변환의 경우 파워 쿼리는 중복 찾기를 포함하여 광범위한 데이터 준비 작업을 처리할 수 있는 강력하고 유연한 도구입니다.

마무리하기 위해 Excel 시트를 중복하여 비교하는 것은 도구 상자에 있는 매우 편리한 기술입니다. 이 기사의 요령을 사용하면 성가신 중복 항목을 찾아내고 데이터를 깔끔하게 유지할 수 있습니다.

우리를 믿으세요. 이 일을 더 잘하게 되면 데이터 작업을 순조롭게 처리하고 주변 사람들에게 깊은 인상을 남길 것입니다!