使用QUERY函数进行逆透视（Unpivoting）未得到预期结果

I've run into this exact issue before—dynamic ranges can cause unexpected behavior when combining FLATTEN, SPLIT, and QUERY because they include all empty rows/columns in the calculation. Here's how to resolve it:

Solution 1: Use INDEX + COUNTA for Dynamic Bounds #

This approach explicitly defines the range of non-empty rows and columns, so you don't end up flattening thousands of empty rows:

=QUERY(ARRAYFORMULA(SPLIT(FLATTEN(
  Data!A2:INDEX(Data!A:A,COUNTA(Data!A:A))&"|"&
  Data!D1:INDEX(Data!AG:AG,1,COUNTA(Data!D1:AG1))&"|"&
  Data!D2:INDEX(Data!AG:AG,COUNTA(Data!A:A),COUNTA(Data!D1:AG1))
),"|")), "Select * WHERE Col3 IS NOT NULL")

How it works: #

• Data!A2:INDEX(Data!A:A,COUNTA(Data!A:A)): Gets all non-empty rows in column A (from row 2 to the last row with data)
• Data!D1:INDEX(Data!AG:AG,1,COUNTA(Data!D1:AG1)): Gets all non-empty date headers from D1 to the last header in AG1
• Data!D2:INDEX(Data!AG:AG,COUNTA(Data!A:A),COUNTA(Data!D1:AG1)): Maps the data range to match the non-empty rows and headers
• This limits the FLATTEN operation to only valid data rows/columns, avoiding the empty "||" entries that break your original formula.

Solution 2: Use LET for Cleaner, Readable Code #

If you prefer more readable formulas, use LET to break down the steps:

=LET(
  // Filter out rows where column A is empty
  filteredRows, FILTER(Data!A2:AG, Data!A2:A <> ""),
  // Get all non-empty date headers
  dateHeaders, Data!D1:INDEX(Data!AG:AG, 1, COUNTA(Data!D1:AG1)),
  // Unpivot the data: combine ID, headers, and values
  unpivotedData, ARRAYFORMULA(SPLIT(FLATTEN(
    INDEX(filteredRows,, 1) & "|" & dateHeaders & "|" & INDEX(filteredRows,, 4):INDEX(filteredRows,, COLUMNS(filteredRows))
  ), "|")),
  // Filter out rows with empty values
  QUERY(unpivotedData, "SELECT * WHERE Col3 IS NOT NULL")
)

This version first removes empty rows from your source data, then performs the unpivot, making the logic easier to follow and debug.

Why Your Original Formula Failed #

When you use full-column ranges like A2:A or D2:AG, Google Sheets includes every row in the column (even empty ones) in the FLATTEN operation. This creates hundreds/thousands of empty entries formatted as ||, which can cause QUERY to behave unexpectedly—especially as your dataset grows. By restricting the range to only non-empty rows/columns, you eliminate these invalid entries entirely.

Bonus: Modern Google Sheets Alternative (TOCOL) #

If you're using the latest Google Sheets version, TOCOL can simplify the flattening step (it automatically ignores empty values when using the second parameter):

=QUERY(SPLIT(TOCOL(
  Data!A2:INDEX(Data!A:A,COUNTA(Data!A:A))&"|"&TOROW(Data!D1:INDEX(Data!AG:AG,1,COUNTA(Data!D1:AG1)))&"|"&Data!D2:INDEX(Data!AG:AG,COUNTA(Data!A:A),COUNTA(Data!D1:AG1)),
  1 // Ignore empty values
),"|"), "Select * WHERE Col3 IS NOT NULL")

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