Công thức chung
=$A$1:INDEX($A:$A,lastrow)
Tóm lược
Một cách để tạo phạm vi được đặt tên động trong Excel là sử dụng hàm INDEX. Trong ví dụ được hiển thị, phạm vi có tên "dữ liệu" được xác định theo công thức sau:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
giải quyết cho phạm vi $ A $ 2: $ A $ 10.
Lưu ý: công thức này dùng để xác định một phạm vi đã đặt tên có thể được sử dụng trong các công thức khác.
Giải trình
Trang này hiển thị một ví dụ về phạm vi được đặt tên động được tạo bằng hàm INDEX cùng với hàm COUNTA. Các dải ô được đặt tên động sẽ tự động mở rộng và thu nhỏ khi dữ liệu được thêm vào hoặc xóa. Chúng là một giải pháp thay thế cho việc sử dụng Bảng Excel, bảng này cũng thay đổi kích thước khi dữ liệu được thêm vào hoặc xóa.
Hàm INDEX trả về giá trị tại một vị trí nhất định trong một dải ô hoặc mảng. Bạn có thể sử dụng INDEX để truy xuất các giá trị riêng lẻ hoặc toàn bộ hàng và cột trong một phạm vi. Điều làm cho INDEX đặc biệt hữu ích cho các phạm vi được đặt tên động là nó thực sự trả về một tham chiếu. Điều này có nghĩa là bạn có thể sử dụng INDEX để tạo một tham chiếu hỗn hợp như $ A $ 1: A100.
Trong ví dụ được hiển thị, phạm vi có tên "dữ liệu" được xác định theo công thức sau:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
giải quyết cho phạm vi $ A $ 2: $ A $ 10.
Công thức này hoạt động như thế nào
Trước tiên, hãy lưu ý rằng công thức này được tạo thành hai phần nằm ở hai bên của toán tử phạm vi (:). Ở bên trái, chúng tôi có tham chiếu bắt đầu cho phạm vi, được mã hóa cứng là:
$A$2
Ở bên phải là tham chiếu kết thúc cho phạm vi, được tạo bằng INDEX như sau:
INDEX($A:$A,COUNTA($A:$A))
Ở đây, chúng tôi cung cấp INDEX cho tất cả cột A cho mảng, sau đó sử dụng hàm COUNTA để tìm ra "hàng cuối cùng" trong phạm vi. COUNTA hoạt động tốt ở đây vì có 10 giá trị trong cột A, bao gồm một hàng tiêu đề. Do đó, COUNTA trả về 10, đi thẳng vào INDEX dưới dạng số hàng. Sau đó, INDEX trả về một tham chiếu đến $ A $ 10, hàng được sử dụng cuối cùng trong phạm vi:
INDEX($A:$A,10) // resolves to $A$10
Vì vậy, kết quả cuối cùng của công thức là phạm vi này:
$A$2:$A$10
Phạm vi hai chiều
Ví dụ trên hoạt động cho phạm vi một chiều. Để tạo phạm vi động hai chiều trong đó số lượng cột cũng là động, bạn có thể sử dụng cùng một cách tiếp cận, được mở rộng như sau:
=$A$2:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))
Như trước đây, COUNTA được sử dụng để tìm ra "cuối cùng" và chúng tôi sử dụng lại COUNTA để lấy "cột cuối cùng". Chúng được cung cấp cho chỉ mục dưới dạng row_num và column_num tương ứng.
Tuy nhiên, đối với mảng, chúng tôi cung cấp trang tính đầy đủ, được nhập dưới dạng tất cả 1048576 hàng, cho phép INDEX trả về tham chiếu trong không gian 2D.
Lưu ý: Excel 2003 chỉ hỗ trợ 65535 hàng.
Xác định hàng cuối cùng
Có một số cách để xác định hàng cuối cùng (vị trí tương đối cuối cùng) trong tập hợp dữ liệu, tùy thuộc vào cấu trúc và nội dung của dữ liệu trong trang tính:
- Hàng cuối cùng trong dữ liệu hỗn hợp có khoảng trống
- Hàng cuối cùng trong dữ liệu hỗn hợp không có ô trống
- Hàng cuối cùng trong dữ liệu văn bản
- Hàng cuối cùng trong dữ liệu số
