Công thức chung
=MATCH(bigtext,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 văn bản (có hoặc không có ô trống), bạn có thể sử dụng hàm MATCH. Trong ví dụ được hiển thị, công thức trong D5 là:
=MATCH(REPT("z",255),B4:B11)
Giải trình
Công thức này sử dụng hàm MATCH ở chế độ đối sánh gần đúng để xác định giá trị văn bản cuối cùng trong một phạm vi. Đối sánh gần đúng được bật bằng cách đặt đối số thứ 3 trong MATCH thành 1 hoặc bỏ qua đối số này, đối số này mặc định là 1.
Giá trị tra cứu là một cái gọi là "văn bản lớn" (đôi khi được viết tắt là "bigtext") có chủ đích là một giá trị "lớn hơn" bất kỳ giá trị nào sẽ xuất hiện trong phạm vi. Khi làm việc với văn bản, sắp xếp theo thứ tự bảng chữ cái, điều này có nghĩa là giá trị văn bản sẽ luôn xuất hiện ở cuối thứ tự sắp xếp theo bảng chữ cái.
Vì công thức này khớp với văn bản, nên ý tưởng là tạo ra một giá trị tra cứu sẽ không bao giờ xuất hiện trong văn bản thực tế, nhưng sẽ luôn là giá trị cuối cùng. Để làm điều đó, chúng tôi sử dụng hàm REPT để lặp lại ký tự "z" 255 lần. Số 255 đại diện cho số lượng ký tự lớn nhất mà MATCH cho phép trong một giá trị tra cứu.
Khi MATCH không thể tìm thấy giá trị này, nó sẽ "lùi" về giá trị văn bản cuối cùng trong phạm vi và trả về vị trí của giá trị đó.
Lưu ý: phương pháp này hoạt động tốt với các ô trống trong phạm vi, nhưng không đáng tin cậy với dữ liệu hỗn hợp bao gồm cả số và văn bản.
Vị trí tương đối cuối cùng so với số hàng cuối cùng
Khi xây dựng các công thức nâng cao tạo phạm vi động, 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. Lưu ý: 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 số hàng trên trang tính:
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 khái niệm "vị trí tương đối cuối cùng".
