문제의 공식화
입력 데이터로 시트 중 하나에 다음 형식의 판매 데이터가 포함된 여러 테이블이 포함된 Excel 파일이 있습니다.
참고 사항 :
- 정렬 없이 행과 열에 다양한 크기의 제품과 지역 세트가 있는 테이블.
- 테이블 사이에 빈 줄을 삽입할 수 있습니다.
- 테이블 수는 얼마든지 가능합니다.
두 가지 중요한 가정. 다음과 같이 가정합니다.
- 각 테이블 위의 첫 번째 열에는 해당 테이블이 설명하는 판매 관리자의 이름(Ivanov, Petrov, Sidorov 등)이 있습니다.
- 모든 테이블에 있는 상품 및 지역 이름은 대소문자를 정확하게 일치시키는 동일한 방식으로 작성됩니다.
궁극적인 목표는 모든 테이블의 데이터를 하나의 평면 정규화 테이블로 수집하여 후속 분석에 편리하고 요약을 작성하는 것입니다. 즉, 다음과 같습니다.
1단계. 파일에 연결
빈 Excel 파일을 새로 만들고 탭에서 선택합시다. Data 명령 데이터 가져오기 – 파일에서 – 책에서 (데이터 — 파일에서 — 통합 문서에서). 판매 데이터가 있는 소스 파일의 위치를 지정한 다음 네비게이터 창에서 필요한 시트를 선택하고 버튼을 클릭합니다. 데이터 변환 (데이터 변환):
결과적으로 모든 데이터는 파워 쿼리 편집기에 로드되어야 합니다.
2단계. 쓰레기 정리
자동으로 생성된 단계 삭제 수정된 유형 (변경된 유형) и 높은 헤더 (프로모션 헤더) 필터를 사용하여 합계가 있는 빈 줄과 줄을 제거합니다. null로 и TOTAL 첫 번째 열로. 결과적으로 다음 그림을 얻습니다.
3단계. 관리자 추가
나중에 누구의 판매가 있는지 이해하려면 테이블에 열을 추가해야 합니다. 각 행에는 해당 성이 있습니다. 이를 위해:
1. 명령을 사용하여 행 번호가 있는 보조 열을 추가해 보겠습니다. 열 추가 – 인덱스 열 – 0부터 (열 추가 — 인덱스 열 — 0부터).
2. 명령을 사용하여 수식이 있는 열 추가 열 추가 – 사용자 정의 열 (열 추가 — 사용자 정의 열) 거기에 다음 구성을 소개합니다.
이 공식의 논리는 간단합니다. 첫 번째 열의 다음 셀 값이 "제품"이면 새 테이블의 시작 부분을 우연히 발견했다는 의미이므로 이전 셀의 값을 다음과 같이 표시합니다. 관리자의 이름. 그렇지 않으면 아무 것도 표시하지 않습니다. 즉, null입니다.
성을 가진 부모 셀을 얻으려면 먼저 이전 단계의 테이블을 참조합니다. #"색인 추가", 필요한 열의 이름을 지정하십시오 [컬럼1] 대괄호로 묶고 해당 열의 셀 번호를 중괄호로 묶습니다. 셀 번호는 열에서 가져온 현재 번호보다 하나 작습니다. 색인각각.
3. 빈 셀을 채우는 것이 남아 있습니다. null로 명령을 사용하여 상위 셀의 이름 변환 – 채우기 – 아래로 (변환 - 채우기 - 아래로) 인덱스가 있는 더 이상 필요하지 않은 열과 첫 번째 열에 성이 있는 행을 삭제합니다. 결과적으로 다음을 얻습니다.
4단계. 관리자별로 별도의 테이블로 그룹화
다음 단계는 각 관리자의 행을 별도의 테이블로 그룹화하는 것입니다. 이렇게 하려면 변환 탭에서 그룹화 기준 명령(변환 – 그룹화 기준)을 사용하고 열리는 창에서 관리자 열과 모든 행(모든 행) 작업을 선택하여 집계 기능을 적용하지 않고 단순히 데이터를 수집합니다. (합계, 평균 등). 피.):
결과적으로 각 관리자에 대해 별도의 테이블을 얻습니다.
5단계: 중첩 테이블 변환
이제 결과 열의 각 셀에 있는 테이블을 제공합니다. 모든 데이터 괜찮은 모양으로.
먼저 각 테이블에서 더 이상 필요하지 않은 열을 삭제합니다. 매니저. 우리는 다시 사용 맞춤 항목 탭 변환 (변환 — 사용자 정의 열) 그리고 다음 공식 :
그런 다음 다른 계산된 열을 사용하여 각 테이블의 첫 번째 행을 제목으로 올립니다.
마지막으로 M 함수를 사용하여 각 테이블을 펼치는 주요 변환을 수행합니다. Table.UnpivotOtherColumns:
헤더의 영역 이름은 새 열로 이동하고 더 좁지만 동시에 더 긴 정규화된 테이블을 얻습니다. 빈 셀 null로 무시됩니다.
불필요한 중간 열을 제거하면 다음이 제공됩니다.
6단계 중첩 테이블 확장
열 머리글에 이중 화살표가 있는 버튼을 사용하여 모든 정규화된 중첩 테이블을 단일 목록으로 확장해야 합니다.
... 그리고 마침내 우리가 원하는 것을 얻었습니다.
명령을 사용하여 결과 테이블을 Excel로 다시 내보낼 수 있습니다. 홈 — 닫기 및 로드 — 닫기 및 로드… (홈 — 닫기&로드 — 닫기&로드…).
- 여러 책의 다른 머리글로 표 만들기
- 주어진 폴더의 모든 파일에서 데이터 수집
- 책의 모든 시트에서 데이터를 하나의 테이블로 수집