数据分析基础（第2版）课件 第3章 数据的处理

数

据

分

析

基

础【复习引入】1.数据分析的六个步骤？1.确定目的2.收集数据3.处理数据4.分析数据5.展现数据6.撰写报告3.处理数据第三章数据的处理3.1数据清洗3.2数据加工3.3数据修整3.4练习第1次课【教学内容】3.1数据清洗3.2.1字段分列3.2.2字段抽取【知识目标】1.了解数据不一致、数据错误、数据缺失、数据重复给数据分析工作带来的危害。2.掌握函数if、left、right、mid、year、month、day、weekday功能和参数要求。【技能目标】1．灵活通过“查找和替换”对话框以及函数对数据进行一致性处理。2．熟练运用字段分列对字段进行拆分、变形。3．灵活运用Excel公式与函数对数据进行抽取。【素质目标】1．认识数据处理的重要性，培养重视基础工作的职业素养。2．灵活掌握数据处理的各种方法和技巧，培养创新意识。数据清洗3.1数据清洗就是对格式错误的数据进行纠正，将错误的数据纠正或删除，将缺失的数据补充完整，将重复的数据删除。下面数据有哪些问题？第三章

数据的处理3.1数据清洗抢答：3.1.1数据一致性处理第三章

数据的处理3.1数据清洗问题：

如何对出生日期、身高进行一致性处理？3.1.2缺失数据处理第三章

数据的处理3.1数据清洗

数据清单中，单元格如果出现空值，就认为数据存在缺失。缺失数据的处理方法通常有以下3种：

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

将有缺失数据的记录删除；

保留该记录，在要用到该数据做分析时，将其临时删除案例操作（42-43页）3.1.3重复数据处理第三章

数据的处理3.1数据清洗

重复数据是指每个字段都完全相同的记录。如果一条记录重复出现，会影响分析的结果，因此在分析数据之前必须将重复记录删除案例操作（43页）学生操作第三章

数据的处理3.1数据清洗倒计时数据加工3.2数据加工的手段第三章

数据的处理3.2数据加工字段分列字段匹配数据转换数据计算字段抽取3.2.1字段分列第三章

数据的处理3.2数据加工所谓字段分列，就是将一个字段分成多个字段。例如将一个完整的日期分成年、月、日三列，或者将身份证号码中的年、月、日分离出来。第三章

数据的处理3.2数据加工第1步：确定分列依据。可以是“分隔符号”，也可以是“固定宽度”。第三章

数据的处理3.2数据加工第2步：确定具体的分隔符号或分列宽度。第三章

数据的处理3.2数据加工第3步：确定各列数据类型、放置位置、是否导入？3.2.2字段抽取第三章

数据的处理3.2数据加工字段抽取是指利用原数据清单中某些字段的部分信息得到一个新字段。1．字符串抽取函数

常用的字符串抽取函数有Left、Right、Mid。

例如

left（”computer”,3）=com例如

right（”computer”,3）=ter例如

mid（”computer”,4,2）=puLeft(文本字符串,截取的长度)——从文本字符串的左边截取指定个数的字符。Right(文本字符串,截取的长度)——从文本字符串的右边截取指定个数的字符。Mid(文本字符串,起点位置,截取的长度)——从文本字符串的中间某个位置开始，截取指定个数的字符。第三章

数据的处理3.2数据加工3.2.2字段抽取案例：从身份证号码中提取省份编码、性别编码第三章

数据的处理3.2数据加工2．日期抽取函数

常用的日期抽取函数有Year、Month、Day、Weekday。

Year(日期)——从日期型数据中提取年份。例如year(“2024-12-19”)=2024

Month(日期)——从日期型数据中提取月份（1～12）。例如month（“2024-12-19”）=12

Day(日期)——从日期型数据中提取日（1～31）。例如day（“2024-12-19”）=19第三章

数据的处理3.2数据加工2．日期抽取函数

Weekday(日期，2)——返回日期是一个星期中的第几天。此时参数2取2表示把星期一当作一个星期的第一天。因此返回值1表示星期一，2表示星期二，3表示星期三，4表示星期四，5表示星期五，6表示星期六，7表示星期天。注意：weekday函数如果省略第二个参数，就是把星期天当作一周的第一天。返回值1表示星期天，2表示星期一……。第三章

数据的处理3.2数据加工案例：从日期数据中提取年、月、日、星期数（47页）3.2.2字段抽取第三章

数据的处理3.2数据加工问题：

D2单元格能否用公式”=year(c2)“从身份证号码中提取出生年份？第三章

数据的处理3.2数据加工学生操作：1.在“个人信息”工作表从“身份证号码”中提取出生年、月、日、省份编码、性别编码。2.在“日期函数”工作表中从“成交日期”中提取年、月、日、星期。3.2.2字段抽取学生操作第三章

数据的处理3.2数据加工倒计时小结第三章

数据的处理3.2数据加工数据处理数据清洗数据一致性处理1、查找替换2、运用函数（if）缺失数据处理1、查找定位2、批量输入（Ctrl+Enter）重复数据处理

“数据”|“删除重复值”数据加工字段分列1、分列依据

2、数据类型设置

3、放置位置字段抽取函数：left、right、mid、year、month、day、weekday课后练习：操作（4）打开“数据处理-课后练习.xlsx”文件，将“数据分列”工作表中A列数据的“姓名”“家庭地址”“邮编”分列显示在C、D、E列，结果如图3-4-5所示。第三章

数据的处理3.4练习图3-4-5数据分列结果第2次课【复习引入】1.找一个学生演示从身份证号码中提取省份编码。2.编码31是哪个省？41、51、61、71呢？【教学内容】3.2.3

字段匹配【知识目标】1.掌握Excel内置函数vlookup、index、match的功能和参数要求。【技能目标】1.熟练运用Vlookup、index、match函数查找数据。2.灵活运用Excel公式与函数对数据进行转换。【素质目标】1.认识数据处理的重要性，培养重视基础工作的职业素养。2.灵活掌握数据处理的各种方法和技巧，培养创新意识。3.熟练掌握Excel各种内置函数的功能和应用，践行强国先强己的责任担当。3.2.3字段匹配第三章

数据的处理3.2数据加工所谓字段匹配，就是将原数据清单中没有，但其它数据清单中有的字段匹配过来。工作表“个人信息”

工作表“省份编码”1、精确匹配第三章

数据的处理3.2数据加工利用Vlookup函数精确匹配（查找）查找值查找区域（其第1列必须含有查找值）返回查找区域的第几列值false表示精确查找学生操作第三章

数据的处理3.2数据加工倒计时2、模糊匹配第三章

数据的处理3.2数据加工当Vlookup函数第4个参数为True时为模糊匹配（查找）。模糊匹配并不是匹配最接近的数值，而是向下匹配。例如在F列查找17，模糊匹配的话，不是匹配与17最接近的18，而是向下与0匹配。模糊匹配一般应用于数值的区间匹配，区间的下限称为阈值。第三章

数据的处理3.2数据加工True表示模糊匹配学生操作第三章

数据的处理3.2数据加工倒计时提问：第三章

数据的处理3.2数据加工工作表“省份编码”工作表“个人信息”能否在工作表“个人信息”I2单元格用公式=vlookup（G2，省份编码！E:F，1，0）将艾城的省份匹配过来？3、Index+Match匹配第三章

数据的处理3.2数据加工index（数据区域，行，[列]）的功能是根据给定的行和列返回数据区域中对应的值。例如：Index（A5:F100，3，6）返回A5:F100中第3行第6列单元格值。如果Index函数的第1个参数只有一列，第3个参数就可以省略。例如：Index（A1:A100，5）返回A1:A100中第5个单元格值。Index（A5:F100，3）会报错，因为没有给出列号。第三章

数据的处理3.2数据加工Match（查找的值，查找区域，[匹配方式]）的功能是返回“查找值”在“查找区域”出现的位置序号。查找区域必须是一维数组，如果单元格区域，就只能是一列或一行；匹配方式：0（false）表示精确，1（True）表示模糊。例如：Match（C2，F:F，0）表示查找C2单元格的值在F列中出现的位置。3、Index+Match匹配随堂练习第三章

数据的处理3.2数据加工（1）如图所示数据清单，在工作表“个人信息”某单元格中输入公式“=match(G2,省份编码！F:F,0)”，则该单元格结果是（）。 A．上海· B．31 C．8 D．7（2）如图所示数据清单，在工作表“个人信息”某单元格中输入公式“=index(省份编码！E:F,3,1)”，则该单元格的值是（）。A．澳门 B．82 C．福建

D．35工作表“省份编码”工作表“个人信息”案例第三章

数据的处理3.2数据加工例6：使用Index+Match函数，根据工作表“省份编码”中E:F列资料，完成每个人的省份匹配。工作表“省份编码”工作表“个人信息”第三章

数据的处理3.2数据加工工作表“省份编码”工作表“个人信息”返回G2在F列中出现的序号8。该序号作为index函数的行号。学生操作第三章

数据的处理3.2数据加工倒计时小结第三章

数据的处理3.2数据加工字段匹配Vlookup函数在查找区域（参数2）的第1列中找到要查找值（参数1），然后返回该行的第n列（参数3）数据。参数4为0表示精确查找，为1表示模糊查找。模糊查找时，第1列的阈值（区间下限）需要从小到大排列。index函数在查找区域（参数1）找到m行（参数2）n列（参数3）值。如果查找区域只有一行或一列数据，参数3不需要。否则参数3必不可少。match函数在查找区域（参数1）找到查找值（参数2）出现的序号。参数3为0表示精确查找，为1表示模糊查找。查找区域只能一列或一行。课后练习：操作（1）打开“数据处理-课后练习.xlsx”文件，在“成绩查询”工作表中分别用Vlookup函数和Index函数查询高考分数，如图3-4-2所示。第三章

数据的处理3.4练习图3-4-2高考分数查询第3次课【引入新课】上次课的数据加工，学习了三个较难的查找函数，Vlookup、index、match函数。本次课放松一下，学习几个简单实用的数据处理技巧。【教学内容】3.2.4数据转换3.2.5数据计算【知识目标】1.掌握Excel数据的快速填充。2.掌握Excel内置函数if、isodd、int、round、today、date的功能和参数要求。【技能目标】1.灵活运用Excel公式与函数对数据进行转换、计算。【素质目标】1．认识数据处理的重要性，培养重视基础工作的职业素养。2．灵活掌握数据处理的各种方法和技巧，培养创新意识。3.2.4数据转换第三章

数据的处理3.2数据加工问题1．如何将左边横放数据改成右边竖放？第三章

数据的处理3.2数据加工解决办法：先复制原始数据，再粘贴（粘贴时选择“转置”）第三章

数据的处理3.2数据加工问题2．如何将多列数据转成一列？分解问题：1.如何将2022年1月的数据放到2021年数据底下？2.公式=C3，往下、往右填充会怎样？第三章

数据的处理3.2数据加工解决办法：（1）在2021年数据最下方单元格B15中输入公式“=C3”，然后抓住B15的填充柄向右填充到C15，松手后再抓住B5：C15的填充柄继续向下填充。（2）复制B3:B38，并粘贴值。第三章

数据的处理3.2数据加工问题3．如何将左边一列数据转成右边多列？温馨提示：1.找寻公式的规律第三章

数据的处理3.2数据加工解决办法：（1）先利用数据的快速填充填入a1、a2、a3、a4、a5……（2）再利用查找替换在a前面添加等号，将其转变成公式。学生操作第三章

数据的处理3.2数据加工倒计时3.2.5数据计算第三章

数据的处理3.2数据加工

有时候，我们需要的数据并不存在于数据清单中，而是要通过对其他字段进行数学计算或函数计算来获取。

没有固定的方法，需要创新思维，灵活运用各种方法和手段。案例1第三章

数据的处理3.2数据加工如图所示工作表，在单元格G列计算“销售额”，在H列计算“好评率”，在I列计算“上架天数”，在J列计算星级。函数资料：Today()——返回当天的日期，不需要参数。第三章

数据的处理3.2数据加工教师案例演示：I2=TODAY()-B2。J2=IF(H2>=90%,"*****",IF(H2>=80%,"****",IF(H2>=70%,"***",IF(H2>=60,"**","*"))))。第三章

数据的处理3.2数据加工1、日期为什么可以相减？思考：2、下表上市天数C2=today（）-B2？第三章

数据的处理3.2数据加工H6的值是90%，为什么J6显示四星而不是五星？提示：增加小数位数看看？思考：第三章

数据的处理3.2数据加工取整函数int

函数Int(数值)的功能是向下取整（数轴上左边最近的整数）。抢答：Int(6.4)=？、Int(6.7)=？、Int(-6.4)=？、Int(-6.7)=？思考：如何用int函数实现四舍五入取整？尝试：数据加0.5再用int取整，结果如何？第三章

数据的处理3.2数据加工四舍五入函数round函数Round（数值，小数位数）的功能是，按给定的小数位数对数值进行四舍五入。数值1263.472小数位数-2-1012结果1300126012631263.51263.47所以，H2的公式可以改成“=int(F2/E2+0.5)”或者“=round(F2/E2,0)”。案例2第三章

数据的处理3.2数据加工如图所示工作表“个人信息”，在单元格K2中计算“出生日期”，在L2中计算“年龄”，在M2中计算“性别”。函数资料：Date(年数,月数,日数)——返回三个数字合成的日期，例如date(2024,10,1)返回日期2024/10/1。Isodd(数值)——判断数值是否为奇数。例如Isodd(5)返回逻辑值True。第三章

数据的处理3.2数据加工教师案例演示：1、单元格K2中使用“=DATE(D2,E2,F2)”计算“出生日期”。2、单元格L2中使用“=(TODAY()-K2)/365”计算“年龄”。3、单元格M2中使用“=IF(ISODD(H2),"男","女")”计算“性别”。学生操作第三章

数据的处理3.2数据加工倒计时小结第三章

数据的处理3.2数据加工数据加工数据转换转置多列转一列一列转多列数据加工if函数嵌套int、round函数date、today函数isodd函数课后练习：操作（2）打开“数据处理-课后练习.xlsx”文件，计算“转化率”工作表中各种商品各环节的转化率（见图3-4-3）。其中“加购物车”环节的转化率=加购物车人数/浏览人数，“交易”环节的转化率=交易人数/加购物车人数。第三章

数据的处理3.4练习图3-4-3转化率计算（3）打开“数据处理-课后练习.xlsx”文件，计算“上市公司”工作表（见图3-4-4）中各企业上市的天数和上市的年数。操作题第三章

数据的处理3.4练习图3-4-4“上市公司”工作表第4次课【复习】

随堂练习：某数据表中列出了某学校5000个学生的身份证号码，如果要将这5000个学生的出生日期从身份证号码中读取出来，以下什么操作方法是错误的。（提示：身份证号码中的第7-14位是出生年月日信息）A.利用函数year（）、month（）、day（）获取B.利用函数mid（）获取C.利用“字段分列”获取D.利用date（）函数获取数据修整3.3【教学内容】3.3数据修整【知识目标】2.理解移动平均法修整数据的思路。【技能目标】1.熟练运用数据分析工具“移动平均”进行数据修整。2.掌握Excel分析工具库的加载。【素质目标】1．认识数据处理的重要性，培养重视基础工作的职业素养。2．灵活掌握数据处理的各种方法和技巧，培养创新意识。第三章

数据的处理3.3数据修整

在一段较长的时间内，由于普通的、持续的、决定性等基本因素的作用，总体往往呈现逐渐向上（见图3-3-1）或向下变动的趋势。图3-3-1明显的向上趋势第三章

数据的处理3.3数据修整

在这样的趋势中，也不排除受一些偶然因素或不规则因素的影响，出现与整体趋势相差很大的极端数据，如图3-3-2中箭头所指的数据。如果直接对这些极端数据进行数据分析，分析的结果可能不准确，所以有必要用一定的数学方法对这些数据进行加工，使数据长期变化的趋势更加明显，为预测总体的未来提供更准确的依据。图3-3-2局部的数据异动第三章

数据的处理3.3数据修整下面介绍如何使用移动平均法对数据进行修整。

移动平均法就是从时间数列的第一位数值开始，按一定项数求平均数，逐项移动，形成一个新的动态数列。

常用的移动平均法有三项移动平均法和四项移动平均法。3.3.1三项移动平均法第三章

数据的处理3.3数据修整例1计算图3-3-3所示的表格中销售额的三项移动平均数计算思路如下：（1）第1个三项移动平均数4273作为中间年份2011年的销售额；

（2）第2个三项移动平均数4488作为中间年份2012年的销售额；依此类推……4273448846845523.3333335680.557705547.3333335941.6666676516.6666676872.6666676921.6666676950学生操作1第三章

数据的处理3.2数据加工开始计时第三章

数据的处理3.3数据修整例2：计算图3-3-3所示的表格中销售额的四项移动平均数。四项移动平均数的计算过程如下。（1）求四项移动平均数：第1个四项移动平均数4417.25暂时放于2011年和2012年之间。第2个四项移动平均数4671暂时放于2012年和2013年之间。依此类推……467151385472.55632.55785.55786.2562606612.568177016.254417.253.3.2四项移动平均法第三章

数据的处理3.3数据修整4417.25467151385472.55632.55785.55786.2562606612.568177016.25（2）因为得到的数据并没有对应某一年，所以继续对所得数据用一次两项移动平均法进行正位：第1个两项移动平均数4544.125放在两个数的中间，作为2012年的销售额。第2个两项移动平均数4904.5作为2013年的销售额。依此类推……4904.54544.125例2：计算图3-3-3所示的表格中销售额的四项移动平均数。学生操作2第三章

数据的处理3.2数据加工开始计时3.3.3加载Excel分析工具库第三章

数据的处理3.3数据修整分析工具库的加载步骤：单击“文件”|“选项”|“加载项”|“转到”|勾选“分析工具库”|“确定”。第三章

数据的处理3.3数据修整加载成功后，会在“数据”选项卡中看到一个新的功能“数据分析”。学生操作3添加“数据分析”功能开始计时第三章

数据的处理3.