Tải xuống sổ làm việc mẫu
Hướng dẫn này sẽ dạy bạn cách tra cứu giá trị cuối cùng trong cột hoặc hàng trong Excel.
Giá trị cuối cùng trong cột
Bạn có thể sử dụng Hàm LOOKUP để tìm ô trống cuối cùng trong một cột.
| 1 | = LOOKUP (2,1 / (B: B ""), B: B) |
Hãy xem qua công thức này.
Phần của công thức B: B ”” trả về một mảng chứa các giá trị True và False: {FALSE, TRUE, TRUE,…}, kiểm tra mỗi ô trong cột B là trống (FALSE).
| 1 | = LOOKUP (2,1 / ({FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE;…), B: B) |
Các giá trị Boolean này chuyển đổi thành 0 hoặc 1 và được sử dụng để chia 1.
| 1 | = LOOKUP (2, {# DIV / 0!; 1; 1; 1; 1; 1; # DIV / 0!;, B: B) |
Đây là lookup_vector cho Hàm LOOKUP. Trong trường hợp của chúng ta, lookup_value là 2, nhưng giá trị lớn nhất trong lookup_vector là 1, do đó, Hàm LOOKUP sẽ khớp với giá trị 1 cuối cùng trong mảng và trả về giá trị tương ứng trong result_vector.
Nếu bạn chắc chắn rằng bạn chỉ có các giá trị số trong cột của mình, dữ liệu của bạn bắt đầu từ hàng 1 và phạm vi dữ liệu của bạn liên tục, bạn có thể sử dụng công thức đơn giản hơn một chút với các hàm INDEX và COUNT.
| 1 | = INDEX (B: B, COUNT (B: B)) |
Hàm COUNT trả về số ô chứa đầy dữ liệu trong phạm vi liên tục (4) và Hàm INDEX do đó cung cấp giá trị của ô trong hàng tương ứng này (thứ 4).
Để tránh các lỗi có thể xảy ra khi phạm vi dữ liệu của bạn chứa hỗn hợp các giá trị số và không phải số hoặc thậm chí một số ô trống, bạn có thể sử dụng Hàm LOOKUP cùng với các hàm ISBLANK và NOT.
| 1 | = LOOKUP (2,1 / (KHÔNG (ISBLANK (B: B))), B: B) |
Hàm ISBLANK trả về một mảng chứa các giá trị True và False, tương ứng với 1’s và 0’s. Hàm NOT thay đổi True (tức là 1) thành False và False (tức là 0) thành True. Nếu chúng ta đảo ngược mảng kết quả này (khi chia 1 cho mảng này), chúng ta nhận được một mảng kết quả chứa lại # DIV / 0! lỗi và 1, có thể được sử dụng như mảng tra cứu (lookup_vector) trong Hàm LOOKUP của chúng tôi. Chức năng của Hàm LOOKUP sau đó giống như trong ví dụ đầu tiên của chúng tôi: nó trả về giá trị của vectơ kết quả ở vị trí của số 1 cuối cùng trong mảng tra cứu.
Khi bạn cần số hàng có mục nhập cuối cùng được trả lại, bạn có thể sửa đổi công thức được sử dụng trong ví dụ đầu tiên của chúng tôi cùng với Hàm ROW trong result_vector của bạn.
| 1 | = LOOKUP (2,1 / (B: B ""), ROW (B: B)) |
Giá trị cuối cùng trong hàng
Để nhận giá trị của ô không trống cuối cùng trong một hàng chứa đầy dữ liệu số, bạn có thể muốn sử dụng cách tiếp cận tương tự nhưng với các hàm khác nhau: Hàm OFFSET cùng với các hàm MATCH và MAX.
| 1 | = OFFSET (Tham chiếu, Hàng, Cột) |
| 1 | = OFFSET (B2,0, MATCH (MAX (B2: XFD2) + 1, B2: XFD2,1) -1) |
Hãy xem công thức này hoạt động như thế nào.
Hàm MATCH
Chúng tôi sử dụng Hàm MATCH để "đếm" có bao nhiêu giá trị ô dưới 1 + giá trị tối đa của tất cả các giá trị trong hàng 2 bắt đầu từ B2.
| 1 | = MATCH (lookup_value, lookup_array, [match_type]) |
| 1 | = MATCH (MAX (B2: XFD2) + 1, B2: XFD2,1) |
Giá trị lookup_value của Hàm MATCH là giá trị tối đa của tất cả các giá trị trong hàng2 + 1. Vì giá trị này rõ ràng là không tồn tại trong hàng 2 và loại so khớp được đặt thành 1 (nhỏ hơn hoặc bằng giá trị lookup_value), Hàm MATCH sẽ trả về vị trí của ô “đã kiểm tra” cuối cùng trong mảng, tức là số ô chứa đầy dữ liệu trong phạm vi B2: XFD2 (XFD là cột cuối cùng trong các phiên bản Excel mới hơn).
Hàm OFFSET
Sau đó, chúng ta sử dụng Hàm OFFSET để lấy giá trị của ô này, vị trí của ô này được trả về bởi Hàm MATCH.
| 1 | = OFFSET (B2,0, C4-1) |
