day6 excel教程——我们该如何清洗处理数据(PowerQuery的运用)
案例分析:
任务要求:
在不改变数据源的情况下,建立一个清洗后的数据表:
1. 将类别和商品拆分到两列数据中。
2. 订单时间不显示时分秒,只具体到日期即可。
3. 增加一列实付金额,对超过1000元金额的订单可以打95折。
拿到表后,我们需要进行以下几个步骤
- 检查原表,标记异常值
- 跟业务团队确认并修改(-1050)(顾客姓名缺失不影响)
- 清洗数据(要求不改变数据源),故先新建一个数据表
新建数据表:
法一:选中表区域(注意不要包括标题2021年1-12月份销售订单)
再按下CTRL+T
,选择确定即可
法二:
插入-表格-框选表区域即可
记得勾选标题
那怎么转回普通表(区域)
先选中超级表
表设计-转换为区域即可
创建好表格后,我们就可以使用power query了
先框选表
数据-来自表格区域
双击可以重命名
金额筛选
确定-就可以在不改变原数据的情况清除金额99999999等异常
时间筛选
这样2022年和空值就被全部筛选掉了
而顾客姓名不影响数据处理可以不用清除
拆分类别/商品为两列
选中该列-拆分列-按分隔符
再重新命名
对订单时间进行格式化(去除时分秒)
点击时间表-日期
就可以了
这时候有同学就有疑问了:为什么不能直接删除而要这样多此一举呢?
因为PowerQuery能记录我们的所有操作,并可调回当前的状态,可以单独地删除某个步骤,换句话说,PowerQuery保留了最初数据源的状态,对数据源无损
增加实付金额,对不超过1000元的金额打个95折
添加列-自定义列-修改新列名-自定义公式输入if [金额]>1000 then [金额]*0.95 else [金额]
-确定
修改结果:
所有操作完毕-文件-关闭并上载至-现有工作表
我们的销售订单就出来了(剩下就是按需筛选即可)