엑셀로 매출, 재고, 거래처, 상품가격 등을 관리하다 보면 지난달 파일과 이번 달 파일을 비교해야 할 때가 있습니다.
예를 들어 지난주에 작성한 상품 재고표와 이번 주에 수정한 재고표가 있다면 어떤 상품의 수량이 변경되었는지, 새로 추가된 상품은 무엇인지, 삭제된 상품은 없는지 확인해야 합니다.
데이터가 몇십 개라면 직접 확인할 수 있지만 수백 개에서 수천 개로 늘어나면 시간이 오래 걸리고 실수도 발생하기 쉽습니다.
이럴 때 챗GPT와 엑셀 함수를 활용하면 두 파일의 변경된 값을 비교하고 변경 내역을 자동으로 표시할 수 있습니다.
이번 글에서는 실제 상품 재고표를 예제로 사용해 두 엑셀 파일을 비교하는 방법부터 변경된 값 표시, 추가·삭제 항목 확인, 변경 보고서 작성까지 알아보겠습니다.
1. 엑셀 두 파일 비교가 필요한 경우
엑셀 파일 비교는 단순히 숫자가 달라졌는지 확인하는 작업이 아닙니다. 어떤 항목이 변경되었고 얼마나 달라졌는지 파악하는 것이 중요합니다.
| 비교 대상 | 확인할 내용 |
|---|---|
| 상품 재고표 | 재고 수량 증가·감소 |
| 상품 가격표 | 판매가격 변경 |
| 거래처 목록 | 신규 거래처 및 삭제된 거래처 |
| 매출 관리표 | 매출금액 변경 |
| 직원 명단 | 신규 입사자 및 퇴사자 |
| 발주 관리표 | 주문 수량 및 납기 변경 |
특히 매일 수정되는 재고표나 가격표는 변경 사항을 자동으로 확인할 수 있도록 만들어 두면 반복 작업을 줄일 수 있습니다.
2. 비교할 엑셀 파일 두 개 준비하기
먼저 비교할 파일을 준비합니다.
이번 실습에서는 다음과 같이 파일 이름을 정하겠습니다.
기존 파일 : 재고관리_기존.xlsx
수정 파일 : 재고관리_수정.xlsx
기존 파일의 데이터는 다음과 같습니다.
| 상품코드 | 상품명 | 재고수량 |
|---|---|---|
| A001 | 무선마우스 | 50 |
| A002 | 키보드 | 30 |
| A003 | USB허브 | 40 |
| A004 | 모니터 | 20 |
| A005 | 노트북거치대 | 25 |
수정 파일에는 다음과 같은 데이터가 들어 있습니다.
| 상품코드 | 상품명 | 재고수량 |
|---|---|---|
| A001 | 무선마우스 | 45 |
| A002 | 키보드 | 30 |
| A003 | USB허브 | 55 |
| A005 | 노트북거치대 | 25 |
| A006 | 웹캠 | 15 |
두 파일을 비교하면 다음과 같은 차이가 있습니다.
A001 상품은 재고가 50개에서 45개로 감소했습니다.
A003 상품은 재고가 40개에서 55개로 증가했습니다.
A004 상품은 수정 파일에서 사라졌습니다.
A006 상품은 새로 추가되었습니다.
이러한 변경 사항을 엑셀에서 자동으로 찾아보겠습니다.
3. 챗GPT에 엑셀 파일 비교 요청하기
챗GPT를 이용하면 복잡한 비교 작업을 쉽게 진행할 수 있습니다.
먼저 챗GPT 대화창에서 파일 첨부 기능을 이용해 기존 파일과 수정 파일을 업로드합니다.
그다음 아래와 같이 요청합니다.
챗GPT 입력 예시
“첨부한 두 엑셀 파일을 상품코드 기준으로 비교해 줘. 기존 파일과 수정 파일의 재고수량이 달라진 상품을 찾아주고, 신규 추가 상품과 삭제된 상품도 구분해 줘. 변경 전 수량, 변경 후 수량, 증감 수량, 변경 상태를 표로 정리하고 결과를 새로운 엑셀 파일로 만들어 줘.”
이렇게 요청하면 챗GPT가 파일 내용을 읽을 수 있는 환경에서 두 파일을 분석하여 변경 내역을 정리할 수 있습니다.
중요한 점은 상품이 나열된 순서가 아니라 상품코드를 기준으로 비교하도록 요청하는 것입니다.
상품 순서가 달라져도 같은 상품코드를 찾아 비교할 수 있기 때문입니다.
단, 상품코드는 중복되지 않아야 하며 두 파일에서 동일한 형식으로 관리하는 것이 좋습니다.
4. 엑셀 XLOOKUP 함수로 변경된 값 비교하기
챗GPT에 파일을 업로드하지 않고 엑셀 자체에서 비교하는 방법도 있습니다.
이번에는 기존 파일과 수정 파일의 데이터를 하나의 통합문서로 가져와 비교하겠습니다.
먼저 새 엑셀 통합문서를 만들고 두 개의 시트를 준비합니다.
첫 번째 시트 이름 : 기존
두 번째 시트 이름 : 수정
각 시트의 A열에는 상품코드, B열에는 상품명, C열에는 재고수량을 입력합니다.
수정 시트에서 D1 셀에 ‘기존 재고’, E1 셀에 ‘증감 수량’, F1 셀에 ‘변경 상태’를 입력합니다.
D2 셀에 다음 수식을 입력합니다.
=XLOOKUP(A2,기존!A:A,기존!C:C,"신규")이 수식은 수정 시트의 상품코드를 기존 시트에서 찾아 해당 상품의 기존 재고수량을 가져옵니다.
예를 들어 A001 상품의 수정 재고가 45개이고 기존 재고가 50개라면 D2 셀에는 50이 표시됩니다.
기존 파일에 없는 A006 상품은 ‘신규’로 표시됩니다.
XLOOKUP 함수는 Microsoft 365, Excel 2021 이상 등 지원되는 버전에서 사용할 수 있습니다.
5. 재고 증감 수량 자동 계산하기
기존 재고와 수정 재고를 비교하면 재고가 얼마나 증가하거나 감소했는지 계산할 수 있습니다.
수정 시트 E2 셀에 다음 수식을 입력합니다.
=IF(D2="신규","",C2-D2)이 수식은 수정 재고에서 기존 재고를 뺀 값을 표시합니다.
계산 결과가 양수이면 재고 증가, 음수이면 재고 감소입니다.
| 상품코드 | 기존 재고 | 수정 재고 | 증감 수량 |
|---|---|---|---|
| A001 | 50 | 45 | -5 |
| A002 | 30 | 30 | 0 |
| A003 | 40 | 55 | +15 |
| A005 | 25 | 25 | 0 |
| A006 | 신규 | 15 | – |
재고가 감소한 상품과 증가한 상품을 숫자로 확인할 수 있습니다.
E열에 사용자 지정 표시 형식 +0;-0;0을 적용하면 증가한 수량 앞에 + 기호도 표시할 수 있습니다.
6. 변경 상태를 자동으로 표시하기
이번에는 변경된 상품과 변경되지 않은 상품을 구분하겠습니다.
수정 시트 F2 셀에 다음 수식을 입력합니다.
=IF(D2="신규","신규 추가",IF(C2=D2,"변경 없음",IF(C2>D2,"재고 증가","재고 감소")))수식을 입력한 후 아래 행까지 채우면 다음과 같은 결과가 나옵니다.
| 상품코드 | 상품명 | 변경 상태 |
|---|---|---|
| A001 | 무선마우스 | 재고 감소 |
| A002 | 키보드 | 변경 없음 |
| A003 | USB허브 | 재고 증가 |
| A005 | 노트북거치대 | 변경 없음 |
| A006 | 웹캠 | 신규 추가 |
이제 상품별 변경 상태를 직접 입력하지 않아도 됩니다.
기존 시트나 수정 시트의 수량을 바꾸면 비교 결과도 자동으로 다시 계산됩니다.
다만 기존 데이터에 없는 상품을 찾는 것과 기존 데이터에서 삭제된 상품을 찾는 것은 별도 작업이 필요합니다.
7. 삭제된 상품 자동으로 찾기
수정 파일에 없는 기존 상품도 확인해야 합니다.
기존 시트의 D1 셀에 ‘상품 상태’를 입력하고 D2 셀에 다음 수식을 입력합니다.
=IF(COUNTIF(수정!A:A,A2)=0,"삭제됨","유지")이 수식은 기존 상품코드가 수정 시트에 존재하는지 확인합니다.
수정 시트에서 해당 상품코드를 찾을 수 없으면 ‘삭제됨’으로 표시합니다.
| 상품코드 | 상품명 | 상품 상태 |
|---|---|---|
| A001 | 무선마우스 | 유지 |
| A002 | 키보드 | 유지 |
| A003 | USB허브 | 유지 |
| A004 | 모니터 | 삭제됨 |
| A005 | 노트북거치대 | 유지 |
A004 모니터가 삭제된 상품으로 표시됩니다.
여기서 ‘삭제됨’은 수정 파일에 해당 상품코드가 없다는 의미입니다. 실제로 상품이 폐기되었거나 판매 중단되었다는 뜻은 아니므로 원본 자료를 확인해야 합니다.
8. 조건부 서식으로 변경된 값에 색상 표시하기
변경된 항목을 색상으로 구분하면 확인하기가 더욱 편리합니다.
수정 시트에서 F2부터 F1000까지 선택합니다.
엑셀 상단 메뉴에서 홈 → 조건부 서식 → 셀 강조 규칙 → 텍스트 포함을 선택합니다.
다음과 같이 설정합니다.
| 변경 상태 | 표시 색상 |
|---|---|
| 재고 감소 | 연한 빨간색 |
| 재고 증가 | 연한 파란색 |
| 신규 추가 | 연한 초록색 |
| 변경 없음 | 기본 색상 |
삭제된 상품은 기존 시트에서 ‘삭제됨’이라는 텍스트에 조건부 서식을 적용하면 됩니다.
이렇게 설정하면 재고가 감소하거나 새로 추가된 상품을 색상으로 빠르게 구분할 수 있습니다.
공식 기능 설명은 마이크로소프트 지원 페이지에서 확인할 수 있습니다.
9. 변경된 항목만 자동으로 추출하기
전체 상품 중 변경된 상품만 별도로 확인하고 싶다면 FILTER 함수를 활용할 수 있습니다.
수정 시트의 H2 셀에 다음 수식을 입력합니다.
=FILTER(A2:F1000,(F2:F1000<>"변경 없음")*(F2:F1000<>""),"변경 항목 없음")이 수식은 F열의 변경 상태가 ‘변경 없음’이 아니면서 빈칸도 아닌 행만 추출합니다.
이번 예제에서는 다음과 같은 상품이 표시됩니다.
| 상품코드 | 상품명 | 변경 상태 |
|---|---|---|
| A001 | 무선마우스 | 재고 감소 |
| A003 | USB허브 | 재고 증가 |
| A006 | 웹캠 | 신규 추가 |
삭제된 A004 상품은 수정 시트에 존재하지 않으므로 이 결과에는 포함되지 않습니다. 삭제 상품은 기존 시트의 상태 열에서 별도로 확인해야 합니다.
FILTER 함수는 Microsoft 365, Excel 2021 이상 등 지원 버전에서 사용할 수 있습니다. 결과가 펼쳐질 영역은 비워 두어야 합니다.
10. 챗GPT로 변경 내역 보고서 작성하기
두 파일의 비교가 끝났다면 챗GPT를 이용해 변경 내역을 보고서 형태로 정리할 수 있습니다.
다음과 같이 요청합니다.
챗GPT 입력 예시
“엑셀 파일 비교 결과를 바탕으로 재고 변경 보고서를 작성해 줘. 재고가 증가한 상품, 감소한 상품, 신규 추가된 상품, 삭제된 상품을 구분하고 각 상품의 변경 전후 수량과 증감 수량을 정리해 줘. 마지막에는 관리자가 확인해야 할 사항을 작성해 줘.”
이번 실습 데이터로 작성하면 다음과 같습니다.
재고 변경 비교 보고서
비교 대상 : 기존 재고표와 수정 재고표
| 구분 | 상품 수 |
|---|---|
| 재고 증가 | 1개 |
| 재고 감소 | 1개 |
| 신규 추가 | 1개 |
| 목록에서 삭제 | 1개 |
| 변경 없음 | 2개 |
주요 변경 사항
무선마우스 재고가 50개에서 45개로 5개 감소했습니다.
USB허브 재고가 40개에서 55개로 15개 증가했습니다.
웹캠 15개가 신규 등록되었습니다.
모니터는 수정 재고표에서 제외되었습니다.
관리자는 재고 감소 상품의 추가 발주 필요 여부를 확인하고, 목록에서 제외된 모니터의 삭제 사유를 점검할 수 있습니다.
11. 엑셀 파일 비교할 때 주의할 점
두 파일을 정확하게 비교하려면 몇 가지 사항을 확인해야 합니다.
첫째, 비교 기준이 되는 상품코드나 거래처번호가 중복되지 않아야 합니다.
둘째, 숫자가 문자로 저장되어 있거나 상품코드에 불필요한 공백이 있으면 비교 결과가 달라질 수 있습니다.
셋째, 상품코드 앞에 0이 포함되어 있다면 두 파일에서 같은 형식으로 저장해야 합니다. 예를 들어 0012와 12는 서로 다른 코드로 취급해야 할 수 있습니다.
넷째, 파일의 행 순서가 달라도 비교할 수 있도록 상품코드 같은 고유 식별값을 기준으로 사용해야 합니다.
다섯째, 수식으로 계산한 결과와 실제 입력된 값이 다른 경우에는 비교 목적에 따라 수식 자체를 비교할지, 계산된 결과를 비교할지 결정해야 합니다.
여섯째, 두 파일을 비교한 결과가 중요한 회계·정산 자료라면 변경 항목뿐 아니라 전체 행 수, 중복 코드, 누락된 값도 함께 검증하는 것이 안전합니다.
또한 위 수식은 수정 시트에 이미 입력된 행을 기준으로 작동합니다. 다음번에 새로운 파일을 가져올 때는 기존 시트와 수정 시트의 데이터를 교체하고 필요한 수식을 새 행까지 적용해야 합니다.
12. 자주 묻는 질문
Q. 두 엑셀 파일의 행 순서가 달라도 비교할 수 있나요?
가능합니다. XLOOKUP 함수나 챗GPT를 이용해 상품코드처럼 고유한 기준값으로 비교하면 행 순서가 달라도 동일한 상품을 찾을 수 있습니다.
Q. 엑셀 파일에 데이터가 수천 개 있어도 비교할 수 있나요?
가능합니다. 다만 파일 크기와 사용 환경에 따라 처리 속도가 달라질 수 있습니다. 데이터가 많다면 전체 열 참조 대신 실제 데이터 범위를 지정하거나 Power Query를 사용하는 방법도 고려할 수 있습니다.
Q. 두 파일에서 여러 열이 동시에 변경되면 어떻게 하나요?
재고수량뿐 아니라 판매가격, 상품명, 거래처 정보도 각각 비교할 수 있습니다. 챗GPT에 비교할 열을 지정하면 여러 항목의 변경 전후 값을 함께 정리하도록 요청할 수 있습니다.
Q. 매일 새로운 파일을 받아 자동으로 비교할 수 있나요?
가능합니다. 다만 매일 파일을 업로드하는 방식은 완전 자동화가 아닙니다. 반복적으로 같은 구조의 파일을 비교한다면 Power Query나 별도 자동화 프로그램을 활용하는 것이 효율적입니다.
13. 마무리
엑셀 두 파일의 변경된 값을 비교하는 작업은 재고관리, 매출관리, 거래처관리 등 다양한 업무에서 활용할 수 있습니다.
챗GPT를 이용하면 두 파일을 분석하고 변경된 항목을 정리하는 작업을 쉽게 진행할 수 있으며, XLOOKUP과 IF, COUNTIF, FILTER 함수를 활용하면 엑셀 안에서도 비교 결과를 자동으로 표시할 수 있습니다.
처음에는 상품코드와 재고수량처럼 간단한 데이터부터 비교해 보고, 익숙해지면 판매가격이나 매출금액 등 여러 항목으로 확대해 보세요.
한 번 비교표를 만들어 두면 이후에는 데이터를 교체하고 결과를 확인하는 방식으로 반복 작업을 줄일 수 있습니다.
답글 남기기