Bài viết này sẽ mô tả ngắn gọn một số phương pháp có giá trị về cách xóa khoảng trống từ danh sách xác thực dữ liệu trong Excel. Khi chúng ta có khoảng trống trong danh sách biểu đồ excel, nó cũng vẫn nằm trong danh sách xác thực dữ liệu không mong muốn.
Vì vậy, tôi sẽ làm việc trên tập dữ liệu sau để chỉ cho bạn cách bạn có thể xóa khoảng trống từ danh sách xác thực dữ liệu trong Excel.
Sự cố khi tạo danh sách xác thực dữ liệu với ô trống
Hãy để tôi cho biết điều gì sẽ xảy ra nếu chúng tôi tạo danh sách thả xuống bao gồm các ô trống. Trước tiên, chúng tôi 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 >> Xác thực dữ liệu
- Một hộp thoại sẽ hiển thị. Chọn Danh sách từ Cho phép thanh (Được hiển thị trong hình sau).
- Bây giờ hãy nhấp vào biểu tượng được đánh dấu .
- Sau đó, chọn các ô B5 tới B14 và nhấp vào biểu tượng được đánh dấu .
- Sau đó, chỉ cần nhấp vào OK .
Bằng cách này, chúng tôi vừa tạo danh sách thả xuống .
Tại đây, bạn có thể nhận thấy rằng 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 xóa khoảng trống khỏi danh sách xác thực dữ liệu trong Excel
1. Xóa khoảng trống khỏi danh sách xác thực dữ liệu bằ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ỳ khoảng trống nào trong cột đó. Trước tiên, bạn cần lọc ra khỏi khoảng trống từ dữ liệu của bạn. Hãy thảo luận về quá trình này.
Chúng tôi cần một số sửa đổi đối với tập dữ liệu của mình.
- Hãy thêm một cột mới phía 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 tôi đang sử dụng cho danh sách thả xuống dưới dạng Danh sách tên có khoảng trống và Danh sách không có khoảng trống , tương ứng. (Để xem cách tạo danh sách thả xuống , vui lòng chuyển đến Phần 1 ).
Các bước:
- Đầu tiên, hãy nhập công thức sau vào ô C5 .
=FILTER(B5:B14,B5:B14<>"")
Đây là BỘ LỌC hàm sẽ lấy phạm vi B5:B14 và kiểm tra bất kỳ khoảng trống nào giữa phạm vi . Sau đó, nó bộ lọc ra trống hoặc trống ô từ 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ỳ khoảng trống nào .
- Sau đó, chọn Trình quản lý tên từ Tab Công thức và nhấp vào Mới .
- Cung cấp phạm vi của bạn một cái tên. Tôi sẽ sử dụng NameNonBlanks như tên của phạm vi .
- Và sau đó viết công thức sau trong Đề cập đến
=offset(offset!$C$5,0,0,counta(offset!$C$4:$C$16)-1,1)
Trong trường hợp này, chúng tôi muốn sử dụng thêm một số ô để có thể nhập một số tên mới, nhưng chúng tôi không muốn ô trống trong danh sách thả xuống của chúng tôi cho những không gian đó. Tại đây, chúng tôi đang tạo các mục nhập mới cho danh sách xác thực dữ liệu từ C12 đến C16 bằng cách đặt công thức đó. Hãy ghi nhớ điều đó ‘ bù đắp!’ đề cập đến trang tính tên mà chúng tôi đang sử dụng.
- Bây giờ hãy nhấp vào 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 xác thực dữ liệu .
- Thay đổi Tên nguồn tới =NameNonBlanks .
- Nhấp vào OK .
- Chọn danh sách thả xuống thanh trong ô D5 . Bạn sẽ thấy danh sách tên chúng tôi đang sử dụng.
- Bây giờ, hãy viết một số tên mới trong ô C12 đến C16 .
- Sau đó, chọn danh sách xác thực 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 bạn . Bạn không thể thấy bất kỳ mục nhập mới nào trong ô C16 vì chúng không nằm trong phạm vi của bạn .
Bằng cách làm theo phương pháp 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 xác thực dữ liệu của bạn mà không tạo bất kỳ khoảng trống nào trong đó.
Đọc thêm: Cách tạo danh sách thả xuống trong Excel để xác thực dữ liệu (8 cách)
2. Sử dụng Đi tới Lệnh đặc biệt để xóa khoảng trống khỏi danh sách
Chúng tôi đã tạo danh sách thả xuống ( phần 1 ), bạn thấy rằng có khoảng trống còn lại trong đó. Để xóa chúng, chúng ta chỉ cần thực hiện theo các bước sau.
Các bước:
- Chọn các ô B5 tới B14 rồi chọn Trang chủ >> Tìm &Chọn >> Chuyển đến Đặc biệt .
- Sau đó, chọn Khoảng trống và sau đó nhấp vào OK .
- Thao tác này sẽ chọn ô trống .
- Bây giờ, hãy chọn bất kỳ ô trống nào sau đây , thực hiện nhấp chuột phải trên đó và chọn Xóa để Xóa Khoảng trống .
- Bạn sẽ thấy một hộp thoại . Chọn Shift Cells Up và nhấp vào OK .
- Thao tác này sẽ xóa khoảng trống từ danh sách ban đầu cũng như từ danh sách thả xuống .
Bằng cách làm theo phương pháp này, bạn có thể dễ dàng xóa khoảng trống hoặc ô trống từ danh sách thả xuống .
Đọc thêm:Tạo danh sách thả xuống xác thực dữ liệu với nhiều lựa chọn trong Excel
3. Sử dụng chức năng bộ lọc Excel để loại bỏ khoảng trống khỏi danh sách xác thực dữ liệu
Chúng tôi có thể áp dụng FILTER chức năng trong khía cạnh này. Chúng tôi sẽ sử dụng tập dữ liệu từ Phần 2 . Để xem cách tạo danh sách thả xuống , vui lòng chuyển đến Phần 1 .
Các bước:
- Nhập công thức sau vào ô C5 .
=FILTER(B5:B14,B5:B14<>"")
Đây là BỘ LỌC hàm sẽ lấy phạm vi B5:B14 và kiểm tra bất kỳ khoảng trống nào giữa phạm vi . Sau đó, nó bộ lọc ra trống hoặc trống ô từ danh sách.
- Nhấn nút ENTER và bạn sẽ thấy danh sách tên không có bất kỳ khoảng trống nào .
- Nhưng nếu bạn đi tới Danh sách thả xuống , bạn sẽ vẫn thấy rằng nó chứa khoảng trống từ cột C .
- Vì vậy, để loại bỏ những khoảng trống này , đi tới Xác thực dữ liệu từ Tab dữ liệu .
- Thay đổi ô cuối cùng của phạm vi đến C11 dưới dạng đã lọc của bạn danh sách có phạm vi C5 đến C11 trong Nguồn
- Bây giờ hãy nhấp vào OK . Bây giờ bạn sẽ không có chỗ trống các ô trong danh sách thả xuống của bạn .
Đây là một cách khá hiệu quả để xóa khoảng trống từ danh sách thả xuống .
Đọc thêm: Danh sách thả xuống xác thực dữ liệu Excel có 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 Xác thực Dữ liệu Excel
- [Đã sửa lỗi] Xác thực dữ liệu không hoạt động để sao chép dán trong Excel (với Giải pháp)
- Cách Tạo Danh sách Xác thực Dữ liệu từ Bảng trong Excel (3 Phương pháp)
- Áp dụng Nhiều Xác thực Dữ liệu trong Một Ô trong Excel (3 Ví dụ)
- Chỉ xác thực dữ liệu Excel bằng chữ và số (Sử dụng Công thức Tùy chỉnh)
4. Kết hợp IF, COUNTIF, ROW, INDEX và các hàm nhỏ để xóa khoảng trống khỏi danh sách xác thực dữ liệu
Chúng tôi cũng có thể sử dụng kết hợp IF , COUNTIF , ROW , INDEX và NHỎ chức năng xóa các ô trống khỏi danh sách xác thực dữ liệu . Nó sẽ hơi phức tạp một chút. Chúng tôi sẽ sử dụng tập dữ liệu từ Phần 2 . Và để xem cách tạo danh sách thả xuống , chuyển đến Phần 1 .
Các bước:
- Đầu tiên, hãy 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 tiên là COUNTIF ($ B $ 5:$ B $ 14, ”? *”)
- COUNTIF đếm hàm không trống văn bản tại đây và đó là lý do tại sao chúng tôi nhận được 7 tên trong cột C .
- ROW hàm trả về hàng số của một ô và ô trống của chúng tôi ở vị trí 5 từ ô B5 . Chúng tôi đang trừ 4 bởi vì chúng tôi muốn nó ít hơn thế.
- Bây giờ, hãy nhấn ENTER .
- Sử dụng Xử lý điền sang Tự động điền các ô thấp hơn.
- Bây giờ chúng tôi có Danh sách tên không có bất kỳ khoảng trống nào . Nhưng nếu chúng tôi nhấp vào danh sách xác thực dữ liệu , chúng tôi vẫn thấy khoảng trống trong danh sách thả xuống .
- Và để loại bỏ những khoảng trống này , đi tới Xác thực dữ liệu từ Tab dữ liệu .
- Thay đổi ô cuối cùng của phạm vi đến C11 dưới dạng đã lọc của bạn danh sách có phạm vi C5 đến C11 trong Nguồn .
- Bây giờ hãy nhấp vào OK . Bây giờ bạn sẽ không có chỗ trống các ô trong danh sách thả xuống của bạn .
Do đó, bạn có thể tạo danh sách xác thực dữ liệu không có khoảng trống .
Đọc thêm: Giá trị mặc định trong danh sách xác thực dữ liệu với Excel VBA (Macro và UserForm)
5. Sử dụng các chức năng kết hợp để xóa các ô trống khỏi danh sách xác thực dữ liệu
Chúng tôi cũng có thể sử dụng các công thức lồng nhau kết hợp với ADDRESS , CHỈ ĐỊNH , COUNTBLANK , NẾU và NHỎ chức năng. Hãy thảo luận về thủ tục. Chúng tôi sẽ sử dụng tập dữ liệu từ Phần 2 . Bạn cũng có thể chuyển đến Phần 1 để xem cách tạo danh sách thả xuống / danh sách xác thực dữ liệu .
Các bước:
- Đầu tiên, hãy 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 về cách hoạt động của công thức này. Nó đi qua phạm vi B5:B14 và kiểm tra ô trống với sự trợ giúp của COUNTBLANK hàm số. Sau đó, nó cũng kiểm tra ô nào không trống trong suốt B5:B14 và do đó nó trả về ô không trống .
- Nhấn CTRL + SHIFT + ENTER (vì nó là một mảng công thức) và bạn sẽ thấy kết quả đầu ra trong ô C5 như bên dưới.
- Bây giờ, hãy sử dụng Xử lý điền sang Tự động điền các ô thấp hơn.
- Nhưng nếu bạn đi tới Danh sách thả xuống , bạn sẽ vẫn thấy rằng nó chứa khoảng trống từ cột C .
- Và để loại bỏ những khoảng trống này , đi tới Xác thực dữ liệu từ Tab dữ liệu .
- Thay đổi ô cuối cùng của phạm vi đến C11 dưới dạng đã lọc của bạn danh sách có phạm vi C5 đến C11 trong Nguồn .
- Bây giờ hãy nhấp vào OK . Bây giờ bạn sẽ không có chỗ trống các ô trong danh sách thả xuống của bạn .
Đây là một cách khác mà bạn có thể tạo danh sách thả xuống không có khoảng trống .
Đọc thêm: Cách sử dụng câu lệnh IF trong công thức xác thực 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 tập dữ liệu để bạn có thể tự mình 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 xóa khoảng trống khỏi danh sách xác thực dữ liệu trong Excel. Những phương pháp này hơi phức tạp một chút nhưng tôi đã cố gắng tóm tắt chúng một 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à nếu bạn có ý tưởng hoặc câu hỏi của riêng mình, hãy để lại chúng trong hộp bình luận.
Các bài viết có liên quan
- Áp dụng Xác thực Dữ liệu Tùy chỉnh cho Nhiều Tiêu chí trong Excel (4 Ví dụ)
- Cách Sử dụng Xác thực Dữ liệu trong Excel với Màu (4 Cách)
- Cách sử dụng danh sách xác thực dữ liệu từ một trang tính khác (6 phương pháp)
- Excel VBA để tạo danh sách xác thực dữ liệu từ mảng
- Cách sử dụng Dải ô đã Đặt tên cho Danh sách Xác thực Dữ liệu với VBA trong Excel