Công thức chung
(=COLUMN(range)-COLUMN(range.firstcell)+1)
Tóm lược
Để có được tập hợp đầy đủ các số cột 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 COLUMN.
Trong ví dụ được hiển thị, công thức mảng trong B4: H4 là:
(=COLUMN(B4:H4)-COLUMN(B4)+1)
Trên trang tính, điều này phải được nhập dưới dạng công thức mảng nhiều ô bằng Control + Shift + Enter
Đây là một công thức mạnh mẽ sẽ tiếp tục tạo ra các số tương đối ngay cả khi các cột được chèn vào phía trước của phạm vi.
Giải trình
Hàm COLUMN đầu tiên tạo ra một mảng gồm 7 số như sau:
(2,3,4,5,6,7,8)
Hàm COLUMN thứ hai tạo một mảng chỉ với một mục như sau:
(2)
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)
Với một phạm vi được đặt tên
Bạn có thể điều chỉnh công thức này để sử dụng với phạm vi đã đặt tên. Ví dụ: trong ví dụ trên, nếu bạn đã tạo một dải ô được đặt tên là "dữ liệu" cho B4: H4, bạn có thể sử dụng công thức này để tạo số cột:
(=COLUMN(data)-COLUMN(INDEX(data,1,1))+1)
Bạn sẽ gặp công thức này trong các công thức mảng khác cần xử lý dữ liệu theo từng cột.
Với SEQUENCE
Với hàm SEQUENCE, công thức trả về các cột hàng tương đối cho một phạm vi rất đơn giản:
=SEQUENCE(COLUMNS(range))
Hàm COLUMNS cung cấp số lượng cột, được trả về cho 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(COLUMNS(B4:H4)) // 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.
