실용적인 Excel 기술 공유: 파워 쿼리를 사용하여 폴더의 통합 문서 병합

이전에 통합 문서의 워크시트를 요약하기 위해 EXCEL의 새로운 기능인 파워 쿼리를 사용하는 방법을 소개했지만, 오늘은 파워 쿼리의 기능이 그 이상입니다. 파워 쿼리는 폴더의 통합 문서를 병합합니다.

바탕화면의 "Sales" 폴더에는 아래와 같이 4개 지역의 매출 데이터가 있습니다. 각 통합 문서의 제목 이름은 일관되며 순서는 다를 수 있습니다. 각 통합 문서의 도시 열 값은 통합 문서 이름으로, 나중에 병합 효과를 볼 때 편리합니다.

작업은 다음과 같습니다.

폴더에서 파일을 닫고 새 통합 문서를 클릭하고 데이터 옵션을 클릭하세요. 카드 아래의 [가져오기 및 변환] 그룹 "새 쿼리" --- "파일에서" --- "폴더에서".

폴더 경로를 입력하거나 폴더 위치를 찾아 선택하고 확인을 클릭할 수 있습니다.

이 인터페이스는 폴더에 있는 모든 통합 문서를 나열합니다. 편집을 클릭하세요.

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

앞서 소개한 것처럼 "콘텐츠" 열의 녹색 글꼴은 이 셀에 파일이 포함되어 있음을 의미합니다. 내용을 미리 보려면 셀을 클릭하세요. (참고: 셀의 내용을 미리 볼 때는 텍스트가 아닌 셀의 빈 공간에 마우스를 올려야 합니다. 텍스트를 클릭하면 셀의 파일이 바로 열립니다) 파일이 직접 추출되기 때문에 폴더에서 이진 형식으로 이동하므로 이진 형식의 통합 문서가 아래 미리 보기 창에 나타납니다.

바이너리 파일로서의 바이너리는 테이블에 표시할 수 없으므로 우리가 해야 할 일은 이를 테이블 형식으로 변경하고 데이터를 테이블로 확장하는 것입니다. 다른 불필요한 열을 먼저 삭제하세요.

"콘텐츠" 열을 선택하고 홈 탭 아래 [열 관리] 그룹에서 "열 삭제" - "다른 열 삭제"를 클릭하세요.

이렇게 하면 "콘텐츠" 열을 제외한 다른 모든 열이 삭제됩니다.

이진 파일을 일반 테이블로 변환하려면 파워 쿼리의 특수 프로그래밍 언어인 M 언어를 사용해야 합니다. 여기서는 일반적으로 사용되는 기능을 소개하겠습니다.

열 추가 옵션 아래 [일반] 그룹에서 "사용자 정의 열"을 클릭하세요.

사용자 정의 열 창에서 "사용자 정의 열 수식"에 =Excel.Workbook([Content],true)를 입력합니다. 여기서 "[Content]"는 사용 가능한 열에서 "Content"를 클릭할 수 있습니다. 오른쪽 을 클릭한 후 오른쪽 하단을 클릭하여 삽입하세요(참고: 수식의 대문자는 틀리지 않아야 합니다).

수식 분석:

Excel.Workbook

기능: Excel 통합 문서에서 워크시트의 기록을 반환합니다.

매개변수: Excel.Workbook(워크북은 바이너리, 선택적 useHeaders는 null 허용 논리, optiondelayTypes는 null 허용 logic) as table

이 함수는 테이블을 반환합니다. 첫 번째 매개변수 통합 문서는 바이너리 형식이고 두 번째 매개변수는 선택적 매개변수의 논리값입니다. true는 원본 테이블의 제목이 사용된다는 의미입니다. 새 테이블의 제목을 사용하려면 기본값은 false입니다. 새 열 이름이 원래 워크시트 제목을 대체합니다. 세 번째 매개변수에 대해서는 걱정하지 마세요.

여기서는 여전히 양식의 원래 제목을 사용하므로 사실대로 입력하세요. 이렇게 하면 제목의 첫 번째 줄을 개선하는 후속 단계가 절약됩니다.

새 열이 성공적으로 추가되었습니다. 아래에 표시된 것은 테이블 스타일 통합 문서입니다. 이는 테이블로 직접 확장될 수 있습니다.

"데이터" 열은 테이블의 데이터를 포함하여 테이블 형식으로 테이블을 표시합니다. 여기서는 이 열만 추출하면 됩니다. 맞춤 열의 오른쪽 상단에 있는 확장 버튼을 클릭하고 확장된 열 '데이터'를 선택한 다음 '원래 열 이름을 접두사로 사용'을 선택 취소하세요.

열 이름은 "Data"가 됩니다. 이때 "Data"에서 데이터를 미리 볼 수 있으며, 아래에 나타나는 것은 테이블의 원본 데이터입니다. 그런 다음 아래의 모든 데이터를 추출하십시오.

마찬가지로 사용자 정의 열의 오른쪽 상단에 있는 확장 버튼을 클릭하고 모든 열을 확장하도록 선택하고 "원래 열 이름을 접두사로 사용"을 선택하지 마세요.

이런 방식으로 레이어별로 드릴링하여 워크시트의 데이터를 얻습니다.

마지막으로 "콘텐츠" 열을 삭제하세요. "콘텐츠" 열을 선택하고 마우스 오른쪽 버튼을 클릭하여 삭제합니다.

마지막으로 이 표를 양식에 업로드하세요.

홈 탭 아래 [닫기] 그룹에서 "닫고 업로드"를 클릭하세요.

이런 식으로 데이터가 워크시트에 요약됩니다.

"도시" 열의 필터 버튼을 클릭하면 4개의 통합 문서에 있는 데이터가 모두 표에 있는 것을 볼 수 있습니다.

폴더에 통합 문서가 하나 더 있으면 어떻게 되나요? 이 폴더에 새 통합 문서 "Xi'an"을 넣어보세요.

방금 통계를 작성한 테이블로 돌아가서 데이터 탭 아래 [연결] 그룹에서 "모두 새로 고침"을 클릭하세요.

이제 도시 열에 "Xi'an"이 있고 이 새 통합 문서를 나타내는 데이터가 추가되었습니다.

요약: 파워 쿼리는 폴더를 병합합니다. 각 워크시트의 제목이 동일하다면 폴더에 있는 통합 문서 수에 관계없이 병합 및 요약이 가능합니다. 그리고 모든 데이터 변경 사항은 모두 새로 고침을 통해 한 번의 클릭으로 업데이트될 수 있습니다.

Power Query는 내장된 M 언어 및 작업 기록기와 결합된 간단한 그래픽 작업을 통해 더 많은 데이터를 통합 방식으로 작업하고 데이터 처리 및 최적화를 빠르게 완료하는 데 도움이 되는 강력한 EXCEL 데이터 분석 도구입니다. 더욱이 VBA보다 시작하기가 더 빠르고 작동하기 쉬우며 그래픽 작업은 대부분의 요구 사항을 충족할 수 있습니다. 여러분, 빨리 배워보세요!

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

위 내용은 실용적인 Excel 기술 공유: 파워 쿼리를 사용하여 폴더의 통합 문서 병합의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!