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

我的城市你来过

20240221新函数let练习

=IFERROR(LET(x,TEXTSPLIT(TEXTJOIN(" ",,IF(COUNTIFS(B5:F5,B5:F5)>1,B5:F5,""))," ")/1,IFS(SUM(IF(x<5,1,0))=COUNT(x),3,SUM(IF(x<5,1,0))=0,4,SUM(IF(x<5,1,0))<COUNT(x),1)),0)

我的城市你来过

20240221新函数choosecols练习
=SUM(TEXTSPLIT(TEXTJOIN(" ",,IF(MONTH($B$4:$B$63)=MONTH($P$2),CHOOSECOLS($C$4:$N$63,MATCH($P4,$C$2:$N$2,)+2),""))," ")/1)

=SUM(CHOOSECOLS($C:$N,MATCH($P4,$C$2:$N$2,)+2))

我的城市你来过

20240221合并单元格订单数求和




=SUM(IFERROR(LET(x,UNIQUE(HSTACK($B$2:$B$48,$C$2:$C$48)),IF(x=D2,CHOOSECOLS(x,2),"")/1),0))

一步==SUM($C$2:$C$48/COUNTIFS($B$2:$B$48,$B$2:$B$48,$C$2:$C$48,$C$2:$C$48))

我的城市你来过

20240222关键词匹配检索
=CHOOSEROWS(WRAPROWS(TEXTSPLIT(TEXTJOIN(" ",,IF(ISNUMBER(FIND($F$2,$B$3:$B$39)),$B$3:$C$39,""))," "),2,""),ROW(A1))

我的城市你来过

20240222合并单元格分推计算
＝(B2/SUM(LET(x,LOOKUP(ROW($A$2:$A$9),IF($A$2:$A$9<>0,ROW($A$2:$A$9))),OFFSET($B$1,INDEX(x-1,ROW(A1)),,SUM(IF(CHOOSEROWS(x,ROW(A1))=x,1,0))))))*(1/LOOKUP(9,1/C$2:C2))

=INDEX(C:C,CHOOSEROWS(IF($A$2:$A$9<>0,ROW($A$2:$A$9),""),ROW(A1)))*$B2/SUM(OFFSET(B$1,INDEX(LOOKUP(ROW($A$2:$A$9),IF($A$2:$A$9<>0,ROW($A$2:$A$9)))-1,ROW(A1)),,LET(x,LOOKUP(ROW($A$2:$A$9),IF($A$2:$A$9<>0,ROW($A$2:$A$9))),SUM(IF(CHOOSEROWS(x,ROW(A1))=x,1,0)))))

橒♂蝣

这么复杂还不好上字典喽

我的城市你来过

20240223多条件排序匹配


=LET(x,MMULT(IF(HSTACK(INT(SUBTOTAL({5,4},OFFSET($A$1:$C$1,ROW($A$1:$A$268),))),ROUNDDOWN($E$2:$E$269,1))=HSTACK(LEFT($K2,INT(LEN($K2)/2))/1,RIGHT($K2,LEN($K2)-INT(LEN($K2)/2))/1,$L2),1,0),ROW($A$1:$A$3)^0),SUM(IF(x=3,1,0)))

=SUM(IF(LET(x,INT(SUBTOTAL({5,4},OFFSET($A$1:$C$1,ROW($A$1:$A$268),))),CHOOSECOLS(x,1)&CHOOSECOLS(x,2)&ROUNDDOWN($E$2:$E$269,1))=CONCAT(K2:L2),1,0))

我的城市你来过

20240223阶梯计提
=SUM(TEXT(C8-B8*{0,0.8,1},"[<]!0")*{2,1,1.5}%)

我的城市你来过

20240223随机顺三数组合

=WRAPROWS(MID(CONCAT(TOCOL(UNIQUE(RANDARRAY(8888,4,0,2,1)))),ROW($A$1:$A$81)*4-3,4),9)

不顺数
=LET(x,{0,5,7},TOCOL(TOCOL(TOCOL(x)&x)&x)&x)

=LET(x,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(CONCAT(TOCOL(UNIQUE(RANDARRAY(188888,4,0,7,1)))),ROW($A$1:$A$9999)*4-3,4),1,""),2,""),3,""),4,""),6,""),8,""),9,""),WRAPROWS(TEXTSPLIT(TEXTJOIN(" ",,IF(LEN(x)=4,x,""),""),," "),9))

=LET(x,MID(CONCAT(TOCOL(UNIQUE(RANDARRAY(188888,4,0,7,1)))),ROW($A$1:$A$9999)*4-3,4),WRAPROWS(TEXTSPLIT(TEXTJOIN(" ",,IF(LENB(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(x,0,"-0"),5,"-5"),7,"-7"))=8,x,""),""),," "),9))

我的城市你来过

20240224统计各列非空单元格数量
=MAX(SUBTOTAL(3,OFFSET($B$2:$B$25,,{0,1,3}))-1)