파워쿼리를 이용하여 여러 시트의 자료 통합하기

파워쿼리를 이용하여 다른 파일에 들어있는 데이터를 취합하는 방법은 이전에 올린 적이 있습니다.

이번에는 같은 파일안에 있는 여러 시트에서 데이터를 취합하는 방법입니다.

 

엑셀 2016 버전 기준으로 작성하였습니다. 엑셀 2010 ~ 2013버전은 추가기능을 다운로드하여 설치하면 사용할 수 있습니다. (2007버전 이하에서는 파워쿼리를 사용할 수 없습니다.)

먼저, 각 시트의 데이터를 “표(Table)”로 만들어야합니다.

여기서 “표”는 일반적으로 우리가 사용하는 의미의 ‘가로 세로 사각형 안에 데이터를 집어넣는 것’이 아니라, 엑셀에서 특수한 기능을 하는 표입니다.

 

표로 만들어 두면 좋은 점이, 나중에 일부 시트에서 값의 변경이 생겼을때, 일일이 모든 시트를 다시 취합할 필요 없이, ‘새로고침’ 한 번으로 데이터가 갱신된다는 점입니다.

그러니 처음에 번거롭더라도, 각 시트의 데이터 부분을 표로 만들어두시면 나중에 편합니다.

 

엑셀의 ‘표’가 편리한 이유를 더 자세히 알고 싶은 분은 아래 링크를 참고하세요

http://jaykim361.tistory.com/286

 

표를 만드는 방법은 간단합니다.

일단 시트에 있는 데이터 부분을 선택합니다.

(반드시 제목행도 포함해야합니다.)

 

 

그 다음 홈 탭의 스타일 항목의 ‘표서식’을 누르면 나오는 여러 표 스타일 중 아무거나 하나 선택합니다.

 

창이 뜨면서 표에 사용할 데이터를 확인합니다.

‘머리글 포함’에 체크하고, 제목행이 포함되어있는지, 데이터가 있는 부분이 모두 포함되었는지 확인합니다.

그러면 위와 같은 형태가 됩니다.

첫행에 필터가 걸리고, 나머지 행에 번갈아가며 색이 입혀지는 형태가 되면 ‘표’로 설정이 된 것입니다.

 

 

표도구 – 속성에 보시면 표의 이름을 정해줄 수 있습니다. 보기에 편한 이름으로 바꾸시면 됩니다.

 

표의 이름을 정해줄 때는 규칙이 있습니다. 규칙에 맞지 않으면 위와 같은 에러메시지가 뜹니다.

 

 

이 작업을 데이터가 있는 모든 시트에 반복합니다.

(시트가 매우 많다면 매크로를 이용해 자동으로 할 수도 있을 것 같습니다.)

 

 

수식 – 이름관리자에 가보시면 지금까지 추가한 표가 표시됩니다. 한번 들어가서 확인해보세요

 

 

이제 시트에서 자료를 취합하겠습니다.

데이터 탭 – 데이터가져오기 – 기타 원본에서 – '빈쿼리'를 실행합니다.

 

파워쿼리 편집기가 뜨면

수식입력줄에서 다음을 입력합니다. (대소문자 구분합니다)

 

=Excel.CurrentWorkbook()

 

 

그러면 아까 설정해두었던 각 시트의 표가 나타납니다.

 

 

Content옆의 확장 단추를 클릭하여, 수합할 항목들이 선택되었는지 확인합니다.

 

그러면 위와 같이, 각 시트의 자료들이 하나의 표로 나타납니다.

 

이 상태에서, 맨 첫열의 필터를 클릭하여, ‘빈항목 제거’를 클릭합니다. (이후에 데이터가 변경되었을때 쉽게 반영하도록 하기 위함입니다.)

 

 

‘닫기 및 로드’ - ‘닫기 및 로드’를 실행합니다.

 

이제 새로운 시트에 수합된 결과가 나타납니다.

 

이제 데이터가 추가되거나 변경되었을때, 해당 내용이 취합 시트에 제대로 반영되는지 테스트해보겠습니다.

아무 시트에나 가서 맨 아래행에 데이터를 추가입력합니다.

B열 접수번호에 값을 넣는 순간, 해당 행 전체가 색깔과 테두리선이 그려지는 것을 볼 수 있습니다. (엑셀에서 표를 설정해두면 편리한 점 중의 하나입니다.)

 

이제 데이터가 취합된 시트로 가서, 표의 아무 셀이나 마우스 오른쪽으로 클릭하고 ‘새로 고침’을 합니다.

 

그러면 아까 추가한 내용이 반영된 것을 보실 수 있습니다.