Excel 2016无VBA获取第N个0上方单元格值的方法求助

针对你需要提取所有0值上方单元格的需求，在Excel 2016 Pro无VBA的前提下，可以用数组公式实现批量提取：

具体公式（以在L列输出结果为例） #

在L2单元格输入以下公式，输入完成后按Ctrl+Shift+Enter触发数组计算，再下拉填充公式直到出现空值：

=IFERROR(INDEX($K$2:$K$31,SMALL(IF($K$2:$K$31=0,ROW($K$2:$K$31)-ROW($K$2)+1)-1,ROWS($L$2:L2))),"")

公式说明 #

• IF($K$2:$K$31=0,ROW($K$2:$K$31)-ROW($K$2)+1)：定位K列所有0值的相对行号
• -1：获取每个0值上方单元格的相对行号
• SMALL(...,ROWS($L$2:L2))：依次提取第1、第2、第N个符合条件的行号，下拉时自动递增序号
• INDEX($K$2:$K$31,...)：根据行号提取对应单元格的运行时长值
• IFERROR(...)：当没有更多符合条件的结果时，显示空值避免错误

额外优化（排除首个单元格为0的情况） #

如果K列第一个单元格（K2）就是0，会因为没有上方单元格返回错误，可调整公式排除这种情况：

=IFERROR(INDEX($K$2:$K$31,SMALL(IF(AND($K$2:$K$31=0,ROW($K$2:$K$31)>ROW($K$2)),ROW($K$2:$K$31)-ROW($K$2)+1)-1,ROWS($L$2:L2))),"")

内容的提问来源于stack exchange，提问作者Sjoerd Eeman