Bí quyết tạo danh sách dropdown trong Excel

Hướng dẫn chi tiết cách tạo danh sách dropdown đơn giản trong Excel

Việc thiết lập một danh sách dropdown đơn giản trong Excel là việc làm không mấy phức tạp. Bạn chỉ cần sử dụng một khoảng cách có tên và công thức INDIRECT. Phương pháp này có thể áp dụng cho các phiên bản Excel từ 2007, 2010 cho đến 2013.

1. Điền thông tin các mục vào danh sách dropdown.

Đầu tiên, nhập các mục bạn muốn hiển thị trong danh sách dropdown, mỗi một danh sách nằm trên một cột khác nhau. Chẳng hạn, tôi đặt danh sách nhà xuất khẩu trái cây trong cột A của bảng nguồn (Fruit), và danh sách này bao gồm các mục cho danh sách dropdown chính và ba cột khác liệt kê các mục cho các danh sách dropdown phụ.

2. Định nghĩa các vùng có tên.

Bây giờ, bạn cần đặt tên cho danh sách chính cũng như cho từng danh sách phụ thuộc. Việc này có thể thực hiện bằng cách thêm tên mới vào Name Manager (trên Formulas tab > Name Manager > New) hoặc ghi trực tiếp vào Name Box.

Tạo vùng có tên

Chú ý: Hãy đảm bảo rằng hàng đầu tiên với tiêu đề cột, như hình minh họa phía trên, không được bao gồm trong vùng có tên.

Lưu ý quan trọng:

  1. Mỗi mục trong danh sách dropdown chính phải là từ đơn lẻ, ví dụ như Apricot, Mango, Oranges.
  2. Danh sách phụ thuộc phải có tên khớp chính xác với mục đã nhập trong danh sách chính. Ví dụ, danh sách phụ thuộc có mục “Mango” khi danh sách dropdown chính được đặt tên là Mango.

Bạn có thể kiểm tra tính chính xác của tất cả tên danh sách và tham chiếu bằng cách ấn Ctrl + F3 để mở Name Manager.

Kiểm tra tên vùng và tham chiếu3. Thiết lập danh sách dropdown (chính) ban đầu.

  1. Chọn ô hay nhóm ô mà bạn mong muốn xuất hiện danh sách dropdown chính.
  2. Trong tab Data, nhấn vào Data Validation và tạo danh sách tùy chọn từ vùng có tên bằng cách chọn List trong mục Allow và nhập tên trong hộp Source.

Chọn ô và áp dụng Data Validation của Excel cho danh sách dropdown chính

Điều này sẽ tạo ra danh sách dropdown trong bảng tính của bạn như dưới đây:

Danh sách dropdown chính trong Excel4. Xây dựng danh sách dropdown phụ thuộc.

Chọn ô của danh sách phụ thuộc và thực hiện Data Validation giống như trong bước trên. Tuy nhiên, lần này bạn sẽ nhập công thức sau vào ô Nguồn:

=INDIRECT(A2)

Nơi A2 là ô có danh sách dropdown (chính).

Nếu nhận thông báo lỗi “The Source currently evaluates to an error. Do you want to continue?” khi ô A2 chưa được điền, hãy chọn Yes. Lựa chọn một tùy chọn từ danh sách đầu tiên sẽ hiện ra các tùy chọn liên quan trong danh sách thứ hai.

Thêm vào một danh sách thả xuống thứ ba phụ thuộc (không bắt buộc)

Bạn có khả năng tạo một danh sách thẻ ba phụ thuộc vào sự lựa chọn ở danh sách thứ hai, hoặc phụ thuộc vào cả hai danh sách đầu tiên nếu muốn.

Thiết lập danh sách thứ ba này dựa trên danh sách thứ hai.

Cách thực hiện tương tự với việc tạo danh sách thứ hai đã được mô tả trước đó. Nhớ lại hai điểm quan trọng đã đề cập để hoàn thành công việc này một cách chính xác.

Ví dụ, để hiển thị danh sách các vùng tại cột C phụ thuộc vào quốc gia chọn ở cột B, bạn sẽ tạo danh sách vùng theo mỗi quốc gia và đặt các tên chính xác giống với tên xuất hiện trong danh sách thứ hai. Ví dụ như danh sách vùng của “Indian” phải có tên “Indian”…

Chọn một ô (ví dụ C2) cho danh sách thứ ba và sử dụng Data Validation với công thức sau đây, nơi B2 chứa danh sách quốc gia:

=INDIRECT(B2)

Tạo danh sách thả xuống phụ thuộc thứ ba theo lựa chọn trong danh sách thứ hai

Khi bạn chọn India trong danh sách quốc gia ở cột B, danh sách thứ ba sẽ hiện ra các tùy chọn sau:

Danh sách thả xuống thứ ba phụ thuộc vào lựa chọn trong menu thả xuống thứ hai

Lưu ý: Mỗi danh sách khu vực là duy nhất theo từng quốc gia và không phụ thuộc vào lựa chọn trong danh sách thứ nhất.

Tạo một danh sách

Dựa trên hai danh sách đầu tiên, cách tạo danh sách thứ ba có liên kết

Để xây dựng một danh sách lựa chọn thứ ba có tính phụ thuộc vào danh sách thứ nhất và thứ hai, hãy thực hiện như sau:

1. Hãy bổ sung thêm dãy phạm vi và đặt tên cho chúng bằng cách ghép các từ trong danh sách đầu tiên và danh sách thứ hai. Chẳng hạn, nếu bạn có Mango, Oranges, etc. trong danh sách đầu tiên và India, Brazil, etc. trong danh sách thứ hai, bạn sẽ tạo ra dãy phạm vi có tên như MangoIndia, MangoBrazil, OrangesIndia, OrangesBrazil, vân vân. Lưu ý không sử dụng dấu gạch dưới hay các ký tự đặc biệt khác trong tên này.

Tạo dãy phạm vi đặt tên thêm.2. Sử dụng Data Validation thông qua công thức INDIRECT/SUBSTITUTE để kết hợp tên của các mục trong hai cột đầu tiên và loại bỏ khoảng trắng. Ví dụ, tại ô C2, công thức xác nhận dữ liệu sẽ là:

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

Ở đây, A2 và B2 là nơi chứa danh sách lựa chọn thứ nhất và thứ hai.

Kết quả sẽ là danh sách tùy chọn thứ ba hiển thị các vùng dữ liệu phù hợp với sự lựa chọn FruitCountry từ hai danh sách đầu tiên.

Danh sách thả xuống thứ ba phụ thuộc vào hai lựa chọn đầu tiên

Đây là một cách tiện lợi để tạo ra danh sách thả xuống kiểu cascading trong Excel. Tuy nhiên, cách này có một số giới hạn.

Nhược điểm của phương pháp này:

  1. Các mục trong danh sách chính cần nhập đơn từ.
  2. Kỹ thuật này không áp dụng cho trường hợp các mục trong danh sách chính chứa các ký tự không hợp lệ, như dấu gạch ngang (-), ký hiệu (&).
  3. Danh sách tùy chọn tạo ra sẽ không được cập nhật tự động nếu bạn thêm hay xóa mục từ danh sách nguồn; bạn cần phải cập nhật lại tên được gán cho phạm vi ô tương ứng.

Cách tạo danh sách tùy chọn nhiều từ

Đối với các mục nhập nhiều từ, các công thức INDIRECT như đã được sử dụng trong ví dụ trên chỉ có thể xử lý các mục đơn từ. Công thức như =INDIRECT(A2) sẽ trỏ đến ô A2 và hiển thị dãy được đặt tên trùng với tên trong ô tham chiếu. Do Excel không cho phép khoảng trắng trong tên, công thức này sẽ không phù hợp với trường hợp tên chứa nhiều từ.

Giải pháp là sử dụng INDIRECT kết hợp với SUBSTITUTE giống như trong việc tạo danh sách thứ ba. Giả sử bạn có mục Water melon trong danh mục sản phẩm. Trong trường hợp này, bạn sẽ liệt kê danh sách xuất khẩu không có khoảng trắng – Watermelon.

Ở danh sách thứ hai, bạn áp dụng Data Validation với công thức sau để loại bỏ khoảng trắng từ ô A2:

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

Tạo danh sách thả xuống cascading với những mục nhập nhiều từ

Phòng tránh sự thay đổi danh sách chọn chính

Xét trường hợp người dùng đã chọn tất cả các mục từ danh sách rồi sau đó đổi ý, họ quay lại danh sách đầu tiên để chọn một lựa chọn khác. Điều này gây ra sự không nhất quán giữa lựa chọn thứ nhất và thứ hai. Để hạn chế điều này, bạn cần ngăn chặn bất cứ sự thay đổi nào ở danh sách thứ nhất sau khi một lựa chọn đã được thực hiện ở danh sách thứ hai.

Để làm điều này, bạn có thể

Khi bắt đầu tạo ra danh sách lựa chọn chính, hãy áp dụng một công thức độc đáo để xác minh nếu có phần tử nào trong danh sách lựa chọn thứ hai đã được chọn hay chưa:

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

Ở đây, B2 là ô chứa danh sách lựa chọn thứ hai, “Fruit” đại diện cho tên của danh sách lựa chọn chính và “FakeList” chỉ là một tên không thực sự tồn tại.

Preventing changes in the primary drop down list

Khả năng tạo danh sách dropdown động trong Excel

Điểm mạnh của danh sách dropdown thứ cấp động trong Excel là người dùng có thể thay đổi danh sách nguồn mà không cần phải chỉnh sửa lại danh sách đó, nó sẽ được cập nhật tự động. Việc tạo ra các menu dropdown động này mặc dù cần thêm thời gian và phức tạp hơn trong các công thức, nhưng tôi cho rằng đó là một khoản đầu tư đáng giá bởi lẽ nó mang lại nhiều tiện ích và sự linh hoạt.

Bạn có thể tạo ra một danh sách dropdown động bằng cách sử dụng sự kết hợp giữa các hàm OFFSET, INDIRECT, COUNTA hoặc kết hợp INDEX / MATCH cho tính năng cao cấp hơn. Phương pháp dưới đây là phương pháp được tôi ưa chuộng hơn cả vì nó mang lại nhiều lợi ích như sau:

  1. Chỉ cần tạo ba phạm vi ô có tên định sẵn, bất kể danh sách chính và phụ thuộc có bao nhiêu phần tử.
  2. Danh sách có thể bao gồm nhiều từ và ký tự đặc biệt.
  3. Số lượng phần tử có thể biến đổi theo mỗi cột.
  4. Thứ tự của các phần tử không cần thiết phải có thứ tự cụ thể.
  5. Dễ dàng duy trì và cập nhật danh sách nguồn.

Hãy bắt đầu với việc thực hành.

1. Sắp xếp lại dữ liệu nguồn của bạn trong một bảng Excel.

Bạn cần tiến hành ghi lại tất cả các tùy chọn có trong danh sách dropdown của mình vào trong một bảng tính Excel. Dữ liệu nguồn lần này phải được lưu trữ dưới dạng một bảng trong Excel.

Ví dụ này áp dụng cho Excel 2007, nhưng bạn cũng có thể áp dụng cho các phiên bản Excel 2013, 2010 hay mới hơn.

Sau khi đã nhập các dữ liệu, hãy lựa chọn toàn bộ và chọn tab Insert > Table. Sau đó, chuyển đến tab Design và điền tên cho bảng của bạn vào ô Table Name.

Cách tối ưu và trực quan nhất là lưu các mục cho menu dropdown chính dưới dạng tiêu đề của bảng và các mục của danh sách dropdown phụ thuộc thành dữ liệu trong bảng. Hình dưới minh họa cấu trúc của bảng, được đặt tên là exporters_tbl – với fruit là tiêu đề và các quốc gia xuất khẩu được liệt kê dưới danh mục của từng loại trái cây tương ứng.

2. Đặt tên.

Giờ khi đã có sẵn dữ liệu nguồn, ta tiến hành thiết lập tên tham chiếu để tự động lấy danh sách chính xác từ bảng dữ liệu.

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

Để khởi tạo tên tham chiếu tới tiêu đề bảng, chọn tiêu đề đó sau đó nhấn vào Formulas > Name Manager > New hoặc sử dụng phím tắt Ctrl + F3.

Excel sẽ dựa vào hệ thống ngữ cảnh của bảng để tạo ra tên dạng table_name[#Headers].

Create a name for the table's header row.

Đặt tên cho nó sao cho dễ nhớ và có ý nghĩa, chẳng hạn như fruit_list, rồi nhấn OK.

2.2. Tạo tên cho những ô sẽ chứa danh sách dropdown chính.

Dù hiện tại bạn chưa có bất kỳ danh sách dropdown nào, nhưng bạn nên chọn những ô để chứa danh sách dropdown chính và gán tên cho chúng vì tên này sẽ được sử dụng trong tên tham chiếu thứ ba.

Ví dụ:Chẳng hạn, tôi gán tên cho hộp tùy chọn đầu tiên, nằm ở vị trí B1 trên Bảng 2, với một cái tên đơn giản như fruit:

Tạo tên cho ô chứa danh sách thả xuống chính

Lời khuyên. Hãy áp dụng tham chiếu ô chính xác để nhân bản các danh sách thả xuống qua bảng tính.

Xin lưu ý vấn đề sau đây vì nó là một lời khuyên quan trọng có thể bạn không muốn bỏ qua.

Khi muốn chép danh sách thả xuống sang những ô khác, hãy dùng tham chiếu ô kiểu hỗn hợp trong việc gán tên cho ô chứa danh sách thả xuống chính của bạn.

Nếu mục đích là chép danh sách thả xuống sang các cột khác (ví dụ như ở phía bên phải trên bản), bạn nên dùng tham chiếu tương đối (không kèm $) cho cột và tham chiếu tuyệt đối (kèm $) cho hàng, ví dụ = Sheet2! B$1.

Như vậy, danh sách thả xuống liên quan đến B1 sẽ xuất hiện tại B2; danh sách của C1 sẽ hiển thị tại C2, và cứ thế tiếp tục.

Sao chép danh sách thả xuống phụ thuộc sang các cột khác

Và nếu bạn muốn chép danh sách thả xuống đến các hàng khác (chẳng hạn như xuống dưới trong cột), hãy dùng tham chiếu tuyệt đối cho cột (kèm $) và tham chiếu tương đối cho hàng (không kèm $), ví dụ = Sheet2! $ B1.

Sao chép danh sách thả xuống phụ thuộc sang các hàng khác

2.3. Gán tên cho các phần tử trong danh sách phụ thuộc.

Khác với việc tạo tên cố định cho từng danh sách phụ thuộc như cách làm đã trình bày ở ví dụ trước, chúng ta sẽ tạo tên dưới dạng công thức mà không cần liên kết với bất kỳ ô cụ thể nào. Công thức này sẽ xác định danh sách các phần tử của danh sách thả xuống thứ hai dựa vào lựa chọn trong danh sách đầu tiên. Ưu điểm này giúp bạn không cần phải đặt tên mới cho mỗi mục mới được thêm vào danh sách thả xuống đầu tiên — một công thức đơn lẻ sẽ bao gồm tất cả mọi thứ.

Bạn tạo một tên mới trên Excel theo cách thôngThường (Công Thức > Quản Lý Tên > Tạo Mới) với công thức sau đây:

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

Trong đó:

  • exporters_tbl – tên bảng (tạo trong Bước 1);
  • fruit – tên của ô chứa danh sách thả xuống đầu tiên (tạo trong Bước 2.2);
  • fruit_list – tên liên kết đến dò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ư được thể hiện trong hình dưới đây.

Rất tốt, bạn đã hoàn thành phần lớn công việc! Trước khi bước vào phần cuối cùng, bạn nên mở Quản Lý Tên (Ctrl + F3) để kiểm tra tên và tham chiếu:

Mở Quản Lý Tên và kiểm tra tên cùng tham chiếu3. Thiết lập kiểm tra xác nhận dữ liệu trong Excel

Phần này thực sự rất dễ hiểu. Với hai tên công thức đã tạo, bạn cần thiết lập xác nhận dữ liệu theo cách bình thường (Tab Dữ liệu > Xác nhận dữ liệu).

    • Với lựa chọn thứ nhất, bạn hãy gõ = fruit_list vào ô Nguồn, đó là tên đã được tạo ở bước 2.1.
    • Đối với lựa chọn phụ, hãy điền vào = exporters_list, cái tên mà bạn đã đặt ở bước 2.3.

    Setting up Excel Data Validation for the dynamic cascading drop-down list

    Chúc mừng! Bây giờ danh sách lựa chọn linh hoạt của bạn đã được hoàn thành và nó sẽ tự động cập nhật mỗi khi có thay đổi từ bảng nguồn.

    .Dynamic cascading drop down menu in Excel

    Danh sách đổ xuống linh hoạt trên Excel này tuyệt vời đấy, nhưng có một nhược điểm khi các cột bảng nguồn chứa nhiều mục không giống nhau, các dòng trống sẽ hiện lên trong menu như hình dưới đây:

    Blank rows appear in the dependent drop-down menu.

    Giải pháp để loại bỏ dòng trống từ danh sách động

    Nếu bạn không muốn có dòng trống trong các lựa chọn của mình, cần một công thức INDEX/MATCH nâng cấp hơn để loại bỏ chúng khỏi danh sách đổ xuống động.

    Mục tiêu là kết hợp hai hàm INDEX, một cho ô trên cùng bên trái và một cho ô dưới cùng bên phải của dải, hoặc sử dụng hàm OFFSET cùng với INDEX và COUNTA. Bạn làm theo các bước chi tiết sau đây:

    1. Đặt thêm một số tên mới.

    Mà thay vì một công thức phức tạp, hãy tạo ra vài tên với câu lệnh đơn giản sau:

    • Dùng tên col_num để tham chiếu số thứ tự cột được chọn: =MATCH(fruit,fruit_list,0)
    • Dùng tên whole_col để chỉ đến toàn bộ cột được chọn: =INDEX(exporters_tbl,,col_num)

    Trong đó, exporters_tbl là tên của bảng nguồn, fruit là tên ô chứa danh sách lựa chọn đầu tiên, và fruit_list là tên chỉ mục các hàng tiêu đề của bảng.

    2. Định nghĩa tên cho danh sách lựa chọn có tính phụ thuộc.

    Kế tiếp, để tạo tên mới cho danh sách lựa chọn phụ (chúng ta gọi là exporters_list2), hãy dùng một trong hai công thức dưới đây:

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

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

    3. Thực hiện thiết lập Data Validation.

    Sau cùng, hãy chọn ô chứa danh sách lựa chọn phụ và thực hiện cài đặt Data Validation bằng cách điền vào =exporters_list2 (tên đã tạo ở bước trên) trong ô Nguồn.

    Hình ảnh dưới đây minh họa danh sách đổ xuống động trên Excel mà không còn dòng trống nào.

    The dynamic cascading drop-down menu in Excel without any blank lines

    Lưu ý: Khi sử dụng danh sách đổ xuống được tạo từ công thức này, người dùng có thể sẽ thay đổi giá trị trong danh sách đầu sau khi chọn từ menu thứ hai, khiến cho các lựa chọn trong hai danh sách trở nên không khớp với nhau. Để ngăn chặn điều này, bạn có thể sử dụng một số hàm hoặc VBA để khóa danh sách đầu tiên sau khi đã chọn mục từ danh sách thứ hai.

    Có khá nhiều kiến thức phải không? Tất cả những kiến thức trên bạn hoàn toàn có thể học được qua khóa học SM90 – Excel từ cơ bản tới chuyên gia của Siêu Marketing. Khóa học này sẽ giúp bạn nắm vững kiến thức một cách chi tiết nhất. Đặc biệt, khóa học không giới hạn thời gian học, cho phép bạn học mọi lúc mọi nơi và tra cứu kiến thức một cách tiện lợi. Hiện tại có chương trình ưu đãi lớn dành cho các bạn đăng ký tham gia khóa học. Chi tiết xem tại đây:

    Trương Thành Tài

    [submission_id id-lien-he]

    0
      0
      Đơn hàng
      Đơn hàng trốngQuay lại Shop