Công thức Excel: Tạo mảng số -

Mục lục

Công thức chung

(=ROW(INDIRECT(start&":"&end)))

Tóm lược

Để tạo một mảng các số như (1; 2; 3; 4; 5), bạn có thể sử dụng công thức dựa trên các hàm ROW và INDIRECT. Kỹ thuật này thường được sử dụng nhất trong các công thức mảng cần một mảng số để xử lý. Trong ví dụ được hiển thị, công thức trong D5 là:

(=ROW(INDIRECT(B5&":"&C5)))

trả về một mảng như (1; 2; 3; 4; 5).

Lưu ý: khi được nhập vào một ô, Excel sẽ chỉ hiển thị mục đầu tiên trong mảng. Sử dụng F9 trong thanh công thức để xem kết quả mảng thực tế. Thông thường, bạn sẽ sử dụng công thức này bên trong một công thức mảng lớn hơn, được nhập bằng control + shift + enter.

Giải trình

Lưu ý: Trong Excel 365, hàm SEQUENCE mới là cách tốt hơn và dễ dàng hơn để tạo một mảng số. Phương pháp được giải thích bên dưới sẽ hoạt động trong các phiên bản trước.

Cốt lõi của công thức này là một chuỗi đại diện cho các hàng. Ví dụ: để tạo một mảng có 10 số, bạn có thể mã hóa một chuỗi thành INDIRECT như thế này:

=ROW(INDIRECT("1:10"))

Hàm INDIRECT diễn giải văn bản này có nghĩa là phạm vi 1:10 (10 hàng) và hàm ROW trả về số hàng cho mỗi hàng trong phạm vi đó bên trong một mảng.

Ví dụ được hiển thị sử dụng một phiên bản chung chung hơn của công thức chọn số bắt đầu và số kết thúc tương ứng từ B5 và C5, vì vậy giải pháp trông giống như sau:

=ROW(INDIRECT(B5&":"&C5)) =ROW(INDIRECT(1&":"&5)) =ROW(INDIRECT("1:5")) =ROW(1:5) =(1;2;3;4;5)

Lý do INDIRECT được sử dụng trong công thức là để bảo vệ chống lại các thay đổi trang tính. Không có CHỈNH SỬA, việc chèn hoặc xóa hàng có thể thay đổi tham chiếu phạm vi, ví dụ:

=ROW(1:5)

sẽ thay đổi thành:

=ROW(1:4)

Nếu hàng 1 bị xóa. Vì INDIRECT hoạt động với một tham chiếu được tạo bằng văn bản, nó không bị ảnh hưởng bởi các thay đổi trên trang tính.

Số hàng tương đối trong một phạm vi

Nếu bạn cần một mảng bao gồm các số hàng tương đối của một phạm vi, bạn có thể sử dụng công thức như sau:

=ROW(range)-ROW(range.firstcell)+1

Xem trang này để được giải thích đầy đủ.

Giá trị âm

Hàm ROW sẽ không xử lý các số âm, vì vậy bạn không thể trộn các số âm vào đầu và cuối . Tuy nhiên, bạn có thể áp dụng các phép toán cho mảng được tạo bởi ROW. Ví dụ: công thức sau sẽ tạo mảng này: (-5; -4; -3; -2; -1)