파워 쿼리를 사용하여 다른 Excel 파일의 테이블 조합

문제의 공식화

대부분의 Excel 사용자가 조만간 직면하게 되는 매우 표준적인 상황 중 하나에 대한 아름다운 솔루션을 살펴보겠습니다. 많은 수의 파일에서 하나의 최종 테이블로 데이터를 신속하고 자동으로 수집해야 합니다. 

지점 도시의 데이터가 포함된 여러 파일이 포함된 다음 폴더가 있다고 가정합니다.

파일 수는 중요하지 않으며 향후 변경될 수 있습니다. 각 파일에는 이름이 지정된 시트가 있습니다. 세일즈데이터 테이블이 있는 위치:

물론 테이블의 행(주문) 수는 다르지만 열 집합은 어디에서나 표준입니다.

작업: 도시 파일 또는 테이블의 행을 추가하거나 삭제할 때 후속 자동 업데이트와 함께 모든 파일의 데이터를 하나의 책으로 수집합니다. 최종 통합 테이블에 따르면 보고서, 피벗 테이블, 필터 정렬 데이터 등을 작성할 수 있습니다. 가장 중요한 것은 수집할 수 있다는 것입니다.

우리는 무기를 선택합니다

솔루션의 경우 최신 버전의 Excel 2016(필요한 기능이 기본적으로 기본적으로 내장되어 있음) 또는 무료 추가 기능이 설치된 이전 버전의 Excel 2010-2013이 필요합니다. 파워 쿼리 Microsoft에서 제공합니다(여기에서 다운로드). 파워 쿼리는 외부 세계에서 데이터를 Excel로 로드한 다음 이를 제거하고 처리하기 위한 매우 유연하고 강력한 도구입니다. 파워 쿼리는 텍스트 파일에서 SQL, 심지어 Facebook까지 거의 모든 기존 데이터 원본을 지원합니다.

Excel 2013 또는 2016이 없으면 더 이상 읽을 수 없습니다(농담). 이전 버전의 Excel에서 이러한 작업은 Visual Basic으로 매크로를 프로그래밍하거나(초보자에게는 매우 어렵습니다) 단조로운 수동 복사(오랜 시간이 걸리고 오류를 생성함)를 통해서만 수행할 수 있습니다.

1단계. 하나의 파일을 샘플로 가져오기

먼저 Excel이 "아이디어를 선택"하도록 한 통합 문서의 데이터를 예로 들어 보겠습니다. 이렇게 하려면 새 통합 문서를 만들고…

• Excel 2016이 있는 경우 탭을 엽니다. Data 그리고 쿼리 생성 – 파일에서 – 책에서 (데이터 - 새 쿼리 - 파일에서 - Excel에서)
• 파워 쿼리 추가 기능이 설치된 Excel 2010-2013이 있는 경우 탭을 엽니다. 파워 쿼리 그리고 그것을 선택 파일에서 – 책에서 (파일에서 — Excel에서)

그런 다음 열리는 창에서 보고서가 있는 폴더로 이동하여 도시 파일을 선택합니다(모두 일반적이기 때문에 어떤 파일이든 상관 없음). 몇 초 후에 왼쪽에서 필요한 시트(판매)를 선택해야 하는 네비게이터 창이 나타나고 그 내용이 오른쪽에 표시됩니다.

이 창의 오른쪽 하단 모서리에 있는 버튼을 클릭하면 다운로드 (하중), 그러면 테이블이 원래 형식으로 시트로 즉시 가져옵니다. 단일 파일의 경우 이것은 좋지만 이러한 파일을 많이 로드해야 하므로 조금 다르게 이동하여 버튼을 클릭합니다. 보정 (수정). 그런 다음 파워 쿼리 쿼리 편집기가 책의 데이터와 함께 별도의 창에 표시되어야 합니다.

이것은 우리가 필요로 하는 보기로 테이블을 "마무리"할 수 있게 해주는 매우 강력한 도구입니다. 모든 기능에 대한 피상적인 설명도 약 XNUMX페이지가 소요되지만 아주 간단히 이 창을 사용하여 다음을 수행할 수 있습니다.

• 불필요한 데이터, 빈 줄, 오류가 있는 줄 필터링
• 하나 이상의 열을 기준으로 데이터 정렬
• 반복을 없애라
• 고정 텍스트를 열로 나눕니다(구분자, 문자 수 등).
• 텍스트를 순서대로 배치하십시오(여분의 공백 제거, 대소문자 수정 등).
• 가능한 모든 방법으로 데이터 유형 변환(텍스트와 같은 숫자를 일반 숫자로 또는 그 반대로 변환)
• 표를 조옮김(회전)하고 XNUMX차원 교차 표를 평면 교차 표로 확장
• 테이블에 열을 추가하고 파워 쿼리에 기본 제공되는 M 언어를 사용하여 테이블에서 수식과 함수를 사용합니다.
• ...

예를 들어, 나중에 피벗 테이블 보고서를 더 쉽게 작성할 수 있도록 월의 텍스트 이름이 있는 열을 테이블에 추가해 보겠습니다. 이렇게 하려면 열 머리글을 마우스 오른쪽 버튼으로 클릭합니다. 데이터명령을 선택하고 중복 열 (중복 열)을 클릭한 다음 나타나는 중복 열의 헤더를 마우스 오른쪽 버튼으로 클릭하고 명령을 선택합니다. 변환 – 월 – 월 이름:

각 행에 대한 월의 텍스트 이름으로 새 열이 형성되어야 합니다. 열 머리글을 두 번 클릭하면 다음에서 이름을 바꿀 수 있습니다. 복사 날짜 더 편안하게 달예를 들어.

일부 열에서 프로그램이 데이터 유형을 제대로 인식하지 못한 경우 각 열의 왼쪽에 있는 형식 아이콘을 클릭하여 도움을 줄 수 있습니다.

간단한 필터를 사용하여 오류가 있는 줄이나 빈 줄은 물론 불필요한 관리자나 고객을 제외할 수 있습니다.

또한 수행된 모든 변환은 오른쪽 패널에 고정되어 항상 롤백(교차)하거나 매개변수(기어)를 변경할 수 있습니다.

가볍고 고급스럽죠?

2단계. 요청을 함수로 변환해 보겠습니다.

가져온 각 책에 대해 수행된 모든 데이터 변환을 이후에 반복하려면 생성된 요청을 함수로 변환해야 합니다. 그러면 이 요청이 모든 파일에 차례로 적용됩니다. 이를 수행하는 것은 실제로 매우 간단합니다.

쿼리 편집기에서 보기 탭으로 이동하여 버튼을 클릭합니다. 고급 편집기 (보기 — 고급 편집기). 이전의 모든 작업이 M 언어의 코드 형식으로 작성되는 창이 열립니다. 예제를 위해 가져온 파일의 경로는 코드에 하드코딩되어 있습니다.

이제 몇 가지 조정을 수행해 보겠습니다.

의미는 간단합니다. 첫 번째 줄 (파일 경로)=> 프로시저를 인수가 있는 함수로 바꿉니다. 파일 경로, 아래에서 고정 경로를 이 변수 ​​값으로 변경합니다. 

모두. 클릭 마감재  다음을 확인해야 합니다.

데이터가 사라진 것을 두려워하지 마십시오. 사실 모든 것이 정상입니다. 모든 것이 다음과 같아야 합니다. 🙂 특정 파일에 묶이지 않고 데이터를 가져오고 처리하는 전체 알고리즘이 기억되는 사용자 지정 함수를 성공적으로 만들었습니다. . 더 이해하기 쉬운 이름을 지정해야 합니다(예: 데이터 가져오기) 필드의 오른쪽 패널에서 영문 이름 그리고 당신은 거둘 수 있습니다 홈 — 닫기 및 다운로드 (홈 — 닫기 및 로드). 예제를 위해 가져온 파일의 경로는 코드에 하드코딩되어 있습니다. 기본 Microsoft Excel 창으로 돌아가지만 함수에 대한 연결이 생성된 패널이 오른쪽에 표시되어야 합니다.

3단계. 모든 파일 수집

가장 어려운 부분은 모두 뒤에 있고 즐겁고 쉬운 부분은 남아 있습니다. 탭으로 이동 데이터 – 쿼리 만들기 – 파일에서 – 폴더에서 (데이터 — 새 쿼리 — 파일에서 — 폴더에서) 또는 Excel 2010-2013이 있는 경우 탭과 유사하게 파워 쿼리. 나타나는 창에서 모든 소스 도시 파일이 있는 폴더를 지정하고 OK. 다음 단계에서는 이 폴더(및 하위 폴더)에 있는 모든 Excel 파일과 각 파일에 대한 세부 정보가 나열되는 창이 열립니다.

변화 (수정) 그리고 다시 친숙한 쿼리 편집기 창으로 들어갑니다.

이제 생성된 함수를 사용하여 테이블에 다른 열을 추가해야 합니다. 그러면 각 파일에서 데이터를 "가져올" 것입니다. 이렇게하려면 탭으로 이동하십시오. 열 추가 – 사용자 정의 열 (열 추가 - 사용자 정의 열 추가) 표시되는 창에서 함수를 입력합니다. 데이터 가져오기, 각 파일의 전체 경로를 인수로 지정합니다.

클릭 후 OK 생성된 열은 오른쪽 테이블에 추가되어야 합니다.

이제 불필요한 열을 모두 삭제해 보겠습니다(Excel에서와 같이 마우스 오른쪽 버튼을 사용하여 – 제거), 추가된 열과 파일 이름이 있는 열만 남겨둡니다. 이 이름(보다 정확하게는 도시)이 각 행의 총 데이터에 유용할 것이기 때문입니다.

이제 "와우 순간" – 추가된 열의 오른쪽 상단 모서리에 자체 화살표가 있는 아이콘을 우리 기능으로 클릭하십시오.

... 체크 해제 원래 열 이름을 접두사로 사용 (원래 열 이름을 접두사로 사용)클릭 OK. 그리고 우리의 함수는 기록된 알고리즘에 따라 공통 테이블에 모든 것을 수집하여 각 파일의 데이터를 로드하고 처리합니다.

완벽한 아름다움을 위해 파일 이름이 있는 첫 번째 열에서 .xlsx 확장명을 제거할 수도 있습니다. 표준 대체 방식으로 "nothing"(열 머리글을 마우스 오른쪽 버튼으로 클릭 - 대용품) 이 열의 이름을 City. 또한 날짜가 있는 열의 데이터 형식을 수정합니다.

모두! 클릭 홈 – 닫기 및 로드 (홈 — 닫기 및 로드). 모든 도시에 대한 쿼리로 수집된 모든 데이터는 "스마트 테이블" 형식으로 현재 Excel 시트에 업로드됩니다.

생성된 연결과 우리의 어셈블리 기능은 어떤 식으로든 별도로 저장할 필요가 없습니다. 일반적인 방식으로 현재 파일과 함께 저장됩니다.

앞으로 폴더(도시 추가 또는 제거) 또는 파일(줄 수 변경)이 변경되면 테이블이나 오른쪽 패널의 쿼리를 직접 마우스 오른쪽 버튼으로 클릭하고 선택하는 것으로 충분합니다. 명령 업데이트 및 저장 (새로 고치다) – 파워 쿼리는 몇 초 안에 모든 데이터를 다시 "다시 작성"합니다.

PS

개정. 2017년 XNUMX월 업데이트 이후 Power Query는 Excel 통합 문서를 자체적으로 수집하는 방법을 배웠습니다. 즉, 더 이상 별도의 기능을 만들 필요가 없으며 자동으로 발생합니다. 따라서 이 기사의 두 번째 단계는 더 이상 필요하지 않으며 전체 프로세스가 눈에 띄게 간단해집니다.

1. 왼쪽 메뉴에서 요청 생성 – 파일에서 – 폴더에서 – 폴더 선택 – 확인
2. 파일 목록이 나타나면 변화
3. 쿼리 편집기 창에서 이중 화살표로 이진 열을 확장하고 각 파일에서 가져올 시트 이름을 선택합니다.

그리고 그게 다야! 노래!

• 피벗 테이블 구축에 적합한 평평한 크로스탭으로 크로스탭 재설계
• Power View에서 애니메이션 거품 차트 만들기
• 다른 Excel 파일의 시트를 하나로 조합하는 매크로