Quản lý nhập dữ liệu lặp lại trong Excel có thể là rắc rối, đặc biệt khi nhiều tệp cần được xử lý thường xuyên. May mắn thay, bằng cách kết hợp Power Query với một hệ thống thư mục đơn giản, bạn có thể mô phỏng tải lên file kéo-thả tự động làm mới mô hình dữ liệu của bạn 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ẽ chỉ cách triển khai tải lên file kéo-thả sử dụng Excel và Power Query.
Hãy tưởng tượng bạn đang quản lý báo cáo bán hàng hàng tháng được thu thập hàng tháng từ các nhân viên hiện trường khác nhau. Thay vì sao chép và dán dữ liệu thủ công, bạn có thể triển khai tải lên file kéo-thả. Sau đó, yêu cầu nhân viên tải lên hoặc kéo thả CSV của họ vào thư mục chung.
Bước 1: Thiết Lập Thư Mục Kéo-Thả Của Bạn
- Mở File Explorer.
- Tạo thư mục mới (ví dụ: Drag and Drop File).
C:\Users\YourName\Documents\Drag and Drop Files.
- Đặt một hoặc nhiều tệp mẫu ở đó. Ví dụ tệp:
- Sales June.csv
- Sales July.csv
- Thông báo cho người dùng kéo và thả tệp vào thư mục này. Thư mục này có thể được chia sẻ hoặc ghim vào quick access.
Mỗi tệp chứa hàng với 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 tuân theo cấu trúc này: cùng 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 Đến Thư Mục
- Mở Excel.
- Chuyển đến tab Data >> chọn Get Data >> chọn From File >> chọn From Folder.
- Điều hướng đến thư mục Drop and Drop Files >> nhấp Open.
- Power Query sẽ hiển thị danh sách tệp trong thư mục đó.
- Trong cửa sổ xem trước, nhấp Combine & Transform Data hoặc Transform Data.
Bước 3: Kết Hợp Tệp Trong Power Query
Power Query sẽ hiển thị danh sách tệp trong thư mục.
- Nhấp Combine >> chọn Combine & Transform Data.
- Một xem trước Combine Files sẽ mở ra.
- Chọn sheet/bảng/delimiter đúng như cần thiết (Power Query tự động phát hiện tiêu đề).
- Nhấp OK.
- Power Query tạo:
- Tự động tạo hàm để tải tất cả tệp.
- Truy vấn biến đổi xếp chồng dữ liệu từ tất cả tệp.
- Thêm các bước để mở rộng và kết hợp chúng.
Bước 4: Biến Dổi Dữ Liệu Nếu Cần
- Bạn có thể áp dụng biến đổi ở đây:
- Đổi tên cột.
- Thay đổi kiểu dữ liệu.
- Lọc hàng.
- Merge queries, v.v.
- Trong trình soạn thảo truy vấn:
- Xóa cột không cần thiết như Extension hoặc Source.Name, nếu không cần.
- Nhấp chuột phải >> chọn Remove.
- Đổi tên truy vấn (ví dụ: CombinedFiles).
- Khi xong, nhấp Close & Load.
- Bây giờ, sheet Excel của bạn sẽ tải tất cả hàng từ tất cả tệp trong thư mục.
Bước 4: Mô Phỏng Tải Lên Kéo-Thả
Để mô phỏng tải lên kéo-thả:
- Chỉ cần thả một tệp .csv mới vào thư mục Drop and Drop Files.
- Kéo và thả tệp Sales August.csv.
- Quay lại Excel.
- Nhấp chuột phải >> chọn Refresh trên bảng.
- 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 Hóa Làm Mới
- Chuyển đến tab Data >> chọn Queries & Connections.
- Nhấp chuột phải vào Query >> chọn Properties.
- Bật:
- Refresh every 10 minutes.
- Nhấp OK.
Giám Sát Tệp Hoặc Định Dạng Cụ Thể
Bạn có thể lọc chỉ tệp .csv bằng cách sử dụng:
= Table.SelectRows(Source, each Text.EndsWith([Extension], ".csv"))
Hoặc loại trừ tệp temp/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 Được Sử Dụng: Đảm bảo tệp không mở trong ứng dụng khác khi làm mới.
- Tiêu Đề Không Được Phát Hiện: Điều chỉnh bước “Combine Files” trong Power Query để coi hàng đầu tiên là tiêu đề.
- Hiệu Suất: Đối với tệp rất lớn, cân nhắc sử dụng “Data Model” thay vì tải vào worksheet.
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 lên file kéo-thả, làm cho 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à một thư mục được giám sát, bạn có thể xây dựng một hệ thống tải lên file nhẹ, thân thiện với người dùng trong Excel. Khả năng của Power Query giám sát thư mục, nhập và xử lý ngay lập tức bất kỳ tệp mới nào bạn thêm tiết kiệm hàng giờ không kể xiết mà nếu không thì dành cho các tác vụ thủ công. Nó hoàn hảo cho doanh nghiệp nhỏ, nhóm hành chính và hoạt động hiện trường cần kết hợp cập nhật tệp định kỳ nhanh chóng và tin cậy.
Nhận Bài Tập Excel Nâng Cao Miễn Phí Có Giải Pháp!