将多行，多列公式压缩为单行 [英] Condense a multi-row, multi-column formula output into a single row

问题描述

我有一个QUERY公式，该公式导入某些数据的前三行"Select Col1, Col2 Order by Col2 desc limit 3"，产生如下输出:

I have a QUERY formula that imports the first three rows of some data, "Select Col1, Col2 Order by Col2 desc limit 3", producing an output like this:

   |   c1   | c2
r1 | Red    | 53
r2 | Blue   | 45
r3 | Yellow | 15

我想要这些相同的公式值看起来像这样:

I'd like to get need those same formula values to look like this:

   |  c1  |  c2  |  c3  |  c4  |   c5   |  c6 
r1 | Red  |  53  | Blue |  45  | Yellow |  15

其中整个输出位于单个行中.
这可能吗?

where the whole output is in a single row.
Is this possible?

推荐答案

是的，用

要获取多列，您可以使用 SPLIT ，而不是连接到" "，而是使用保证不会出现在查询结果中的任意定界符进行连接.如果您希望各种各样的文本成为可能的公式结果，则可能需要变得很有创造力，或者使用unicode.

To get multiple columns, you would wrap this with SPLIT, and instead of joining on " ", join with some arbitrary delimiter that is guaranteed to not be in the query result. If you expect a wide variety of text to be a possible formula result, you may need to get quite creative, or use unicode.

=SPLIT(TEXTJOIN("-;-", TRUE, QUERY(....)), "-;-", FALSE)

这篇关于将多行，多列公式压缩为单行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！