最近要用 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