Google查询电子表格:反转录 [英] Google Query Spreadsheet: reverse transpose
本文介绍了Google查询电子表格:反转录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有这张表(举例)
id | a | b | c
1 | 23 | 13 | 56
2 | 67 | 45 |
3 | 43 | |
4 | 78 | 34 | 45
我想使用查询语句上传转置命令,以便将其上传到我的数据库,结果应该如下所示:
1 | 23
1 | 13
1 | 56
2 | 67
2 | 45
3 | 43
4 | 78
4 | 34
4 | 45
我该怎么做?
解决方案
假设您有A:D中的数据,请尝试:
< pre $ = ArrayFormula {(转置(拆分(查询(rept(A:A和amp;,if(len(B:B),countif(if(not(isblank(B:D )),row(B:B)),row(B:B)),)),, 50000),)),transpose(split(join(,query(transpose(B:D),, 50000)),))})
I have this table (as an example)
id | a | b | c
1 | 23 | 13 | 56
2 | 67 | 45 |
3 | 43 | |
4 | 78 | 34 | 45
I want to upload transpose that using the query statement so that I can upload it to my database, the result should look like:
1 | 23
1 | 13
1 | 56
2 | 67
2 | 45
3 | 43
4 | 78
4 | 34
4 | 45
How would I do that?
解决方案
Assuming you have the data in A:D, try:
=ArrayFormula({transpose(split(query(rept(A:A&" ", if(len(B:B), countif(if(not(isblank(B:D)), row(B:B)), row(B:B)),)),,50000)," ")),transpose(split(join(" ",query(transpose(B:D),,50000)), " "))})
这篇关于Google查询电子表格:反转录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文