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

橒♂蝣

你举这个例子很规啊

我的城市你来过

20240121创建工作表目录链接

=HYPERLINK("#"&"成绩总表"&"!A1","成绩总表")

=HYPERLINK("#"&"目录表"&"!A1","HOME")

我的城市你来过

20240121单元格数据按频率高低重新排序


=CONCAT(UNIQUE(SORTBY(MID(A1,ROW(INDIRECT("a1:a"&LEN(A1))),1),LEN(A1)-LEN(SUBSTITUTE(A1,MID(A1,ROW(INDIRECT("a1:a"&LEN(A1))),1),"")),-1)))

我的城市你来过

20240203提取手机号、固话
手机号=TEXTJOIN("、",,IFERROR(IF(LENB(LARGE(UNIQUE(IFERROR(MID($A2,IFERROR(FIND(1,$A2,ROW($A$1:$A$99)),99),11)*1,"")),ROW($A$1:$A$3)))=11,LARGE(UNIQUE(IFERROR(MID($A2,IFERROR(FIND(1,$A2,ROW($A$1:$A$99)),99),11)*1,"")),ROW($A$1:$A$3)),""),""))
固话
=IF(OR(LENB($B2)=11,$B2=""),CONCAT(IFERROR(--MID(SUBSTITUTE($A2,$B2,""),ROW($A$1:$A$99),1),"")),"")

我的城市你来过

20240203记录choose用法
CHOOSE(COUNT(UNIQUE(TRIM(MID(TEXTJOIN(REPT(" ",99),,A1,G1,M1),ROW($A$1:$A$3)*99-98,99))*1)/MAX(A1,G1,M1)),5,4,MATCH(MAX(A1,G1,M1),CHOOSE({1,2,3},A1,G1,M1),))

我的城市你来过

20240215连号统计

=TEXTJOIN("+",,LEN(LARGE(TEXTSPLIT(TRIM(SUBSTITUTE(CONCAT(SUBSTITUTE(FREQUENCY(TEXTSPLIT(A1," ")*1,ROW($A$1:$A$99)),"0",",")),","," "))," ")/1,ROW($A$1:$A$2))))

我的城市你来过

20240215权重排名

=SUM((N(MMULT((($C4:$K4="√")+LEFT($L4:$L4,4))/1,TEXTSPLIT(TEXTJOIN(" ",,MMULT({1;10;100},{3,2,1})),," ")*1)<MMULT((($C$4:$K$17="√")+LEFT($L$4:$L$17,4))/1,TEXTSPLIT(TEXTJOIN(" ",,MMULT({1;10;100},{3,2,1})),," ")*1))))+1

注：=TEXTSPLIT(TEXTJOIN(" ",,MMULT({1;10;100},{3,2,1})),," ")*1={3;2;1;30;20;10;300;200;100}
      “<”单对多，确定排名位置

我的城市你来过

20240215IP统一格式方法
=TEXTJOIN(".",,TEXT(TEXTSPLIT(C6,".",),"000"))

我的城市你来过

20240216跨表动态查询数据
=IF(COLUMN(A$1)=1,OFFSET(INDIRECT($B$3&"!$A$4"),ROW($A1),)&"",VLOOKUP($A5,VSTACK('1班:4班'!$A$4:$G$20),CHOOSE(LEFT($B$3,1),6,4,5,7),))

我的城市你来过

20240216汇总多表数据
=IFERROR(TRIM(MID(TEXTJOIN(REPT(" ",66),,IF(上级考核!$B$3:$B$12=LOOKUP("座",$B$3:$B3),上级考核!C$3:C$12,""),IF(自查考核!$B$3:$B$8=LOOKUP("座",$B$3:$B3),CHOOSE(MOD(COLUMN(A:A),4),自查考核!C$3:C$8&"(自查)",自查考核!$D$3:$D$8,自查考核!$E$3:$E$8),"")),MOD(ROW(A1),5)*66-65,66)),"")