Excel实战：提取重复字节段

提取字符

本篇适合：公式基础较强者，主要思路分享。

其他办公软件问题，公众号留言，也会回复。

QQ交流群1：644328490（已满）

QQ交流群2：860692128（新开）

QQ群，可下载表格源文件。

需求：如图-1提取重复字节段

图-1丨提取字节段

关键词：提取字符

---

01 思路分析

初看需求，有点懵：这怎么用公式实现，没有这种公式/函数呀......

没关系，当不能一步到位时，可以试试分步走：

1. 分离出所有字节段；

2. 查找重复；

3. 罗列结果。

如是想到：当搞定第1步，第2步随便用个countifs函数，或高亮重复项，第3步基本操作，就全OK了！

“知道怎么弄了，这波稳了。”

于是，群主信心满满，一波分列操作：

图-2丨分列操作

果然还是不行啊~

这里有两个问题：

1、字节段分离后，横向零散的分布在各单元格，不便于查重；

2、当一个单元格内，字节段很多时，根本无法分列。

如果能像下图，整整齐齐排列，然后高亮重复项，那就简单了。

图-3丨整整齐齐字节段

难点在于，如何整整齐齐的，分离字节段！

02 分离字节段

步骤1：修正空格

源数据中，字节段间存在多个空格隔开的情况，现利用公式，将源数据规律成：每个字节段前后，均只有一个空格。

如图-4，B2处公式：

=CONCATENATE(" ",TRIM(A2)," ")，向下填充。

图-4丨规律的源数据

步骤2：统计字节段数

字节段数=B列数据的空格数-1，C2处公式：

=LEN(B2)-LEN(SUBSTITUTE(B2," ",""))-1，向下填充。

图-5丨字节段数

步骤3：对字节段数，从上到下依次求和

D1=0，D2处公式：

=SUM($C$1:C2)，向下填充。（注意C2不锁定，目的是公式下拉后，求和区域逐格增大）

图-6丨字节段数求和

步骤4：构建整数序列

可以在E2处输入公式：=ROW(),然后下拉；也可以E1输入1，E2输入2，再黑标双击左键。推荐第2种，减少计算量。

图-7丨构建整数序列

步骤5：修正列号

F2处公式：

=COUNTIF($D$1:$D$15,"<"&E2)，向下填充。

该列作用很巧妙，先不讲，往下看自然懂。

图-8丨列号

步骤6：引用列值

G2处公式：

=INDEX($B$2:$B$15,F2)，向下填充。

图-9丨引用列值

6步铺垫，接下来重头戏！

步骤7：替换第n个空格

如图-10，H2处公式：

=SUBSTITUTE(G2," ","闲钓宇哥",COUNTIFS($F$1:F2,F2))，向下填充。

巧妙利用countifs函数，当公式下拉一格，就会替换掉下一个空格。

图-10丨替换第n个空格

同理，替换第n+1个空格，I2处公式：

=SUBSTITUTE(G2," ","闲钓宇哥",COUNTIFS($F$1:F2,F2)+1)，向下填充。

步骤8：确定mid函数的开始位置及字符个数

利用search函数，查找“闲钓宇哥”的位置，也就得到了原本空格的位置。最后配合mid函数，即可分离出字节段。

如图-11，J2处公式：

=SEARCH("闲钓宇哥",H2,COUNTIFS($F$1:F2,F2))，向下填充。

图-11丨mid函数开始位置

K2处公式：

=SEARCH("闲钓宇哥",I2,COUNTIFS($F$1:F2,F2))-J2，向下填充。

公式含义：第n+1个空格位置-第n个空格位置=字节段长度。

步骤9：分离字节段，见证奇迹......

L2处公式：

=TRIM(MID(G2,J2,K2))，向下填充。

关键一步，意外的简单呀~

图-12丨分离字节段

最后，高亮重复项，然后提取重复项+删重，基本操作，略。

---

03 总结

本思路分3步走，重点的第1步又分为9小步，初看还是比较复杂的。

建议同时看看：《Excel实战：逆向还原统计数据》《第n次出现文本的位置》，有助于思路解析。

公众号回复“重复”，即可获取。

感谢关注，表格问题，可在公众号中留言提问。