Bài viết này sẽ mô tả ngắn gọn một số phương pháp giá trị về cách loại bỏ ô trống khỏi danh sách kiểm tra dữ liệu trong Excel. Khi chúng ta có ô trống trong danh sách của biểu đồ Excel, nó cũng vẫn nằm trong danh sách kiểm tra dữ liệu điều này là không mong muốn.
Vì vậy, tôi sẽ làm việc trên bộ dữ liệu sau để chỉ cho bạn cách bạn có thể loại bỏ ô trống khỏi danh sách kiểm tra dữ liệu trong Excel.
Vấn đề khi tạo Danh sách Kiểm tra dữ liệu với ô trống
Để tôi chỉ cho bạn thấy điều gì xảy ra nếu chúng ta tạo danh sách thả xuống bao gồm ô trống. Trước tiên chúng ta cần tạo danh sách thả xuống.
Các bước:
- Chọn ô C5.
- Và sau đó chọn Dữ liệu >> Công cụ Dữ liệu >> Kiểm tra dữ liệu
- Một hộp thoại sẽ hiện ra. Chọn Danh sách từ thanh Cho phép (Được hiển thị trong hình sau).
- Bây giờ nhấp vào biểu tượng được đánh dấu.
- Sau đó, chọn ô từ B5 đến B14 và nhấp vào biểu tượng được đánh dấu.
- Sau đó, chỉ cần nhấp OK.
Bằng cách này, chúng ta vừa tạo xong danh sách thả xuống.
Ở đây, bạn có thể nhận thấy danh sách thả xuống được tạo cùng với ô trống. Bây giờ tôi sẽ mô tả cách bạn có thể loại trừ các ô trống này khỏi danh sách thả xuống.
5 Cách loại bỏ ô trống khỏi Danh sách Kiểm tra dữ liệu trong Excel
1. Loại bỏ ô trống khỏi Danh sách Kiểm tra dữ liệu sử dụng hàm OFFSET
Đây là một cách bạn có thể tạo thêm không gian cho danh sách thả xuống của mình mà không có bất kỳ ô trống nào trong cột đó. Trước tiên bạn cần lọc ra các ô trống khỏi dữ liệu của bạn. Hãy thảo luận về quy trình.
Chúng ta cần một số sửa đổi cho bộ dữ liệu của mình.
- Hãy thêm một cột mới trước cột đang được sử dụng cho danh sách thả xuống. Chúng tôi đặt tên cho cột mới và cột chúng ta đang dùng cho danh sách thả xuống lần lượt là Danh sách tên có ô trống và Danh sách không ô trống. (Để xem cách tạo danh sách thả xuống, vui lòng xem Phần 1).
Các bước:
- Trước tiên, nhập công thức sau vào ô C5.
=FILTER(B5:B14,B5:B14<>"")
Ở đây hàm FILTER sẽ lấy phạm vi B5:B14 và kiểm tra bất kỳ ô trống nào giữa phạm vi. Sau đó nó lọc ra các ô trống hoặc rỗng khỏi danh sách.
- Bây giờ nhấn ENTER. Bạn sẽ thấy danh sách tên không có bất kỳ ô trống nào.
- Sau đó, chọn Quản lý tên từ Tab Công thức và nhấp vào Mới.
- Đặt tên cho phạm vi của bạn. Tôi sẽ dùng TenKhongTrong làm tên của phạm vi.
- Và sau đó viết công thức sau trong Tham chiếu đến
=offset(offset!$C$5,0,0,counta(offset!$C$4:$C$16)-1,1)
Trong tình huống này, chúng ta muốn sử dụng thêm một số ô nơi có thể nhập tên mới, nhưng chúng ta không muốn ô trống trong danh sách thả xuống cho những khoảng trống đó. Ở đây chúng ta đang tạo các mục nhập mới cho danh sách kiểm tra dữ liệu từ C12 đến C16 bằng cách đặt công thức đó. Hãy nhớ 'offset!' tham chiếu đến tên trang tính mà chúng ta đang sử dụng.
- Bây giờ nhấp OK. Bạn sẽ thấy một Cửa sổ. Chỉ cần đóng nó.
- Và sau đó chọn ô D5 và chọn Dữ liệu >> Danh sách Kiểm tra dữ liệu.
- Thay đổi Tên nguồn thành =TenKhongTrong.
- Nhấp OK.
- Chọn thanh danh sách thả xuống trong ô D5. Bạn sẽ thấy danh sách tên chúng ta đang sử dụng.
- Bây giờ viết một số tên mới xuyên suốt ô C12 đến C16.
- Sau đó chọn ô danh sách kiểm tra dữ liệu D5.
Bạn có thể thấy các tên mới trong danh sách thả xuống của mình. Bạn không thể thấy bất kỳ mục nhập mới nào dưới ô C16 vì chúng không nằm trong phạm vi của bạn.
Theo cách tiếp cận này, bạn có thể tạo một số khoảng trống cho các mục nhập mới trong danh sách kiểm tra dữ liệu của mình mà không làm cho bất kỳ ô trống nào trong nó.
Đọc thêm: Cách tạo danh sách thả xuống Excel cho Kiểm tra dữ liệu (8 cách)
2. Sử dụng lệnh Chuyển đến đặc biệt để loại bỏ ô trống khỏi danh sách
Chúng ta đã tạo danh sách thả xuống (phần 1), bạn thấy rằng vẫn còn ô trống trong đó. Để loại bỏ chúng, chúng ta có thể đơn giản thực hiện theo các bước sau.
Các bước:
- Chọn ô từ B5 đến B14 và sau đó chọn Trang chủ >> Tìm và chọn >> Chuyển đến đặc biệt.
- Sau đó, chọn Ô trống và nhấp OK.
- Thao tác này sẽ chọn các ô trống.
- Bây giờ chọn bất kỳ ô trống nào trong số đó, nhấp chuột phải vào nó và chọn Xóa để Xóa các ô trống.
- Bạn sẽ thấy một hộp thoại. Chọn Dịch chuyển ô lên và nhấp OK.
- Thao tác này sẽ loại bỏ ô trống khỏi danh sách gốc cũng như khỏi danh sách thả xuống.
Bằng cách tiếp cận này, bạn có thể dễ dàng loại bỏ ô trống hoặc ô rỗng khỏi danh sách thả xuống.
Đọc thêm: Tạo danh sách thả xuống Kiểm tra dữ liệu với chọn nhiều trong Excel
3. Sử dụng hàm FILTER của Excel để loại bỏ ô trống khỏi Danh sách Kiểm tra dữ liệu
Chúng ta có thể áp dụng hàm FILTER trong khía cạnh này. Chúng ta sẽ sử dụng bộ dữ liệu từ Phần 2. Để xem cách tạo danh sách thả xuống, vui lòng xem Phần 1.
Các bước:
- Nhập công thức sau vào ô C5.
=FILTER(B5:B14,B5:B14<>"")
Ở đây hàm FILTER sẽ lấy phạm vi B5:B14 và kiểm tra bất kỳ ô trống nào giữa phạm vi. Sau đó nó lọc ra các ô trống hoặc rỗng khỏi danh sách.
- Nhấn phím ENTER và bạn sẽ thấy danh sách tên không có bất kỳ ô trống nào.
- Nhưng nếu bạn vào Danh sách Thả xuống, bạn vẫn sẽ thấy nó chứa ô trống từ cột C.
- Vì vậy để loại bỏ các ô trống này, đi đến Kiểm tra dữ liệu từ Tab Dữ liệu.
- Thay đổi ô cuối cùng của phạm vi thành C11 vì danh sách đã lọc của bạn có phạm vi C5 đến C11 trong Nguồn
- Bây giờ nhấp OK. Bây giờ bạn sẽ không có ô trống nào trong danh sách thả xuống.
Đây là một cách khá hiệu quả để loại bỏ ô trống khỏi danh sách thả xuống.
Đọc thêm: Danh sách thả xuống Kiểm tra dữ liệu Excel với Bộ lọc (2 ví dụ)
Bài đọc tương tự:
- Cách sử dụng công thức VLOOKUP tùy chỉnh trong Kiểm tra dữ liệu Excel
- [Đã sửa] Kiểm tra dữ liệu không hoạt động cho Sao chép Dán trong Excel (có giải pháp)
- Cách tạo danh sách Kiểm tra dữ liệu từ Bảng trong Excel (3 phương pháp)
- Áp dụng nhiều Kiểm tra dữ liệu trong một ô trong Excel (3 ví dụ)
- Kiểm tra dữ liệu Excel Chỉ chữ và số (Sử dụng công thức tùy chỉnh)
4. Kết hợp hàm IF, COUNTIF, ROW, INDEX và SMALL để loại bỏ ô trống khỏi Danh sách Kiểm tra dữ liệu
Chúng ta cũng có thể sử dụng tổ hợp IF, COUNTIF, ROW, INDEX và SMALL để loại bỏ ô trống khỏi danh sách kiểm tra dữ liệu. Nó sẽ hơi phức tạp một chút. Chúng ta sẽ sử dụng bộ dữ liệu từ Phần 2. Và để xem cách tạo danh sách thả xuống, hãy xem Phần 1.
Các bước:
- Trước tiên, nhập công thức sau vào ô C5.
=IF(COUNTIF($B$5:$B$14,"?*")<ROW(B5)-4,"",INDEX(B:B,SMALL(IF(B$5:B$14<>"",ROW(B$5:B$14)),ROWS(B$5:B5))))
Công thức có hai phần chính. Phần đầu là COUNTIF($B$5:$B$14,"?*")<ROW(B5)-4,"" và phần thứ hai là INDEX(B:B,SMALL(IF(B$5:B$14<>"",ROW(B$5:B$14)),ROWS(B$5:B5))).
- Hàm COUNTIF đếm ô không trống ở đây và đó là lý do chúng ta có 7 tên trong cột C.
- Hàm ROW trả về số hàng của một ô và ô trống của chúng ta ở vị trí 5 từ ô B5. Chúng ta trừ đi 4 vì chúng ta muốn nó nhỏ hơn giá trị đó.
- Bây giờ nhấn ENTER.
- Sử dụng Kéo thả để Tự động điền các ô dưới.
- Bây giờ chúng ta có Danh sách tên không có bất kỳ ô trống nào. Nhưng nếu chúng ta nhấp vào danh sách kiểm tra dữ liệu, chúng ta vẫn thấy ô trống trong danh sách thả xuống.
- Và để loại bỏ các ô trống này, đi đến Kiểm tra dữ liệu từ Tab Dữ liệu.
- Thay đổi ô cuối cùng của phạm vi thành C11 vì danh sách đã lọc của bạn có phạm vi C5 đến C11 trong Nguồn.
- Bây giờ nhấp OK. Bạn sẽ không còn ô trống nào trong danh sách thả xuống.
Vì vậy bạn có thể tạo danh sách kiểm tra dữ liệu không có ô trống.
Đọc thêm: Giá trị mặc định trong Danh sách Kiểm tra dữ liệu với Excel VBA (Macro và UserForm)
5. Sử dụng các hàm kết hợp để loại bỏ ô trống khỏi Danh sách Kiểm tra dữ liệu
Chúng ta cũng có thể sử dụng các công thức lồng nhau kết hợp với ADDRESS, INDIRECT, COUNTBLANK, IF và SMALL. Hãy thảo luận về thủ tục. Chúng ta sẽ sử dụng bộ dữ liệu từ Phần 2. Bạn cũng có thể xem Phần 1 để xem cách tạo danh sách thả xuống/kiểm tra dữ liệu.
Các bước:
- Trước tiên, nhập công thức sau vào ô C5.
=IF(ROW()-ROW($B$5:$B$14)+1>ROWS($B$5:$B$14)-COUNTBLANK($B$5:$B$14),"", INDIRECT(ADDRESS(SMALL((IF($B$5:$B$14<>"",ROW($B$5:$B$14),ROW()+ROWS($B$5:$B$14))),ROW()-ROW($C$5:$C$14)+1),COLUMN($B$5:$B$14),4)))
Ở đây, tôi sẽ giải thích một cách rất đơn giản cách công thức này hoạt động. Nó đi qua phạm vi B5:B14 và kiểm tra ô trống với sự giúp đỡ của hàm COUNTBLANK. Sau đó nó cũng kiểm tra các ô nào không trống xuyên suốt B5:B14 và do đó trả về ô không rỗng.
- Nhấn CTRL + SHIFT + ENTER (vì đây là công thức mảng) và bạn sẽ thấy kết quả đầu ra trong ô C5 như bên dưới.
- Bây giờ sử dụng Kéo thả để Tự động điền các ô dưới.
- Nhưng nếu bạn vào Danh sách Thả xuống, bạn vẫn sẽ thấy nó chứa ô trống từ cột C.
- Và để loại bỏ các ô trống này, đi đến Kiểm tra dữ liệu từ Tab Dữ liệu.
- Thay đổi ô cuối cùng của phạm vi thành C11 vì danh sách đã lọc của bạn có phạm vi C5 đến C11 trong Nguồn.
- Bây giờ nhấp OK. Bạn sẽ không còn ô trống nào trong danh sách thả xuống.
Đây là một cách khác bạn có thể làm cho danh sách thả xuống không có ô trống.
Đọc thêm: Cách sử dụng câu lệnh IF trong công thức Kiểm tra dữ liệu trong Excel (6 cách)
Phần thực hành
Trong phần này, tôi cung cấp cho bạn bộ dữ liệu để bạn có thể tự thực hành các phương pháp này.
Kết luận
Tóm lại, tôi đã cố gắng giải thích một số phương pháp loại bỏ ô trống khỏi danh sách kiểm tra dữ liệu trong Excel. Các phương pháp này hơi phức tạp nhưng tôi đã cố gắng tóm tắt chúng theo cách rất đơn giản và dễ hiểu. Tôi yêu cầu bạn để lại một số phản hồi về bài viết này trong phần bình luận, và cũng nếu bạn có ý tưởng hoặc câu hỏi riêng, hãy để lại trong hộp bình luận.
Các bài viết liên quan
- Áp dụng Kiểm tra dữ liệu tùy chỉnh cho nhiều tiêu chí trong Excel (4 ví dụ)
- Cách sử dụng Kiểm tra dữ liệu trong Excel với Màu sắc (4 cách)
- Cách sử dụng Danh sách Kiểm tra dữ liệu từ Trang tính khác (6 phương pháp)
- Excel VBA tạo Danh sách Kiểm tra dữ liệu từ Mảng
- Cách sử dụng Phạm vi có tên cho Danh sách Kiểm tra dữ liệu với VBA trong Excel