Excel 分列处理复杂数据技巧分享

原文标题：《天天都在用的 Excel 分列，还藏着这个你不知道的神技？！》

大家好，我是农夫，专治疑难杂「数」的农夫~

昨天同事突然扔给了我这样一个数据~

让我把它分成下面这样的四列~

这还能难倒我这数据小能手吗？

废话不多说，盘它~

拿到这样的数据，大部分小伙伴脑袋里面率先想到的肯定是 Excel 的【分列】功能。

但是，实际操作发现，无论是使用【分隔符号】还是【固定宽度】都无能为力呀！

于是~

咳咳！说回正题，在正式处理这个数据之前，我们先来分析下当前数据有什么特征。

数据分析

我们观察一下数据，发现有以下几个特征：

首先，第一列红色列全都是数字，第二列蓝色列全都是文字。

Excel 中的分列功能需要一个标识符，比如空格。

那么，我们只要把数字和文字中间，插入一个标识符就可以分列了嘛！

其次，第二列蓝色列和第三列红色列和前面的特征一样，只不过是文字和数字换了位置；

最后，我们来看第三列红色列和第四列绿色列。我们发现两列都是数字。

但是，仔细分析就会发现第四列 -- 绿色列都存在一个小数点，而小数点前面都是一位数字。

这样，我们不就可以，将小数点加前面的一位数字进行特征识别了吗？

好了，既然思路都有了，那么，接下来我们要怎么做呢？

数据处理

对于数据处理，我们可以使用 Word 通配符来帮忙。

比如：你要检索「农」字开头的单词，写入【农 [一-龟]{1,}】，就可以匹配到农夫、农夫好帅、农民、农村、农业、农业经济、农村合作社、农田；

而如果写入【农 *】，只会搜到农字这个单字。

这其实就是利用正则表达式实现的。

然而，正则表达式又是啥？

就相当于通配符的升华。

通俗理解是文本数据的超级匹配模式，用更简洁的方法来指定所需的文本。类似于一对多模式。

其中，一就是由元字符组成的通用模式；多则是文本中符合该模式的文本数据。

就好比：水果（你写的正则表达式）包括苹果、梨、香蕉、车厘子、柚子……（匹配上的文本数据。）

既然是处理文本数据，那我们就不能仅局限在 Excel 了，毕竟 Office 三剑客都各有各的看家本领。

其中，Word 在文本领域可是三剑客中的扛把子呀！

❶ Word 数据处理

① 先将数据粘贴进入 Word 中，操作过程中选择「选择性粘贴」-【只保留文本】。

② 序号与部门之间添加分隔符。

按【Ctrl+H】快捷键进入替换页面，在查找内容输入：【([0-9])([一-龟])】。

【([0-9])】：

选中 0 到 9 的任意一个数字，并定为一组。

【([一-龟])】：

选中文档中的任意一个汉字，并定为一组。

【([0-9])([一-龟])】：

只有前面是数字，后面是汉字才能被选中，同时将其分为前后两组~

在替换内容中输入：【\1|\2】

【\1】代表第一组 [0-9] 模式中查找到的任意一个数字；【\2】代表第二组 [一-龟] 模式中查找到的任意一个文字；并在两组数据间加入分隔符【|】。

之后，点击【更多 >>】，然后勾选【使用通配符】，否则，会查找失败~

因为通配符是文本值中代替未知字符的特殊字符，可以方便查找基于特定模式匹配的数据。

以【*?】为例，勾选了通配符，会让 Word 认识到【*?】不再是普通字符，而是一种特殊匹配符号。

动图走一波~

③ 部门与工作量之间添加分隔符。

按【Ctrl+F】快捷键进入替换页面，在查找内容输入：【([一-龟])([0-9])】。

【([一-龟])】：

选中文档中的任意一个汉字，并定为一组。

【([0-9])】：

选中 0 到 9 的任意一个数字，并定为一组。

【([一-龟])([0-9])】：

只有前面是汉字，后面是数字才能被选中，同时将其分为前后两组~

在替换内容中输入：【\1|\2】

【\1】代表第一组 [一-龟] 模式中查到的任意一个文字，

【\2】代表第二组 [0-9] 模式中查到的任意一个数字，

并在两组数据间加入分隔符【|】。

④ 工作量与工作周期之间添加分隔符。

按【Ctrl+F】快捷键进入替换页面，在查找内容输入：【([0-9])([0-9])(.)】。

【([0-9])】：

选中 0 到 9 的任意一个数字，并定为一组。

【(.)】：

选中符号【.】，并定为一组。

【([0-9])([0-9])(.)】：

只有第一个是数字，第二个数字，第三个是点号才能被选中，同时将其分为前中后三组~

在替换内容中输入：【\1|\2\3】

【\1】代表第一组【[0-9]】模式中查到的任意一个数字；

【\2】代表第二组【[0-9]】模式中查到的任意一个数字；

【\3】代表第三组中的点号，并在第一和第二组间加入分隔符【|】。

以上设置好后，就可将数据粘回 Excel 中处理了~

▋Excel 数据处理

⑤ 用 Excel 进行分列。

分隔符添加完毕后，就可以轻松的使用【分列】功能进行数据分列了~

再用【分列】的【固定宽度】处理下列名就可以了~

最后我们就得到了这样的规范数据了~

知识总结

一定要牢记，拿到数据不要着急。

首先要分析数据特征；根椐数据特征使用相应的软件进行初步处理。

Word+Excel 处理：

❶ 分析数据并粘入 Word；

❷ 构建正则表达式的匹配模型，如【([一-龟])([0-9])】；

❸ 勾选【使用通配符】并进行分组；

❹ 加入分隔标识；

❺ 粘回 Excel【分列】处理。

PS：通配符中的各个符号都是半角下的英文状态哦！

其实除了以上这种处理方法之外，还有一种 PQ 法，由于篇幅有限，我这里简单讲下步骤，感兴趣的同学可以自己动手试试看~

PQ 处理：

❶ 选中数据区域内的任意一个单元格，点击【数据选项卡】-「获取数据」-「来自文件」-选择「从文件夹」；

❷ 拆分列 --【按字符数】；

❸ 拆分列 --【按照从非数字到数字的转换】；

❹ 拆分列 --【按照从数字到非数字的转换】。

本文来自微信公众号：秋叶 Excel （ID：excel100），作者：农夫