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

我的城市你来过

2023.01.21时间交集排班人数


网友公式=MAX(COUNTIFS(B2:B19,"<="&ROW(INDIRECT(C22&":"&C23)),C2:C19,">="&ROW(INDIRECT(C22&":"&C23))))

我的城市你来过

2022.01.25文本提取数字求和

=SUMPRODUCT(IFERROR(MID(TEXT(MID(SUBSTITUTE(SUBSTITUTE(TRIM(A3),"、","，"),"，",REPT(" ",9)),ROW($1:$999),9),),2,9)/1,))

我的城市你来过

2022.01.25计算结果返回地址






=IF($B3>=C$2,"",SUM(C$2,$B3)*1.05)
=LARGE(IF($C$3:$L$12<9000,$C$3:$L$12,""),COLUMN(A1))
=TEXTJOIN("/",,OFFSET($A$1,SUMPRODUCT(($A$1:$L$12=B16)*ROW($1:$12))-1,),OFFSET($A$1,,SUMPRODUCT(($A$1:$L$12=B16)*COLUMN($A:$L))-1,))

我的城市你来过

2023.01.28合并单元格引用计算
=SUMIFS(雷!G:G,雷!E:E,INDEX(A:A,MAX(IF($A$1:$A1<>"",ROW($A$1:$A1)))),雷!F:F,B1)
=SUMIFS(雷!G:G,雷!E:E,LOOKUP("座",$A$1:$A1),雷!F:F,B1)

我的城市你来过

2023.01.29连续性统计

连续次数=COUNT(0/(FREQUENCY(ROW(E15:E45),IF((A15:A45*1=431)*(E15:E45>35),,ROW(E15:E45)))>3))
连续天数=MIN(FREQUENCY(IF((A15:A45*1=431)*(E15:E45>35)<>0,E15:E45,0),35))
开始日期=OFFSET(D$14,MATCH(,0/(A15:A45*1=431)/(E15:E45>35),),)
结束日期=OFFSET(D$14,MAX(MATCH(9E+307,0/(A15:A45*1=431)/(E15:E45>35)+ROW(E15:E45),1)),)
平均成绩=AVERAGE(IF((A15:A45*1=431)*(D16:D46-D15:D45=1)*(E15:E45>35)<>0,E15:E45))

我的城市你来过

2023.01.30多条件求和
=SUMPRODUCT((R$2&R$3&R$4=$C$2:$N$2&$C$3:$N$3&$C$4:$N$4)*($A$5:$A$14&$B$5:$B$14=$P5&$Q5)*$C$5:$N$14)

我的城市你来过

2023.02.18提取单元格文字
=IFERROR(LEFT(SUBSTITUTE(C2,CHAR(10),"、"),FIND("、",SUBSTITUTE(C2,CHAR(10),"、"))-1),"")

我的城市你来过

2023.02.18条件提取文字
=SUBSTITUTE(TRIM(SUBSTITUTE(CONCAT(IF($C$3:$C$8=K3,$B$3:$B$8&$D$3:$D$8,"、")),"、"," "))," ",CHAR(10))
=SUMIF(C:C,K3,E:E)
=UNIQUE(C3:C8,1)

我的城市你来过

2023.02.24多重替换
=REPLACE(REPLACE(REPLACE($A$2,FIND(MID(CONCAT(IFERROR(--MID($A$2,ROW($A$1:$A$99),1),"")),8,4),$A$2)-4,4,REPT("*",4)),FIND(MID(CONCAT(IFERROR(--MID($A$2,ROW($A$1:$A$99),1),"")),19,4),$A$2)-4,4,REPT("*",4)),FIND(MID(CONCAT(IFERROR(--MID($A$2,ROW($A$1:$A$99),1),"")),30,4),$A$2)-4,4,REPT("*",4))

我的城市你来过

2023.02.25计奖匹配

=IFNA(IF(G3=$P$3,IFS(SUM(COUNTIFS(A3:F3,$J$3:$O$3))<=2,5,SUM(COUNTIFS(A3:F3,$J$3:$O$3))=3,10,SUM(COUNTIFS(A3:F3,$J$3:$O$3))=4,200,SUM(COUNTIFS(A3:F3,$J$3:$O$3))=5,3000,SUM(COUNTIFS(A3:F3,$J$3:$O$3))=6,100%),IFS(SUM(COUNTIFS(A3:F3,$J$3:$O$3))=4,10,SUM(COUNTIFS(A3:F3,$J$3:$O$3))=5,200,SUM(COUNTIFS(A3:F3,$J$3:$O$3))=6,25%)),"欢迎光临")