英文:
Is there a way to convert a given table to another table in excel/google sheets using a combination of formulas? as the example in the below images
问题
尝试为此生成公式，但结果不理想。
英文:
I tried to generate formulas for the same using chatgpt, but the results were not apt.
答案1
得分: 1
你可以尝试：
=let(Σ,wraprows(,3,),
Λ,reduce(Σ,sequence(counta(A2:A)),lambda(a,c,{a;
reduce(Σ,sequence(counta(B1:C1)),lambda(x,y,{x;index(if(sequence(index(B2:C,c,y)),{index(A2:A,c),index(B1:C1,y)})),sequence(index(B2:C,c,y))}))})),
filter(Λ,index(Λ,,1)<>"")
[![在这里输入图片描述][1]][1]
***更新的答案***：
=let(Σ,wraprows(,3,),
Λ,reduce(Σ,sequence(counta(I2:I)),lambda(a,c,{a;
reduce(Σ,sequence(counta(J1:K1)),lambda(x,y,{x;iferror(index(if(sequence(index(J2:K,c,y)),{index(A2:A,c),index(J1:K1,y)})),wraprows(,2,)),iferror(sequence(index(J2:K,c,y)),wraprows(,1,))}))})),
filter(Λ,index(Λ,,1)<>"")
[1]: https://i.stack.imgur.com/UNy5h.png
英文:
You may try:
=let(Σ,wraprows(,3,),
Λ,reduce(Σ,sequence(counta(A2:A)),lambda(a,c,{a;
reduce(Σ,sequence(counta(B1:C1)),lambda(x,y,{x;index(if(sequence(index(B2:C,c,y)),{index(A2:A,c),index(B1:C1,y)})),sequence(index(B2:C,c,y))}))})),
filter(Λ,index(Λ,,1)<>""))
Updated Answer:
=let(Σ,wraprows(,3,),
Λ,reduce(Σ,sequence(counta(I2:I)),lambda(a,c,{a;
reduce(Σ,sequence(counta(J1:K1)),lambda(x,y,{x;iferror(index(if(sequence(index(J2:K,c,y)),{index(A2:A,c),index(J1:K1,y)})),wraprows(,2,)),iferror(sequence(index(J2:K,c,y)),wraprows(,1,))}))})),
filter(Λ,index(Λ,,1)<>""))
答案2
得分: 1
这是另一种解决方案：
={"村庄","班级","团队";
ARRAYFORMULA(
QUERY(
SPLIT(
TOCOL(
TOCOL(TOCOL(A2:A,3)&"ζ"&B1:C1)&"ζ"&
MAP(TOCOL(B2:C,3),LAMBDA(n,SEQUENCE(1,n)))),
"ζ"),
"where Col3 is not null"))}
这个公式是标准数据旋转公式的变体。
英文:
Here's another solution:
={"Village","Class","Team";
ARRAYFORMULA(
QUERY(
SPLIT(
TOCOL(
TOCOL(TOCOL(A2:A,3)&"ζ"&B1:C1)&"ζ"&
MAP(TOCOL(B2:C,3),LAMBDA(n,SEQUENCE(1,n)))),
"ζ"),
"where Col3 is not null"))}
This formula is a variation of the standard unpivoting formula.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论