Thành Thạo Danh Sách Thả Xuống Excel: Tạo, Sửa, Xóa, Lọc & Bảo Vệ

Tập dữ liệu trình bày thông tin mua sắm trực tuyến: 'Mã Đơn', 'Tên Khách', 'Thiết Bị', 'Ngày Đặt', 'Ngày Giao', và 'Phương Thức Thanh Toán'.

Danh Sách Thả Xuống Trong Excel Là Gì?

Danh sách thả xuống là tính năng cho phép người dùng chọn một phần tử từ danh sách các tùy chọn.

Cách Tạo Danh Sách Thả Xuống Trong Excel?

1. Tạo Danh Sách Thả Xuống Từ Phạm Vi Ô

Để tạo danh sách thả xuống dựa trên giá trị trong cột B:

• Chọn một ô (C18).
• Đi đến tab Dữ Liệu và chọn Xác Thực Dữ Liệu.

• Trong Xác Thực Dữ Liệu, đi đến Cài Đặt.
• Trong Danh Sách, chọn Cho Phép.
• Định nghĩa phạm vi ($B$6:$B$15) trong Nguồn.
• Nhấp OK.

Đây là kết quả.

• Nhập công thức sau trong C19 để có giá trị từ cột C, dựa trên lựa chọn Mã Đơn.

=VLOOKUP($C$18,$B$6:$G$15,ROW(B19)-ROW($B$18)+1,FALSE)

• Dùng Fill Handle để trích xuất giá trị.

2. Tạo Danh Sách Thả Xuống Từ Bảng

• Chọn phạm vi ô và đi đến tab Chèn để tạo bảng.
• Nhấp Bảng.

• Tích Bảng của tôi có tiêu đề.
• Nhấp OK.

• Để tạo danh sách thả xuống trong C18 với dữ liệu trong Tên Khách, chọn Danh Sách trong Cho Phép.
• Nhập công thức sau với hàm INDIRECT trong Nguồn và nhấp OK.

=INDIRECT("Table1[Customer Name]")

• Nhập công thức sau trong C19 để trích xuất tên thiết bị dựa trên lựa chọn tên khách.

=INDEX(Table1,MATCH(C18,Table1[Customer Name],0),3)

Đây là kết quả.

3. Tạo Danh Sách Thả Xuống Với Phạm Vi Đặt Tên

• Chọn phạm vi ô và đi đến tab Công Thức.
• Nhấp Đặt Tên.

• Đặt tên (ID) trong Tên và định nghĩa phạm vi trong Tham Chiếu Đến.

• Tạo danh sách thả xuống mã đơn trong C19 bằng cách nhập công thức sau trong Nguồn:

• Dùng công thức sau trong C19 để có tên khách dựa trên lựa chọn mã đơn.

=INDEX(B6:G15,MATCH($C$18,ID,0),2)

• Dùng công thức sau để có tên thiết bị trong C20.

=INDEX(B6:G15,MATCH($C$18,ID,0),3)

Đây là kết quả.

4. Tạo Danh Sách Thả Xuống Có Màu

Định nghĩa màu tùy chỉnh với Định Dạng Có Điều Kiện cho mỗi giá trị của danh sách thả xuống.

• Chọn Danh Sách trong Cho Phép.
• Nhập tên màu cách nhau bằng dấu phẩy (,): (Đen,Trắng,Bạc,Vàng) trong Nguồn.

• Để định nghĩa màu cho từng phần tử của thả xuống, chọn thả xuống và đi đến Định Dạng Có Điều Kiện trong tab Trang Chủ.
• Đi đến Quy Tắc Làm Nổi Ô và nhấp Bằng…

• Trong Bằng, nhập tên màu (Đen).
• Chọn Định Dạng Tùy Chỉnh để đặt màu cho tên màu.

• Chọn màu trong Fill (Nền).

• Bạn cũng có thể đổi màu font trong Font. Ở đây, trắng.

• Nhấp OK.

• Đặt màu cho các tùy chọn khác.

Danh sách thả xuống hiển thị màu dựa trên giá trị ô.

Màu sẽ tự động thay đổi dựa trên lựa chọn.

• Dùng Fill Handle để Tự Động Điền danh sách thả xuống.

5. Tạo Danh Sách Thả Xuống Với Giá Trị Duy Nhất

Tạo danh sách thả xuống với giá trị duy nhất trong cột D và E.

• Dùng công thức sau trong B22 để thấy tên thương hiệu duy nhất.

• Nhập công thức sau để tạo danh sách thả xuống trong C5 với giá trị duy nhất.

• Nhập công thức sau trong C22 để thấy giá trị duy nhất trong cột E khớp với thương hiệu trong C5.

=FILTER(E9:E18,C5=D9:D18)

• Đi đến Xác Thực Dữ Liệu.
• Dùng công thức sau trong Nguồn.

Đây là kết quả.

6. Tạo Danh Sách Thả Xuống Dựa Trên Dữ Liệu Từ Sheet Khác

Thương hiệu mobile và mẫu điện thoại ở worksheet PhoneList.

Dùng tên thương hiệu và mẫu điện thoại để tạo hai danh sách thả xuống trong worksheet khác (AnotherSheet).

• Để tạo danh sách thả xuống tên thương hiệu trong D6 với dữ liệu trong worksheet PhoneList, nhập công thức sau trong Nguồn.

• Dùng Fill Handle để tạo cùng danh sách thả xuống trong cột D.
• Dùng công thức sau với hàm INDIRECT trong Nguồn để có mẫu điện thoại từ worksheet PhoneList trong thả xuống dựa trên thương hiệu cột D.

Nhấp vào đây để phóng to hình ảnh.

Đây là kết quả.

7. Tạo Danh Sách Thả Xuống Dựa Trên Dữ Liệu Từ Workbook Khác

Trong workbook AvailablePaymentMethods, có danh sách phương thức thanh toán mua sắm trực tuyến.

• Đi đến Công Thức và chọn Đặt Tên.

• Trong Tên Mới, đặt tên (PayType) trong Tên.
• Định nghĩa phạm vi (Sheet1!$B$3:$B$7) trong Tham Chiếu Đến.
• Nhấp OK.

• Đi đến workbook danh sách thả xuống Excel và đặt phạm vi đặt tên (PayType).
• Nhập [AvailablePaymentMethods.xlsx]Sheet1!PayType để định nghĩa phạm vi tên trong workbook AvailablePaymentMethods.
• Nhập công thức sau trong Nguồn để tạo danh sách thả xuống với phạm vi tên trong G6.

• Bạn có thể chọn phương thức thanh toán từ danh sách thả xuống.

• Dùng Fill Handle để Tự Động Điền danh sách thả xuống.

Lưu Ý

i. Cả hai workbook phải mở khi tạo danh sách thả xuống.
ii. Đừng tạo nhiều danh sách thả xuống phụ thuộc từ workbook khác.

8. Tạo Danh Sách Thả Xuống Phụ Thuộc

• Để tạo danh sách thả xuống độc lập thương hiệu trong D6, nhập công thức sau trong Nguồn (Xác Thực Dữ Liệu).

• Để tạo danh sách thả xuống phụ thuộc mẫu điện thoại trong E6, nhập công thức sau với hàm INDIRECT trong Nguồn.

Nhấp vào đây để phóng to hình ảnh.

Danh sách thả xuống phụ thuộc dựa trên lựa chọn thương hiệu từ danh sách thả xuống độc lập được tạo.

• Dùng Fill Handle để Tự Động Điền các danh sách thả xuống.

9. Tạo Danh Sách Thả Xuống Có Tìm Kiếm

• Chèn cột mới (G và H) để có tên thương hiệu và mẫu duy nhất. Dùng công thức sau trong G6.

=UNIQUE(OFFSET(D6,0,0,COUNTA(D:D)-1,1))

• Nhập công thức sau trong Nguồn để tạo danh sách thả xuống có tìm kiếm trong C19 với tên thương hiệu.

Bạn sẽ có thể tìm kiếm tên thương hiệu trong C19.

• Nhập công thức sau trong H6 để thấy mẫu điện thoại dựa trên lựa chọn thương hiệu của thả xuống có tìm kiếm.

=UNIQUE(FILTER(E6:E17,C19=D6:D17))

• Nhập công thức sau trong Nguồn để tạo danh sách thả xuống có tìm kiếm trong C20 với tên mẫu điện thoại.

Bạn có danh sách thả xuống độc lập và phụ thuộc có tìm kiếm.

10. Tạo Danh Sách Thả Xuống Động Để Thêm Và Tự Động Cập Nhật Mục Mới

Để trích xuất giá trị dựa trên ID.

Nhấp vào đây để phóng to hình ảnh.

• Chèn cột riêng và nhập công thức sau trong L6 để thấy ID duy nhất dựa trên giá trị cột B.

=UNIQUE(OFFSET(B6,0,0,COUNTA(B:B)-3,1))

Nhấp vào đây để phóng to hình ảnh.

• Tạo danh sách thả xuống với ID duy nhất trong J5 bằng cách nhập công thức sau trong Nguồn.

Nhấp vào đây để phóng to hình ảnh.

• Chọn ID từ danh sách thả xuống và dùng công thức sau trong J6 để thấy tên khách cho ID đã chọn.

=VLOOKUP($J$5,B:G,ROW(I6)-ROW($I$5)+1,FALSE)

• Dùng Fill Handle để Tự Động Điền phần còn lại giá trị.

Nhấp vào đây để phóng to hình ảnh.

• Ngày trích xuất sẽ hiển thị như số nguyên. Đổi định dạng số thành Ngày Ngắn hoặc Ngày Dài trong Định Dạng Số.

• Thêm ID mới và giá trị liên quan. Chúng sẽ tự động cập nhật trong danh sách thả xuống và cột L.

Nhấp vào đây để phóng to hình ảnh.

Đây là kết quả.

11. Tạo Danh Sách Thả Xuống Có Tìm Kiếm, Động, Và Đa Phụ Thuộc

• Nhập công thức sau trong L6.

=UNIQUE(OFFSET(D6,0,0,COUNTA(D:D)-1,1))

• Tạo danh sách thả xuống độc lập có tìm kiếm tên thương hiệu trong J5 bằng cách nhập công thức sau trong Nguồn.

• Lọc mẫu điện thoại dựa trên thương hiệu chọn từ danh sách thả xuống trong J5. Dùng công thức sau.

=UNIQUE(FILTER(E:E,J5=D:D))

• Tạo danh sách thả xuống phụ thuộc thương hiệu có tìm kiếm tên mẫu trong J6. Nhập công thức sau trong Nguồn.

• Lọc chipset dựa trên mẫu chọn từ danh sách thả xuống trong J6. Dùng công thức sau.

=UNIQUE(FILTER(F:F,J6=E:E))

• Nhập công thức sau trong Nguồn để tạo danh sách thả xuống phụ thuộc mẫu có tìm kiếm chipset trong J7.

• Dùng công thức sau trong J8 để tìm giá sản phẩm khớp giá trị đã chọn.

=INDEX(G:G,MATCH(1,(J5=D:D)*(J6=E:E)*(J7=F:F),0))

Vì tất cả danh sách thả xuống đều động, bạn có thể thêm mục mới và danh sách thả xuống sẽ cập nhật.

12. Tạo Danh Sách Thả Xuống Với Thông Báo Tùy Chỉnh

• Tạo danh sách thả xuống phụ thuộc mẫu điện thoại, như mô tả trong Phương Pháp 7.

• Trong Xác Thực Dữ Liệu, đi đến Thông Báo Nhập.
• Tích Hiển thị thông báo nhập khi ô được chọn.
• Đặt tiêu đề trong Tiêu Đề và thông báo tùy chỉnh trong Thông Báo Nhập.
• Nhấp OK.

Thông báo tùy chỉnh sẽ hiển thị khi ô được chọn.

13. Tạo Danh Sách Thả Xuống Có Thể Chỉnh Sửa Với Thông Báo Cảnh Báo Lỗi

• Nhập công thức sau trong G6 để tạo cột động với tên thương hiệu duy nhất.

=UNIQUE(OFFSET(D6,0,0,COUNTA(D:D)-1,1))

• Tạo danh sách thả xuống động có tìm kiếm tên thương hiệu trong C19 bằng cách nhập công thức sau trong Nguồn.

• Để tạo danh sách thả xuống có thể chỉnh sửa, đi đến tab Cảnh Báo Lỗi.
• Tích ô Hiển thị cảnh báo lỗi sau khi dữ liệu không hợp lệ được nhập.
• Đặt kiểu, tiêu đề, và thông báo lỗi cho giá trị tìm kiếm sai và nhấp OK.

• Tạo danh sách thả xuống với giá trị trong Model.

Nếu bạn nhập đầu vào sai trong các danh sách thả xuống có thể chỉnh sửa đó, cảnh báo lỗi sẽ hiển thị với tùy chọn thử lại hoặc hủy.

Cách Thêm Hoặc Xóa Mục Từ Danh Sách Thả Xuống Excel?

Bạn có thể thêm hoặc xóa mục bằng cách thêm hoặc xóa ô khi định nghĩa phạm vi trong Nguồn.

Cách Bảo Vệ Danh Sách Thả Xuống Trong Excel?

Danh sách thả xuống tên khách được tạo trong C18.

• Chọn toàn bộ bảng và đi đến tab Trang Chủ.
• Nhấp Căn Chỉnh để mở rộng.

• Trong Định Dạng Ô, tích Khóa trong tab Bảo Vệ.

• Bảng được bảo vệ. Đi đến tab Xem Xét và nhấp vào Bảo Vệ Workbook.

• Nhập mật khẩu và tích Chọn ô đã khóa.
• Nhấp OK.

• Nhập lại mật khẩu.

Cả bảng và danh sách thả xuống đều được bảo vệ.

Cách Xóa Danh Sách Thả Xuống Trong Excel?

• Chọn danh sách thả xuống và đi đến tab Dữ Liệu.
• Nhấp Xác Thực Dữ Liệu.

• Trong Xác Thực Dữ Liệu, nhấp Xóa Tất Cả.
• Nhấp OK.

Danh sách thả xuống sẽ bị xóa.

Cách Lọc Danh Sách Thả Xuống & Trích Xuất Dữ Liệu Dựa Trên Lựa Chọn Trong Excel?

• Tạo bảng.

• Tạo danh sách thả xuống trong cột D.

• Nhập công thức sau để trích xuất toàn bộ dữ liệu dựa trên lựa chọn.

=FILTER(Table3,Table3[Device]=$C$18,"Không Có Thông Tin")

• Thay đổi giá trị danh sách thả xuống và trích xuất dữ liệu sẽ tự động cập nhật.

Cách Giải Quyết Vấn Đề Với Danh Sách Thả Xuống Excel?

1. Danh Sách Thả Xuống Không Hiện

Giải Pháp:

• Tích Danh sách thả xuống trong ô để làm cho danh sách thả xuống hiển thị.

2. Hiển Thị Trống Trong Danh Sách Thả Xuống

Giải Pháp:

• Định nghĩa phạm vi danh sách thả xuống để bỏ qua ô trống.

3. Mục Hợp Lệ Không Được Chấp Nhận Trong Danh Sách Thả Xuống

Giải Pháp:

• Dùng chữ hoa/thường đúng khi nhập mục hợp lệ.

4. Mục Không Hợp Lệ Được Chấp Nhận Trong Danh Sách Thả Xuống

Vì phạm vi nguồn lớn hơn danh sách giá trị thực tế, giá trị nhập vào những ô đó có thể được liệt kê, dù chúng không hợp lệ.

Giải Pháp:

• Định nghĩa phạm vi danh sách thả xuống để ngăn nhập phần tử không hợp lệ.

Tải File Thực Hành
Tải file thực hành.

Danh Sách Thả Xuống Excel: Trung Tâm Kiến Thức

• Tạo Danh Sách Thả Xuống Trong Excel
• Sửa Danh Sách Thả Xuống Trong Excel
• Danh Sách Thả Xuống Với Lọc Trong Excel
• Excel Danh Sách Thả Xuống Phụ Thuộc
• Cách Xóa Danh Sách Thả Xuống Trong Excel
• Cách Liên Kết Giá Trị Ô Với Danh Sách Thả Xuống Trong Excel
• Excel Danh Sách Thả Xuống Không Hoạt Động
• Câu Lệnh IF Tạo Danh Sách Thả Xuống Trong Excel
• Trích Xuất Dữ Liệu Dựa Trên Lựa Chọn Danh Sách Thả Xuống Trong Excel
• Điền Danh Sách Dựa Trên Giá Trị Ô Trong Excel
• Danh Sách Thả Xuống Trong Nhiều Cột Trong Excel
• Tùy Chọn Trống Cho Danh Sách Thả Xuống Trong Excel
• Chọn Từ Thả Xuống Và Kéo Dữ Liệu Từ Sheet Khác
• [Đã Sửa!] Danh Sách Thả Xuống Bỏ Qua Trống Không Hoạt Động Trong Excel
• Tự Động Hoàn Thành Danh Sách Xác Thực Dữ Liệu Thả Xuống Trong Excel

<< Quay Lại Xác Thực Dữ Liệu Trong Excel | Học Excel

Nhận Miễn Phí Bài Tập Excel Nâng Cao Kèm Giải Pháp!