通过实例剖析 excel 中经典的筛选数据的函数公式的用法

Excel中关于筛选数据最简单的方法，是使用数据透视表完成。如果对透视表不熟悉，可以使用Excel函数写公式来筛选。　　如下图，题目说明：A1：F11为数据源。筛选各组中工资最高的人的各项资料(如果最高工资重复，请按顺序分别显示出来），详见附件。　　A18输入公式，按下ctrl+shift+enter组合键完成数组公式的输入，然后右拉下拉复制公式。 =INDEX($B:$F,SMALL(IF(($F$2:$F$11=MAX(($D$2:$D$11=$A$16)*$F$2:$F$11))*($D$2:$D$11=$A$16),ROW($2:$11),4^8),ROW(A1)),COLUMN(A1))&""　　解题思路：确定两个条件：组数：D2:D11=$A16；最高工资：F2:F11=MAX((D2:D11=A16)*F2:F11)) 　　公式构成：index（区域，行，列）&""——index($B:$F，行部分，COLUMN(A1)) &""。　　用index+small函数构造出来的筛选公式，经典在于获取出相应的行。剖析公式一般从内到位，用F9键逐一查看运算结果。　　第一：small部分，获取行号，剖析如下：　　1.MAX((D2:D11=A16)*F2:F11))*(D2:D11=A16) 　　D2:D11=A16，判断D列的组别和A16组别是否相等，得到FALSE和TRUE构成的逻辑数组。 　　(D2:D11=A16)*F2:F11，计算结果将符合条件的true对应的数字取出来： 　　{0;0;0;9000;6000;0;0;0;0;0} 　　然后用max(数字)，取出最大值9000。　　2．IF部分： 　　IF（条件，是，否）——if（F2:F11=9000，ROW($2:$11),4^8） 　　在F2：F11区域中查找等于第一部分max计算的最大值，如果等于最大值，就返回对应的行号（ROW($2:$11)），否则就返回4^8。4^8：是4的8次方，结果等于65536 即2003中最大的行号。　　3.small部分：　　Small(最大行号和符合条件的行号，row(A1) 　　用SMALL在65536和对应的一个行号中取最小值，得到的就是符合条件的行号。 　　SMALL({65536;65536;65536;5;65536;65536;65536;65536;65536;65536},ROW(A1))，结果是5。