经常碰到一些题目,要求返回某某某第1条数据,第N条数据,最后一条数据,所有数据等等。 下面通过一个简单的栗子,来分享一些方法。 数据源: 第一条数据:(以张三为栗子) 1、vlookup默认返回所有数据的第一条数据。 =VLOOKUP(D1,A1:B11,2,0) 除了vlookup还可以应用match函数 match精确查找,默认返回的也是第一条数据所在的位置(行号) =INDEX(B:B,MATCH(D1,A1:A11,0)) 这两个公式,index match效率会高不少。 最后一条数据:lookup =LOOKUP(1,0/(A2:A11=D1),B2:B11) 下面就A2:A11=D1这个结构来说说如何构建数组。 A2:A11=D1,遍历A2:A11中每一个单元格,如果单元格是张三,那么就返回true,否则返回false,抹黑A2:A11=D1,按f9得出下列数组。 {TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE} 然后,再进一步处理该数组,将其转换为数值形式的数组。 我们要先知道,true和false也是可以用来做运算的。 比如:1 TRUE=2,1 FALSE=1;那么在算是中true=1,false=0(注意,true并不一定等于1,false并不一定等于0,只是在算式中才会这样,有时候只是单纯的逻辑判断结果。) 0/(A2:A11=D1)即: 0/{TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE} 也就是0/1=0,0/0返回#DIV/0!,上述结果为: {0;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!} 再运用lookup函数,查找1,在 {0;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!} 中查找,显然是找不到正确的结果。那么lookup函数就会返回比1小的最后一个数字,也就是最后一个0的位置。 ..... 所以,这样就找到了张三的最后一条数据。 当然,我们也可以这样来构建数组。 1、A1:A11=D1,构建单元格返回等于“张三”的数组,返回 {FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE} 2、既然我们已经知道True=1,False=0;那么我们可以用行号来做文章。 构建行号一般用ROW函数,如row(B1:B11)或row(1:8),返回结果 {1;2;3;4;5;6;7;8;9;10;11},表示1到11行 3、将1、2两个数组相乘 (A1:A11=D1)*ROW(B1:B11) ⬇ {FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}*{1;2;3;4;5;6;7;8;9;10;11} ⬇ {0;2;0;0;5;0;0;8;0;0;0} 至此,我们看到,凡是数组中大于0的数字,都是张三所在的行号,我们取最大的数值即可,使用max函数,公式为:(数组公式,需ctrl shift enter三键一起按) =MAX((A1:A11=D1)*ROW(B1:B11)) 由此,得出,张三最后一条数据所在的行号为8,那么返回其最后一条数据的方法就很多了,可以使用index,offset,indirect等等。 =INDEX(B:B,MAX((A1:A11=D1)*ROW(B1:B11))) =OFFSET(B1,MAX((A1:A11=D1)*ROW(B1:B11))-1,) =INDIRECT('b'&MAX((A1:A11=D1)*ROW(B1:B11))) 除了用max函数,我们也可以用match函数来构建数组 =MATCH(0,0/(A1:A11=D1),1),数组三键后,返回结果 8;得出张三的最后一条数据所在的行号。 返回张三的所有数据。 函数:index small if的万金油公式,此公式在这里就不再多说,这是一条很出名的公式,百度一下就很多很多。 函数辅助列: =COUNTIF(A$2:A2,A2),相对引用单元格区域,对张三进行计数 添加辅助列之后,要引用那一条数据就用可以引用那一条数据,或者所有数据。 1、所有数据:sumifs =SUMIFS(B:B,A:A,$D$1,C:C,ROW(A1)) 2、所有数据:lookup =LOOKUP(1,0/(($A$2:$A$11=$D$1)*($C$2:$C$11=ROW(A1))),$B$2:$B$11) 或者改变辅助列的公式为: =A2&COUNTIF(A$2:A2,A2) =INDEX(B:B,MATCH(D$1&ROW(A2),$C$1:$C$11,0),) =LOOKUP(1,0/($C$2:$C$11=$D$1&ROW(A1)),$B$2:$B$11) 辅助列的方法,其实是十分灵活简单有效率的,比单纯的万金油公式在实际运用中要好得多。 用辅助列的方法可以得出任意第N条数据。 辅助列,甚好。 |
|