英文:
Filter formula that excludes text found in a range from the filtered range
问题
我正在使用FILTER函数从B3:B600范围中删除不需要的项目。问题是，我的当前公式只在使用范围中的列表作为条件时排除完全匹配，或者在使用单个单元格时排除部分匹配。我想简化它，并能够从范围中删除完全匹配和部分匹配。
当前公式：
=FILTER(B3:B600,NOT(ISNUMBER(SEARCH(Q3:Q100,B3:B600)))*NOT(ISNUMBER(MATCH(B3:B600,R3:R100,0)))*(B3:B600<>""))
由于我有时需要使用精确匹配来排除，我正在使用两个范围。第一个范围用于排除部分匹配，第二个用于精确匹配。原因是一些有效项目可能符合部分标准，并且如果我使用通用过滤器，它们可能会被排除。
英文:
I'm using the FILTER function to remove undesirable items from a range in B3:B600. The issue is that my current formula only excludes exact matches when using a list from a range as the criteria, or excludes partial matches when using a single cell. I would like to simplify it and be able to remove both exact matches and partial matches from a list in a range.
Current formula:
=FILTER(B3:B600,NOT(ISNUMBER(SEARCH(Q3:Q100,B3:B600)))*NOT(ISNUMBER(MATCH(B3:B600,R3:R100,0)))*(B3:B600<>""))
Since I sometimes need to use exact matches to exclude, I am using two ranges. The first range is for excluding the partial matches, the second is for the exact matches. The reason is that some valid items might meet the partial criteria and be excluded if I were to use a one-size fits all filter.
答案1
得分: 2
你可以尝试以下公式-
=FILTER(B3:B9,BYROW(B3:B9,LAMBDA(x,NOT(OR(COUNTIFS(x,"*"&D3:D5&"*")))))
英文:
You may give a try to the following formula-
=FILTER(B3:B9,BYROW(B3:B9,LAMBDA(x,NOT(OR(COUNTIFS(x,"*"&D3:D5&"*"))))))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论