数据分析基础课程-第3章-数据的处理课件

目录/Contents3.1数据清洗3.2数据的来源第三章数据的处理3.3数据的修整目录/Contents3.1数据清洗3.2数据的来源13.1数据清洗数据清洗就是将格式错误的数据进行处理纠正，将错误的数据纠正或删除，将缺失的数据补充完整，将重复多余的数据删除。3.1.1数据一致性处理通过统计调查收集上来的数据，经常会出现同一字段的数据格式不一致的问题，如图3-1所示。这会直接影响后续的数据分析，所以必须对数据的格式做出一致性处理。图3-1数据格式不一致的资料3.1数据清洗数据清洗就是将格式错误的数据进行处理纠正，21.1数字通信系统的基本概念下面就以图3-1所示的数据为例，将“身高”这个字段中的数据去掉字符“cm”。打开Excel文件“数据处理.xlsx”，找到“数据清洗”工作表。（1）把鼠标指针移到字母C上，当指针变成

时，单击选择C列，如图3-2所示。（2）选择“查找和选择”|“替换”命令，如图3-3所示。41.1数字通信系统的基本概念下面就以图3-1所示的数据为3图3-2选择C列图3-3选择“替换”命令图3-2选择C列图3-3选择“替换”命令4（3）在“查找和替换”对话框的“查找内容”中输入“cm”，设置“替换为”为空，单击“全部替换”按钮完成替换，如图3-4所示。替换后的结果如图3-5所示。图3-4输入查找内容和替换内容图3-5替换后的结果（3）在“查找和替换”对话框的“查找内容”中输入“cm”，设53.1.2缺失数据的处理数据清单中，单元格如果出现空值，就认为数据存在缺失。缺失数据的处理方法通常有以下3种：

用样本均值（或众数、中位数）代替缺失值；

将有缺失值的记录删除；

保留该记录，在要用到该值做分析时，将其临时删除（最常用方法）。3.1.2缺失数据的处理6首先来解决如何发现缺失数据，仅靠眼睛来搜索缺失数据显然是不现实的，一般我们用“定位条件”来查找缺失数据的单元格。下面演示将“年龄”字段中的空值均替换为“18”。（1）选择“年龄”所在的E列。（2）选择“查找和选择”|“定位条件”命令，如图3-6所示。（3）在“定位条件”对话框中，选中“空值”单选项，如图3-7所示。图3-6选择“定位条件”命令

图3-7选择定位条件“空值”首先来解决如何发现缺失数据，仅靠眼睛来搜索缺失数据显然是不现7（4）单击“确定”按钮后，E列所有的空白单元格呈选中状态，如图3-8所示。（5）输入替代值“18”，按Ctrl+Enter组合键确认，结果如图3-9所示。图3-8查找到所有空白单元格图3-9统一输入新的数据（4）单击“确定”按钮后，E列所有的空白单元格呈选中状态，如83.1.3删除重复记录删除重复记录的操作极其简单，只需单击数据表的任意位置，再单击“数据”|“删除重复项”按钮即可，如图3-10所示。图3-10删除重复项3.1.3删除重复记录图3-10删除重复项93.2数据加工数据加工的手段主要有数据转置、字段分列、字段匹配、数据抽取、数据计算。3.2.1数据转置操作的方法是：先复制好横行数据，然后在粘贴时单击“开始”|“剪贴板”组“粘贴”按钮下面的三角箭头，单击“转置”按钮即可，如图3-11所示。图3-11转置性粘贴3.2数据加工数据加工的手段主要有数据转置、字段分列、字10 3.2.2字段分列（1）选择“字段分列”工作表的A列数据，如图3-12所示。（2）单击“数据”|“分列”按钮，如图3-13所示。图3-12选择A列

图3-13数据分列 3.2.2字段分列图3-12选择A列图3-1311（3）要将字段“姓名”中的第一个字分列出来，所以选中“固定列宽”单选项，如图3-14所示。图3-14选中“固定列宽”单选项（3）要将字段“姓名”中的第一个字分列出来，所以选中“固定列12（4）单击“下一步”按钮，在刻度尺上单击鼠标确定分列的位置，如图3-15所示。（5）单击“下一步”按钮，确定目标区域的起点单元格D1，如图3-16所示。图3-15确定分列位置图3-16确定目标区域（4）单击“下一步”按钮，在刻度尺上单击鼠标确定分列的位置，13（6）单击“完成”按钮，分列的结果如图3-17所示。图3-17分列结果（6）单击“完成”按钮，分列的结果如图3-17所示。图3-1143.2.3字段匹配字段匹配就是将原数据清单中没有但其他数据清单中有的字段匹配过来。例如，文件“数据处理.xlsx”中的“全校名单”工作表是某校2015级全体学生的基本信息（见图3-18），“四级名单”工作表是2015级学生中报考了英语四级的学生名单（见图3-19）。图3-18“全校名单”工作表图3-19“四级名单”工作表3.2.3字段匹配图3-18“全校名单”工作表图3-115（1）将“白有成”的身份证号码匹配到单元格D2。（2）双击D2的填充柄完成填充，将所有人的身份证号码都匹配过来，结果如图3-21所示。图3-20vlookup函数的参数设置图3-21匹配结果（1）将“白有成”的身份证号码匹配到单元格D2。图3-20163.2.4数据抽取数据抽取是指利用原数据清单中某些字段的部分信息得到一个新字段。常用的数据抽取函数有left()、right()、mid()、year()、month()、day()、weekday()。图3-22weekday函数应用3.2.4数据抽取图3-22weekday函数应用173.2.5数据计算例1：文件“数据处理.xlsx”的“数据计算1”工作表中只有“销量”和“单价”，没有“销售额”，可以通过公式“销售额=单价×销量”来计算销售额，如图3-23所示。图3-23计算销售额3.2.5数据计算图3-23计算销售额18例2：文件“数据处理.xlsx”的“数据计算2”工作表中只有“成交单数”和“好评单数”，可以通过公式“好评率= ”来计算好评率，如图3-24所示。图3-24计算好评率例2：文件“数据处理.xlsx”的“数据计算2”工作表中只有19例3：文件“数据处理.xlsx”的“数据计算3”工作表中，已知商品的“上架日期”和“下架日期”，可以通过公式“销售天数=下架日期-上架日期”来计算商品的销售天数，如图3-25所示。图3-25计算销售天数例3：文件“数据处理.xlsx”的“数据计算3”工作表中，已20例4：文件“数据处理.xlsx”的“数据计算4”工作表中，已知商品的“上架日期”，要计算迄今为止的上架天数，可以用函数today来获取当天的日期，用公式“=today()-B2”来计算上架天数，如图3-26所示。图3-26计算上架天数例4：文件“数据处理.xlsx”的“数据计算4”工作表中，已21例5：文件“数据处理.xlsx”的“数据计算3”工作表中，可以用公式“=D2/360”计算销售年数，结果为3.6，如图3-27所示。图3-27计算销售年数例5：文件“数据处理.xlsx”的“数据计算3”工作表中，可22这样计算出来的年数通常是一个小数，如果希望得到整数，可以用int函数取整，即用公式“=int(D2/360)”，结果是3，如图3-28所示。因为int函数的功能是返回不大于括号内参数的整数。图3-28用int函数取整这样计算出来的年数通常是一个小数，如果希望得到整数，可以用i23如果要进行四舍五入式取整，则要用函数round。当round(number，digits)函数的第二个参数为0时，就可以对第一个参数进行四舍五入式取整，所以在编辑栏将公式修改为“=round(D2/360,0)”即可，如图3-29所示。图3-29用round函数取整如果要进行四舍五入式取整，则要用函数round。当round24函数int(number)的功能是向下取整（数轴上离左边最近的整数），如图3-30所示。图3-30int函数解释函数int(number)的功能是向下取整（数轴上离左边最近25所以，int(6.4)=int(6.7)=6，int(-6.4)=int(-6.7)=-7。函数round(number,digits)的功能是进行四舍五入运算，功能解释如表3-1所示。number1263.472digits-2-1012四舍五入的位数十位个位取整保留1位小数保留2位小数结果1300126012631263.51263.47表3-1round函数解释所以，int(6.4)=int(6.7)=6，int(-6.263.3数据的修整在一段较长的时间内，由于普通的、持续的、决定性等基本因素的作用，总体往往呈现逐渐向上或向下变动的趋势，如图3-31所示。图3-31明显的向上趋势3.3数据的修整在一段较长的时间内，由于普通的、持续的、27在这样的趋势中，也不排除受一些偶然因素或不规则因素的影响，出现与整体趋势相差很大的极端数据，如图3-32中箭头所对应的数据所示。移动平均法就是从时间数列的第一位数值开始，按一定项数求平均数，逐项移动，形成一个新的动态数列。常用的移动平均法有三项移动平均法和四项移动平均法。图3-32局部的数据异动在这样的趋势中，也不排除受一些偶然因素或不规则因素的影响，出283.3.1三项移动平均法例：计算图3-33所示的表格中，商品销售额的三项移动平均数。分析：选择单元格区域A1:B13，单击“插入”|“散点图”|“仅带数据标记的散点图”按钮，如图3-34所示。图3-332004—2015年销售额统计图3-34插入散点图3.3.1三项移动平均法图3-332004—201529结果得到图3-35所示的散点图，从散点图可以直观地看出，第3个点明显偏小，而第6个点明显偏大，这可能是由不确定因素的影响造成的。在这种情况下，可以通过移动平均法对数据做修整，尽量排除不确定因素对数据造成的影响。图3-35原始数据的散点图结果得到图3-35所示的散点图，从散点图可以直观地看出，第330图3-36选择“平均值”命令图3-37计算三项平均数图3-36选择“平均值”命令图3-37计算三项31图3-38计算三项移动平均数图3-39修整后的数据散点图图3-38计算三项移动平均数图3-39修整后的数据散323.3.2四项移动平均法例：计算图3-40所示的表格中商品销售额的四项移动平均数。图3-40四项移动平均数的计算3.3.2四项移动平均法图3-40四项移动平均数的计33下面在Excel中用函数average计算四项移动平均数。（1）打开文件“数据处理.xlsx”中的“四项移动平均”工作表，在C3中使用公式“=average(B2:B5)”计算第一个四项移动平均数，如图3-41所示。（2）确认C3的计算后，将C3的填充柄填充到C11。图3-41计算四项移动平均数下面在Excel中用函数average计算四项移动平均数。图34（3）在D4中使用公式“=average(C3:C4)”，如图3-42所示。（4）确认D4的计算后，将D4的填充柄填充到D11。注意：若采用奇数项移动平均，平均值对准居中原时间数列的项数，一次可得趋势值。若采用偶数项移动平均，平均值未对准居中原时间数列的项数，需再通过一次移动平均进行正位。图3-42计算四项移动平均正位数（3）在D4中使用公式“=average(C3:C4)”，如353.3.3分析工具库的加载和应用在Excel中，还可以用分析工具库完成移动平均数的计算。默认情况下，Excel并没有安装分析工具库。下面介绍分析工具库的安装过程。（1）在Excel2010窗口中，选择“文件”|“选项”命令。（2）在随后打开的“Excel选项”对话框中，单击左边的“加载项”选项后，再单击下方的“转到”按钮，如图3-43所示。图3-43加载项3.3.3分析工具库的加载和应用图3-43加载项36（3）在随后打开的“加载宏”对话框中，选中“分析工具库”复选项，单击“确定”按钮，如图3-44所示。（4）加载成功后，会在”数据”选项卡中看到一个新的功能“数据分析”，如图3-45所示。图3-44加载“分析工具库”图3-45加载成功后的“数据”选项卡（3）在随后打开的“加载宏”对话框中，选中“分析工具库”复选37下面介绍利用“数据分析”功能计算三项移动平均数的方法。（1）打开文件“数据处理.xlsx”中的“三项移动平均”工作表，单击“数据”|“数据分析”按钮。（2）在随后打开的“数据分析”对话框中选择“移动平均”选项，如图3-46所示。图3-46选择“移动平均”选项下面介绍利用“数据分析”功能计算三项移动平均数的方法。图3-38（3）在“移动平均”对话框中设置各参数如图3-47所示，最终结果如图3-48所示。图3-47三项“移动平均”的设置图3-48最终结果（3）在“移动平均”对话框中设置各参数如图3-47所示，最终39目录/Contents3.1数据清洗3.2数据的来源第三章数据的处理3.3数据的修整目录/Contents3.1数据清洗3.2数据的来源403.1数据清洗数据清洗就是将格式错误的数据进行处理纠正，将错误的数据纠正或删除，将缺失的数据补充完整，将重复多余的数据删除。3.1.1数据一致性处理通过统计调查收集上来的数据，经常会出现同一字段的数据格式不一致的问题，如图3-1所示。这会直接影响后续的数据分析，所以必须对数据的格式做出一致性处理。图3-1数据格式不一致的资料3.1数据清洗数据清洗就是将格式错误的数据进行处理纠正，411.1数字通信系统的基本概念下面就以图3-1所示的数据为例，将“身高”这个字段中的数据去掉字符“cm”。打开Excel文件“数据处理.xlsx”，找到“数据清洗”工作表。（1）把鼠标指针移到字母C上，当指针变成

时，单击选择C列，如图3-2所示。（2）选择“查找和选择”|“替换”命令，如图3-3所示。41.1数字通信系统的基本概念下面就以图3-1所示的数据为42图3-2选择C列图3-3选择“替换”命令图3-2选择C列图3-3选择“替换”命令43（3）在“查找和替换”对话框的“查找内容”中输入“cm”，设置“替换为”为空，单击“全部替换”按钮完成替换，如图3-4所示。替换后的结果如图3-5所示。图3-4输入查找内容和替换内容图3-5替换后的结果（3）在“查找和替换”对话框的“查找内容”中输入“cm”，设443.1.2缺失数据的处理数据清单中，单元格如果出现空值，就认为数据存在缺失。缺失数据的处理方法通常有以下3种：

用样本均值（或众数、中位数）代替缺失值；

将有缺失值的记录删除；

保留该记录，在要用到该值做分析时，将其临时删除（最常用方法）。3.1.2缺失数据的处理45首先来解决如何发现缺失数据，仅靠眼睛来搜索缺失数据显然是不现实的，一般我们用“定位条件”来查找缺失数据的单元格。下面演示将“年龄”字段中的空值均替换为“18”。（1）选择“年龄”所在的E列。（2）选择“查找和选择”|“定位条件”命令，如图3-6所示。（3）在“定位条件”对话框中，选中“空值”单选项，如图3-7所示。图3-6选择“定位条件”命令

图3-7选择定位条件“空值”首先来解决如何发现缺失数据，仅靠眼睛来搜索缺失数据显然是不现46（4）单击“确定”按钮后，E列所有的空白单元格呈选中状态，如图3-8所示。（5）输入替代值“18”，按Ctrl+Enter组合键确认，结果如图3-9所示。图3-8查找到所有空白单元格图3-9统一输入新的数据（4）单击“确定”按钮后，E列所有的空白单元格呈选中状态，如473.1.3删除重复记录删除重复记录的操作极其简单，只需单击数据表的任意位置，再单击“数据”|“删除重复项”按钮即可，如图3-10所示。图3-10删除重复项3.1.3删除重复记录图3-10删除重复项483.2数据加工数据加工的手段主要有数据转置、字段分列、字段匹配、数据抽取、数据计算。3.2.1数据转置操作的方法是：先复制好横行数据，然后在粘贴时单击“开始”|“剪贴板”组“粘贴”按钮下面的三角箭头，单击“转置”按钮即可，如图3-11所示。图3-11转置性粘贴3.2数据加工数据加工的手段主要有数据转置、字段分列、字49 3.2.2字段分列（1）选择“字段分列”工作表的A列数据，如图3-12所示。（2）单击“数据”|“分列”按钮，如图3-13所示。图3-12选择A列

图3-13数据分列 3.2.2字段分列图3-12选择A列图3-1350（3）要将字段“姓名”中的第一个字分列出来，所以选中“固定列宽”单选项，如图3-14所示。图3-14选中“固定列宽”单选项（3）要将字段“姓名”中的第一个字分列出来，所以选中“固定列51（4）单击“下一步”按钮，在刻度尺上单击鼠标确定分列的位置，如图3-15所示。（5）单击“下一步”按钮，确定目标区域的起点单元格D1，如图3-16所示。图3-15确定分列位置图3-16确定目标区域（4）单击“下一步”按钮，在刻度尺上单击鼠标确定分列的位置，52（6）单击“完成”按钮，分列的结果如图3-17所示。图3-17分列结果（6）单击“完成”按钮，分列的结果如图3-17所示。图3-1533.2.3字段匹配字段匹配就是将原数据清单中没有但其他数据清单中有的字段匹配过来。例如，文件“数据处理.xlsx”中的“全校名单”工作表是某校2015级全体学生的基本信息（见图3-18），“四级名单”工作表是2015级学生中报考了英语四级的学生名单（见图3-19）。图3-18“全校名单”工作表图3-19“四级名单”工作表3.2.3字段匹配图3-18“全校名单”工作表图3-154（1）将“白有成”的身份证号码匹配到单元格D2。（2）双击D2的填充柄完成填充，将所有人的身份证号码都匹配过来，结果如图3-21所示。图3-20vlookup函数的参数设置图3-21匹配结果（1）将“白有成”的身份证号码匹配到单元格D2。图3-20553.2.4数据抽取数据抽取是指利用原数据清单中某些字段的部分信息得到一个新字段。常用的数据抽取函数有left()、right()、mid()、year()、month()、day()、weekday()。图3-22weekday函数应用3.2.4数据抽取图3-22weekday函数应用563.2.5数据计算例1：文件“数据处理.xlsx”的“数据计算1”工作表中只有“销量”和“单价”，没有“销售额”，可以通过公式“销售额=单价×销量”来计算销售额，如图3-23所示。图3-23计算销售额3.2.5数据计算图3-23计算销售额57例2：文件“数据处理.xlsx”的“数据计算2”工作表中只有“成交单数”和“好评单数”，可以通过公式“好评率= ”来计算好评率，如图3-24所示。图3-24计算好评率例2：文件“数据处理.xlsx”的“数据计算2”工作表中只有58例3：文件“数据处理.xlsx”的“数据计算3”工作表中，已知商品的“上架日期”和“下架日期”，可以通过公式“销售天数=下架日期-上架日期”来计算商品的销售天数，如图3-25所示。图3-25计算销售天数例3：文件“数据处理.xlsx”的“数据计算3”工作表中，已59例4：文件“数据处理.xlsx”的“数据计算4”工作表中，已知商品的“上架日期”，要计算迄今为止的上架天数，可以用函数today来获取当天的日期，用公式“=today()-B2”来计算上架天数，如图3-26所示。图3-26计算上架天数例4：文件“数据处理.xlsx”的“数据计算4”工作表中，已60例5：文件“数据处理.xlsx”的“数据计算3”工作表中，可以用公式“=D2/360”计算销售年数，结果为3.6，如图3-27所示。图3-27计算销售年数例5：文件“数据处理.xlsx”的“数据计算3”工作表中，可61这样计算出来的年数通常是一个小数，如果希望得到整数，可以用int函数取整，即用公式“=int(D2/360)”，结果是3，如图3-28所示。因为int函数的功能是返回不大于括号内参数的整数。图3-28用int函数取整这样计算出来的年数通常是一个小数，如果希望得到整数，可以用i62如果要进行四舍五入式取整，则要用函数round。当round(number，digits)函数的第二个参数为0时，就可以对第一个参数进行四舍五入式取整，所以在编辑栏将公式修改为“=round(D2/360,0)”即可，如图3-29所示。图3-29用round函数取整如果要进行四舍五入式取整，则要用函数round。当round63函数int(number)的功能是向下取整（数轴上离左边最近的整数），如图3-30所示。图3-30int函数解释函数int(number)的功能是向下取整（数轴上离左边最近64所以，int(6.4)=int(6.7)=6，int(-6.4)=int(-6.7)=-7。函数round(number,digits)的功能是进行四舍五入运算，功能解释如表3-1所示。number1263.472digits-2-1012四舍五入的位数十位个位取整保留1位小数保留2位小数结果1300126012631263.51263.47表3-1round函数解释所以，int(6.4)=int(6.7)=6，int(-6.653.3数据的修整在一段较长的时间内，由于普通的、持续的、决定性等基本因素的作用，总体往往呈现逐渐向上或向下变动的趋势，如图3-31所示。图3-31明显的向上趋势3.3数据的修整在一段较长的时间内，由于普通的、持续的、66在这样的趋势中，也不排除受一些偶然因素或不规则因素的影响，出现与整体趋势相差很大的极端数据，如图3-32中箭头所对应的数据所示。移动平均法就是从时间数列的第一位数值开始，按一定项数求平均数，逐项移动，形成一个新的动态数列。常用的移动平均法有三项移动平均法和四项移动平均法。图3-32局部的数据异动在这样的趋势中，也不排除受一些偶然因素或不规则因素的影响，出673.3.1三项移动平均法例：计算图3-33所示的表格中，商品销售额的三项移动平均数。分析：选择单元格区域A1:B13，单击“插入”|“散点图”|“仅带数据标记的散点图”按钮，如图3-34所示。图3-332004—2015年销售额统计图3-34插入散点图3.3.1三项移动平均法图3-332004—201568结果得到图3-35所示的散点图，从散点图可以直观地看出，第3个点明显偏小，而第6个点明显偏大，这可能是由不确定因素的影响造成的。在这种情况下，可以通过移动平均法对数据做修整，尽量排除不确定因素对数据造成的影响。图3-35原始数据的散点图结果得到图3-35所示的散点图，从散点图可以直观地看出，第369图3-36选择“平均值”命令图3-37计算三项平均数图3-36选择“平均值”命令图3-37计算三项70图3-38计算三项移动平均数图3-39修整后的数据散点图图3-38计算三项移动平均数图3-39修整后的数据散713.3.2四项移动平均法例：计算图