编按：

当遇到不规范数据源时，所有常规操作都瞬间无效，这是让每个exceler都头大的问题。因此，重新整理不规范数据源就成了大家必须掌握的excel技巧之一。今天，小E为大家带来的就是excel中最强的数据整理技巧——power query！让它帮你完成数据清洗和后续数据核算，很轻松！

小伙伴们，大家好！今天带来一个数据清洗的教程。

从后期数据分析的角度来说，大家都希望数据源越规范越好。数据规范的最基本原则更是必须遵守，即数值和文本不能放在一个单元格里。但是，笔者今天还是收到了一个不懂规则的“猪”队友提交的表格：

问题是分析每个人的平均分。比如张三的平均分算式是(97+92)/2，李四的平均分算式是(85+69+38)/3……依次类推。

但是笔者面对这样格式杂乱的数据表格，感觉到了棘手，本来已经熟练应用的函数也不能马上套用了。先压压惊，笔者想到了excel中的数据清洗工具——power query。

小贴士：power query是2016及以上的office excel才内置的功能，16版本下使用需要安装插件。

Step 01

用鼠标点击数据源任意位置，然后点击“数据”选项卡下的“自表格/区域”，将数据加载到power query编辑器。

Step 02

在power query编辑器页面，用鼠标点击“主页”，选择“拆分列”选项卡下的“按分隔符”。

在弹出的界面中，选择“自定义”，并在下方输入框中输入相应的符号。因为这里的姓名和分数之间是用顿号隔开的，所以在输入框中输入顿号。然后点击“确定”即可。

这样便将人名和分数分开了，效果如下：

Step 03

拆分分数列。

用鼠标点击分数列，用鼠标点击“主页”，选择“拆分列”选项卡下的“按分隔符”（和步骤2相同）。然后在弹出的界面中，在符号自定义处输入“-”，在拆分位置中选择“每次出现分隔符时”，在高级选项下选择“行”，最后点击确定。

这时，数据已经清洗到比较规范的程度了，如下图：

Step 04

有了规范的数据后计算平均分。用鼠标点击 “主页”下的“分组依据”选项卡。在弹出的对话框中，分别设置“分数.1”、“平均分”、“平均值”、“分数.2”。

分析：

“分数.1”对应的是数据源的人名列；“平均分”是新列名；“平均值”表示要对分数列做的计算类型，此时是计算“平均值”；“分数.2”对应的是数据源的分数列。

将4个要素选择完毕后点“确定”。所有人的分数的平均值就算出来啦。

如果没有后续数据分析应用需求，大家可以把鼠标移动到“平均分”列上，点击鼠标右键，选择“删除其他列”，即只保留平均分列。

Step 05

将power query界面关闭，并上载至现有工作表。然后选择原始数据源“分数”列右侧的单元格，用鼠标点击“确定”即可。

最后便将计算后的平均分加载到了数据源旁边。

注意：

如果后续有数据新增或者改动，只需要右键刷新即可。

小伙伴们学会了吗？power query是数据清洗利器，适当的时候使用可以事半功倍哦。

本文配套的练习课件请加入QQ群：902294808下载。

做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！

扫下方二维码关注公众号，可随时随地学习Excel：

相关推荐：

Excel运用规范1：一个单元格只记录一条信息

只再说最后一次：Excel数据源表家规

快速整理不规范的Excel表格的7个公式

Excel和Word强强联手,整理不规范数据，快如闪电!

版权申明：

本文作者过儿；同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。