求解使用EXCEL统计出地址中的高频词（E高手请进）

我的城市你来过

20240224倒数N个非空单位格平均数

=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)

我的城市你来过

20240224时间判断


=LET(y,CONCAT(IF(LET(x,HOUR(TEXT(TRIM(MID(A1,FIND(" ",A1)+3,66)),"hh:mm:ss")),IF((x<12)*(IFERROR(FIND("下",A1),0)),x+13,x+1))=ROW($A$8:$A$20),"白","")),IF(LEN(y)=0,"晚","白"))

我的城市你来过

20240224内插占比计算
=LET(x,OFFSET($A$1:$A$12,,MATCH(FLOOR(MOD(A15,1000),100),$B$1:$K$1),,2),y,CHOOSECOLS(x,2)-CHOOSECOLS(x,1),MIN(INDEX(x+y*MOD(A15,100)/100,MATCH(FLOOR(A15,1000),$A$1:$A$12,))))

我的城市你来过

20240224字段匹配度查询
=TEXTSPLIT(TEXTJOIN(" ",,LET(x,ROW(INDIRECT("a1:a"&LEN(F2))),y,MMULT(--ISNUMBER(FIND(TOROW(MID(F2,x,1)),$A$2:$A$49)),x^0),IF(LARGE(y,1)=y,$A$2:$B$49,"")))," ")

我的城市你来过

20240225学生成绩排名
=LET(y,FILTER($J$3:$Q$48,$Q$3:$Q$48>=LARGE($Q$3:$Q$48,24)),CHOOSECOLS(SORTBY(y,CHOOSECOLS(y,8),-1),{1,5,8}))

我的城市你来过

20240225工期时间按排
=IF($C3="","",TEXT(MIN($E3,I$1)-MAX($D3,H$1),"0;!0"))

=SUBSTITUTE(TEXTSPLIT(TEXTJOIN("-",,IFERROR(LET(x,IF(ISNUMBER(FIND(LEFT(TEXT($D4,"yyyymmdd"),4),$I$2:$N$2)),$I$1:$N$1-$D4,""),x&"-"&IF($C4-x<365,$C4-x,REPT("365-",INT(($C4-x)/365))&MOD(($C4-x),365)-1)),"@")),"-"),"@","")

我的城市你来过

20240225后几列斜数连0统计


=LET(x,TRANSPOSE(SUBTOTAL(5,OFFSET($M$1,ROW($A1:$A$11),1-ROW($A$1:$A$11)))),CHOOSECOLS(LEN(TEXTSPLIT(CONCAT(IFNA(SUBSTITUTE(SUBSTITUTE(x,1,"@"),0,1),"@")),"@")),1))

我的城市你来过

20240225途经路线排班
起始路1=TRIM(TEXTSPLIT(TEXTJOIN(" ",,REPT($C$2:$C$17&"-",COUNTIFS($A$2:$A$17,$B$2:$B$17))),,"-"))
后续路线=LET(x,TRIM(TEXTSPLIT(TEXTJOIN("-",,IF(MAX(IF($C$2:$C$17=E6,$B$2:$B$17,""))=$A$2:$A$17,$C$2:$C$17,"")),"-")),IFERROR(INDEX(x,MIN(COUNTA(x),COUNTIF($E$5:$E6,$E6))),""))

我的城市你来过

20240226查询订单合并求和及数据显示
=CHOOSEROWS(LET(x,SORT(IF(MMULT(--ISNUMBER(FIND(TEXTSPLIT($A3,"/"),$E$9:$E$16)),ROW(INDIRECT("A$1:A"&LENB($A3)+1-LENB(SUBSTITUTE($A3,"/",""))))^0)=1,F$9:F$16,"")),IF(ISNUMBER(x),MAX(SUM(x)),TEXTJOIN("/",,x))),1)

我的城市你来过

20240228动态区域统计
=SUM(COUNTIFS(INDIRECT("B3:B"&COUNTIF(B:B,"<>")+2),INDIRECT("d3:d"&COUNTIF(D:D,"<>")+2)))