매월 매출 파일을 하나씩 복사해서 붙여넣고 있다면, 파워쿼리의 폴더 가져오기를 활용할 수 있습니다.

같은 형식의 파일을 한 폴더에 모아두면 됩니다. 이후에는 새 파일만 추가하고 새로 고침하면 전체 자료가 자동으로 합쳐집니다.

왜 폴더에서 가져올까

1월, 2월, 3월 매출 파일이 따로 있다고 가정해 보겠습니다.

각 파일을 열어서 하나의 통합 파일로 옮기면 시간이 오래 걸립니다. 열 순서가 달라지거나 일부 행을 빠뜨릴 가능성도 있습니다.

파워쿼리는 지정한 폴더 안의 파일을 한꺼번에 불러옵니다. 반복되는 월별 보고서나 지점별 실적을 정리할 때 유용합니다.

예제 파일 설계

먼저 아래와 같이 월별 파일을 준비합니다.

각 파일의 열 이름은 동일하게 맞춥니다.

열 이름과 순서가 같아야 안정적으로 결합됩니다. 파일마다 제목 행이나 합계 행을 추가하지 않는 편이 좋습니다.

폴더 안의 파일 합치는 방법

통합 문서를 새로 만든 뒤 데이터 탭으로 이동합니다.

다음 순서로 선택합니다.

월별 파일이 들어 있는 폴더를 지정합니다. 파일 목록이 나타나면 ‘결합 및 변환’을 선택합니다.

파워쿼리가 예제 파일을 기준으로 나머지 파일의 구조를 확인합니다. 사용할 시트나 표를 선택하면 모든 파일이 하나의 쿼리로 합쳐집니다.

불필요한 데이터 정리하기

폴더를 불러오면 파일명, 확장자, 폴더 경로 같은 정보가 함께 나타날 수 있습니다. 보고서에 필요하지 않은 열은 제거합니다.

월 구분이 필요하면 파일명에서 월 정보를 추출하거나 거래일을 이용합니다.

예를 들어 거래일 열을 선택한 뒤 다음 메뉴를 이용할 수 있습니다.

필요하지 않은 월은 월 열의 필터에서 제외하면 됩니다. 반대로 특정 월만 남겨 월간 보고서를 만들 수도 있습니다.

빈 행, 합계 행, 임시 파일도 필터로 제거합니다. 파일명이 ~$로 시작하는 임시 파일은 오류를 만들 수 있으므로 제외하는 것이 좋습니다.

결과를 엑셀로 불러오기

정리가 끝났다면 홈 탭에서 다음 메뉴를 선택합니다.

결과를 표로 불러올지, 피벗 테이블이나 데이터 모델에서 사용할지 선택합니다. 일반적인 매출 목록이라면 새 워크시트의 표로 불러오면 됩니다.

매월 반복하는 업무 매뉴얼

담당자는 다음 달 파일을 기존 폴더에 추가합니다. 파일의 열 이름과 시트 구조는 이전 파일과 동일하게 유지합니다.

통합 문서를 연 뒤 결과 표에서 마우스 오른쪽 버튼을 누르고 ‘새로 고침’을 선택합니다.

파일 형식이 바뀌었거나 오류가 발생했다면 오른쪽 ‘쿼리 및 연결’ 창에서 해당 쿼리를 우클릭합니다. ‘편집’을 선택하면 파워쿼리 편집기로 다시 들어갈 수 있습니다.

마무리

폴더 결합의 핵심은 모든 파일의 구조를 같게 유지하는 것입니다. 최초 한 번만 폴더 지정, 데이터 변환, 월 필터를 설정하면 됩니다.

이후에는 새 파일을 폴더에 넣고 새로 고침하는 것만으로 자료가 합쳐집니다. 매달 반복되던 복사와 붙여넣기를 간단한 정기 업무로 바꿀 수 있습니다.

당분간 매주 목,일 파워쿼리/VBA 포스팅 드려요