Tạo Một Danh Sách Tùy Chọn Trong Excel

Tạo danh sách tùy chọn đơn giản trong Excel rất dễ dàng. Tất cả bạn cần là một phạm vi được đặt tên và công thức INDIRECT. Cách này áp dụng với tất cả các phiên bản Excel 2007, 2010 và 2013.

1. Nhập các mục cho danh sách tùy chọn.

Trước hết, gõ các mục mà bạn muốn xuất hiện trong danh sách tùy chọn, mỗi danh sách trong một cột riêng biệt. Ví dụ, tôi đang tạo ra một tùy chọn của các nhà xuất khẩu trái cây và cột A của bảng nguồn ( ) bao gồm các mục của danh sách tùy chọn đầu tiên và 3 cột khác liệt kê các mục cho các trình đơn tùy chọn phụ thuộc.

2. Tạo vùng được đặt tên.

1. Các mục xuất hiện trong danh sách tùy chọn đầu tiên tiêu đề chỉ gồm một từ, ví dụ như .
2. Tên của các danh sách phụ thuộc phải chính xác với mục được nhập trong danh sách chính. Ví dụ, danh sách phụ thuộc được hiển thị "" khi danh sách tùy chọn cũng được đặt tên đầu tiên là .

Khi hoàn tất, bạn có thể nhấn Ctrl + F3 để mở cửa sổ và kiểm tra xem tất cả các danh sách tên và tham chiếu có chính xác hay không.

1. Trong một bảng tính khác hoặc cùng bảng tính, hãy chọn một ô hoặc nhiều ô mà bạn muốn danh sách tùy chọn chính của mình xuất hiện.
2. Chuyển đến tab , nhấp vào và thiết lập một danh sách tùy chọn dựa trên phạm vi được đặt tên bằng cách chọn dưới và nhập tên trong hộp .

Chọn các ô cho danh sách tùy chọn phụ thuộc của bạn và áp dụng Data Validation như được mô tả trong bước trước. Nhưng lần này, thay vì nhập tên của dải ô, bạn nhập công thức sau vào trường :

Trong đó A2 là ô với danh sách (chính) tùy chọn đầu tiên của bạn.

Nhấp , và ngay khi bạn chọn một mục từ danh sách tùy chọn đầu tiên, bạn sẽ thấy các mục tương ứng với nó trong danh sách tùy chọn phụ thuộc thứ hai.

Thêm danh sách tùy chọn phụ thuộc thứ ba (tùy chọn)

Nếu cần, bạn có thể thêm danh sách tùy chọn thứ ba tùy thuộc vào lựa chọn trong danh sách thả thứ hai hoặc trên các lựa chọn trong hai danh sách tùy chọn đầu tiên.

Thiết lập danh sách phụ thuộc thứ ba dựa vào danh sách thứ hai.

Ví dụ: nếu bạn muốn hiển thị danh sách các vùng trong cột C tùy thuộc vào quốc gia nào được chọn trong cột B, bạn tạo một danh sách các vùng cho mỗi quốc gia và đặt tên theo tên của quốc gia, phải chính xác với tên quốc gia đó xuất hiện trong danh sách tùy chọn thứ hai. Ví dụ, danh sách các khu vực Indian thì cũng phải được đặt tên là "Indian", hoặc Chines - "Chines", v.v.

Sau đó, bạn chọn một ô cho danh sách tùy chọn thứ ba (trong trường hợp này là ô C2) và áp dụng Data Validation với công thức sau (B2 là ô của danh sách tùy chọn thứ hai có chứa danh sách các quốc gia):

Nếu cần, bạn có thể thêm danh sách tùy chọn thứ ba tùy thuộc vào lựa chọn trong danh sách thả thứ hai hoặc trên các lựa chọn trong hai danh sách tùy chọn đầu tiên.

Thiết lập danh sách phụ thuộc thứ ba dựa vào danh sách thứ hai.

Ví dụ: nếu bạn muốn hiển thị danh sách các vùng trong cột C tùy thuộc vào quốc gia nào được chọn trong cột B, bạn tạo một danh sách các vùng cho mỗi quốc gia và đặt tên theo tên của quốc gia, phải chính xác với tên quốc gia đó xuất hiện trong danh sách tùy chọn thứ hai. Ví dụ, danh sách các khu vực Indian thì cũng phải được đặt tên là "Indian", hoặc Chines - "Chines", v.v.

Sau đó, bạn chọn một ô cho danh sách tùy chọn thứ ba (trong trường hợp này là ô C2) và áp dụng Data Validation với công thức sau (B2 là ô của danh sách tùy chọn thứ hai có chứa danh sách các quốc gia):

Bây giờ, mỗi khi bạn chọn theo danh sách các quốc gia ở cột B, bạn sẽ có các lựa chọn sau trong danh sách tùy chọn thứ ba:

Nếu bạn cần tạo một danh sách tùy chọn tầng phụ thuộc vào các lựa chọn trong danh sách tùy chọn thứ nhất và thứ hai, hãy tiếp tục theo cách này:

1. Tạo thêm các dãy và đặt tên chúng cho bằng cách kết hợp từ trong hai danh sách tùy chọn đầu tiên của bạn. Ví dụ: bạn có trong danh sách thứ nhất và ở danh sách thứ hai. Sau đó, bạn tạo các vùng có tên , , , , v.v .. Những tên này không được chứa dấu gạch dưới hoặc bất kỳ ký tự nào khác.

Trong đó A2 và B2 chứa danh sách tùy chọn thứ nhất và thứ hai.

Kết quả là, danh sách tùy chọn thứ của bạn sẽ hiển thị các vùng tương ứng với và được chọn trong danh sách tùy chọn đầu tiên.

1. Các mục trong danh sách tùy chọn chính chỉ được nhập một từ.
2. Nó cũng không hoạt động nếu các mục trong danh sách tùy chọn chính chứa các ký tự không được phép, chẳng hạn như dấu gạch ngang (-), ký hiệu (&).
3. Các danh sách tùy chọn được tạo ra theo cách này sẽ không được cập nhật tự động, nghĩa là bạn sẽ phải thay đổi tham chiếu các dải ô được đặt tên mỗi khi bạn thêm hoặc xoá các mục trong danh sách nguồn.

Các công thức INDIRECT như chúng ta đã sử dụng trong ví dụ ở trên chỉ có thể xử lý các mục chỉ có từ. Ví dụ, công thức = INDIRECT (A2) tham chiếu đến ô A2 và hiển thị phạm vi được đặt tên đúng với tên như trong ô được tham chiếu. Tuy nhiên, Excel không cho phép có các dấu cách trong tên, đó là lý do tại sao công thức này sẽ không hoạt động khi tên có nhiều từ.

Giải pháp là sử dụng hàm INDIRECT kết hợp với SUBSTITUTE như chúng ta đã làm khi tạo danh sách tùy chọn thứ 3.

Giả sử bạn có trong số các sản phẩm. Trong trường hợp này, bạn sẽ liệt kê danh sách các nhà xuất khẩu dưa hấu viết lền không khoảng trắng - .

Sau đó, ở danh sách tùy chọn thứ hai, hãy áp dụng Data Validation bằng công thức sau đây để loại bỏ khoảng trắng khỏi tên trong ô A2:

Để thực hiện việc này, khi tạo danh sách tùy chọn đầu tiên, hãy sử dụng một công thức đặc biệt để kiểm tra xem liệu bất kỳ mục nào được chọn trong danh sách tùy chọn thứ hai không:

Trong đó B2 chứa danh sách tùy chọn thứ hai, " " là tên của danh sách tùy chọn đầu tiên và " " là bất kỳ tên giả nào không tồn tại.

Bạn có thể tạo một danh sách tùy chọn động sử dụng kết hợp các hàm OFFSET, INDIRECT và COUNTA hoặc INDEX / MATCH có độ linh hoạt cao hơn. Cách sau đây là cách ưa thích của tôi bởi vì nó có nhiều ưu điểm, quan trọng nhất trong số đó là:

1. Bạn chỉ phải tạo ba dải ô được đặt tên, cho dù có bao nhiêu mục trong danh sách chính và phụ thuộc.
2. Danh sách của bạn có thể chứa nhiều từ và bất kỳ ký tự đặc biệt nào.
3. Số mục có thể thay đổi theo từng cột.
4. Thứ tự sắp xếp các mục không quan trọng.
5. Cuối cùng, rất dễ dàng để duy trì và sửa đổi các danh sách nguồn.

Chúng ta hãy bắt đầu thực hành.

1. Tổ chức dữ liệu nguồn của bạn trong một bảng.

Điều đầu tiên bạn phải làm là viết ra tất cả các lựa chọn cho danh sách tùy chọn của bạn trong một bảng tính. Lần này, bạn sẽ phải sử dụng bảng Excel để lưu trữ dữ liệu nguồn.

Các ví dụ được áp dụng với Excel 2007, vì vậy bạn có thể sử dụng phương pháp này trong tất cả các phiên bản mới hơn của Excel 2013, 2010 và 2007.

Bây giờ, dữ liệu nguồn của bạn đã sẵn sàng, tiếp theo là thiết lập các tên tham chiếu sẽ tự động tìm kiếm danh sách chính xác từ bảng của bạn.

2.1. Thêm tên cho hàng tiêu đề của bảng (danh sách tùy chọn chính)

Microsoft Excel sẽ sử dụng hệ thống tham chiếu bảng được xây dựng để tạo tên dạng .

2.2. Tạo một tên cho ô có chứa danh sách tùy chọn đầu tiên.

Tôi biết rằng bạn chưa có bất kỳ danh sách tùy chọn nào. Nhưng bạn phải chọn các Ô để lưu trữ danh sách tùy chọn đầu tiên và tạo tên cho chúng vì bạn sẽ cần đến tên này trong tên tham chiếu thứ ba.

Ví dụ, hộp tùy chọn đầu tiên của tôi nằm trong ô B1 trên Bảng 2, vì vậy tôi tạo tên cho nó, một tên đơn giản ví dụ như :

Nếu bạn dự định sao chép danh sách tùy chọn của bạn đến các ô khác, hãy sử dụng các tham chiếu ô hỗn hợp khi tạo tên cho các ô với danh sách tùy chọn đầu tiên của bạn.

Để sao chép chính xác các danh sách tùy chọn đến các khác (ví dụ: ở bên phải trong bảng tính), sử dụng các tham chiếu tương đối (không có $) và các dòng tuyệt đối (với $) ví dụ = Sheet2! B$1.

Kết quả là, danh sách tùy chọn phụ thuộc của B1 sẽ xuất hiện trong ô B2; Danh sách tùy chọn phụ thuộc của C1 sẽ hiển thị trong C2, v.v.

Tron đó:

• - tên của bảng (tạo ra trong bước 1);
• - tên của ô có chứa danh sách tùy chọn đầu tiên (tạo trong bước 2.2);

Vâng, bạn đã thực hiện phần chính của công việc! Trước khi đi đến bước cuối cùng, bạn nên mở Name Manager ( Ctrl + F3 ) và xác minh tên cũng như tham chiếu:

• Đối với danh sách tùy chọn đầu tiên, trong hộp Nguồn, nhập (tên được tạo trong bước 2.1).
• Đối với danh sách tùy chọn phụ thuộc, hãy nhập (tên được tạo trong bước 2.3).

Đã xong! Danh sách tùy chọn dạng động của bạn đã hoàn thành và sẽ cập nhật tự động cập nhật những thay đổi khi bạn đã thực hiện với bảng nguồn

Danh sách tùy chọn Excel dạng động này, khá hoàn hảo, duy chỉ có một thiếu sót - nếu các cột trong bảng nguồn của bạn chứa nhiều mục khác nhau, các hàng trống sẽ xuất hiện trong trình đơn của bạn như sau:

Nếu bạn muốn xóa bất kỳ dòng trống nào trong hộp tùy chọn của mình, bạn sẽ phải làm một bước xa hơn và cải tiến công thức INDEX / MATCH được sử dụng để tạo ra danh sách tùy chọn dạng động.

Ý tưởng là sử dụng 2 hàm INDEX, trong đó đầu tiên lấy ô trên trái và thứ hai trả về ô dưới cùng bên phải của dải hoặc hàm OFFSET với INDEX và COUNTA lồng nhau. Các bước chi tiết:

1. Tạo thêm hai tên.

Không cần một cho công thức quá cồng kềnh, tạo ra một vài tên với các công thức đơn giản sau đây:

• Một tên gọi là để tham khảo số cột đã chọn:
• Tên gọi để tham chiếu cột đã chọn (không phải một số, mà toàn bộ cột):

Trong các công thức trên, là tên bảng nguồn của bạn, là tên của ô có chứa danh sách tùy chọn đầu tiên và là tên tham chiếu hàng tiêu đề của bảng.

2. Tạo tham chiếu được đặt tên cho danh sách tùy chọn phụ thuộc.

3. Áp dụng Data Validation.

Khi làm việc với danh sách tùy chọn dạng động được tạo bằng các công thức trên, không có gì ngăn cản người dùng thay đổi giá trị trong danh sách đầu tiên sau khi thực hiện lựa chọn trong trình đơn thứ hai, do đó các lựa chọn trong danh sách tùy chọn chính và phụ có thể không khớp. Bạn có thể chặn các thay đổi trong hộp đầu tiên sau khi một lựa chọn được thực hiện trong hộp thứ hai bằng cách sử dụng hàm hoặc VBA.