假设要显示表格A列最后一行非空单元格所在行号,公式如下:
=INDEX(MAX((A:A <>"")*ROW(A:A)),1)
其中,
(A:A <>"")
返回一个由 TRUE 和 FALSE 组成的数组,表示 A 列中哪些单元格是非空的。true值为1,false值为0。ROW(A:A)
返回一个由行号组成的数组;(A:A <>"")*ROW(A:A)
返回两个数组的乘积,数据类型依然为数组。其中,非空行对应的数值为实际行号;空行对应的数值为 0(false值为0)。MAX((A:A <>"")* ROW(A:A))
返回数组中的最大值,本例中即为最大非空单元格所在行号。依然是数组类型。INDEX(MAX((A:A <>"")* ROW(A:A)),1)
取出数组中的这个唯一值。
示例如下:
假若有这样一个表
第6行为空(行数含标题行)。
公式=A:A<>""
、=ROW(A:A)
会返回2个数组。第一个数组存储是否空行判断的布尔值,第二个数组是行号。
我们可用index可以查看数组的值。
公式:=INDEX(array,row_num,[column_num])
例如,查看A:A<>""
返回数组的值:
公式 | 数值 |
---|---|
=INDEX((A:A<>“”),1) | TRUE |
=INDEX((A:A<>“”),2) | TRUE |
=INDEX((A:A<>“”),3) | TRUE |
=INDEX((A:A<>“”),4) | TRUE |
=INDEX((A:A<>“”),5) | TRUE |
=INDEX((A:A<>“”),6) | FALSE |
=INDEX((A:A<>“”),7) | TRUE |
同理,查看其他数组的值如下:
=A:A<>"" | =ROW(A:A) | =A:A<>""*ROW(A:A) |
---|---|---|
TRUE | 1 | 1 |
TRUE | 2 | 2 |
TRUE | 3 | 3 |
TRUE | 4 | 4 |
TRUE | 5 | 5 |
FALSE | 6 | 0 |
TRUE | 7 | 7 |
第6行为空行,
因此,第一列判断是否空行显示false;
第二列,显示的是行号,行号为6;
第三列,是第一列和第二列的乘积。因为false值为0,和行号的乘积也为0.
第7行后均为空行,因此第7行之后也全为0.
即最大行号为7.
MAX((A:A <>"")* ROW(A:A))
将取其最大值,即7。
INDEX(MAX((A:A <>"")* ROW(A:A)),1)
返回行号,为7.
excel中显示如下:
–
附:
最后这一步,网上很多文章用的是SUMPRODUCT
,也可以,但意思有别。
官方的SUMPRODUCT函数示例:
本示例使用 SUMPRODUCT 返回给定项和大小的总销售额:
SUMPRODUCT 匹配项 Y/大小 M 的所有实例并求和,因此对于此示例,21 加 41 等于 62。
对于max函数之后的数组中只有一个值,因此sumproduct求和数组中的值,最终的结果是一样的。
只是初看sumproduct这个函数,可能会疑惑此处sum的是什么东西呢。