06/04/2021, 14:51

Cách tạo drop-down list trong Excel - hủ thuật Excel

Trong bài này mình sẽ hướng dẫn các bạn cách tạo drop-down list trong Excel, giúp bạn có thể tạo ra một danh sách tùy chọn xổ xuống ở dạng list. Dropdown list trong Excel rất hữu ích nếu bạn muốn chắc chắn rằng người dùng chọn một mục từ danh sách, thay vì nhập các giá trị của riêng họ. Điều này ...

Trong bài này mình sẽ hướng dẫn các bạn cách tạo drop-down list trong Excel, giúp bạn có thể tạo ra một danh sách tùy chọn xổ xuống ở dạng list.

Dropdown list trong Excel rất hữu ích nếu bạn muốn chắc chắn rằng người dùng chọn một mục từ danh sách, thay vì nhập các giá trị của riêng họ. Điều này giúp dữ liệu của bạn luôn được đảm bảo đúng.

1. Tạo một dropdown list trong Excel

Để tạo dropdown list thì bạn thực hiện các thao tác dưới đây trong workspace của Excel.

Bước 1: Đầu tiên mình sẽ tạo một sheet mới để nhập danh sách các dữ liệu cho list, điều này giúp bảo vệ dữ liệu của bạn không bị chỉnh sửa lung tung, bằng cách đặt mật khẩu bảo vệ sheet.

items png

Bạn cũng có thể ẩn sheet 2 đi thay vì đặt mật khẩu bằng cách click chuột phải vào sheet 2 và chọn Hide.

Bước 2: Tại sheet 1, bạn hãy chọn cell muốn hiển thị list, như trong hình dưới đây là mình chọn B1.

select cell png

Bước 3: Vào tab Data và chọn Data Validation.

click data validation png

Một hộp thoại xuất hiện, đây là nơi dùng để tạo ra những ràng buộc dữ liệu cho Excel.

Bước 4: Trong mục Allow bạn hãy chọn List, sau đó nhập nguồn trỏ đến Sheet 2 và range dữ liệu từ A1:A3. Lưu ý phải có dấu đô la nhé.

=Sheet2!$A%$1:$A$3

validation criteria png

Bước 5: Click OK là xong, lúc này bạn sẽ thấy một danh sách xổ xuống như hình dưới đây.

drop down list png

* Lưu ý: Bạn có thể copy dropdown list này sang một vị trí khác bằng cách click vào dropdown và nhấn Ctr + c, xong qua cell khác nhấn Ctr + v.

Ngoài ra bạn cũng có thể nhập danh sách giá trị trong ô source thay vì trỏ đến sheet 2 bằng cách nhập các giá trị vào và cách nhau bởi dấu phẩy.

simple drop down list png

Như trong hình này mình đã tạo hai giá trị là YesNo. Lưu ý là nó có phân biệt chữ hoa chữ thường đấy nhé, nếu bạn cố tình nhập yes thì nó sẽ báo lỗi ngay.

2. Chấp nhận một giá trị khác ngoài list

Trường hợp bạn muốn người dùng có thể nhập một giá trị khác trong danh sách của dropdown list thì có thể sử dụng cách sau.

Bước 1: Hãy nhập một giá trị bất kì không có trong list, lúc này một hộp thoại xuất hiện, điều này nó cảnh báo là bạn đã nhập một giá trị không tồn tại trong danh sách.

error alert png

Bước 2: Giữ nguyên con trỏ trong cell B1. Trong tab Data, chọn Data Validation.

click data validation png

Một hộp thoại xuất hiện.

Bước 3: Trong tab Error Alertm bạn hãy bỏ dấu check trong dòng "Show error alert after invalid data is entered".

allow other entries png

Bước 4: Click OK, và bây giờ bạn có thể nhập một giá trị bất kì ngoài danh sách.

manual input png

3. Thêm / xóa phần tử khỏi danh sách

Bạn có thể bổ sung hoặc xóa item ra khỏi droplist trong Excel bằng cách thực hiện các thao tác dưới đây.

Thêm phần tử mới

Bước 1: Để thêm một item thì hãy chuyển qua Sheet 2 (nơi chứa danh sách dữ liệu), chọn 1 item bất kì.

select item png

Bước 2: Click chuột phải, chọn Insert.

click insert png

Bước 3: Chọn "Shift cells down" và click OK.

shift cells down png

Bây giờ Excel sẽ tự động thay đổi range từ Sheet2!$A$1:$A$3 thành Sheet2!$A$1:$A$4. Bạn có thể vào hộp thoại Data Validation để kiểm tra.

new range reference png

Bước 4: Cuối cùng hãy nhập dữ liệu cho ô mà bạn vừa thêm đó.

type new item png

Chuyển qua Sheet 1 thì bạn sẽ thấy danh sách đã được thêm một phần tử mới.

updated drop down list png

Xóa phần tử

Để xóa một phần tử khỏi danh sách thì ta chỉ cần chọn phần tử đó, click chuột phải và chọn Delete, chọn "Shift cells up" và click OK.

4. Dynamic Drop-down List trong Excel

Cách lam trên gặp khó khăn khi thêm nhiều phần tử, vì vậy bạn có thể sử dụng cách dưới đây để tạo ra một dropdown list linh động hơn, nghĩa là bạn có thể xóa / thêm phần tử một cách thoải mái mà không cần các thao tác rườm rà đó.

Bước 1: Tại Sheet 1, chọn B1 (nơi mà bạn muốn hiển thị droplist)

select cell png

Bước 2: Cick vào Data Validation, một hộp thoại mới sẽ xuất hiện.

click data validation png

Bước 3: Trong mục Allow bạn hãy chọn List, trong mục Source bạn hãy đổi công thức lại thành:

=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)

offset function png

Giải thích: Hàm offset có 5 tham số truyền vào như sau:

  • Reference: Sheet2!$A$1
  • Rows to offset: 0
  • Columns to offset: 0
  • Height: COUNTA(Sheet2!$A:$A)
  • Width: 1

Công thức COUNTA(Sheet2!$A:$A) sẽ trả về số ô không rỗng trong cột A.

Như vậy mục đích của công thức này là khi bạn thêm / bớt phần tử thì nó sẽ cập nhật tự động. Bạn có thể thay cột A thành cột bất kì mà bạn đang sử dụng.

Bước 4: Click OK, sau đó qua Sheet 2 để thêm / bớt phần tử.

add new item png

Và tại Sheet 1 bạn cũng sẽ thấy dropdown list đã cập nhật số lượng phần tử tương ứng với sheet 2.

dynamic drop down list png

5. Xóa dropdown list trong Excel

Sau khi tạo dropdown list và bạn không muốn sử dụng đến nó nữa thì có thể thực hiện theo các bước dưới đây.

Bước 1: Chọn Dropdown list muốn xóa.

select cell with drop down list png

Bước 2: Trong tab Data, chọn Data Validation. Một hộp thoại sẽ xuất hiện.

click data validation png

Bước 3: Hãy click vào Clear All và nhấn OK.

click clear all png

Nếu bạn muốn xóa tất cả dropdown list thì có thể chọn vào option "Apply these changes to all other cells with the same settings".

Trên là hướng dẫn cách sử dụng dropdown list trong Excel. Đây là tính năng khá đơn giản nhưng lại rất hữu ích, giúp bạn lập trình ra những form nhập dữ liệu chuyên nghiệp.

Vũ Văn Thanh

12 chủ đề

2597 bài viết

Cùng chủ đề
0