excel - 简单查询复杂答案 - 如何为简单公式选择 RowA6-Row(最后一个非空白)

Question

我有很多列，下面都标有很多值，可以是单词或数字

这是当前的方程 =INDEX(AK6:AK94,MODE(MATCH(AK6:AK94,AK6:AK94,0))) 我把它放在每列的单元格 5 中。

每列中的值数量可能会增加或减少。如果我引用整个列(直到工作表末尾)，空格会干扰准确的输出。

如何将单元格A6引用到最后一个非空白

Answer 1

与 sancho.s 给出的 SUMPRODUCT/MAX 等方法相比，有更有效且非 volatile 的设置可用于确定范围中的最后一个非空白单元格，但前提是空白单元格是空白的。该范围内的单元格都是“真正的”空白，而不是空字符串“”，例如作为这些单元格中公式的结果。

如果可以保证这一点，那么对于包含混合数据类型(一些文本，一些数字)的范围，您可以使用:

=MAX(MATCH(REPT("z",255),A:A),MATCH(9.9E+307,A:A))

这比任何测试指定范围内的每个单元格是否为空白的解决方案(例如 SUMPRODUCT/MAX 设置)都要高效得多。

此外，上述构造可以引用整个 A 列，而不会影响计算速度，从而无需选择有限的范围。 (请注意，在 SUMPRODUCT(或任何其他数组公式)中使用相同的范围，即 A:A 根本不是一个好主意，因为这将迫使 Excel 单独计算超过一百万个单元格，导致速度明显变慢工作簿性能)。

对于形成动态范围，我一直感到惊讶的是，互联网上有如此多的资源继续提倡涉及诸如 OFFSET 和 INDIRECT 等 volatile 函数的设置(我什至看到几个网站使用 ADDRESS 来实现此目的) ，特别是当有一个非常好的非 volatile (实际上，不是完全非 volatile ，但足够接近)可用的 INDEX 设置时，即:

AK6:INDEX(A:A,LastRow)

其中 LastRow 是根据我上面发布的公式定义的名称。

问候