相同姓名的求平均分

我的城市你来过

=IFERROR(INDEX(UNIQUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(" ",,$B$3:$B$63,$D$3:$D$58)," ",REPT(" ",299)),ROW($A$1:$A$299)*299-298,299))),ROW(A1))&"","")

=SUM(IF($B$3:$D$63=$G3,$C$3:$E$63,""))/COUNT(IF($B$3:$D$63=$G3,$C$3:$E$63,""))

zxn1129

我的城市你来过 发表于 2023-4-23 10:32
=IFERROR(INDEX(UNIQUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(" ",,$B$3:$B$63,$D$3:$D$58)," ",REPT(" ",299)),RO ...

感谢

pgp5866

=DROP(REDUCE(0,B3:B63,LAMBDA(x,y,IF(y=OFFSET(y,,2),VSTACK(x,HSTACK(y,AVERAGE(OFFSET(y,,1),OFFSET(y,,3)))),x))),1)

zxn1129

我的城市你来过 发表于 2023-4-23 10:32
=IFERROR(INDEX(UNIQUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(" ",,$B$3:$B$63,$D$3:$D$58)," ",REPT(" ",299)),RO ...

感谢，十分感谢

jisijie630

G3:
=INDIRECT(TEXT(MIN(IF(COUNTIF(G$2:G2,IF({1,0},$B$3:$B$63,$D$3:$D$63)),9999,($A$3:$A$63+2)/1%+{2,4})),"r0c00"),)&""

H3:
=IFERROR(AVERAGEIF(B:D,G3,C:E),"")

zxn1129

ifan0916 发表于 2023-4-21 17:06
同一学期有重名 按一个人处理？

是的