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

我的城市你来过

2023.04.15模糊查询
=VLOOKUP(MID($E2,1,4)&"*",IF({1,0},$B:$B,A:A),{2,1,-1},0)

我的城市你来过

2023.04.18提取求和业绩
=IFERROR(INDEX(UNIQUE(TRIM(MID(SUBSTITUTE(TEXTJOIN("、",,$A$2:$A$7),"、",REPT(" ",66)),ROW($A$1:$A$10)*66-65,66))),ROW(A1))&"","")
=SUM(IFERROR(IF(FIND(I2,$A$2:$A$8)>0,$B$2:$G$8/(LEN($A$2:$A$8)-LEN(SUBSTITUTE($A$2:$A$8,"、",""))+1),""),""))

我的城市你来过

2023.04.24合并单元格求解
=OFFSET(职称聘聘任原表!$A$1,,LOOKUP(MAX(IF((职称聘聘任原表!$A$6:$A$32=$A2)*(职称聘聘任原表!$B$6:$R$32<>""),COLUMN(职称聘聘任原表!$B$1:$R$1),))-1,LOOKUP(COLUMN(职称聘聘任原表!$A$1:$R$1),MATCH(职称聘聘任原表!$B$1:$R$1,职称聘聘任原表!$B$1:$R$1,))))
=CONCAT(IF((职称聘聘任原表!$A$6:$A$32=$A2)*(职称聘聘任原表!$B$6:$R$32<>""),职称聘聘任原表!$B$5:$R$5,""))
=CONCAT(IF((职称聘聘任原表!$A$6:$A$32=$A2)*(职称聘聘任原表!$B$6:$R$32<>""),职称聘聘任原表!$B$6:$R$32,""))
=DATEDIF(TEXT(D2,"0000-00-00"),TODAY(),"y")

我的城市你来过

2023.04.29筛选求和
=SUM(IFERROR(MID($A$11:$A$14,1,FIND("合",$A$11:$A$14)-1)*SUBTOTAL(103,OFFSET($A$11,ROW($A$1:$A$4)-1,)),0))
103隐藏，3不隐藏

我的城市你来过

2023.05.06数字递归
=MID(CONCAT(IFERROR(REPT(ROW($A$1:$A$5),H$1-ROW($A$1:$A$5)),"")),ROW(A1),1)

橒♂蝣

看蒙了，没想到有这么多方法

我的城市你来过

2023.05.11按条件排序筛选
=IFERROR(INDEX(IF(($D$3:$D$14)<=$G$2,$C$3:$C$14,""),MIN(IF(SMALL(IF($D$3:$D$14<=$G$2,$D$3:$D$14,4^8),ROW(A1))=$D$3:$D$14,ROW($A$3:$A$14)-2,15))),"")

我的城市你来过

2023.06.08转置内容
=MID(CONCAT(REPT($A$2:$A$6,1+LEN($B$2:$B$6)-LEN(SUBSTITUTE($B$2:$B$6,"、","")))),ROW(A1)*3-2,3)
=TRIM(MID(SUBSTITUTE(CONCAT(IF($A$2:$A$6=C2,$B$2:$B$6,"")),"、",REPT(" ",66)),COUNTIF(C$2:C2,C2)*66-65,66))

我的城市你来过

2023.08.09内容填充
=TRIM(TEXTJOIN(" ",,IF(LOOKUP(ROW($A$1:$A$25),MATCH($B$3:$B$27,$B$3:$B$27),$B$3:$B$27)=E3,TEXT($C$3:$C$27,"yyyy-mm-dd"),"")))

我的城市你来过

202401001应收账格式

=TEXT(OFFSET($A3,,SMALL(IF($H3:$V3=$D3,COLUMN($H$3:$V$3)-4),COUNT(IF($H3:$V3=$D3,COLUMN($H$3:$V$3))))-1),"yyyy-mm-dd")&"至"&TEXT($R3,"yyyy-mm-dd")

=MAX(IF(($H$2:$V$2="未收")*($H3:$V3>0),$H3:$V3,0))