스프레드시트 두 명단 비교하기, 빠진 항목과 새로 추가된 행 찾는 방법
엑셀·스프레드시트에서 이전 명단과 새 명단을 비교할 때 식별 키를 고르고 COUNTIF로 누락·추가 항목을 찾은 뒤 중복과 형식 차이를 검증하는 실무 방법입니다.
지난주 명단과 이번 주 명단을 비교해야 할 때 행 번호를 한 줄씩 대조하면 금방 틀어집니다. 가나다순이나 등록 순서가 달라지기 때문입니다. 이 작업의 기준은 같은 위치에 있는 행이 아니라 같은 항목을 식별하는 값입니다. 주문번호·직원 ID·접수번호처럼 변하지 않는 기준을 정하면 목록이 섞여 있어도 빠진 항목과 새 항목을 분리할 수 있습니다.
아래 방법은 Excel에서 두 열의 목록에 특정 키가 존재하는지를 비교하는 예시입니다. Google Sheets에서도 같은 함수 구조를 사용할 수 있지만, 지역별 수식 구분자나 가져온 데이터의 형식에 따라 입력 모습이 다를 수 있습니다.
행 수가 같아도 같은 명단은 아닐 수 있다
가령 이전 명단에 주문 A101, A102, A103이 있고 새 명단에 A101, A103, A104가 있다고 해보겠습니다. 두 명단 모두 3행이지만 A102는 사라졌고 A104는 추가됐습니다. 행 수만 세거나 총합만 맞추면 이런 변화가 보이지 않습니다.
또한 같은 사람의 이름이 두 번 나타나는 것은 서로 다른 거래 두 건일 수 있습니다. 이름만으로 비교하면 한 행이 다른 행을 잘못 대체할 수 있습니다. 사번, 거래번호, 신청 ID 등 업무상 유일한 값을 식별할 수 있는 열이 있는지 확인해야 합니다.
그런 값이 없다면 날짜와 부서, 품목과 주문번호 등 여러 열의 조합으로 하나의 키를 만들어야 할 수 있습니다. 다만 중복이 있는 자료에서는 같은 조합이 실제로 동일한 업무 건을 뜻하는지 검증 없이 합치면 안 됩니다. 식별 기준을 고르는 상세 판단은 중복 레코드를 구분할 키 선택에서 이어서 다룹니다.
이전 목록과 새 목록은 원본을 보존한 채 나란히 둔다
새 작업 시트를 만들고 A열에 이전 목록의 키, D열에 새 목록의 키를 복사해 둡니다. 헤더는 1행에 두고, 실제 데이터는 2행부터 시작하는 예시로 설명하겠습니다.
| A열: 이전 ID | D열: 새 ID | 해석 |
|---|---|---|
| A101 | A101 | 양쪽에 존재 |
| A102 | A103 | A102는 새 목록에 없음 |
| A103 | A104 | A104는 새로 등장 |
표의 같은 행끼리 비교하려는 뜻이 아닙니다. A열의 각 값이 D열 어디엔가 있는지, 반대로 D열의 각 값이 A열 어디엔가 있는지를 확인합니다. 원본을 정렬하거나 삭제하지 않고 복사본에서 비교해야 뒤늦게 키 오류를 발견해도 되돌릴 수 있습니다.
COUNTIF로 이전 명단에서 사라진 항목 찾기
이전 명단 A2의 ID가 새 명단 D2:D100에 몇 번 있는지 구하는 식은 다음과 같습니다.
=COUNTIF($D$2:$D$100,A2)
결과가 0이면 새 목록의 지정한 범위에는 그 ID가 없습니다. 결과가 1 이상이면 적어도 한 번 나타났다는 뜻입니다. 결과를 읽기 쉽게 하려면 B2에 아래처럼 입력하고 아래로 채웁니다.
=IF(A2="","확인 필요",IF(COUNTIF($D$2:$D$100,A2)=0,"새 명단에 없음","존재"))
달러 기호로 참조 범위를 고정해야 수식을 아래로 복사해도 비교 대상 범위가 이동하지 않습니다. 실제 데이터가 100행보다 많다면 마지막 행을 맞춰 바꾸어야 하며, 헤더·합계 행이 비교 범위에 섞이지 않게 합니다. Microsoft의 COUNTIF 사용 안내는 특정 값이 범위에 나타나는 횟수를 세는 동작을 설명합니다.
이 수식은 ‘이전에 있었는데 새 명단에서는 빠진 항목’을 찾습니다. 새로 생긴 항목은 방향을 거꾸로 비교해야 합니다.
새로 추가된 항목은 검색 방향을 뒤집는다
새 명단의 D2를 이전 명단 A2:A100과 비교하면 됩니다. E2에 다음 수식을 넣고 아래로 채웁니다.
=IF(D2="","확인 필요",IF(COUNTIF($A$2:$A$100,D2)=0,"새로 추가","기존에도 존재"))
이렇게 하면 B열은 삭제·누락 후보, E열은 추가 후보가 됩니다. 필터로 ‘새 명단에 없음’과 ‘새로 추가’를 각각 추리면 변경 건을 확인할 수 있습니다.
단, 이 결과는 반드시 삭제 또는 신규 등록을 의미하지 않습니다. 키 입력 오류, 데이터 추출 조건 차이, 계정·부서 이동, 조회 대상 기간 변경 때문에 한쪽 목록에서 보이지 않는 것일 수도 있습니다. 실제 업무 처리 전에는 원본 시스템의 조회 기간과 조건을 대조해야 합니다.
분명히 같은 항목인데 ‘없음’이 나오면
실무에서 자주 발생하는 문제는 보이는 값은 비슷하지만 저장된 값이 다른 경우입니다.
- 00127과 127: 앞자리 0을 보존해야 하는 식별번호를 숫자로 바꾸면서 값이 달라짐
- A102와 뒤에 공백이 붙은 A102: 가져오기 과정에서 문자 혼입
- 서울-01과 서울01: 구분 기호 입력 규칙이 다름
- 서로 다른 원본의 고객코드: 코드 이름만 같고 발급 체계는 다름
일부 불필요한 공백은 별도 보조 열에서 정리할 수 있지만, 사번이나 주문코드의 하이픈·0을 임의로 지우면 오히려 서로 다른 키가 합쳐질 수 있습니다. 어떤 문자를 같다고 볼지는 업무 규칙으로 정한 뒤 복사본에서 수정합니다.
또한 COUNTIF는 와일드카드 문자인 별표(*)와 물음표(?)를 조건에서 특별하게 처리할 수 있습니다. 코드 값에 이런 문자가 들어간다면 단순 검색 결과를 검증해야 합니다. 영문 대소문자를 구분해 엄격히 비교해야 하는 식별 체계라면 COUNTIF만으로 충분한지도 별도로 검토하세요.
중복이 있는 명단은 ‘존재’와 ‘건수’를 구분한다
COUNTIF가 2를 반환해도 위 분기 수식에서는 ‘존재’로 표시합니다. 하지만 이전 목록에 A101이 두 건이고 새 목록에 한 건만 있다면 항목 자체는 존재하지만 건수는 줄었습니다.
중복이 발생할 수 있는 업무에서는 고유 ID를 더 정확히 정하거나, 키별 이전 건수와 새 건수를 각각 세어 차이를 비교해야 합니다. 예를 들어 한 주문에 여러 품목 행이 있다면 주문번호만으로 ‘누락 행’을 판단할 수 없습니다. 품목번호까지 결합하거나 행 단위를 통일해야 합니다.
이 차이를 무시하면 주문 한 건이 줄어든 것을 놓치거나, 반대로 단순 중복 제거를 업무 데이터 삭제로 오해할 수 있습니다.
비교 결과는 소수의 원본 행으로 검증한다
전체 결과를 보고서나 인수인계 자료로 보내기 전에 ‘삭제 후보’와 ‘추가 후보’에서 몇 건을 추려 원본 파일의 실제 ID, 추출 시각, 필터 조건을 확인하세요. 특히 수식 범위 밖에 있던 행, 앞자리 0이 바뀐 코드, 중복된 키는 오검출의 주요 원인입니다.
최종 결과표에는 어떤 두 파일을 비교했는지와 기준일, 비교 키, 새로 추가된 건수, 이전에는 있었으나 새 목록에는 없는 건수, 확인이 필요한 예외를 남겨 두면 다른 사람이 같은 계산을 다시 설명할 수 있습니다. 단순히 두 명단의 색을 맞추는 작업이 아니라 변경 내역의 근거를 남기는 업무로 마무리하는 것입니다.
스프레드시트스프레드시트 인수인계, 열 정의서와 데이터 기준일을 함께 남기는 방법업무 스프레드시트를 다른 담당자에게 넘길 때 열별 의미·값 형식·출처·기준일·수정 책임자를 정의하고 검증 예시로 인수인계 누락을 막는 실무 가이드입니다.
스프레드시트스프레드시트 빈칸과 0은 다르다: 집계 오류를 막는 결측값 기준업무 스프레드시트에서 미입력·미해당·실제 0을 구분하고 합계·평균·비율이 왜곡되지 않도록 데이터 입력과 집계 규칙을 설계하는 방법입니다.
스프레드시트엑셀에서 CSV 한글이 깨질 때, UTF-8 가져오기와 원본 손상 구분법CSV 파일을 엑셀로 열었는데 한글이 깨지면 파일 인코딩과 엑셀의 읽기 방식을 구분하고, 텍스트/CSV 가져오기에서 문자 집합을 확인해 원본 손상 없이 검증하는 방법입니다.