Nội dung
Nếu bạn đã bắt đầu sử dụng các công cụ của phần bổ trợ Power Query miễn phí trong Microsoft Excel, thì rất nhanh chóng bạn sẽ gặp phải một vấn đề chuyên môn cao, nhưng rất thường xuyên và khó chịu liên quan đến việc liên tục phá vỡ các liên kết đến dữ liệu nguồn. Bản chất của vấn đề là nếu trong truy vấn của bạn, bạn tham chiếu đến các tệp hoặc thư mục bên ngoài, thì Power Query sẽ mã hóa cứng đường dẫn tuyệt đối đến chúng trong văn bản truy vấn. Mọi thứ hoạt động tốt trên máy tính của bạn, nhưng nếu bạn quyết định gửi một tệp có yêu cầu cho đồng nghiệp của mình, thì họ sẽ thất vọng, bởi vì. họ có một đường dẫn khác đến dữ liệu nguồn trên máy tính của họ và truy vấn của chúng tôi sẽ không hoạt động.
Làm gì trong tình huống như vậy? Chúng ta hãy xem xét trường hợp này chi tiết hơn với ví dụ sau.
Công thức của vấn đề
Giả sử chúng ta có trong thư mục E:Báo cáo bán hàng tập tin nói dối 100 sản phẩm hàng đầu. Xls, là tệp tải lên từ cơ sở dữ liệu công ty hoặc hệ thống ERP của chúng tôi (1C, SAP, v.v.) Tệp này chứa thông tin về các mặt hàng phổ biến nhất và bên trong trông giống như sau:
Có thể rõ ràng ngay lập tức rằng hầu như không thể làm việc với nó trong Excel ở dạng này: các hàng trống qua một chứa dữ liệu, các ô được hợp nhất, cột bổ sung, tiêu đề nhiều cấp, v.v. sẽ gây trở ngại.
Do đó, bên cạnh tệp này trong cùng một thư mục, chúng tôi tạo một tệp mới khác Trình xử lý.xlsx, trong đó chúng tôi sẽ tạo một truy vấn Power Query sẽ tải dữ liệu xấu từ tệp tải lên nguồn 100 sản phẩm hàng đầu. Xlsvà sắp xếp chúng theo thứ tự:
Yêu cầu tệp bên ngoài
Mở tệp Trình xử lý.xlsx, chọn trên tab Ngày Lệnh Lấy dữ liệu - Từ tệp - Từ sổ làm việc Excel (Dữ liệu - Lấy dữ liệu - Từ tệp - Từ Excel), sau đó chỉ định vị trí của tệp nguồn và trang tính mà chúng ta cần. Dữ liệu đã chọn sẽ được tải vào trình chỉnh sửa Power Query:
Hãy đưa chúng trở lại bình thường:
- Xóa các dòng trống với Trang chủ - Xóa dòng - Xóa dòng trống (Trang chủ - Xóa hàng - Xóa hàng trống).
- Xóa 4 dòng trên cùng không cần thiết cho đến hết Trang chủ - Xóa hàng - Xóa hàng trên cùng (Trang chủ - Xóa hàng - Xóa hàng trên cùng).
- Nâng hàng đầu tiên lên tiêu đề bảng bằng nút Sử dụng dòng đầu tiên làm tiêu đề chuyển hướng Trang Chủ (Trang chủ - Sử dụng hàng đầu tiên làm tiêu đề).
- Tách bài viết gồm năm chữ số khỏi tên sản phẩm trong cột thứ hai bằng lệnh cột chia nhỏ chuyển hướng Chuyển đổi (Chuyển đổi - Cột tách).
- Xóa các cột không cần thiết và đổi tên các tiêu đề của những cột còn lại để hiển thị tốt hơn.
Kết quả là, chúng ta sẽ có được bức tranh sau đây, dễ chịu hơn nhiều:
Vẫn phải tải bảng được đánh dấu này trở lại trang tính trong tệp của chúng tôi Trình xử lý.xlsx đội đóng và tải xuống (Trang chủ - Đóng & Tải) chuyển hướng Trang Chủ:
Tìm đường dẫn đến một tệp trong một yêu cầu
Bây giờ, chúng ta hãy xem truy vấn của chúng ta trông như thế nào, bằng ngôn ngữ nội bộ được tích hợp trong Power Query với tên ngắn gọn là “M”. Để thực hiện việc này, hãy quay lại truy vấn của chúng tôi bằng cách nhấp đúp vào nó trong ngăn bên phải Yêu cầu và kết nối và trên tab Đánh giá chọn Trình chỉnh sửa nâng cao (Xem - Trình chỉnh sửa nâng cao):
Trong cửa sổ mở ra, dòng thứ hai ngay lập tức hiển thị một đường dẫn được mã hóa cứng đến tệp tải lên ban đầu của chúng tôi. Nếu chúng ta có thể thay thế chuỗi văn bản này bằng một tham số, biến hoặc một liên kết đến một ô trang tính Excel nơi đường dẫn này được viết trước, thì chúng ta có thể dễ dàng thay đổi nó sau này.
Thêm bảng thông minh với đường dẫn tệp
Hãy đóng Power Query ngay bây giờ và quay lại tệp của chúng tôi Trình xử lý.xlsx. Hãy thêm một trang tính trống mới và tạo một bảng nhỏ "thông minh" trên đó, trong ô duy nhất mà đường dẫn đầy đủ đến tệp dữ liệu nguồn của chúng ta sẽ được ghi:
Để tạo bảng thông minh từ một phạm vi thông thường, bạn có thể sử dụng phím tắt Ctrl+T hoặc nút Định dạng dưới dạng bảng chuyển hướng Trang Chủ (Trang chủ - Định dạng dưới dạng Bảng). Tiêu đề cột (ô A1) hoàn toàn có thể là bất kỳ thứ gì. Cũng lưu ý rằng để rõ ràng, tôi đã đặt tên cho bảng Thông số chuyển hướng xây dựng (Thiết kế).
Tất nhiên, việc sao chép đường dẫn từ Explorer hoặc thậm chí nhập đường dẫn theo cách thủ công là không quá khó, nhưng tốt nhất là bạn nên giảm thiểu yếu tố con người và tự động xác định đường dẫn, nếu có thể. Điều này có thể được thực hiện bằng cách sử dụng hàm trang tính Excel tiêu chuẩn BÁN (TỦ), có thể cung cấp một loạt thông tin hữu ích về ô được chỉ định làm đối số - bao gồm cả đường dẫn đến tệp hiện tại:
Nếu chúng ta giả định rằng tệp dữ liệu nguồn luôn nằm trong cùng một thư mục với Bộ xử lý của chúng ta, thì đường dẫn chúng ta cần có thể được tạo theo công thức sau:
= LEFT (CELL (“filename”); FIND (“[“; CELL (“filename”)) - 1) & ”100 products.xls hàng đầu”
hoặc phiên bản tiếng Anh:
= LEFT (CELL («tên tệp»); TÌM («[«; CELL («tên tệp»)) - 1) & »Топ-100 товаров.xls»
… Chức năng ở đâu LEVSIMV (TRÁI) lấy một đoạn văn bản từ liên kết đầy đủ lên đến dấu ngoặc vuông mở (tức là đường dẫn đến thư mục hiện tại), sau đó tên và phần mở rộng của tệp dữ liệu nguồn của chúng tôi được dán vào nó.
Tham số hóa đường dẫn trong truy vấn
Lần chạm cuối cùng và quan trọng nhất vẫn là - ghi đường dẫn đến tệp nguồn trong yêu cầu 100 sản phẩm hàng đầu. Xls, tham chiếu đến ô A2 của bảng "thông minh" đã tạo của chúng tôi Thông số.
Để thực hiện việc này, hãy quay lại truy vấn Power Query và mở lại Trình chỉnh sửa nâng cao chuyển hướng Đánh giá (Xem - Trình chỉnh sửa nâng cao). Thay vì một chuỗi văn bản-đường dẫn trong dấu ngoặc kép “E: Báo cáo bán hàng 100 sản phẩm hàng đầu.xlsx” Hãy giới thiệu cấu trúc sau:
Excel.CurrentWorkbook (){[Tên = ”Cài đặt”]} [Nội dung]0 {}[Đường dẫn đến dữ liệu nguồn]
Hãy xem nó bao gồm những gì:
- Excel.CurrentWorkbook () là một chức năng của ngôn ngữ M để truy cập nội dung của tệp hiện tại
- {[Tên = ”Cài đặt”]} [Nội dung] - đây là một tham số sàng lọc cho chức năng trước đó, cho biết rằng chúng tôi muốn lấy nội dung của bảng "thông minh" Thông số
- [Đường dẫn đến dữ liệu nguồn] là tên của cột trong bảng Thông sốmà chúng tôi đề cập đến
- 0 {} là số hàng trong bảng Thông sốtừ đó chúng tôi muốn lấy dữ liệu. Nắp không được tính và việc đánh số bắt đầu từ số không, không phải từ một.
Đó là tất cả, trên thực tế.
Nó vẫn còn để nhấp vào Kết thúc và kiểm tra cách thức hoạt động của yêu cầu của chúng tôi. Bây giờ, khi gửi toàn bộ thư mục có cả hai tệp bên trong sang một PC khác, yêu cầu sẽ vẫn hoạt động và tự động xác định đường dẫn đến dữ liệu.
- Power Query là gì và tại sao lại cần nó khi làm việc trong Microsoft Excel
- Cách nhập đoạn văn bản nổi vào Power Query
- Thiết kế lại một bảng biểu đồ XNUMXD thành một bảng phẳng với Power Query