在另一个 FILTER 函数的包含参数中使用 Excel FILTER 函数的动态数组输出

在另一个 FILTER 函数的包含参数中使用 Excel FILTER 函数的动态数组输出

我有两个 Excel 表,我想在其中过滤一些数据,使用过滤一个表的动态数组结果来过滤第二个表。这是一个简化的示例 - 第一个表标记为辅助表并用于根据 CATEGORY 返回相关 SEQ 数组:

序贯 类别
三十二 水果
33 蔬菜
三十四 水果
三十五 蔬菜

我还有一张桌子叫数据表其中包含一些更广泛的数据,包括 SEQ,但不包括 CATEGORY:

指数 序贯 部门 价值
0 14 蓝色的 45689
1 三十二 蓝色的 23749
2 三十二 红色的 23892
3 二十五 红色的 32981

从第二张表中,我想从逻辑上过滤公式等效于“当 DEPARTMENT 为蓝色且 SEQ 来自 HelperTable 中的 CATEGORY Fruit 时的值”,根据该表该公式应返回 23749。

因此我可以这样做FILTER(HelperTable[SEQ],HelperTable[CATEGORY]="Fruit")并得到{32,34},溢出两个单元格,但我不知道如何将该结果嵌套在封装的 FILTER 中: FILTER(DataTable[VALUE], (DataTable[DEPARTMENT]="Blue")*(DataTable[SEQ]=OTHER_FILTER_RESULT))

答案1

使用 ISNUMBER(MATCH()),其中要搜索的值HelperTable[SEQ]和要搜索的数组是第一个过滤器的返回值:FILTER(HelperTable[SEQ],HelperTable[CATEGORY]="Fruit")

=FILTER(DatTable[VALUE],(DatTable[DEPARTMENT]="Blue")*(ISNUMBER(MATCH(DatTable[SEQ],FILTER(HelperTable[SEQ],HelperTable[CATEGORY]="Fruit"),0))))

在此处输入图片描述

答案2

如果您希望坚持FILTER工作,以下方法可以解决问题:

=FILTER(  FILTER(D2:G5,F2:F5="Blue"),  IFERROR(  MATCH(  FILTER(E2:E5,F2:F5="Blue"),  FILTER(A2:A9,B2:B9="Fruit"),  0),  0)  )

第二个(第一个“内部”)FILTER获取与“蓝色”匹配的记录,该数组是第一个(“外部”)的输入FILTER

要构建外部FILTER的保留/丢弃数组(本例中为 {FALSE;TRUE} 或 {0;1}),MATCH首先通过返回原始数据(而不是上面的)来FILTER获取上面出现的成功序列号。函数中的第二个将适当匹配的数组与辅助表的数据进行匹配,然后...将成功数据的序列号与这些匹配。请注意,所有这些返回都是 ROWS,因此第一个句子的保留/丢弃数组中是。FILTERFILTERFILTERMATCHMATCH;

现在,外部FILTER根据颜色选择记录的结果和用于决定显示哪些记录的数组,该数组由相同的数据构建,但独立于数据,因此匹配两者没有任何困难。 (这是使用子FILTER过滤时最大的困难。 人们通常最终使用一定数量的行范围,比如 100,并返回其中的 38 行,但保留/丢弃数组基于该返回,现在您的源大小 100 和测试大小 38 不匹配,因此会出现错误。 此公式通过分别构建它们来避免这种情况,因此在最终使用时,两者的大小相同。 然后只需正确测试并将结果转换为 {1;1;1;0;0;1;1;0;1;0;0;0;1} 类型的数组。)

只需添加此代码来演示FILTER即可完成这些子过滤,或者更确切地说,只需添加一种方式即可。只要您记住数组大小匹配问题,就可以做到这一点。通常,有很多种方法。这个方法可能可以改进,但没有必要,因为Scott Craner的方法更容易扩展到两个以上的条件,就像人们在日常使用中很容易遇到的那样。

相关内容