Chắc hẳn các bạn đã quen với việc tạo Drop-down list bằng Data Validation rồi nhỉ.
Tuy nhiên, nguồn dữ liệu tham chiếu (Source) mà các bạn dùng để làm drop-down list sẽ không được cập nhật mỗi khi cần thêm dữ liệu mới vào danh sách. Việc này sẽ khiến chúng ta mất thời gian vì phải cài đặt lại từ đầu.
Bài viết này mình hướng dẫn các bạn tạo drop-down list tự động cập nhật mỗi khi thêm dữ liệu bằng hàm INDIRECT.
Cho một dữ liệu raw như sau:
Tiến hành tạo drop-down list bằng Data Validation:
Tại Source chúng ta tham chiếu trực tiếp đến vùng chứa dữ liệu muốn đưa vào list.
Kết quả, drop-down list sẽ bị cố định nội dung trong vùng đó, những dữ liệu thêm vào bên dưới (trong ví dụ là năm 2016) sẽ không được đưa vào list trừ khi chúng ta phải thiết lập lại từ đầu. Việc này khá mất thời gian.
Giải pháp ở đây là chúng sẽ sẽ sử dụng hàm INDIRECT để tạo mảng động (danh sách động) cho drop-down list.
Để biết thêm về hàm INDIRECT, xem thêm tại: https://phamnguyenkhanh.home.blog/indirect/
=INDIRECT(“B3:B”&(COUNTA($B$3:$B$100)+2))
Giải thích hàm:
– INDIRECT, như đã nói, đây là hàm tạo mảng động, nghĩa là nó sẽ thay đổi chứ không cố định một mảng bất kỳ, thay đổi như thế nào chúng ta tiếp tục xét thêm các thành phần bên trong;
– “B3:B”, mảng này được bắt đầu từ ô B3 (giá trị đầu tiên 2010), nếu dữ liệu các bạn bắt đầu tại vị trí khác, thì phải thay đổi cho phù hợp. Và tại sao phải đóng ngoặc, vì INDIRECT là hàm tham chiếu gián tiếp, chúng ta không thể nhập trực tiếp một ô hay vùng bất kỳ, tất cả phải chuyển về dạng text hoặc một công thức nào đấy;
– COUNTA, hàm này sẽ trả về số ô có chứa nội dung trong vùng tham chiếu. Tại sao đếm số lại không dùng COUNT mà dùng COUNTA, câu trả lời là, chúng ta đôi khi nhập dữ liệu bị sai thao tác dẫn đến format là số lại chuyển thành format là text khiến hàm COUNT không thể đếm được,
– ($B$3:$B$100)+2, tại sao lại tham chiếu đến B100 mà không phải là ô nào khác, cái này tuỳ thuộc vào độ dài danh sách dự tính được đưa vào drop-down list. B50 cũng được, B1000 cũng được, miễn nó phải lớn hơn tổng danh sách.
Kết quả:
Chúc các bạn thao tác thành công!
phamnguyenkhanh.home.blog 