엑셀 리스트 비교 추출 - egsel liseuteu bigyo chuchul

반응형

지난 강좌에 이어 이번에는 두 시트 간의 데이터를 비교하여 일치 또는 불일치하는 데이터를 추출하는 방법에 대해 소개하겠습니다.

그럼 이번 강좌를 익히는데 도움이 되는 고급 필터 기능을 활용하여 중복 데이터를 구하는 강좌를 참고하세요. 

아래 URL을 클릭하면 강좌를 볼 수 있습니다. 

https://hantip.net/77

실습 데이터 다운로드 ↓

두시트비교일치불일치데이터추출.xlsx

다운로드

A시트와 B시트를 비교하여 A시트에 있는 데이터 중에 B시트에 없는 데이터를 추출하려고 합니다.

결과를 나타낼 시트를 선택하고 조건식을 입력합니다.

=COUNTIF(B!$B$3:$B$17,A!B3)<1

첫 번째 인수는 ‘B시트의 등록번호 범위’를 지정합니다.

이름은 동명이인이 있을 수 있기 때문에 고유번호 등록번호를 비교합니다.

두 번째 인수는 ‘A시트의 첫 번째 등록 번호(B3)셀’을 지정합니다.

그럼 Countif 함수는 A시트의 첫 번째 등록번호를 B시트의 등록번호 범위와 비교합니다.

등록번호 ‘B1209’가 B시트 등록번호 범위에 몇 번 있는지 구하게 됩니다.

그 개수가 1미만인 경우가 하나도 없는 경우가 됩니다.

이 동작을 A시트의 마지막 등록번호까지 반복해서 그 개수를 구한 다음 조건과 비교를 합니다.

이때 고급필터는 B시트에 없는 A시트의 등록번호를 추출합니다.

조건 작성이 끝나면 결과를 구할 시트를 선택하고 결과를 표시할 시작 셀을 선택한 후 [데이터]-[고급] 메뉴를 실행합니다.

목록 범위에는 A시트의 데이터 전체 범위를 지정합니다.

추출하려고하는 데이터는 A시트의 데이터입니다.

B시트에 있는 데이터를 제외하고 추출하려고 합니다.

조건 범위도 지정하고 ‘다른 장소에 복사’ 옵션을 선택한 후 복사 위치를 지정하면 됩니다.

B시트의 데이터를 제외한 A시트의 데이터를 추출했습니다.

동영상 강좌도 준비했습니다.

도움이 되길 바랍니다.

짤막한 강좌 한정희 강사였습니다.

반응형

공유하기

게시글 관리

저작자표시 비영리 변경금지

• 카카오스토리
• 트위터
• 페이스북

'기초에서 실무까지 > 엑셀' 카테고리의 다른 글

엑셀 두 시트 비교하기, 함수 안 쓰고 가장 쉽게 비교하는 방법!

두 시트 비교 목차 바로가기

1. 예제파일 다운로드
2. 조건부서식을 이용한 두 시트 비교하기를 사용하는 경우
3. 조건부서식을 이용하여 서로다른 시트 비교하기

영상강의

동영상 재생이 안 되시나요? - 유튜브에서 시청 하기

큰 화면으로 보기

예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

• [실무기초] 서로 다른 두 시트 비교하기

  예제파일

  다운로드

이번 강의에서는..

함수를 사용하지 않고 서로다른 두 시트를 비교하는 가장 쉬운 방법을 알아봅니다. 아주 쉽고 빠른 방법인만큼 다소 제한된 상황에서만 적용할 수 있지만, 남녀노소 누구나 쉽게 사용할 수 있다는 점과 다양한 방법으로 쉽게 응용할 수 있다는 점에서 실무자가 알아두면 아주 유용한 기능중 하나입니다.

조건부서식을 이용한 두 시트 비교하기 기능은 아래 두 조건을 만족하는 상황에서 사용가능합니다.

1. 비교할 두 시트는 '같은 통합문서'의 시트여야 합니다. 만약 다른 통합문서에 있는 시트를 비교할 경우 '조건부 서식 조건으로 다른 통합문서에 대한 참조를 사용할 수 없습니다.'라는 오류문구가 출력됩니다.
   

   만약 사용중인 엑셀이 2010 이전버전일 경우, '조건부 서식 조건으로 다른 워크시트 또는 통합문서에 대한 참조를 사용할 수 없습니다.' 라는 오류문구가 출력됩니다. 그럴경우 비교할 대상 범위를 동일한 시트로 복사->붙여넣기 한 후 조건부서식을 적용해야 합니다. (다른 시트를 참조하는 조건부서식은 엑셀 2010 이후버전부터 제공되는 기능입니다..ㅠ-ㅠ)

   
   두개의 시트는 같은 통합문서에 있어야 합니다.
2. 비교할 두 범위는 동일한 넓이와 순서, 그리고 형식을 가져야합니다. 조건부서식은 각 시트를 '셀 단위'로 하나씩 비교합니다. 따라서 비교할 대상의 넓이나 형식이 다를 경우 옳지않은 결과를 반환할 수 있습니다.
   
   비교 대상의 넓이나 형식이 다를경우 옳지않은 결과를 반환할 수 있습니다.

두 시트간의 값 포함여부,합계의 차이 등을 계산하려면 이번 포스트에서 다루는 내용대신 SUMIF 함수 및 COUNTIF 함수를 사용하여 두 시트를 비교합니다.

조건부 서식을 이용한 '시트 비교' 단계별로 알아보기

매일 관리되는 '발주서'를 예제로 알아보겠습니다. '라면천국 신촌점'으로 매일 라면을 판매하고 있다고 가정하겠습니다. 출고 마감시간은 오후 12시.. 지금시간 11시 50분인데, 주문수량이 바뀌었다고 급하게 발주서를 보내왔어요!?

어느 항목이 몇개 수정되었는지 같이 이야기해주면 좋았을텐데.. 그냥 수정된 발주서만 '떡~'하니 보내준 상황입니다.

업체에 다시 물어보기에는 시간이 없고, 어서 창고에 수정된 내용을 알려줘야하는데.. 그러려면 어서 두 시트를 비교해서 바뀐 제품을 알아내야겠죠?

이런 상황에서 조건부서식을 이용하면 두 시트를 아주 간단하게 비교할 수 있습니다.

조건부서식을 이용하여 서로다른 시트 비교하기

1. 서로 다른 부분을 표시할 시트로 이동한 뒤, 대상 범위를 선택합니다.
   
   결과를 표시할 시트에서 범위를 선택합니다
2. [홈] - [조건부서식] - [셀 강조규칙] - [같음] 을 선택합니다.
   
   
   [조건부서식] - [셀 강조규칙] - [같음] 으로 이동합니다
3. 비교할 시트로 이동한 뒤, 대상 범위의 좌측상단 첫번째 셀을 선택합니다.
   
   비교 대상 범위의 '첫번째 셀'을 선택합니다
4. 셀 주소앞에 달러($) 표시가 붙어 절대참조로 입력됩니다. 키보드 F4키 3번을 눌러 상대참조로 변경한 뒤 '확인'을 클릭합니다.
   
   셀 참조방식을 '상대참조'로 변경합니다
5. 비교대상과 같은부분이 빨간색으로 표시됩니다. 값이 다른부분만 빨간색으로 표시하기 위해, 범위가 선택된 상태로 [조건부서식] - [규칙관리]로 이동합니다.
   
   
   범위가 선택된 상태로, [조건부서식] - [규칙관리]로 이동합니다
6. [규칙편집] 을 선택한 뒤, 규칙설명을 등호(=)가 아닌 다름(<>) 기호로 변경한 뒤 '확인'을 눌러 마무리합니다.
   
   
   [규칙편집]에서 규칙설명을 등호(=)에서 다름(<>)으로 변경합니다
7. 그럼 서로 다른 부분만 빨간색으로 표시되는것을 확인 할 수 있습니다.
   
   서로 다른 두 시트 비교가 완료되었습니다
8. 이외에도 조건부서식의 다양한 기능을 활용하면, 함수를 사용하지 않아도 여러가지 상황에서 문제를 해결할 수 있습니다.
   
   이외에도 다양한 조건부서식을 응용하면 여러상황에서 문제를 쉽게 해결할 수 있습니다

링크] MS 홈페이지 조건부서식 상세설명 바로가기

5 27 투표

게시글평점