Công thức chung
=MATCH(bignum,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 số (có hoặc không có ô trống), bạn có thể sử dụng hàm MATCH với cái gọi là "số lớn".
Trong ví dụ được hiển thị, công thức trong E5 là:
=MATCH(9.99E+307,B4:B9)
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 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 có * 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:
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ị số 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à cái gọi là "số lớn" (đôi khi được viết tắt là "bignum") có chủ ý lớn hơn bất kỳ giá trị nào sẽ xuất hiện trong phạm vi.
Kết quả là MATCH sẽ "lùi" về giá trị số cuối cùng trong phạm vi và trả lại vị 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ề bignum
Con số lớn nhất mà Excel có thể xử lý là 9,99999999999999E + 307.
Khi sử dụng MATCH theo cách này, bạn có thể sử dụng bất kỳ số lớn nào được đảm bảo lớn hơn bất kỳ giá trị nào trong phạm vi, ví dụ:
=MATCH(1E+06,range) // 1 million =MATCH(1E+09,range) // 1 billion =MATCH(1E+12,range) // 1 trillion
Lợi thế của việc sử dụng 9.99E + 307 hoặc tương tự, đó là (1) một con số khổng lồ và (2) dễ nhận biết là một trình giữ chỗ cho một "số lớn". Bạn sẽ thấy nó được sử dụng trong các công thức Excel nâng cao khác nhau.
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".
