- 최초 작성일: 2002-03-27
- 최종 수정일: 2026-09-29
- 조회수: 30 회
- 작성자: 권현욱 (엑셀러)
- 강의 제목: 시트 비교 후 다른 데이터 골라내기
들어가기 전에
오래 전 엑셀 파일로 진행하던 강의를 웹 글로 복원한 것이라, "버튼을 눌러보세요"처럼 엑셀에서 직접 조작하라는 표현이 남아 있을 수 있습니다. 결과는 본문의 그림이나 표로 확인하실 수 있으며, 현재와 맞지 않는 내용은 보완하되 전반적인 스타일은 당시의 갬성(?)을 유지했습니다.
같은 형태의 데이터가 서로 다른 시트에 나뉘어 있을 때, 한쪽 시트에는 있는데 다른 쪽 시트에는 없는 항목만 골라내야 하는 경우가 종종 있습니다. VBA를 사용해도 되지만, 이번 시간에는 VBA를 몰라도 워크시트 함수와 자동 필터만으로 이 문제를 해결하는 방법을 살펴보겠습니다.
- 최신 글을 불러오는 중...
시트 비교 후 다른 데이터 골라내기
핵심 요약
- MATCH 함수로 Sheet1의 값이 Sheet2에도 있는지 확인하면, 없는 값은 #N/A 오류로 표시됩니다.
- 자동 필터에서 "#N/A" 값을 선택하면 한쪽 시트에만 있는 데이터만 골라 볼 수 있습니다.
- MATCH 비교를 한 번 더 추가하면 관리번호는 같지만 다른 항목 값이 다른 데이터까지 함께 찾아낼 수 있습니다.
질문 하나
엑셀 고수님들… 한번 들어보세요.
제가 해결해야할 문제를 풀려고 책까지 구입하였는데
얼마전에 질문을 드렸는데, 실력이 부족하여…
부디 자세히 가르쳐 주시기 바랍니다.
문제는 자료의 양은 실제는 많구요.
--레코드추출1--질문1(이중비교)
먼저 SHEET1에서 관리번호의 각 셀을 기준으로,
SHEET2의 관리번호와 비교하여 같은 것중에서,
채택형태를 각각 다시 비교하여 틀린 것만을 추출하여,
SHEET3에 SHEET1의 레코드를 추출해 나타내야 하는 것입니다.
--레코드추출2--질문2
SHEET1에 관리번호와 SHEET2의 관리번호를
비교하여 SHEET1에는 있고 SHEET2에는 없는 것을 추출하여,
SHEET3에 SHEET1의 레코드를 나타내는 것입니다.
SHEET 구조 간략히
관리번호 | 채택형태 | 기타 | 등 | 등 |
123-D-34 | H | - | - | - |
132-T-23 | A | - | - | - |
- | - | - | - | - |
- | - | - | - | - |
- | - | - | - | - |
두 가지를 질문하셨습니다만, 실제로는 같은 내용이군요. ^^
Sheet1과 Sheet2를 비교하여 Sheet1에만 있는 내용을 별도의 시트로 옮기겠다는 질문이시지요? VBA를 아신다면 VB0027, 38 강좌 등을 참고하시면 되겠습니다만, 만약 VBA를 모르는 경우에 대비하여 엑셀의 워크시트 함수만으로 해결해 보도록 하겠습니다.
우선 질문하신 분의 자료를 약간 수정하여 Sheet1, Sheet2에 각각 올려 두었습니다.
(1) Sheet1의 D2 셀에 다음과 같은 수식을 입력한 다음 수식을 아래로 주~욱 복사합니다.
=MATCH(A2,Sheet2!$A$2:$A$20,0)그러면 Sheet1에는 있고 Sheet2에는 없는 데이터에는 모두 오류가 생길 것입니다.
(2) '데이터-필터-자동 필터' 메뉴를 선택합니다.
(3) 기타 필드 옆에 있는 드롭다운 버튼을 클릭하고 "#N/A" 값을 선택합니다.
위의 질문1과 같이 한번 비교한 것을 대상으로 또한번 비교를 하시려면 D열 옆에 Match 함수를 또한번 더 사용하신 다음 적절한 것을 필터링 하시면 되겠지요?
정리 — MATCH와 자동 필터로 시트 차이 데이터 찾기
| 단계 | 작업 내용 |
|---|---|
| 1. MATCH 수식 입력 | =MATCH(A2,Sheet2!$A$2:$A$20,0)을 Sheet1의 보조 열에 채우기 |
| 2. 오류 확인 | Sheet2에 없는 값은 #N/A 오류로 표시됨 |
| 3. 자동 필터 적용 | 데이터-필터-자동 필터 메뉴 실행 |
| 4. #N/A만 표시 | 드롭다운에서 "#N/A" 값을 선택해 차이 데이터만 추출 |
자주 묻는 질문 (FAQ)
Q1. Sheet1에만 있고 Sheet2에는 없는 데이터를 어떻게 찾나요?
Sheet1의 보조 열에 =MATCH(A2,Sheet2!$A$2:$A$20,0) 수식을 입력하고 아래로 복사합니다. Sheet2에 같은 값이 없으면 이 수식은 #N/A 오류를 반환하므로, 오류가 난 행이 곧 Sheet1에만 있는 데이터입니다.
Q2. MATCH로 찾은 #N/A 오류 행만 어떻게 골라내나요?
'데이터-필터-자동 필터' 메뉴를 적용한 뒤, MATCH 수식이 들어간 열의 드롭다운에서 "#N/A" 값을 선택하면 Sheet2에 없는 데이터, 즉 Sheet1에만 있는 행만 화면에 표시됩니다.
Q3. 두 항목을 동시에 비교해서 다른 값만 추출할 수도 있나요?
네. 관리번호를 MATCH로 한 번 비교한 뒤, 그 옆 열에 채택형태 등 다른 항목을 MATCH로 한 번 더 비교하는 수식을 추가하고 적절히 필터링하면, 관리번호는 같지만 다른 항목이 다른 데이터만 골라낼 수 있습니다.
마치며
VBA 없이도 MATCH와 자동 필터의 #N/A 조건만으로 두 시트의 차이를 손쉽게 찾아낼 수 있습니다. 비교 기준을 한 단계 더 추가하면 이중 비교와 같은 좀 더 복잡한 조건도 같은 원리로 해결할 수 있습니다.
