두 Excel 시트를 비교해 차이점을 찾는 방법
고유 ID로 Excel 시트를 비교해 바뀐 값과 추가되거나 누락된 기록을 찾습니다. 예제로 양방향 대조와 중복 ID 처리를 익히고 AI에 비교 범위를 명확하게 지정해 보세요.
주문 내보내기 파일 두 개의 행 수가 같아도 서로 다른 주문이 들어 있을 수 있습니다. 한쪽 시트를 정렬하거나 기록을 삽입했다면 A2와 A2를 비교하는 것만으로는 충분하지 않습니다.
업무 기록은 변하지 않는 식별자로 먼저 연결한 다음, 그 식별자에 속하는 필드를 비교해야 합니다. 결과는 변경된 기록, 이전 시트에만 있는 기록, 새 시트에만 있는 기록으로 구분하세요. 원본 두 개를 모두 보존하고 보고서는 별도 시트에 만듭니다.
비교할 내용을 먼저 정하기
| 확인할 질문 | 적절한 방법 |
|---|---|
| 주문 금액이 바뀌었나요? | 주문 ID를 연결한 뒤 금액 비교 |
| 어떤 주문이 추가되거나 삭제됐나요? | 양방향으로 ID 존재 여부 확인 |
| 수식이나 셀 서식이 바뀌었나요? | 반환된 값만이 아니라 통합 문서 버전 비교 |
| 작은 시트 두 개가 눈으로 보기에 다른가요? | 나란히 표시한 뒤 차이 조사 |
Microsoft의 워크시트 나란히 보기는 눈으로 확인할 때 도움이 됩니다. 하지만 업무 식별자를 기준으로 기록을 맞춰 주지는 않습니다. 아래 예제는 서식이나 수식 문자열이 아닌 값을 비교합니다.
고유한 키부터 정하기
각 원본에 한 번씩만 나와야 하는 주문 ID, 직원 ID 등의 식별자를 사용하세요. 이름만으로는 적절하지 않은 경우가 많습니다. 주문 하나에 여러 명세 행이 있다면 주문 ID만으로는 고유하지 않습니다. 주문 ID와 명세 번호처럼 정의가 명확한 조합이 필요합니다.
연결하기 전에 빈 ID, 중복, 앞자리 0, 텍스트와 숫자 형식의 차이를 살펴보세요. ID를 정리해도 원래 ID를 함께 보관합니다. 억지로 일치시키려고 의미 있는 공백이나 앞자리 0을 없애지 마세요. 다른 표에서 필드를 가져오는 작업은 열 연결 가이드에서 다룹니다.
아래 수식은 영어 함수 이름과 쉼표를 사용합니다. Excel 언어 및 지역 설정에 따라 번역된 함수 이름이나 세미콜론이 필요할 수 있습니다. 예제의 ID에는 와일드카드 문자가 없고, 금액은 빈칸이 아닌 숫자입니다.
작은 예제로 비교해 보기
통합 문서 복사본에 Old와 New라는 시트 두 개를 만듭니다. 다음 가상 기록을 A열과 B열에 넣고 1행을 머리글로 사용하세요. 행 순서는 의도적으로 다르게 구성했습니다.
| Old ID | Old 금액 | New ID | New 금액 |
|---|---|---|---|
| A101 | 120 | A103 | 75 |
| A102 | 80 | A101 | 120 |
| A103 | 75 | A105 | 60 |
| A104 | 50 | A102 | 95 |
앞의 두 열은 Old에, 뒤의 두 열은 New에 넣습니다. 설명용 데이터이며 고객의 실제 결과나 속도 벤치마크가 아닙니다.
먼저 각 시트의 보조 열에서 해당 행의 ID가 같은 원본에 몇 번 나오는지 셉니다.
=COUNTIF($A$2:$A$5,A2)
이 예제에서는 비어 있지 않은 모든 ID가 1을 반환해야 합니다. 조회하기 전에 1보다 큰 결과를 확인하세요. COUNTIF는 대소문자를 구분하지 않으며 조건에 와일드카드를 사용할 수 있습니다. ID의 대소문자에 의미가 있거나 ID에 *, ?, ~가 포함돼 있다면 다른 일치 규칙이 필요합니다. 이 단순한 수식을 그대로 사용하지 마세요.
다음으로 Old!D2에서 새 원본에 일치하는 항목이 몇 개인지 세고 아래로 채웁니다.
=COUNTIF(New!$A$2:$A$5,A2)
0은 ID가 Old에만 있다는 뜻이고, 1은 유일한 후보가 있다는 뜻입니다. 1보다 크면 연결 대상이 모호합니다. Old!E2에서 새 금액을 가져옵니다.
=XLOOKUP(A2,New!$A$2:$A$5,New!$B$2:$B$5,"Not found",0)
D가 1이고 양쪽 금액이 모두 유효한 숫자일 때만 E와 B를 비교하세요. 기록이 없는 경우, 금액이 비어 있는 경우, 실제 값이 0인 경우를 같은 것으로 취급하면 안 됩니다. 계산된 금액이라면 차이를 분류하기 전에 적절한 반올림이나 허용 오차 규칙을 정하세요.
XLOOKUP은 첫 번째 일치 항목을 반환할 뿐, 중복 문제를 해결하지는 않습니다. Excel 2016과 2019에서는 사용할 수도 없습니다. 해당 버전에서는 같은 고유성 검사를 마친 다음 E2에 아래의 정확히 일치하는 대체 수식을 사용할 수 있습니다.
=IFNA(INDEX(New!$B$2:$B$5,MATCH(A2,New!$A$2:$A$5,0)),"Not found")
함수 동작과 호환성은 Microsoft의 XLOOKUP 설명 및 INDEX와 MATCH 가이드를 참고하세요.
마지막으로 New에서 각 ID가 Old에 몇 번 나타나는지 세어 추가된 기록을 찾습니다.
=COUNTIF(Old!$A$2:$A$5,A2)
이 열에서 0만 필터링하면 A105를 찾을 수 있습니다. Old에서 New 방향으로만 확인했다면 놓칠 기록입니다.
각 차이를 설명하는 보고서 만들기
이 예제의 별도 보고서는 다음과 같아야 합니다.
| ID | 이전 금액 | 새 금액 | 상태 |
|---|---|---|---|
| A101 | 120 | 120 | 변경 없음 |
| A102 | 80 | 95 | 변경됨 |
| A103 | 75 | 75 | 변경 없음 |
| A104 | 50 | — | Old에만 있음 |
| A105 | — | 60 | New에만 있음 |
서로 다른 ID는 다섯 개이며, 변경 없음 두 개, 변경 한 개, Old에만 있는 항목 한 개, New에만 있는 항목 한 개입니다. 두 원본의 행 수는 각각 네 개입니다. 같은 행끼리 비교하면 실제와 다른 인상을 줄 수 있습니다.
여러 필드를 비교할 때는 변경마다 필드 이름, 이전 값, 새 값을 기록하세요. ID뿐 아니라 원본 행 참조도 보관합니다. 비어 있거나 중복된 키는 조용히 삭제하지 말고 별도 검토 목록에 넣으세요. 원본을 바꾸기 전에 안전한 중복 제거 방법을 확인해 보세요.
GetSheetAI에 비교 범위 지정하기
GetSheetAI Excel 사이드바에서 단순히 “이 시트들을 비교해 줘”라고 하기보다는 키, 필드, 출력 위치, 규칙을 지정하세요. 아래 요청의 필드 이름을 실제 머리글로 바꾸고, 원본에 금액 열만 있다면 Status는 제외하세요.
Old와 New의 실제 데이터가 있는 범위를 Order ID로 비교해 주세요. 먼저 각 원본의 빈 ID와 중복 ID를 보고해 주세요. ID 연결이 모호하면 중단하고 연결 방법을 질문해 주세요. Amount와 Status만 비교하세요. 기록 누락, 빈 값, 0을 구분하세요. Comparison 시트를 새로 만들고 ID, 변경된 필드, 이전 값, 새 값, 결과 분류, 원본 행 참조를 넣어 주세요. 한쪽에만 있는 기록도 포함하세요. 두 원본 시트는 수정하지 마세요. 각 분류의 개수를 요약하고 미해결 행을 보여 주세요.
변경을 수락하기 전에 제안된 일치 규칙을 검토하세요. AI가 비교 작업을 정리하는 데 도움을 줄 수는 있지만, 서로 다른 식별자가 같은 주문을 뜻하는지는 사용자가 규칙을 알려 주지 않으면 판단할 수 없습니다.
보고서를 사용하기 전에 확인하기
- 변경 없는 기록 한 개, 변경된 기록 한 개, 각 원본에만 있는 기록을 하나씩 확인합니다.
- 전체 결과 분류의 고유 ID 개수를 대조하고, 미해결 ID는 별도로 셉니다.
- 어느 원본을 정렬해도 분류가 바뀌지 않는지 확인합니다.
- 보이는 행이나 필터링된 행만이 아니라 데이터가 있는 전체 범위가 포함됐는지 확인합니다.
- 다른 사람이 비교 과정을 재현할 수 있도록 원본 내보내기 파일을 보관합니다.
다음 작업이 버전 비교가 아니라 청구서와 입금 대조라면 청구서 대사 절차를 사용하세요. 청구서 하나에 여러 입금이 있는 경우에는 이 예제의 ID당 한 기록과 다른 규칙이 필요합니다.
