두 시트에서 불일치, 중복되는 데이터 찾기(Access 이용)

안녕하세요.

 

'A-1' 시트와 'A-2' 시트에 입력된 값 중 'A-2' 시트에만 있고, 'A-1' 시트에는 없는 값을 찾으려고 합니다.

또 'B-1' 시트와 'B-2' 시트 모두에 있는 데이터를 구하려고 합니다.

 

엑셀에서 COUNTIFS 함수와 [고급 필터] 기능으로 구하는 강좌를 소개했습니다.

 

2021.10.27 - 두 시트에 중복된 데이터 추출하기(COUNTIFS 함수 활용)

두 시트에 중복된 데이터 추출하기(COUNTIFS 함수 활용)

 

 

액세스 쿼리 중 [불일치 검색 쿼리]와 [중복 데이터 검색 쿼리]를 이용해 같은 결과를 구하겠습니다.

 

실습 파일 다운로드

실습파일.xlsx

0.02MB

불일치-중복 데이터 찾기.accdb

0.34MB

 

완성 파일 다운로드

불일치-중복 데이터 찾기_완성.accdb

0.54MB

 

실습 파일 중 액세스 파일을 엽니다.

현재 액세스 파일은 아무런 데이터도 없는 빈 데이터베이스 파일입니다.

 

엑셀 시트 연결하기

액세스 파일에 엑셀 파일의 데이터를 가져오거나 연결해서 사용할 수 있습니다.

 

엑셀 파일의 'A-1' 시트와 'A-2' 시트를 연결해서 'A-2' 시트에는 있는 'A-1' 시트에는 없는 데이터만 나타내겠습니다.

[외부 데이터]-[가져오기 및 연결]-[새 데이터 원본]-[파일에서]-[Excel]을 선택합니다.

[외부 데이터 가져오기] 대화상자에서 실습파일.xlsx를 선택하고, '연결 테이블을 만들어 데이터원본에 연결'을 선택하고 [확인]을 클릭합니다.

한 번에 하나의 시트를 가져올 수 있습니다.

'워크시트 표시'가 선택된 상태에서 'A-1'을 선택하고 [다음]을 클릭합니다.

'첫 행에 열 머리글이 있음'에 체크를 하고 [다음]을 클릭합니다.

'첫 행에 열 머리글이 있다'는 의미는 엑셀 시트의 첫 행에 있는 데이터는 필드명이라는 것입니다.

'연결 테이블 이름'은 시트명이 나타납니다. 시트명과 같은 이름을 그대로 사용하는 것이 헷갈리지 않는 좋은 방법입니다.

'테이블로 연결되었다'는 메시지가 나타납니다.

왼쪽 [모든 Access 개체] 창의 [테이블] 항목에 'A-1' 테이블이 표시됩니다.

같은 방법으로 'A-2' 시트도 테이블로 연결합니다.

[외부 데이터]-[가져오기 및 연결]-[새 데이터 원본]-[파일에서]-[Excel]을 선택합니다.

 

데이터베이스에 테이블이 하나 추가 되어 있기 때문에 앞에서 나타났던 대화상자 모습과 차이가 있습니다.

마지막 항목인 '연결 테이블을 만들어 데이터 원본에 연결'을 선택한 뒤 [확인]을 클릭합니다.

'워크시트 표시'가 선택된 상태에서 'A-2'을 선택하고 [다음]을 클릭합니다.

'연결 테이블 이름'은 시트 이름과 같이 'A-2'로 두고 [마침]을 클릭합니다.

이제 왼쪽 [모든 Access 개체] 창의 [테이블] 항목에 'A-1', 'A-2' 테이블이 표시됩니다.

불일치 검색 쿼리

불일치 검색을 할 테이블 2개가 준비되었으니 쿼리를 만듭니다.

 

[만들기]-[쿼리]-[쿼리 마법사]를 선택합니다.

[불일치 검색 쿼리 마법사]를 선택하고 [확인]을 클릭합니다.

'A-2' 테이블에는 있고 'A-1' 테이블에는 없는 데이터를 찾는 중이기 때문에 기준이 되는 테이블은 'A-2' 테이블입니다.

'A-2' 테이블을 선택하고 [다음]을 클릭합니다.

비교할 테이블인 'A-1' 테이블을 선택하고 [다음]을 클릭합니다.

각 테이블의 필드 중 비교를 할 필드인 '이름' 필드를 각각 선택합니다.

결과로 나타낼 필드를 선택합니다. '번호', '이름' 필드를 모두 선택하고 [다음]을 클릭합니다.

쿼리 이름은 제시된 이름 그대로 두고 [마침]을 클릭합니다.

'A-2' 테이블에는 있고 'A-1' 테이블에는 없는 데이터가 표시됩니다.

 

엑셀 시트 가져오기

[중복 데이터 검색 퀴리]는 한 개의 테이블이나 쿼리에 동작합니다.

그래서 'B-1' 시트의 데이터와 'B-2' 시트의 데이터를 한 개의 테이블로 가져옵니다.

 

[외부 데이터]-[새 데이터 원본]-[파일에서]-[Excel]을 선택합니다.

실습파일.xlsx를 선택하고, '현재 데이터베이스의 새 테이블로 원본 데이터 가져오기'를 선택한 뒤 [확인]을 클릭합니다.

'워크시트 표시'가 선택된 상태에서 'B-1' 시트를 선택하고 [다음]을 클릭합니다.

'첫 행에 열 머리글이 있음'이 선택된 상태에서 [다음]을 클릭합니다.

가져오는 각 필드의 데이터 형식에 대한 설정을 할 수 있는데, 수정할 내용은 없기 때문에 그냥 [다음]을 클릭합니다.

'기본 키 없음'을 선택하고 [다음]을 클릭합니다.

'테이블로 가져오기' 항목에서 테이블 이름을 B로 입력하고 [마침]을 클릭합니다.

'가져오기 단계 저장'은 선택하지 않고 [닫기]를 클릭합니다.

왼쪽 [모든 Access 개체] 창의 [테이블] 항목에 B 테이블이 표시됩니다.

앞에서 엑셀 파일을 연결한 테이블을 나타내는 아이콘과 다릅니다.

다시 [외부 데이터]-[새 데이터 원본]-[파일에서]-[Excel]을 선택합니다.

실습파일.xlsx를 선택하고, '다음 테이블에 레코드 복사본 추가: B'를 선택한 뒤 [확인]을 클릭합니다.

'워크시트 표시'가 선택된 상태에서 'B-2' 시트를 선택하고 [다음]을 클릭합니다.

[다음]을 클릭합니다.

[마침]을 클릭합니다.

'가져오기 단계 저장'은 선택하지 않은 상태에서 [닫기]를 클릭합니다.

왼쪽 [모든 Access 개체] 창의 [테이블] 항목에 B 테이블을 더블클릭해 열어 보면 'B-2' 시트의 데이터가 추가되어 있습니다.

 

중복 데이터 검색 쿼리

[만들기-[쿼리]-[쿼리 마법사]를 선택합니다.

[중복 데이터 검색 쿼리 마법사]를 선택하고 [확인]을 클릭합니다.

'테이블: B'를 선택하고 [다음]을 클릭합니다.

'어떤 필드에 중복된 정보가 있습니까?'에서 '이름', '학교명' 필드를 선택하고 [다음]을 클릭합니다.

'쿼리를 사용하여 중복 값과 함께 필드를 표시하시겠습니까?'에서 '번호' 필드는 추가하지 않고 그냥 [다음]을 클릭합니다.

쿼리 이름은 기본값으로 그대로 두고 [마침]을 클릭합니다.

중복된 데이터가 표시됩니다.

마지막 필드에는 몇 번 중복되었는지도 같이 표시되었습니다.

 

쿼리 결과를 엑셀 파일로 내보내기

쿼리의 결과를 엑셀 파일로 내보낼 수 있습니다.

[모든 Access 개체] 창 [A-1과(와) 일치하지 않는 A-2]에서 마우스 오른쪽 클릭해서 [내보내기]-[Excel]을 선택합니다.

내보낼 위치와 파일 이름을 선택하고 아래 옵션은 그대로 두고 [확인]을 클릭합니다.

'내보내기 단계 저장'은 선택하지 않은 상태에서 [닫기]를 클릭합니다.

엑셀 파일이 만들어졌습니다.

 

필요하신 분께 도움이 되길 바랍니다.