如何将同一行中不同列的单元格合并到同一列中不同行的单元格? [英] How do I combine cells from different columns on the same row to cells on different rows in the same column?

问题描述

我以某种方式获得数据，但是我需要将其自动转换为Google Data Studio中特定图表的不同布局.

I have data coming in a certain way, but I need it automatically converted into a different layout for a specific chart in Google Data Studio.

基本上，我想从这里开始

Basically, I want to go from this

Date    Apple   Banana   Cherry

Jan 1   500     800      2000
Jan 2   800     920      2878
Jan 3   1700    1850     3000

对此:

Date     Type     Amount

Jan 1    Apple    500
Jan 1    Banana   800 
Jan 1    Cherry   2000
Jan 2    Apple    800
Jan 2    Banana   920
Jan 2    Cherry   2878
Jan 3    Apple    1700
Jan 3    Banana   1850
Jan 3    Cherry   3000

我想不出解决方案(我相对较新).我希望有人可以指出正确的方向.在此先感谢您能获得的任何帮助！

I can't think of a solution (I'm relatively new at this). I'm hoping someone might be able to point me in the right direction. Thanks in advance for any help I can get!

推荐答案

终极720°自由度:

=ARRAYFORMULA({"Date", "Type", "Amount"; 
 SPLIT(TRANSPOSE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF(B2:Z<>"", A2:A&"♠"&B1:1&"♠"&B2:Z&"♦", )), , 500000)), , 500000)), "♦")), "♠")})

这篇关于如何将同一行中不同列的单元格合并到同一列中不同行的单元格?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！