英文:
How to use 'not in' in google sheet query?
问题
Sure, here is the translated code part:
[![在这里输入图片描述][1]][1]
[1]: https://i.stack.imgur.com/bJVTk.png
大家好，
我想要在单元格F3中使用Google查询来过滤列C和D。这取决于单元格A3:A7中的产品是什么。在上面的截图中，我希望被排除的行是产品A和C，因此查询能够帮助我删除具有产品A和产品C的行。我尝试在查询中使用 `not in`，但它不够灵活，因为现在单元格A3:A7中只有2个要排除的产品，将来可能会有更多或更少。
如上面的截图所示，期望的输出结果（列F、G）。如果有其他公式可以实现这一目标，请也与我分享。
非常感谢任何帮助！
英文:
Hi all,
I want to use a google query in cell F3 to filter the columns C&D. It depends on what is the Product in cell A3:A7. In the screenshot above, the rows that I want to be excluded is Product A and C, so the query able to help me remove the rows with Product A and Product C. I tried to use not in in the query, but it is not flexible as right now in cell A3:A7 only have 2 products want to be excluded, in future there might be more or less.
The expected output shown in the screenshot above (column F,G). If there is any other formula can achieve this, please share with me as well.
Any help will be greatly appreciated!
答案1
得分: 1
=FILTER(C3:D,NOT(COUNTIF(A3:A,D3:D)))
英文:
Try:
=FILTER(C3:D,NOT(COUNTIF(A3:A,D3:D)))
答案2
得分: 1
在Google表格中，QUERY()函数中的not matches可用作SQL条件中的Not In的等效选项。尝试使用以下公式：
=QUERY(C3:D,"where not D matches '" & TEXTJOIN("|",1,A2:A) & "'")
英文:
In google-sheet QUERY() function you can use not matches as equivalent of Not In SQL condition. Try-
=QUERY(C3:D,"where not D matches '" & TEXTJOIN("|",1,A2:A) & "'")
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论