Google Sheets反向转置

Question

我从另一个用户那里看到了这个解决方案，它得到了( Opal)的回答。我正在尝试修改结果，但遇到了空值的问题。我有这个原始数据表(作为示例)

idn | id名称| d1 | d2 | d3 ...

id1 | /name1/ | 23 | 13 | 56

id2 | /name2/ | 67 | 45

id3 | /name3/ | 43

id4 |/名称4/| 78 | 34 | 45

。。

我想将此列表转置为以下形式，以便上载到数据库，结果应如下所示：

id1 | /name1/ | date1 | 23

id1 | /name1/ | date2 | 13

id1 | /name1/ | date3 | 56

id2 | /name2/ | date1 | 67

id2 | /name2/ | date2 | 45

id3 | /name3/ | date1 | 43

id4 | /name4/ | date1 | 78

id4 | /name4/ | date2 | 34

id4 | /name4/ | date3 | 45

。

。

我如何做到这一点-我目前的解决方案如下，但不适用于空白。我看到Opal solution可以处理空格，但不知道如何创建date列(结果中的第3列)

ArrayFormula({transpose(split(query(rept(A2:A&“"，COUNTA(C1:1))，,50000)，”"))，转置(SPLIT(查询(REPT(B2:B& "，COUNTA(C1:1))，,50000)，“"))，转置(SPLIT(join(”"，ARRAYFORMULA(REPT(join(“"，(查询(C1:1)&”"，COUNTA(A2:A)，“"))，转置(split(join(“"，query(转置(C2:M)，,50000))，”“)

有什么建议吗？这是一个很大的数据集，我正在达到所有字符串函数的限制，rept限制为32,000，连接限制为50,000谢谢。

Answer 1

这是一个相当不错的公式。我相信你知道，如果空格是0，那么公式是有效的。我知道这不是一个完美的解决方案，但它是有效的。在另一张图纸中使用：

=ArrayFormula({transpose(split(query(rept(Sheet1!A2:A&" ", COUNTA(Sheet1!C1:1)),,50000)," ")),transpose(split(query(rept(Sheet1!B2:B&" ", COUNTA(Sheet1!C1:1)),,50000)," ")),TRANSPOSE(SPLIT(JOIN(" ", ARRAYFORMULA(REPT(join(" ",(query(Sheet1!C1:1)))&" ", COUNTA(Sheet1!A2:A)))), " ")),transpose(split(join(" ",query(transpose(Sheet1!C2:E5+0),,50000)), " "))})

在公式中，C2:E5+0将空格替换为0。它不会影响原始数据。这将返回格式正确的数据，但包含不需要的0行。在另一个工作表上，使用以下命令清理不需要的行：

=query(Sheet2!A:D,"select * where D != 0")

这将以正确的格式返回所需的数据。