Excel 从后向前进行查找

最近要用 Excel 来做一个报表,期间需要一个功能:给你一个列表(包含重复出现的元素)和指定的某个元素,让你通过公式计算得到这个元素在列表中最后出现的位置。

看起来很简单的功能,Excel 居然没有提供原生的支持。尝试过 Index, Match, Find 等,都不支持从后往前的查找。最后在网上找到了歪果仁写的一个方法,据说是 Excel 的 MVP,老牛逼了,文末会给出链接。


所以,我在这篇文章中只是作为一个搬运工,请大家膜拜原作者。


准备工作

熟悉 Row, Sumproduct, Index 这几个公式的用法,及 True/False 在 Excel 中的数值表示


Row (reference)

Row 的中文意思是 “行”,简单来说就是根据你传入的单元格,告诉你该单元格在表中的行号。


Index

中文意为索引,实际为根据你输入的区间,及相对行号(或相对列号),返回对应于该行号和列号(默认为1)的单元格中的内容。其中第1个参数,表示在哪个范围内进行查找;第2个参数表示相对于当前区间的行号;第3个参数表示相对于当前区间的列号。


Sumproduct

中文意思是乘积之和,即将两个相同维度数组中的对应的元素进行相乘,并返回所有元数乘积之和。


True/False

在公式计算中,True = 1,False = 0,所以 True * 1 = 1,False * 1 = 0。


思 路

假设有下列这样的表,让你在 B12 单元格中输出 CC 所出现的最后一行的值:

1,让 A1~A9 中的所有值都和 CC 做一次布尔运算,这样会得到类似 {False,False,True,False,False,False,False,True,False} 这样的一个数组;

2,然后与行号相乘,会得到 {0,0,3,0,0,0,0,8,0} 的数组;

3,取得数组中最大的元素即为最后一次出现的行号;


实 现

B12 =INDEX(B1:B9,SUMPRODUCT(MAX((A1:A9=A12)*ROW(A1:A9))))

1,A1:A9 = A12 即为了获取布尔值

2,(A1:A9)*Row(A1:A9) 即为了获取匹配 A12 的所有行号

3,Max((A1:A9=A12)*Row(A1:A9)) 即为了获取最大的行号

4,使用 SumProduct 从而支持上述数组相乘

5,Index(B1:B9, xxx) 即从 B1~B9 单元格中返回对应于 xxx 相对行号的值


参考链接

How to Use SUMPRODUCT to Find the Last Item in an Excel List

Find the Last Occurrence of an Item in a List in Excel



文章索引

[隐 藏]

本站采用知识共享署名 3.0 中国大陆许可协议进行许可。 ©2014 Charley Box | 关于本站 | 浙ICP备13014059号