google-sheets - How do I get the row number of the last cell in a column,which is greater than zero?

Question

I have tried with

=INDEX($K$2:K15,MATCH(TRUE,INDEX($K$2:K15>0,0),))

but this gives me the value of last cell in K row which is greater than zero. How do I get the number of the row where last non-zero value lives?

标签： google-sheets

Answer 1

拖累

=INDEX(FILTER(ROW(K2:K15),K2:K15>0),1)

FILTER(ROW(K2:K15),K2:K15>0)是获取列值> 0的所有行。索引是获取第一个。

要获取最后一行编号：

=INDEX(FILTER(ROW(K2:K15),K2:K15>0),COUNTIF(K2:K15,">0"))

row2 的这个公式：=INDEX(FILTER(ROW($K$2:K2),$K$2:K2>0),COUNTIF($K$2:K2,">0"))。向下拖动，看看它是否有效。如果没有找到行，则给出 #N/A

---

单一配方解决方案

=ARRAYFORMULA(VLOOKUP(ROW(K2:K16), QUERY(QUERY( { row(K2:K16) , IF(K2:K16>0,ROW(K2:K16),0) } , "select Col2, min(Col1) group by Col2"),"select Col2, Col1"), 2))

粘贴到第 2 行的单元格中。