求一行中最后10个非空值的平均值的公式

我的城市你来过

=LET(x,COUNT(TOCOL($G$3:$G$25,1)),y,10,AVERAGE(CHOOSECOLS(WRAPCOLS(TEXTSPLIT(TEXTJOIN(" ",,TEXTSPLIT(TEXTJOIN("-",,REPT("0-",y-MOD(x,y))),,"-"),TOCOL($G$3:$G$25,1)),," "),y),INT(x/y)+1)/1))

=LET(x,COUNT($G$3:$G$25),y,10,SUM(MID(REPT(0,(x/y-1)*y)&REPT(1,y),ROW(INDIRECT("a1:a"&x)),1)*TOCOL($G$3:$G$25,3))/y)

=LET(x,COUNT($G$3:$G$25),y,ROW($A$1:$A$10),AVERAGE(LARGE((ROW(INDIRECT("$A$1:$A"&x))/1%&TOCOL($G$3:$G$25,3))*1,y)-SORT(ROW(INDIRECT("a"&x&":a"&x-9)),,-1)*1000))


=LET(x,TOCOL($G$3:$G$25,3),y,10,(SUM(x)-SUM(INDEX(x,ROW(INDIRECT("A1:A"&(COUNT(x)/y-1)*y)))))/y)

招蜂引蝶

sandays 发表于 2024-2-19 21:42

这个方法我试过了是最简便最实用的。