Chào các bác, hôm nay em muốn chia sẻ một chút kinh nghiệm về việc tự động hóa tạo danh sách thả xuống (dropdown list) liên kết giữa các ô trong Excel. Tình huống là em có một file Excel cần nhập liệu nhiều, mà các danh mục lại phụ thuộc lẫn nhau. Ví dụ, chọn tỉnh thành ở ô A1 thì danh sách quận/huyện ở ô B1 sẽ tự động cập nhật chỉ hiển thị các quận/huyện thuộc tỉnh đó.
Việc làm thủ công thì mất thời gian và dễ sai sót, nhất là khi danh sách có hàng trăm tỉnh/thành phố và hàng nghìn quận/huyện. Sau một hồi tìm tòi, em phát hiện ra có thể dùng Python để giải quyết vấn đề này một cách ngon lành.
Cách làm cơ bản:
- Đầu tiên, chuẩn bị sẵn dữ liệu danh sách tỉnh/thành phố và danh sách quận/huyện tương ứng trong một file Excel riêng hoặc một cấu trúc dữ liệu nào đó mà Python có thể đọc được (ví dụ: file CSV, JSON).
- Sử dụng thư viện
pandasđể đọc dữ liệu này vào Python. - Với mỗi cặp tỉnh/quận, chúng ta sẽ tạo một Named Range trong file Excel đích. Ví dụ, với tỉnh 'Hà Nội', ta sẽ tạo một Named Range tên là 'Ha_Noi' chứa danh sách các quận của Hà Nội.
- Tiếp theo, sử dụng
openpyxl(hoặcxlsxwriter) để ghi các quy tắc Data Validation vào file Excel. - Đối với ô chọn tỉnh (ví dụ A1), tạo một Data Validation dạng 'List' với nguồn dữ liệu là danh sách các tỉnh.
- Đối với ô chọn quận (ví dụ B1), tạo một Data Validation dạng 'List' với nguồn dữ liệu là một công thức sử dụng hàm
INDIRECT. Công thức này sẽ trỏ đến Named Range tương ứng với tỉnh đã chọn ở ô A1. Ví dụ, nếu ô A1 là 'Hà Nội', công thức sẽ là=INDIRECT(SUBSTITUTE(A1," ","_")).
Việc này giúp tự động hóa hoàn toàn quá trình tạo và cập nhật các danh sách thả xuống liên kết, tiết kiệm rất nhiều thời gian và công sức. Em đã áp dụng thử và thấy hiệu quả rõ rệt. Bác nào có nhu cầu hoặc gặp vấn đề tương tự có thể tham khảo nhé.
Có bác nào có cách khác hay hơn không, chia sẻ cho em học hỏi với ạ!