打开APP
userphoto
未登录

开通VIP,畅享免费电子书等14项超值服

开通VIP
6种场景,10个公式,只为查找Excel最后一个非空单元格
userphoto

2023.03.31 北京

关注

前一篇推文中,我教大家如何自动更新打印区域的时候,用到了一个公式,如何查找行和列的最后一个非空单元格。

这个知识点引起了很多同学的热烈讨论,如何查找最后一个非空元格?查找文本和数值的公式是否一样?找到后是返回单元格内容还是返回行号列标?

对于这些问题,今天我就来好好捋一捋如何查找最后一个非空单元格。

案例:

下图 1 是某公司销售的获客数总表,比较有代表性的是 A 列是文本,B 列为数值。

我们就以此表来详述查找文本和数值的最后一个单元格,分别返回值和行号,有哪些适用的公式。

效果如下图 2 所示。

解决方案:

我把查找需求分为以下几种大类,分别来看有哪些公式:

  • 查找文本:

    • 返回行号或列号

    • 返回单元格值

  • 查找数值:

    • 返回行号或列号

    • 返回单元格值

以本例来看,D 列是查找文本,E 列查找数值,返回的结果类型写在 F 列。

第一类:查找文本或数值,返回行号

1. 在 D2 单元格中输入以下公式 --> 回车(O365 直接回车即可,O365 以下版本需要按 Ctrl+Shift+Enter 生成数组公式):

=MATCH(1,0/(A:A<>''))

公式释义:

  • A:A<>'':会生成一组 true 和 false 组成的数组,最后一个非空单元格返回最后一个 true 值;

  • 0/...:用 0 除以上述数组,得到由 0 或错误值组成的数组;

  • MATCH(1,...):在上述数组中查找 1,找不到,就会一直往下找,直至最后一个接近的值,并返回其在区域内的序列号

2. 同理,在 E2 单元格中输入以下公式 --> O365 直接按回车,低版本按 Ctrl+Shift+Enter:

=MATCH(1,0/(B:B<>''))

公式释义:

  • 跟上述公式原理一样,查找并返回区域内最后一个非空单元格的行号

第二类:查找文本或数值,返回值

1. 在 D3 单元格中输入以下公式 --> 回车:

=LOOKUP(1,0/(A:A<>''),A:A)

公式释义:

  • lookup 函数用于模糊查找,1,0 的用法原理跟前面一个公式一样,唯一不同的是 lookup 返回的是单元格的值

2. 在 E3 单元格中输入以下公式 --> 回车:

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

公式释义:

  • 与上一个例子同理

第三类:只能查找文本,返回行号

1. 在 D4 单元格中输入以下公式 --> 回车:

=MATCH('々',A:A)

公式释义:

  • “々”在汉字中是一个编码很大的字符,可以通过小键盘的 Alt+41385 输入;

  • MATCH('々',A:A):在 A 列中查找“々”,当匹配不到,也没有更大编码的值时,就会返回区域内最后一个文本单元格的序列号

第四类:只能查找文本,返回值

1. 在 D5 单元格中输入以下公式 --> 回车:

=LOOKUP('々',A:A)

公式释义:

  • 与前一个公式同理,所不同的是 lookup 函数返回的是单元格的值

第五类:只能查找数值,返回值

1. 在 E5 单元格中输入以下公式 --> 回车:

=LOOKUP(9E+307,B:B)

公式释义:

  • 9E+307 通常表示 Excel 能处理的最大数值;

  • LOOKUP(9E+307,B:B):在 B 列中查找这个最大数值,找不到则返回最后一个单元格的值

第六类:只能查找数值,返回行号

1. 在 E4 单元格中输入以下公式 --> 回车:

=MATCH(MAX(B:B)+1,B:B)

公式释义:

  • MAX(B:B)+1:查找 B 列中的最大值并加上 1;

  • MATCH(...,B:B):在 B 列中查找这个比最大值还大的值,找不到就一直找到最后一个单元格,并返回其在区域中的序列号

2. 在 E6 单元格中输入以下公式 --> 回车:

=MATCH(9E+307,B:B)

公式释义:

  • 9E+307 的作用跟上一个公式的参数一样,也是找一个比区域内最大数值还大的数

3. 在 E7 单元格中输入以下公式 --> 回车:

=LOOKUP(MAX(B:B)+1,B:B,ROW(B:B))

公式释义:

  • ROW(B:B):生成一个 B 列的行号数组,数组内是从 1 开始,以 1 递增的自然数

  • LOOKUP(MAX(B:B)+1,B:B,...):在 B 列中查找比最大数还要大的数值,找不到就查找到最后一个非空单元格,返回第三个参数中同等位置的值,即最后一个单元格的行号

最终结果如下。

转发、在看也是爱!

本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请点击举报
打开APP,阅读全文并永久保存 查看更多类似文章
猜你喜欢
类似文章
【热】打开小程序,算一算2024你的财运
怎样提取最后一列非空单元格内容?
Excel函数教程
如何在Excel中判断是否有重复数据出现
史上最全MATCH函数应用教程
公式中的这些特殊数字,你都了解吗?
Excel函数公式使用心得
更多类似文章 >>
生活服务
热点新闻
分享 收藏 导长图 关注 下载文章
绑定账号成功
后续可登录账号畅享VIP特权!
如果VIP功能使用有故障,
可点击这里联系客服!

联系客服