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

我的城市你来过

2023.03.01提取百分数
=SUBSTITUTE(A1,-LOOKUP(0,-RIGHT(LEFT(A1&"0%",FIND("#",SUBSTITUTE(A1&"0%","%","#",COLUMN(A1)))-1),COLUMN($A$1:$E$1)))&"%","")

我的城市你来过

2023.03.04提取数字
=-LOOKUP(,-MID($A1,MATCH(,0/MID($A1,ROW($A$1:$A$9),1),),ROW($A$1:$A$9)))
=TRIM(MID(SUBSTITUTE($A2,"*",REPT(" ",66)),COLUMN(A1)*66,66))

我的城市你来过

2023.03.10单行变多行

=MID(CONCAT(REPT($A$2:$A$3,LEN($B$2:$B$3)-LEN(SUBSTITUTE($B$2:$B$3,"，",""))+1)),(ROW(A1)-1)*12+1,12)
=TRIM(MID(SUBSTITUTE(TEXTJOIN("，",,$B$2:$B$3),"，",REPT(" ",66)),ROW(A1)*66-65,66))
=-LOOKUP(,-MID(K2,ROW($A$1:$A$9),ROW($A$1:$A$9)))
=VLOOKUP($J2,$A:$H,COLUMN(D$1:D$1),0)

我的城市你来过

2023.03.13扩行方法
F1=TRIM(MID(SUBSTITUTE(CONCAT($B$1:$B$6&REPT(",",$A$1:$A$6+1)),",",REPT(" ",66)),ROW(A1)*66-65,66))
E1=MID(CONCAT(REPT($B$1:$B$6,$A$1:$A$6+1)),ROW(A1)*10-9,10)

我的城市你来过

2023.03.18去重+choose使用

=IFERROR(INDEX($A$3:$F$3,,SMALL(IF((MATCH($A$3:$F$3,$A$3:$F$3,)=COLUMN($A$3:$F$3))*($A$3:$F$3>=3)>0,COLUMN($A$1:$F$1),4^8),COLUMN(A1))),"")

=CHOOSE(SUM(1*(COUNTIF(OFFSET($B8,ROW($A$1),COLUMN(A1)*3-2,,3),$A9)>={0,1,2,3})),"0","1","1","对")

我的城市你来过

2023.03.19保留数字间空格
=IF(ISNUMBER(MID(A1,MAX(IFERROR(UNIQUE(SEARCH(" ",A1,ROW($A$1:$A$33)),,),0))-1,1)*1),SUBSTITUTE(A1," ","",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1),SUBSTITUTE(A1," ",""))

我的城市你来过

2023.03.21部分匹配条件求和
=SUM(IFERROR(IFNA(MATCH($G$2:$G$9,$J$1:$J$5,),)^0*($E$2:$E$9=A2),0)*$F$2:$F$9)

我的城市你来过

2023.03.22错位多列条件求和

=SUMPRODUCT((MONTH($B$2:$H$128)=J2)*($B$2:$H$128<>"")*$A$2:$G$128)

我的城市你来过

2023.03.23多列去重合并
=IFERROR(INDIRECT(TEXT(RIGHT(SMALL(IF(--($A$1:$B$8<>"")-(COUNTIF(G$1:G1,$A$1:$B$8))>0,ROW($A$1:$B$8)/1%+COLUMN($A$1:$B$1)*10001),ROW($A$1)),4)*1,"R0C00"),),"")

我的城市你来过

2023.03.23连续区域断点起始值
=INDEX(E2:I2,MATCH(1,F2:I2-E2:H2,))
=MAX(FREQUENCY(IF($E2:$I2<>"",COLUMN($A$1:$E$1),""),IF($E2:$I2="",COLUMN($A$1:$E$1),"")))