Nội dung
Công thức của vấn đề
Chúng tôi có một loạt các ô với dữ liệu chứa các ô trống:
Nhiệm vụ là loại bỏ các ô trống, chỉ để lại các ô có thông tin.
Phương pháp 1. Thô và nhanh
- Chọn phạm vi ban đầu
- cắm chìa khóa F5, nút tiếp theo Đánh dấu (Đặc biệt). Trong cửa sổ mở ra, hãy chọn Ô trống(Khoảng trống) và nhấp OK.
Tất cả các ô trống trong phạm vi được chọn.
- Chúng tôi đưa ra một lệnh trong menu để xóa các ô đã chọn: nhấp chuột phải- Xóa ô (Xóa ô) với sự dịch chuyển lên.
Phương pháp 2: Công thức mảng
Để đơn giản hóa, hãy đặt tên cho các phạm vi làm việc của chúng tôi bằng cách sử dụng Người quản lý tên (Người quản lý tên) chuyển hướng công thức (Công thức) hoặc trong Excel 2003 trở lên, menu Chèn - Tên - Chỉ định (Chèn - Tên - Xác định)
Đặt tên cho dải ô B3: B10 có sản phẩm nào, phạm vi D3: D10 - Không có sản phẩm nào. Các dải ô phải có cùng kích thước và có thể nằm ở bất kỳ vị trí nào tương đối với nhau.
Bây giờ chọn ô đầu tiên của phạm vi thứ hai (D3) và nhập công thức đáng sợ này vào đó:
=IF(ROW() -ROW(NoEmpty)+1>NOTROWS(YesEmpty)-COUNTBLANK(YesEmpty);””;INDIRECT(ADDRESS(LOWEST((IF(Empty<>“”,ROW(Empty);ROW() + ROWS(Có phần trống))); LINE()-ROW(Không có phần trống)+1); COLUMN(Có phần trống); 4)))
Trong phiên bản tiếng Anh, nó sẽ là:
=IF(ROW()-ROW(NoEmpty)+1>ROWS(Trống)-COUNTBLANK(Trống),””,INDIRECT(ADDRESS(SMALL((IF(Trống<>“),ROW(Trống),ROW() +ROWS(CóEmpty))),ROW()-ROW(KhôngEmpty)+1),CỘT(CóEmpty),4)))
Hơn nữa, nó phải được nhập dưới dạng công thức mảng, tức là nhấn sau khi dán đăng ký hạng mục thi (như thường lệ) và Ctrl + Shift + Enter. Bây giờ công thức có thể được sao chép xuống bằng cách sử dụng tính năng tự động hoàn thành (kéo dấu thập màu đen ở góc dưới bên phải của ô) - và chúng tôi sẽ nhận được phạm vi ban đầu, nhưng không có ô trống:
Phương pháp 3. Chức năng tùy chỉnh trong VBA
Nếu có nghi ngờ rằng bạn sẽ thường xuyên phải lặp lại quy trình loại bỏ các ô trống khỏi phạm vi, thì tốt hơn là bạn nên thêm chức năng của riêng mình để loại bỏ các ô trống vào bộ tiêu chuẩn một lần và sử dụng nó trong tất cả các trường hợp tiếp theo.
Để thực hiện việc này, hãy mở Visual Basic Editor (ALT + F11), chèn một mô-đun trống mới (menu Chèn - Mô-đun) và sao chép văn bản của hàm này vào đó:
Hàm NoBlanks(DataRange As Range) As Variant() Dim N As Long Dim N2 As Long Dim Rng As Range Dim MaxCells As Long Dim Kết quả() As Variant Dim R As Long Dim C As Long MaxCells = Application.WorksheetFunction.Max( _ Application.Caller.Cells.Count, DataRange.Cells.Count) Kết quả điều chỉnh lại (1 đến MaxCells, 1 đến 1) cho mỗi Rng trong DataRange.Cells Nếu Rng.Value <> vbNullString thì N = N + 1 Kết quả (N, 1 ) = Rng.Value End If Next Rng For N2 = N + 1 To MaxCells Result(N2, 1) = vbNullString Next N2 If Application.Caller.Rows.Count = 1 Then NoBlanks = Application.Transpose(Result) Else NoBlanks = Kết quả Chức năng kết thúc nếu kết thúc
Đừng quên lưu tệp và chuyển lại từ Visual Basic Editor sang Excel. Để sử dụng chức năng này trong ví dụ của chúng tôi:
- Chọn đủ phạm vi ô trống, ví dụ F3: F10.
- Đi tới menu Chèn chức năng (Chèn chức năng)hoặc bấm vào nút Chèn chức năng (Chèn chức năng) chuyển hướng công thức (Công thức) trong các phiên bản Excel mới hơn. Trong danh mục Đã xác định người dùng (Đã xác định người dùng) chọn chức năng của chúng tôi Noblanks.
- Chỉ định phạm vi nguồn với khoảng trống (B3: B10) làm đối số của hàm và nhấn Ctrl + Shift + Enterđể nhập hàm dưới dạng công thức mảng.
:
- Xóa tất cả các hàng trống trong bảng cùng một lúc bằng một macro đơn giản
- Xóa tất cả các hàng trống trong trang tính cùng một lúc bằng tiện ích bổ sung PLEX
- Nhanh chóng điền vào tất cả các ô trống
- Macro là gì, nơi chèn mã macro trong VBA