Computer >> Máy Tính >  >> Phần mềm >> Office

Cách xóa các mục đã sử dụng khỏi danh sách thả xuống trong Excel (2 phương pháp)

Trong 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 có thể 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à bạn không muốn chỉ định một nhân viên nhiều hơn một lần. Một tình huống khác có thể là bạn đang chỉ định người chơi vào các vị trí khác nhau trong một trò chơi tính điểm và bạn phải chỉ định một người chơi vào một vị trí cụ thể. Trong những trường hợp như vậy, nếu bạn có danh sách thả xuống để chỉ định nhân viên làm việc theo ca hoặc người chơi ở các vị trí khác nhau, bạn có thể muốn xóa tên của nhân viên hoặc người chơi khỏi danh sách thả xuống sau khi họ đã được chỉ định . Trong hướng dẫn này, tôi sẽ chỉ cho bạn cách xóa các mục đã sử dụng khỏi danh sách thả xuống trong Excel.

Tải xuống sách thực hành này để thực hiện nhiệm vụ khi bạn đang đọc bài viết này.

2 cách dễ dàng để xóa các mục đã sử dụng khỏi danh sách thả xuống trong Excel

Hãy giả sử một tình huống trong đó chúng ta có một trang tính Excel với tên của nhân viên của một tổ chức. Bạn cần phân công từng nhân viên này vào một ca làm việc khác nhau và bạn không muốn chỉ định một nhân viên nhiều hơn một lần. Vì vậy, bạn cần một danh sách thả xuống với tên của các nhân viên sẽ tự động loại bỏ một nhân viên sau khi anh ta / cô ta đã được giao nhiệm vụ. Tôi sẽ sử dụng trang tính này để chỉ cho bạn 2 cách dễ dàng để xóa các mục đã sử dụng khỏi danh sách thả xuống . Hình ảnh bên dưới cho thấy trang tính chúng tôi sẽ làm việc với danh sách thả xuống với các mục đã sử dụng bị loại bỏ.

Cách xóa các mục đã sử dụng khỏi danh sách thả xuống trong Excel (2 phương pháp)

Phương pháp 1: Sử dụng các cột của trình trợ giúp để xóa các mục đã sử dụng khỏi danh sách thả xuống trong Excel

Một cách dễ dàng để xóa các mục đã sử dụng khỏi danh sách thả xuống là sử dụng hai cột trợ giúp . Hãy xem chúng ta có thể làm điều đó như thế nào.

Bước 1:

  • Đầu tiên, hãy viết công thức sau vào ô C5 dưới Số hàng
=IF(COUNTIF($F$5:$F$14,B5)>=1,"",ROW())

Cách xóa các mục đã sử dụng khỏi danh sách thả xuống trong Excel (2 phương pháp)

Phân tích công thức:

  • IF hàm sẽ chạy kiểm tra logic COUNTIF ($ F $ 5:$ F $ 14, B5)> =1 .
  • COUNTIF hàm sẽ tìm hiểu xem ô B5 xuất hiện trong phạm vi tuyệt đối $ F $ 5:$ F $ 14 nhiều hơn một lần .
  • Nếu ô B5 xuất hiện một lần hoặc hơn thế nữa trong phạm vi tuyệt đối $ F $ 5:$ F $ 14 , IF hàm sẽ trả về một chuỗi trống ( “” ).
  • Nếu không, IF hàm sẽ trả về số hàng của ô B5 sử dụng ROW .
  • Sau đó, khi nhấn ENTER , chúng tôi sẽ tìm ra số hàng của ô B5 trong ô C5 .

Cách xóa các mục đã sử dụng khỏi danh sách thả xuống trong Excel (2 phương pháp)

  • Bây giờ, chúng tôi sẽ kéo ô điều khiển lấp đầy của ô C5 xuống dưới để áp dụng công thức đến phần còn lại của ô trong Số hàng.

Cách xóa các mục đã sử dụng khỏi danh sách thả xuống trong Excel (2 phương pháp)

  • Cuối cùng, bây giờ chúng ta sẽ nhận được tất cả số hàng trong số các ô của Nhân viên .

Cách xóa các mục đã sử dụng khỏi danh sách thả xuống trong Excel (2 phương pháp)

Bước 2:

  • Tiếp theo, viết công thức sau vào ô D5 dưới 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))))

Cách xóa các mục đã sử dụng khỏi danh sách thả xuống trong Excel (2 phương pháp)

Phân tích công thức:

  • IF hàm sẽ chạy kiểm tra logic ROW (B5) -ROW (B $ 5) +1> COUNT (C $ 5:C $ 14) .
  • COUNT hàm sẽ đếm số ô trong phạm vi tuyệt đối C $ 5:C $ 14 .
  • NHỎ hàm sẽ tìm ra thứ k giá trị nhỏ nhất trong phạm vi tuyệt đối C $ 5:C $ 14 . Đây, k sẽ được xác định bởi 1 + ROW (B5) -ROW (B $ 5) .
  • INDEX hàm sẽ lấy thứ k giá trị nhỏ nhất trong phạm vi tuyệt đối C $ 5:C $ 14 được xác định bởi NHỎ hàm như một đối số duy nhất ( row_num ) và trả về tài liệu tham khảo trong số ô .
  • Sau đó, khi nhấn ENTER , chúng tôi sẽ nhận được tên nhân viên của ô B5 trong ô D5 .

Cách xóa các mục đã sử dụng khỏi danh sách thả xuống trong Excel (2 phương pháp)

  • Bây giờ, chúng tôi sẽ kéo ô điều khiển lấp đầy của ô D5 xuống dưới để áp dụng công thức đến phần còn lại của ô trong Tên nhân viên .

Cách xóa các mục đã sử dụng khỏi danh sách thả xuống trong Excel (2 phương pháp)

  • Cuối cùng, bây giờ chúng tôi sẽ có tất cả nhân viên tên trong Nhân viên cột.

Cách xóa các mục đã sử dụng khỏi danh sách thả xuống trong Excel (2 phương pháp)

Bước 3:

  • Tiếp theo, chúng tôi sẽ nhấp vào Xác định tên trong Công thức .

Cách xóa các mục đã sử dụng khỏi danh sách thả xuống trong Excel (2 phương pháp)

  • Bây giờ, một cửa sổ mới có tiêu đề Chỉnh sửa tên sẽ xuất hiện. Chúng tôi sẽ chèn Nhân viên trong Tên hộp nhập liệu.
  • Sau đó, chúng tôi sẽ chèn công thức bên dưới vào phần Tham chiếu đến hộp nhập liệu.
=Helper!$B$4:$D$14=OFFSET(Helper!$D$5,0,0, COUNTA(Helper!$D$5:$D$14)-COUNTBLANK(Helper!$D$5:$D$14),1)

Phân tích công thức:

  • Người trợ giúp là tên của trang tính chúng tôi đang làm việc.
  • COUNTA hàm sẽ đếm tất cả giá trị ô trong phạm vi tuyệt đối $ D $ 5:$ D $ 14 .
  • COUNTBLANK hàm sẽ đếm số trong tổng số ô trống trong phạm vi tuyệt đối $ D $ 5:$ D $ 14 .
  • Sau đó, chúng tôi sẽ nhấp vào OK .

Cách xóa các mục đã sử dụng khỏi danh sách thả xuống trong Excel (2 phương pháp)

Bước 4:

  • Tiếp theo, chúng tôi sẽ chọn tất cả các ô trong Trình đơn thả xuống để tạo danh sách thả xuống .
  • Bây giờ, chúng tôi sẽ nhấp vào Xác thực dữ liệu trình đơn thả xuống trong Dữ liệu .
  • Sau đó, chúng tôi sẽ chọn Xác thực dữ liệu từ trình đơn thả xuống .

Cách xóa các mục đã sử dụng khỏi danh sách thả xuống trong Excel (2 phương pháp)

  • Bây giờ, một cửa sổ mới có tiêu đề Xác thực dữ liệu sẽ xuất hiện. Sau đó, chúng tôi sẽ chọn Danh sách từ Cho phép menu thả xuống.

Cách xóa các mục đã sử dụng khỏi danh sách thả xuống trong Excel (2 phương pháp)

  • Sau đó, chúng tôi sẽ chèn =Nhân viên trong Nguồn hộp nhập liệu.
  • Sau đó, chúng tôi sẽ nhấp vào OK .

Cách xóa các mục đã sử dụng khỏi danh sách thả xuống trong Excel (2 phương pháp)

  • Cuối cùng, chúng ta sẽ thấy trình đơn thả xuống danh sách trong mỗi ô của Trình đơn thả xuống.
  • Bây giờ, chúng tôi sẽ chọn tên Gus Fring từ danh sách thả xuống trong ô F5 .

Cách xóa các mục đã sử dụng khỏi danh sách thả xuống trong Excel (2 phương pháp)

  • Bây giờ, nếu chúng ta nhấp vào trình đơn thả xuống thứ hai , chúng ta sẽ thấy rằng tên Gus Fring không có trong danh sách thả xuống này. Vì chúng tôi đã sử dụng mặt hàng này, nên mặt hàng này sẽ bị xóa khỏi danh sách thả xuống sau.

Cách xóa các mục đã sử dụng khỏi danh sách thả xuống trong Excel (2 phương pháp)

  • Tiếp theo, nếu chúng tôi chọn tên từ các danh sách thả xuống khác, chúng tôi sẽ thấy rằng các mục đã chọn hoặc tên sẽ bị loại bỏ từ danh sách thả xuống sau .

Cách xóa các mục đã sử dụng khỏi danh sách thả xuống trong Excel (2 phương pháp)

Đọc thêm: Cách tạo danh sách thả xuống trong nhiều cột trong Excel (3 cách)

Bài đọc tương tự:

  • Cách tạo hộp danh sách nhiều lựa chọn trong Excel
  • Danh sách thả xuống của 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 các mục đã sử dụng khỏi danh sách thả xuống trong Excel Kết hợp hàm FILTER và COUNTIF

Nếu bạn có quyền truy cập vào Microsoft Office 365 , cách dễ nhất sau đó sẽ là sử dụng FILTER chức năng dành riêng cho Excel 365 để xóa các mục đã sử dụng khỏi danh sách thả xuống. Chúng ta phải làm theo các bước sau.

Bước 1:

  • Đầu tiên, hãy viết công thức sau vào ô C5 dưới Số hàng
=FILTER(B5:B14, COUNTIF(E5:E14,B5:B14)=0)

Cách xóa các mục đã sử dụng khỏi danh sách thả xuống trong Excel (2 phương pháp)

Phân tích công thức:

  • BỘ LỌC chức năng sẽ cho phép chúng tôi lọc phạm vi B5:B14 dựa trên tiêu chí COUNTIF (E5:E14, B5:B14) =0 .
  • COUNTIF hàm sẽ xác định xem phạm vi B5:B14 xuất hiện trong phạm vi E5:E14 hoặc không .
  • Sau đó, khi nhấn ENTER , bây giờ chúng tôi sẽ nhận được tất cả nhân viên tên của Nhân viên cột.

Cách xóa các mục đã sử dụng khỏi danh sách thả xuống trong Excel (2 phương pháp)

Bước 2:

  • Tiếp theo, chúng tôi sẽ chọn tất cả các ô trong Trình đơn thả xuống để tạo danh sách thả xuống .
  • Bây giờ, chúng tôi sẽ nhấp vào Xác thực dữ liệu trình đơn thả xuống trong Dữ liệu .
  • Sau đó, chúng tôi sẽ chọn Xác thực dữ liệu từ trình đơn thả xuống .

Cách xóa các mục đã sử dụng khỏi danh sách thả xuống trong Excel (2 phương pháp)

  • Bây giờ, một cửa sổ mới có tiêu đề Xác thực dữ liệu sẽ xuất hiện. Chúng tôi sẽ chọn Danh sách từ Cho phép menu thả xuống.

Cách xóa các mục đã sử dụng khỏi danh sách thả xuống trong Excel (2 phương pháp)

  • Sau đó, chúng tôi sẽ chèn $ C $ 5:$ C $ 14 trong Nguồn hộp nhập liệu. Ngoài ra, bạn cũng có thể chèn =$ C $ 5 # trong Nguồn hộp nhập liệu.
  • Sau đó, chúng tôi sẽ nhấp vào OK .

Cách xóa các mục đã sử dụng khỏi danh sách thả xuống trong Excel (2 phương pháp)

  • Cuối cùng, chúng ta sẽ thấy trình đơn thả xuống danh sách trong mỗi ô của Trình đơn thả xuống.
  • Bây giờ, chúng tôi sẽ chọn tên Stuart Bloom từ danh sách thả xuống trong ô F5 .

Cách xóa các mục đã sử dụng khỏi danh sách thả xuống trong Excel (2 phương pháp)

  • Bây giờ, nếu chúng ta nhấp vào trình đơn thả xuống thứ hai , chúng ta sẽ thấy rằng tên Stuart Bloom không có trong danh sách thả xuống này. Vì chúng tôi đã sử dụng mặt hàng này, nên mặt hàng này sẽ bị xóa khỏi danh sách thả xuống sau.

Cách xóa các mục đã sử dụng khỏi danh sách thả xuống trong Excel (2 phương pháp)

  • Tiếp theo, nếu chúng tôi chọn tên từ các danh sách thả xuống khác, chúng tôi sẽ thấy rằng các mục đã chọn hoặc tên sẽ bị loại bỏ từ danh sách thả xuống sau .

Cách xóa các mục đã sử dụng khỏi danh sách thả xuống trong Excel (2 phương pháp)

Đọ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

🎯 BỘ LỌC hàm là một hàm độc quyền hiện chỉ có sẵn cho Excel 365 . Vì vậy, nó sẽ không hoạt động trong trang tính của bạn nếu bạn không có Excel 365 trên PC của bạn.

🎯 Và đọc bài viết này để tìm hiểu cách tạo danh sách thả xuống với các giá trị duy nhất trong Excel.

Kết luận

Trong bài viết này, chúng ta đã tìm hiểu cách xóa các mục đã sử dụng khỏi danh sách thả xuống trong Excel . Tôi hy vọng từ bây giờ bạn có thể xóa các mục đã sử dụng khỏi danh sách thả xuống trong Excel một cách dễ dàng. Tuy nhiên, nếu bạn có bất kỳ thắc mắc hoặc khuyến nghị nào về bài viết này, vui lòng để lại bình luận bên dưới. Chúc một ngày tốt lành !!!

Các bài viết có 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 với màu (2 cách)
  • Danh sách thả xuống của 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)