英文:
Line insertions and spacing with Google Sheets formulas
问题
以下是已翻译的内容：
我有以下格式的数据：
以文本形式：
In/Out 类别 子类别 金额
In In A In A.1 100
In In A In A.2 20
In In B In B.1 500
Out Out C Out C.1 20
Out Out D Out D.1 50
Out Out D Out D.2 10
Out Out D Out D.3 15
Out Out E Out E.1 45
Out Out F Out F.1 5
Out Out F Out F.2 110
我想以以下格式显示它：
最好使用内置公式，而不是自定义函数。
“总计”列不太重要。我可以相对容易地单独完成它。我更关心的是去除重复行并插入行。
谢谢
英文:
I have data in the following format:
In text form:
In/Out Category Sub-Category Amount
In In A In A.1 100
In In A In A.2 20
In In B In B.1 500
Out Out C Out C.1 20
Out Out D Out D.1 50
Out Out D Out D.2 10
Out Out D Out D.3 15
Out Out E Out E.1 45
Out Out F Out F.1 5
Out Out F Out F.2 110
I would like to display it in the following format:
Preferably with built in formulas and not custom made functions.
The "Totals" column is not as important. I can do that separately fairly easily. It is more the removal of the repetitions and insertions of rows that I am after.
Thanks
答案1
得分: 3
你可以尝试以下代码：
=let(one,wraprows(,1,),null,wraprows(,4,),Σ,unique(tocol(A2:A,1)),data,
reduce(A1:D1,sequence(counta(Σ)),lambda(a,c,{a;
{index(Σ,c),wraprows(,2,),sumif(A:A,index(Σ,c),D:D)};
let(Δ,unique(filter(B:B,A:A=index(Σ,c))),reduce(wraprows("null_",4,),sequence(counta(Δ)),lambda(x,y,{x;{one,index(Δ,y),one,sumif(B:B,index(Δ,y),D:D)};
let(Γ,filter(C:D,B:B=index(Δ,y)),Ξ,counta(choosecols(Γ,1)),{makearray(Ξ,2,lambda(r,c,iferror(1/0))),Γ}); null})))})),
filter(data,index(data,,1)<>"null_"))
希望这对你有帮助。
英文:
You may try:
=let(one,wraprows(,1,),null,wraprows(,4,),Σ,unique(tocol(A2:A,1)),data,
reduce(A1:D1,sequence(counta(Σ)),lambda(a,c,{a;
{index(Σ,c),wraprows(,2,),sumif(A:A,index(Σ,c),D:D)};
let(Δ,unique(filter(B:B,A:A=index(Σ,c))),reduce(wraprows("null_",4,),sequence(counta(Δ)),lambda(x,y,{x;{one,index(Δ,y),one,sumif(B:B,index(Δ,y),D:D)};
let(Γ,filter(C:D,B:B=index(Δ,y)),Ξ,counta(choosecols(Γ,1)),{makearray(Ξ,2,lambda(r,c,iferror(1/0))),Γ});
null})))})),
filter(data,index(data,,1)<>"null_"))
答案2
得分: 0
以下是翻译好的内容：
这是另一种解决方案：
=LET(data,A:D,cdata,INDEX(data,2,1):INDEX(data,MAX(ROW(data)*(MMULT(N(data<>"",{1;1;1;1})=4))),io,INDEX(cdata,,1),cat,INDEX(cdata,,2),scat,INDEX(cdata,,3),amt,INDEX(cdata,,4),IFNA(REDUCE(INDEX(data,1),UNIQUE(io),LAMBDA(a,c,VSTACK(a,HSTACK(c,VSTACK({"","",SUMIF(io,c,amt)},LET(_,REDUCE(,UNIQUE(FILTER(cat,io=c)),LAMBDA(_a,_c,VSTACK(_a,HSTACK(_c,VSTACK({"",SUMIF(cat,_c,amt)},FILTER({scat,amt},cat=_c),))))),FILTER(_,SEQUENCE(ROWS(_))>1)))))))))))
英文:
Here's another solution:
=LET(data,A:D,cdata,INDEX(data,2,1):INDEX(data,MAX(ROW(data)*(MMULT(N(data<>""),{1;1;1;1})=4))),io,INDEX(cdata,,1),cat,INDEX(cdata,,2),scat,INDEX(cdata,,3),amt,INDEX(cdata,,4),IFNA(REDUCE(INDEX(data,1),UNIQUE(io),LAMBDA(a,c,VSTACK(a,HSTACK(c,VSTACK({"","",SUMIF(io,c,amt)},LET(_,REDUCE(,UNIQUE(FILTER(cat,io=c)),LAMBDA(_a,_c,VSTACK(_a,HSTACK(_c,VSTACK({"",SUMIF(cat,_c,amt)},FILTER({scat,amt},cat=_c),))))),FILTER(_,SEQUENCE(ROWS(_))>1)))))))))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论