去重复去空格可以从小到最好

xiaodong112233 · 发表于 2021-3-7 16:45

去重复去空格可以从小到最好高手帮助，谢谢

xiaodong112233 · 发表于 2021-3-7 16:46

去重复去空格可以从小到最好

dim麻辣君 · 发表于 2021-3-7 16:50

本帖最后由 dim麻辣君于 2021-3-7 17:53 编辑

我看错了看错了看错了看错了

cinlo · 发表于 2021-3-7 17:12

本帖最后由 cinlo 于 2021-3-7 17:19 编辑

先上个长的：INDEX(INDEX(A$2:A$13,SMALL(IF(A$2:A$12>0,ROW($1:$11),12),ROW($1:$11)),),MATCH(0,COUNTIF(C$1:C1,(INDEX(A$2:A$13,SMALL(IF(A$2:A$12>0,ROW($1:$11),12),ROW($1:$11)),))),),)&""
365版本可以这样：UNIQUE(FILTER(A2:A12,A2:A12>0))

hopeson2010 · 发表于 2021-3-7 20:34

数组公式下拉:

=IFERROR(INDEX(A:A,RIGHT(SMALL(IF((A$2:A$12<>"")*(MATCH(A$2:A$12&0,A$2:A$12&0,)=ROW($1:$11)),MMULT(--(0&TRIM(MID(SUBSTITUTE(TRIM(A$2:A$12)," ",REPT(" ",20)),{1,21,41,61,81,101},20))),10^{10;8;6;4;2;0})/1%%+ROW($2:$12)),ROW(A1)),4)),"")

复制代码

丫头的 · 发表于 2021-3-7 21:10

=INDEX(A:A,RIGHT(AGGREGATE(15,6,ROW($2:$14)/(MATCH(A$2:A$14,A$2:A$14,)=ROW($1:$13))+MMULT(IFERROR(MID(SUBSTITUTE(A$2:A$14," "," "),6*COLUMN(A:I)-5,6)*10^(10-COLUMN(A:I)),),ROW($1:$9))/1%,ROW(A1)),2))

静听溪流 · 发表于 2021-3-7 21:18

=IFERROR(TEXTJOIN(" ",,--MID(TEXT(SMALL(FILTERXML("<a>"&CONCAT(TEXT(MMULT(--(0&TRIM(MID(SUBSTITUTE(A$2:A$12," ",REPT(" ",9)),COLUMN(A:F)*9-8,9))),10^(12-2*ROW($1:$6))),"<c>0<!/c>;;"))&"</a>","a/c[preceding::*=.=0]"),ROW(A1)),REPT(0,12)),2*ROW($1:$6)-1,2)),"")

李逸雪 · 发表于 2021-3-8 13:27

=INDEX(A:A,RIGHT(MIN(MMULT(IFERROR(MID(SUBSTITUTE(IF(COUNTIF(D$1:D1,A$2:A12),,A$2:A12)," ",REPT(" ",99)),COLUMN(A:F)*99-98,99)*100^{6,5,4,3,2,1},9^99),Z1:Z6+1)+ROW($2:12)),2))&""

复制代码

袁勇 · 发表于 2021-3-8 14:55

=IFERROR(INDEX(A:A,LOOKUP(1,0/FREQUENCY(1%,(COUNTIF(C$1:C1,A$2:A12)=0)*(0&SUBSTITUTE(A$2:A12," ",0))*1),ROW($2:12))),"")

复制代码

		自动登录	找回密码
密码			免费注册

[求助] 去重复去空格可以从小到最好

最新热点 /1