商务数据分析基础 课件 第四章 数据处理

第四章数据处理目录1、数据清洗2、数据加工3、数据修整1数据清洗1.1数据一致性处理1.2缺失数据的处理1.3删除重复数据1.1数据一致性处理通过统计调查收集上来的数据,经常会出现同一字段的数据格式不一致的问题,如图4-1所示。这会对后续的数据分析造成影响,所以必须对数据格式进行处理。图4-1源数据下面就以图4-1为例,将“身高”字段中的数据去掉字符“cm”。(1)选中C列,如图4-2所示。(2)选择“查找和选择”→“替换”命令,如图43所示。图

4-2

选中

C

列4-3

选择

“

替换”

命令(3) 在“查找和替换”对话框中选择“替换”选项卡,在“查找内容”文本框中输入“cm”,设置“替换为”为空。单击“全部替换”按钮完成替换,如图4-4所示。替换后的结果如图4-5所示。图

4-4

输入查找内容图

4-5

替换后的结果1.2缺失数据的处理样本清单中,如果单元格为空,则认为存在缺失数据。缺失数据通常用以下两种方法替换:(1)用样本均值或众数代替缺失值。(2)将缺失值数据删除。首先要发现缺失值。一般用定位条件来查找缺失数据的单元格。下面演示将“年龄”字段中的空值替换为“20”。1)选中年龄所在的E列,选择“查找和选择”→“定位条件”命令,如图4-6所示。图4-6选择“定位条件”命令

2)在“定位条件”对话框中,选中空值单选按钮,如图4-7所示。3)单击“确定”按钮,E列所有的空白单元格呈选中状态,如图4-8所示。

图

4-7

选中

“

空值”

单选按钮图

4-8

选中所有空值

(4) 输入替代值“20”,按<Ctrl+Enter>组合键确认,结果如图4-9所示。图4-9结果呈现1.3删除重复记录在遇到Excel表格里有很多重复项的时候,如何删除相同内容,只保留一个呢?删除重复项的操作极其简单,只需单击数据表的任意位置,再单击“数据”选项卡中的“删除重复值”按钮即可,如图4-10所示。图4-10删除重复值2数据加工2.1

数据转置2.2

字段分列2.3

字段匹配2.4

数据抽取2.4

数据计算2.1数据转换在Excel应用过程中,有时需要把行数据转置成列数据,或者把列数据转置成行数据,因为一条一条粘贴过于烦琐,这里就需要用到Excel中的数据转置功能。操作方法是:先复制好横行数据,然后单击“开始”选项卡→“剪贴板”组中的“粘贴”下拉按钮,在下拉列表中单击“转置”按钮即可,如图4-11所示。图4-11数据转换2.2

字段分列字段分列就是将一个字段分为多个字段。操作如下:(1)将A列中的日期分成年、月、日。首先将这组日期选中,如图4-12所示。(2)选择“数据”选项卡,单击“分列”按钮,如图4-13所示。图4-12源数据图4-13单击“分列”按钮(3) 在文本分列向导界面,选择“分隔符号”单选按钮,单击“下一步”按钮,如图4-14所示。图4-14文本分列向导步骤1(4) 在“分隔符号”区中勾选“其他”复选框,在其右侧框内输入日期之间的小横杠“”,单击“下一步”按钮,如图4-15所示。图4-15文本分列向导步骤2(5) 进入图416所示界面,选择分列的格式,这里默认即可,单击“完成”按钮,如图4-16所示。(6) 分列的结果如图4-17所示。我们看到分成了三列,分别是年、月、日。图

4-16

文本分列向导步骤

3图

4-17

结果呈现2.3

字段匹配字段匹配就是运用VLOOKUP函数将原数据清单中没有但其他数据清单中有的字段匹配过来。(1)以图4-18中的数据为例,我们需要在A:B列中找到与C列对应的B列的数据;打开Excel后,单击D1单元格,如图4-18所示。图418源数据

图4-18源数据(2) 单击“公式”选项卡→“函数库”组→“插入函数”按钮,弹出“插入函数”对话框,在“搜索函数”框中输入vlookup,如图4-19所示。图4-19“插入函数”对话框(3) 搜索到VLOOKUP函数之后会出来四个文本框需要我们填列。下面一一介绍这四个文本框中数据分别代表的含义:第一个文本框中数据表示的是我们在什么数据范围内取的我们需要的数据,此表格中需要在A:B两列中取得C列所对应的B列的数据,所以范围就是A:B列。第二个文本框中数据表示的是需要取在什么条件下的数据,在此表格中我们需要找出C列的所对应的B列数据,所以选中C列。第三个文本框中数据表示的是我们需要取的是数据范围的第几列数,因为我们需要的数据是A:B两列中的B列数据,也就是第二列数据,所以填2;依此类推,如果是数据范围内的第三列数,则填3。第四个文本框中数据表示的是如果是错误的结果则显示为0,如图4-20所示。图4-20设置函数参数(4) 将这四个文本框都填完之后,按<Enter>键就会显示出C列在A:B列中所对应的B列的数据,如图4-21所示。需要注意的是C列的格式要与A列格式一样,否则,数据取不出来。图4-21结果呈现2.4数据抽取数据抽取是指利用原数据清单中某些字段的部分信息得到一个新字段。常用的数据抽取函数有:LEFT()、RIGHT()、YEAR()、MONTH()、WEEKDAY()、DAY()等。WEEKDAY()抽取函数的语法格式为WEEKDAY(date,type),其中,date为日期;type表示返值是从1到7还是从0到6,以及从星期几开始计数,如省略则返值为1到7,且从星期日起计。WEEKDAY()抽取函数的应用如图4-22所示。图422WEEKDAY()抽取函数应用2.5数据计算有时候,我们需要的数据并不存在于数据表中,而是通过对其他字段进行数学计算或函数计算来获取。(1)好评率可以通过公式“好评率=(好评单数/成交单数)×100%”来计算,如图4-23所示。(2)销售天数可以通过公式“销售天数=下架日期-上架日期”来计算,如图4-24所示。图

4-23好评率计算图

4-24

销售天数计算3数据修整3.1三项移动平均法3.2四项移动平均法3.3分析工具加载3.1三项移动平均法案例:计算如图4-25所示的表格中商品销售额的三项移动平均数。分析:在本书配套教学素材中打开文件“商品销售额.xlsx”,选择单元格区域A1∶B13,单击“插入”选项卡→在“图表”组中单击“散点图”下拉按钮绘制散点图,如图4-26所示。图

4-25商品销售额图

4-26

绘制散点图选项结果得到如图4-27所示的散点图,从图4-27中可以直观地看出,第三个点对应的数据明显偏小,而第六个点对应的数据明显偏大,这可能是由不确定因素造成的。在这种情况下,可以通过移动平均法对数据进行修整,尽量排除不确定因素对数据造成的影响。图427原始数据的散点图三项移动平均法的计算思路如下:第一个三项移动平均数=(4205+4632+4000)/3=4279作为2月的数据;第二个三项移动平均数=(4632+4000+4800)/3=4477.33作为3月的数据;依此类推。下面用函数AVERAGE计算三项移动平均数。(1)选中C3单元格,单击“公式”选项卡,选择“自动求和”→“平均值”命令,如图4-28所示。图4-28选择“平均值”命令(2)更改公式为“=AVERAGE(B2∶B4)”,如图4-29所示。图4-29更改计算公式(3)确认C3单元格的计算后,拖动C3单元格的填充柄至C12单元格,如图4-30所示。(4)修整后的散点图如图4-31所示图

4-30填充单元格图

4-31

结果呈现四项移动平均因无法居中对齐中间项,因此需要进行一次正位。案例:计算如图4-32所示的表格中商品销售额的四项移动平均数。3.2四项移动平均图432源数据(2)确认C3单元格的计算后,拖动C3单元格的填充柄至C11单元格。(3)在D4单元格中使用公式“=AVERAGE(C3∶C4)”,如图4-34所示。图

4

34

使用公式(4)确认D4单元格的计算后,拖动D4单元格的填充柄至D11单元格。(5)修整后的散点图如图4-35所示。图4-35修整后的散点图分析工具库的加载方法如下:(1)选择“文件”→“选项”命令,如图4-36所示。(2)打开“Excel选项”对话框,选择“加载项”,在