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

我的城市你来过

非重复项统计个数：


=SUM(IF(COUNTIF($A$3:$E$8,OFFSET(G2,,,,6))>=1,1,0))三键

重复项：

=SUM(COUNTIF($A$3:$E$8,OFFSET(G2,,,,6)))三键

我的城市你来过

周期内求和：


=SUMPRODUCT((YEAR($C$9:$C$999)*1=YEAR($X9)*(MONTH($C$9:$C$999)*1=MONTH($X9)))*F$9:F$999)

我的城市你来过

多条件求和

=VLOOKUP(OFFSET(F2,,,,2),B2:D6,3,0)

我的城市你来过

2023.01.12区间匹配


=INDEX($A$1:$F$30,MATCH($H2,$A$1:$A$30,0),MIN(IF($I2<={1;2;3;5.5;10.5;9E+307},{2;3;4;5;6;6})))
=OFFSET($A$1,MATCH($H2,$A$2:$A$30,),LOOKUP(I2,{0,1;1.001,2;2.001,3;3.001,4;5.601,5}))
=OFFSET($A$1,MATCH($H2,$A$2:$A$30,),MIN(IF($I2<={1;2;3;5.5;10.5;9E+307},{2;3;4;5;6;6}-1)))

我的城市你来过

2023.01.14提取唯一值


=INDEX(B$6:B$31,MATCH(0,COUNTIF($U$5:U5,B$6:B$31),0))&""

我的城市你来过

2023.01.15金额面值转化
=INT(($A3-SUMPRODUCT($A$2:A$2*$A3:A3))/B$2)

我的城市你来过

2023.01.17关键词前提取数字
=-LOOKUP(1,-RIGHT(LEFT(B2,FIND("室",B2)-1),ROW($1:$10)))
=-LOOKUP(1,-RIGHT(LEFT(B2,FIND("幢",B2)-1),ROW($1:$10)))

我的城市你来过

2023.01.18数字提取方法
=CONCAT(IFERROR(MID(A1,ROW($1:$50),1)*1,""))
=LOOKUP(9^9,FIND(ROW($1:$999),A1),ROW($1:$999))
=MATCH(,-FIND(ROW($1:999),A1))
=MAX(IFERROR(--MID(A1,ROW($1:99),{1,2,3}),))

我的城市你来过

2023.01.19去重提取唯一值处理
=TEXTJOIN(" ",1,IFERROR(UNIQUE(TRANSPOSE(SUBSTITUTE($B2:$N2," ","@")),,1),""))

我的城市你来过

2023.01.19去重分列
B=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(A2),"、","@"),CHAR(10),""),"，","@"),"\","@")
C==INDEX($B$2:$B$283,MATCH(0,COUNTIF($C$1:$C1,B$2:B$283),0))
D==TRIM(MID(SUBSTITUTE(C2,"@",REPT(" ",66)),1,66))
E==TRIM(MID(SUBSTITUTE(SUBSTITUTE(C2,D2&"@",""),"@",REPT(" ",66)),1,66))
F==TRIM(MID(SUBSTITUTE(SUBSTITUTE(C2,D2&"@"&E2,""),"@",REPT(" ",66)),66,66))
G==IF(F2="","",TRIM(MID(SUBSTITUTE(SUBSTITUTE(C2,D2&"@"&E2&"@"&F2,""),"@",REPT(" ",66)),66,66)))
H==IF(G2="","",TRIM(MID(SUBSTITUTE(SUBSTITUTE(C2,D2&"@"&E2&"@"&F2&"@",""),"@",REPT(" ",66)),66,66)))
I==IF(H2="","",TRIM(MID(SUBSTITUTE(SUBSTITUTE(C2,D2&"@"&E2&"@"&F2&"@"&G2&"@"&H2,""),"@",REPT(" ",66)),66,66)))
J==OFFSET($D$1,ROW(D6)/6,MOD(ROW(D6),6)&"")
K==INDEX($J$2:$J$613,MATCH(0,COUNTIF($K$1:K1,$J$2:$J$613),))