如何在Excel的列中搜索以前的非空单元格? [英] How do I search for previous non-empty cell in column in Excel?

问题描述

我非常感谢帮助找到一个Excel公式，该公式将搜索列中的上一个(不是 last )非空单元格，然后添加1.空单元未始终一致地分开.

I would greatly appreciate help in finding an Excel formula that would search for the previous (not last) non-empty cell in the column, and then add 1. The empty cells are not consistently separated apart.

ColA
1 
2

3

4
5
6


7
8

我更喜欢公式，但是我对VBA持开放态度.任何建议将不胜感激.谢谢！

I prefer a formula, but I'm open to VBA. Any advice would be greatly appreciated. Thanks!

推荐答案

您可能希望考虑此公式，该公式输入到单元格A3中并从那里向下复制.

You may like to consider this formula, entered in Cell A3 and copied down from there.

=LOOKUP(2,1/(ISNUMBER(A$1:A2)),A$1:A2)+1

此公式的优点也是它的缺点:它允许数字和文本混合在上面，将找到最后一个数字并将其加1.但是，如果没有数字，将抛出一个错误，该错误很容易用IFERROR()捕获，但这会使公式变得笨拙.因此，我建议在第3行中输入它，然后在A2中输入一个起始号码.

The advantage of this formula is also its disadvantage: It allows a mixture of numbers and text above it, will find the last number and add 1 to it. However, if there is no number it will throw an error which is easy to trap with IFERROR() but that makes the formula too unwieldy. So I recommend to enter it in row 3 and enter a starting number in A2.

@BigBen对MAX()函数的建议是我以前在遇到类似要求时使用的，但是LOOKUP()在某些情况下可能会表现更好.

@BigBen's recommendation of the MAX() function is what I have previously used when faced with similar requirements but LOOKUP() may be able to perform better under some circumstances.

这篇关于如何在Excel的列中搜索以前的非空单元格?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！