Cách tạo nhiều danh sách thả xuống mà không cần lặp lại bằng cách sử dụng các dãy được đặt tên trong Excel

Là quản trị viên của trang tính Excel, bạn không muốn bất kỳ ai chỉnh sửa trang tính ở bất kỳ đâu. Vì vậy, chúng tôi hạn chế người dùng khác chỉ cho phép từ các giá trị được đề cập trong danh sách. Đây là một công cụ xác thực dữ liệu cho phép hạn chế người dùng. Nhiều danh sách thả xuống có nghĩa là khi một danh sách được kết nối với một danh sách khác. Ví dụ: nếu chúng tôi chọn tuần từ danh sách đầu tiên, bây giờ các tùy chọn khác, sẽ được giữ nguyên là Thứ Hai, Thứ Ba…, Chủ Nhật. Nếu chúng ta chọn trái cây từ danh sách đầu tiên, thì danh sách trước đó sẽ hiển thị tên trái cây, không phải tên các ngày trong tuần.

Tất cả những điều này có thể khó hiểu. Hãy hiểu cách sử dụng hàm bằng một ví dụ. Ở đây Chúng tôi có một số danh sách ở đây như hình dưới đây.

Đầu tiên, chúng ta cần tạo một danh sách thả xuống cho Danh mục chính và sau đó chúng ta sẽ chuyển sang Danh mục phụ.

Chọn Công thức> Trình quản lý Tên trong Tên đã xác định HOẶC sử dụng phím tắt Ctrl F3 để mở Trình quản lý tên, nơi chúng tôi sẽ giữ danh sách của mảng với tên của chúng để chúng tôi có thể gọi chúng bằng tên đó bất cứ khi nào cần thiết.

Nhấp vào Mới để tạo. Tại đây Tên sẽ là Tháng và trong tùy chọn Tham khảo nhập danh sách dưới Tháng như hình bên dưới.

Chúng ta sẽ làm tương tự trong Week_Days và nó sẽ hiển thị như

Nhấp vào Đóng và bây giờ chọn ô mà chúng ta cần thêm danh sách thả xuống.

Sau đó nhấp vào Xác thực dữ liệu dưới thanh Dữ liệu. Tùy chọn chọn danh sách là Cho phép và chọn các ô cho tên danh mục chính, trong trường hợp này là ô B2 và C2 là “Tháng” và “Ngày trong tuần”

Như chúng ta có thể thấy một danh sách thả xuống được tạo yêu cầu người dùng chọn từ tùy chọn đã cho.

Bây giờ hãy chọn ô trong Danh mục con và chỉ cần viết công thức trong Xác thực dữ liệu và nhấp vào OK.

Kết quả hiển thị như thế này

Nếu tôi không muốn Ngày và Ngày trong tuần. Thay vào đó, tôi muốn Trái cây_Name và rau_Name. Chúng tôi chỉ cần chỉnh sửa danh sách Trình quản lý tên của mình.

Nhấn Ctrl + F3 để mở Trình quản lý tên và xóa danh sách đã được chèn và thêm danh sách mới, tức là Tên_Rau và Rau_công.

Bây giờ chọn ô trong Sub_category như thể hiện trong ảnh chụp nhanh bên dưới.

Thay vì ô Month và Week_Days, chúng ta sẽ sử dụng Fruits_Name và Vegetables_Name trong Data Validation và nhấp vào OK

Như bạn có thể thấy danh sách mới được thêm vào đây.

Đây là cách chúng ta có thể chỉnh sửa trong danh sách thả xuống và thay đổi lựa chọn danh sách.

Giả sử bạn là một giáo viên. Bạn đã chuẩn bị điểm danh của học sinh trong một sổ làm việc. Điểm danh của mỗi tháng nằm trong một trang tính khác nhau của tháng đó.

Trong trang tính tổng thể, bạn muốn đặt một hàm VLOOKUP để kiểm tra xem học sinh đó có mặt hay vắng mặt trong tháng đó. Sẽ rất đơn giản nếu dữ liệu của bạn nằm trên cùng một trang tính, nhưng không phải vậy. Nó nằm trong các trang tính khác nhau. Nhưng điều đó không có nghĩa là chúng ta không thể lấy dữ liệu từ một trang tính khác trong excel. Chúng tôi có thể và sẽ.

Trong tháng, bạn đã tạo một danh sách thả xuống trong ô C1. Nó chứa một danh sách các tháng. Bây giờ bạn muốn hiển thị vắng mặt hoặc có mặt dựa trên tháng đã chọn trong Ô C1. Chúng ta hãy chỉ xem công thức chung trước.

Đối với ví dụ này, tôi tham dự các trang tính “Jan”, “Feb” và “Mar” trong cùng một phạm vi A2: C11.

Bây giờ tôi đã chuẩn bị một trang tính tổng thể.

Trong ô C4, đặt công thức này và kéo nó xuống.

Bây giờ, bất cứ khi nào bạn thay đổi tên của tháng trong ô C1, excel sẽ lấy giá trị từ một trang tính khác (từ trang tính của tháng đó, nếu nó tồn tại).

Chúng tôi đã sử dụng hàm Gián tiếp trong Excel để tìm nạp giá trị từ một trang tính khác.

INDIRECT thay đổi văn bản thành tham chiếu. Chúng tôi đã sử dụng INDIRECT để tham chiếu các trang tính khác trong excel.

Ví dụ: nếu bạn viết INDIRECT (“sheet2: A2”) trong a1 trên sheet1. Nó sẽ kéo giá trị từ sheet2! A2 trong sheet1: A1. Nếu bạn viết = VLOOKUP (“abc”, INDIRECT (“sheet2! A2: B100”), 2,0) bất kỳ trang tính nào, hàm VLOOKUP sẽ tìm kiếm “abc” trong phạm vi A2: B100 trên trang tính2. INDIRECT (“” & $ C $ 1 & “! B2: C11”): Ở đây chúng tôi muốn tên trang tính thay đổi, đó là lý do tại sao chúng tôi viết nó như thế này. Nếu Ô C1 chứa “Jan”, nó sẽ dịch sang INDIRECT (“Jan! B2: C11”), sau đó sẽ dịch sang Jan! B2: C11 cho mảng bảng VLOOKUP. Nếu C1 có Feb, nó sẽ dịch sang INDIRECT (“Feb! B2: C11”) *, v.v.

Sau đó, VLOOKUP đã thực hiện công việc của mình.

VLOOKUP (B4, INDIRECT (“” & $ C $ 1 & “! B2: C11”), 2,0): bây giờ kể từ khi Indirect cung cấp mảng bảng, hàm VLOOKUP chỉ cần kéo dữ liệu từ phạm vi đó một cách dễ dàng.

Hy vọng bài viết này về Cách tạo nhiều danh sách thả xuống mà không lặp lại bằng cách sử dụng các dãy được đặt tên trong Excel là giải thích. Tìm thêm các bài viết về xác thực giá trị và các công thức Excel liên quan tại đây. Nếu bạn thích blog của chúng tôi, hãy chia sẻ nó với bạn bè của bạn trên Facebook. Và bạn cũng có thể theo dõi chúng tôi trên Twitter và Facebook. Chúng tôi rất muốn nghe ý kiến ​​của bạn, hãy cho chúng tôi biết cách chúng tôi có thể cải thiện, bổ sung hoặc đổi mới công việc của mình và làm cho nó tốt hơn cho bạn. Viết thư cho chúng tôi tại [email protected].

Hộp công thức trong xác thực dữ liệu cho phép bạn chọn loại hạn chế bắt buộc.