Google表格QUERY + MATCHES函数不排除字符串 [英] Google sheet QUERY + MATCHES function doesn't exclude strings

问题描述

我想从不包含某些字符的工作表中导入行.我首先使用CONTAINS函数完成了此操作，但没有找到使用多个参数进行操作的方法.所以我使用MATCHES函数做到了:

I would like to import rows from a sheet that exclude certains character. I did it first using the CONTAINS function but i didn't find a way to do it with multiple parameters. So i did it using the MATCHES function :

= Query(importrange("URL";"Sheet！a:be");"SELECT Col1，Col3，Col4，Col26，Col8，Col30，Col40，Col41，Col44，Col45，Col49不在Col8中匹配'.* alc.* |.* vin.* |.* alcool.*'''')

但是在Col8中仍然有这些字符串出现的行，我不知道为什么?

however there is still rows where those strings appear in Col8, i don't know why ?

无论如何，我该怎么做才能过滤除那些字符串之外的导入内容?

How could i do it in order to filter my import excluding those strings no matter what ?

推荐答案

如果由于 MATCHES 而导致行没有被过滤掉，则区分大小写(确实如此，并且不能使用中的标志> QUERY 的正则表达式)，则可以改用 FILTER :

In case the rows are not filtered out because of MATCHES is case sensitive (it is, and one cannot use flags in QUERY's regex), you can use FILTER instead:

=FILTER(
  QUERY(
    IMPORTRANGE("URL"; "Sheet!A:BE");
    "SELECT Col1, Col3, Col4, Col26, Col8, Col30, Col40, Col41, Col44, Col45, Col49"
  );
  NOT(REGEXMATCH(IMPORTRANGE("URL"; "Sheet!H:H"); "(?i)alc|vin|alcool"))
)

这篇关于Google表格QUERY + MATCHES函数不排除字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！