首页 > Excel专区 > Excel函数 >

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

Excel函数 2021-11-25 12:51:27

Excel中关于筛选数据最简单的方法,是使用数据透视表完成。如果对透视表不熟悉,可以使用Excel函数写公式来筛选。

  如下图,题目说明:A1:F11为数据源。筛选各组中工资最高的人的各项资料(如果最高工资重复,请按顺序分别显示出来),详见附件。

excel筛选函数,excel筛选数据

  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部分,获取行号,剖析如下:

Excel用函数公式筛选数据的方法

  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。

  第二:index(区域,行,列)
  Index($B:$F,5,COLUMN(A1)),返回B:F列这个区域的第五行第一列,对应的单元格就是B5单元格。

  第三:为了美观,最后添加&""
  上面index部分就可以完成筛选数据,但在下拉右拉复制公式时,超过结果以外的单元格会显示“0”,如果想去掉0,直接用空白单元格,不显示0,就可以在公式最后添加&""。

  &""是什么意思呢? &是个文本粘贴符,后面的""是表示空白文本,就等于在后面强制性的把(0)粘贴成了空白文本。


标签: excel筛选数据的函数公式

office教程网 Copyright © 2016-2020 https://www.office9.cn. Some Rights Reserved.