Nội dung
Trong nhiều trường hợp, các hàng và cột trống có thể gây khó khăn cho bảng. Các chức năng tiêu chuẩn để sắp xếp, lọc, tóm tắt, tạo bảng tổng hợp, v.v. coi các hàng và cột trống như một dấu ngắt bảng, mà không cần lấy dữ liệu nằm xa hơn phía sau chúng. Nếu có nhiều khoảng trống như vậy, thì việc loại bỏ chúng theo cách thủ công có thể rất tốn kém và sẽ không hiệu quả để loại bỏ tất cả cùng một lúc “hàng loạt” bằng cách sử dụng bộ lọc, bởi vì bộ lọc cũng sẽ “vấp ngã” khi nghỉ.
Hãy xem xét một số cách để giải quyết vấn đề này.
Phương pháp 1. Tìm kiếm các ô trống
Đây có thể không phải là cách thuận tiện nhất, nhưng chắc chắn là cách dễ nhất đáng được đề cập.
Giả sử chúng ta đang xử lý một bảng như vậy có chứa nhiều hàng và cột trống bên trong (được đánh dấu để rõ ràng):
Giả sử chúng ta chắc chắn rằng cột đầu tiên của bảng (cột B) luôn chứa tên của một thành phố. Khi đó các ô trống trong cột này sẽ là dấu hiệu của các hàng trống không cần thiết. Để nhanh chóng xóa tất cả chúng, hãy làm như sau:
- Chọn phạm vi với các thành phố (B2: B26)
- cắm chìa khóa F5 và sau đó nhấn Đánh dấu (Chuyển đến Đặc biệt) hoặc chọn trên tab Trang chủ - Tìm và Chọn - Chọn một nhóm ô (Trang chủ - Tìm & Chọn - Chuyển đến phần đặc biệt).
- Trong cửa sổ mở ra, hãy chọn tùy chọn Ô trống (Khoảng trống) và báo chí OK - tất cả các ô trống trong cột đầu tiên của bảng của chúng ta nên được chọn.
- Bây giờ hãy chọn trên tab Trang Chủ Lệnh Xóa - Xóa hàng khỏi trang tính (Xóa - Xóa hàng) hoặc nhấn phím tắt Ctrl+trừ - và nhiệm vụ của chúng ta đã được giải quyết.
Tất nhiên, bạn có thể loại bỏ các cột trống theo cách chính xác, sử dụng tiêu đề bảng làm cơ sở.
Phương pháp 2: Tìm kiếm các hàng trống
Như bạn có thể đã tìm ra, phương pháp trước sẽ chỉ hoạt động nếu dữ liệu của chúng tôi nhất thiết phải chứa các hàng và cột được điền đầy đủ, có thể được kết nối khi tìm kiếm các ô trống. Nhưng điều gì sẽ xảy ra nếu không có độ tin cậy như vậy và dữ liệu cũng có thể chứa các ô trống?
Hãy xem bảng sau đây, chẳng hạn cho một trường hợp như vậy:
Ở đây, cách tiếp cận sẽ phức tạp hơn một chút:
- Nhập vào ô A2 hàm ĐẾM (QUỐC GIA), sẽ tính toán số lượng ô đã điền trong hàng bên phải và sao chép công thức này xuống toàn bộ bảng:
- Chọn ô A2 và bật bộ lọc bằng lệnh Dữ liệu - Bộ lọc (Dữ liệu - Bộ lọc) hoặc phím tắt Ctrl+sự thay đổi+L.
- Hãy lọc ra các số không theo cột được tính toán, tức là tất cả các hàng không có dữ liệu.
- Nó vẫn còn để chọn các dòng đã lọc và xóa chúng bằng lệnh Trang chủ - Xóa - 'Xóa hàng khỏi trang tính (Trang chủ - Xóa - Xóa hàng) hoặc phím tắt Ctrl+trừ.
- Chúng tôi tắt bộ lọc và lấy dữ liệu của chúng tôi mà không có dòng trống.
Thật không may, thủ thuật này không còn có thể được thực hiện với các cột - Excel vẫn chưa học cách lọc theo cột.
Phương pháp 3. Macro để loại bỏ tất cả các hàng và cột trống trên một trang tính
Bạn cũng có thể sử dụng một macro đơn giản để tự động hóa tác vụ này. Nhấn phím tắt Khác+F11 hoặc chọn từ tab nhà phát triển - Ngôn ngữ lập trình (Nhà phát triển - Trình chỉnh sửa Visual Basic). Nếu các tab nhà phát triển không hiển thị, bạn có thể bật nó thông qua Tệp - Tùy chọn - Thiết lập ruy-băng (Tệp - Tùy chọn - Tùy chỉnh ruy-băng).
Trong cửa sổ trình soạn thảo Visual Basic mở ra, hãy chọn lệnh menu Chèn - Mô-đun và trong mô-đun trống xuất hiện, hãy sao chép và dán các dòng sau:
Sub DeleteEmpty () Dim r As Long, rng As Range 'удаляем пустые строки For r = 1 To ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count If Application.CountA (Rows (r)) = 0 Then If rng Không có gì thì Đặt rng = Hàng (r) Bộ khác rng = Liên hợp (rng, Hàng (r)) Kết thúc Nếu Tiếp theo r Nếu Không rng Không có gì thì Rng Xóa 'удаляем пустые столбцы Đặt rng = Không có gì Cho r = 1 Tới ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count If Application.CountA (Columns (r)) = 0 Then If rng Is Nothing Then Set rng = Columns (r) Else Set rng = Union (rng, Columns ( r)) Kết thúc Nếu Tiếp theo r Nếu Không Không có gì thì Rng. Xóa Kết thúc Sub
Đóng trình chỉnh sửa và quay lại Excel.
Bây giờ nhấn kết hợp Khác+F8 hoặc nút Macros chuyển hướng nhà phát triển. Cửa sổ mở ra sẽ liệt kê tất cả các macro hiện có sẵn để bạn chạy, bao gồm cả macro bạn vừa tạo. XóaTrống. Chọn nó và nhấp vào nút chạy (chạy) - tất cả các hàng và cột trống trên trang tính sẽ bị xóa ngay lập tức.
Phương pháp 4: Truy vấn nguồn
Một cách khác để giải quyết vấn đề của chúng tôi và một tình huống rất phổ biến là xóa các hàng và cột trống trong Power Query.
Đầu tiên, hãy tải bảng của chúng ta vào Trình soạn thảo truy vấn Power Query. Bạn có thể chuyển đổi nó thành một “thông minh” động bằng phím tắt Ctrl + T hoặc chỉ cần chọn phạm vi dữ liệu của chúng tôi và đặt tên cho nó (ví dụ: Ngày) trong thanh công thức, chuyển đổi thành có tên:
Bây giờ chúng ta sử dụng lệnh Data - Get data - From table / range (Dữ liệu - Lấy dữ liệu - Từ bảng / dải ô) và tải mọi thứ vào Power Query:
Sau đó, mọi thứ rất đơn giản:
- Ta xóa các dòng trống bằng lệnh Home - Reduce lines - Delete lines - Delete rỗng (Home - Remove Rows - Remove blank lines).
- Bấm chuột phải vào tiêu đề của cột Thành phố đầu tiên và chọn lệnh Bỏ chia các Cột Khác từ trình đơn ngữ cảnh. Bảng của chúng ta sẽ là, vì nó được gọi một cách chính xác về mặt kỹ thuật, bình thường hóa - được chuyển đổi thành ba cột: thành phố, tháng và giá trị từ giao điểm của thành phố và tháng từ bảng gốc. Điểm đặc biệt của thao tác này trong Power Query là nó bỏ qua các ô trống trong dữ liệu nguồn, đó là những gì chúng ta cần:
- Bây giờ chúng ta thực hiện thao tác ngược lại - chúng ta chuyển bảng kết quả trở lại thành một bảng hai chiều để đưa nó về dạng ban đầu. Chọn cột có tháng và trên tab Chuyển đổi chọn một đội Tổng hợp cột (Chuyển đổi - Cột xoay). Trong cửa sổ mở ra, dưới dạng một cột giá trị, hãy chọn giá trị cuối cùng (Giá trị) và trong các tùy chọn nâng cao - thao tác Không tổng hợp (Không tổng hợp):
- Nó vẫn là tải kết quả trở lại Excel bằng lệnh Trang chủ - Đóng và tải - Đóng và tải vào… (Trang chủ - Đóng & Tải - Đóng & Tải vào…)
- Macro là gì, nó hoạt động như thế nào, sao chép văn bản của macro ở đâu, cách chạy macro?
- Điền vào tất cả các ô trống trong danh sách bằng giá trị của các ô mẹ
- Xóa tất cả các ô trống khỏi một phạm vi nhất định
- Xóa tất cả các hàng trống trong trang tính bằng tiện ích bổ sung PLEX