原文标题:《天天都在用的 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),作者:农夫