excel中countif函数新增行影响上一行公式

Question

#excel countif函数判断某值出现次数，其中范围为非固定，这将导致新增行后，上一行公式受到影响

如图所示，39行为新增行，38行红字为受影响的公式=COUNTIF($A$2:A39,A38)，正确公式应该为=COUNTIF($A$2:A38,A38)

Answer 1

引自免费微信小程序：皆我百晓生

在Excel中，如果你想要创建一个不会因为新行插入而改变引用范围的计数函数，可以使用相对引用配合OFFSET和ROW函数。这样，即使你在数据表中添加新的行，公式也会自动调整范围以排除新行。

首先，删除当前B38单元格中的公式=COUNTIF($A$2:A39,A38)。

然后，在B38单元格中输入以下公式：

=COUNTIF(OFFSET($A$2, ROW(), 0), A38)

这个公式的解释是：

• OFFSET($A$2, ROW(), 0)：这是一个动态范围，$A$2 是起始位置，ROW() 函数返回当前行号（这里是38），所以每次向下移动一行时，ROW()会增加，但 $A$2 保持不变，因此范围会随着行号变化而自动调整。

这个公式会计算从 $A$2 开始到当前行号所在行（不包括当前行）的所有单元格中与 A38 相同的值的数量。

请注意，这个方法适用于你想从固定的起始行开始计数的情况。如果你的数据表顶部可能会有变动，你需要稍微调整这个公式以适应不同的情况。不过，对于你提供的例子来说，这个公式应该能满足需求。