Tạo bảng với các tiêu đề khác nhau từ nhiều sách

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

Chúng tôi có một số tệp (trong ví dụ của chúng tôi - 4 tệp, trong trường hợp chung - bao nhiêu tùy thích) trong một thư mục Báo cáo:

Tạo bảng với các tiêu đề khác nhau từ nhiều sách

Bên trong, các tệp này trông như thế này:

Tạo bảng với các tiêu đề khác nhau từ nhiều sách

Trong đó:

  • Bảng dữ liệu chúng ta cần luôn được gọi là Hình ảnh, nhưng có thể ở bất kỳ đâu trong sổ làm việc.
  • Ngoài trang tính Hình ảnh Mỗi cuốn sách có thể có các trang tính khác.
  • Các bảng có dữ liệu có số lượng hàng khác nhau và có thể bắt đầu bằng một hàng khác trên trang tính.
  • Tên của các cột giống nhau trong các bảng khác nhau có thể khác nhau (ví dụ: Số lượng = Số lượng = Số lượng).
  • Các cột trong bảng có thể được sắp xếp theo một thứ tự khác nhau.

Nhiệm vụ: thu thập dữ liệu bán hàng từ tất cả các tệp từ trang tính Hình ảnh vào một bảng chung để sau đó xây dựng bản tóm tắt hoặc bất kỳ phân tích nào khác trên đó.

Bước 1. Chuẩn bị một thư mục tên cột

Điều đầu tiên cần làm là chuẩn bị một cuốn sách tham khảo với tất cả các tùy chọn có thể cho tên cột và cách giải thích chính xác của chúng:

Tạo bảng với các tiêu đề khác nhau từ nhiều sách

Chúng tôi chuyển đổi danh sách này thành một bảng "thông minh" động bằng cách sử dụng nút Định dạng dưới dạng bảng trên tab Trang Chủ (Trang chủ - Định dạng dưới dạng Bảng) hoặc phím tắt Ctrl+T và tải nó vào Power Query bằng lệnh Dữ liệu - Từ Bảng / Phạm vi (Dữ liệu - Từ Bảng / Phạm vi). Trong các phiên bản gần đây của Excel, nó đã được đổi tên thành Với những chiếc lá (Từ trang tính).

Trong cửa sổ trình chỉnh sửa truy vấn Power Query, theo truyền thống, chúng tôi xóa bước Loại đã thay đổi và thêm một bước mới thay vì bước đó bằng cách nhấp vào nút fxtrong thanh công thức (nếu nó không hiển thị, thì bạn có thể bật nó trên tab Đánh giá) và nhập công thức ở đó bằng ngôn ngữ Power Query cài sẵn M:

= Table.ToRows (Nguồn)

Lệnh này sẽ chuyển đổi lệnh đã tải ở bước trước nguồn bảng tham chiếu thành một danh sách bao gồm các danh sách lồng nhau (Danh sách), mỗi danh sách trong số đó, lần lượt, là một cặp giá trị Nó đã trở thành từ một dòng:

Tạo bảng với các tiêu đề khác nhau từ nhiều sách

Chúng tôi sẽ cần loại dữ liệu này sau một thời gian ngắn, khi đổi tên hàng loạt các tiêu đề từ tất cả các bảng đã tải.

Sau khi hoàn thành chuyển đổi, hãy chọn các lệnh Trang chủ - Đóng và tải - Đóng và tải vào… và loại hình nhập khẩu Chỉ cần tạo kết nối (Home - Close & Load - Close & Load to… - Chỉ tạo kết nối) và quay lại Excel.

Bước 2. Chúng tôi tải mọi thứ từ tất cả các tệp như nguyên trạng

Bây giờ chúng ta hãy tải nội dung của tất cả các tệp của chúng ta từ thư mục - bây giờ, như vậy. Chọn đội Dữ liệu - Lấy dữ liệu - Từ tệp - Từ thư mục (Dữ liệu - Lấy dữ liệu - Từ tệp - Từ thư mục) và sau đó là thư mục chứa sách nguồn của chúng tôi.

Trong cửa sổ xem trước, nhấp vào Chuyển đổi (Biến đổi) or Thay đổi (Chỉnh sửa):

Tạo bảng với các tiêu đề khác nhau từ nhiều sách

Và sau đó mở rộng nội dung của tất cả các tệp đã tải xuống (Nhị phân) nút có mũi tên đôi trong tiêu đề cột Nội dung:

Tạo bảng với các tiêu đề khác nhau từ nhiều sách

Power Query trên ví dụ về tệp đầu tiên (Vostok.xlsx) sẽ hỏi chúng tôi tên của trang tính mà chúng tôi muốn lấy từ mỗi sổ làm việc - chọn Hình ảnh và nhấn OK:

Tạo bảng với các tiêu đề khác nhau từ nhiều sách

Sau đó (trên thực tế), một số sự kiện không rõ ràng đối với người dùng sẽ xảy ra, hậu quả của chúng có thể nhìn thấy rõ ràng trong bảng điều khiển bên trái:

Tạo bảng với các tiêu đề khác nhau từ nhiều sách

  1. Power Query sẽ lấy tệp đầu tiên từ thư mục (chúng tôi sẽ có Vostok.xlsx - xem Ví dụ về tệp) làm ví dụ và nhập nội dung của nó bằng cách tạo một truy vấn Chuyển đổi tệp mẫu. Truy vấn này sẽ có một số bước đơn giản như nguồn (truy cập file) THÔNG TIN (lựa chọn trang tính) và có thể nâng cao các tiêu đề. Yêu cầu này chỉ có thể tải dữ liệu từ một tệp cụ thể Vostok.xlsx.
  2. Dựa trên yêu cầu này, hàm được liên kết với nó sẽ được tạo Chuyển đổi tệp (được biểu thị bằng một biểu tượng đặc trưng fx), trong đó tệp nguồn sẽ không còn là một hằng số nữa, mà là một giá trị biến - một tham số. Do đó, chức năng này có thể trích xuất dữ liệu từ bất kỳ cuốn sách nào mà chúng tôi đưa vào nó như một đối số.
  3. Hàm sẽ được áp dụng lần lượt cho từng tệp (Nhị phân) từ cột Nội dung - bước chịu trách nhiệm cho việc này Gọi chức năng tùy chỉnh trong truy vấn của chúng tôi có thêm một cột vào danh sách tệp Chuyển đổi tệp với kết quả nhập từ mỗi sổ làm việc:

    Tạo bảng với các tiêu đề khác nhau từ nhiều sách

  4. Các cột phụ bị loại bỏ.
  5. Nội dung của các bảng lồng nhau được mở rộng (bước Cột bảng mở rộng) - và chúng tôi thấy kết quả cuối cùng của việc thu thập dữ liệu từ tất cả các sách:

    Tạo bảng với các tiêu đề khác nhau từ nhiều sách

Bước 3. Chà nhám

Ảnh chụp màn hình trước đó cho thấy rõ ràng rằng việc lắp ráp trực tiếp “nguyên trạng” hóa ra có chất lượng kém:

  • Các cột được đảo ngược.
  • Nhiều dòng phụ (trống và không duy nhất).
  • Tiêu đề bảng không được coi là tiêu đề và được trộn với dữ liệu.

Bạn có thể khắc phục tất cả những vấn đề này rất dễ dàng - chỉ cần tinh chỉnh truy vấn Chuyển đổi tệp mẫu. Tất cả các điều chỉnh mà chúng tôi thực hiện đối với nó sẽ tự động rơi vào chức năng Chuyển đổi tệp được liên kết, có nghĩa là chúng sẽ được sử dụng sau này khi nhập dữ liệu từ mỗi tệp.

Bằng cách mở một yêu cầu Chuyển đổi tệp mẫu, thêm các bước để lọc các hàng không cần thiết (ví dụ: theo cột Column2) và nâng cao các tiêu đề bằng nút Sử dụng dòng đầu tiên làm tiêu đề (Sử dụng hàng đầu tiên làm tiêu đề). Bàn sẽ đẹp hơn nhiều.

Để các cột từ các tệp khác nhau sau này tự động khớp với nhau, chúng phải được đặt tên giống nhau. Bạn có thể thực hiện đổi tên hàng loạt như vậy theo một thư mục đã tạo trước đó với một dòng mã M. Hãy nhấn nút một lần nữa fx trong thanh công thức và thêm một hàm để thay đổi:

= Table.RenameColumns (# ”Tiêu đề Nâng cao”, Tiêu đề, Thiếu trường. Bỏ qua)

Tạo bảng với các tiêu đề khác nhau từ nhiều sách

Hàm này lấy bảng từ bước trước Tiêu đề nâng cao và đổi tên tất cả các cột trong đó theo danh sách tra cứu lồng nhau Tiêu đề. Đối số thứ ba ThiếuField.Bỏ qua là cần thiết để trên những tiêu đề có trong thư mục, nhưng không có trong bảng, lỗi không xảy ra.

Thực ra chỉ có vậy thôi.

Quay lại yêu cầu Báo cáo chúng ta sẽ thấy một bức tranh hoàn toàn khác - đẹp hơn nhiều so với bức trước:

Tạo bảng với các tiêu đề khác nhau từ nhiều sách

  • Power Query, Power Pivot, Power BI là gì và tại sao người dùng Excel cần chúng
  • 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

 

Bình luận