如何计算列内除最后一个非空值外所有非空值的平均值与总和

以下方案同时兼容Excel、LibreOffice Calc、Google Sheets，无需额外插件，新增数据后自动调整计算范围，默认数据存放于A列，可根据实际需求修改列标。

核心逻辑 #

先定位目标列最后一个非空值的行号，再截取从起始行到倒数第二个非空值的范围进行计算，自动跳过列中间的空值。

1. 动态求和公式 #

=SUM(A1:INDEX(A:A,MAX(IF(A:A<>"",ROW(A:A),0))-1))

• Excel 2019及更早版本输入完公式后需按Ctrl+Shift+Enter确认数组公式，Excel 365/2021、LibreOffice Calc、Google Sheets直接回车即可生效
• 需修改数据列时，把公式中所有的A:A替换为目标列即可，比如B列就改为B:B、ROW(B:B)
• 如果数据不是从第一行开始，比如从A2开始，把公式开头的A1修改为A2即可

2. 动态平均值公式 #

=AVERAGE(A1:INDEX(A:A,MAX(IF(A:A<>"",ROW(A:A),0))-1))

• 公式生效规则、列标修改方式和求和公式完全一致

边界情况处理 #

如果目标列仅存在1个非空值时，基础公式会返回0（求和）/计算错误（平均值），可使用带判断的优化公式：

• 边界优化版求和公式

=IF(MAX(IF(A:A<>"",ROW(A:A),0))<2,0,SUM(A1:INDEX(A:A,MAX(IF(A:A<>"",ROW(A:A),0))-1)))

• 边界优化版平均值公式

=IF(MAX(IF(A:A<>"",ROW(A:A),0))<2,"数据不足",AVERAGE(A1:INDEX(A:A,MAX(IF(A:A<>"",ROW(A:A),0))-1)))

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