【函数小题】字符串去重&压缩&相同再拥抱[12楼抄袭演变供参考]

LWQ张无忌

象山海鲜 发表于 2020-6-10 14:28
加条件，用字符堆

第一题，优化
=CONCAT(MID(A3,SMALL(IF(MID(A3,ROW($1:11),1)<>MID(A3,ROW($2:12),1),FIND(MID(A3,ROW($1:11),1),A3),99),ROW($1:11)),1))

LWQ张无忌

象山海鲜 发表于 2020-6-10 14:28
加条件，用字符堆

第一题，优化
=CONCAT(MID(A3,SMALL(IF(MID(A3,ROW($1:11),1)<>MID(A3,ROW($2:12),1),FIND(MID(A3,ROW($1:11),1),A3),99),ROW($1:11)),1))
第二题，优化
=CONCAT(MID(CONCAT(MID(A3,ROW($1:12),1)&FREQUENCY(ROW($1:11),(MID(A3,ROW($1:11),1)<>MID(A3,ROW($2:12),1))*ROW($1:11))),MOD(SMALL(IF(MID(A3,ROW($1:11),1)<>MID(A3,ROW($2:12),1),(FIND(MID(A3,ROW($1:11),1),A3)/1%+ROW($1:11))*2-1,9^9),ROW($1:11)),100),2))

=CONCAT(IFERROR(LOOKUP(MOD(SMALL(IF(MID(A3,ROW($1:12),1)<>MID(A3,ROW($2:13),1),FIND(MID(A3,ROW($1:12),1),A3)/1%+ROW($1:11)),ROW($1:11)),100),ROW($1:11),MID(A3,ROW($1:12),1)&FREQUENCY(ROW($1:11),(MID(A3,ROW($1:11),1)<>MID(A3,ROW($2:12),1))*ROW($1:11))),""))

优化几个
=CONCAT(SUBSTITUTE(MID(CONCAT(IFERROR(MID(A3,ROW($1:11),1)&1/FREQUENCY(ROW($1:11),(MID(A3,ROW($1:11),1)<>MID(A3,ROW($2:12),1))*ROW($1:11))^-1,"鲜牌")),RIGHT(SMALL(FIND(MID(A3,ROW($1:11),1),A3)/1%+ROW($1:11),ROW($1:11)),2)*2-1,2),"鲜牌",))

Wps,搞定了，加个列号才可。
=CONCAT(INDEX(IFERROR(MID(A3,ROW($1:11),1)&1/FREQUENCY(ROW($1:11),(MID(A3,ROW($1:11),1)<>MID(A3,ROW($2:12),1))*ROW($1:11))^-1,""),N(IF(1,MOD(SMALL(FIND(MID(A3,ROW($1:11),1),A3)/1%+ROW($1:11),ROW($1:11)),100))),1))

=CONCAT(IFERROR(LOOKUP(MOD(SMALL(FIND(MID(A3,ROW($1:11),1),A3)/1%+ROW($1:11),ROW($1:11)),100),ROW($1:11),MID(A3,ROW($1:11),1)&1/FREQUENCY(ROW($1:11),(MID(A3,ROW($1:11),1)<>MID(A3,ROW($2:12),1))*ROW($1:11))^-1),""))

静听溪流

=CONCAT(MID(BASE(TEXT(DECIMAL(SORT(1&TEXT(FIND(MID(A3,ROW($1:$11),1),A3),"00")&ROW($11:$21)&MID(A3,ROW($1:$11),1)&TEXT(FREQUENCY(ROW($1:$11),(MID(A3,ROW($1:$10),1)<>MID(A3,ROW($2:$11),1))*ROW($1:$10)),"0;;")),36),"[>3E8]0;!1"),36),6,3))

cutecpu

=CONCAT(IFERROR(INDEX(SORT(IF({1,0},MID(A3,ROW($1:11),1)&1/FREQUENCY(ROW($1:11),(MID(A3,ROW($1:11),1)<>MID(A3,ROW($2:12),1))*ROW($1:10))^-1,FIND(MID(A3,ROW($1:11),1),A3)),2),N(IF(1,ROW($1:11)))),""))

祝順心，南無阿彌陀佛！

LWQ张无忌

cutecpu 发表于 2020-6-13 00:55
=CONCAT(IFERROR(INDEX(SORT(IF({1,0},MID(A3,ROW($1:11),1)&1/FREQUENCY(ROW($1:11),(MID(A3,ROW($1:11),1 ...

WPS，你们很多函数我没有，看不见

LWQ张无忌

cutecpu 发表于 2020-6-13 00:55
=CONCAT(IFERROR(INDEX(SORT(IF({1,0},MID(A3,ROW($1:11),1)&1/FREQUENCY(ROW($1:11),(MID(A3,ROW($1:11),1 ...

=CONCAT(INDEX(IFERROR(MID(A4,ROW($1:11),1)&1/FREQUENCY(ROW($1:11),(MID(A4,ROW($1:11),1)<>MID(A4,ROW($2:12),1))*ROW($1:11))^-1,""),N(IF({1},MOD(SMALL(FIND(MID(A4,ROW($1:11),1),A4)/1%+ROW(1:11),ROW(1:11)),100)))))
大师，这个为什么提不到？
下面这样F9看到空的，铺在单元格有看到，CONCAT连接又看不到？
=INDEX(IFERROR(MID(A4,ROW($1:11),1)&1/FREQUENCY(ROW($1:11),(MID(A4,ROW($1:11),1)<>MID(A4,ROW($2:12),1))*ROW($1:11))^-1,""),N(IF({1},MOD(SMALL(FIND(MID(A4,ROW($1:11),1),A4)/1%+ROW(1:11),ROW(1:11)),100))))

cutecpu

LWQ张无忌 发表于 2020-6-13 13:36
=CONCAT(INDEX(IFERROR(MID(A4,ROW($1:11),1)&1/FREQUENCY(ROW($1:11),(MID(A4,ROW($1:11),1)MID(A4,ROW( ...

无忌老师您好，我把您的公式放在 office365上测试正常。

祝順心，南無阿彌陀佛！

LWQ张无忌

cutecpu 发表于 2020-6-13 14:11
无忌老师您好，我把您的公式放在 office365上测试正常。

祝順心，南無阿彌陀佛！

Wps,搞定了，加个列号才可以。
=CONCAT(INDEX(IFERROR(MID(A3,ROW($1:11),1)&1/FREQUENCY(ROW($1:11),(MID(A3,ROW($1:11),1)<>MID(A3,ROW($2:12),1))*ROW($1:11))^-1,""),N(IF(1,MOD(SMALL(FIND(MID(A3,ROW($1:11),1),A3)/1%+ROW($1:11),ROW($1:11)),100))),1))


还发现有趣现象，没有加N(IF())的时候，同样有加列1，放在一个单元格值显示第一个，若铺在多个单元格，可以显示正确的，太奇怪。
=CONCAT(INDEX(IFERROR(MID(A4,ROW($1:11),1)&1/FREQUENCY(ROW($1:11),(MID(A4,ROW($1:11),1)<>MID(A4,ROW($2:12),1))*ROW($1:11))^-1,""),MOD(SMALL(FIND(MID(A4,ROW($1:11),1),A4)/1%+ROW(1:11),ROW(1:11)),100),1))