엑셀 파일을 어렵게 비교하고 있군요—여기 더 나은 두 가지 방법이 있습니다

월요일 아침이고, '업데이트된' Excel 통합 문서 사본이 당신의 받은편지함에 있습니다. 하지만 열어보면 무엇이 변경되었는지 알아내는 것이 종종 어렵습니다. 차이를 찾아내는 게임을 하는 대신, 이러한 내장된 Excel 도구를 사용하여 몇 초 만에 차이를 강조 표시하세요.

방법 1: 조건부 서식으로 일치하지 않는 값을 강조 표시

시각적 알림을 사용하여 작은 데이터 세트를 감사하세요

조건부 서식은 두 데이터 세트 간의 차이를 빠르고 시각적으로 확인할 수 있는 방법입니다. 그러나 두 버전이 동일한 통합 문서에 있을 때만 작동하며, Excel은 조건부 서식 수식이 다른 통합 문서를 참조하도록 허용하지 않습니다. 만약 서로 다른 통합 문서에 있다면, 두 통합 문서를 모두 열고 다음 단계를 따라 통합하십시오:

1. 업데이트된 시트의 탭을 마우스 오른쪽 버튼으로 클릭한 다음, 이동 또는 복사를 클릭합니다.
2. 드롭다운 메뉴 To book에서 원본 워크북을 선택하세요.
3. 업데이트된 시트를 원본 시트 오른쪽에 표시하려면 끝으로 이동을 선택하세요. 또한, 이 시트를 다른 통합 문서에 복제만 하려면 복사본 만들기를 선택하고, 영구적으로 이동하려면 이 옵션을 선택 해제하세요.
4. 확인을 클릭하세요.

이제 두 워크시트가 하나의 파일에 모두 있으면, 보기 탭에서 새 창을 클릭하여 파일의 두 번째 인스턴스를 연 다음, 모두 정렬 > 세로를 클릭하여 나란히 배치합니다. 이제 두 워크시트를 동시에 나란히 열어볼 수 있습니다.

이제 Excel에 두 버전 간의 차이점을 강조 표시하도록 지시할 준비가 되었습니다:

1. 원본 시트에서 전체 데이터 범위를 선택하세요.
2. 홈 탭에서 조건부 서식 > 새 규칙을 클릭합니다.
3. 클릭 수식을 사용하여 서식을 지정할 셀 결정.
4. 대화 상자에서 서식을 클릭한 다음 연한 빨간색과 같은 강조 색을 선택합니다.
5. 원본 데이터세트에서 첫 번째 셀을 선택하고 <>를 입력한 후 업데이트된 시트에서 해당 셀을 선택하여 수식을 만듭니다. 각 참조에서 절대 잠금을 제거하려면 F4를 세 번 누릅니다.

제가 예제에서 사용한 공식은 다음과 같습니다:

=A2<>Sales_Updated!A2

이 방법은 작고 깨끗한 데이터셋에서는 잘 작동하지만, 주요 약점이 있습니다: 완벽한 행 정렬에 의존합니다. 누군가 시트 중 하나에 행을 삽입, 삭제 또는 순서를 변경하면, 엑셀은 위치별로 계속 비교하게 되어 광범위한 잘못된 불일치가 발생합니다.

방법 2: Power Query 조인을 사용하여 행 데이터를 비교하기

대규모 워크시트를 위한 내구성 있는 감사 추적 기록을 구축하십시오

더 큰 데이터 세트의 버전을 비교해야 하는 경우, Power Query가 가장 강력한 방법입니다. 셀 위치별로 비교하는 대신, 사용자가 정의한 값을 기반으로 데이터를 일치시켜 행 이동과 구조적 변경에 강합니다.

먼저, 두 데이터세트가 모두 Excel 표(Ctrl+T)로 형식이 지정되었는지 확인한 후, 두 데이터를 모두 연결로서 Power Query 편집기에 로드합니다:

1. 첫 번째 테이블을 Power Query로 로드하려면 해당 테이블의 셀 중 하나를 선택한 다음 데이터 > 테이블/범위에서로 이동하세요.
2. 편집기에서 닫기 및 불러오기 위치.를 클릭합니다
3. 연결만 만들기를 선택하고 확인을 클릭합니다.
4. 두 번째 표에 대해서도 이 단계를 반복하세요.

두 테이블을 모두 Power Query에 불러온 후, 비교를 시작할 수 있습니다. 먼저 원본 테이블을 업데이트된 버전과 비교하는 것부터 시작하세요:

1. 쿼리 및 연결 창에서 쿼리 중 하나를 더블 클릭하여 편집기를 다시 열세요.
2. 그런 다음, 홈 탭에서 쿼리 병합 > 새로 쿼리 병합을 클릭합니다.
3. 병합 대화 상자에서, 위에 원본 테이블을 선택하고 아래에 업데이트된 테이블을 선택합니다.
4. 상단 테이블에서 첫 번째 열을 클릭한 다음, 하단 테이블에서 동일한 열을 클릭합니다. 그런 다음, 나머지 모든 열에 대해 이 과정을 반복할 때 Ctrl 키를 누릅니다. 각 열 쌍은 일치 순서를 나타내는 번호가 매겨져 있습니다.
5. 조인 유형으로 Left Anti를 선택하고 확인을 클릭합니다. 이렇게 하면 원본에는 존재하지만 업데이트된 버전에서는 정확히 일치하지 않는 행이 반환됩니다. 이는 해당 행이 제거되었거나 일치가 깨질 정도로 변경되었음을 의미합니다.

이제 새로 합쳐진 쿼리에 몇 가지 작은 수정을 하세요:

1. 병합된 두 번째 테이블(중첩 테이블 열)이 포함된 열을 제거합니다.
2. 쿼리 이름을 v1_Changed와 같이 변경하세요.

이제 같은 과정을 반복해야 하지만, 병합 대화 상자에서 테이블 순서를 반대로 설정하세요—업데이트된 테이블을 위에 선택하고 원본 테이블을 아래에 선택한 후 동일한 Left Anti 조인을 실행하고 쿼리 이름을 변경합니다(예: v2_Changed). 양방향으로 병합을 실행하면 두 가지 결과를 얻을 수 있습니다: 하나는 원본 데이터셋에만 존재하는 행을 보여줍니다(업데이트에서 제거되거나 변경된 행), 다른 하나는 업데이트된 데이터셋에만 존재하는 행을 보여줍니다(새로운 행 또는 변경된 행).

이 작업을 완료하면 닫기 및 로드 대상를 클릭하고, 테이블을 선택한 후 확인을 클릭하여 이 쿼리를 두 개의 새 워크시트에 로드합니다.

이 방법의 장점은 두 소스 데이터셋 중 하나에 새 행을 추가하고 데이터 탭에서 모두 새로 고침을 클릭하면, 새 레코드가 자동으로 "변경됨" 쿼리에 포함되어 정보를 최신 상태로 유지한다는 점입니다.

다음에 "업데이트된" 워크북이 당신의 받은 편지함에 도착하면, 데이터를 기준으로 도구를 선택하세요. 작은 목록을 5분 정도 빠르게 점검할 때는 조건부 서식 트릭이 가장 유용합니다. 하지만 행 순서를 신뢰할 수 없는 대규모 데이터셋에서는 Power Query가 무엇도 누락되지 않도록 보장하는 최고의 방법입니다. 어느 쪽이든, 번거로운 수작업을 반복 가능하고 자동화된 프로세스로 바꾼 셈입니다.