Google Sheets含空单元格时获取最后列及近3列总和的方法

需求说明 #

• 数据区域为第1-7行，每周/周期会在右侧新增一列（新增列可能存在大量空单元格）
• 原有INDEX+COUNTA方案因空单元格无法准确定位最后一列，失效
• 需实现两个目标：
  • 动态提取每一行对应最后一列的单元格数据
  • 动态计算每一行最后3列数据的总和（空单元格按0计算）
• 第11-16行为预期效果示例

解决方案（分Excel版本） #

1. 提取最后一列数据

不管新增列是否有空单元格，只要定位到最右侧的新增列，可使用以下公式（以提取第1行最后一列数据为例，下拉可适配第2-7行）：

=INDEX($1:$1,,MATCH(REPT("z",255),$1:$1))

• 原理：REPT("z",255)生成Excel中最大的文本值，MATCH会找到第1行最后一个包含文本的列（即新增列的表头），再用INDEX提取对应行的该列数据
• 如果第1行无表头（表头在其他行），将公式中的$1:$1替换为表头所在行即可

若使用Excel 365/2021版本，可更简洁地用XLOOKUP：

=XLOOKUP(TRUE,COLUMN($1:$1)=MAX(COLUMN($1:$1)*($1:$1<>"")),$1:$1)

2. 计算最后3列数据总和

同样兼容空单元格，按以下公式计算（以第1行为例，下拉适配第2-7行）：

=SUM(OFFSET($A1,0,MAX(1,MATCH(REPT("z",255),$1:$1)-3),1,MIN(3,MATCH(REPT("z",255),$1:$1))))

• 原理：先用MATCH定位最后一列，再通过OFFSET偏移出最后1-3列的区域，SUM自动将空单元格视为0求和
• MAX(1,...)避免当总列数不足3时出现负偏移，MIN(3,...)确保列数不足3时只求和现有列

Excel 365/2021版本可简化为：

=SUM(TAKE($1:$1,-3))

• TAKE($1:$1,-3)直接提取该行最后3列数据，SUM自动求和

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