[목록 비교] 둘 이상의 데이터 비교 : 데이터 중 중복된 값 찾기, 중복되지 않은 값 찾기

 

* 이 방법은 중복되지 않은 값 찾기, 둘 이상의 데이터 목록 비교시에 추천 드립니다.
* 작업용 엑셀 데이터 자료를 남겨둘 때에도 유용하게 사용됩니다.

두개 이상의 raw data 형식의 데이터 목록을 비교해야 할 때가 많았는데
유용하게 썻던 방법을 공유한다.

첫번째 방법은 vlookup 함수를 사용하는 방법이고
두번째 방법은 피벗테이블을 사용하는 방법이다.

하나씩 소개해 보겠다~ 팔로미~~

(1)  vlookup 함수를 사용한 비교법 

: 한 데이터 목록을 중심으로 비교할때 추천!

https://blog.naver.com/milkywaystream/221128917768

[찾기/참조] 1. VLOOKUP, HLOOKUP

* 엑셀의 제일 기본은 VLOOKUP, HLOOKUP 이라고들 말한다. 엑셀 할줄 알아? 의 기준은 VL...

blog.naver.com

※ ↑ ↑ vlookup 함수 사용하는 법 ↑ ↑
(hlookup 함수 사용법도 같이 있어요!!)

 

두 데이터 목록이 있고, 이 두 목록을 비교할 것이다.
어떤 값이 두 목록에 다 들어가있고, 어떤 값이 한 목록에만 들어가 있는지
 vlookup함수로 확인할수 있다.

오른쪽에 있는 DATA2 목록을 중심으로 vlookup 함수를 넣어 대조해 보면~

※ 함수문 설명 ※
vlookup(①D3, ②$B$3:$B$7,③1,④0)
= ① D3셀과 동일한 데이터를
② B3:B7셀 위치에서 찾아서
③ 찾은 데이터를 중심으로 1번째 열의 값을 가져오되
④ "정확한 값"을 찾아서 가져올 것

이란 뜻입니다 . ㅎㅎㅎㅎㅎ

이렇게 결과값이 나온다. ( 위 표는 자동채우기까지 해준 상태)

DATA2를 기준으로, DATA1에도 있는 "사과","배"는 값이 끌어와졌지만
DATA1에 없는 "바나나","딸기","자몽" 은 에러 (#N/A) 가 나타난다.

좀더 깔끔하게 보고 싶거나, 에러가 난 값 (DATA1과 중복되지 않는 값) 을 먼저 보고자 한다면

"데이터" - "필터" - "텍스트 내림차순 정렬" 을 해보자

에러 (#N/A)는 큰 값이기 때문에, 내림차순을 하면 목록의 제일 위로 올라온다. 
좀더 깔끔하고, 에러가 난 값들만 모아서 볼 수가 있다.

잘 정렬된 엑셀을 보면 참 신이 나요...

vlookup 함수를 이용한 데이터 대조는 끝~~


(2)  피벗테이블을 사용한 비교법 

: 중복된 데이터, 중복되지 않은 데이터를 한눈에 볼 때 추천!

이번엔 피벗테이블을 사용해서 비교해 보겠다

비교할 두 데이터 목록을 합쳐서 하나로 만들어 준다.

합칠때 주의할 것!!

1) 꼭 열 이름을 지정해 줄 것 (피벗테이블용 데이터이기 때문)
2) 참조값은 꼭 데이터 목록 이름으로 할 것. 그래야 구분이 된다.
3) 한 열은 꼭 "1"로 채울 것. 위의 합쳐진 표에도 가운데 열에 1이 채워져있다.

그럼 이제 예쁘게 만들어진 표를

① 전체 선택하고
② 리본메뉴 - "삽입" 에 들어가서
③ 피벗테이블 삽입을 눌러 피벗테이블을 만들어준다.

새 시트에 만들어도 되고~ 기존 시트에 만들어도 되고~
( 기존시트에 만들면 원본 데이터랑 비교하기 쉽고 실수할 확률도 줄어듭니당^^ )

피벗테이블이 만들어졌으면, 오른쪽 피벗 테이블 필드 목록처럼 셋팅해 준다.

① 행 레이블 (세로로 볼 것) : 비교할 데이터들
② 열 레이블 (가로로 볼 것) : 데이터 목록 이름
③ 값 : 1로 채웠던 열 이름 (이 예제에서는 "1)

피벗 테이블의 행 레이블은 자동으로 중복 제거가 된다.
원본 데이터에 두개, 세개가 있어도 피벗테이블의 행 레이블로 가게되면 그냥 한개로 나온다는 것!!

( 단, 같은 값으로 보여도 텍스트와 숫자는 각각 다른 값으로 엑셀이 연산을 합니다.
때문에 텍스트인지, 숫자인지 꼭 확인이 필요합니다!!)

※ 엑셀에서 텍스트 ↔ 숫자 변환하는 방법

여튼 다 왔음.. 저렇게 셋팅한 후에 

총합계 - 정렬 - 숫자내림차순 정리를 해보자!!

숫자 내림차순 정렬을 하면 높은 숫자부터 정렬이 된다.
비교한 데이터목록이 총 2개였기 때문에, 두 목록 중 겹치는 항목은 총합계가 2로 나온다.
여기서는 "사과","배"가 두 목록 중 겹치는 항목이 된다 .

반면에 겹치지 않는 항목은 그보다 작은수가 나온다.
"한과","딸기","자몽","바나나","대추","밤" 은 겹치지 않는 항목들이다.
그리고 각 항목들이 어느 데이터 목록에 해당되는지도 알 수 있다.
한과, 대추, 밤의 경우 DATA1,
딸기, 자몽, 바나나의 경우 DATA2에 해당된다고 보면 된다.

피벗테이블 활용법도 끝~~!

두 목록 비교, 중복된 항목 갯수 세기, 중복되지 않은 항목 갯수 세기,
두 목록에서 중복되는 항목 알아보기...

목록 비교하는 업무 지겹도록 했는데
누군가에겐 도움이 되었으면 좋겠따...☆

끝!