天道酬勤,学无止境

Excel formula reference cell above last cell in range

问题

我正在尝试设置一个公式来自动计算范围内最近添加的单元格(包括范围底部的#N/A 值)与其上方的单元格之间的百分比变化。 我一直在使用这个公式来获得底部不是#N/A 单元格的值:

LOOKUP(2, 1/NOT(ISNA(G8:G19)), G8:G19)

哪个工作正常。 我对如何到达其上方的单元格的第一个想法是使用 OFFSET,如下所示:

OFFSET(LOOKUP(2, 1/NOT(ISNA(G8:G19)), G8:G19), -1, 0)

但这给了我一个错误,我认为是因为查找函数返回的是单元格中的值,而不是单元格引用。 我应该如何格式化函数以返回范围内最后一个非 N/A 单元格上方的单元格的值?

回答1

尝试使用此替代方法在 G 列中查找最后一个非错误的数值。

=index(G:G, match(1e99, G:G))/index(G:G, match(1e99, G:G)-1)

使用 MATCH 查找列中的最后一个数字会将行号返回给 INDEX。 从行号中减去 1 是一件简单的事情。

回答2

一种方法是使用此数组公式:

=INDEX($G$8:$G$19,MATCH(2,IF(NOT(ISNA($G$8:$G$19)),1))-1)

作为一个数组公式,必须在退出编辑模式时使用 Ctrl-Shift-Enter 确认,而不是输入。 如果操作正确,Excel 会将{}放在公式周围。

受限制的 HTML

  • 允许的HTML标签:<a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • 自动断行和分段。
  • 网页和电子邮件地址自动转换为链接。

相关推荐