Công thức chung
(=ROW(range)-ROW(range.firstcell)+1)
Tóm lược
Để có được tập hợp đầy đủ các số hàng tương đối trong một phạm vi, bạn có thể sử dụng công thức mảng dựa trên hàm ROW. Trong ví dụ được hiển thị, công thức trong B5: B11 là:
(=ROW(B5:B11)-ROW(B5)+1)
Lưu ý: đây là công thức mảng phải được nhập bằng Control + Shift + Enter. Nếu bạn đang nhập giá trị này trên trang tính (và không phải bên trong công thức khác), hãy thực hiện lựa chọn bao gồm nhiều hàng, nhập công thức và xác nhận bằng Control + Shift + Enter.
Đây là công thức sẽ tiếp tục tạo ra các số tương đối ngay cả khi phạm vi được di chuyển. Tuy nhiên, đây không phải là lựa chọn tốt nếu các hàng cần được sắp xếp, xóa hoặc thêm, vì công thức mảng sẽ ngăn các thay đổi. Các tùy chọn công thức được giải thích ở đây sẽ hoạt động tốt hơn.
Giải trình
Hàm ROW đầu tiên tạo ra một mảng gồm 7 số như sau:
(5;6;7;8;9;10;11)
Hàm ROW thứ hai tạo một mảng chỉ với một mục như sau:
(5)
sau đó được trừ khỏi mảng đầu tiên để mang lại:
(0;1;2;3;4;5;6)
Cuối cùng, 1 được thêm vào để có được:
(1;2;3;4;5;6;7)
Phiên bản chung với phạm vi được đặt tên
Với một phạm vi đã đặt tên, bạn có thể tạo một phiên bản công thức chung hơn bằng cách sử dụng hàm MIN hoặc hàm INDEX. Ví dụ: với phạm vi được đặt tên là "danh sách", bạn có thể sử dụng MIN như sau:
(ROW(list)-MIN(ROW(list))+1)
Với INDEX, chúng tôi tìm nạp tham chiếu đầu tiên trong phạm vi được đặt tên và sử dụng ROW trên đó:
(=ROW(list)-ROW(INDEX(list,1,1))+1)
Bạn sẽ thường thấy các công thức "hàng tương đối" như thế này bên trong các công thức mảng phức tạp cần số hàng để tính toán kết quả.
Với SEQUENCE
Với hàm SEQUENCE, công thức trả về số hàng tương đối cho một phạm vi rất đơn giản:
=SEQUENCE(ROWS(range))
Hàm ROWS cung cấp số lượng hàng, được trả về hàm SEQUENCE. Sau đó, SEQUENCE xây dựng một mảng số, bắt đầu bằng số 1. Vì vậy, theo ví dụ ban đầu ở trên, công thức dưới đây trả về cùng một kết quả:
=SEQUENCE(ROWS(B5:B11)) // returns (1;2;3;4;5;6;7)
Lưu ý: công thức SEQUENCE là một hàm mảng động mới chỉ có trong Excel 365.
