如何获得具有原始矩阵的每个单元格的行号的矩阵? [英] How to get a matrix with the row numbers for every cell of the original matrix?

问题描述

偶然发现一个奇怪的行为.这个公式:

Stumbled upon a strange behavior. This formula:

=ARRAYFORMULA(ROW($A$1:$E$5))

返回行号从1到5的5元素列.我认为ARRAYFORMULA会遍历指定范围内的每个单元格并在其上执行ROW()-从而产生一个相同大小的新范围(5列5行)，但行号包含在单元格中.

Returns a 5 element column with the row numbers from 1 to 5. I thought that ARRAYFORMULA would iterate through every cell in the range specified and execute the ROW() on it - resulting in a new range of the same size (5 columns, 5 rows), but with the row numbers in cells.

例如=ARRAYFORMULA(ISBLANK($A$1:$E$5))返回5 on 5范围.

For example =ARRAYFORMULA(ISBLANK($A$1:$E$5)) returns a 5 on 5 range.

我找到了一种强制执行所需行为的方法(请参见下面的答案)，但是还有更好的方法吗?我想念什么吗?

I found a way to force the desired behavior (see the answer below), but is there a better way? Am I missing something?

推荐答案

我认为很多人都在使用:

I think a lot of people use:

=ARRAYFORMULA(IF(COLUMN(A1:E5),ROW(A1:E5)))

有很多方法可以做到这一点，但是几乎所有方法都需要使用两个引用.

There are a lot of ways to do it, but almost all will involve using two refs.

这大约和我能得到的一样小.取决于您输入的内容是否适合使用.

This is about as small as I could get it. Depends on what your inputs look like as to whether this would make sense to use.

=ARRAYFORMULA(SEQUENCE(1,5,0,0)+SEQUENCE(5))

这篇关于如何获得具有原始矩阵的每个单元格的行号的矩阵?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！