엑셀 두 목록 비교: 완료 목록에 없는 행만 파워 쿼리로 뽑기

엑셀 두 목록 비교에서 완료 이력에 없는 새 행만 뽑으려면 파워 쿼리의 왼쪽 안티 조인을 사용하면 돼요. 새 목록을 첫 번째 표, 완료된 기록만 남긴 이력을 두 번째 표로 놓고 같은 식별자를 비교해요. 결과는 원본을 지우지 않은 별도 시트에 만들고, 이력이 바뀔 때 새로 고쳐요.

중요한 점은 결과를 곧바로 ‘다시 처리해도 되는 목록’으로 읽지 않는 거예요. 오른쪽 완료 목록에서 찾지 못했다는 뜻일 뿐, 처리 결과가 아직 확인되지 않았거나 다른 파일에 이력이 있을 수도 있어요. 아래 예제에서도 결과 미확인 기록은 다시 발송하지 않고 따로 확인해요.

설명은 Windows 데스크톱의 Microsoft 365용 Excel, Excel 2024·2021 기준이에요. Mac용 Microsoft 365에도 파워 쿼리 편집기가 있지만 이 글의 Windows 경로를 그대로 적용하지는 않아요. 웹이나 모바일에서 같은 메뉴가 보인다고 전제하지 않고, 편집 가능한 통합 문서 사본에서 시작해요.

엑셀 두 목록 비교에 사용할 새 목록과 완료 이력 개념도

1. 여섯 행으로 정답부터 정해요

실제 고객이나 주문 자료 대신 아래처럼 가상 목록을 만들어 연습해요. 신규 목록에는 기록번호와 식별자 두 열을 두고, 같은 식별자가 두 번 들어 있는 행도 그대로 남겨요. 기록번호는 결과에서 원래 어느 행이었는지 찾기 위한 보조 정보예요.

기록번호신규 식별자
1A-001
2A-002
3A-003
4A-003
5빈칸
6A-004

표에서 빈칸이라고 표시한 식별자 셀에는 실제로 아무것도 입력하지 않아요. 이력 표에는 A001과 A004를 ‘완료’, A002를 ‘결과 미확인’으로 입력해요. 이 예제에서는 하이픈 유무가 같은 ID를 뜻한다는 규칙을 미리 정해요. 따라서 A-001은 A001과 같은 대상으로 비교하지만, 모든 업무 코드에서 하이픈을 없애도 된다는 뜻은 아니에요.

빈 식별자 한 행을 별도 검토로 빼고 완료 이력만 비교하면 남아야 하는 값은 A-002, A-003, A-003이에요. 결과는 세 행이고 식별자의 종류는 두 개예요. 같은 코드 두 행이 남는 것은 왼쪽 안티 조인이 중복 제거 기능이 아니기 때문이에요.

2. 원본과 비교용 열을 나누어요

두 목록을 각각 머리글이 있는 Excel 표로 만들어요. 목록 안의 셀을 선택하고 Ctrl+T를 눌러 범위를 확인한 뒤 머리글 포함 여부를 맞춰요. 표 이름은 신규목록과 처리이력처럼 구분하고, 마지막 기록까지 표에 들어갔는지 먼저 확인해요.

무엇을 같은 기록으로 볼지도 정해야 해요. 고객번호만 비교하면 같은 고객의 서로 다른 주문까지 완료로 빠질 수 있어요. 주문별 처리가 목적이라면 주문번호를 쓰고, 주문번호와 상품코드를 함께 알아야 한다면 두 열을 한 쌍으로 비교하는 것이 맞아요.

원래 식별자는 증빙으로 남겨요. 편집기에서 식별자 열을 복제해 비교키라는 이름을 붙이고, 필요한 정리는 이 열에만 적용해요. 비교키가 같아진 두 원본이 정말 같은 대상인지 확인할 수 있어야 나중에 잘못 제외된 기록을 추적할 수 있어요.

예를 들어 실제 규칙에서 AB-12와 A-B12가 서로 다른 상품이라면 하이픈 제거로 둘 다 AB12가 되는 것은 오류예요. 공백이나 구분기호가 불편해 보여도 의미를 확인하기 전에는 일괄 삭제하지 않아요. 표준화는 비교를 쉽게 하는 수단이지 코드의 뜻을 새로 정하는 기능이 아니에요.

두 목록에서 같은 코드끼리 대응시키는 개념도

3. 두 쿼리의 키를 먼저 텍스트로 맞춰요

신규 표 안의 셀을 선택하고 데이터 → 테이블/범위에서를 열어요. 파워 쿼리 편집기가 나타나면 쿼리 이름을 신규_비교로 정해요. 처리이력 표도 같은 방식으로 가져와 이력_비교라는 다른 쿼리를 만들어요.

양쪽 비교키의 데이터 형식은 텍스트로 맞춰요. 열 이름이 달라도 비교할 수 있지만, 비교하는 값의 형식이 서로 다르면 제대로 일치하지 않을 수 있어요. 숫자 123과 텍스트 123을 화면 모양만 보고 같은 준비 상태라고 판단하지 않아요. Microsoft의 병합 열 선택 안내에서도 같은 데이터 형식을 확인하도록 설명해요.

적용된 단계에 자동으로 만들어진 ‘변경된 유형’이 있으면 그 단계에서 식별자가 이미 숫자로 바뀌었는지 확인해요. 앞자리 0이나 긴 번호의 뒷자리가 사라진 뒤 텍스트로 바꾸어도 원래 번호는 돌아오지 않아요. 원본부터 손상됐다면 원래 파일을 다시 가져오는 쪽으로 고쳐요.

CSV를 이용한다면 파일을 더블클릭해 열고 덮어쓰기보다 CSV 가져오기에서 인코딩과 코드 형식 확인하기 순서를 먼저 적용해요. 비교 결과가 틀렸을 때 조인 종류만 바꿔서는 가져오기 과정에서 손실된 값을 복구할 수 없어요.

이번 예제의 비교키에서는 값 바꾸기로 하이픈을 빈 문자열로 바꿔요. 양쪽 모두 같은 규칙을 적용한 뒤 A-001의 비교키가 A001인지 확인해요. 신규_비교의 비교키 필터에서 null과 빈 값을 제외해요. 이 예제의 기록번호 5는 원본에 남겨 별도 검토로 표시해요. 빈칸과 오류가 있는 키는 작업 대상으로 섞지 말고 별도 검토 쿼리나 검토 목록에 남겨요. 이유를 모르는 오류를 지워서 건수만 맞추지 않아요.

4. 오른쪽 표에는 완료한 기록만 남겨요

이력_비교의 상태 열을 필터링해 ‘완료’만 남겨요. 예제에서는 A001과 A004 두 행이 남고 A002는 빠져야 해요. 쿼리 이름도 이력_완료로 바꾸면 병합 화면에서 잘못된 이력을 고르는 일을 줄일 수 있어요.

이 단계를 생략하고 전체 처리이력을 오른쪽에 놓으면 A002도 ‘이미 존재하는 값’이어서 결과에서 빠져요. 그 경우 얻는 것은 완료 이력에 없는 목록이 아니라 어떤 상태로든 이력에 나타나지 않은 목록이에요. 원하는 질문이 달라지므로 상태 필터를 조인 전에 확인해야 해요.

완료라고 적는 기준도 함께 보아요. 접수 완료와 실제 발송 완료가 같은 뜻이 아니라면 하나의 상태로 섞으면 안 돼요. 파일이 여러 개라면 어느 기간과 어느 담당자의 완료 이력을 비교하는지도 적어 두어요. 쿼리는 오른쪽에 넣지 않은 이력의 존재까지 알아내지 못해요.

다른 목록에 없는 항목만 따로 모은 개념도

5. 새 쿼리로 병합하고 왼쪽 안티를 골라요

파워 쿼리 편집기에서 홈의 쿼리 병합 메뉴를 열어 새 쿼리로 병합하는 항목인 Merge Queries as New를 선택해요. 한국어 표시가 ‘쿼리를 새 항목으로 병합’ 또는 새로 만들기 표현으로 보이면 영문 명칭과 새 쿼리 생성 여부를 함께 대조해요. 기존 신규 쿼리에 결과 단계를 덧붙이는 작업과 구분해요.

  1. 병합 대화 상자의 위쪽 첫 번째 표에 신규_비교를 놓아요.
  2. 아래쪽 두 번째 표에는 이력_완료를 놓아요.
  3. 위아래 표에서 비교키 열 머리글을 각각 선택해요.
  4. 조인 종류에서 왼쪽 안티 조인을 선택해요.
  5. 유사 항목 일치는 사용하지 않고 확인을 눌러요.

여기서 왼쪽은 화면의 왼편을 뜻하는 것이 아니라 병합의 첫 번째 표를 뜻해요. 두 표를 반대로 놓으면 완료 이력에만 있는 값을 찾는 다른 질문이 돼요. 쿼리 병합 공식 도움말에서 조인 종류별로 남는 행의 차이를 확인할 수 있어요.

여러 열을 키로 쓸 때는 Ctrl을 누르고 선택하되 양쪽 선택 순서까지 같게 맞춰요. 첫 번째 열은 첫 번째 열과, 두 번째는 두 번째와 비교돼요. ID의 정확한 일치가 필요한 업무에서는 철자가 비슷한 다른 코드를 같은 것으로 취급하는 유사 일치를 켜지 않아요.

6. 세 행을 검수하고 별도 시트에 로드해요

결과 쿼리 이름은 완료이력_미일치로 정해요. 오른쪽 표를 가리키는 중첩 열이 생겨도 완료 기록의 세부 내용을 가져올 필요는 없어요. 원래 기록번호, 원본 식별자, 비교키를 확인한 후 불필요한 중첩 열을 제거하면 돼요. 왼쪽 안티 결과에 완료 대상이 없어야 한다는 기준은 Microsoft의 왼쪽 안티 조인 예제와 같아요.

기록번호 2·3·4의 세 행이 있는지 확인해요. 1번과 6번은 완료 이력에 있으므로 없어야 하고, 빈 식별자인 5번은 별도 검토 목록에 있어야 해요. 여섯 원본을 ‘완료 일치 두 행, 미일치 세 행, 키 검토 한 행’으로 설명할 수 있으면 예제의 분류가 맞아요.

순서가 중요하면 병합 전에 신규 쿼리에 인덱스 열을 추가하고, 결과를 그 열로 정렬해요. 조인을 했으니 원래 순서가 항상 유지된다고 기대하지 않아요. 같은 식별자 두 행을 한 행으로 줄일지는 이 비교와 별도의 업무 결정이에요.

홈의 닫기 및 로드 메뉴에서 로드 대상을 열고 표와 새 워크시트를 선택해요. 신규 원본 위에 결과를 덮지 않아요. 이후 수정은 원본 표와 이력 표에서 하고, 결과 시트에 직접 적어 넣은 설명을 영구 기록으로 믿지 않아요. 새로 고침 결과와 충돌할 수 있기 때문이에요.

남은 행의 코드를 하나씩 검토하는 개념도

7. 완료 이력 한 건을 추가해 갱신을 시험해요

연습용 이력 원본 표에 A003·완료를 한 번 추가해요. 데이터에서 모두 새로 고침을 실행하고 완료될 때까지 기다려요. 결과에 A-002 한 행만 남으면 두 개 있던 A-003 행이 같은 완료키에 의해 모두 제외된 거예요.

완료 이력에 A003을 두 번 적어야 왼쪽 두 행이 빠지는 것은 아니에요. 이 비교는 오른쪽에서 일치하는 값의 존재를 검사하기 때문이에요. 다만 완료 이력 자체의 중복이 실제로 다른 처리 건을 뜻한다면 처음 정한 식별자가 업무를 충분히 구분하는지 다시 살펴봐요.

남은 A-002는 결과 미확인 상태예요. 이 행이 보인다는 이유로 같은 작업을 다시 보내지 않아요. 기존 접수번호나 발송 내역에서 실제 완료 여부를 먼저 확인하고, 확인한 사실을 이력에 반영한 다음 다시 갱신해요. 비교 목록과 실행 승인은 다른 단계예요.

결과가 이상하면 표·키·이력을 따로 확인해요

거의 모든 신규 행이 남는다면 두 키의 형식, 불필요한 공백, 하이픈 규칙, 다른 열 선택을 확인해요. 반대로 결과가 전혀 없으면 정말 모두 완료됐는지와 함께 전체 이력을 완료 이력으로 잘못 썼는지 보아요. 오류가 난 행을 무조건 제외하면 미완료 대상을 조용히 잃을 수 있어요.

새 행이 반영되지 않으면 셀이 원본 Excel 표의 범위 안에 들어갔는지 확인해요. 다른 파일을 원본으로 쓴다면 저장 여부와 연결 경로도 확인해요. 새로 고침 버튼을 눌렀다는 사실보다 갱신 완료와 추가한 가상 ID의 실제 결과를 기준으로 판단해요.

제출용 파일이 필요하면 검수가 끝난 결과를 별도 통합 문서에 값으로 옮기고 기준 날짜와 이력 범위를 남겨요. 결과 시트만 보이게 숨긴 통합 문서에는 원본 개인정보가 다른 시트나 연결에 남아 있을 수 있어요. 전달할 자료 범위도 비교 정확성과 별도로 확인해요.

마지막에는 ‘세 행이 나왔다’보다 어떤 원본 행이 왜 남았는지를 설명할 수 있는지 보아요. 신규 목록, 완료 이력의 범위, 비교키 규칙, 예외 검토가 맞아야 재사용할 수 있어요. 같은 절차를 반복할 때도 원본을 고치고 새로 고친 뒤 결과를 확인하는 순서를 유지해요.

답글 남기기