通过实例剖析 excel 中经典的筛选数据的函数公式的用法
发布时间:2022-03-12 09:19:54
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)粘贴成了空白文本。
猜你喜欢
- Excel2013中怎么加载数据分析模块?excel中数据很多的时候,我们需要加载数据分析功能来帮我们完成计算,怎么加载数据分析模块呢?下面
- 我们办公使用Excel表格的时候,经常会输入各种各样的数据,类似于电话号码、身份证号等数据。为了保证数字的准确性,我们得限制数据的长度。一起
- 我们在文档中使用MathType编辑大量公式,使用格式刷之后公式会上靠,使文档看起来不美观,这时我们就要采取一些办法来解决,以下步骤教您如何
- 大家好,这篇文章跟大家分享如何在Word文档中输入人民币和美元货币符号。下图左一为单横线人民币符号,左二为双横线人民币符号,右一为美元货币符
- wps演示怎么转换成视频图解首先大家就要学会制作PPT,因为视频是用PPT转化得来的,所以大家想要一个号的视频,就需要大家先学会制作一个好的
- 今天给大家分享的是excel合并单元格的那些事儿,请准备好板凳啤酒花生米,静耳倾听。本文主要讲解不规则单元格添加序号、取消单元格合并并填充内
- 应用场景按照下面图1所示Word文档"邀请函样本"中的邀请函样本批量发送电子邮件给不同的评审专家,每封电子邮件邀请函的抬
- 根据往年的数据或样本数据,用Excel做销售预测或成本分析,在各行业都有广泛的应用。本文讲述了使用TREND函数和LINEST函数做销售预测
- 当你买一些商品时,你会注意到所有商品后面都有一个条形码。通过识别条形码,你可以得到许多信息,如制造商、名称、日期、价格等。那么条形码是如何制
- 我们在使用Win10系统时Win10 Windows无法格式化F怎么办?出现这一问题该如何解决呢?小编在这里给大家介绍下Win10 Wind
- 在日常工作中我们常常会遇到需要打印A3纸的表格,但手头却只有A4纸或只有支持A4纸的打印机,那怎么办呢。下面让小编为你带来excel表格a3
- 操作步骤如下所述: 第1步,打开Word2010文档窗口,单击选中剪贴画对象。第2步,在“图片工具/格式”功能区的
- excel求和怎么操作在excel表格中,自动求和是一个非常实用的操作,但是应该怎么在excel表格中进行设置呢?下面随seo实验室小编一起
- 在Word2003中,当用户建立了一个新的Word文档后,在空白的Word文档起始处有一个不断闪烁的竖条,这就是插入点光标,如图200911
- 大家是否常惊叹人家的图表咋就这么美,自己的图表都不堪入目呢?其实图表既简单又复杂,简单是因为只要选中数据单击插入图表就能做出折线图、柱状图等
- 缩进标记在某些情况下,你可能希望能更多地控制缩进。Word提供缩进标记,使您可以缩进的段落,到你想要的位置。那么下面就由小编给大家分享下wo
- 使用Word,大多情况下启动Word后第一件事就是执行“打开”命令,既然如此,何不让Word一启动就自动显示“打开”对话框呢?这样我们需要多
- -首页不要页码-前言、目录部分用 I, II, III 罗马数字编页码-正文部分用 1,2,3 阿拉伯数字编页码我们需要做三个操作:显示分节
- 说明IMSEC 函数返回以 x + yi 或 x + yj 文本格式表示的复数的正割值。返回值复数的正割值。语法=IMSEC(inumber
- Excel中的手机号码如何分段显示呢?通常为了更清晰的查看手机号码,都会选择分段隔开数字,便于浏览。在Excel表格中怎么分段显示手机号码呢