精华内容
下载资源
问答
  • excel多条件筛选函数 用Excel函数实现排序与筛选的方法,多掌握一门知识就等于多能解决一些问题,今天要介绍的是excel多条件筛选函数的相关知识,我相信当你看完excel多条件筛选函数 用Excel函数实现排序与筛选的...

    excel多条件筛选函数 用Excel函数实现排序与筛选的方法,多掌握一门知识就等于多能解决一些问题,今天要介绍的是excel多条件筛选函数的相关知识,我相信当你看完excel多条件筛选函数 用Excel函数实现排序与筛选的方法这篇文章的时候你一定会掌握一门新技能,从而让你的工作更简单!

    Execl本身具有很方便的排序与筛选功能,下拉“数据”菜单即可选择排序或筛选对数据清单进行排序或筛选。但也有不足,首先无论排序或筛选都改变了原清单的原貌,特别是清单的数据从其它工作表链接来而源数据发生变化时,或清单录入新记录时必须从新进行排序或筛选。其次还有局限,例如排序只能最多对三个关键字(三列数据)排序,筛选对同一列数据可用“与”、或“或”条件筛选,但对不同列数据只能用“与”条件筛选。例如对某张职工花名册工作簿,要求筛选出年龄大于25岁且小于50岁或年龄大于50岁或小于25岁都是可行的,如同时要求性别是男的或女的也是可行的。但要求筛选出女的年龄在22岁到45岁,男的年龄在25岁到50岁时Execl本身具有的筛选功能则无能为力了。再者排序与筛选不能结合使用,即不能在排序时根据条件筛选出来的记录进行排序。例如有一张职工资料清单,其中有的职工已经退休,对在职职工的年龄进行排序时无法剔除已退休职工的数据。

    本文试图用Execl的函数来解决上述问题。

    一、用函数实现排序

    题目

    如有一张工资表,A2:F501,共6列500行3000个单元格。表头A1为姓名代码(1至500)、B1为姓名、C1为津贴、D1为奖金、E1为工资、F1收入合计。现要求对职工收入从多到少排序,且在职工总收入相同时再按工资从多到少排序,在职工总收入和工资相同时再按奖金从多到少排序,在职工职工总收入和工资、奖金相同时再按津贴从多到少排序。

    方法

    G1单元格填入公式

    “=if(F2=0,10^100,INT(CONCATENATE(999-f2,999-e2,999-d2,999-c2)))”,

    CONCATENATE是一个拼合函数,可以把30个以下的单元的数据拼合成一个数据,这些被拼合的数据之间用逗号分开。用f2、e2等被拼合的数据用999来减,是为了使它们位数相同。(假定任何一个职工的总收入少于899元)。被拼合成的函数是文本函数,CONCATENATE与INT函数套用是为了使文本转换为数字。最外层的if函数是排序时用来剔除不进行排序的记录,在本例中指收入为零的记录。(在上文提到的职工年龄排序,则公式改为“if(f2="退休",10^100,.....)”,即剔除了退休职工。)

    第二步把G1单元格的公式拖放到G500单元格(最简便的方法是点击G1单元格后向G1单元格右下方移动鼠标,见到黑十时双击鼠标就完成了G1到G500的填充)。

    第三步在在H2单元填入公式“=MATCH(SMALL(G:G,ROW(A1)),G:G,0)”与第二步一样拖放到H501单元格。此公式实际上是把三列公式合成一列公式,ROW(A1)即为A1的行数是1,随着向下拖放依次为2、3、4...,SMALL(G:G,ROW(A1))为G列中最小的数随着向下拖放依次为第2、第3、..小的数,MATCH(SMALL(G:G,ROW(A1)),G:G,0)即为G列各行的数据中最小、第2、第3小等的数据在第几行。

    第四步把A1至F1单元格的表头复制到I1至N1单元格,在I2单元格输入公式“=INDEX($A$2:$F$501,$H2,COLUMN(A$1))”INDEX函数是一个引用函数,即把$A$2:$F$501单元格列阵第$H2行第COLUMN(A$1)列的数据放入I2单元格。然后把I2单元格的公式拖放到N2单元格,点击N2单元格后向N2单元格右下方移动鼠标见到黑十时双击鼠标就完成了I2到N501单元格的填充到此全部完成。

    以上叙述看似繁杂实际非常简单,只要把A1至F1的表头复制到I1至N1单元格,再分别在G1、H2、I2单元格输入公式然后向下拖放,即使对EXCEL应用不熟练的同志一分锺内便能完成。

    对上述程序稍作变化还可得到更多用度。上面例子数据是从大到小排列的,如H列的函数中的SMALL改为LARGE,上面例子数据就从小到大排列了。如H2单元格的公式改为“=IF(O1=1,MATCH(SMALL(G:G,ROW(A1)),G:G,0),MATCH(LARGE(G:G,ROW(A1)),G:G,0))”

    并把H2单元格的公式向下拖放。这样在O1单元格输入1上面例子数据是从大到小排列的,O1单元格输入1以外的数上面例子数据就从小到大排列了。

    如在H列前插入若干列,如插入一列,则现在的H列输入类似G列的公式,例如

    “=if(F2=0,10^100,d2)”,现在的I列的公式改为“=IF(P1=1,MATCH(SMALL(G:G,ROW(A1)),G:G,0),

    MATCH(SMALL(H:H,ROW(A1)),H:H,0)))”

    即在P单元格输入1以外的值就实现了按奖金大小排序.这样只要通过改变P1(原来的O1单元格)单元格内容的改变就能立即得到按不同要求的排序。

    二、用函数实现筛选

    题目

    如有一张职工名册表,A2:F501,共6列500行3000个单元格。表头A1为姓名代码(1至500)、B1为姓名、C1为性别、D1为年龄、E1为学历、F1职称。现要求对职工的性别、年龄、学历、职称进行交错筛选,例如要求在同一张表上筛选出1、女的年龄在22岁到45岁,男的年龄在25岁到50岁,2、女博士,3、男博士后。

    方法

    第一步在G2单元格输入公式”=IF(OR(AND(C2="女",D2>=22,D2<=45),AND(C2="男",

    D2>=25,D2<=50)),ROW(A1),0)“,在H2单元格输入公式”=IF(AND(C2="女",E2="博士"),

    ROW(B1),0)“,在I2单元格输入公式”=IF(AND(C2="男",E2="博士后"),ROW(B1),0)“。在J2单元格输入公式“=IF(K$2=1,LARGE(G:G,ROW(A1)),IF(K$2=2,LARGE(H:H,ROW(A1)),

    IF(K$2=3,LARGE(I:I,ROW(A1)),0)))”然后用上述提到的方法向下拖放。G、H、I列的公式的含义就是凡符合筛选条件的行记录下行号否则为零,J列的公式的含义根据K2的数值选择G、H、I中的一列进行排序并把不合条件的行除去。

    第二步在K1单元格输文字”筛选选择”,A1到F1表头复制到L1到Q1,在L2单元格输入

    公式“=IF($J2=0,0,INDEX($A$2:$F$501,$J2,COLUMN(A$1)))”,然后向右拖放到Q2,再向下拖放。INDEX函数的含义上文已说明。

    第三步在P1单元格输入1或2或3便可实现上述三种筛选。

    以上就是excel多条件筛选函数 用Excel函数实现排序与筛选的方法全部内容了,希望大家看完有所启发,对自己的工作生活有所帮助,想要了解更多跟excel多条件筛选函数 用Excel函数实现排序与筛选的方法请关注我们优词网!

    展开全文
  • Excel多条件筛选求和

    2013-07-26 15:25:00
    单位A 代码B 面积(㎡)C A组 011 124 A组 123 15 A组 011 356 A组 123 44 ...按照单位和代码求面积的和,可以用EXCEL中的SUM函数, 1、可以在函数栏中先输入=SUM...
    单位A 代码B 面积(㎡)C
    A组 011 124
    A组 123 15
    A组 011 356
    A组 123 44
    B组 123 31
    B组 011 2
    B组 123 2

     按照单位和代码求面积的和,可以用EXCEL中的SUM函数,

    1、可以在函数栏中先输入=SUM(IF(A$2:A$8=A1,IF(B$2:B$8="011",C$2:C$8),0)),然后再按shift+Ctrl+Enter键,即可。

    2、如果仅有一个条件,就直接输入=SUMIF(A$2:A$8,A1,C$2:C$8)即可。

    3、也可以输入=SUM((A$2:A$8=A1)*(B$2:B$8="011")*(C$2:C$8)),然后再按shift+Ctrl+Enter键。最后一项为求和区域,前面为求和条件

    注意,输入条件时,文本要加" "双引号。而数字则不用。

    希望对大家有所帮助。

    转载于:https://www.cnblogs.com/wylaok/p/3217188.html

    展开全文
  • 编按:哈喽,大家好!...*********说起一对查找,大家首先想到的就是万金油公式,以前也分享过一篇相关的教程《Excel万金油公式INDEX-SMALL-IF-ROW筛选函数公式解读》。或许有些朋友也会想到那个最...

    bce0cd0a1881887ad20b40c0ace0cdd3.png

    编按:哈喽,大家好!说到excel中的筛选,想必大家早已是了如指掌,不过增强版的筛选,你听说过吗?它可比普通的筛选厉害多了,不仅能实现excel中的一对多查找,就连复杂的多对多查找也不在话下!赶紧来看看吧!

    *********

    说起一对多查找,大家首先想到的就是万金油公式,以前也分享过一篇相关的教程《Excel万金油公式INDEX-SMALL-IF-ROW筛选函数公式解读》。或许有些朋友也会想到那个最近很火,据说是可以让VLOOKUP提前退休的XLOOKUP函数实现一对多查找。

    其实是使用公式还是用其它方式解决问题,也要看应用场景的,比如只是临时的需要,那就完全不需要使用这么繁琐的万金油公式,也不需要这个像空中楼阁似的XLOOKUP函数出马(能使用这个函数的Excel少得可怜)。

    今天要介绍的这个功能在处理一对多甚至是多对多的查找时,就有着无可替代的优势,以下面这个员工档案表为例:

    b539c40ca606f38870a53d971f88d179.png

    要把财务部的所有人员信息单独提取出来,使用公式就会很麻烦,而用下面要介绍的这个功能只需要点几下鼠标就能搞定,这个功能就是高级筛选。

    到底有多神奇,看动画演示就知道了:

    0531221f288acb6a85fc8285beb72167.gif

    看完可能有朋友会想,这好像和筛选后复制粘贴差不多,只不过是方便了一点点而已,并没有多神奇啊。

    不要急,这才是开始。

    请看第二个问题:要把财务部的男性员工全部提取出来。

    这个就属于多对多的匹配(两个条件对应多个结果),用公式可以想象到有多麻烦,即便用筛选,也需要筛选两次,再复制粘贴,而使用高级筛选,同样只是需要点几下鼠标而已。

    8a62da1ba6dc5e49029ce7dd31b88320.gif

    看到这两个操作的区别了吗,就是条件区域不一样,问题2中条件区域是这样的:

    c9934cfbfa56eab9f48442008a9a5d13.png

    请注意:这里虽然是两个条件,部门和性别,但是这两个条件是针对同一个人而设置的,因此是平行的条件,在同一行呈现。两个条件还有一种情况,例如需要把财务部和人事部的人员信息一次提取出来,这也是两个条件,但却是针对部门而言有两个条件,所以条件在同一列,因此条件区域应该是这样的:

    161158a1db56a6614bf024685029bacd.png

    我想如何用高级筛选大家应该都能想到了,还是看看操作演示吧:

    3b5350e94cbd95f9fe93977e1511fa2f.gif

    通过以上三个演示,各位想必对高级筛选有一点兴趣了吧!

    再来看看下一个问题:需要把财务部小于30岁的女性和人事部所有的男性都提取出来,有没有觉得这个条件好复杂呢,其实看一下就明白了:

    f12167ec2e6f8313fe72762ab3d8e08b.png

    而操作依然非常简单:

    da3305b52c90a8169bcfa46b75e418bf.gif

    来看今天的最后一个例子,把姓张的所有人员信息提取出来。

    cbd9d5e2c57aedbf7b0e0ae8a2ed72cb.gif

    在这个例子中,条件也非常简单:

    59667b0bd5aaab6ed2619a051acdc9b0.png

    张后面的*表示通配符

    在Excel中,通配符是一个非常有用的符号,下面做个小科普

    通配符是Excel中一个常用的辅助符号,它只有3种:问号“?”,表示任意一个字符;星号“*”,表示长度不固定的任意字符;波浪符“~”,将通配符转为普通字符。

    上面的例子中,张*就表示姓名中的第一个字是张,至于后面是什么,*统统都代表了。

    *********

    好了,最后再来总结一下高级筛选做一对多查找时设置条件区域的几个要点:

    1、条件区域的标题应当与数据源中的表头对应;

    2、针对于同一个项目(列)可以设置多个条件,条件在同一列呈现;

    3、针对于多个项目设置条件时,需要把多个条件标题设置在同一行排列,并填写具体的条件值;

    4、针对于多项设置多个条件时,需要一个多行多列的条件区域,如果其中某个条件为空,则不作判定;

    5、高级筛选只能将结果复制到同一个sheet中。

    ****部落窝教育-excel高级筛选应用****

    原创:老菜鸟/部落窝教育(未经同意,请勿转载)

    更多教程:部落窝教育(http://www.itblw.com

    微信公众号:exceljiaocheng

    展开全文
  • 编按:哈喽,大家好!...*********说起一对查找,大家首先想到的就是万金油公式,以前也分享过一篇相关的教程《Excel万金油公式INDEX-SMALL-IF-ROW筛选函数公式解读》。或许有些朋友也会想到那个最...
    5d95173a7763aa42c23239d83cf7e045.png

    编按:哈喽,大家好!说到excel中的筛选,想必大家早已是了如指掌,不过增强版的筛选,你听说过吗?它可比普通的筛选厉害多了,不仅能实现excel中的一对多查找,就连复杂的多对多查找也不在话下!赶紧来看看吧!

    *********

    说起一对多查找,大家首先想到的就是万金油公式,以前也分享过一篇相关的教程《Excel万金油公式INDEX-SMALL-IF-ROW筛选函数公式解读》。或许有些朋友也会想到那个最近很火,据说是可以让VLOOKUP提前退休的XLOOKUP函数实现一对多查找。

    其实是使用公式还是用其它方式解决问题,也要看应用场景的,比如只是临时的需要,那就完全不需要使用这么繁琐的万金油公式,也不需要这个像空中楼阁似的XLOOKUP函数出马(能使用这个函数的Excel少得可怜)。

    今天要介绍的这个功能在处理一对多甚至是多对多的查找时,就有着无可替代的优势,以下面这个员工档案表为例:

    382c5a175f9f0509d6ec50ca890fcefb.png

    要把财务部的所有人员信息单独提取出来,使用公式就会很麻烦,而用下面要介绍的这个功能只需要点几下鼠标就能搞定,这个功能就是高级筛选。

    到底有多神奇,看动画演示就知道了:

    e1b4098bf7e303eafec3255e6f32ebec.gif

    看完可能有朋友会想,这好像和筛选后复制粘贴差不多,只不过是方便了一点点而已,并没有多神奇啊。

    不要急,这才是开始。

    请看第二个问题:要把财务部的男性员工全部提取出来。

    这个就属于多对多的匹配(两个条件对应多个结果),用公式可以想象到有多麻烦,即便用筛选,也需要筛选两次,再复制粘贴,而使用高级筛选,同样只是需要点几下鼠标而已。

    54e776b3e841681dbe1cfdf421667f97.gif

    看到这两个操作的区别了吗,就是条件区域不一样,问题2中条件区域是这样的:

    5d1e68a593ab3e3ef8df63e66597afb1.png

    请注意:这里虽然是两个条件,部门和性别,但是这两个条件是针对同一个人而设置的,因此是平行的条件,在同一行呈现。两个条件还有一种情况,例如需要把财务部和人事部的人员信息一次提取出来,这也是两个条件,但却是针对部门而言有两个条件,所以条件在同一列,因此条件区域应该是这样的:

    a7b7616b375548a989c64ed604586d28.png

    我想如何用高级筛选大家应该都能想到了,还是看看操作演示吧:

    fc181d8177dd61b6e9d0e7382aea85bd.gif

    通过以上三个演示,各位想必对高级筛选有一点兴趣了吧!

    再来看看下一个问题:需要把财务部小于30岁的女性和人事部所有的男性都提取出来,有没有觉得这个条件好复杂呢,其实看一下就明白了:

    866a606addb77c05db4e50bcaae3bdaf.png

    而操作依然非常简单:

    f8742bb4a8c1925a93afac2510c858d2.gif

    来看今天的最后一个例子,把姓张的所有人员信息提取出来。

    29e4a547c07c33d595f5e20185993b2d.gif

    在这个例子中,条件也非常简单:

    4eb9bbc1ebb427b318936f724b681ed5.png

    张后面的*表示通配符

    在Excel中,通配符是一个非常有用的符号,下面做个小科普

    通配符是Excel中一个常用的辅助符号,它只有3种:问号“?”,表示任意一个字符;星号“*”,表示长度不固定的任意字符;波浪符“~”,将通配符转为普通字符。

    上面的例子中,张*就表示姓名中的第一个字是张,至于后面是什么,*统统都代表了。

    *********

    好了,最后再来总结一下高级筛选做一对多查找时设置条件区域的几个要点:

    1、条件区域的标题应当与数据源中的表头对应;

    2、针对于同一个项目(列)可以设置多个条件,条件在同一列呈现;

    3、针对于多个项目设置条件时,需要把多个条件标题设置在同一行排列,并填写具体的条件值;

    4、针对于多项设置多个条件时,需要一个多行多列的条件区域,如果其中某个条件为空,则不作判定;

    5、高级筛选只能将结果复制到同一个sheet中。

    ****部落窝教育-excel高级筛选应用****

    原创:老菜鸟/部落窝教育(未经同意,请勿转载)

    更多教程:部落窝教育(www.itblw.com)

    微信公众号:exceljiaocheng

    展开全文
  • 无标题筛选或者函数筛选的核心要点:标题为 空 或者任意其他 非 列表区域字段条件区域必须包含 单元格引用必须以等号(=)开头表达式中可以包含各种函数单元格引用必须是第一条记录(标题行的下...
  • 这个问题,我们可以用三种方法去解决,小伙伴们挑选适合自己的一种方法记住即可~高级筛选法不用函数公式,Excel自带高级筛选功能,只不过这里并不是精准匹配,需要对筛选条件进行处理。在D列对筛选条件进行处理,将...
  • 只不过筛选功能仅能满足一个条件的查找,如果同时满足两个条件,这时候单纯的使用excel筛选功能就会显得无能为力了。那么,问题来了,如果我们要挑选出同时满足两个条件的数据应该如何实现呢?在这种情况下,我们会...
  • 可以完成列联动筛选,比如筛选B列大于A列的数据可以筛选非重复的数据,重复的只保留一个可以用函数完成非常复杂条件筛选以上都是自动筛选无法完成的,够高级了吧:D二、如何使用高级筛选?打开“数据”选项卡,可...
  • 主要有:使用SUM函数编写的数组公式、联用SUMIF和辅助列(将多条件变为单条件)、使用SUMPRODUCT函数、使用SUMIFS函数(限于Excel2007及以上的版本),方法千差万别、效果各有千秋。本人更喜欢用SUMPRODUCT函数。由
  • 分析:由于下面的字段的顺序与上面的字段的序列是不一样的,所以使用VLOOKUP函数的话,先得使用一般的筛选然后才能一列一列地查询匹配过来。今天小必老师教给大家一个简单的纯操就能完成这个问题。主要使用高级筛选...
  • 但很朋友对Excel的高级筛选功能却知之甚少,那么今天我们就通过几个案列来详细介绍下Excel的高级筛选功能,通过今天的学习,你会发现Excel高级筛选的功能让你意外的强大,即便是不懂vlookup函数,也可以是个查询...
  • 可以完成列联动筛选,比如筛选B列大于A列的数据可以筛选非重复的数据,重复的只保留一个可以用函数完成非常复杂条件筛选以上都是自动筛选无法完成的,够高级了吧:D二、如何使用高级筛选?打开“数据”选项...
  • 这个问题看上去似乎有点复杂,感觉一下子想不出好的解决办法,实际上,却非常简单,而且仅用常见的求和函数,就能轻松解决问题,话不说,赶紧来看看吧!*********今天分享的这个问题来源于一位群友的求助...
  • 一、Excel工作表筛选:普通单条件筛选目的:筛选相应“学历”下的员工信息。方法:1、选中工作表数据区域中的任意单元格,【数据】-【筛选】,给标题行添加筛选按钮。2、单击【学历】右侧的倒三角,将搜索框下面不予...
  • 他们的格式都相近:部门,姓名,型号等等 现在是希望在“结果”这个工作表中对五个工作表的值进行汇总,而且要能按照部门和姓名进行筛选 这个问题可以通过以下公式来解决 =SUMPRODUCT((T(INDIRECT("'"&...
  • 相信小伙伴们非常熟悉筛选功能,如果不会函数公式查找匹配,筛选是最佳首选。一、一个条件筛选上图中我们需要筛选出"T恤"的相关数据,首先选中表头行任一单元格,点击【开始】或【数据】选项卡中的【排序和筛选】...
  • Excel多条件匹配

    2011-08-18 15:19:00
    看了小蚊子的博客关于Excel多条件匹配的文章,自己也尝试一下,发现实际操作还真的是会学到更多的东西。目前Excel多条件匹配的方法主要有三种: SUMPRODUCT函数,SUMIFS函数(注意不是sumif),高级筛选功能 1....
  • 大家好,上期内容我们介绍了数据筛选并乘积求和的小方法,今天,就再来带大家了解一下多条件求和的公式~如下图所示,我们今天的任务就是要利用sumif函数求出各小组的总销售情况。首先,在单元格中输入公式:公式为:...
  • Excel中,之所以函数是它的核心,不仅仅是因为他在数据处理当中的强大,我想更的是它带来的变化性,很地方运用函数就会使原来的功能更加灵活,高效,比如在条件格式中,函数的运用能使功能更加多样,在高级...
  • 有时候,我们利用高级筛选就可以轻松达成多条件的查询。比如下图所示,我们需要快速查找出岗位补贴>900并且出勤津贴>500的员工,那怎么快速完成查询呢?接下来,请睁大眼睛,跟随小编一起来学习吧!具体操作...
  • 如果评选最强大的查找函数,肯定有很多人选Vlookup、lookup或xlookup,但有一个函数却在实力上碾压它们,它就是:Filter函数filter是Excel365新增的筛选函数,除了单条件、反向查找、多条件查找外,还可以实现其他...
  • 这个问题,我们可以用三种方法去解决,小伙伴们挑选适合自己的一种方法记住即可~高级筛选法不用函数公式,Excel自带高级筛选功能,只不过这里并不是精准匹配,需要对筛选条件进行处理。在D列对筛选条件进行处理,将...
  • Excel中把指定数据筛选出来的方法有很,可以用普通筛选、可以用相关的函数,笔者感觉在一些情况下使用高级筛选更加方便,这里简要介绍一下高级筛选的用法,供大家参考。 例如要让Excel自动从下图中的表格中挑选...
  • FILTER单条件筛选在以下示例中,我们使用公式 =FILTER(A5:...多条件筛选在此示例中,我们使用乘法运算符 (*),以返回数组范围 (A5:D20) 中包含“苹果”且位于东部区域的所有值:=FILTER(A5:D20,(C5:C20=H1)*(A5:A20=...

空空如也

空空如也

1 2 3 4 5 ... 14
收藏数 276
精华内容 110
关键字:

excel多条件筛选函数