当前位置:主页 > Office办公 > excel筛选数据的函数公式

excel筛选数据的函数公式

通过实例剖析 excel 中经典的筛选数据的函数公式的用法
通过实例剖析 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。