在Google电子表格中创建一个新视图,从水平到垂直。

huangapple go评论69阅读模式
英文:

create a new view in google spreadsheet from horizontal to vertical

问题

我有2个选项卡,在选项卡1中有
列C:用户ID
列D-K:月份
在每个月份下面有与列C的每个用户相关的数字
我如何创建一个新的选项卡2,以便我有
列A包含用户,列B包含第一个月,列C包含与第一个月相关联的数字
每一行都应该有用户,月份,相关的数字重复出现在每个月份(从五月到十二月,共8个月)
在这个文件中,我在一个公式中添加了月份(在选项卡2中)
但如何为每个用户重复它?
https://docs.google.com/spreadsheets/d/1NzzbZW5XksqHt8zqEnbr6mhSs6SkIyatCsrdxcES59Y/edit?usp=sharing
谢谢!

你可以检查共享的文件

英文:

i have 2 tabs, in tab1 i have
column C: users ids
column D-K: months
under each month there are numbers referring to each and every user of column C
how can i create a new tab2 so that i have
column A contains user, column B contains the 1st month, column C contains the number linked to the first month
each row should have user, month, related number repeated for each month (8, from May to December)
in this file i added the month in a formula (in tab2)
but how to repeat it for each userid?
https://docs.google.com/spreadsheets/d/1NzzbZW5XksqHt8zqEnbr6mhSs6SkIyatCsrdxcES59Y/edit?usp=sharing
thanks!

you can check the shared file

答案1

得分: 1

=let(rep_, 'tab1'!C3:C18, grp_, 'tab1'!D3:K18, months_, 'tab1'!D2:K2,
Σ, reduce(wraprows(, 3, ), sequence(counta(rep_)), lambda(a, c, {a;{wrapcols(index(rep_,c), columns(grp_), index(rep_,c)), tocol(months_), tocol(index(grp_,c))}})),
filter(Σ, index(Σ, , 1)<>""))

英文:

You may try:

=let(rep_,&#39;tab1&#39;!C3:C18, grp_,&#39;tab1&#39;!D3:K18, months_,&#39;tab1&#39;!D2:K2,
     Σ,reduce(wraprows(,3,),sequence(counta(rep_)),lambda(a,c,{a;{wrapcols(index(rep_,c),columns(grp_),index(rep_,c)),tocol(months_),tocol(index(grp_,c))}})),
     filter(Σ,index(Σ,,1)&lt;&gt;&quot;&quot;))

在Google电子表格中创建一个新视图,从水平到垂直。

huangapple
  • 本文由 发表于 2023年6月22日 18:19:51
  • 转载请务必保留本文链接:https://go.coder-hub.com/76530888.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定