第4章 Excel数据处理

第4章Excel数据处理4.1数据清洗4.2数据抽取4.3数据合并4.4数据计算14.1数据清洗4.1.1缺失值的处理4.1.2重复值的处理4.1.3异常值的处理4.1.4不规范数据的处理24.1.1缺失值的处理1．找出空值找出空值的方法有很多种，其中筛选空值和定位空值是两种典型的方法。2．处理空值在数据清洗过程中，可以根据需要对空值进行处理。下面介绍3种处理空值的方法。（1）删除空值（2）保留空值（3）使用替代值34.1.2重复值的处理1．查找重复值查找重复值有两种方法：一种是使用COUNTIF()函数，另一种是使用条件格式功能。2．删除重复值在Excel中使用删除重复值功能可以删除重复值，具体操作步骤如下。44.1.2重复值的处理（1）在工作表中选择A1:F18单元格区域，在“数据”选项卡“数据工具”组中单击“删除重复值”按钮，弹出“删除重复值”对话框，仅保持“学号”复选框的勾选，如下图所示。54.1.2重复值的处理（2）单击“确定”按钮，重复值删除完成后会弹出提示对话框，单击“确定”按钮，如下图所示。64.1.3异常值的处理异常值是指原始数据中个别明显偏离其余数据的值。异常值有两种处理方法：一种是删除异常值，如果异常值对数据分析影响不大，则直接删除异常值；另一种是修改异常值，可以在合理的情况下将异常值修改为正常值。74.1.4不规范数据的处理1．处理合并单元格处理合并单元格的常用方法是取消合并单元格并进行相应填充。2．删除多余空行工作表中多余空行会对数据处理和数据分析造成误导，因此必须删除。对于数据量较少的情况，可以直接查找并删除多余空行。对于数据量较多的情况，可以通过定位空值的方法查找并删除多余空行。84.1.4不规范数据的处理3．删除分类汇总数据行分类汇总数据中也会存在空值。选择空值所在的列，打开“定位条件”对话框定位空值，然后单击“删除”→“删除工作表行”命令，即可删除分类汇总数据中的所有包含空值的行。94.2数据抽取4.2.1查找引用4.2.2字段拆分104.2.1查找引用1．MATCH()函数MATCH()函数是查找指定数值在指定单元格区域中的相对位置；其语法格式为MATCH(lookup_value,lookup_arrary,match_type)。2．INDEX()函数INDEX()函数是根据行列位置的坐标抽取对应的数值；其语法格式为INDEX(array,row_num,column_num)。114.2.1查找引用3．查找引用的应用MATCH()函数可以查找指定数值的位置，INDEX()函数可以根据指定数值位置的坐标抽取该数值。使用这两个函数与数据验证功能，可以更加灵活地查找数据。124.2.2字段拆分1．LEFT()函数LEFT()函数是从字符串的左侧开始拆分字符串，从文本字符串的第一个字符开始返回指定个数的字符。2．RIGHT()函数RIGHT()函数是从字符串的右侧开始拆分字符串，从文本字符串的最后一个字符开始返回指定个数的字符。134.2.2字段拆分3．MID()函数MID()函数是从字符串的中间位置开始拆分字符串，从文本字符串中指定的位置开始返回指定个数的字符。4．分列功能分列功能有分隔符号分列和固定列宽分列两种方法。144.3数据合并4.3.1数据表合并4.3.2字段合并154.3.1数据表合并1．横向连接在进行数据处理时，可能会遇到一个数据表中缺失的列数据与另一个数据表中的列数据相对应的情况，即要查找一个数据表中某一列的数据需要对另一个数据表中每一行的数据进行匹配查找，这种对行合并操作的方式称为横向连接。164.3.1数据表合并2．纵向连接在进行数据处理时，还可能会遇到一个数据表中缺失的列数据与另一个数据表中的行数据相对应的情况，即要查找一个数据表中某一列的数据需要对另一个数据表中每一列的数据进行匹配查找，这种对列合并操作的方式称为纵向连接。174.3.2字段合并1．连接符&使用连接符&可将多个单元格合并在一起。2．CONCATENATE()函数CONCATENATE()函数是将多个字符串合并为一个字符串。184.3.2字段合并在E8单元格中输入“广东省深圳市南山区世界之窗”，将光标移至E8单元格右下角，当光标变为十字形时按住鼠标左键拖动至E13单元格，填充数据，单击“自动填充选项”下拉按钮，在打开的下拉列表中选中“快速填充”单选按钮，如下图所示。194.3.2字段合并这样各省、市、区和地址合并为相应的详细地址，如下图所示。204.4数据计算4.4.1字段计算4.4.2数据标准化214.4.1字段计算1．算术运算Excel中常用的算术运算符及其用法如下表所示。224.4.1字段计算2．比较运算Excel中常用的比较运算符及其用法如下表所示。234.4.2数据标准化1．0-1标准化0-1

标准化又称离差标准化或归一化，是指通过对一组数据最大值、最小值的线性变换处理，使数据落在[0,1]区间内。2．z-score标准化z-score标准化又称标准差标准化。经过z-score标准化后的数据均符合标准正态分布，即均值为0、标准差为1。24本章小结本章首先介绍了数据清