excel - 如何用单元格之间的空白扩展动态范围？

Question

我想定义一个动态范围，只要插入新的行或列，它就会扩展我的范围的行和列。我当前的公式不想扩展到单元格 $T$13 .我的标题从行 $M$7 开始.如何调整我的公式？

正在使用的公式
=OFFSET(Sheet1!$M$8,0,0,COUNTA(Sheet1!$M:$M),COUNTA(Sheet1!$1:$1))
我需要将范围扩展到单元格 $T$13

Answer 1

现在，您的公式计算列 M 中的文本值的数量。

这不是一种稳健的方法，因为 M 列仅包含五个文本值，但 S 和 T 列具有更多值。

如果您不知道哪一列的条目数最多，您可以在每一列中引入一个辅助单元格，用于计算下面的条目数。我建议你插入一个新的第 2 行。例如，在 M 列中，在 M2 中输入一个公式

=counta($M$3:M$99999)

将该公式复制到 T 列。

接下来，您可以评估哪一列的数字最大

=max(M2:T2)

这可以像这样插入到您的原始公式中:

=OFFSET(Sheet1!$M$8,0,0,max(M2:T2),COUNTA(Sheet1!$1:$1))

所以现在，该公式不再只查看 M 列中有多少行，而是使用 M 到 S 列中的最大行数。

如果它扰乱了您的工作表设计，您现在可以隐藏第 2 行。

编辑:仅使用 CountA 计算文本值将忽略空白单元格并返回不正确的结果。您确实需要一个公式来查找每列中最后一个填充单元格的行号。

这确实应该是一个新问题，但是这里有

如果该列有数字值，您可以使用

=MATCH(99^99,B5:B999,1)

如果该列有文本值，您可以使用

=MATCH("zzz",C5:C999,1)

相应地调整您的范围。