Trong bài viết này, bạn tạo khám phá bốn cách tạo danh sách Data Validation Excel (drop-down list) khác nhau: dựa trên danh sách giá trị, phạm vi của ô, phạm vi được đặt tên và danh sách dropdown động. Đồng thời chúng tôi cũng sẽ hướng dẫn bạn cách tạo danh sách dropdown từ một workbook khác, cũng như cách chỉnh sửa và xóa danh sách Data Validation trong Excel.
Drop-down list (danh sách thả xuống) của Excel, còn gọi là hộp thả xuống hoặc hộp kết hợp, được sử dụng để nhập dữ liệu vào bảng tính từ danh sách các mục được xác định trước. Mục đích chính của việc sử dụng danh sách thả xuống trong Excel là để hạn chế số lượng lựa chọn có sẵn cho người dùng. Ngoài ra, menu thả xuống còn ngăn chặn lỗi chính tả và giúp nhập dữ liệu nhanh hơn.
Cách tạo và chỉnh sửa danh sách thả xuống (drop-down list) tĩnh trong Excel
Có 4 cách để tạo menu thả xuống trong Excel, mỗi cách đều có ưu, nhược điểm riêng. Dưới đây là hướng dẫn chi tiết cho từng phương pháp.
Xem thêm khóa học onlineThủ thuật Excel cập nhật hàng tuần cho dân văn phòngcực kỳ hữu ích, giúp các bạn làm việc trở nên thông minh và chuyên nghiệp trong mắt bạn bè...
Tạo danh sách thả xuống với các giá trị được phân tách bằng dấu phẩy
Đây là cách tạo hộp thả xuống trong tất cả các phiên bản Excel 2016, 2013, 2010, 2007 và 2003 nhanh nhất chỉ với 3 bước.
1. Chọn một ô hoặc dãy ô cho danh sách thả xuống của bạn. Nếu bạn muốn chọn nhiều ô không liền kề, bấm và giữ phím Ctrl trong khi chọn.
2. Sử dụng Excel Data Validation để tạo danh sách thả xuống. TrongData tab > Data Tools group, sau đó 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 thực hiện như sau:
Chỉnh sửa danh sách thả xuống được phân tách bằng dấu phẩy
Nếu bạn đã tạo một hộp thả xuống được phân tách bằng dấu phẩy thì hãy thực hiện theo các bước sau:
- Chọn một ô hoặc các ô tham chiếu đến danh sách Data Validation Excel của bạn, tức là các ô chứa hộp thả xuống mà bạn muốn chỉnh sửa
- Bấ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 thay đổi và đóng cửa sổ Data Validation Excel
Nếu bạn muốn áp dụng các thay đổi cho tất cả các ô chứa danh sách thả xuống này thì hãy chọn tùy chọn "Apply these changes to all other cells with the same settings"
Tạo danh sách thả xuống Excel dựa trên phạm vi đã đặt tên
1. Nhập các mục bạn muốn xuất hiện trong danh sách thả xuống của mình. Các giá trị này phải được nhập vào một cột hoặc một hàng mà không có bất kỳ ô trống nào.
2. Tạo một dải ô được đặt tên
Để tạo một Phạm vi được đặt tên (Named range) theo cách sau:
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 cài đặt phù hợp
Chỉnh sửa danh sách thả xuống Excel dựa trên phạm vi đã đặt tên
Nếu bạn đã tạo một hộp thả xuống dựa trên phạm vi được đặt tên thì chỉ cần chỉnh sửa các mục trong phạm vi của mình và sau đó thay đổi tham chiếu đến Named Range. Tất cả các hộp thả xuống dựa trên phạm vi được đặt tên này sẽ được cập nhật tự động.
- Thêm hoặc xóa các mục trong phạm vi đã đặt tên. Mở trang tính có chứa phạm vi đã đặt tên, xóa hoặc nhập các entry mới. Hãy nhớ sắp xếp các mục theo thứ 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.
Danh sách Excel data validation bằng table object
Thay vì sử dụng một dải ô được đặt tên thông thường, bạn có thể chuyển đổi dữ liệu của mình sang một bảng Excel đầy đủ chức năng (Insert > Table hoặc bấm Ctrl + T) và sau đó tạo danh sách xác thực dữ liệu từ bảng đó.
Để làm điều này, bạn nhập = your_table_name [column_name] vào trường Refers to field hoặc chọn tất cả các ô không có tiêu đề cột trước khi mở Name Manager và tự động điền hộp Refers to.
Khi bạn tạo danh sách thả xuống theo cách này, mỗi khi bạn thêm một hàng mới vào bảng, danh sách thả xuống của bạn sẽ tự động cập nhật.
Tạo một hộp thả xuống dựa trên một loạt các ô
Để tạo một hộp thả xuống dựa trên một dải ô, hãy thực hiện các bước sau:
Đặt con trỏ vào hộp Source hoặc nhấp vào biểu tượngCollapse Dialog và chọn phạm vi ô để đưa vào danh sách thả xuống của bạn. Phạm vi 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à chọn một phạm vi bằng chuột.
Chỉnh sửa menu thả xuống dựa trên một loạt các ô
Nếu bạn đã tạo một hộp thả xuống bằng cách chỉ định một dãy ô thay vì tham chiếu một dải ô đã đặt tên thì hãy tiến hành theo cách sau.
- Đi tới bảng tính chứa các mục xuất hiện trong hộp thả xuống của bạn và chỉnh sửa danh sách theo cách bạn muốn.
- Chọn ô hoặc các ô có chứa danh sách thả xuống của bạn.
- Nhấp vào Data Validation trong tab Data.
- Trong cửa sổ Data Validation Excel, trên tab Settings, bạn cần thay đổi tham chiếu ô trong hộp Source. Bạn có thể chỉnh sửa theo cách 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 thay đổi và đóng cửa sổ.
Tạo danh sách thả xuống Excel động (tự động cập nhật)
Nếu bạn thường chỉnh sửa các mục trong trình đơn thả xuống, bạn có thể muốn tạo danh sách thả xuống động trong Excel. Trong trường hợp này, danh sách của bạn sẽ được cập nhật tự động trong tất cả các ô chứa nó, sau khi bạn xóa hoặc thêm các mục mới vào danh sách nguồn.
Cách tạo danh sách thả xuống được cập nhật động trong Excel đơn giản nhất là tạo một danh sách được đặt 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ác bước bên dưới:
- Bắt đầu bằng cách tạo một danh sách thả xuống thông thường dựa trên một phạm vi được đặt tên như mô tả ở trên.
- Ở bước 2, khi tạo tên, bạn đặt công thức sau vào hộp Refers to.
= OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1).
Trong đó
Như bạn thấy, công thức 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, sau đó trả về một tham chiếu đến một phạm vi chứa các ô không trống, bắt đầu từ ô đầu tiên bạn chỉ định trong công thức.
Tạo danh sách thả xuống từ một workbook khác
Tạo danh sách thả xuống tĩnh từ một workbook khác
Danh sách thả xuống động từ một workbook khác
- Tạo tên phạm vi trong workbook Source bằng công thức OFFSET giống như chúng ta đã thực hiện ở phầnTạo menu thả xuống Excel động (tự động cập nhật, chỉ khác ở phạm vi được lấy từworkbook khác.
- Trong workbook chính, bạn chỉ cần áp dụng Data Validation theo cách thông thường.
Một số điểm cần lưu ý khi tạo danh sách thả xuống trong Excel
Xử lý lỗiData Validation không hoạt động
Bạn không thể tạo danh sách thả xuống do tùy chọn Data Validation của bạn bị tắt hoặc bị vô hiệu hóa? Điều này có thể xảy ra do một số nguyên nhân:
Các tùy chọn bổ sung cho hộp thả xuống Excel
Hiển thị thông báo khi nhấp vào một ô có menu thả xuống
Cho phép người dùng nhập dữ liệu vào combo box
Theo mặc định, danh sách thả xuống bạn tạo trong Excel là mặc định và không thể chỉnh sửa, do đó các giá trị trong danh sách cũng bị hạn chế. Tuy nhiên, bạn có thể cho phép người dùng nhập các giá trị của riêng họ vào hộp.
Về mặt kỹ thuật, điều này biến danh sách thả xuống thành một hộp tổ hợp Excel. Thuật ngữ "combo box" có nghĩa là một danh sách thả xuống có thể chỉnh sửa cho phép người dùng chọn một giá trị từ danh sách hoặc nhập một giá trị 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 đến tab Error Alert.
- Chọn hộp "Show error alert after invalid data is entered" nếu bạn muốn hiển thị cảnh báo khi người dùng cố gắng nhập một số dữ liệu không có trong menu thả xuống. Nếu bạn không muốn hiển thị thông báo này thì có thể bỏ chọn.
- Nếu bạn muốn hiển thị thông báo cảnh báo, hãy chọn một trong các tùy chọn từ hộp Style rồi nhập tiêu đề và thông báo. Hoặc bạn có thể chọn Information hoặc Warning để cho phép người sử dụng nhập văn bản trong hộp combo.
- Nếu bạn không biết nên nhập tiêu đề hoặc nội dung thông báo nào thì có thể để trống các trường. Trong trường hợp này, Microsoft Excel sẽ 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 muốn có các hộp thả xuống trong trang tính Excel của mình nữa thì có thể xóa các hộp thả xuống này khỏi một số hoặc tất cả các ô.
Xóa danh sách thả xuống khỏi (các) ô đã chọn
Phương pháp này sẽ loại bỏ 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ả danh sách thả xuống và giá trị của ô thì hãy 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 tất cả các ô trong trang tính hiện tại
Bạn 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 này sẽ không xóa cùng một hộp thả xuống khỏi các ô trong các trang tính khác nếu có.
- Chọn bất kỳ ô nào chứa danh sách thả xuống của bạn.
- Nhấp vào Data Validation trong tab Data.
- Trong cửa sổ Data Validation, 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ẽ được chọn, bạn có thể quan sát ảnh chụp màn hình bên dưới.
- Nhấp vào nút Clear All để xóa danh sách thả xuống.
- Bấm OK để lưu các thay đổi và đóng cửa sổ Data Validation.