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),作者:农夫

Published by

风君子

独自遨游何稽首 揭天掀地慰生平

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注