Tải lên tệp kéo và thả liền mạch trong Excel bằng Power Query

Việc quản lý việc nhập dữ liệu lặp lại trong Excel có thể gặp rắc rối, đặc biệt khi cần xử lý nhiều tệp thường xuyên. May mắn thay, bằng cách kết hợp Power Query với hệ thống thư mục đơn giản, bạn có thể mô phỏng tải lên tệp bằng cách kéo và thả để tự động làm mới mô hình dữ liệu của mình mà không cần VBA hoặc macro phức tạp.

Trong hướng dẫn này, chúng tôi sẽ trình bày cách triển khai tải tệp lên bằng cách kéo và thả bằng Excel và Power Query.

Hãy tưởng tượng bạn đang quản lý các báo cáo bán hàng hàng tháng được thu thập hàng tháng từ các đại lý hiện trường khác nhau. Thay vì sao chép và dán dữ liệu theo cách thủ công, bạn có thể thực hiện tải tệp lên bằng cách kéo và thả. Sau đó, yêu cầu đại lý tải lên hoặc kéo tệp CSV của họ vào thư mục dùng chung.

Bước 1:Thiết lập thư mục kéo và thả của bạn

• Mở tệp Explorer.
• Tạo thư mục mới (ví dụ:Kéo và thả tệp).

C:\Users\YourName\Documents\Drag and Drop Files.

• Đặt một hoặc nhiều tệp mẫu vào đó. Tệp ví dụ:
  • Doanh số tháng 6.csv
  • Doanh số tháng 7.csv
• Thông báo cho người dùng kéo và thả tập tin vào thư mục này. Điều này có thể được chia sẻ hoặc ghim để truy cập nhanh.

Mỗi tệp chứa các hàng có dữ liệu bán hàng (sản phẩm, khu vực, số lượng và doanh thu). Đảm bảo tất cả các tệp đều tuân theo cấu trúc này:cùng một cột, cùng thứ tự và cùng định dạng CSV.

Bước 2:Sử dụng Power Query để kết nối với thư mục

• Mở Excel.
• Đi tới Dữ liệu tab>> chọn Nhận dữ liệu>> chọn Từ tệp>> chọn Từ thư mục .

• Điều hướng đến Thả và thả tệp thư mục>> nhấp vào Mở .

• Power Query sẽ hiển thị danh sách các tệp trong thư mục đó.
• Trong cửa sổ xem trước, nhấp vào Kết hợp và chuyển đổi dữ liệu hoặc Chuyển đổi dữ liệu .

Bước 3:Kết hợp các tệp trong Power Query

Power Query sẽ hiển thị danh sách các tệp trong thư mục.

• Nhấp vào Kết hợp>> chọn Kết hợp và chuyển đổi dữ liệu .
• A Kết hợp các tệp bản xem trước sẽ mở ra.
• Chọn đúng trang tính/bảng/dấu phân cách nếu cần (Power Query tự động phát hiện tiêu đề).
• Nhấp vào OK .

• Power Query tạo:
  • Tự động tạo chức năng tải tất cả các tệp.
  • Truy vấn chuyển đổi sắp xếp dữ liệu từ tất cả các tệp.
  • Thêm các bước để mở rộng và kết hợp chúng.

Bước 4:Chuyển đổi dữ liệu NẾU yêu cầu

• Bạn có thể áp dụng các phép biến đổi tại đây:
  • Đổi tên cột.
  • Thay đổi loại dữ liệu.
  • Lọc hàng.
  • Hợp nhất các truy vấn, v.v.
• Trong trình soạn thảo truy vấn:
  • Xóa các cột không cần thiết như Tiện ích mở rộng hoặc Source.Name , nếu không cần thiết.
  • Nhấp chuột phải>> chọn Xóa .
  • Đổi tên truy vấn (ví dụ:Tệp kết hợp).

• Sau khi hoàn tất, hãy nhấp vào Đóng và tải .

• Bây giờ, trang tính Excel của bạn sẽ tải tất cả các hàng từ tất cả các tệp trong thư mục.

Bước 4:Mô phỏng tải lên bằng cách kéo và thả

Để mô phỏng tải lên bằng cách kéo và thả:

• Chỉ cần thả tệp .csv mới vào thư mục Thả và Thả Tệp.
• Kéo và thả Bán hàng tháng 8.csv tập tin.

• Quay lại Excel.
• Nhấp chuột phải>> chọn Làm mới trên bàn.

• Các hàng mới từ tệp đã thả sẽ xuất hiện ngay lập tức.

Bước 5:Tự động làm mới

• Đi tới Dữ liệu tab>> chọn Truy vấn &Kết nối .
• Nhấp chuột phải vào Truy vấn >> chọn Thuộc tính .

• Bật:
  • Làm mới 10 phút một lần .
• Nhấp vào OK .

Giám sát các tệp hoặc định dạng cụ thể

Bạn chỉ có thể lọc các tệp .csv bằng cách sử dụng:

= Table.SelectRows(Source, each Text.EndsWith([Extension], ".csv"))

Hoặc loại trừ các tệp tạm thời/hệ thống bằng cách sử dụng:

= Table.SelectRows(Source, each not Text.StartsWith([Name], "~$"))

Mẹo khắc phục sự cố

• Tệp đang sử dụng: Đảm bảo tệp không mở trong ứng dụng khác khi làm mới.
• Không phát hiện được tiêu đề: Điều chỉnh bước “Kết hợp các tệp” trong Power Query để coi hàng đầu tiên là tiêu đề.
• Hiệu suất: Đối với các tệp rất lớn, hãy cân nhắc sử dụng “Mô hình dữ liệu” thay vì tải vào trang tính.

Kết luận

Bằng cách làm theo các bước trên, bạn có thể triển khai tải tệp lên bằng cách kéo và thả, điều này giúp Excel thông minh hơn và quy trình làm việc của bạn hiệu quả hơn nhiều. Chỉ với Power Query và thư mục được giám sát, bạn có thể xây dựng một hệ thống tải tệp nhẹ, thân thiện với người dùng trong Excel. Khả năng giám sát thư mục, nhập ngay lập tức và xử lý bất kỳ tệp mới nào mà bạn thêm của Power Query sẽ tiết kiệm vô số thời gian dành cho các tác vụ thủ công. Nó hoàn hảo cho các doanh nghiệp nhỏ, nhóm quản trị và hoạt động tại hiện trường cần kết hợp các bản cập nhật tệp thường xuyên một cách nhanh chóng và đáng tin cậy.