将 Google Sheets Query 结果从一行拆分为两行 [英] Split Google Sheets Query results from one row into two
本文介绍了将 Google Sheets Query 结果从一行拆分为两行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想获取我的 QUERY
结果并将一个单元格(H [描述])移动到每个结果的下一行.
我找到了
<小时>=ARRAYFORMULA(QUERY(SUBSTITUTE(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(TRANSPOSE))QUERY(TRANSPOSE(IF(C1:C<>", "♥"&A1:A&"♦"&B1:B&"♦"&C1:C&"♥ ♦"&D1:D, )),,999^99)),,999^99), "♥")), "♦")), D1, ),其中 Col2 不为空", 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 Sheets Query 结果从一行拆分为两行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文