湖南龙网

  1. 首页 > 生活百科 > >

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 。
【Excel用函数公式筛选数据的方法图解教程】第二：index(区域 ， 行 ， 列)
Index($B:$F ， 5 ， COLUMN(A1)) ， 返回B:F列这个区域的第五行第一列 ， 对应的单元格就是B5单元格 。
第三：为了美观 ， 最后添加&""
上面index部分就可以完成筛选数据 ， 但在下拉右拉复制公式时 ， 超过结果以外的单元格会显示“0” ， 如果想去掉0 ， 直接用空白单元格 ， 不显示0 ， 就可以在公式最后添加&"" 。
&""是什么意思呢? &是个文本粘贴符 ， 后面的""是表示空白文本 ， 就等于在后面强制性的把(0)粘贴成了空白文本 。

    推荐阅读


    上一篇：Excel中DB函数使用固定余额递减法计算折旧率及实例下载

    下一篇：天人一物，内外一理。什么意思？