我有两个 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
答案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,因此第一个句子的保留/丢弃数组中是。FILTER
FILTER
FILTER
MATCH
MATCH
;
现在,外部FILTER
根据颜色选择记录的结果和用于决定显示哪些记录的数组,该数组由相同的数据构建,但独立于数据,因此匹配两者没有任何困难。 (这是使用子FILTER
过滤时最大的困难。 人们通常最终使用一定数量的行范围,比如 100,并返回其中的 38 行,但保留/丢弃数组基于该返回,现在您的源大小 100 和测试大小 38 不匹配,因此会出现错误。 此公式通过分别构建它们来避免这种情况,因此在最终使用时,两者的大小相同。 然后只需正确测试并将结果转换为 {1;1;1;0;0;1;1;0;1;0;0;0;1} 类型的数组。)
只需添加此代码来演示FILTER
即可完成这些子过滤,或者更确切地说,只需添加一种方式即可。只要您记住数组大小匹配问题,就可以做到这一点。通常,有很多种方法。这个方法可能可以改进,但没有必要,因为Scott Craner
的方法更容易扩展到两个以上的条件,就像人们在日常使用中很容易遇到的那样。