Excel电商数据分析与应用 课件 4.数据的清洗方法

数据的清洗方法4.1数据规整&4.2数据合并与分组&4.3数据变形1数据类型2CONTENTS缺失值与异常值处理3纵向合并4横向合并5数据分组6数据透视7数据逆透视数据类型PARTONE数据类型

数据类型是数据一致性检查的基本项，在严谨的数据清洗过程中每个列（字段）都需要指定数据类型，否则在后续的分析过程中有可能因为数据类型不符合算法要求导致出错。在Excel的许多场景应用中无须指定数据的类型，但在PowerQuery中必须指定数据类型。在PowerQuery编辑器【转换】选项卡中修改数据类型，也可以全选数据，单击【检测数据类型】选项即可。数据类型数据类型示例逻辑true

false整数0

1

-1

小数0.31.3-1.3时间#time(09,15,00)文本"hello"二进制#binary("AQID")常见的数据类型如表：缺失值与异常值处理PARTTWO缺失值与异常值处理缺失值和异常值的处理有两种方法，第一种是删除法，删除缺失或异常数据的记录，使用此方法的前提条件是删除记录不会对数据分析的结果产生影响。第二种是插补法，当删除整条记录对分析结果有严重影响时，根据实际情况可用0插补也可以用均值插补。

在PowerQuery编辑器【开始】选项卡中使用【替换值】功能替换缺失值或异常值。如图：纵向合并PARTTHREE纵向合并纵向合并在数据库中也称为追加查询，常用于多张相同结构字段的表格合并成一张表格的场景。

例：现有100家门店的销售数据，分别在100个文件中，将100家门店的销售数据合并成一张表。纵向合并①在Excel【数据】选项卡中选择【获取数据】，在【自文件】中选择【从文件夹】选项，将数据导入PowerQuery编辑器。纵向合并②在弹出的“文件夹”对话框中，选择文件夹路径。纵向合并③Excel2019版本有组合功能可以一键组合成一个文件，Excel2010/2016版本可参考后续操作。单击【组合】后会再弹出下拉菜单，选择【合并和编辑】选项。纵向合并④针对部分版本没有组合功能的操作，需要进入编辑器中编辑，每一个Binary都是一个二进制文件，数据以二进制文件形式被PowerQuery编辑器读取。纵向合并⑤在【添加列】选项卡中，单击【自定义列】选项。输入“Csv.Document([Content],[Encoding=936])”将二进制文件转换成表格。Csv.Document是将二进制文件转换成Csv格式的表格。[Encoding=936]是指定中文编码为GBK。纵向合并⑥展开Table，取消勾选【使用原始列名作为前缀】。纵向合并⑦发现展开后字段名称在记录中，需要在【开始】选项卡单击【将第一行用作标题】选项，第一行的记录会转变成字段名（标题）。纵向合并⑧由于每一个文件都有字段名称，因此需要将多余的名称过滤掉，如图4-14所示，对【日期】进行筛选，将“日期”过滤掉。纵向合并⑨在【开始】选项卡中单击【关闭并上载】选项，将数据导入Excel，可以观察合并后的数据结果。横向合并PARTFOUR横向合并横向合并在数据库中称为合并查询，合并查询分为左外部、右外部、完全外部、内部、左反和右反6种连接方式。

下图为左外部连接示意图和右外部连接示意图左外部连接示意图右外部连接示意图横向合并例：现有两张表，分别是学生的基本信息表和学生的成绩表，若分析班级不同性别的学生成绩的差异，需要将两张表进行合并。学生基本信息表姓名性别年龄张三男19李四男18陈红女19孙玉女19学生成绩表姓名学科成绩张三语文82张三数学80张三英语79李四语文99李四数学77李四英语98陈红语文98陈红数学96陈红英语83孙玉语文95孙玉数学94孙玉英语84横向合并①：选中表格，在【数据】选项卡中单击【自表格/区域】（某些版本是【从表格】的字样）选项，分别将两张表格导入PowerQuery编辑器。横向合并②在PowerQuery编辑器的【开始】选项卡中，单击【合并查询】选项，在【合并】对话框中，表1和表2分别选中【姓名】字段，连接种类选择【左外部】。横向合并③合并后展开表，由于信息表中已经有姓名字段，因此取消勾选【姓名】，并且取消勾选【使用原始列名作为前缀】。横向合并下图为完全外部连接返回左表和右表的所有数据、内部连接仅返回左表和右表匹配的所有数据、左反连接返回仅左表有的数据、右反连接返回仅右表有的数据。完全外部连接示意图内部连接示意图左反连接示意图右反连接示意图横向合并例：某企业针对老消费者开展了赠品活动，现有已领取礼品的消费者名单如表4-3所示，需要排查未领礼品的人员名单，下表是所有消费者的名单。已领礼品消费者名单张三李四王五赵六所有消费者名单张三李四王五赵六钱七孙八横向合并前面步骤与上个案例相同，不同的是在“合并”窗口，连接方式选择【左反】。合并后，就可以找到没有领取的名单，表中的空表可以直接删除。最后在【开始】选项卡中单击【关闭并上载】选项，将数据导入Excel，即可得到消费者名单。数据分组PARTFIVE数据分组数据分组是根据某个维度将数据以某种算术方法（求和、计数等）进行统计汇总。例4-4：现有某班级同学各个学科的成绩（如下表），老师想统计班级各个学科的平均分。学生成绩表姓名学科成绩张三语文82张三数学80张三英语79李四语文99李四数学77李四英语98陈红语文98陈红数学96陈红英语83孙玉语文95孙玉数学94孙玉英语84数据分组①先选中学习成绩表，在【数据】选项卡中单击【自表格/区域】（某些版本是【从表格】的字样）选项，将表格导入PowerQuery编辑器。数据分组②选中【学科】列，在【开始】选项卡中单击【分组依据】选项，对数据进行分组统计。在“分组依据”对话框中进行设置，【操作】选择【平均值】，【柱】选择【成绩】。数据透视PARTSIX数据透视数据变形是将数据的结构进行变换，特别是一维表转为二维表，或者二维表转为一维表，变换结构便于后续的数据分析工作开展。数据透视是将某维度的行转变成列，可实现数据的快速汇总和分类。例:将下列的学生成绩表转变成姓名和学科的二维表学生成绩表姓名学科成绩张三语文82张三数学80张三英语79李四语文99李四数学77李四英语98陈红语文98陈红数学96陈红英语83孙玉语文95孙玉数学94孙玉英语84数据透视①选中表格，在【数据】选项卡中单击【自表格/区域】选项，将两张表格导入PowerQuery编辑器。数据透视②在PowerQuery编辑器中，选中【成绩】列，在【转换】选项卡中单击【透视列】选项。数据透视③在【透视列】对话框中，设置【值列】为【成绩】，单击【确定】按