两个小技巧，防止加行导致的引用偏移。

在引用单元格的时候，经常会遇到在引用的位置增加行，导致引用的位置偏移，没有达到预期的效果。

例如使用 SUM 函数计算 A2 到 A 列的合计。

但是在第一行的下面新增一行的时候，原先引用 A2:A 的位置就会被移动到 A3:A ，这样就会导致新增加的行是无法被 SUM 函数计算。

解决方法一

使用 INDIRECT 函数，以字符串的形式引用单元格。将原先 A2:A 改为 INDIRECT("A2:A")，因为是文本内容，所以无论怎么增加行都不会使引用的位置偏移。

但是这个方式也有一个弊端，在横向移动的时候，引用的列也不会改变。

解决方法二

在使用 INDIRECT 函数的基础上进行改进。

=SUM(INDIRECT(ADDRESS(ROW() + 1, COLUMN())&":"&REGEXREPLACE(ADDRESS(ROW(), COLUMN(), 2), "\$.+", "")))

先来拆分出来理解一下这个函数运行的过程。

首先是 ADDRESS(ROW() + 1, COLUMN())，使用 ADDRESS 函数配合 ROW 和 COLUMN 函数获取单元格自身的位置，不过要在 ROW 的位置加上 1，因为预期的效果是引用 A2:A。那么现在已经用 ADDRESS 函数获得了 $A$2，虽然是绝对引用，但并不会影响结果。

再使用 ADDRESS 函数用同样的方法，获取单元格自身的位置，不过在引用模式的参数设置 2，绝对引用行，这样的目的是为了接下来用正则表达式更方便的筛选。这个时候得到了一个 A$1，但是我们需要的是 A 列。

使用 REGEXREPLACE 函数，用正则表达式进行替换，正则表达式语法 \$.+，获取从 $ 符号往后的所有字符，然后替换成空值。这样就得到了 A 列。

然后将前面的 $A$2 和 A 列用 &（与号）拼接在一起。

ADDRESS(ROW() + 1, COLUMN())&":"&REGEXREPLACE(ADDRESS(ROW(), COLUMN(), 2), "\$.+", "")

这样就得到了一个引用的范围，$A$2:A。

再使用 INDIRECT 函数引用这个范围。

这样即使在下面添加行，引用的范围也不会偏移，同时横向移动的时候，引用的列也会自动改变成当前的列。