如何获取行内最后非空单元格值、最后三值平均值及进度预测？

1. 获取指定行最后一个非空单元格的值及位置 #

• 获取数值：使用适配空白单元格场景的LOOKUP公式：

  =LOOKUP(9.99E+307, P23:BW23)
  

  注：9.99E+307是Excel/Google Sheets支持的最大数值之一，可匹配所有非空数值单元格。
• 获取单元格地址：结合INDEX与MATCH定位：

  =CELL("address", INDEX(P23:BW23, MATCH(9.99E+307, P23:BW23)))
  

2. 计算最后三个非空单元格的平均值 #

直接用数值做区间无效，需先定位最后非空单元格的位置，再扩展范围计算：

=AVERAGE(OFFSET(INDEX(P23:BW23, MATCH(9.99E+307, P23:BW23)), 0, -2, 1, 3))

• 逻辑：MATCH找到最后非空值的列位置，INDEX定位单元格，OFFSET向左扩展2列形成1行3列区域，最终计算平均值。
• 兼容不足3个非空值的场景（自动取现有值的平均）：

  =AVERAGE(OFFSET(P23:BW23, 0, MAX(1, MATCH(9.99E+307, P23:BW23)-2), 1, MIN(3, MATCH(9.99E+307, P23:BW23))))
  

3. 计算过去3/6/12个月的进度百分比 #

公式逻辑：(最新值 - N个月前的值)/N个月前的值，转换为百分比。以3个月为例：

=(LOOKUP(9.99E+307, P23:BW23)-INDEX(P23:BW23, MATCH(9.99E+307, P23:BW23)-3))/INDEX(P23:BW23, MATCH(9.99E+307, P23:BW23)-3)

• 替换公式中的3为6或12，即可计算对应周期的进度；
• 避免数据不足时的错误：

  =IF(MATCH(9.99E+307, P23:BW23)<3, "", (LOOKUP(9.99E+307, P23:BW23)-INDEX(P23:BW23, MATCH(9.99E+307, P23:BW23)-3))/INDEX(P23:BW23, MATCH(9.99E+307, P23:BW23)-3))
  

4. 使用TREND函数生成3/6/12个月后的预测值 #

TREND需传入已有的评分值和对应月份序号，假设P列为第1个月、Q列为第2个月：

• 3个月后预测值：

  =TREND(FILTER(P23:BW23, P23:BW23<>""), SEQUENCE(COUNT(P23:BW23)), COUNT(P23:BW23)+3)
  

• 替换+3为+6或+12，可生成对应周期的预测值；
• 逻辑：FILTER提取非空评分，SEQUENCE生成月份序号，最后指定未来月份（现有数据量+N）。

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