怎样分类求和

不爱吃鱼欧小喵

如图，横向排列人名，竖向是科目，怎样汇总求和后人名竖向排列，科目横向排列

wp231957

主要数据是参与计算的，就尽量避免合并单元格

szl3178

1. =LET(x,TEXTSPLIT(TEXTJOIN("+",,TOCOL(IF(C2:H2>0,SCAN(,B4:B8,LAMBDA(x,y,IF(y>0,y,x)))&"-"&C2:H2&"-"&C4:H8,A),3)&"-"&TOCOL((C4:H8*1>0)*C4:H8,3)),"-","+"),VSTACK({"姓名","科目一","科目二","科目三"},DROP(CHOOSECOLS(PIVOTBY(CHOOSECOLS(x,2),TAKE(x,,1),TAKE(x,,-1)*1,SUM,,0,,0),1,4,2,3),1)))

复制代码

能力有限，做不到一毛一样

不爱吃鱼欧小喵

szl3178 发表于 2025-3-12 14:17
能力有限，做不到一毛一样

厉害厉害，太复杂了，准备把原表换个方式了

hugeinfo

用pq清洗一下就行

你是那块小饼干

=SUM(FILTER(CHOOSECOLS($C$4:$H$8,MATCH($M4,SCAN(0,$C$2:$H$2,LAMBDA(X,Y,LOOKUP("座",$C$2:Y))),0)+1),SCAN(0,$B$4:$B$8,LAMBDA(_X,_Y,LOOKUP("座",$B$4:_Y)))=N$3,""))

mykitami

=LET(fx,LAMBDA(z,SCAN(,z,LAMBDA(x,y,IF(y="",x,y)))),_a,FILTER(C4:H8,C3:H3="分数"),cx,LAMBDA(x,TOCOL(IF(_a>0,x,0/0),2)),xm,cx(TOROW(C2:H2,1)),km,cx(fx(B4:B8)),jg,DROP(PIVOTBY(HSTACK(MATCH(xm,C2:H2,),xm),HSTACK(MATCH(km,B4:B8,),km),cx(_a),SUM,0,0,,0,),1,1),HSTACK(VSTACK("姓名",DROP(TAKE(jg,,1),1)),DROP(jg,,1)))

你是那块小饼干

1. =LET(T,HSTACK(VSTACK("姓名",SCAN(0,$B$4:$B$8,LAMBDA(_X,_Y,LOOKUP("座",B4:_Y)))),CHOOSECOLS(VSTACK(SCAN(0,$C$2:$H$2,LAMBDA(X,Y,LOOKUP("座",C2:Y))),C4:H8),2,4,6)),TRANSPOSE(VSTACK(CHOOSEROWS(T,1),GROUPBY(CHOOSECOLS(DROP(T,1),1),CHOOSECOLS(DROP(T,1),2,3,4),SUM,0,0))))

复制代码

leepoison

1. =TRANSPOSE(VSTACK(TOROW(B2:H2,3),GROUPBY(SCAN(,B4:B8,LAMBDA(x,y,IF(y="",x,y))),HSTACK(D4:D8,F4:F8,H4:H8),SUM,0,0)))

复制代码

hxj119

1. =REDUCE(M3:P3,TOCOL(C2:I2,3),LAMBDA(x,y,LET(n,MATCH(y,C2:I2,0),VSTACK(x,HSTACK(y,SUM(OFFSET(C4,,n,2)),SUM(OFFSET(C4,2,n,2)),OFFSET(C4,4,n))))))

复制代码