- 공유 링크 만들기
- X
- 이메일
- 기타 앱
이 글의 목적은 엑셀에서 2개 워크시트를 정밀하게 비교하여 차이를 신속하게 식별하고, 일치 검증과 변경 이력을 자동화하며, 실무 보고서로 바로 전환할 수 있는 방법을 종합적으로 제시하는 것이다.
1. 비교 전략 설계: 입력·레버·출력 정의
엑셀에서 두 시트를 비교할 때는 먼저 데이터 구조와 식별키를 명확히 정의해야 한다. 비교의 입력은 시트A와 시트B이며, 레버는 키 설계·정렬·함수·조건부 서식·파워쿼리·VBA 등이다. 출력은 차이표·대응관계 매핑·변경요약 보고서이다. 실무에서는 행 단위 매칭 실패를 방지하기 위해 고유키를 먼저 만든 다음 함수로 비교하는 순서가 가장 안정적이다.
주의 : 고유키가 없는 상태에서 단순 셀 위치 비교를 수행하면 행 추가·삭제 시 대량의 오탐지가 발생한다.
2. 고유키 설계: 복합키와 해시키
고유키는 한 행을 유일하게 식별하는 열 조합으로 만든다. 예시는 <주문번호+품목코드+일자>와 같은 복합키이다. 복합키 문자열이 길면 해시 형태의 키로 압축하여 성능을 높일 수 있다.
-- A시트 =TEXT(A2,"000000") & "|" & B2 & "|" & TEXT(C2,"yyyymmdd") ' 복합키
-- 해시 느낌의 간단 키(충돌 가능성은 낮으나 0이 아님)
=DEC2HEX(ABS(CRC32(TEXTJOIN("|",,A2:C2)))) ' CRC32 UDF가 없으면 생략복합키 열을 시트A와 시트B 모두에 생성하여 이후 모든 비교의 기준으로 사용한다.
3. 정렬·정규화 전처리
- 공백·대소문자·형식 정리: TRIM, CLEAN, PROPER, VALUE로 정규화한다.
- 숫자·날짜 형식 일치: 텍스트 숫자를 값으로 변환하고, 날짜는 일관 포맷으로 맞춘다.
- 정렬 기준: 키 기준 오름차순 정렬 후 비교하면 위치 기반 비교도 가능하다.
=TRIM(SUBSTITUTE(A2,CHAR(160)," ")) ' 고정폭 공백 제거 =IFERROR(VALUE(D2),D2) ' 텍스트 숫자 → 숫자 =DATEVALUE(TEXT(E2,"yyyy-mm-dd")) ' 날짜 일치화4. 핵심 함수 비교법: XLOOKUP·MATCH·COUNTIF
4.1 존재 여부 비교
시트A의 키가 시트B에도 존재하는지 점검한다.
=IF( ISNUMBER( XMATCH(A!Key2, B!Key2) ), "일치", "B에 없음" ) ' 365 =IF( ISNUMBER( MATCH(A!Key2, B!Key2, 0) ), "일치", "B에 없음" ) ' 호환4.2 값 비교: 열별 차이 표시
키로 행을 정렬하지 않아도 XLOOKUP으로 대응 행을 찾아 직접 비교한다.
-- A의 단가와 B의 단가 비교 =LET( k, A!Key2, vA, A!UnitPrice, vB, XLOOKUP(k, B!Key2, B!UnitPrice, NA()), IF( ISNA(vB), "B에 없음", IF( vA=vB, "동일", "불일치: A=" & vA & ", B=" & vB ) ) )4.3 다열 동시 비교: 차이 요약 문자열
=LET( k, A!Key2, a1, A!Qty, b1, XLOOKUP(k,B!Key2,B!Qty,NA()), a2, A!Price, b2, XLOOKUP(k,B!Key2,B!Price,NA()), a3, A!Status, b3, XLOOKUP(k,B!Key2,B!Status,NA()), diff, TEXTJOIN(", ", TRUE, IF(a1=b1,"","Qty:" & a1 & "→" & b1), IF(a2=b2,"","Price:" & a2 & "→" & b2), IF(a3=b3,"","Status:" & a3 & "→" & b3) ), IF( ISNA(b1), "B에 없음", IF(diff="", "동일", diff)) )4.4 중복·누락 집계
=COUNTIF(B!Key2, A!Key2) ' 0이면 B에 누락, 2 이상이면 B 중복 =COUNTIF(A!Key2, B!Key2) ' A 기준 역방향 점검주의 : 부동소수점 비교는 반올림 기준을 명시하여 오탐을 줄여야 한다.
=IF(ROUND(A!Amount,2)=ROUND(B!Amount,2),"동일","불일치")5. 조건부 서식으로 시각적 차이 강조
5.1 키 존재 여부 강조
시트A의 키 범위에 조건부 서식 수식을 적용한다.
=COUNTIF(B!$F:$F, $F2)=0 ' F열이 Key2라고 가정, TRUE면 빨간 채움5.2 동일 위치 셀 값 비교
두 시트가 완전히 같은 레이아웃이면 위치 비교가 빠르다.
=A!$G2<>B!$G2 ' TRUE면 강조5.3 임계값 기반 수치 편차 강조
=ABS(A!$H2 - B!$H2) >= 0.016. 파워쿼리 병합으로 구조적 비교
파워쿼리는 대용량 데이터 비교에 안정적이다. 단계는 다음과 같다.
- 각 시트를 데이터 범위로 로드하여 쿼리 두 개를 만든다.
- 병합을 선택하고 키 열을 지정한 뒤 병합 유형을 선택한다.
- Left Anti로 A에는 있고 B에는 없는 행을, Right Anti로 반대 방향 누락을 확인한다.
- Inner Join으로 공통 행을 결합한 후 확장하여 개별 열 차이를 새 계산열로 만든다.
| 병합 유형 | 의미 | 용도 |
|---|---|---|
| Inner | 양쪽 모두 존재 | 일치검증 대상 집합 확보 |
| Left Anti | A만 존재 | B 누락 검출 |
| Right Anti | B만 존재 | A 누락 검출 |
| Full Outer | 전체 합집합 | 포괄 비교 보고서 |
7. 동적배열로 차이목록 자동 생성
-- B 누락 키 목록 =FILTER(A!Key2, COUNTIF(B!Key2, A!Key2)=0, "모두 일치")
-- A와 B 단가 불일치 목록
=LET(
k, A!Key2,
a, A!UnitPrice,
b, XLOOKUP(k, B!Key2, B!UnitPrice, NA()),
FILTER( HSTACK(k,a,b), (ISNUMBER(b))*(a<>b), "모두 동일")
)8. 위치기반 비교 보고서: 셀 단위 스냅샷
레이아웃이 동일한 두 표라면 셀별 비교표를 별도 시트에 생성한다.
-- 차이 여부 매트릭스 =IF(A!B2=B!B2,"","Δ")
-- 요약 지표
=LET(rng, C2:N1000, SUM(--(A!rng<>B!rng))) ' 불일치 셀 개수9. 정확비교 함수: EXACT와 대소문자 민감 비교
문자 데이터가 대소문자 구분을 요구하면 EXACT를 사용한다.
=IF( EXACT( A!Name2, XLOOKUP(A!Key2,B!Key2,B!Name2,"") ), "동일", "대소문자 불일치")10. 유사일치와 와일드카드 비교
제품명이 약간 다른 경우 와일드카드 또는 근사 매칭을 활용한다. 와일드카드는 패턴 일치에 적합하다.
=XLOOKUP( A!Prod & "*", B!Prod, B!Key2, "없음", 2 ) ' 와일드카드 =IFERROR( XLOOKUP( A!Prod, B!Prod, B!Key2, , 1 ), "근사매칭 실패") ' 정렬 필요주의 : 근사매칭은 정렬된 기준열에서만 의미가 있으며, 숫자형에 사용하는 것이 안전하다.
11. 차이 요약 대시보드 만들기
- 카드형 지표: 총 행수, 신규추가, 삭제, 변경, 일치율을 요약한다.
- 피벗: 변경열 기준으로 변경유형별 건수를 집계한다.
- 슬라이서: 상태, 담당자, 거래처 등 조건별 필터를 제공한다.
-- 일치율 =LET( k, A!Key2, cnt, ROWS(k), chg, ROWS( FILTER(k, A!Amount<>XLOOKUP(k,B!Key2,B!Amount,NA()), "") ), TEXT( (cnt-chg)/cnt, "0.0%" ) )12. 대용량 데이터 성능 최적화
- 범위는 표 개체로 전환하여 구조적 참조를 사용한다.
- 불필요한 휘발성 함수(INDIRECT, OFFSET)는 피한다.
- XLOOKUP 반환 범위를 필요한 열로만 제한한다.
- 계산 옵션을 수동으로 두고 F9로 실행한다.
- 필요 시 보조열을 만들어 계산을 분할한다.
13. 실무 포맷: 변경이력 표준 레이아웃
| Key | 구분 | 필드 | A값 | B값 |
|---|---|---|---|---|
| K0001 | 변경 | UnitPrice | 1,200 | 1,250 |
| K0002 | 삭제 | 행 전체 | 존재 | 미존재 |
| K0003 | 추가 | 행 전체 | 미존재 | 존재 |
14. 보고서 자동화: LET·LAMBDA로 재사용 함수 만들기
-- 두 시트의 값 차이를 반환하는 사용자 함수 =LAMBDA(kRange, aRange, bKey, bRange, LET( k, kRange, vA, aRange, vB, XLOOKUP(k, bKey, bRange, NA()), IF( ISNA(vB), "B에 없음", IF(vA=vB, "동일", vA & "→" & vB)) ) )
-- 이름 관리자에서 함수명을 DiffText로 등록하여 사용
=DiffText(A!Key2, A!UnitPrice, B!Key2, B!UnitPrice)15. VBA로 셀 대 셀 비교 및 결과 하이라이트
Option Explicit Sub CompareSheetsCellByCell() Dim wsA As Worksheet, wsB As Worksheet, wsO As Worksheet Dim r As Long, c As Long, maxR As Long, maxC As Long Set wsA = Worksheets("A") ' 원본 Set wsB = Worksheets("B") ' 비교 Set wsO = Worksheets.Add ' 결과 시트 wsO.Name = "Diff" maxR = Application.Max(wsA.UsedRange.Rows.Count, wsB.UsedRange.Rows.Count) maxC = Application.Max(wsA.UsedRange.Columns.Count, wsB.UsedRange.Columns.Count) For r = 1 To maxR For c = 1 To maxC Dim vA, vB vA = wsA.Cells(r, c).Value vB = wsB.Cells(r, c).Value If vA <> vB Then With wsO.Cells(r, c) .Value = "Δ" .Interior.Color = RGB(255, 199, 206) .AddComment "A: " & vA & vbCrLf & "B: " & vB End With End If Next c Next r End Sub주의 : 위치 기반 비교 매크로는 행 삽입에 취약하므로 키 병합 기반 보고서와 함께 사용해야 한다.
16. 검사 시나리오별 최적 방법 선택표
| 상황 | 권장 방법 | 장점 | 주의점 |
|---|---|---|---|
| 고정 레이아웃 두 표의 즉시 비교 | 위치 기반 조건부 서식, VBA 셀 매핑 | 빠른 시각화 | 행 이동에 취약 |
| 키 중심의 데이터 일치 검증 | XLOOKUP/MATCH, COUNTIF | 구조적 비교 | 키 정합 필수 |
| 대용량·중복·누락 탐지 | 파워쿼리 병합 | 안정성과 성능 | 쿼리 갱신 절차 필요 |
| 변경 요약 보고서 자동화 | 동적배열 + LAMBDA | 재사용 용이 | 버전 호환 고려 |
| 텍스트 민감 비교 | EXACT 대소문자 비교 | 정밀성 | 공백 정규화 선행 |
17. 실무 예제: 키 병합 기반 차이표 만들기
- A, B 양쪽에 Key2를 생성한다.
- 결과 시트에 키 전체 목록을 만들고 UNIQUE로 중복을 제거한다.
- 각 필드에 대해 XLOOKUP으로 A값과 B값을 가져온다.
- IF로 동일·변경·추가·삭제를 분류한다.
-- 키 목록 =UNIQUE(VSTACK(A!Key2, B!Key2))
-- 값 가져오기
=HSTACK(
[키],
XLOOKUP([키],A!Key2,A!Qty,""),
XLOOKUP([키],B!Key2,B!Qty,""),
XLOOKUP([키],A!Key2,A!Price,""),
XLOOKUP([키],B!Key2,B!Price,"")
)
-- 상태 분류
=LET(
qA, XLOOKUP([키],A!Key2,A!Qty,""),
qB, XLOOKUP([키],B!Key2,B!Qty,""),
IF( (qA="")(qB<>""), "추가",
IF( (qA<>"")(qB=""), "삭제",
IF( qA=qB, "동일", "변경")
)
)
)18. 숫자 비교의 회계적 일치 기준
세금·단가 등 소수 처리 기준을 미리 규정해야 혼선을 줄일 수 있다.
- 금액은 소수 둘째자리 반올림 비교를 표준으로 한다.
- 부가세 포함·제외 여부를 동일하게 맞춘다.
- 통화 환산 기준일과 환율 소수 자릿수를 고정한다.
19. 에러 처리와 로깅
=IFERROR( XLOOKUP(k,B!Key2,B!Amount), "B에 없음" ) =IF( ISNUMBER(SEARCH("Δ",diffCell)), "불일치", "동일")오류는 별도 로그 시트에 기록하여 재현성을 확보한다.
20. 베스트 프랙티스 체크리스트
- 비교 전에 키 정합을 100% 보장한다.
- 숫자·날짜 형식을 통일한다.
- 조건부 서식 규칙에 명료한 기준을 부여한다.
- 보고서에는 추가·삭제·변경·동일 네 범주를 모두 표시한다.
- 대용량은 파워쿼리로 병합하여 관리한다.
- 반복 비교는 LAMBDA로 캡슐화한다.
FAQ
행 추가로 인해 전체가 불일치로 보일 때 해결 방법은 무엇인가?
키 기반 비교로 전환하여 위치 영향에서 자유로운 구조적 비교를 수행해야 한다. 키를 생성하고 XLOOKUP 또는 파워쿼리 병합으로 매칭한 뒤 값만 비교하면 된다.
정렬 없이도 정확 비교가 가능한가?
가능하다. 키를 기준으로 XLOOKUP으로 대응 값을 찾기 때문에 원본 순서에 의존하지 않는다.
대소문자를 구분해야 하는 품목코드는 어떻게 비교하나?
EXACT 함수를 사용하여 대소문자 민감 비교를 수행한다. 비교 전 TRIM으로 공백을 정리한다.
근사 매칭이 필요한 모델명이 많다. 안전한가?
와일드카드는 패턴 일치에는 유용하나 오탐이 발생할 수 있다. 가능하면 참조 표준코드를 도입하고 근사 매칭은 보조 검증 용도로 제한한다.
보고서를 한 번에 만들 수 있는 기본 골격은 무엇인가?
UNIQUE로 키 합집합을 만들고, 각 필드를 XLOOKUP으로 끌어와 IF로 상태를 분류하는 구조가 표준이다. 동적배열을 사용하면 자동 확장된다.