여러 책의 다른 머리글로 표 만들기

문제의 공식화

하나의 폴더에 여러 파일(이 예에서는 4개, 일반적인 경우 원하는 만큼)이 있습니다. 보고서:

내부에서 이러한 파일은 다음과 같습니다.

여기서:

• 우리가 필요로 하는 데이터 시트는 항상 사진, 하지만 통합 문서의 아무 곳에나 있을 수 있습니다.
  
• 시트 너머 사진 각 책에는 다른 시트가 있을 수 있습니다.
• 데이터가 있는 테이블의 행 수가 다르며 워크시트에서 다른 행으로 시작할 수 있습니다.
• 다른 테이블에 있는 동일한 열의 이름은 다를 수 있습니다(예: 수량 = 수량 = 수량).
• 테이블의 열은 다른 순서로 정렬할 수 있습니다.

작업: 시트의 모든 파일에서 판매 데이터 수집 사진 이후에 요약 또는 기타 분석을 작성하기 위해 하나의 공통 테이블에 저장합니다.

1단계. 열 이름 디렉토리 준비

가장 먼저 할 일은 열 이름과 올바른 해석에 대한 가능한 모든 옵션이 포함된 참고서를 준비하는 것입니다.

이 목록을 탭의 표로 형식 지정 버튼을 사용하여 동적 "스마트" 표로 변환합니다. 홈 (홈 — 표 형식) 또는 키보드 단축키 Ctrl 키+T 명령을 사용하여 파워 쿼리에 로드합니다. 데이터 – 테이블/범위에서 (데이터 — 테이블/범위에서). 최신 버전의 Excel에서는 다음으로 이름이 변경되었습니다. 잎으로 (시트에서).

파워 쿼리 쿼리 편집기 창에서는 일반적으로 단계를 삭제합니다. 변경된 유형 버튼을 클릭하여 대신 새 단계를 추가하십시오. f_x수식 입력줄에서(보이지 않으면 탭에서 활성화할 수 있습니다. 검토) 기본 제공 파워 쿼리 언어 M으로 수식을 입력합니다.

=Table.ToRows(소스)

이 명령은 이전 단계에서 로드된 것을 변환합니다. 출처 중첩된 목록(목록)으로 구성된 목록으로 참조 테이블을 참조하며, 각 목록은 차례로 값 쌍입니다. 되었다 한 줄에서 :

로드된 모든 테이블에서 헤더의 이름을 대량으로 변경할 때 조금 후에 이러한 유형의 데이터가 필요합니다.

변환을 완료한 후 명령을 선택합니다. 홈 — 닫기 및 로드 — 닫기 및 로드… 및 수입 유형 연결만 만드세요 (Home — Close&Load — Close&Load to… — 연결만 생성) 그리고 엑셀로 돌아갑니다.

2단계. 모든 파일의 모든 것을 있는 그대로 로드합니다.

이제 폴더에서 모든 파일의 내용을 로드해 보겠습니다. 지금은 그대로입니다. 팀 선택 데이터 – 데이터 가져오기 – 파일에서 – 폴더에서 (데이터 — 데이터 가져오기 — 파일에서 — 폴더에서) 그런 다음 원본 책이 있는 폴더입니다.

미리보기 창에서 개 심자 (변환) or 변화 (수정):

그런 다음 다운로드한 모든 파일의 내용을 확장합니다. (바이너리) 열 머리글에 이중 화살표가 있는 버튼 내용:

첫 번째 파일의 예에 대한 파워 쿼리(보스토크.xlsx) 각 통합 문서에서 가져오려는 시트의 이름을 묻습니다. 사진 확인을 누릅니다.

그 후에 (사실) 사용자에게 명확하지 않은 몇 가지 이벤트가 발생하며 그 결과는 왼쪽 패널에 명확하게 표시됩니다.

1. 파워 쿼리는 폴더에서 첫 번째 파일을 가져옵니다. 보스토크.xlsx — 참조 파일 예) 예를 들어 쿼리를 만들어 콘텐츠를 가져옵니다. 샘플 파일 변환. 이 쿼리에는 다음과 같은 몇 가지 간단한 단계가 있습니다. 출처 (파일 액세스) 카테고리 (시트 선택) 및 아마도 제목을 올릴 수 있습니다. 이 요청은 하나의 특정 파일에서만 데이터를 로드할 수 있습니다. 보스토크.xlsx.
   
2. 이 요청을 기반으로 연결된 기능이 생성됩니다. 파일 변환 (특징 아이콘으로 표시 f_x), 여기서 소스 파일은 더 이상 상수가 아니라 변수 값인 매개변수가 됩니다. 따라서 이 함수는 우리가 인수로 삽입한 책에서 데이터를 추출할 수 있습니다.
   
3. 해당 컬럼의 각 파일(Binary)에 차례로 함수가 적용됩니다. 내용 – 단계가 이에 대한 책임이 있습니다. 커스텀 함수 호출 파일 목록에 열을 추가하는 쿼리에서 파일 변환 각 통합 문서의 가져오기 결과:

   

4. 추가 열이 제거됩니다.
5. 중첩 테이블의 내용이 확장됩니다(단계 확장 테이블 열) – 모든 책에서 데이터 수집의 최종 결과를 봅니다.

   
   

3단계. 샌딩

이전 스크린샷은 "있는 그대로" 직접 조립이 품질이 좋지 않음을 분명히 보여줍니다.

• 열이 반전됩니다.
• 많은 추가 줄(비어 있을 뿐만 아니라).
• 테이블 헤더는 헤더로 인식되지 않으며 데이터와 혼합됩니다.
  

이 모든 문제를 매우 쉽게 해결할 수 있습니다. 샘플 파일 변환 쿼리를 조정하기만 하면 됩니다. 이에 대한 모든 조정은 연결된 파일 변환 기능에 자동으로 적용되므로 나중에 각 파일에서 데이터를 가져올 때 사용됩니다.

요청을 열면 샘플 파일 변환, 불필요한 행을 필터링하는 단계 추가(예: 열 Column2) 버튼으로 제목 올리기 첫 번째 줄을 헤더로 사용 (첫 번째 행을 헤더로 사용). 테이블이 훨씬 좋아 보일 것입니다.

다른 파일의 열이 나중에 자동으로 서로 맞도록 하려면 이름이 동일해야 합니다. 한 줄의 M 코드로 이전에 생성된 디렉토리에 따라 이러한 대량 이름 변경을 수행할 수 있습니다. 다시 버튼을 눌러보자 f_x 수식 입력줄에서 변경할 함수를 추가합니다.

= Table.RenameColumns(#"높은 머리글", 머리글, MissingField.Ignore)

이 함수는 이전 단계의 테이블을 가져옵니다. 높은 헤더 중첩된 조회 목록에 따라 모든 열의 이름을 바꿉니다. 헤드 라인. 세 번째 인수 누락된 필드.무시 디렉토리에는 있지만 테이블에는 없는 표제에서 오류가 발생하지 않도록 하기 위해 필요합니다.

사실 그게 다야.

요청으로 돌아가기 보고서 우리는 완전히 다른 그림을 보게 될 것입니다 – 이전 그림보다 훨씬 더 멋집니다:

• Power Query, Power Pivot, Power BI란 무엇이며 Excel 사용자에게 필요한 이유
  
• 주어진 폴더의 모든 파일에서 데이터 수집
• 책의 모든 시트에서 데이터를 하나의 테이블로 수집