通过实例剖析 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)粘贴成了空白文本。


猜你喜欢
- 随着笔记本电脑、移动存储的普及,无论是公司的还是个人的,都越来越关注自有知识产权的保护和内部重要文档的安全问题。如何才能在电脑上安全地存放自
- 有时侯,我们在WPS表格时录入数据时会 发现数字末尾的“0”录不上,这其实是单元格格式设置不正确,只要设置单元格格式为“文本”格式就可以录入
- 最近macdown小编发现一款播放神器——射手影音SPlayer for Mac!射手影音SPlayer for Mac是Mac os系统上
- 怎么使用PS制作立体阴影文字呢?我们经常会看到一些文字是立体形状且带有阴影的,其实这种艺术字的制作方法非常简单,用Photoshop软件就能
- 在Excel工作表中,经常需要在一列中输入固定范围的信息。为了避免用户手工输入的麻烦和输入错误,用户可以将这列中的每个单元格制作成“下拉列表
- windows10 Build 9879版本已经推出了纯净版本,但稳定性似乎还是有待加强,最近,0xAB蓝屏问题让很多用户都头疼不已wind
- Edge浏览器是一款微软所推出的浏览器软件,有不少小伙伴都在使用,不过最近有的小伙伴在使用Edge浏览器观看视频的时候发现flash未启用,
- 已经设置成功的红色标注线,选择线条中的自由曲线,点击插入-形状, 在所划之线处于选中状态下, ,在需要标注的地方即可任意划线,例如选择红色,
- Win10 OneNote无法登录怎么办?我们都知道OneNote中的应用是需要登录微软帐户才可以的,最近有用户反映在登录出出现无法登录稍后
- Win7如何设置工作组?在Windows系统中的很多功能组件都需要工作组的帮助,如果工作组不一样,很有可能无法给予用户连接环境,如果用户计算
- 说到浏览器,相信大家都不陌生,虽然这款浏览器的功能非常强大,但是也有人不喜欢它,因此就想将其删除掉。这该如何操作呢?下面,小编就给大家介绍w
- 在许多人的印象中火绒安全软件只是一款口碑极佳的安全防护软件,但该软件中还内置了不少实用性极高的功能。那么问题出现了,火绒安全可以清除垃圾吗?
- Win10如何解决安全启动冲突问题?最近有用户反映,在使用Win10系统的过程中发现它无法正常启动,出现红色警告框,提示安全启动违规,检测到
- Win10系统下背景无法设置,显示已由组织隐藏或设置,这样的情况我们要怎么解决呢?据用户反馈之前用过必应壁纸软件,怀疑跟这个有关,那么就跟着
- 需要编辑文档时可以用自带的文本或写字板代替。如果是使用写字板编辑文档,如何将它保存为TXT格式呢?下面有个方法,需要的朋友可以尝试操作下对于
- Win11蓝屏出现DPC_Watchdog_Violation错误代码怎么解决?最近用户遇到Win11系统提示dpc watchdog vi
- 1.打开需要插入页脚的Word文档 2.点击工具栏的“插入”,然后选择“页脚” 3.除了
- Win10出现错误代码0xc0000001怎么办?相信不少朋友在开机的时候都遇到过这个问题,那么是不是只要遇到这种情况都只能重装系统了呢?其
- 还真不习惯微软对版本命名的变化,熟悉了Alpha、Beta、RC,现在微软偏偏给你整个Win8 DP、CP、RP。总之名字就是代号而已,姑且
- 我们的电脑在安装升级了win10操作系统之后,有的小伙伴们在玩巫师3的时候就遇到了出现游戏过程中突然出现问题然后重启的情况。对于这中问题小编