excel怎么根据多个条件返回对应数据,FILTER函数使用技巧
小编:思敏 5773阅读 2020.06.03
FILTER 函数可以基于定义的条件筛选一系列数据。
在以下示例中,我们使用公式 =FILTER(A5:D20,C5:C20=H2,"") 返回包含“苹果”(如单元格 H2 中所选定)的所有记录,如果没有“苹果”,则返回空字符串 ("")。
-----------------------------------------------------------------------------------------------------------------------
语法
FILTER 函数基于布尔值 (True/False) 数组筛选数组。
=FILTER(array,include,[if_empty])
注意:
可将数组视为一行值、一列值或几行值和几列值的组合。 在上述示例中,FILTER 公式的源数组为范围 A5:D20。
FILTER 函数将返回一个数组,如果该数组是公式的最终结果,则将溢出。 这意味着,当按“Enter”时,Excel 将动态创建相应大小的数组范围。 如果支持数据位于 Excel 表格中,若使用结构化引用,则从数组范围中添加或删除数据时,数组将自动重设大小。 有关详细信息,请参阅关于溢出数组行为的此文章。
如果数据集可能返回空值,请使用第三个参数 ([if_empty])。 否则将导致 #CALC! 错误 ,因为 Excel 当前不支持空数组。
如果 include 参数的任何值都是一个错误的值(#N/A、#VALUE 等)或无法转换为布尔值,则 FILTER 函数将返回一个错误。 ?
Excel 对工作簿之间的动态数据提供有限支持,并且仅当这两个工作簿时都处于打开状态时才支持此方案。 如果关闭源工作簿,刷新时,任何链接的动态数组公式都将返回 #REF! 错误 。
示例
用于返回多个条件的 FILTER
在此示例中,我们使用乘法运算符 (*),以返回数组范围 (A5:D20) 中包含“苹果”且位于东部区域的所有值:=FILTER(A5:D20,(C5:C20=H1)*(A5:A20=H2),"")。
用于返回多个条件并排序的 FILTER
在此示例中,我们配合使用之前的 FILTER 函数和 SORT 函数,以返回数组范围 (A5:D20) 中包含“苹果”且位于东部区域的所有值,然后对 Units 进行降序排序:=SORT(FILTER(A5:D20,(C5:C20=H1)*(A5:A20=H2),""),4,-1)
在此示例中,我们配合使用 FILTER 函数和加法运算符 (+),以返回数组范围 (A5:D20) 中包含“苹果”或位于东部区域的所有值,然后对 Units 进行降序排序:=SORT(FILTER(A5:D20,(C5:C20=H1)+(A5:A20=H2),""),4,-1)。
请注意,这些函数都不需要绝对引用,因为它们仅存在于一个单元格中,并将其结果溢出到相邻单元格。
在以下示例中,我们使用公式 =FILTER(A5:D20,C5:C20=H2,"") 返回包含“苹果”(如单元格 H2 中所选定)的所有记录,如果没有“苹果”,则返回空字符串 ("")。
-----------------------------------------------------------------------------------------------------------------------
语法
FILTER 函数基于布尔值 (True/False) 数组筛选数组。
=FILTER(array,include,[if_empty])
参数 |
描述 |
array 必需 |
要筛选的数组或区域 |
include 必需 |
布尔值数组,其高度或宽度与数组相同 |
[if_empty] 可选 |
所含数组中的所有值都为空(筛选器不返回任何内容)时返回的值 |
可将数组视为一行值、一列值或几行值和几列值的组合。 在上述示例中,FILTER 公式的源数组为范围 A5:D20。
FILTER 函数将返回一个数组,如果该数组是公式的最终结果,则将溢出。 这意味着,当按“Enter”时,Excel 将动态创建相应大小的数组范围。 如果支持数据位于 Excel 表格中,若使用结构化引用,则从数组范围中添加或删除数据时,数组将自动重设大小。 有关详细信息,请参阅关于溢出数组行为的此文章。
如果数据集可能返回空值,请使用第三个参数 ([if_empty])。 否则将导致 #CALC! 错误 ,因为 Excel 当前不支持空数组。
如果 include 参数的任何值都是一个错误的值(#N/A、#VALUE 等)或无法转换为布尔值,则 FILTER 函数将返回一个错误。 ?
Excel 对工作簿之间的动态数据提供有限支持,并且仅当这两个工作簿时都处于打开状态时才支持此方案。 如果关闭源工作簿,刷新时,任何链接的动态数组公式都将返回 #REF! 错误 。
示例
用于返回多个条件的 FILTER
在此示例中,我们使用乘法运算符 (*),以返回数组范围 (A5:D20) 中包含“苹果”且位于东部区域的所有值:=FILTER(A5:D20,(C5:C20=H1)*(A5:A20=H2),"")。
用于返回多个条件并排序的 FILTER
在此示例中,我们配合使用之前的 FILTER 函数和 SORT 函数,以返回数组范围 (A5:D20) 中包含“苹果”且位于东部区域的所有值,然后对 Units 进行降序排序:=SORT(FILTER(A5:D20,(C5:C20=H1)*(A5:A20=H2),""),4,-1)
在此示例中,我们配合使用 FILTER 函数和加法运算符 (+),以返回数组范围 (A5:D20) 中包含“苹果”或位于东部区域的所有值,然后对 Units 进行降序排序:=SORT(FILTER(A5:D20,(C5:C20=H1)+(A5:A20=H2),""),4,-1)。
请注意,这些函数都不需要绝对引用,因为它们仅存在于一个单元格中,并将其结果溢出到相邻单元格。
相关推荐
- excel怎么快速计算符合条件单元格的乘积,DPRODUCT函数怎么用 本文介绍 Microsoft Excel 中 DPRODUCT 函数的公式语法和用法。说明返回列表或数据库中满足指定条件的记录字段(列)中的数值的乘积。语法DPRODUCT(database, field, criteria)DPRODUCT 函数语法具有下列参数:Database 必需。 构成列表或数据库的单元格区域。…
- excel怎么快速标注数据是否满足条件,GESTEP函数怎么用 如果 number ≥ step,则返回 1;否则返回 0(零)。 可以使用此函数来筛选一组值。 例如通过对几个 GESTEP函数进行求和,可计算超过阈值的值的计数。语法GESTEP(number, [step])GESTEP 函数语法具有下列参数:Number:必需,要针对步骤进行测试的值。Step:可…
- excel怎么按照条件统计不同的数据,COUNTIFS函数快捷统计技巧 COUNTIFS 函数将条件应用于跨多个区域的单元格,然后统计满足所有条件的次数。语法COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…)COUNTIFS 函数语法具有以下参数:criteria_range1 :必需,在其中计算关联条件的第一个区域。criteria…
- excel怎么按照条件对数据进行标示,OR函数怎么用 在其参数组中,任何一个参数逻辑值为 TRUE,即返回 TRUE;所有参数的逻辑值为 FALSE,才返回 FALSE。 语法 OR(logical1,logical2,...) Logical1,logical2,... 为需要进行检验的 1 到 30 个条件表达式。 说明 参数必须能计算为逻辑值,如 TRUE 或 FALSE,或者为…
- 腾讯文档文档如何分享给好友? 1)电脑web端:进入任意文档--编辑好内容后--点击界面右上角的「 分享 」--设置分享权限及分享人即可。2) 进入任意文档--编辑好内容后--点击界面右上角的「 分享 」--设置分享权限及分享人即可。
- excel怎么快速计算样本标准偏差,STDEV.S函数实用技巧 基于样本估算标准偏差(忽略样本中的逻辑值和文本)。标准偏差可以测量值在平均值(中值)附近分布的范围大小。语法STDEV.S(number1,[number2],...)STDEV.S 函数语法具有下列参数:Number1 必需。 对应于总体样本的第一个数值参数。 也可以用单一数组或对某个…
- 3DMAX提示和技巧 本主题标识使用 Civil View 的一些重要提示和技巧。常规使用屏幕分辨率至少为 1280x1024 的 Civil View。低于此分辨率时,一些面板将占用过多屏幕空间。 将视口设置为线框显示以达到最佳性能。 要尽可能简化用户界面,请在单个视口中工作并关闭 3ds Max 命令面…