Google Sheets公式实现特定区域非空单元格逐行输出问询

Hey Brandon,

Looks like you need to unpivot your wide-format table into a narrow one—extracting only the non-empty entries from your Type 1-4 columns, and pairing each with their corresponding date, name, and type label. This is a super common task in spreadsheets, and you can absolutely do it with built-in Google Sheets functions (no scripts needed!) using combinations of tools you already know, plus a few handy ones like FLATTEN, LET, and TOCOL.

Here are two reliable approaches tailored to your needs:

Method 1: Using FLATTEN + QUERY (Simple & Straightforward) #

If you prefer a concise formula that leverages QUERY (which you’re familiar with), this works great. Assuming your raw data starts at row 2, with:

• Column A = Date
• Column B = Name
• Columns C-F = Type 1 to Type 4

Use this formula:

=QUERY(
  {
    FLATTEN(A2:A&"|"&B2:B&"|Type 1|"&C2:C),
    FLATTEN(A2:A&"|"&B2:B&"|Type 2|"&D2:D),
    FLATTEN(A2:A&"|"&B2:B&"|Type 3|"&E2:E),
    FLATTEN(A2:A&"|"&B2:B&"|Type 4|"&F2:F)
  },
  "SELECT SPLIT(Col1, '|') WHERE SPLIT(Col1, '|')[OFFSET(3)] <> ''",
  0
)

How it works:

1. FLATTEN takes each row’s date, name, type label, and value, concatenates them into a single string (using | as a separator to avoid conflicts with your data), then converts the result into a single column. We do this for each Type column separately.
2. QUERY filters out any rows where the value part (the 4th segment of the string) is empty.
3. SPLIT breaks the filtered strings back into four distinct columns: Date, Name, Type, and Value.

Method 2: Using LET + TOCOL + FILTER (Clean & Scalable) #

If you want a more readable and scalable formula (easier to adjust if you add more Type columns later), use LET to name variables, paired with TOCOL (a newer function that’s more flexible than FLATTEN):

=ARRAYFORMULA(
  LET(
    raw_data, A2:F,
    date_col, INDEX(raw_data,,1),
    name_col, INDEX(raw_data,,2),
    type_labels, {"Type 1","Type 2","Type 3","Type 4"},
    value_cols, INDEX(raw_data,,3):INDEX(raw_data,,6),
    
    expanded_dates, TOCOL(date_col * SEQUENCE(1, COLUMNS(value_cols)), 2),
    expanded_names, TOCOL(name_col * SEQUENCE(1, COLUMNS(value_cols)), 2),
    expanded_types, TOCOL(TOROW(type_labels) * SEQUENCE(ROWS(value_cols), 1), 2),
    expanded_values, TOCOL(value_cols, 2),
    
    FILTER(
      {expanded_dates, expanded_names, expanded_types, expanded_values},
      expanded_values <> ""
    )
  )
)

How it works:

1. LET assigns names to key parts of your data, making the formula easier to read and modify.
2. SEQUENCE creates repeating patterns:
   • date_col * SEQUENCE(1,4) repeats each date 4 times (once for each Type column)
   • TOROW(type_labels) * SEQUENCE(ROWS(value_cols),1) repeats each type label for every row of data
3. TOCOL converts these 2D repeating arrays into single columns, using the 2 parameter to automatically skip empty values.
4. Finally, FILTER removes any rows where the value is empty, leaving only the valid entries.

Quick Notes: #

• Adjust the column ranges (like A2:F or INDEX(raw_data,,3):INDEX(raw_data,,6)) to match your actual spreadsheet layout.
• If you’re using an older version of Google Sheets where TOCOL isn’t available, replace TOCOL(...,2) with FLATTEN(IF(..., "", "")) (similar to the first method’s logic).

Both methods use functions you’re already comfortable with (FILTER, QUERY) alongside a couple of powerful array functions to handle the unpivoting. Let me know if you need help tweaking this to fit your exact data!

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