Excel là công cụ hữu ích nhất khi nói đến việc xử lý các tập dữ liệu khổng lồ. Thông thường chúng ta đã quen với việc tạo danh sách thả xuống nhưng chúng tôi thường cần tạo danh sách thả xuống động trong Excel để làm cho cuộc sống của chúng tôi dễ dàng hơn. Chúng tôi có thể dễ dàng làm như vậy 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 OFFSET Chức năng .
Đây là tập dữ liệu mà tôi sẽ sử dụng để minh họa cách tạo danh sách thả xuống động trong Excel với chức năng OFFSET . Chúng tôi có một số (các) sự kiện thể thao và Danh sách người chiến thắng . Chúng tôi sẽ sắp xếp những người chiến thắng vào các sự kiện tương ứng bằng cách tạo danh sách thả xuống động .
3 phương pháp tạo danh sách thả xuống động bằng Excel OFFSET
1. Tạo danh sách thả xuống động trong Excel với các hàm OFFSET và COUNTA
Sau đây, tôi sẽ minh họa cách tạo danh sách thả xuống động trong Excel sử dụng OFFSET và COUNTA chức năng. 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 chiến thắng từ Danh sách người chiến thắng .
CÁC BƯỚC:
➤ Chọn phạm vi C4:C11 . Sau đó, chuyển đến Dữ liệu tab>> Công cụ dữ liệu >> Xác thực dữ liệu >> Xác thực dữ liệu .
➤ Xác thực dữ liệu hộp thoại sẽ bật lên. Chọn Danh sách từ trình đơn thả xuống trong hộp thoại đó .
➤ Trong Nguồn , hãy viết ra 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 trống trong phạm vi E4:E100
Đầu ra ➜ {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 một tham chiếu nhất định.
➥ OFFSET ($ E $ 4,0,0,4,1)
Đầu ra ➜ {“Alex”; ”Morgan”; ”Faulkner”; ”Eliot”}
Giải thích: Tham chiếu là E4 . Kể từ hàng là 0 và cột là 0 cuối cùng là chiều cao trong tổng số 4 , chúng tôi sẽ có các giá trị từ ô E4:E7 .
➤ Chọn OK . 
Excel sẽ tạo trình đơn thả xuống hộp 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 giống hệt như các danh sách trong Danh sách người chiến thắng . Bây giờ, để kiểm tra xem đây có phải là hộp thả xuống động hay không, hãy giả sử rằng Người chiến thắng của Chụp sự kiện là James . Kể từ khi James không có trong Danh sách người chiến thắng , hãy thêm tên của anh ấy và xem điều gì sẽ xảy ra.
Ngay sau khi chúng tôi thêm tên của James trong Danh sách người chiến thắng , Excel đã tự động cập nhật các tùy chọn trong tùy chọn thả xuống . Vì vậy, các danh sách thả xuống này là động trong tự nhiên .
➤ Bây giờ chọn Người chiến thắng còn lại .
LƯU Ý :Hãy nhớ rằng phạm vi chúng tôi đã chọn trong Hàm COUNTA là E4:E100 . Đó là lý do tại sao Excel sẽ cập nhật tùy chọn thả xuống miễn là chúng tôi thêm hoặc cập nhật ô trong phạm vi E4:E100 .
Đọc thêm: Cách tạo danh sách xác thực dữ liệu động bằng VBA trong Excel
2. Cách tạo danh sách thả xuống động trong Excel với các hàm OFFSET và COUNTIF
Chúng tôi cũng có thể tạo danh sách thả xuống động trong Excel sử dụng OFFSET và COUNTIF chức năng.
CÁC BƯỚC:
➤ Mang theo Xác thực dữ liệu hộp thoại như phương pháp-1 . Trong Nguồn , hãy viết ra 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 trống trong phạm vi E4:E100
Đầu ra ➜ {4}
➥ OFFSET ($ E $ 4,0,0, COUNTIF ($ E $ 4:$ E $ 100, ”<>”)) ➜ Trả về một phạm vi dựa trên hàng và cột của một tham chiếu nhất định.
➥ OFFSET ($ E $ 4,0,0,4,1)
Đầu ra ➜ {“Alex”; ”Morgan”; ”Faulkner”; ”Eliot”}
Giải thích: Tham chiếu là E4 . Kể từ hàng là 0 và cột là 0 cuối cùng là chiều cao trong tổng số 4 , chúng tôi sẽ có các giá trị từ ô E4:E7
➤ Chọn OK . 
➤ Excel sẽ tạo trình đơn thả xuống hộp trong mỗi ô của phạm vi C4:C11 .
Để kiểm tra xem đây có phải là hộp thả xuống động hay không, hãy giả sử rằng Người chiến thắng của Chụp sự kiện là James . Kể từ khi James không có trong Danh sách người chiến thắng , hãy thêm tên của anh ấy và xem điều gì sẽ xảy ra.
Ngay sau khi chúng tôi thêm tên của James trong Danh sách người chiến thắng , Excel đã tự động cập nhật các tùy chọn trong tùy chọn thả xuống . Vì vậy, các danh sách thả xuống này là động trong tự nhiên .
➤ Bây giờ chọn Người chiến thắng còn lại .
LƯU Ý :Hãy nhớ rằng phạm vi chúng tôi đã chọn trong Hàm COUNTIF là E4:E100 . Đó là lý do tại sao Excel sẽ cập nhật tùy chọn thả xuống miễn là chúng tôi thêm hoặc cập nhật ô trong phạm vi E4:E100 .
3. Cách tạo danh sách thả xuống lồng nhau bằng cách sử dụng kết hợp các hàm
Trong phần này, chúng tôi sẽ đi sâu vào việc tạo danh sách thả xuống động thông minh hơn và nâng cao hơn , một lồng nhau một. Chúng tôi sẽ sử dụng OFFSET , COUNTA và MATCH các chức năng với nhau. Hãy để tôi giải thích những gì chúng tôi đang làm.
Đây là tập 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ể .. Về cơ bản, chúng tôi 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
➤ Mang theo Xác thực dữ liệu hộp thoại như phương pháp-1 . Trong Nguồn , hãy thực hiện một tham chiếu ô , là 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 một danh sách thả xuống khác trong F4 . Các tùy chọn trong danh sách thả xuống trong tổng số F4 sẽ phụ thuộc vào những gì chúng tôi đã chọn trong danh sách thả xuống trong tổng số F3 . Làm như vậy,
➤ Mang theo Xác thực dữ liệu hộp thoại như phương pháp-1 . Trong Nguồn , hãy viết ra 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
➥ TRẬN ĐẤU ($ 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
Đầu ra: {1} .
➥ OFFSET ($ B $ 3,1, TRẬN ĐẤU ($ F $ 3, $ B $ 3:$ D $ 3,0) -1,10,1) ➜ Trả về phạm vi dựa trên trên hàng và cột của một tài liệu tham khảo nhất định . Chiều cao là 10 . Đó là lý do tại sao đầu ra sẽ là một mảng 10 giá trị ô bắt đầu từ tham chiếu .
Đầu ra: {“Sam”; ”Curran”; ”Yank”; ”Rochester”; 0; 0; 0; 0; 0; 0}
➥ COUNTA (OFFSET ($ B $ 3,1, TRẬN ĐẤU ($ F $ 3, $ B $ 3:$ D $ 3,0) -1,10,1)) ➜ Trả về số ô không trống trong phạm vi đã chọn .
➥ COUNTA {“Sam”; ”Curran”; ”Yank”; ”Rochester”; 0; 0; 0; 0; 0; 0}
Đầu ra: {4}
➥ OFFSET ($ B $ 3,1, TRẬN ĐẤU ($ F $ 3, $ B $ 3:$ D $ 3,0) -1, COUNTA (OFFSET ($ B $ 3,1, TRẬN ĐẤU ($ 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 một tham chiếu nhất định
➥ 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)
Đầu ra: {“Sam”; ”Curran”; ”Yank”; ”Rochester”}
Giải thích: Tham chiếu là B3 . Kể từ hàng là 1 và cột là 0 cuối cùng là chiều cao trong tổng số 4 , chúng tôi sẽ có cá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 trên 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 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 10 động hàng đầu trong Excel (8 phương pháp)
Sách bài tập Thực hành
Như bạn có thể thấy, tạo danh sách thả xuống động trong Excel với chức năng OFFSET là thực sự khó khăn. Vì vậy, tôi đề nghị bạn nên thực hành nhiều hơn và nhiều hơn nữa. Tôi đã đính kèm một tờ hướng dẫn thực hành cho bạn.
Kết luận
Trong bài viết này, tôi đã minh họa 3 các phương pháp để tạo danh sách thả xuống động trong Excel với chức năng OFFSET . Tôi hy vọng bạn sẽ 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 đóng góp nào, vui lòng để lại trong khung 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í (Một và Nhiều Tiêu chí)