Excel公式技巧63:查找最后一行

excelperfect

在Excel VBA中,可以使用End属性来获取最后一行,例如语句:

Range('A' &Rows.Count).End(xlUp).Row

可以获得列A中最后一行,即从底向上最后一个含有内容的单元格所在的行。

如果想要使用公式来获取某列中最后一行呢?如下图1所示。

图1

可以看到,我们使用了LOOKUP函数,公式为:

=LOOKUP(2,1/(B:B<>''),B:B)

公式中:

B:B<>''

将生成数组:

{FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;…;FALSE}

使用1来除以这个数组,得到数组:

{#DIV/0!;1;1;1;1;1;1;#DIV/0!;#DIV/0!;…; #DIV/0!}

LOOKUP函数查找该数组中接近2的最后一个值的位置并返回相应位置的列B中的值,即单元格B7中的值。

当要查找的列中包含错误值,特别是最后一个非空单元格中包含错误值时,则需将公式调整为:

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

如果列中单元格由数值与文本混合,可以使用下面的公式获取最后一个数值:

=LOOKUP(2,1/(ISNUMBER(B:B)),B:B)

要获取最后一个值所在的行的行号,可使用公式:

=LOOKUP(2,1/(B:B<>””),ROW(B:B))

小结

通常,在查找值时我们会想到VLOOKUP函数,然而LOOKUP函数在某些情形下却能够巧妙地得到结果。并且,LOOKUP函数的公式接受数组而并不需要按Ctrl+Shift+Enter键。

欢迎在下面留言,完善本文内容,让更多的人学到更完美的知识。

欢迎到知识星球:完美Excel社群,进行技术交流和提问,获取更多电子资料。

完美Excel社群2020.11.6动态

#VBA# Excel编程周末速成班第8课:处理日期和时间

主要内容:Date数据类型如何存储日期和时间信息;从文字日期创建日期值;从系统时钟读取日期和时间;获取有关日期的详细信息;格式化日期和时间以供显示

(0)

相关推荐

  • 青出于蓝的Excel函数:XLOOKUP

    VLOOKUP函数是所有使用Excel的朋友对非常熟悉的一个函数.尽管它有这样那样的缺陷,但是我们还是离不开它.几乎,在Excel的各种应用场景中,我们都会发现它的身影.为了更好地使用这个函数解决各种 ...

  • Excel公式技巧82:查找指定值所在的单元格

    excelperfect 通常,我们会根据指定的位置查找值,例如使用VLOOKUP函数查找指定行列单元格中的值.然而,如果我们知道了某个值,需要查找这个值所在的单元格,这如何使用公式呢? 例如,下图1 ...

  • Excel公式技巧83:使用VLOOKUP进行二分查找

    excelperfect VLOOKUP函数是我们非常熟悉也很常用的一个函数.下面是其语法: VLOOKUP(lookup_value,table_array, col_index_num,[rang ...

  • Excel公式技巧93:查找某行中第一个非零值所在的列标题

    excelperfect 有时候,一行数据中前面的数据值都是0,从某列开始就是大于0的数值,我们需要知道首先出现大于0的数值所在的单元格.例如下图1所示,每行数据中非零值出现的位置不同,我们想知道非零 ...

  • Excel公式技巧94:在不同的工作表中查找数据

    excelperfect 很多时候,我们都需要从工作簿中的各工作表中提取数据信息.如果你在给工作表命名时遵循一定的规则,那么可以将VLOOKUP函数与INDIRECT函数结合使用,以从不同的工作表中提 ...

  • Excel公式技巧96:区分大小写查找

    有时候,我们需要执行区分大小写的查找.如下图1所示,由字母a.t.l.a和s的不同大小写组成的字符串,现在要查找字符串"AtLaS"对应的数量. 图1 可以使用下面的数组公式: = ...

  • Excel公式技巧97:多条件查找

    excelperfect 有时候,我们需要根据多个条件在数据表中查找值,此时,就需要使用一些公式技巧了.本文的示例使用INDEX函数/MATCH函数组合的数组公式来实现多条件查找. 示例1:满足两个条 ...

  • Excel公式技巧101:使用COUNTIFS来验证VLOOKUP查找结果

    excelperfect 大家知道,使用VLOOKUP函数进行查找时,如果找到多个匹配的值,将只返回第一个找到的值.如下图1所示,工作表Data中的数据. 图1 在工作表Vlookup中,列C中是要查 ...

  • Excel公式技巧104:查找任意月的最后一天

    excelperfect 下面是计算任何月份的最后一天的一个方便的技巧. 假设y和m分别是想要查找的最后一天的日期中的年和月,那么: =DATE(y,m+1,0) 使用0作为日参数值,返回前一个月的最 ...

  • Excel公式技巧80:多条件查找值

    excelperfect 我们经常会碰到要查找满足多个条件的值,如下图1所示,想要查找以"凉"开头且价格高于20的商品. 图1 根据前两篇文章中学到的技巧,使用INDEX/MATC ...