Khi làm việc với xác thực dữ liệu trong Excel, bạn có thể cần xóa các mục đã sử dụng khỏi danh sách thả xuống để tránh gán một mục trong danh sách hai lần. Ví dụ: bạn phải phân công nhiều nhân viên vào các ca làm việc khác nhau và không muốn phân công một nhân viên hơn một lần. Hoặc bạn gán người chơi vào các vị trí trong trò chơi tính điểm và cần gán một người chơi vào một vị trí cụ thể. Trong những trường hợp này, nếu có danh sách thả xuống để phân công nhân viên hoặc người chơi, bạn có thể muốn xóa tên nhân viên/người chơi khỏi danh sách sau khi đã được gán. Trong hướng dẫn này, tôi sẽ chỉ cho bạn cách xóa các mục đã dùng khỏi danh sách thả xuống trong Excel.
Tải xuống tệp thực hành để tập làm theo bài viết.
2 cách dễ dàng để xóa mục đã dùng khỏi danh sách thả xuống trong Excel
Giả sử một tình huống: chúng ta có bảng tính Excel chứa tên nhân viên của một tổ chức. Bạn cần gán mỗi nhân viên cho một ca làm việc khác nhau và không muốn gán ai hai lần. Do đó, bạn cần một danh sách thả xuống tên nhân viên sẽ tự động xóa nhân viên đã được gán. Tôi sẽ dùng bảng tính này để demo 2 cách dễ dàng để xóa mục đã dùng khỏi danh sách thả xuống. Hình ảnh dưới đây minh họa kết quả cuối cùng.
Phương pháp 1: Sử dụng cột phụ trợ để xóa mục đã dùng khỏi danh sách thả xuống trong Excel
Một cách dễ dàng là sử dụng hai cột phụ trợ. Hãy xem cách thực hiện.
Bước 1:
- Nhập công thức sau vào ô C5 (cột Số thứ tự hàng):
=IF(COUNTIF($F$5:$F$14,B5)>=1,"",ROW())
Giải thích công thức:
- Hàm IF kiểm tra điều kiện logic COUNTIF($F$5:$F$14, B5)>=1.
- Hàm COUNTIF đếm xem ô B5 xuất hiện bao nhiêu lần trong phạm vi tuyệt đối $F$5:$F$14.
- Nếu ô B5 xuất hiện một lần hoặc nhiều hơn trong phạm vi đó, hàm IF trả về chuỗi rỗng (“”).
- Ngược lại, hàm IF trả về số hàng của ô B5 bằng hàm ROW.
- Nhấn ENTER để thấy số hàng của ô B5 tại C5.
- Kéo tay cài (fill handle) của ô C5 xuống để áp dụng công thức cho các ô còn lại trong cột Số thứ tự hàng.
- Kết quả: Tất cả các số hàng của cột Nhân viên được hiển thị.
Bước 2:
- Nhập công thức sau vào ô D5 (cột Tên nhân viên):
=IF(ROW(B5)-ROW(B$5)+1>COUNT(C$5:C$14),"",INDEX(B:B,SMALL(C$5:C$14,1+ROW(B5)-ROW(B$5))))
Giải thích công thức:
- Hàm IF kiểm tra ROW(B5)-ROW(B$5)+1>COUNT(C$5:C$14).
- Hàm COUNT đếm số ô có dữ liệu trong phạm vi C$5:C$14.
- Hàm SMALL tìm giá trị nhỏ thứ k trong phạm vi C$5:C$14, với k = 1+ROW(B5)-ROW(B$5).
- Hàm INDEX lấy tham chiếu ô dựa trên giá trị hàng từ SMALL.
- Nhấn ENTER để lấy tên nhân viên từ B5 vào D5.
- Kéo tay cài của D5 xuống để điền công thức cho cột Tên nhân viên.
- Kết quả: Danh sách tên nhân viên không trùng lặp xuất hiện.
Bước 3:
- Chọn Đặt tên (Define Name) dưới tab Công thức (Formulas).
- Cửa sổ Sửa tên (Edit Name) hiện ra. Nhập Employee vào ô Tên (Name).
- Nhập công thức sau vào ô Tham chiếu đến (Refers to):
=Helper!$B$4:$D$14=OFFSET(Helper!$D$5,0,0, COUNTA(Helper!$D$5:$D$14)-COUNTBLANK(Helper!$D$5:$D$14),1)Giải thích:
- Helper là tên trang tính đang làm việc.
- COUNTA đếm tất cả giá trị ô trong $D$5:$D$14.
- COUNTBLANK đếm số ô trống trong cùng phạm vi.
- Nhấn OK.
Bước 4:
- Chọn tất cả ô trong cột Danh sách thả xuống (Drop-Down) để tạo danh sách.
- Chọn Xác thực dữ liệu (Data Validation) từ tab Dữ liệu.
- Chọn Xác thực dữ liệu từ menu thả xuống.
- Cửa sổ Xác thực dữ liệu hiện ra. Chọn Danh sách (List) từ menu Cho phép (Allow).
- Nhập =Employee vào ô Nguồn (Source).
- Nhấn OK.
- Danh sách thả xuống xuất hiện trong mỗi ô của cột Drop-Down.
- Chọn tên Gus Fring từ danh sách thả xuống tại ô F5.
- Khi click vào danh sách thả xuống thứ hai, tên Gus Fring không còn xuất hiện vì đã được dùng.
- Tiếp tục chọn tên từ các danh sách thả xuống khác, các mục đã chọn sẽ bị loại khỏi các danh sách tiếp theo.
Đọc thêm: Cách tạo danh sách thả xuống cho nhiều cột trong Excel (3 cách)
Bài đọc tương tự:
- Cách tạo Listbox chọn nhiều trong Excel
- Danh sách thả xuống Excel tùy thuộc vào lựa chọn
- Cách liên kết giá trị ô với danh sách thả xuống trong Excel (5 cách)
- Danh sách thả xuống có điều kiện trong Excel (Tạo, Sắp xếp và Sử dụng)
- Cách tạo danh sách thả xuống phụ thuộc động trong Excel
Phương pháp 2: Xóa mục đã dùng khỏi danh sách thả xuống trong Excel bằng kết hợp hàm FILTER và COUNTIF
Nếu bạn có Microsoft Office 365, cách dễ nhất là dùng hàm FILTER (đặc quyền của Excel 365) để xóa mục đã dùng. Các bước như sau:
Bước 1:
- Nhập công thức sau vào ô C5 (cột Số thứ tự hàng):
=FILTER(B5:B14, COUNTIF(E5:E14,B5:B14)=0)
Giải thích công thức:
- Hàm FILTER lọc phạm vi B5:B14 dựa trên tiêu chí COUNTIF(E5:E14, B5:B14)=0.
- Hàm COUNTIF kiểm tra xem phạm vi B5:B14 có xuất hiện trong E5:E14 hay không.
- Nhấn ENTER để lấy danh sách tên nhân viên chưa được dùng.
Bước 2:
- Chọn tất cả ô trong cột Drop-Down để tạo danh sách thả xuống.
- Chọn Xác thực dữ liệu từ tab Dữ liệu.
- Chọn Xác thực dữ liệu từ menu.
- Cửa sổ Xác thực dữ liệu hiện ra. Chọn Danh sách từ menu Cho phép.
- Nhập $C$5:$C$14 (hoặc =$C$5#) vào ô Nguồn.
- Nhấn OK.
- Danh sách thả xuống xuất hiện. Chọn tên Stuart Bloom tại ô F5.
- Tại danh sách thả xuống thứ hai, Stuart Bloom không còn có mặt.
- Tiếp tục chọn từ các danh sách khác, các mục đã chọn sẽ tự động bị loại.
Đọc thêm: Tạo bộ lọc thả xuống để trích xuất dữ liệu dựa trên lựa chọn trong Excel
Ghi chú nhanh
🎯 Hàm FILTER chỉ dành cho Excel 365. Sẽ không hoạt động nếu bạn không cài Excel 365.
🎯 Đọc bài này để học cách tạo danh sách thả xuống với giá trị duy nhất trong Excel.
Kết luận
Trong bài này, chúng ta đã học cách xóa mục đã dùng khỏi danh sách thả xuống trong Excel. Hy vọng từ giờ bạn có thể làm điều này dễ dàng. Nếu có thắc mắc hoặc góp ý, hãy để lại bình luận. Chúc bạn một ngày tốt lành!
Các bài viết liên quan
- Tạo danh sách thả xuống có thể tìm kiếm trong Excel (2 phương pháp)
- Cách tạo danh sách thả xuống Excel có màu sắc (2 cách)
- Danh sách thả xuống Excel không hoạt động (8 vấn đề và giải pháp)
- Cách tạo danh sách từ phạm vi trong Excel (3 phương pháp)
- Tự động cập nhật danh sách thả xuống trong Excel (3 cách)