Google Sheets QUERY函数解析错误求助：按条件复制Sheet1数据到Sheet2

Got it, let's sort out that QUERY formula issue you're facing. Your current formula has a few syntax missteps that are causing the parse error—here's how to fix it, plus an alternative that might be even easier to use.

What's Wrong with Your Original Formula? #

Let's break down the errors in =query("Sheet1"!A:D,"Select A where F="Yes""):

• Incorrect data range: You're only referencing A:D, but the formula needs access to column F to check the "Yes" value. You need to include column F in the data range.
• Unnecessary quotes around the sheet reference: Sheet1 doesn't need to be wrapped in quotes—just write Sheet1!A:F directly.
• Limited SELECT statement: You're only selecting column A, but you want to copy columns A:D.
• Mismatched quotes for the string value: In the QUERY language, string values need to be wrapped in single quotes (since the entire query string is in double quotes). Using double quotes inside double quotes breaks the syntax.

Correct QUERY Formula #

Replace your formula with this, and it should work as expected:

=QUERY(Sheet1!A:F, "SELECT A,B,C,D WHERE F='Yes'")

How this works:

1. Sheet1!A:F tells the formula to use all data from columns A to F in Sheet1 (so it can check column F's values).
2. "SELECT A,B,C,D" specifies that we want to pull columns A through D into Sheet2.
3. WHERE F='Yes' filters the rows to only include those where column F has the exact value "Yes".

Alternative: Use the FILTER Function (Simpler for This Use Case) #

If you find QUERY's SQL-style syntax tricky, the FILTER function is often more straightforward for basic row filtering. Try this instead:

=FILTER(Sheet1!A:D, Sheet1!F:F="Yes")

This formula directly filters columns A:D from Sheet1, keeping only rows where column F equals "Yes". It automatically expands to include all matching rows, and you don't have to worry about SQL-like syntax.

Bonus: Handle Case Insensitivity (If Needed) #

If your column F might have variations like "YES" or "yes" and you want to match all of them, adjust the QUERY formula to use UPPER():

=QUERY(Sheet1!A:F, "SELECT A,B,C,D WHERE UPPER(F)='YES'")

Or for the FILTER function:

=FILTER(Sheet1!A:D, UPPER(Sheet1!F:F)="YES")

内容的提问来源于stack exchange，提问作者Jason