别再用分列拆分数据了,弱爆了!教你一个最强的数据清洗方法_query

【置顶公众号】或【设为星标】及时接收不迷路

小伙伴们，大家好！

你有没有收到过一看就是外行做的表？？？

那些对Excel一窍不通的“猪队友们”总是能以各种方式让我们炸毛！

比如下面这张图：

今天就给大家带来 Excel中最强的数据整理技巧——power query！

让它帮你完成数据清洗和后续数据核算，很轻松！

我们的问题是分析每个人的平均分。

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

小贴士：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是数据清洗利器，适当的时候使用可以事半功倍哦。

还有什么疑问，欢迎评论区留言~~