Excel 사례 공유: Powerquery를 사용하여 한 번의 클릭으로 여러 시트 병합

저번에 파워 쿼리 병합 워크시트를 공유한 후, 많은 친구들이 워크시트를 늘리거나 줄이면 어떻게 해야 하는지 묻는 메시지를 남겼습니다. 오늘은 워크시트 변경이나 통합 문서의 데이터 변경에 관계없이 한 번의 클릭으로 요약을 새로 고칠 수 있도록 파워 쿼리를 사용하여 통합 문서를 병합하는 방법을 소개하겠습니다. 지금 와서 배워보세요!

마지막 튜토리얼을 공부하지 않은 분들은 이전 튜토리얼을 먼저 공부해 보세요: "Excel Case Sharing: Using Power Query to Merge Multiple Tables"

그림에 표시된 대로 4개의 워크시트가 있습니다. 워크북 아래에는 4개 도시의 판매 데이터가 각각 나열됩니다.

먼저 새 통합 문서를 만들고 데이터 탭에서 [획득 및 변환] 그룹을 열고 새 쿼리---파일에서---통합 문서에서를 클릭합니다.

데이터 가져오기 창에서 통합 문서의 위치를 ​​찾아 통합 문서를 선택한 후 가져오기를 클릭하세요.

탐색기 창에서 "복수 선택"을 확인하고 아래 4개의 워크시트를 확인하세요.

통합 문서를 직접 선택하고(끝의 4는 통합 문서에 4개의 워크시트가 있음을 나타냄) 편집을 클릭할 수도 있습니다.

이렇게 하면 이전에 소개한 파워 쿼리 편집기 인터페이스로 들어갑니다. 상단은 메뉴바, 가운데는 테이블 영역과 마지막에 워크시트로 반환될 데이터가 표시되는 쿼리 설정 창입니다.

중앙에 표시되는 테이블 영역에는 데이터 원본 통합 문서의 정보가 표시됩니다. "이름" 열은 통합 문서에 포함된 워크시트의 이름이고, "데이터" 열은 녹색 글꼴로 표시됩니다. 각 각 셀에는 테이블이 포함되어 있습니다. 셀을 클릭하면 내부 콘텐츠를 미리 볼 수 있습니다. (참고: 마우스를 텍스트 위에 놓고 손바닥 모양으로 바꾸면 셀에 있는 파일이 열리고 새 단계가 생성됩니다. 미리 봐야 할 경우에는 다른 곳을 클릭하면 됩니다. 셀. ). 이 테이블의 내용은 아래 미리보기 창에 표시되며, 이는 요약하려는 데이터입니다.

지금 해야 할 일은 "데이터" 열의 모든 테이블을 추출하고 병합하는 것입니다.

데이터 오른쪽 상단에 있는 확장 버튼을 클릭하고 드롭다운 창에서 모든 열을 확장하려면 선택하세요. 일반적으로 "원래 열 이름을 접두사로 사용"을 선택할 필요가 없으므로 확인을 클릭하세요.

이렇게 하면 워크북에 있는 워크시트의 모든 내용이 표에 요약됩니다. 오른쪽의 쿼리 설정 창에 새로운 단계가 나타납니다. 단계 앞의 십자 표시를 클릭하면 해당 단계가 삭제되고 이전 단계로 돌아갑니다. 이것은 파워 쿼리의 단계 레코더입니다. 특정 단계에서 실수를 해서 되돌아가야 할 경우 위 단계 중 하나를 선택하여 해당 단계의 결과를 보거나 새 단계를 삭제할 수 있습니다.

다음으로 불필요한 다른 열을 삭제하세요. Ctrl 키를 누른 채 삭제하려는 열을 선택하고 홈 탭 아래 [열 관리] 그룹에서 "열 삭제"를 클릭하세요.

새 열 이름을 원래 열 이름으로 변경하려면 필드의 첫 번째 행을 열 이름으로 직접 사용하면 됩니다. 홈 탭의 [변환] 그룹에서 "첫 번째 행을 제목으로 사용"을 클릭하세요.

병합된 워크시트에는 여러 제목이 있으므로 중복된 제목을 필터링하세요. 영업사원 열 오른쪽 상단의 필터 버튼을 클릭하고 '영업사원'을 선택하세요.

이것으로 우리가 원하는 작업이 완료되었습니다. 홈 탭 아래 [닫기] 그룹에서 "닫기 및 업로드"를 클릭하세요.

닫기 및 업로드는 파워 쿼리에서 작성한 모든 쿼리를 각 워크시트에 업로드하는 것을 의미합니다. 쿼리가 하나만 있는 경우 이 방법을 사용할 수 있습니다.

을 닫고 업로드하면 모든 사람이 테이블에 업로드할지 연결을 생성할지 선택할 수 있는 팝업 창이 나타납니다. 생성된 연결은 나중에 소개할 피벗 테이블 등을 생성하는 데 직접 사용할 수 있습니다.

이렇게 하면 파워 쿼리의 콘텐츠가 양식에 직접 업로드됩니다. 요약을 용이하게 하기 위해 정리된 데이터를 피벗 테이블에 삽입합니다.

이 요약 쿼리가 완료되었습니다. 데이터 원본 통합 문서에 워크시트를 추가해 보겠습니다. 내부의 필드 이름은 다른 워크시트와 일치합니다. 저장을 클릭합니다.

그런 다음 요약 테이블의 데이터 탭 아래 [연결] 그룹에서 "모두 새로 고침"을 클릭하세요.

새 워크시트가 추가되었습니다.

마찬가지로 워크시트가 줄어들거나 데이터가 변경되는 경우에도 이렇게 완성할 수 있습니다. 이런 방식으로 통합 문서의 데이터를 쉽게 요약할 수 있습니다.

때때로 소스 데이터가 많아 테이블에 모든 데이터를 업로드할 때 응답이 느려질 수 있습니다. 이 경우 연결을 직접 사용하여 분석용 피벗 테이블을 생성할 수 있습니다.

우리가 만든 쿼리는 워크시트 오른쪽의 통합 문서 쿼리 창에 나타납니다. 쿼리를 선택하고 마우스 오른쪽 버튼을 클릭한 다음 "로드 대상"을 선택합니다.

이 통합 문서 쿼리 창이 없으면 데이터 탭의 [가져오기 및 변환] 그룹에서 “쿼리 표시”를 클릭하세요.

"다음으로 로드" 창에서 "연결만 생성"을 선택하고 로드를 클릭하세요.

이렇게 완성된 쿼리는 통합문서에 연결되어 저장됩니다. 그런 다음 삽입 탭 아래 [표] 그룹에서 "피벗 테이블"을 클릭하세요.

피벗 테이블을 만들기 전에 항상 첫 번째 테이블이나 범위를 선택합니다. 두 번째 "외부 데이터 소스 사용"을 선택하고 "연결 선택"을 클릭합니다.

기존 연결 창에서 방금 생성한 연결을 볼 수 있으며, 이를 선택하고 클릭하여 연 다음 피벗 테이블 삽입을 확인합니다.

이렇게 하면 이 연결을 기반으로 생성된 피벗 테이블을 볼 수 있는데, 이는 방금 테이블을 사용하여 생성한 피벗 테이블과 동일합니다.

이 방법을 사용하면 과도한 EXCEL 데이터로 인해 파일이 너무 커지는 것을 방지할 수 있습니다. 반면에 데이터 요약이 필요한 경우 테이블에 로드하는 단계를 줄일 수도 있습니다.

관련 학습 권장 사항: excel 튜토리얼

위 내용은 Excel 사례 공유: Powerquery를 사용하여 한 번의 클릭으로 여러 시트 병합의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!