Excel là công cụ hữu ích nhất khi xử lý các tập dữ liệu lớn. Chúng ta thường quen với việc tạo danh sách thả xuống nhưng thường cần tạo danh sách thả xuống động trong Excel để công việc dễ dàng hơn. Chúng ta có thể thực hiện dễ dàng bằng cách áp dụng hàm OFFSET. Trong bài viết này, tôi sẽ hướng dẫn bạn cách tạo danh sách thả xuống động trong Excel với hàm OFFSET.
3 Phương Pháp Tạo Danh Sách Thả Xuống Động Sử Dụng Hàm OFFSET Trong Excel
1. Tạo Danh Sách Thả Xuống Động Với Hàm OFFSET Và COUNTA
Ở đây, tôi sẽ minh họa cách tạo danh sách thả xuống động trong Excel sử dụng hàm OFFSET và COUNTA. Tôi cần tạo danh sách thả xuống động trong phạm vi C4:C11. Tôi sẽ chọn Người Thắng Cuộc từ Danh Sách Người Thắng Cuộc.
CÁC BƯỚC THỰC HIỆN:
- Chọn phạm vi C4:C11. Sau đó chuyển đến tab Dữ liệu (Data) >> Công cụ Dữ liệu (Data Tools) >> Xác thực Dữ liệu (Data Validation) >> Xác thực Dữ liệu (Data Validation).
- Hộp thoại Xác thực Dữ liệu sẽ xuất hiện. Chọn Danh sách (List) từ menu thả xuống trong hộp thoại đó.
- Trong ô Nguồn (Source), nhập công thức sau:
=OFFSET($E$4,0,0,COUNTA($E$4:$E$100),1)
Phân Tích Công Thức
- COUNTA($E$4:$E$100) ➜ Trả về số ô không rỗng trong phạm vi E4:E100. Kết quả: {4}
- OFFSET($E$4,0,0,COUNTA($E$4:$E$100),1) ➜ Trả về một phạm vi dựa trên hàng và cột của tham chiếu cho trước.
- OFFSET($E$4,0,0,4,1) ➜ Kết quả: {"Alex";"Morgan";"Faulkner";"Eliot"}
Giải thích: Tham chiếu là E4. Vì hàng là 0 và cột là 0 với chiều cao 4 ô, ta sẽ có giá trị từ ô E4:E7.
- Chọn OK.
Excel sẽ tạo hộp thả xuống (drop-down box) trong mỗi ô của phạm vi C4:C11.
Lưu ý rằng các tùy chọn trong hộp thả xuống chính xác như Danh Sách Người Thắng Cuộc. Để kiểm tra xem đây có phải danh sách thả xuống động hay không, giả sử Người Thắng Cuộc của Sự Kiện Bắn Súng là James. Vì James không có trong Danh Sách Người Thắng Cuộc, hãy thêm tên của anh ấy và xem điều gì xảy ra.
Ngay khi thêm tên James vào Danh Sách Người Thắng Cuộc, Excel tự động cập nhật các tùy chọn trong danh sách thả xuống. Vì vậy, các danh sách thả xuống này có tính động.
- Bây giờ chọn các Người Thắng Cuộc còn lại.
LƯU Ý: Nhớ rằng phạm vi chúng ta chọn trong hàm COUNTA là E4:E100. Đó là lý do Excel sẽ cập nhật tùy chọn thả xuống miễn là chúng ta thêm hoặc cập nhật các ô trong phạm vi E4:E100.
Đọc Thêm: Cách Tạo Danh Sách Xác Thực Dữ Liệu Động Sử Dụng VBA Trong Excel
2. Cách Tạo Danh Sách Thả Xuống Động Với Hàm OFFSET Và COUNTIF
Chúng ta cũng có thể tạo danh sách thả xuống động trong Excel sử dụng hàm OFFSET và COUNTIF.
CÁC BƯỚC THỰC HIỆN:
- Mở hộp thoại Xác thực Dữ liệu như phương pháp 1. Trong ô Nguồn (Source), nhập công thức sau:
=OFFSET($E$4,0,0,COUNTIF($E$4:$E$100,"<>"))
Phân Tích Công Thức
- COUNTIF($E$4:$E$100,"<>") ➜ Trả về số ô không rỗng trong phạm vi E4:E100. Kết quả: {4}
- OFFSET($E$4,0,0,COUNTIF($E$4:$E$100,"<>")) ➜ Trả về phạm vi dựa trên hàng và cột của tham chiếu cho trước.
- OFFSET($E$4,0,0,4,1) ➜ Kết quả: {"Alex";"Morgan";"Faulkner";"Eliot"}
Giải thích: Tham chiếu là E4. Vì hàng là 0 và cột là 0 với chiều cao 4 ô, ta sẽ có giá trị từ ô E4:E7.
- Chọn OK.
- Excel sẽ tạo hộp thả xuống trong mỗi ô của phạm vi C4:C11.
Để kiểm tra xem đây có phải hộp thả xuống động hay không, giả sử Người Thắng Cuộc của Sự Kiện Bắn Súng là James. Vì James không có trong Danh Sách Người Thắng Cuộc, hãy thêm tên của anh ấy và xem điều gì xảy ra.
Ngay khi thêm tên James vào Danh Sách Người Thắng Cuộc, Excel tự động cập nhật các tùy chọn trong danh sách thả xuống. Vì vậy, các danh sách thả xuống này có tính động.
- Bây giờ chọn các Người Thắng Cuộc còn lại.
LƯU Ý: Nhớ rằng phạm vi chúng ta chọn trong hàm COUNTIF là E4:E100. Đó là lý do Excel sẽ cập nhật tùy chọn thả xuống miễn là chúng ta thêm hoặc cập nhật các ô trong phạm vi E4:E100.
3. Tạo Danh Sách Thả Xuống Lồng Nhau (Nested) Sử Dụng Kết Hợp Các Hàm
Trong phần này, chúng ta sẽ tìm hiểu cách tạo một danh sách thả xuống động thông minh và nâng cao hơn, đó là danh sách lồng nhau (nested). Chúng ta sẽ sử dụng các hàm OFFSET, COUNTA, và MATCH cùng nhau. Hãy để tôi giải thích những gì chúng ta sẽ làm.
Đây là bộ dữ liệu cho phương pháp này, đại diện cho thông tin của các sản phẩm cụ thể. Chúng ta sẽ tạo hai danh sách thả xuống trong ô F3 và F4. Tùy thuộc vào tùy chọn được chọn trong F3, Excel sẽ cập nhật các tùy chọn trong F4. Hãy làm từng bước một.
BƯỚC 1: Tạo Danh Sách Thả Xuống Trong F3
- Mở hộp thoại Xác thực Dữ liệu như phương pháp 1. Trong ô Nguồn (Source), thực hiện tham chiếu ô, là các tiêu đề bảng (ô B3:D3).
Excel sẽ tạo danh sách thả xuống trong F3.
BƯỚC 2: Tạo Danh Sách Thả Xuống Động Trong F4
Bây giờ tôi sẽ tạo danh sách thả xuống khác trong F4. Các tùy chọn trong danh sách thả xuống của F4 sẽ phụ thuộc vào những gì chúng ta đã chọn trong danh sách thả xuống của F3. Để làm điều này:
- Mở hộp thoại Xác thực Dữ liệu như phương pháp 1. Trong ô Nguồn (Source), nhập công thức sau:
=OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,COUNTA(OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,10,1)),1)
Phân Tích Công Thức
- MATCH($F$3,$B$3:$D$3,0) ➜ Trả về vị trí tương đối của giá trị ô F3 từ phạm vi B3:D3. Kết quả: {1}.
- OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,10,1) ➜ Trả về một phạm vi dựa trên hàng và cột của tham chiếu cho trước. Chiều cao là 10. Đó là lý do kết quả sẽ là một mảng 10 giá trị ô bắt đầu từ tham chiếu. Kết quả: {"Sam";"Curran";"Yank";"Rochester";0;0;0;0;0;0}
- COUNTA(OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,10,1)) ➜ Trả về số ô không rỗng trong phạm vi được chọn.
- COUNTA{"Sam";"Curran";"Yank";"Rochester";0;0;0;0;0;0} ➜ Kết quả: {4}
- OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,COUNTA(OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,10,1)),1) ➜ Trả về phạm vi dựa trên hàng và cột của tham chiếu cho trước.
- OFFSET($B$3,1,1-1,COUNTA{"Sam";"Curran";"Yank";"Rochester";0;0;0;0;0;0}),1)
- OFFSET($B$3,1,0,4,1) ➜ Kết quả: {"Sam";"Curran";"Yank";"Rochester"}
Giải thích: Tham chiếu là B3. Vì hàng là 1 và cột là 0 với chiều cao 4 ô, ta sẽ có giá trị từ ô B4:B7.
- Chọn OK.
Excel sẽ tạo danh sách thả xuống động trong F4. Các tùy chọn sẽ thay đổi tùy thuộc vào những gì bạn chọn trong F3. Ví dụ, khi bạn chọn Tên trong danh sách thả xuống F3, danh sách thả xuống trong F4 sẽ hiển thị các tên có sẵn trong cột Tên.
Tương tự, khi bạn chọn Sản Phẩm trong danh sách thả xuống F3, danh sách thả xuống trong F4 sẽ hiển thị các sản phẩm có sẵn trong cột Sản Phẩm.
Bây giờ nếu bạn thêm hoặc cập nhật Tên, Sản Phẩm, hoặc Thương Hiệu, Excel sẽ cập nhật danh sách thả xuống trong F4. Ví dụ, tôi đã thêm một tên mới Rock trong cột Tên và Excel đã thêm tên đó vào danh sách thả xuống.
Đọc Thêm: Cách Tạo Danh Sách Top 10 Động Trong Excel (8 Phương Pháp)
File Thực Hành
Như bạn thấy, việc tạo danh sách thả xuống động trong Excel với hàm OFFSET khá phức tạp. Vì vậy tôi建议 bạn thực hành nhiều hơn. Tôi đã đính kèm một trang tính thực hành cho bạn.
Kết Luận
Trong bài viết này, tôi đã minh họa 3 phương pháp để tạo danh sách thả xuống động trong Excel với hàm OFFSET. Hy vọng bạn thấy bài viết này hữu ích. Cuối cùng, nếu bạn có bất kỳ ý kiến nào, vui lòng để lại trong hộp bình luận.
Các Bài Viết Liên Quan
- Excel Tạo Danh Sách Động Từ Bảng (3 Cách Dễ Dàng)
- Cách Tạo Danh Sách Động Trong Excel Dựa Trên Tiêu Chí (Tiêu Chí Đơn Và Nhiều Tiêu Chí)