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

我的城市你来过

20240228跨表取最大值所在表格城市名
=IFERROR(CHOOSE(ROUNDUP(LET(x,VSTACK(上海:天津!$A$1:$B$100),MAX(IF(IF(A2=x,CHOOSECOLS(x,2),"")=LARGE(IF(A2=x,CHOOSECOLS(x,2),""),1),ROW($A$1:$A$300),"")))/100,0),"上海","北京","天津"),"无")

我的城市你来过

20240228字段匹配

=IFERROR(TEXTSPLIT(TEXTJOIN(" ",,LET(x,ROW(INDIRECT("a1:a"&LEN(A2))),y,MMULT(--ISNUMBER(FIND(TOROW(MID(A2,x,1)),表一!$A$2:$A$86)),x^0),IF(LARGE(y,1)=y,表一!$A$2:$A$86,"")))," "),"")

我的城市你来过

20240303跨表公式求和
=EVALUATE(SUBSTITUTE(TEXTAFTER(TOCOL(IF(FIND(B$2,HSTACK(INDIRECT($A3&"!A:A")&INDIRECT($A3&"!b:b")&INDIRECT($A3&"!c:c")))>0,HSTACK(INDIRECT($A3&"!A:A")&INDIRECT($A3&"!b:b")&INDIRECT($A3&"!c:c"),"")),3),"("),")","*"))


=LET(x,HSTACK(INDIRECT($A3&"!A:A")&INDIRECT($A3&"!b:b")&INDIRECT($A3&"!c:c")),EVALUATE(SUBSTITUTE(TEXTAFTER(TOCOL(IF(FIND(B$2,x)>0,x,""),3),"("),")","*")))

也可以用个LET少点字符，不过WPS用LET的话，发现一个问题，就是关闭表格后，公式会变错误值。

我的城市你来过

20240303化学元素比对
=CONCAT(IF(TEXTSPLIT(CONCAT(IF(ISNUMBER(MID(SUBSTITUTE($C2," ","@"),ROW(INDIRECT("a1:a"&LENB($C2))),1)*1),"",MID(SUBSTITUTE($C2," ","@"),ROW(INDIRECT("a1:a"&LENB($C2))),1))),"@")=F$1,1,""))

我的城市你来过

20240305求和mmult

=SUM(MMULT(--ISNUMBER(FIND({"白菜","土豆"},$A$2:$A$5)),--(MONTH(B$1:E$1)=2)*{1;1})*$B$2:$E$5)

我的城市你来过

20240307文字与数字间加空格


=SUBSTITUTE(SUBSTITUTE(A1,TEXTJOIN("",,IF(ISNUMBER(MID(A1,ROW(INDIRECT("a1:a"&LEN(A1))),1)*1),MID(A1,ROW(INDIRECT("a1:a"&LEN(A1))),1),""))," * "),"*",TEXTJOIN("",,IF(ISNUMBER(MID(A1,ROW(INDIRECT("a1:a"&LEN(A1))),1)*1),MID(A1,ROW(INDIRECT("a1:a"&LEN(A1))),1),"")))

我的城市你来过

20240307随机等量填充名字列

=SORTBY(TOCOL(IF(COLUMN(A:D)<5,TOCOL(E1:J5))),RANDARRAY(COUNTA(E1:J5)*4,,1,1000,1))

=SORTBY(DROP(TOCOL(TEXTSPLIT(CONCAT(REPT("@"&$J$1:$J$7,4)),,"@")),1),RANDARRAY(COUNTA($J$1:$J$7)*4,,1,1000,1))

我的城市你来过

20240308应收账款账期分析
=IF(F4>0,TEXT(OFFSET($K$3,,MATCH(1=0,$K4:$AJ4<>"",)-1-MAX(IF(FREQUENCY(F4,SUBTOTAL(9,OFFSET($K4,,MATCH(1=0,$K4:$AJ4<>"",)-2,,-ROW($A$1:$A$15))))=1,ROW($A$1:$A$15)))),"yyyy年mm月")&"起有应收货款","")

我的城市你来过

20240308多区域汇总求和


=SUM(IFERROR(CHOOSE(MAX(($P$8={"数字","语文","英语"})*{1,2,3}),$E$9:$E$24+$K$9:$K$24,$F$9:$F$24-$I$9:$I$24-$J$9:$J$24,$G$9:$G$24)*IF($C$9:$C$24=$O10,ROW($C$9:$C$24)^0,""),""))

我的城市你来过

20240308数据重复扩展
=INDEX(OFFSET($A$1,1,,SUM(1-ISBLANK($A$2:$A$30))),SUBSTITUTE(MOD(ROW($C$1:$C$33),SUM(1-ISBLANK($A$2:$A$30))),0,8))