Tải xuống sổ làm việc mẫu
Hướng dẫn này sẽ trình bày cách tìm hàng không trống cuối cùng trong tập dữ liệu trong Excel và Google Trang tính.
Tìm hàng cuối cùng với dữ liệu
Thường sẽ hữu ích khi biết dữ liệu của bạn kết thúc ở hàng nào. Nếu phạm vi của bạn có hoặc có thể có ô trống, bạn có thể tìm hàng không trống cuối cùng bằng một trong các phương pháp bên dưới.
Phương pháp phổ quát
Phương pháp đầu tiên sử dụng Hàm ROW và MAX và có thể được sử dụng với bất kỳ loại dữ liệu nào:
| 1 | = MAX ((B: B "") * (ROW (B: B))) |
Hãy phân tích công thức này.
Chúng tôi bắt đầu bằng cách sử dụng một bài kiểm tra logic trên cột dữ liệu. Công thức của chúng tôi xem xét toàn bộ cột (B: B) và xuất ra TRUE cho các ô không trống và FALSE cho các ô trống.
| 1 | = B: B "" |
Hàm ROW tạo ra số hàng của một ô nhất định. Nếu chúng tôi không cung cấp cho nó một đầu vào ô cụ thể, nó sẽ cung cấp số hàng của ô mà nó nằm trong đó.
| 1 | = ROW () |
Nhân mỗi số hàng với các giá trị TRUE (= 1) hoặc FALSE (= 0) tương ứng, trả về số hàng cho một ô được điền và số 0 cho ô trống.
| 1 | = E3 * F3 |
Hàm MAX cung cấp tối đa một bộ số. Trong ví dụ này, vì tất cả các ô trống đều tạo ra giá trị 0 nên giá trị lớn nhất là số hàng cao nhất.
| 1 | = MAX (G3: G12) |
Kết hợp các bước này cho chúng ta công thức ban đầu:
| 1 | = MAX ((B: B "") * (ROW (B: B))) |
Xin lưu ý rằng đây là một công thức mảng, vì vậy nếu bạn đang sử dụng Excel 2022 trở xuống, bạn phải nhấn CTRL + SHIFT + ENTER để kích hoạt nó.
Phương pháp cho phạm vi văn bản
Nếu phạm vi (không liên tục) của bạn chỉ chứa các giá trị văn bản và ô trống, bạn có thể sử dụng công thức ít phức tạp hơn chứa các Hàm REPT và MATCH:
| 1 | = MATCH (REPT ("z", 50), B: B) |
Hãy xem công thức này hoạt động như thế nào.
Hàm REPT
Hàm REPT lặp lại một chuỗi văn bản một số lần nhất định. Đối với ví dụ này, chúng ta có thể sử dụng nó để tạo một chuỗi văn bản sẽ là chuỗi cuối cùng trong bất kỳ danh sách được sắp xếp theo thứ tự bảng chữ cái nào. Nếu chúng ta lặp lại "z" 50 lần, điều này sẽ hoạt động đối với hầu hết mọi cột dữ liệu văn bản; không có mục nào đến trước “zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz” theo thứ tự bảng chữ cái.
| 1 | = REPT ("z", 50) |
Hàm MATCH
Hàm MATCH tìm một giá trị tra cứu đã cho trong một mảng.
Chúng tôi thực hiện tìm kiếm toàn bộ cột dữ liệu cho chuỗi văn bản 50-z của chúng tôi. Việc bỏ qua đầu vào loại đối sánh trong Hàm MATCH cho biết nó tìm một giá trị gần đúng hơn là một đối sánh chính xác.
| 1 | = MATCH (REPT ("z", 50), B: B) |
Hàm MATCH tìm kiếm trong Cột B và tìm chuỗi văn bản gồm 50 “z” s của chúng ta. Vì nó không tìm thấy nó, công thức trả về vị trí của ô không trống cuối cùng. Ô này chứa giá trị cuối cùng trong mảng tra cứu nhỏ hơn (hoặc bằng) giá trị tra cứu.
Hãy nhớ rằng công thức này chỉ hoạt động khi phạm vi của bạn chỉ chứa văn bản và ô trống (hoặc ít nhất giá trị của ô cuối cùng không phải là số).
Tìm hàng cuối cùng với dữ liệu trong Google Trang tính
Các công thức này hoạt động giống hệt nhau trong Google Trang tính cũng như trong Excel.
