[ 응용 ] 엑셀 파워쿼리(Power Query)를 사용한 시트 합치기, 엑셀 파일 자료 취합하기

[ 들어가며 ]

 

여러 부서 또는 여러 고객들의 자료를 별도의 엑셀파일로 관리해야 하는 경우가 있지만,

때에 따라서는 모든 자료를 하나의 시트로 취합하여

관리해야 하는 경우가 있습니다.

 

여러개의 엑셀 파일을 

파워쿼리(Power Query)를 사용해

간단하게 취합하는 방법을 알아보겠습니다.

 

[ 준비하기 ]

 

이렇게 하나의 폴더에 

취합해야 할 엑셀파일을 모아 둡니다.

 

각 파일의 내부 자료는

아래와 같이 이루어져 있습니다.

 

이제 이 파일들을 하나의 시트에 취합해 보겠습니다.

 

[ 취합하기 ]

 

새로운 파일을 만들거나

새로운 시트를 만들어 줍니다.

 

그렇게 한 후

아래 그림과 같이

[ 데이터 ] → [ 데이터 가져오기 ] → [ 파일에서 ] → [ 폴더에서 ]

를 차례로 클릭해 줍니다.

 

해당 폴더를 선택한 후 

[ 열기 ]를 눌러줍니다.

 

그렇게 하면

아래 그림과 같이 창이 하나 나타납니다.

원하는 파일이 누락되지 않았는지 확인한 후

[ 결합 및 로드 ]를 눌러줍니다.

[ 결합 ] 옆에 있는화살표를 클릭하면 아래 그림과 같은

메뉴를 보실 수 있습니다. 

 

그 다음 아래 그림과 같이

[ 시트 선택 ]을 한 후 [ 확인 ]을 눌러줍니다.

 

잠깐의 로딩이 있은 후

아래 그림과 같이

일차적으로 취합된 표가 나타납니다.

 

기본이 되는 엑셀 파일의 데이터의 형식에 따라

취합되는 형태가 조금 다를 수는 있습니다.

 

먼저 취합된 자료를 보면

A열에 파일이름이 있는데 필요가 없습니다.

그리고 구분을 하기위해 만들어 놓은

[ 이름, 국어, 영어, 수학 ]이 중복되고

기타 불필요한 부분들이 있습니다.

 

이제 이것들을 조금씩 수정해 보겠습니다.

 

엑셀의 메뉴를 보면

[ 쿼리 ]라는 탭이 나타난 것을 보실 수 있습니다.

[ 쿼리 ] → [ 편집 ]을 선택합니다.

 

먼저 맨 앞의 파일이름은 필요가 없으므로

제거해 보도록 하겠습니다.

아래 그림의 

[ 보라색 네모 ] 부분을 클릭하고

[ 제거 ]를 누릅니다.

 

이렇게 하면 아래 그림과 같이 나타납니다.

 

다음으로는 불필요한 [ 비어있는 항목을 제거 ]하도록 하겠습니다.

아래 그림의 빨간색 네모 부분입니다.

 

동그라미 안의 화살표를 누른 후

[ 빈 항목 제거 ]를 눌러줍니다.

 

그러면 이제 빈항목들이 제거되고

아래 그림과 같이 나타나게 됩니다.

 

그런데 [ 이름, 국어, 영어, 수학 ]이 중복되서 나타날 뿐만 아니라

우리는 이것을 제목행으로 사용하고 싶은데

그렇게 되지 않았습니다.

 

이제 이 부분들을 수정해 보겠습니다.

[ 행 제거 ]를 누른 후 [ 중복된 항목 제거 ]를 눌러줍니다.

 

이제 거의 다 됐습니다.

[ 중복된 항목 제거 ]를 하면 아래 그림과 같이 나타납니다.

 

이제 [ 이름, 국어, 영어, 수학 ]을 맨위로 끌어올려

제목행으로 만들기만 하면 됩니다.

아래 그림에서 처럼

[ 첫 행을 머리글로 사용 ]을 눌러줍니다.

 

이제 한번의 클릭만이 남았습니다.

[ 첫 행을 머리글로 사용 ]을 눌러주면 아래 그림과 같이 나타납니다.

 

이 상태에서 

[ 닫기 및 로드 ]를 눌러줍니다.

 

아래 그림이 취합된 최종 파일입니다.

 

별도의 프로그래밍 없이 간단하게 엑셀파일을 취합할 수 있습니다.

여기를 방문하시면 더 많은 엑셀 관련 자료를 확인할 수 있습니다.

엑셀 공부하기
VBA 공부하기