Tạo bảng nhiều định dạng từ một trang tính trong Power Query

Công thức của vấn đề

Là dữ liệu đầu vào, chúng tôi có một tệp Excel, trong đó một trong các trang tính chứa một số bảng với dữ liệu bán hàng có dạng sau:

Lưu ý rằng:

• Các bảng có kích thước khác nhau và với các tập hợp sản phẩm và khu vực khác nhau trong các hàng và cột mà không cần phân loại.
• Các dòng trống có thể được chèn giữa các bảng.
• Số lượng bàn có thể là bất kỳ.
  

Hai giả định quan trọng. Nó được cho rằng:

• Phía trên mỗi bảng, trong cột đầu tiên, có tên của người quản lý có doanh số mà bảng minh họa (Ivanov, Petrov, Sidorov, v.v.)
  
• Tên của hàng hóa và khu vực trong tất cả các bảng được viết theo cùng một cách - với độ chính xác của từng trường hợp.
  

Mục tiêu cuối cùng là thu thập dữ liệu từ tất cả các bảng vào một bảng được chuẩn hóa phẳng, thuận tiện cho việc phân tích tiếp theo và xây dựng một bản tóm tắt, tức là trong bảng này:

Bước 1. Kết nối với tệp

Hãy tạo một tệp Excel trống mới và chọn nó trên tab Ngày Lệnh Lấy dữ liệu - Từ tệp - Từ sách (Dữ liệu - Từ tệp - Từ sổ làm việc). Chỉ định vị trí của tệp nguồn với dữ liệu bán hàng và sau đó trong cửa sổ điều hướng, chọn trang tính chúng ta cần và nhấp vào nút Chuyển đổi dữ liệu (Chuyển đổi dữ liệu):

Do đó, tất cả dữ liệu từ nó sẽ được tải vào trình soạn thảo Power Query:

Bước 2. Dọn sạch thùng rác

Xóa các bước được tạo tự động loại sửa đổi (Loại đã thay đổi) и Tiêu đề nâng cao (Tiêu đề được Quảng cáo) và loại bỏ các dòng trống và dòng có tổng bằng cách sử dụng bộ lọc vô giá trị и TOTAL bởi cột đầu tiên. Kết quả là, chúng tôi nhận được hình ảnh sau:

Bước 3. Thêm người quản lý

Để sau này hiểu được doanh số của ai, cần phải thêm một cột vào bảng của chúng ta, trong đó mỗi hàng sẽ có một họ tương ứng. Đối với điều này:

1. Hãy thêm một cột bổ trợ với số dòng bằng lệnh Thêm cột - Cột chỉ mục - Từ 0 (Thêm cột - Cột chỉ mục - Từ 0).

2. Thêm một cột có công thức bằng lệnh Thêm một cột - Cột tùy chỉnh (Thêm cột - Cột tùy chỉnh) và giới thiệu việc xây dựng sau đây ở đó:

Logic của công thức này rất đơn giản - nếu giá trị của ô tiếp theo trong cột đầu tiên là "Sản phẩm", thì điều này có nghĩa là chúng tôi đã bắt đầu một bảng mới, vì vậy chúng tôi hiển thị giá trị của ô trước đó với tên của người quản lý. Nếu không, chúng tôi không hiển thị bất cứ điều gì, tức là null.

Để lấy ô cha có họ, trước tiên chúng ta tham khảo bảng từ bước trước # ”Đã thêm chỉ mục”, và sau đó chỉ định tên của cột chúng ta cần [Cột1] trong dấu ngoặc vuông và số ô trong cột đó trong dấu ngoặc nhọn. Số ô sẽ nhỏ hơn ô hiện tại một số, chúng tôi lấy từ cột Chỉ số, Tương ứng.

3. Nó vẫn còn để điền vào các ô trống với vô giá trị tên từ các ô cao hơn bằng lệnh Chuyển đổi - Điền vào - Xuống (Chuyển đổi - Tô - Xuống) và xóa cột không còn cần thiết với các chỉ số và hàng có họ trong cột đầu tiên. Kết quả là, chúng tôi nhận được:

Bước 4. Nhóm người quản lý thành các bảng riêng biệt

Bước tiếp theo là nhóm các hàng cho mỗi người quản lý thành các bảng riêng biệt. Để thực hiện việc này, trên tab Chuyển đổi, sử dụng lệnh Nhóm theo (Transform - Group By) và trong cửa sổ mở ra, hãy chọn cột Trình quản lý và thao tác Tất cả các hàng (Tất cả các hàng) để thu thập dữ liệu một cách đơn giản mà không cần áp dụng bất kỳ chức năng tổng hợp nào đối với chúng (tổng, trung bình, v.v.). P.):

Do đó, chúng tôi nhận được các bảng riêng biệt cho từng người quản lý:

Bước 5: Chuyển đổi các bảng lồng nhau

Bây giờ chúng ta cung cấp các bảng nằm trong mỗi ô của cột kết quả Tất cả dữ liệu trong hình dạng khá.

Đầu tiên, xóa một cột không còn cần thiết trong mỗi bảng Quản Lý. Chúng tôi sử dụng lại Cột tùy chỉnh chuyển hướng Chuyển đổi (Chuyển đổi - Cột tùy chỉnh) và công thức sau:

Sau đó, với một cột được tính toán khác, chúng tôi nâng hàng đầu tiên trong mỗi bảng lên các tiêu đề:

Và cuối cùng, chúng tôi thực hiện chuyển đổi chính - mở từng bảng bằng cách sử dụng hàm M Table.UnpivotOtherColumns:

Tên của các vùng từ tiêu đề sẽ đi vào một cột mới và chúng ta sẽ nhận được một bảng hẹp hơn, nhưng đồng thời, một bảng được chuẩn hóa dài hơn. Các ô trống với vô giá trị bị bỏ qua.

Loại bỏ các cột trung gian không cần thiết, chúng tôi có:

Bước 6 Mở rộng các bảng lồng nhau

Nó vẫn để mở rộng tất cả các bảng lồng nhau đã chuẩn hóa thành một danh sách duy nhất bằng cách sử dụng nút có mũi tên đôi trong tiêu đề cột:

… Và cuối cùng chúng tôi đã đạt được những gì chúng tôi muốn:

Bạn có thể xuất bảng kết quả trở lại Excel bằng lệnh Trang chủ - Đóng và tải - Đóng và tải vào… (Trang chủ - Đóng & Tải - Đóng & Tải vào…).

• Tạo bảng với các tiêu đề khác nhau từ nhiều sách
• Thu thập dữ liệu từ tất cả các tệp trong một thư mục nhất định
• Thu thập dữ liệu từ tất cả các trang của cuốn sách vào một bảng