Excel–各种带条件花式查找,用filter函数就对了 |
您所在的位置:网站首页 › 函数可逆的条件 › Excel–各种带条件花式查找,用filter函数就对了 |
公众号平台最新的推送规则对技术类文章不太友善,如果不想错过干货,请务必 “设为星标”哦!!! 点击上方蓝字 --> 点击“...”--> 选择“设为星标”
连续讲了几期 O365 函数,非常受读者欢迎。 O365 与之前的版本相较算是一个质的飞越,函数语法变得更加简洁易懂,还简化了数组公式的使用,新增的函数没有鸡肋,每一个都解决了之前版本的痛点。 所以今天再教一个同样让我期待已久的 filter 函数。 函数说明: 作用: 基于定义的条件筛选区域内的数据。 语法: FILTER(array,include,[if_empty]) 参数: array:必需,要筛选的区域或数组。 include:必需,布尔值数组,其高度或宽度与 array 相同。 [if_empty]:可选,include 参数的数组中所有值都为空(筛选器不返回任何内容)时返回的值。 说明: array 可以一行值、一列值,也可以是几行值和几列值的组合。 FILTER 函数会返回一个数组,如果该数组是公式的最终结果,将会溢出。也就是说,按下回车键后,Excel 将动态创建相应大小的数组范围。如果支持数据位于 Excel 表格中,若使用结构化引用,则从数组范围中添加或删除数据时,数组将自动重设大小。 如果数据集可能返回空值,需要使用第三个参数 ([if_empty])。否则将导致 #CALC! 错误 ,因为 Excel 当前不支持空数组。 如果 include 参数的所有值都是个错误值(#N/A、#VALUE 等)或无法转换为布尔值,则 FILTER 函数将返回一个错误。 如果数据在不同的工作簿中,只有所有工作簿时都处于打开状态时才能返回正确的结果。否则将返回 #REF! 错误 。 案例: 下图 1 中的表格为某公司员工一季度的收入和业绩完成情况明细,需要在右边区域按以下要求查找: “诸葛钢铁”的所有记录 “诸葛钢铁”1月的记录 “诸葛钢铁”和“赵铁锤”的所有记录 业绩完成率超过 100% 的所有记录 业绩完成率末 3 名,从低到高排 姓名中有“大”字的所有记录 效果如下图 2 至 7 所示。
解决方案 1:查找“诸葛钢铁”的所有记录 在 G2 单元格中输入以下公式: =FILTER(A2:E25,B2:B25="诸葛钢铁") 公式释义: A2:E25:在 A2:E25 区域筛选 B2:B25="诸葛钢铁":筛选条件为 B 列的单元格内容等于"诸葛钢铁"
解决方案 2:查找“诸葛钢铁”1月的记录 在 G2 单元格中输入以下公式: =FILTER(A2:E25,(B2:B25="诸葛钢铁")*(A2:A25="1月")) 公式释义: A2:E25:在 A2:E25 区域筛选 (B2:B25="诸葛钢铁")*(A2:A25="1月"):筛选条件为 B 列等于"诸葛钢铁"并且 A 列等于“1月” * 表示“与”条件
解决方案 3:查找“诸葛钢铁”和“赵铁锤”的所有记录 在 G2 单元格中输入以下公式: =FILTER(A2:E25,(B2:B25="诸葛钢铁")+(B2:B25="赵铁锤")) 公式释义: A2:E25:在 A2:E25 区域筛选 (B2:B25="诸葛钢铁")+(B2:B25="赵铁锤"):筛选条件为 B 列等于"诸葛钢铁"或“赵铁锤” + 表示“或”条件
解决方案 4:查找业绩完成率超过 100% 的所有记录 在 G2 单元格中输入以下公式: =FILTER(A2:E25,D2:D25>1) 公式释义: 筛选区域相同,不再复述 D2:D25>1:筛选条件为 D 列的数值大于 1
解决方案 5:查找业绩完成率末 3 名,从低到高排 在 G2 单元格中输入以下公式: =SORT(FILTER(A2:E25,D2:D25 公式释义: SMALL(D2:D25,4):D2:D25 区域中数值最小的 4 个单元格 D2:D25 FILTER(A2:E25,...):根据末 3 名,筛选出区域内的行 SORT(...,4):将筛选出的结果按第 4 列升序排序,即“业绩完成率”从低到高排
解决方案 6:查找姓名中有“大”字的所有记录 在 G2 单元格中输入以下公式: =FILTER(A2:E25,ISNUMBER(FIND("大",B2:B25)),"查无此人") 公式释义: FIND("大",B2:B25):在 B2:B25 区域中查找“大”字,找到会返回一个数字,表示它在文本中的位置;找不到则返回错误值 ISNUMBER(...):用 isnumber 判断 find 函数返回的是否是数字,产生一组结果为 true 或 false 的逻辑值 FILTER(A2:E25,...,"查无此人"):筛选出区域内条件为 true 的行;如果找不到,则返回“查无此人” 有关 find 函数的详解,请参阅 Excel – 困扰许久的多条件模糊查找,输出不同结果,用find函数终于搞定了 。
如果把公式中的“大”替换为“中”,G2 单元格就会返回“查无此人”。
|
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |