Tạo một danh sách tùy chọn trong Excel

Làm thế nào để tạo ra một tùy chọn đơn giả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 (Fruit) 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.

Bây giờ bạn cần tạo tên cho danh sách chính của bạn và cho mỗi danh sách phụ thuộc. Bạn có thể thực hiện việc này bằng cách thêm tên mới vào cửa sổ Name Manager (Formulas tab > Name Manager > New) hoặc nhập tên trực tiếp vào Name Box.

Chú ý. Lưu ý rằng nếu hàng đầu tiên của bạn là loại tiêu đề cột như bạn thấy trong hình ở trên, bạn sẽ không đưa vào vùng được đặt tên.

Những điều cần ghi nhớ:

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ư Apricot, Mango, Oranges.
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ị “Mango” khi danh sách tùy chọn cũng được đặt tên đầu tiên là Mango.

Khi hoàn tất, bạn có thể nhấn Ctrl + F3 để mở cửa sổ Name Manager 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.

3. Đặt danh sách tùy chọn (chính) đầu tiên.

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 Data , nhấp vào Data Validation 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 List dưới Allow và nhập tên trong hộp Source.

Kết quả là, bạn sẽ có một trình đơn tùy chọn trong bảng tính của bạn tương tự như sau:

4. Tạo danh sách tùy chọn phụ thuộc.

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 Nguồn :

=INDIRECT(A2)

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

Nếu ô A2 đang trống, bạn sẽ nhận được thông báo lỗi “The Source currently evaluates to an error. Do you want to continue?”

Nhấp Yes, 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.

Bạn có thể thực hiện danh sách tùy chọn loại này theo cách tương tự như chúng ta vừa tạo trình đơn tùy chọn phụ thuộc thứ hai. Chỉ cần nhớ hai điều quan trọng được thảo luận ở trên, điều này rất cần thiết cho công việc của bạn.

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):

=INDIRECT(B2)

5. 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.

Bạn có thể thực hiện danh sách tùy chọn loại này theo cách tương tự như chúng ta vừa tạo trình đơn tùy chọn phụ thuộc thứ hai. Chỉ cần nhớ hai điều quan trọng được thảo luận ở trên, điều này rất cần thiết cho công việc của bạn.

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):

=INDIRECT(B2)

Bây giờ, mỗi khi bạn chọn India  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:

Chú ý. Danh sách các khu vực được hiển thị là duy nhất cho mỗi quốc gia nhưng nó không phụ thuộc vào lựa chọn trong danh sách tùy chọn đầu tiên.

Tạo một danh sách thứ ba phụ thuộc vào hai danh sách đầu tiên

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ó Mango, Oranges, v..vtrong danh sách thứ nhất và India,, Braxin, v.v … ở danh sách thứ hai. Sau đó, bạn tạo các vùng có tên MangoIndia , MangoBrazil , OrangesIndia , OrangesBrazil , 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.

2. Áp dụng Data Validation bằng công thức INDIRECT / SUBSTITUTE  kết nối tên của các mục nhập trong hai cột đầu tiên và loại bỏ khoảng trắng khỏi tên. Ví dụ: trong ô C2, công thức xác nhận dữ liệu sẽ là:

=INDIRECT(SUBSTITUTE(A2&B2,” “,””))

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 Fruit  và Country được chọn trong danh sách tùy chọn đầu tiên.

Đây là cách dễ dàng nhất để tạo hộp tùy chọn dạng cascading trong Excel. Tuy nhiên, phương pháp này có một số hạn chế.

Hạn chế của phương pháp này:

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.

Làm thế nào để tạo danh sách tùy chọn với các mục nhập nhiều từ

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ó Water melon 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 – Watermelon.

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:

=INDIRECT(SUBSTITUTE(A2,” “,””))

Cách ngăn những thay đổi trong danh sách tùy chọn chính

Hãy tưởng tượng trường hợp sau đây. Người dùng lựa chọn tất cả các danh sách tùy chọn, sau đó họ thay đổi ý định, quay lại danh sách đầu tiên và chọn một mục khác. Kết quả là, lựa chọn đầu tiên và thứ hai không khớp. Để ngăn điều này xảy ra, bạn cần chặn bất kỳ thay đổi nào trong danh sách tùy chọn đầu tiên ngay khi lựa chọn được đưa ra trong danh sách thứ hai.

Để 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:

=IF(B2=””, Fruit, INDIRECT(“FakeList”))

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

Tạo các danh sách tùy chọn dạng động trong Excel

Lợi thế của danh sách tùy chọn phụ thuộc dạng động của Excel là bạn được tự do chỉnh sửa danh sách nguồn và các hộp tùy chọn của bạn sẽ được cập nhật trực tiếp. Tất nhiên, việc tạo ra các trình đơn tùy chọn động đòi hỏi nhiều thời gian hơn và các công thức phức tạp hơn, nhưng tôi tin rằng đó là một sự đầu tư xứng đáng bởi vì nó tạo ra nhiều điều hữu ích và thú vị.

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.

Khi bạn đã nhập dữ liệu, hãy chọn tất cả các mục và nhấp vào tab  Insert > Table. . Sau đó, chuyển sang tab Design và nhập tên của bảng của bạn vào hộp Table .

Cách thuận tiện và trực quan nhất là lưu trữ các mục cho menu tùy chọn đầu tiên dưới dạng các tiêu đề bảng và các mục cho danh sách tùy chọn phụ thuộc như dữ liệu bảng. Ảnh dưới đây minh họa cấu trúc bảng, được đặt tên là exporters_tbl – fruit là tiêu đề bảng và một danh sách các nước xuất khẩu được thêm vào dưới tên loại trái cây tương ứng.

2. Tạo tên.

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)

Để tạo tên tham chiếu đến tiêu đề bảng, hãy chọn nó và sau đó nhấp vào ormulas > Name Manager > New  hoặc nhấn Ctrl + F3 .

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 table_name[#Headers] .

Nhập vào một tên có ý nghĩa và dễ nhớ, ví dụ như fruit_list , và nhấn OK .

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ư fruit:

Mẹo. Sử dụng các tham chiếu ô thích hợp để sao chép các danh sách tùy chọn trên bảng tính.

Hãy đọc những điều dưới đây thật cẩn thận, bởi vì đây là một mẹo rất hữu ích mà bạn không muốn bỏ lỡ.

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 cột 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.

Và nếu bạn dự định sao chép các danh sách tùy chọn cho các hàng khác (ví dụ: xuống các ô khác trong cột), thì sử dụng các tham chiếu cột tuyệt đối (với $) và và hàng tương ứng (không $) ví dụ = Sheet2! $ B1.

2.3. Tạo tên cho các mục trong danh sách phụ thuộc.

Thay vì thiết lập tên duy nhất cho mỗi danh sách phụ thuộc như chúng ta đã làm trong ví dụ trước, chúng ta sẽ tạo ra một tên dạng công thức mà  không gán cho bất kỳ ô hoặc nhiều ô cụ thể nào. Nó sẽ lấy danh sách các mục của danh sách tùy chọn thứ hai dựa vào lựa chọn của danh sách tùy chọn thứ nhất. Lợi ích chính của việc sử dụng công thức là bạn sẽ không phải tạo tên mới khi bạn thêm các mục mới vào danh sách tùy chọn đầu tiên – một công thức tạo ra sẽ đặt tên cho tất cả.

Bạn tạo một tên Excel mới theo cách thông thường ( Formulas > Name Manager > New) bằng công thức sau:

=INDEX(exporters_tbl,,MATCH(fruit,fruit_list,0))

Tron đó:

• exporters_tbl – tên của bảng (tạo ra trong bước 1);
• fruit – tên của ô có chứa danh sách tùy chọn đầu tiên (tạo trong bước 2.2);
• fruit_list – tên liên quan đến hàng tiêu đề của bảng (tạo trong bước 2.1).

Tôi đã đặt tên cho nó là exporters_list , như bạn thấy trong hình bên dưới.

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:

3. Thiết lập xác nhận dữ liệu Excel

Đây thực sự là phần dễ hiểu nhất. Với hai công thức đặt tên, bạn thiết lập Data Validation theo cách thông thường ( Data tab > Data validation) ).

• Đối với danh sách tùy chọn đầu tiên, trong hộp Nguồn, nhập = fruit_list (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 = exporters_list (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:

Loại trừ các hàng trống khỏi danh sách tùy chọn dạng động

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à col_num để tham khảo số cột đã chọn:=MATCH(fruit,fruit_list,0)
• Tên gọi whole_col để tham chiếu cột đã chọn (không phải một số, mà toàn bộ cột):=INDEX(exporters_tbl,,col_num)

Trong các công thức trên, exporters_tbl là tên bảng nguồn của bạn, fruit là tên của ô có chứa danh sách tùy chọn đầu tiên và fruit_list 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.

Tiếp theo, sử dụng một trong hai công thức dưới đây để tạo một tên mới (chúng ta gọi nó là exporters_list2 ) để sử dụng với danh sách tùy chọn phụ thuộc:

=INDEX(exporters_tbl,1,col_num) : INDEX(exporters_tbl, COUNTA(entire_col), col_num)

=OFFSET(INDEX(exporters_tbl,1,col_num),0,0,COUNTA(entire_col))

3. Áp dụng Data Validation.

Cuối cùng, chọn ô chứa danh sách tùy chọn phụ thuộc và áp dụng Data Validation bằng cách nhập = exporters_list2 (tên được tạo ra trong bước trước) trong hộp Nguồn .

Hình dưới đây cho thấy danh sách tùy chọn dạng động trong Excel, và tất cả các dòng trống đã biến mất!

Chú ý. 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.