Excel数据批量处理技巧 | 数据项行列拆分

邹桂明

很多人对于Excel的函数公式写的非常熟练，但是在这些不规范的数据面前，只能用最原始的复制粘贴低效的完成工作。
所以，掌握数据整理的技巧，在一开始统计时规范数据，能够很大程度上降低报表的统计难度，提升工作效率。
所以，今天给大家分享3个数据整理简单使用的机器，分分钟让表格变的顺手又好用！
1- 规则数据——分列搞定数据提取

整理数据一定要学的第1个技巧就是【分列】，它可以按照指定的规律，把数据拆分成多列。
1.1 按照固定宽度，提取数据

比如，要在身份证号码中，把出生日期提取出来。
因为身份证号码是非常有规律的数字，第7~15位代表出生日期。所以就可以使用【分列】功能快速的提取。


在分列功能中，按照【固定宽度】，就可以提取对应的数据。


在第下一步中，可以设置拆分的位置，然后点击【下一步】【完成】就可以实现数据的拆分。


1.2 按照分隔符号，提取数据

这是【分列】功能最常用的方法。
比如下面的表格中，部门、姓名、性别等多个信息混在A列中，现在要拆分到单独列。
好在每个数据之间有一个“分隔符号”逗号，所以就可以按照逗号快速的拆分。


选择A列数之后，使用【分列】【分隔符号】来拆分数据即可。


在下一步操作中，勾选对应的“分隔符号”，然后【下一步】【完成】，数据就批量拆分完成了。


在使用Excel的时候，如果一个表格，需要你不停的复制、粘贴来完成，那么一定要多问自己一句【这样的操作，Excel可以批量】实现吗？
Excel的功能，远比你想象的要更加强大！
2- 不规则数据——Ctrl+E超神快捷键

做报表时，限制你能力的，可能不是你的Excel水平，而是Excel的版本！
在Excel 2013版之后，Excel新增了一个超神快捷键Ctrl+E，用来提取数据，变的和复制粘贴一样简单。
比如下面的表格中，想要提取括号里的数字，只需要填写第1个数字，然后按下Ctrl+E就可以批量完成。


再比如，要提取下面第2个中括号中的数据，同样输入第1组数据中的结果，按下Ctrl+E就可以了。


提取身份证号码中的出生日期，也不需要使用【分列】功能了，输入第1个出生日期，按下Ctrl+E就可以轻松实现。


如果你还在使用2007，2010甚至更老的版本，就算了为了这个Ctrl+E功能，真的没有理由不升级软件了。
3- Power Query，数据行列整理

有的人，表格用了5年10年，还是一个Excel新手。
有的人，刚毕业1年2年，就成为办公室里的Excel大表格。
如果真有这么大的差距，那么很有可能第2个人，学习了Excel新增的一个超强数据整理神器：Power Query
Power Query，是Excel 2016以后新增的一个数据处理插件，弥补了Excel在数据处理上面短板。
到底好不好用，来看看下面的案例就知道了。

现在需要把B列中的不同区域名称，按照顿号拆分开来，变成右边的样子。



当然，聪明的你，一下子就想到了咱们学习过的【分列】功能。
分列确实可以按照顿号来拆分数据，但是拆分出来的结果是这样的。


数据都是一列一列的，不符合我们的要求。
要说，这个需求还是得用Power Query，分分钟搞定！
1- 加载数据到Power Query

首先选择任意单元格，在【数据】选项卡中，点击【自表格/区域】，把数据加载到Power Query中去。


加载后是这个样子



2- 拆分数据

Power Query有一个和【分列】类似的功能【拆分列】，就多了一个字，但是功能却强大了不少。
选择要拆分列的任意单元格，在【主页】选项卡中，点击【拆分列】。


在弹出的对话框中，输入要拆分的分隔符【顿号】。
注意，关键的地方来了，在下面的【高级】部分，勾选拆分到【行】，然后点击【确定】，数据就一下子拆分到不同的行了，简单又高效。




最后，在【主页】选项卡中，点击【关闭并上载】，把数据加载到Excel中就可以了。


Power Query强大的功能还有很多，比如：

• 多个工作表/簿数据汇总
  
• 网页数据抓取
  
• 二维表转一维表等等
  

不过，Power Query是微软开发的，所以国产的WPS没有类似的功能。
4- word查找与替换实现数据拆分

3中的任务场景，还可以通过辅助使用word来实现
1、复制到word查找替换



2、放回Excel并拆分合并单元格



素材分享

分享一个好用的素材，花瓣网相信很多同学都接触过，其中也有很多Excel面板模板可以借鉴
花瓣网：http://u.ppt100.cn/eevub