Excel——利用excel对数据进行初步的清洗（2）

任务要求：在不改动数据源的情况下，建立一个清洗后的数据表：

                  1.将类别和商品拆分到两列数据中；

                  2.订单时间不显示分秒，具体到日期即可；

                  3.增加一列师父金额，对超过1000元金额的订单可以打95折。

注意：在拿到数据后，首先要检查是否存在数据缺失、异常值、不规范等情况，如果确认有问题，和业务团队确认是否有收集信息的疏漏，最后评估并决定如何进行处理。

数据如下图1-1所示：

图1-1

 一、找出缺失值、异常值和不规范等等的异常数据

1. 缺失值

可以看到图1-1中蔬菜-根茎菜这一行的顾客姓名是空值，以及最后一列蔬菜-根茎菜的订单时间为空值，我们对其先进行标注，结果如下图1-2所示：

图1-2

2. 异常值

可以看到在金额这一列中第三行有一个9999999的数据，这属于异常值，也对其进行标记。注意，我们可以看到数据的日期是2021年1月-12月的订单，而订单时间中有一个2022年6月3日的订单，这也属于异常值的一部分，最终标记结果如下图1-3所示：

图1-3

二、对于缺失值以及其他的异常数据和业务团队进行确认

如2022年6月3日这个订单确实是2022年的订单而不是数据录入错误，我们后面删除该行数据即可。

三、数据清洗

• 任务要求我们不能改动数据源，所以我们需要新建一个数据表格，在这里需要使用上次所使用到的一个插件Power Query
• 在新建数据表之前，若想将之前的表格新建查询，需要将该表格转化为超级表1.

1.将原数据表转化为超级表

• 选中所有的数据以及表头，然后按Ctrl+T，结果如下图3-1、3-2所示：

  

  图3-1

图3-2

•  普通表格变成超级表后，点击表格会发现多了一个“表设计”的选项卡，之前这个和选项卡是不存在的，如下图3-3所示：

  

  图3-3

• 如果我们将超级表转回普通表格，只需点击“表设计”该选项卡下的“转换为区域”即可 

2. 创建完表格后使用Power Query（此时表格状态为超级表）

• 点击“数据”选项卡下的“来自表格区域”，结果如下图3-4所示：

  

  图3-4

• 此时Power Query中的表格与excel中的表格的数据是一致的，首先对该表格重命名，双击点击 “表3”即可重命名（本文将该表格重命名为21年销售订单数据）

（1）将金额中的异常值99999999筛除

• 第一种方法：可以通过点击Power Query中“金额”旁边的小三角形，从下面的数字中把99999999筛选掉，操作如下图3-5所示

  

  图3-5

• 第二种方法：点击小三角形下的“数字筛选器”，选择“小于或者等于” ，然后右边填写10000，再点击确认即可，操作如下图3-6所示：

  

  图3-6

（2）将订单日期中的异常值筛除

• 点击“订单时间”旁的小三角形下的“日期/时间筛选器”下的“介于”，然后填写日期范围，最后点击确认即可，操作如下图3-7所示：

  

  图3-7

• 那么，订单时间的异常值以及空值就可以筛除掉。顾客姓名并不影响对数据进行处理，本文就不进行处理了。

四、将类别和商品拆分到两列数据 

• 选中“类别/商品”该列，然后点击上面选项卡中的“拆分列”中的“按分隔符”，操作如下图4-1、4-2所示：

  

  图4-1

图4-2

•  这里要注意的是，它自动帮助我们识别到分割符是“-”，点击确认即可。如果我们的分隔符是逗号或者是分号时要注意是英文状态下的分隔符还是中文状态下的分隔符，最终结果如图4-3所示：

  

  图4-3

• 上述操作就将一列数据拆分成两列数据，将第一列数据重命名为类别，第二列重命名为商品（此操作步骤就不作展示了，双击标题即可重命名）

五、 订单时间不显示分秒，具体到日期

• 点击“订单时间”前面的时间表符号下的“日期”，操作如下图5-1所示：

  

  图5-1

• 结果如下图5-2所示，从结果中可以看到“订单时间”该列的数据只显示日期的数据，不显示具体的分秒时间了

  

  图5-2

   

六、增加一列实付金额，对超过1000元金额的订单可以打95折 

• 需要添加一列，选择顶端选项卡的“添加列”选项下的“自定义列”，操作如下图6-1所示：

  

  图6-1

• 将列命名为“实付金额”，然后在自定义公式中编写公式，首先输入if，然后空格，点击右侧“可用列” 中的“金额”点击下面的插入或者是双击“金额”即可，再紧跟“金额”后面输入>1000，空格，then 空格 ，双击“金额”，再紧跟“金额”后输入*0.95，空格，else，空格，再双击“金额”，公式便编写完成（左下角会提示我们公式的语法是否有错误以及输入if 后会提醒输入then），点击确认便可。操作和结果分别如下图6-2、6-3所示：

  

  图6-2

图6-3

•  最后，点击左上角的“主页”下的“关闭并上载至”，然后选择“现有工作表”，再选择数据存放的位置，点击确定即可。操作以及最终结果分别如下图6-4、6-5所示：

  

  图6-4

图6-5

• 任务到此就成功完成啦 

注意： 本文中的案例以及Excel的使用方法均来自哔哩哔哩中的珞珈AI办公up主！！！

            如若有侵权，请联系删除。

            本文是作者个人学习后的总结，未经作者授权，禁止转载，谢谢配合。

            珞珈AI办公的个人空间-珞珈AI办公个人主页-哔哩哔哩视频哔哩哔哩珞珈AI办公的个人空间，提供珞珈AI办公分享的视频、音频、文章、动态、收藏等内容，关注珞珈AI办公账号，第一时间了解UP主动态。公众号【珞珈AI办公】回复【B站】领取30套顶级商业PPT模板与商业表格素材❤️跟着我，从零到一学会EXCEL❤️https://space.bilibili.com/308552985