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

我的城市你来过

20240317三级地名匹配
=LET(x,MMULT(--ISNUMBER(FIND(TOROW(MID(SUBSTITUTE($F2,"省",""),ROW($A$1:$A$3)*2-1,2)),$A$2:$C$11)),{100;10;1}),y,CONCAT(UNIQUE(IF(MAX(x)=x,$B$2:$B$11,""))),CONCAT(UNIQUE(IF(LEN(y)>3,IF(ISNUMBER(FIND(LEFT($F2,2),$B$2:$B$11)),$B$2:$B$11,""),y))))

简化=LET(x,MMULT(--ISNUMBER(FIND(LEFT($A$2:$C$11,2),F2))*{100,10,1},{1;1;1}),CONCAT(UNIQUE(IF(MAX(x)=x,$B$2:$B$11,""))))

我的城市你来过

20240320钢筋配比
=INDEX(技术参数!$I$2:$I$16,MATCH(,0/(C3<=-(IMREAL(技术参数!$H$2:$H$16&"i")=B3)*IMAGINARY(技术参数!$H$2:$H$16&"i")),))

=LET(x,FILTER(技术参数!$H$2:$I$16,TEXTBEFORE(技术参数!$H$2:$H$16,"-")/1=$B3),INDEX(CHOOSECOLS(x,2),LOOKUP(C3,TEXTAFTER(x,"-")/1,ROW($A$1:$A$10)+1)))

我的城市你来过

20240322记录二行比大小
=SUM(--(TEXT($B$6:$M$6-$B$7:$M$7,"[>0]1;[<0]2;[0]3")*1=COLUMN(A1)))

我的城市你来过

20240327数据重排

=LET(x,TEXTSPLIT($A2,{"，";" ";"。";"；"}),y,LENB(x),SORTBY(x,y))

=LET(x,TEXTSPLIT($A2,{"，";" ";"。";"；"}),CONCAT(IF(LENB(x)=SMALL(LENB(x),COLUMN(A1)),x,"")))

我的城市你来过

20240328分段截取按列排序
=TOCOL(IF(COUNTIF(INDIRECT("a1:a"&ROW($A$1:$A$1739)),"订单号")=COLUMN(A1),$A:$A,FIND("座",$A:$A)),3)

我的城市你来过

20240401区间排名
=LET(x,FILTER($B$3:$B$13,MMULT(-ISNUMBER(FIND({"小计","合计"},$A$3:$A$13)),{1;1})=0),y,0,IF(SUM(--ISNUMBER(FIND({"小计","合计"},$A3)))>0,"",1+SUM(IFERROR(1*IF(x>B3,y+1,""),0))))

我的城市你来过

20240401单元格转置

A2单元格下拉=LET(x,ROUNDUP(ROW($A1)/COUNTA(原表!$A$2:$A$221),),HSTACK(INDEX(原表!$F$1:$Z$1,,x),OFFSET(原表!$A$2:$D$2,MOD(ROW($A1)-1,COUNTA(原表!$A$2:$A$221)),),OFFSET(原表!F$2,MOD(ROW($A1)-1,COUNTA(原表!$A$2:$A$221)),x-1)))

我的城市你来过

20240402区间比例求和
=LET(x,$I$3-HSTACK(0,SUBTOTAL(9,OFFSET($B$3,,,,COLUMN($A$1:$F$1)))),y,IFNA($B$4:$G$4*IF(x>SMALL(IF(x>=0,x,4^8),1),$B$3:$G$3,x)/$B$3:$G$3,""),SUM(IF(y>0,y,"")))

=LET(x,MAX(IF(SUBTOTAL(9,OFFSET($B$3:$G$3,,,,COLUMN($A$3:$F$3)))<=$I$3,COLUMN($A$1:$F$1),"")),SUM(OFFSET($B$4,,,,x),INDEX($B$4:$G$4/$B$3:$G$3,,x+1)*($I$3-LARGE(SUM(OFFSET($B$3,,,,x)),1))))

我的城市你来过

20240405统计水果品种
=COUNTA(UNIQUE(IF(COUNTIF(IF(ISNUMBER(FIND("中国",$E14)),$C$2:$C$5,$B$2:$B$5),$E$3:$E$9)=1,$F$3:$F$9,"")))-1

我的城市你来过

20240411快递运费计算
=IFERROR(LET(x,WRAPROWS(TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(TEXTJOIN("@",,FILTER(价格表!$B$4:$G$34,价格表!$A$4:$A$34=$D5)),"@","@0@",1),"@","@0@",3),"@"),2),y,INDEX(x,XMATCH($B5,{1,3,10,20},1),),IF($B5<=3,INDEX(y,1),($B5-3)*INDEX(y,,2)+INDEX(y,1))),$B5*15)