|
每天一点小技能 职场打怪不得怂  编按：身为exceler，一定要遵守数据录入规范！因为不规范的数据源会让几乎所有后续操作都无效，这是让所有exceler都头大的问题。如果你不幸遇到了这样的“猪”队友，那么就只能靠自己重新清洗数据源了。今天，小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是数据清洗利器，适当的时候使用可以事半功倍哦。如果您想从零开始，全面系统学习power query，欢迎参加部落窝教育的Excel实战训练营学习。训练营有详细的PQ课程，以及工作辅导答疑服务。扫下面海报二维码查看：
Excel教程相关推荐 |
|
|
