Excel用函数公式筛选数据的方法图解教程
发布时间:2023-03-09 07:49:21
本文通过实例剖析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。
第二:index(区域,行,列)
Index($B:$F,5,COLUMN(A1)),返回B:F列这个区域的第五行第一列,对应的单元格就是B5单元格。
第三:为了美观,最后添加&""
上面index部分就可以完成筛选数据,但在下拉右拉复制公式时,超过结果以外的单元格会显示“0”,如果想去掉0,直接用空白单元格,不显示0,就可以在公式最后添加&""。
&""是什么意思呢? &是个文本粘贴符,后面的""是表示空白文本,就等于在后面强制性的把(0)粘贴成了空白文本。
猜你喜欢
- 在Excel中录入数据的时候可能会需要用到文本框进行录入,录入之后不懂得如何设置属性,下面让小编为你带来在excel怎么设置文本框的方法。在
- word2016中怎么设置怎么从中间开始显示页码?word文档真正开始的位置在中间,想要从中间开始设置页码,该怎么设置呢?下面我们就来看看详
- 在制作Excel工作表时,用户经常需要在不同的工作表间引用数据。在引用数据时,有时需要引用相匹配的数据,,现在需要在“Sheet2”工作表的
- excel表格中彩色页眉页脚怎样打印?excel表格中不能直接设置彩色的页眉页脚,设置也没办法打印出来,该怎么办呢?下面我们就来看看exce
- Surface Dial是一款极具创意的外围设备,它能够和Win10系统一起工作,实现一些快捷功能,比如快速滚屏、屏幕缩放和定位操作,使用非
- 在创建好数据透视表后,为了使其满足对数据分析的需要,可对excel2010表格中的数据透视表的布局进行修改。当数据透视表中的分析要点不明确时
- 经常使用word进行中英文混合文字编辑时,可能会遇到中英文段落不整齐,那么,该怎么办呢?不着急,赶紧看完本就会找到解决办法了。Word文档中
- 我们在使用word进行排版设置时经常会碰到到输入上标或下标的情况,上标下标的作用非常广泛,那么在word文档中如何输入上标和下标呢?下面小编
- 在Excel中录入数据的过程中都会遇到意外退出的时候,只要是意外退出都会有数据丢失的情况出现,这个时候就需要恢复未保存Excel文件的技巧了
- 很多情况下的一些默认行为,会导致可怕的后果。我们用win10商店下载游戏或者软件,默认保存在c盘,长期以往会导致c盘空间不足,影响系统的运行
- Word恢复文本转换器的图文使用方法具体解决方案如下:第一步:找任意一个未损坏的文件打开word,在工具-选项-常规中,选中“打开时确认转换
- 微软近日正式发布了适用于 Microsoft Edge 浏览器的 Office Online 扩展插件,此前该插件只适用于 Google C
- 在Excel中,下拉菜单可以方便我们输入数据,很多人都知道这是用数据有效性制作出来的。那么EXCEL怎么制作表格下拉菜单?下面小编就为大家详
- 用户在需要办公时第一反应是选择使用word软件来解决文档的编辑工作,给用户带来了许多的便利,word软件现在已经是热门的办公软件了,受到了很
- 在编辑文档时,用户可能会常常碰到一些标准词典中没有但又希望拼写检查能够作为正确单词接受的词汇。用户可以自定义字典,并把这些词汇添加到自定义的
- 默认情况下,Word2010文档的文本框垂直对齐方式为顶端对齐,文本框内部左右边距为0.25厘米,上下边距为0.13厘米。这种设置符合大多数
- word开始栏如何固定1、我们鼠标左键双击开始即可将菜单栏固定2、固定后就是我们熟悉的word页面了3、若想再次隐藏开始菜单再次双击开始即可
- 在工作和学习中,我们往往需要对文字进行对齐。下面是小编为大家精心整理的关于Word如何不使用空格对齐文字,希望能够帮助到你们。方法/步骤1如
- 在我们使用excel来进行计算和统计数据的时候,经常需要将数据转换成百分比的模式,要如何才能转换呢,下面让小编为你带来excel以百分比方式
- 表格在文档中经常会用到,但为什么你的表格老是被嫌丑,而别人的表格既美观又漂亮呢?本期Word小编教大家几招快速美化的技巧。如下图所示,如何将