Trong nội dung bài viết này, bạn tạo tìm hiểu bốn cách tạo list Data Validation Excel (drop-down list) khác nhau: dựa vào danh sách giá bán trị, phạm vi của ô, phạm vi được lấy tên và danh sách dropdown động. Đồng thời công ty chúng tôi cũng đã hướng dẫn chúng ta cách tạo list dropdown xuất phát từ một workbook khác, cũng tương tự cách chỉnh sửa và xóa danh sách Data Validation trong Excel.
Bạn đang xem: Tạo danh sách thả xuống có điều kiện
Drop-down các mục (danh sách thả xuống) của Excel, còn được gọi là hộp thả xuống hoặc vỏ hộp kết hợp, được sử dụng để nhập tài liệu vào bảng tính trường đoản cú danh sách các mục được xác minh trước. Mục đích chính của bài toán sử dụng danh sách thả xuống vào Excel là để hạn chế số lượng lựa chọn bao gồm sẵn cho những người dùng. Xung quanh ra, menu thả xuống còn ngăn chặn lỗi bao gồm tả cùng giúp nhập tài liệu nhanh hơn.
Cách chế tạo và sửa đổi danh sách thả xuống (drop-down list) tĩnh vào Excel
Có 4 cách để tạo menu thả xuống vào Excel, mỗi cách đều phải có ưu, điểm yếu kém riêng. Dưới đấy là hướng dẫn chi tiết cho từng phương pháp.
Đây là cách tạo vỏ hộp thả xuống trong toàn bộ các phiên bạn dạng Excel 2016, 2013, 2010, 2007 với 2003 nhanh nhất chỉ với 3 bước.
1. Chọn 1 ô hoặc dãy ô cho danh sách thả xuống của bạn. Nếu bạn có nhu cầu chọn những ô ko liền kề, bấm với giữ phím Ctrl trong khi chọn.
2. Sử dụng Excel Data Validation để tạo danh sách thả xuống. Trong Data tab > Data Tools group, kế tiếp lick Data Validation
3. Nhập các mục trong danh sách và chọn các tùy chọn.
Trong cửa sổ Data Validation, trên tab Settings, hãy triển khai như sau:
Trong vỏ hộp Allow, chọn ListTrong hộp Source, nhập các mục bạn muốn xuất hiện nay trong trình 1-1 thả xuống được phân tách bằng dấu phẩy (có hoặc không có dấu cách)Click chọn hộp In-cell dropdown; nếu không, mũi thương hiệu thả xuống sẽ không xuất hiện kề bên ôChọn hoặc hoặc quăng quật chọn Ignore blank tùy ở trong vào cách bạn có nhu cầu xử lý các ô trốngNhấp vào OK và bạn đã trả tất!Chỉnh sửa danh sách thả xuống được phân bóc tách bằng vết phẩy
Nếu chúng ta đã sinh sản một vỏ hộp thả xuống được phân bóc tách bằng dấu phẩy thì hãy thực hiện theo quá trình sau:
Chọn một ô hoặc các ô tham chiếu đến danh sách Data Validation Excel của bạn, có nghĩa là các ô chứa hộp thả xuống mà bạn muốn chỉnh sửaBấm Data Validation (Excel ribbon > Data tab)Xóa hoặc nhập các mục mới vào hộp Source.Bấm OK để lưu các chuyển đổi và đóng cửa sổ Data Validation ExcelNếu bạn muốn áp dụng các biến hóa cho tất cả các ô chứa danh sách thả xuống này thì nên lựa chọn tùy chọn "Apply these changes to all other cells with the same settings"
Tạo list thả xuống Excel dựa vào phạm vi đã đặt tên
1. Nhập những mục bạn có nhu cầu xuất hiện nay trong danh sách thả xuống của mình. Các giá trị này bắt buộc được nhập vào trong 1 cột hoặc một hàng mà lại không có bất kỳ ô trống nào.
2. Tạo nên một dải ô được để tên
Để tạo một Phạm vi được đặt tên (Named range) theo cách sau:
Chọn toàn bộ các mục bạn có nhu cầu đưa vào list thả xuống, bấm vào phải vào chúng và lựa chọn Define Name từ thực đơn ngữ cảnh. Quanh đó ra, bạn cũng có thể nhấp vào Name Manager trên tab bí quyết hoặc nhấnCtrl + F3.Trong vỏ hộp thoại Trình làm chủ tên, bấm NewTrong trường Name, hãy nhập tên cho các mục nhập của bạn, đảm bảo rằng phạm vi đúng đắn được hiển thị trong vỏ hộp Tham chiếu đến , kế tiếp bấm OK. Đảm bảo rằng tên phạm vi của doanh nghiệp không có ngẫu nhiên dấu cách hoặc che dấu gạch nối nào, chũm vào đó hãy áp dụng dấu gạch bên dưới (_)3. Chọn vị trí cho danh sách thả xuống của bạn
4. Áp dụng Excel Data Validation và thiết lập các setup phù hợp
Chỉnh sửa list thả xuống Excel dựa vào phạm vi đã đặt tên
Nếu bạn đã chế tạo một hộp thả xuống dựa vào phạm vi chọn cái tên thì chỉ việc chỉnh sửa những mục trong phạm vi của chính mình và sau đó biến đổi tham chiếu cho Named Range. Tất cả các hộp thả xuống dựa vào phạm vi chọn cái tên này sẽ được update tự động.
Thêm hoặc xóa các mục trong phạm vi đang đặt tên. Mở trang tính gồm chứa phạm vi đã đặt tên, xóa hoặc nhập các entry mới. Hãy nhớ sắp đến xếp các mục theo sản phẩm tự bạn muốn chúng xuất hiện trong danh sách thả xuống Excel của bạn
Thay đổi tham chiếu đến Named Range.
Trên thanh hiện tượng Excel, bạn phải chuyển đến tab Formulas tab > Name Manager hoặc nhấn tổ hợp Ctrl + F3 để mở cửa sổ Name Manager.
Trong hành lang cửa số Name Manager, nên lựa chọn phạm vi vẫn đặt tên mà bạn muốn cập nhật.
Thay thay đổi tham chiếu trong vỏ hộp Refers to bằng cách nhấp vào hình tượng Collapse Dialog cùng chọn tất cả các entry cho danh sách thả xuống của bạn.
Nhấp vào Close, kế tiếp trong thông báo chứng thực xuất hiện, click vào Yes nhằm lưu các thay đổi của bạn.
Để kiêng việc cập nhật các tham chiếu của dải ô đã đặt thương hiệu sau từng lần biến hóa danh sách nguồn, bạn cũng có thể tạo một thực đơn thả xuống Excel động. Trong trường hòa hợp này, list thả xuống của các bạn sẽ được cập nhật auto trong toàn bộ các ô được link ngay sau khi bạn xóa hoặc thêm các mục new vào danh sách.
Danh sách Excel data validation bằng table object
Thay vì sử dụng một dải ô được lấy tên thông thường, bạn cũng có thể chuyển đổi dữ liệu của bản thân mình sang một bảng Excel đầy đủ tính năng ( Insert > Table hoặc bấm Ctrl + T) và tiếp nối tạo danh sách xác thực tài liệu từ bảng đó.
Để có tác dụng điều này, các bạn nhập = your_table_name
Khi bạn tạo danh sách thả xuống theo phong cách này, mỗi khi chúng ta thêm một hàng new vào bảng, list thả xuống của bạn sẽ tự động hóa cập nhật.
Tạo một vỏ hộp thả xuống dựa vào một loạt các ô
Để tạo nên một vỏ hộp thả xuống dựa vào một dải ô, hãy thực hiện công việc sau:
Nhập các mục vào những ô riêng rẽ biệt.Chọn ô mà bạn có nhu cầu danh sách thả xuống xuất hiện.Trên tab Data tab, chọn Data Validation.Đặt bé trỏ vào vỏ hộp Source hoặc nhấp vào biểu tượng Collapse Dialog và lựa chọn phạm vi ô để đưa vào list thả xuống của bạn. Phạm vi rất có thể nằm trong cùng một hoặc trong một trang tính khác. Bạn chỉ cần chuyển đến trang tính khác và lựa chọn một phạm vi bởi chuột.
Chỉnh sửa thực đơn thả xuống dựa trên một loạt các ô
Nếu bạn đã chế tạo một hộp thả xuống bằng phương pháp chỉ định một dãy ô thay bởi tham chiếu một dải ô vẫn đặt tên thì hãy triển khai theo cách sau.
Đi cho tới bảng tính chứa những mục lộ diện trong vỏ hộp thả xuống của doanh nghiệp và chỉnh sửa danh sách theo phong cách bạn muốn.Chọn ô hoặc những ô có chứa list thả xuống của bạn.Nhấp vào Data Validation vào tab Data.Trong cửa sổ Data Validation Excel, trên tab Settings, bạn cần chuyển đổi tham chiếu ô trong vỏ hộp Source. Bạn cũng có thể chỉnh sửa theo cách bằng tay thủ công hoặc nhấp vào biểu tượng Collapse Dialog.Nhấp vào nút OK để lưu các đổi khác và tạm dừng hoạt động sổ.Xem thêm: Hàm Convert Text To Number Trong Excel 8/2021, Convert Text To Number In Excel
Tạo danh sách thả xuống Excel động (tự động cập nhật)
Nếu các bạn thường chỉnh sửa các mục trong trình solo thả xuống, bạn cũng có thể muốn tạo danh sách thả xuống đụng trong Excel. Trong trường thích hợp này, danh sách của các bạn sẽ được cập nhật tự động hóa trong tất cả các ô đựng nó, sau khi chúng ta xóa hoặc thêm các mục mới vào danh sách nguồn.
Cách tạo list thả xuống được update động trong Excel đơn giản nhất là chế tạo một danh sách được lấy tên dựa trên một bảng. Nếu bạn thích một dải ô được đặt tên thông thường hì hãy tham chiếu nó bằng công thức OFFSET theo công việc bên dưới:
Bắt đầu bằng phương pháp tạo một danh sách thả xuống thông thường dựa bên trên một phạm vi chọn cái tên như thể hiện ở trên.Ở cách 2, khi sản xuất tên, bạn đặt bí quyết sau vào hộp Refers to.= OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1).
Trong kia
Sheet1 - thương hiệu của trang tínhA - cột khu vực chứa các mục trong list thả xuống của bạn$A$1 - ô đựng mục thứ nhất của danh sáchNhư chúng ta thấy, bí quyết này bao gồm 2 hàm Excel là OFFSET và COUNTA. Hàm COUNTA đếm tất cả các ô trống trong cột được chỉ định, tiếp nối trả về một tham chiếu cho một phạm vi chứa các ô ko trống, bắt đầu từ ô đầu tiên bạn chỉ định và hướng dẫn trong công thức.
Tạo list thả xuống xuất phát điểm từ 1 workbook khác
Bạn hoàn toàn có thể tạo thực đơn thả xuống vào Excel bằng cách sử dụng danh sách từ một workbook khác có tác dụng nguồn. Để tiến hành việc này, bạn sẽ phải chế tạo ra 2 phạm vi chọn cái tên - 1 trong các sách nguồn và một trong các sách mà bạn muốn sử dụng danh sách Excel Data Validation của mình.
Danh sách thả xuống được tạo theo cách này đã không tự động hóa cập nhật khi bạn thêm hoặc xóa những entry trong danh sách nguồn và các bạn sẽ phải sửa đổi phần list nguồn theo phong cách thủ công.
Mở workbook tất cả chứa danh sách nguồn, sống ví dụ này bọn họ sẽ lựa chọn SourceBook.xlsx, tiếp nối tạo một phạm vi đang đặt tên cho các entry mà bạn có nhu cầu đưa vào danh sách thả xuống của mình, ví dụ: Source_list.
Tạo tên phạm vi trong workbook Source bằng công thức OFFSET tương tự như chúng ta đã triển khai ở phần Tạo menu thả xuống Excel đụng (tự động cập nhật, chỉ không giống ở phạm vi được rước từ workbook khác.Trong workbook chính, bạn chỉ việc áp dụng Data Validation theo cách thông thường.
Một số điểm cần chú ý khi tạo danh sách thả xuống vào Excel
Xử lý lỗi Data Validation không hoạt động
Bạn tất yêu tạo list thả xuống vì tùy lựa chọn Data Validation của khách hàng bị tắt hoặc bị vô hiệu hóa hóa? Điều này có thể xảy ra do một số nguyên nhân:
Không thể thêm list thả xuống vào trang tính được bảo đảm an toàn hoặc chia sẻ. Vào trường hòa hợp này, bạn cần loại bỏ đảm bảo hoặc ngừng chia sẻ trang tính, tiếp nối thử nhấp vào Data Validation một đợt nữa.Bạn vẫn tạo danh sách thả xuống từ bỏ bảng Excel được liên kết với trang SharePoint. Nếu như vậy bạn cần hủy link bảng hoặc xóa format bảng và thử lại.Các tùy chọn bổ sung cập nhật cho hộp thả xuống Excel
Trong hầu như các trường hợp, những tùy chọn của tab Settings mà chúng ta đã đàm luận ở bên trên là trọn vẹn đầy đủ. Nếu không vẫn có hai tùy chọn khác gồm sẵn trên các tab không giống của cửa sổ hộp thoại Data Validation.
Cho phép người dùng nhập dữ liệu vào combo box
Theo mặc định, list thả xuống bạn tạo trong Excel là khoác định và chẳng thể chỉnh sửa, do đó những giá trị trong danh sách cũng bị hạn chế. Tuy nhiên, chúng ta cũng có thể cho phép người tiêu dùng nhập các giá trị của riêng họ vào hộp.
Về phương diện kỹ thuật, điều này biến list thả xuống thành một hộp tổ hợp Excel. Thuật ngữ "combo box" có nghĩa là một list thả xuống có thể chỉnh sửa cho phép người dùng lựa chọn 1 giá trị từ list hoặc nhập một cực hiếm trực tiếp vào hộp.
Trong hộp thoại Data Validation (Data tab > Data Validation), bạn cần chuyển mang đến tab Error Alert.Chọn vỏ hộp "Show error alert after invalid data is entered" nếu bạn có nhu cầu hiển thị cảnh báo khi tín đồ dùng cố gắng nhập một trong những dữ liệu không tồn tại trong thực đơn thả xuống. Nếu bạn không muốn hiển thị thông báo này thì rất có thể bỏ chọn.Nếu bạn muốn hiển thị thông tin cảnh báo, hãy chọn một trong các tùy chọn từ vỏ hộp Style rồi nhập tiêu đề và thông báo. Hoặc chúng ta có thể chọn Information hoặc Warning để có thể chấp nhận được người áp dụng nhập văn bản trong hộp combo.Một thông tin Information đã hiển thị nếu người dùng của bạn có tác dụng nhập nhiều lựa chọn của bản thân khá thường xuyên xuyên.Một thông báo Warning đã hiển thị khi tín đồ dùng chọn 1 mục từ hộp thả xuống thay vày nhập tài liệu của riêng họ, mặc dù nó không cấm các entry tùy chỉnh.Stop (mặc định) đã ngăn người dùng nhập bất kỳ dữ liệu nào không có trong danh sách thả xuống Excel của bạn.nếu như khách hàng không biết cần nhập title hoặc nội dung thông báo nào thì hoàn toàn có thể để trống các trường. Trong trường hợp này, Microsoft Excel đang hiển thị cảnh báo mặc định "The value you entered is not valid. A user has restricted values that can be entered into this cell".
Cách xóa danh sách thả xuống trong Excel
Nếu bạn không thích có những hộp thả xuống vào trang tính Excel của bản thân nữa thì có thể xóa những hộp thả xuống này khỏi một vài hoặc tất cả các ô.
Chọn một ô hoặc một trong những ô mà bạn muốn xóa các hộp thả xuống.Chuyển đến tab Data và nhấp vào Data Validation.Trên tab Settings, bạn phải chọn nút Clear All.
Phương pháp này sẽ thải trừ các menu thả xuống khỏi các ô đã chọn nhưng vẫn giữ các giá trị hiện đang được chọn.
Nếu bạn muốn xóa cả list thả xuống và quý giá của ô thì nên chọn các ô và nhấp vào nút Clear trên home tab > Editing group > Clear.
Xóa danh sách thả xuống Excel khỏi toàn bộ các ô trong trang tính hiện nay tại
Bạn rất có thể làm theo các bước dưới đây để xóa danh sách thả xuống khỏi tất cả các ô được liên kết trong trang tính hiện tại. Thao tác làm việc này sẽ không xóa cùng một hộp thả xuống khỏi những ô trong số trang tính khác giả dụ có.
Chọn ngẫu nhiên ô làm sao chứa danh sách thả xuống của bạn.Nhấp vào Data Validation vào tab Data.Trong cửa sổ Data Validation, bên trên tab Settings, bạn cần chọn hộp kiểm "Apply these changes to all other cells with the same settings". Sau khi chọn, tất cả các ô tham chiếu đến danh sách Data Validation Excel này sẽ tiến hành chọn, chúng ta cũng có thể quan sát ảnh chụp màn hình bên dưới.Nhấp vào nút Clear All nhằm xóa danh sách thả xuống.Bấm OK nhằm lưu các chuyển đổi và ngừng hoạt động sổ Data Validation.