好久没更新函数学习的文章了,对于PMC来说,掌握函数是一个技能,只有掌握了,才有能力去分析,整合数据,PMC可以不学习函数,也可以学习,从收益的角度来看,学习函数对逻辑能力,解决问题的能力等都有提升,所以还是推荐大学好好学习函数;

今天需要学习的函数,非常经典,解决了以前非常痛苦的一对多,模糊查询,一维转二维的问题统统解决;

三维数据转化为二维(用了这个函数后一对多)(1)

函数定义: 函数可以基于定义的条件筛选出一系列数据

白话说明: 在不改变源数据的同时,在新的区域生成一个按条件筛选的数据

使用格式: FILTER(array,include,[if_empty])

对应版本: OFFICE365、OFFICE2021

参数

参数要求

参数说明

array

必需

要排序的区域或数组

include

必需

布尔值数组,其高度或宽度与数组相同

[if_empty]

可选

如果找不到,返回的结果

1.可将数组视为一行值、一列值或几行值和几列值得组合。

2.FILTER 函数将返回一个数组,如果该数组是公式的最终结果

3.按“Enter”时,Excel 将动态创建相应大小的数组范围

4.Excel 对工作簿之间的动态数据提供有限支持,并且仅当这两个工作簿都处于打开状态时才支持此方案。

5.针对4的理解就是尽量内部关联,外部引用如果关了一个就会出错误,返回 #REF! 错误

6.如果数据集可能返回空值,请使用第三个参数 ([if_empty])。否则将导致 #CALC! 错误,因为 Excel 当前不支持空数组。

一般用法

案例1:输入单个查询条件,返回查询条件的结果,如学历 之一对多查询

下图中,在源数据中,输入查询的条件,就返回了对应的结果,如查询本科,符号本科的条件全部在新表中显示出来了。这也相当于一对多查询

三维数据转化为二维(用了这个函数后一对多)(2)

步骤1:创建查询条件区域→数据→数据验证→序列→录入:本科,专科,高中,初中

三维数据转化为二维(用了这个函数后一对多)(3)

步骤2:录入公式 =FILTER(B26:E35,C26:C35=G26),注意各参数对应的区域;

三维数据转化为二维(用了这个函数后一对多)(4)

案例2:输入多个查询条件,返回查询条件的结果,如学历 部门

与案例1的区别是多条件,多条件我们可以通过“条件*条件”返回逻辑值的经典方法来实现。

下图中,想实现工艺部门中本科学历的员工有多少?

录入公式=FILTER(B43:E52,(D43:D52=G46)*(C43:C52=G43),"无合适数据")

返回如下图结果

三维数据转化为二维(用了这个函数后一对多)(5)

案例3:三条件查询以上加比较符号的筛选

条件不单可以并列查询,也可以实现比较符号“<"," >",来实现

下图中,是想实现工艺部门,本科学历,绩效大于90分的结果

=FILTER(B62:E71,(D62:D71=G65)*(C62:C71=G62)*(E62:E71<G68),"无合适数据")

三维数据转化为二维(用了这个函数后一对多)(6)

案例4:条件不光可以是并,也可是或者,用加号实现

=FILTER(B77:E86,(C77:C86=G77)*(D77:D86=G80) (D77:D86=G86),"")

这里知识点就是,乘号是并,加号或者

三维数据转化为二维(用了这个函数后一对多)(7)


以上是一般用法,现在要讲

高级用法

案例5:模糊查询

有时候查询筛选数据的时候,只记得关键字,或者少量信息,我们可以用模糊查询来实现,此时需要配合函数 FIND ISNUMBER 来实现;

下图中,输入员工姓名的某个关键字,就可以返回包含这个关键字的相关结果,如输入:“文”

=FILTER(B93:E102,ISNUMBER(FIND(G93,B93:B102)),"无此人")

三维数据转化为二维(用了这个函数后一对多)(8)

公式释义:

  1. 先用FIND找包含文的位置,找到近回数字,找不到返回错误
  2. 再用ISNUMBER转换在逻辑值 FLASE与TRUE
  3. 最后用FILTER返回TRUE的结果

案例6:返回指定结果并排名, 查询部门后,并对绩效进行降序排序

有时候我们找到结果后,还需要对结果的数据进行处理,如排名;

下图中,返回了工艺部门的员工数据,还想对员工的绩效进行降序排名

录入公式:=SORT(FILTER(B111:E120,D111:D120=G111,""),4,-1)

三维数据转化为二维(用了这个函数后一对多)(9)

公式释义:

  1. 先用FILTER返回工艺部所有员工
  2. 再用SORT对返回数据进行条件排序
  3. 数据4代表排序依据在第4列
  4. -1代表降序

案例7:把下面的结果转成二维数据

有时候PMC在建立多工序数据库的时候,如果按一维数据来显示的话,行太多了,可能上万行,此时如果转成二维数据,行数减少了,数据也直观。

如下图,某金工车间的工序数据库为一维数据,需要转成二维数据

三维数据转化为二维(用了这个函数后一对多)(10)

我们可以这样操作:

步骤1:录入函数=UNIQUE(B127:B137) 把一维数据中的产品名称去除重复值;

三维数据转化为二维(用了这个函数后一对多)(11)

步骤2:录入函数=TRANSPOSE(FILTER(C128:C137,B128:B137=$G128,))

进行一维转二维转换

三维数据转化为二维(用了这个函数后一对多)(12)

公式释义:

  1. 先用UNIQUE去除重复值
  2. 再用FILTE对去除重复值后的条件进行查询并返回数据
  3. 再用TRANSPOSE对数据进行转置

原创不易,喜欢此文,请转换分享后私信“FILTER”获得源文件

三维数据转化为二维(用了这个函数后一对多)(13)

我是古哥:

从事制造行业18年,在企业运营、供应链管理、智能制造系统等方面具有丰富的实战经验。企业智能化,柔性化计划运营管理专家,擅长通过企业流程优化规范,企业管理、导入计划运营提升企业效率;对提高企业准时交货率,降低企业库存,输出智能制造人才有丰富的经验。学习PMC生产计划,关注古哥计划!10月特训班3期即将开班,全方位学习计划运营知识。

,