一列中最后一个非空单元格

Question

有没有人知道在Microsoft Excel中找到一列中最后一个不为空的单元格值的公式？

- Michael S

---

我会考虑使用VBA来完成这个任务。 - David Heffernan

如果您不想担心排序顺序，不想输入数组公式，并且在您尝试查找最后一个非空单元格的列中没有巨大的负数数字值或以“ZZZZZZZZZZZZZZZZZZZZZZZZ”开头的文本值，则更有效的公式是Max()、Iferror()和Match()的组合，如下所示：=MAX(IFERROR(MATCH(-9.99999999999999E+27,Sheet2!A:A,-1),0),IFERROR(MATCH("ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ",Sheet2!A:A,1),0))，它返回行号，然后可以间接引用以返回所需的值。 - Josh Fierro

Answer 1

使用以下简单公式更快速

=LOOKUP(2,1/(A:A<>""),A:A)

Excel 2003：

=LOOKUP(2,1/(A1:A65535<>""),A1:A65535)

它给你以下优势：

• 它不是数组公式
• 它不是易失性公式

说明：

• (A:A<>"") 返回数组{TRUE,TRUE,..,FALSE,..}
• 1/(A:A<>"") 修改这个数组为 {1,1,..,#DIV/0!,..}
• 由于LOOKUP函数期望一个升序的排序后的数组，并且考虑到如果LOOKUP函数找不到精确匹配，它会选择lookup_range中最大的小于或等于该值（在我们的情况下是{1,1,..,#DIV/0!,..}），该公式查找数组中最后的1并返回对应的result_range（第三个参数-A:A）中的值。

还有一点需要注意 - 上面的公式不考虑包含错误的单元格（只有在最后一个非空单元格有错误时才会看到）。 如果您想考虑它们，请使用：

=LOOKUP(2,1/(NOT(ISBLANK(A:A))),A:A)

下面的图片展示了它们之间的区别：

- Dmitry Pavliv

---

感谢您的解释和美丽的图片。仍有一些问题需要解决：1. LOOKUP 的文档说明最后一个参数 result_vector 是可选的。然而，如果我省略它，我会得到一个非常奇怪的结果，我不理解。 - Honza Zidek

1. 可选项并不意味着不必要，如果省略它，则查找将从 1/(A:A<>"") 返回值（除了空列 A 之外始终为 1）。2) A:A<>"" 返回布尔数组，但您需要数字数组 - =LOOKUP(2,1*(A:A<>""),A:A)，但它也不能按您想要的方式工作 - 查找始终依赖于数组已排序的事实。由于最后一个元素始终小于查找值，因此查看数组的其余部分毫无意义，查找只是返回它（或来自 result_vector 的相应值）。因此，=LOOKUP(2,1*(A1:A10<>""),A1:A10) 总是返回 A10 中的值（如果它不是错误）。

- Dmitry Pavliv

这只是我的猜测，很可能是正确的，但是除了开发人员，没有人知道查找中使用了哪种搜索算法。也许它只是一个反向循环或二分查找算法，或者更复杂的算法...我们不知道...所以，我认为没有必要在响应中添加未经证实的信息，只需猜测即可。 - Dmitry Pavliv

但是你的解释很有用 - 我不把它看作是实现的描述，而是函数行为的逻辑。 - Honza Zidek

嗨，感谢您发布这个。只是好奇，我在 https://exceljet.net/formula/get-value-of-last-non-empty-cell 上找到了完全相同的公式和几乎相同的措辞。那也是您还是其他人？没有明显的联系。 - Solomon Rutzky

Answer 2

这适用于文本和数字，并且不在意是否存在空单元格，即它将返回最后一个非空单元格。

它需要使用数组公式，也就是说，您在键入或粘贴后按Ctrl-Shift-Enter。下面是针对A列的公式：

=INDEX(A:A,MAX((A:A<>"")*(ROW(A:A))))

- Doug Glancy

---

在Excel 2007中对我无效。我粘贴了确切的公式。我有一列（A），其中值从=ROW()一直到127左右，但公式返回“1”。 - DontFretBrett

@DontFretBrett，请确保按照答案中指定的方式将其输入为数组公式，使用Ctrl-Shift-Enter。 - Doug Glancy

@DougGlancy 是我的错误。谢谢！ - DontFretBrett

重要的区别在于，这个函数返回一个单元格引用（因此可以传递给例如CELL()或OFFSET()），而更受欢迎的那个返回一个值，但不告诉你它在哪里。 - GSerg

=INDEX($C:$C,SUMPRODUCT(MAX(($C:$C<>"")*(ROW($C:$C)))))) ... 因此使用SUMPRODUCT不需要输入为数组公式。 - Rosetta

Answer 3

这里有另一种选项：=偏移($A$1;计数(A:A)-1;0)

- iDevlop

---

如果单元格中存在非数值的值（请使用COUNTA而不是COUNT），那么这种方法不起作用，更不用说空白单元格了。 - Jean-François Corbett

应该将这放在顶部。它很简单，不依赖于不常见函数的晦涩细节。 - Tmdean

结果中含有空格时，它不应该位于顶部，因为那是错误的。 - Myrddin Emrys

Answer 4

我知道这个问题很老，但我不满意提供的答案。

• LOOKUP, VLOOKUP和HLOOKUP存在性能问题，基本上不应该使用。

• 数组函数有很多额外的开销，也可能存在性能问题，因此只应作为最后的选择。

• COUNT和COUNTA在数据不连续非空时会遇到问题，即在所涉及的范围内存在空格然后再次出现数据。

• INDIRECT是不稳定的，所以只应作为最后的选择。

• OFFSET是不稳定的，所以只应作为最后的选择。

• 任何对最后一行或列的引用（例如Excel 2003中的第65536行）都不够健壮，并会产生额外的开销。

以下是我使用的方法:

• 当数据类型混合时: =max(MATCH(1E+306,[RANGE],1),MATCH("*",[RANGE],-1))

• 当已知数据仅包含数字时: =MATCH(1E+306,[RANGE],1)

• 当已知数据仅包含文本时: =MATCH("*",[RANGE],-1)

MATCH的开销最小且不是不稳定的，因此如果您正在处理大量数据，则应使用它。

- W5ALIVE

---

这是一个很好的答案（+1），但仍然存在一些漏洞，这种方法可能会错过。例如，如果有逻辑值（TRUE/FALSE）的单元格，它们将无法被检测到。尽管这个答案并不是百分之百可靠，但我仍然认为这个答案对性能的影响最小。 - ImaginaryHuman072889

Match 返回的是索引而非单元格的值。您可以使用 Index 函数来获取该值。另外，您应该对 [范围] 应该是什么进行一些说明。我使用了“N:N”选择整个列。以下是一个对我有用的示例：=INDEX(N:N,MATCH(1E+306,N:N,1)) - swimfar

是的，我对此进行了时间测试，看起来它比其他方案快得多。 - Tom Sharpe

Answer 5

受Doug Glancy答案的启发，我想出了一种无需使用数组公式即可完成相同操作的方法。不要问我为什么，但我很想尽可能避免使用数组公式（没有特定原因，这只是我的风格）。

下面是方法：

=SUMPRODUCT(MAX(($A:$A<>"")*(ROW(A:A))))

为了使用列A作为参考列查找最后一个非空行

=SUMPRODUCT(MAX(($1:$1<>"")*(COLUMN(1:1))))

要使用第一行作为参考行查找最后一个非空列，

这可以与索引函数结合使用，有效地定义动态命名范围，但这与此处所解决的问题无关。

我已经在Excel 2010中使用上述方法进行了测试，包括“本机”和“兼容模式”（适用于旧版本的Excel），它们都有效。同样，您不需要执行Ctrl+Shift+Enter。通过利用Excel中sumproduct的工作方式，我们可以满足执行数组操作的需求，但没有使用数组公式。我希望有人能像我一样欣赏这些提出的sumproduct解决方案的优美、简洁和优雅之处。尽管我不保证上述解决方案的内存效率，但它们很简单，外观漂亮，有助于实现预期目标，并且足够灵活以将其用于其他目的：）

希望对您有所帮助！

祝你好运！

- Santiago

---

关于为什么使用非数组公式，请参见下面的这个答案。 - sancho.s ReinstateMonicaCellio

Answer 6

这个方法适用于Excel 2003（以及稍作修改后的后续版本）。按Ctrl+Shift+Enter（而不是仅仅按Enter）将其输入为数组公式。

=IF(ISBLANK(A65536),INDEX(A1:A65535,MAX((A1:A65535<>"")*(ROW(A1:A65535)))),A65536)

请注意，Excel 2003无法将数组公式应用于整个列。这样做会产生#NUM！;可能会出现不可预测的结果!（编辑：来自Microsoft的信息相互冲突：相同情况在Excel 2007中可能或可能不是真的；问题可能已在2010年被解决。）

这就是为什么我将数组公式应用于范围A1:A65535并对最后一个单元格进行特殊处理，即在Excel 2003中为A65536。不能只说A：A甚至A1:A65536，因为后者会自动恢复为A：A。

如果你绝对确定A65536为空，则可以跳过IF部分：

=INDEX(A1:A65535,MAX((A1:A65535<>"")*(ROW(A1:A65535))))

请注意，如果您使用的是Excel 2007或2010，则最后一行的行号为1048576而不是65536，请根据需要进行调整。

如果您的数据中间没有空单元格，则我会使用更简单的公式=INDEX(A:A,COUNTA(A:A))。

- Jean-François Corbett

---

对于行而不是列，这个公式适用于我 =INDEX(19:19,COUNTA(19:19))。 - Serj Sagan

Answer 7

一种不使用数组公式的备选方案，可能比以前的回答中提供的不使用数组公式的解决方案更加健壮，是：

=INDEX(A:A,INDEX(MAX(($A:$A<>"")*(ROW(A:A))),0))

请参考这个答案。感谢 Brad 和 barry houdini，他们帮助解决了这个问题。

以下是不使用数组公式的可能原因：

1. 官方Microsoft页面（查找“使用数组公式的缺点”）。
   数组公式看起来很神奇，但也有一些缺点：
   • 你偶尔可能会忘记按CTRL+SHIFT+ENTER。每当输入或编辑数组公式时，请记得按下这个组合键。
   • 其他用户可能无法理解您的公式。数组公式相对没有文档记录，所以如果其他人需要修改您的工作簿，您应该避免使用数组公式或确保那些用户知道如何更改它们。
   • 根据计算机的处理速度和内存，大型数组公式可能会减慢计算速度。
2. 数组公式异端邪说。

- sancho.s ReinstateMonicaCellio

---

我无法让它与D列一起工作。我用“D”替换了所有“A”的出现。我做错了什么？ - Marc.2377

Answer 8

如果您要在A列中搜索，请使用以下方法：

=INDIRECT("A" & SUMPRODUCT(MAX((A:A<>"")*(ROW(A:A)))))

如果你的范围是A1:A10，你可以使用以下方法：

=INDIRECT("A" & SUMPRODUCT(MAX(($A$1:$A10<>"")*(ROW($A$1:$A10)))))

在这个公式中：

SUMPRODUCT(MAX(($A$1:$A10<>"")*(ROW($A$1:$A10))))

返回最后一个非空行号，indirect()返回单元格的值。

- Sharif Lotfi

Answer 9

=INDEX(A:A, COUNTA(A:A), 1) 取自这里。该公式用于在Excel列中返回最后一个项目。

- HattrickNZ

Answer 10

=MATCH("*";A1:A10;-1) 用于文本数据

=MATCH(0;A1:A10;-1) 用于数值数据

- liharvi

---

请在回答中提供代码片段时使用代码块。 - β.εηοιτ.βε

我认为这些公式会给我们第一个非空单元格，而不是最后一个非空单元格。 - Baodad