将Google表格查询结果从一行分成两部分 [英] Split Google Sheets Query results from one row into two

问题描述

我要获取我的 QUERY 结果并将一个单元格(H [说明])移到每个结果的下一行.

我找到了

---

  = ARRAYFORMULA(QUERY(SUBSTITUTE(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(C1:C< C，C& A1:A&♦"& B1:B&♦"& C1:C&♥♦&& D1:D，)),, 999 ^ 99)),, 999 ^ 99)，♥"))，♦"))，D1，)，其中Col2不是Null"，0)) 

I want to take my QUERY results and move one cell (H [description]) to the following row of each result.

I found this answer, which is very close, but the format of the table is different, so I'm not sure how to apply it to my scenario.

This is the basic idea:

    Category | Sub-Category | Item       | Description
    Fruit    | Citrus       | Orange     | It's orange
    Fruit    | Melon        | Cantaloupe | Round

Into

    Category | Sub-Category | Item  
    Fruit    | Citrus       | Orange     
    It's orange
    Fruit    | Melon        | Cantaloupe 
    Round

解决方案

=ARRAYFORMULA(QUERY(SUBSTITUTE(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(
 QUERY(TRANSPOSE(IF(IFERROR(SPLIT(C1:C, ";"))<>"", "♥"&A1:A&"♦"&B1:B&"♦"&
 IFERROR(SPLIT(C1:C, ";"))&"♥"&D1:D, )),,999^99)),,999^99), "♥")), "♦")), D1, ), 
 "where Col1 is not null", 0))

---

=ARRAYFORMULA(QUERY(SUBSTITUTE(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(
 QUERY(TRANSPOSE(IF(C1:C<>"", "♥"&A1:A&"♦"&B1:B&"♦"&C1:C&"♥ ♦"&D1:D, ))
 ,,999^99)),,999^99), "♥")), "♦")), D1, ), 
 "where Col2 is not Null", 0))

这篇关于将Google表格查询结果从一行分成两部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！