Excel에서 두 목록을 비교하는 방법

Excel은 효율적인 데이터 처리 프로그램입니다. 그리고 정보 분석의 방법 중 하나는 두 목록을 비교하는 것입니다. Excel에서 두 목록을 올바르게 비교하면 이 프로세스를 구성하는 것이 매우 쉽습니다. 오늘 논의할 몇 가지 요점만 따르면 충분합니다. 이 방법의 실제 구현은 전적으로 특정 순간에 개인이나 조직의 필요에 달려 있습니다. 따라서 몇 가지 가능한 경우를 고려해야 합니다.

Excel에서 두 목록 비교

물론 두 목록을 수동으로 비교할 수도 있습니다. 하지만 시간이 오래 걸릴 것입니다. Excel에는 데이터를 빠르게 비교할 수 있을 뿐만 아니라 눈으로 쉽게 얻을 수 없는 정보를 얻을 수 있는 자체 지능형 도구 키트가 있습니다. 좌표 A와 B가 있는 두 개의 열이 있다고 가정합니다. 일부 값은 그 안에 반복됩니다.

문제의 공식화

따라서 이러한 열을 비교해야 합니다. 두 문서를 비교하는 절차는 다음과 같습니다.

1. 이러한 각 목록의 고유 셀이 동일하고 고유 셀의 총 수가 동일하고 셀이 동일하면 이러한 목록은 동일한 것으로 간주될 수 있습니다. 이 목록의 값이 쌓이는 순서는 그다지 중요하지 않습니다.
2. 고유 값 자체는 같지만 반복 횟수가 다른 경우 목록의 부분적 일치에 대해 이야기할 수 있습니다. 따라서 이러한 목록에는 다른 수의 요소가 포함될 수 있습니다.
3. 두 목록이 일치하지 않는다는 사실은 다른 고유 값 집합으로 표시됩니다.

이 세 가지 조건이 동시에 우리 문제의 조건입니다.

문제의 해결책

목록을 더 쉽게 비교할 수 있도록 두 개의 동적 범위를 생성해 보겠습니다. 그들 각각은 각 목록에 해당합니다.

두 목록을 비교하려면 다음을 수행하십시오.

1. 별도의 열에서 두 목록에 고유한 고유 값 목록을 만듭니다. 이를 위해 다음 공식을 사용합니다. ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). 수식 자체는 배열 수식으로 작성해야 합니다.
2. 데이터 배열에서 각각의 고유한 값이 몇 번이나 발생하는지 알아봅시다. 이를 수행하는 공식은 다음과 같습니다. =COUNTIF(목록1,D5) 및 =COUNTI(목록2,D5).
3. 이 범위에 포함된 모든 목록에서 반복 횟수와 고유 값 수가 모두 동일한 경우 함수는 값 0을 반환합니다. 이는 일치가 XNUMX%임을 나타냅니다. 이 경우 이러한 목록의 제목은 녹색 배경을 갖게 됩니다.
4. 모든 고유 콘텐츠가 두 목록에 모두 있는 경우 수식에서 반환됨 =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) 값은 1이 됩니다. E2에 2이 포함되어 있지 않지만 이러한 값이 셀 EXNUMX 및 FXNUMX에 포함되어 있으면 이 경우 범위는 일치하는 것으로 인식되지만 부분적으로만 인식됩니다. 이 경우 해당 목록의 제목이 주황색으로 바뀝니다.
5. 그리고 위에서 설명한 수식 중 하나가 XNUMX이 아닌 값을 반환하면 목록은 완전히 일치하지 않습니다.

이것은 수식을 사용하여 일치 항목에 대한 열을 분석하는 방법에 대한 질문에 대한 답변입니다. 보시다시피 함수를 사용하면 언뜻보기에는 수학과 관련이없는 거의 모든 작업을 구현할 수 있습니다.

테스트 예

우리 버전의 테이블에는 위에서 설명한 각 유형의 세 가지 유형의 목록이 있습니다. 부분적으로 그리고 완전히 일치하는 것과 일치하지 않는 것이 있습니다.

데이터를 비교하기 위해 이러한 목록 쌍을 교대로 삽입하는 범위 A5:B19를 사용합니다. 비교 결과가 무엇인지에 대해서는 원래 목록의 색상으로 이해합니다. 완전히 다른 경우 빨간색 배경이 됩니다. 데이터의 일부가 동일하면 노란색입니다. 완전한 ID의 경우 해당 제목은 녹색입니다. 결과에 따라 색상을 만드는 방법은 무엇입니까? 이를 위해서는 조건부 서식이 필요합니다.

두 가지 방법으로 두 목록의 차이점 찾기

목록이 동기식인지 여부에 따라 차이점을 찾는 두 가지 방법을 더 설명하겠습니다.

옵션 1. 동기 목록

이것은 쉬운 옵션입니다. 이러한 목록이 있다고 가정합니다.

값이 수렴하지 않은 횟수를 확인하려면 다음 공식을 사용할 수 있습니다. =SUMPRODUCT(—(A2:A20<>B2:B20)). 결과적으로 0이 나오면 두 목록이 동일함을 의미합니다.

옵션 2: 섞은 목록

목록에 포함된 개체의 순서가 동일하지 않은 경우 조건부 서식과 같은 기능을 적용하고 중복 값에 색상을 지정해야 합니다. 또는 기능을 사용 카운티, 이를 사용하여 한 목록의 요소가 두 번째 목록에서 발생하는 횟수를 결정합니다.

2개의 열을 행별로 비교하는 방법

두 열을 비교할 때 종종 다른 행에 있는 정보를 비교해야 합니다. 이를 위해 운영자가 우리를 도울 것입니다. 만약. 실제로 어떻게 작동하는지 살펴보겠습니다. 이를 위해 몇 가지 예시적인 상황을 제시합니다.

예시. 한 행의 일치 및 차이점에 대해 2개의 열을 비교하는 방법

같은 행에 있지만 다른 열에 있는 값이 같은지 분석하기 위해 함수를 작성합니다. IF. 데이터 처리 결과가 표시될 보조 열의 각 행에 수식이 삽입됩니다. 그러나 각 행에 처방할 필요는 전혀 없으며 이 열의 나머지 셀에 복사하거나 자동 완성 마커를 사용하면 됩니다.

두 열의 값이 동일한지 여부를 이해하려면 이러한 수식을 작성해야 합니다. =IF(A2=B2, "일치", ""). 이 함수의 논리는 매우 간단합니다. A2 셀과 B2 셀의 값을 비교하고 동일하면 "일치" 값을 표시합니다. 데이터가 다른 경우 값을 반환하지 않습니다. 셀 사이에 일치하는 항목이 있는지 확인할 수도 있습니다. 이 경우 사용되는 공식은 다음과 같습니다. =IF(A2<>B2, "일치하지 않음", ""). 원칙은 동일하며 먼저 확인이 수행됩니다. 셀이 기준을 충족하는 것으로 판명되면 "일치하지 않음" 값이 표시됩니다.

수식 필드에 다음 수식을 사용하여 값이 같으면 "일치"하고 다를 경우 "일치하지 않음"을 모두 표시할 수도 있습니다. =IF(A2=B2; "일치", "일치하지 않음"). 등호 연산자 대신 부등식 연산자를 사용할 수도 있습니다. 이 경우 표시되는 값의 순서만 약간 다릅니다. =IF(A2<>B2, "일치하지 않음", "일치하다"). 수식의 첫 번째 버전을 사용한 후 결과는 다음과 같습니다.

이 수식 변형은 대소문자를 구분하지 않습니다. 따라서 한 열의 값이 대문자로 작성되었다는 점에서만 다른 열과 다른 경우 프로그램은 이러한 차이를 인식하지 못합니다. 비교를 대소문자를 구분하려면 기준에서 함수를 사용해야 합니다. 정확한. 나머지 인수는 변경되지 않은 상태로 유지됩니다. =IF(EXACT(A2,B2), "일치", "고유").

한 행의 일치 항목에 대해 여러 열을 비교하는 방법

전체 기준 세트에 따라 목록의 값을 분석하는 것이 가능합니다.

1. 모든 곳에서 동일한 값을 갖는 행을 찾으십시오.
2. 단 두 개의 목록에서 일치하는 행을 찾으십시오.

이러한 각 경우에 진행하는 방법에 대한 몇 가지 예를 살펴보겠습니다.

예시. 테이블의 여러 열에서 한 행에서 일치하는 항목을 찾는 방법

필요한 정보가 포함된 일련의 열이 있다고 가정합니다. 우리는 값이 동일한 행을 결정하는 작업에 직면해 있습니다. 이렇게 하려면 다음 공식을 사용해야 합니다. =IF(AND(A2=B2,A2=C2), "일치", " ").

테이블에 열이 너무 많으면 함수와 함께 사용하기만 하면 됩니다. IF 연산자 카운티: =IF(COUNTIF($A2:$C2,$A2)=3;”일치”;” “). 이 수식에 사용된 숫자는 확인할 열의 수를 나타냅니다. 다를 경우 상황에 맞는 최대한 많이 작성해야 합니다.

예시. 테이블의 두 열에서 한 행에서 일치하는 항목을 찾는 방법

한 행의 값이 테이블의 값과 두 열의 값이 일치하는지 확인해야 한다고 가정해 보겠습니다. 이렇게 하려면 함수를 조건으로 사용해야 합니다. OR, 여기서 각 열의 동등성을 다른 열에 번갈아 씁니다. 다음은 예입니다.

다음 공식을 사용합니다. =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). 테이블에 열이 많은 상황이 있을 수 있습니다. 이 경우 수식이 거대해지고 필요한 모든 조합을 선택하는 데 많은 시간이 걸릴 수 있습니다. 이 문제를 해결하려면 함수를 사용해야 합니다. 카운티: =IF(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0; "고유한 문자열"; "고유하지 않은 문자열")

총 두 가지 기능이 있음을 알 수 있습니다. 카운티. 첫 번째 열에서는 A2와 유사성이 있는 열의 수를 교대로 결정하고 두 번째 열에서는 B2 값으로 유사성 수를 확인합니다. 이 수식으로 계산한 결과 값이 XNUMX이면 이 열의 모든 행이 고유하고 더 많으면 유사성이 있음을 나타냅니다. 따라서 두 수식으로 계산하고 최종 결과를 더한 결과 값이 XNUMX이면 텍스트 값 "고유 문자열"이 반환되고 이 숫자가 더 크면 이 문자열이 고유하지 않다고 기록됩니다.

일치를 위해 Excel에서 2개의 열을 비교하는 방법

이제 예를 들어 보겠습니다. 두 개의 열이 있는 테이블이 있다고 가정해 보겠습니다. 일치하는지 확인해야 합니다. 이렇게 하려면 함수도 사용할 수식을 적용해야 합니다. IF, 그리고 연산자 카운티: =IF(COUNTIF($B:$B,$A5)=0, "B열에 일치하는 항목이 없습니다.", "B열에 일치하는 항목이 있습니다.")

추가 조치가 필요하지 않습니다. 이 공식으로 결과를 계산한 후 함수의 세 번째 인수 값이 IF 성냥. 없는 경우 두 번째 인수의 내용입니다.

Excel에서 일치하는 두 열을 비교하고 색상으로 강조 표시하는 방법

일치하는 열을 시각적으로 쉽게 식별할 수 있도록 색상으로 강조 표시할 수 있습니다. 이렇게 하려면 "조건부 서식" 기능을 사용해야 합니다. 실제로 봅시다.

여러 열에서 색상별로 일치 항목 찾기 및 강조 표시

일치 항목을 확인하고 강조 표시하려면 먼저 검사가 수행될 데이터 범위를 선택한 다음 "홈" 탭에서 "조건부 서식" 항목을 열어야 합니다. 거기에서 셀 선택 규칙으로 "Duplicate Values"를 선택합니다.

그런 다음 왼쪽 팝업 목록에서 "반복"옵션을 찾고 오른쪽 목록에서 선택에 사용할 색상을 선택하는 새 대화 상자가 나타납니다. "확인"버튼을 클릭하면 유사한 모든 셀의 배경이 선택됩니다. 그런 다음 눈으로 열을 비교하십시오.

일치하는 선 찾기 및 강조 표시

문자열이 일치하는지 확인하는 기술이 약간 다릅니다. 먼저 추가 열을 생성해야 하며 거기에서 & 연산자를 사용하여 결합된 값을 사용합니다. 이렇게 하려면 다음 형식의 수식을 작성해야 합니다. =A2&B2&C2&D2.

생성되고 결합된 값이 포함된 열을 선택합니다. 다음으로 열에 대해 위에서 설명한 것과 동일한 일련의 작업을 수행합니다. 중복된 선은 지정한 색상으로 강조 표시됩니다.

우리는 반복을 찾는 데 어려움이 없음을 알 수 있습니다. Excel에는 이를 위해 필요한 모든 도구가 포함되어 있습니다. 이 모든 지식을 실행에 옮기기 전에 연습하는 것이 중요합니다.