Công thức chung
(=MATCH(2,1/(range"")))
Tóm lược
Để có được vị trí tương đối cuối cùng (tức là hàng cuối cùng, cột cuối cùng) cho dữ liệu hỗn hợp có thể chứa các ô trống, bạn có thể sử dụng hàm MATCH như mô tả bên dưới.
Lưu ý: đây là công thức mảng và phải được nhập bằng Control + Shift + Enter.
Trong ví dụ được hiển thị, công thức trong E5 là:
(=MATCH(2,1/(B4:B10"")))
Vị trí * tương đối * cuối cùng, không phải hàng trên trang tính
Khi xây dựng các công thức nâng cao hơn, bạn thường phải tìm ra vị trí cuối cùng của dữ liệu trong danh sách. Tùy thuộc vào dữ liệu, đây có thể là hàng cuối cùng có dữ liệu, cột cuối cùng có dữ liệu hoặc giao điểm của cả hai. Chúng tôi muốn * vị trí tương đối * cuối cùng bên trong một phạm vi nhất định không phải là số hàng trên trang tính:
Giải trình
Công thức này sử dụng hàm MATCH được cấu hình để tìm vị trí của ô không trống cuối cùng trong một phạm vi.
Làm việc từ trong ra ngoài, mảng tra cứu bên trong MATCH được xây dựng như sau:
=1/(B4:B10"")) =1/(TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE) =(1;#DIV/0!;1;#DIV/0!;1;1;#DIV/0!)
Lưu ý: tất cả các giá trị trong mảng là 1 hoặc # DIV / 0! lỗi.
MATCH sau đó được đặt để khớp với giá trị 2 trong "chế độ so khớp gần đúng", bằng cách bỏ qua đối số thứ 3 sẽ bị bỏ qua.
Vì giá trị tra cứu của 2 sẽ không bao giờ được tìm thấy, MATCH sẽ luôn tìm giá trị 1 cuối cùng trong mảng tra cứu, tương ứng với ô trống cuối cùng.
Cách tiếp cận này sẽ hoạt động với bất kỳ loại dữ liệu nào, bao gồm số, văn bản, ngày tháng, v.v. Nó cũng hoạt động với chuỗi văn bản rỗng được trả về bởi các công thức như sau:
=IF(A1<100,"")
Dải động
Bạn có thể sử dụng công thức này để tạo phạm vi động với các hàm khác như INDEX và OFFSET. Xem các liên kết bên dưới để biết ví dụ và giải thích:
- Dải động với INDEX và COUNTA
- Dải động với OFFSET và COUNTA
Cảm hứng cho bài viết này đến từ cuốn sách xuất sắc Control + Shift + Enter của Mike Girvin, nơi Mike đã giải thích rất tốt khái niệm "vị trí tương đối cuối cùng".
