Excel中如何编写引用相对于公式位置指定数量单元格的公式？

要实现引用公式位置左侧紧邻的7个单元格并求和，同时保证插入列时引用范围既不扩展也不跳过新列，可以用以下两种方法：

方法一：使用INDEX函数定位区域 #

=SUM(INDEX(1:1048576,ROW(),COLUMN()-7):INDEX(1:1048576,ROW(),COLUMN()-1))

原理说明 #

• INDEX(1:1048576,ROW(),COLUMN()-7)：定位到当前单元格所在行、向左数第7列的单元格（即左侧第7个单元格）
• INDEX(1:1048576,ROW(),COLUMN()-1)：定位到当前单元格左侧紧邻的第一个单元格
• 两个INDEX之间的冒号表示引用这两个单元格之间的连续区域，刚好包含7个单元格
• SUM函数对该区域求和

插入列后的表现 #

• 在公式左侧插入列：公式所在单元格的列号自动增加，COLUMN()-7和COLUMN()-1同步调整，引用区域始终是当前位置左侧的7个单元格，不会跳过新列
• 在引用范围内插入列：新列会被自动包含进引用区域，同时保持7个单元格的数量，不会扩展引用范围

方法二：使用OFFSET函数动态偏移 #

=SUM(OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())), 0, -7, 1, 7))

原理说明 #

• INDIRECT(ADDRESS(ROW(), COLUMN()))：返回当前单元格的地址，作为OFFSET的偏移基点
• 0：行偏移为0，保持在同一行
• -7：列偏移为-7，向左移动7列
• 1：引用1行数据
• 7：引用7列数据，刚好覆盖左侧紧邻的7个单元格

额外优化：处理单元格不足的情况 #

如果公式所在单元格左侧不足7个单元格，会返回#REF!错误，可以用IFERROR添加容错处理：

=IFERROR(SUM(INDEX(1:1048576,ROW(),COLUMN()-7):INDEX(1:1048576,ROW(),COLUMN()-1)),"左侧单元格数量不足")

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