6种场景，10个公式，只为查找Excel最后一个非空单元格

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

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

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

案例：

下图 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 列中查找比最大数还要大的数值，找不到就查找到最后一个非空单元格，返回第三个参数中同等位置的值，即最后一个单元格的行号
  

最终结果如下。

转发、在看也是爱！