《数据清洗课件-第5章 Excel数据清洗与转换》由会员分享，可在线阅读，更多相关《数据清洗课件-第5章 Excel数据清洗与转换（59页珍藏版）》请在金锄头文库上搜索。
1、第5章 Excel数据清洗与转换,5.1 Excel数据清洗概述,5.1.1 Excel简介 Microsoft Excel是一个功能强大的电子表格程序，是微软公司office系列办公软件的组件之一，它不仅可以将整齐而美观的表格呈现给用户，还可以用来进行数据的分析和预测，完成许多复杂的数据运算，帮助使用者做出更加有根据的决策。同时它还可以将表格中的数据通过各种各样的图形、图表的形式表现出来，增强表格的表达力和感染力，广泛地应用于管理、统计财经、金融等众多领域。 Microsoft Excel也是一个复杂的数据管理和分析软件，它可以执行许多繁重而复杂的计算,，帮助用户做出最佳决策。利用Excel
2、可以方便地实现数据清洗功能，通过过滤、排序、绘图等方式可以直观的呈现数据的各种规律。Excel主要用于日常办公和中小型数据集的处理，难以处理海量数据的清理任务。即使是很小的数据集在使用前也需要进行必要的预处理。因此，通过介绍Excel中数据清洗操作，有助于读者了解Excel数据清洗的步骤和方法，掌握一定的操作技能，为后续清洗大型数据集打下良好的基础。,5.1.2 Excel数据清洗与转换方法 Excel数据清洗和转换的基本步骤如下： 1）从外部数据源导入数据。 2）在单独的工作簿中创建原始数据的副本。 3）确保以行和列的表格形式显示数据，并且每列中的数据都相似；所有的列和行都可见；范围内没有空
3、白行。为了获得较佳结果，请使用Excel表。 4）首先执行不需要对列进行操作的任务，例如拼写检查或使用“查找和替换”对话框。 5）然后执行需要对列进行操作的任务。对列进行操作的一般步骤为： a.在需要清理的原始列(A)旁边插入新列(B)。 b.在新列(B)的顶部添加将要转换数据的公式。 c.在新列(B)中向下填充公式。在Excel表中，将使用向下填充的值自动创建计算列。 d.选择并复制新列(B)，然后将其作为值粘贴到新列(B)中。 e.删除原始列(A)，这样，新列B将转换为A。,5.2 Excel数据清洗与转换实现,5.2.1常用数据分析函数介绍,1.IS类函数 IS类函数包括ISBLANK,
4、 ISERR, ISERROR, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISREF和ISTEXT等函数。该类函数用来对某个单元格当前值的类型进行判断，以便知道其类型后，再采取下一步行动，辅助实现数据的清洗。,例如：公式=ISBLANK(A1)，表示对A1单元格是否为空进行判断。如果是空的，则返回值为TRUE，如果不为空，则返回值为FALSE。 例如：公式=ISBLANK()返回FALSE，公式=ISREF(A2)返回TRUE（其中A2为空白单元格）。如果需要计算B1:B5区域的平均值，但不能确定单元格内是否包含数字，则公式AVERAGE(B1:B5)返回错
5、误值#DIV/0!。为了处理这种情况，可以使用公式“=IF(ISERROR(AVERAGE(B1:B5)，引用包含空白单元格，AVERAGE(B1:B5)，就能查出B1:B5区域可能出现空白单元格的情况。,2. 计算统计类函数 (1) SUMSUMIFSUMIFS函数 1) SUM函数 【主要功能】求和操作，用来计算某一个或多个单元格区域所有数值的求和。 【语法格式】=SUM(number1, number2, number3, .)。,【例5-1】在 C4 单元格中输入如下公式：=SUM(A1:D1)，即表示对 A1、B1、C1 和 D1 四个水平方向的连续单元格求和，相当于公式=A1+B1
6、+C1+D1。运行结果如图5-1所示。,2) SUMIF函数 【主要功能】按条件进行求和。根据指定条件对若干单元格、区域和引用求和。即对条件区域进行判断，如果某些单元格满足指定条件，则对求和区域所对应的若干单元格进行求和。 【语法格式】=SUMIF(range,criteria,sum_range)。,a.常用的单条件求和 【例5-2】在C11单元格中输入公式：=SUMIF(B2:B9,一组,C2:C9)，这个公式实际上统计了一组的生产量，最终计算的结果为427。运行如图5-2所示。,b.模糊求和，就是使用通配符的单条件求和 【例5-3】统计严姓员工的生产量，则公式：=SUMIF(A2:A9,
7、严*,C2:C9)，相当于对C2单元格、C4单元格和C9单元格求和，最终结果为333。此公式也可替代为：=SUM(LEFT(A2:A9)=严)*C2:C9)的数组公式，计算结果同样是333。运行如图5-3所示。,c省略第三个参数的单条件求和 SUMIF 函数求和，第三个参数省略时，则对条件区域中的单元格求和。 【例5-4】对生产量大于110件的生产量求和。公式：=SUMIF(C2:C9,110)，计算的结果是333。由于SUMIF的第三个参数省略时，此时求和的单元格区域为：C2:C9，相当于公式=SUMIF(C2:C9,110,C2:C9)的计算结果。运行如图5-4所示。,3) SUMIFS函
8、数 【主要功能】多条件求和。按多个条件对指定单元格、区域和引用求和，扩展了SUMIF的功能。用于计算单元格区域或数组中符合多个指定条件的数字的总和。 【语法格式】=SUMIFS(sum_range,range,criteria,range2,criteria2, )。,【例5-5】在E1单元格中输入一个公式并按【Enter】键，汇总销售额在15000到25000之间的员工销售总额。输入的公式如下：=SUMIFS(B2:B10,B2:B10,=15000,B2:B10,=15000)*(B2:B10=25000)*(B2:B10)。运行如图5-5所示。,(2) COUNTCOUNTIFCOUNT
9、IFS计数函数 1) COUNT函数 【主要功能】计算数字类型数据的个数。 【语法格式】=COUNT(value1,value2,.)。 例如：如果A1=8、A2=、A3=中国、A4=14、A5=“*”、A6=168，则公式=COUNT(A1:A6)，返回3，实际上是对数字单元格进行了统计个数。 2)COUNTIF函数 【主要功能】COUNTIF函数用于计算区域中满足给定条件的单元格的个数。 【语法格式】COUNTIF(range,criteria)。 【例5-6】统计销量大于800的员工人数，在F1单元格中输入一个公式并按【Enter】键，统计销量大于800的员工人数。其中，A列为员工姓名，
10、B列为员工性别，C列为员工销量。输入的公式如下：=COUNTIF(C2:C10,800)。运行如图5-6所示。,【例5-7】计算两列数据中相同数据的个数，在E1单元格中输入一个数组公式并按【Ctrl+Shift+Enter】组合键，计算两列数据中相同数据的个数。输入的数组公式如下：=SUM(COUNTIF(A2:A10,B2:B10)。首先使用COUNTIF函数统计B2:B10单元格区域中的人名是否出现在A2:A10单元格区域中，如果出现，则计数为1，否则为0。然后使用SUM函数对包含1和0的数组求和，统计1的个数，也就是同时出现在A、B两列中的人员姓名的数量。运行如图5-7所示。,【例5-8
11、】统计不重复员工人数，在F1单元格中输入一个数组公式并按【Ctrl+Shift+Enter】组合键，统计不重复员工人数。其中，A列为员工姓名，但是有重复；B列为员工性别；C列为员工销量。输入的数组公式如下：=SUM(1/COUNTIF(C2:C10,C2:C10)。首先使用COUNTIF函数统计C2:C10单元格区域中每个单元格在C2:C10中出现的次数，得到数组2;2;2;1;2;2;1;1;2。用1除以这个数组，数组中的1仍为1，而其他数字都转换为分数。当对这些分数求和时，都会转换为1。例如，某个数字出现3次，那么每一次出现都被1除，即是1/3，出现3次则为3*1/3，等于1，所有通过对1
12、除以数组后的结果求和，可以统计不重复的员工人数。运行如图5-8所示。,3) COUNTIFS函数 【主要功能】COUNTIFS函数用于计算区域中满足多个条件的单元格数目。 【语法格式】COUNTIFS(criteria_range1,criteria1,criteria_range2,criteria2,)。,【例5-9】在F1单元格中输入一个公式并按【Enter】键，统计销量在6001000之间的男员工人数。其中，A列为员工姓名，B列为员工性别，C列为员工销量。输入的公式如下：=COUNTIFS(B2:B10,男, C2:C10, =600, C2:C10, =1000)。运行如图5-9所示
13、。,(3)SUMPRODUCT函数 【主要功能】计算数组元素的乘积之和。在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。 【语法格式】=SUMPRODUCT(array1,array2,.)。,【例5-10】有数组一与数组二，其中数组一为：2,7;3,8;4,9；数组二为：6,3;7,4;8,5，要求这两个数组间对应的元素相乘，并返回乘积之和；即2*6+7*3+3*7+8*4+4*8+9*5。计算结果163，具体公式写法有如下几种。,【例5-11】在F1单元格中输入一个公式并按【Enter】键，统计销售部女员工人数。输入的公式如下：=SUMPRODUCT(B2:B17=女)*1,(
14、C2:C17= 销售部)*1)。运行如图5-11所示。,(4) RANK函数 【主要功能】排序，返回某一数值在一列数值中相对于其他数值的大小排位。 【语法格式】=RANK(Number,Ref,Order)。,【例5-12】在一组语文成绩中，按降序对成绩进行排名，在D2单元格中输入公式=RANK(C2,C$2:C$10)，此公式等同于=RANK(C2,C$2:C$10,0)，输完公式后，在D2单元格中出现数字4，说明甲的成绩排名为第4名；在D2单元格的右下角，将公式向下拖曳，这样就将本组语文成绩按降序排名了。运行如图5-12所示。,(5) RANDRANDBETWEEN随机数函数 1) RAN
15、D函数 【主要功能】返回一个大于等于0小于1的随机数，每次计算工作表（按F9 键）将返回一个新的数值。 【语法格式】=RAND()。,2) RANDBETWEEN函数 【主要功能】返回两个指定数值之间的一个随机数，每次重新计算工作表（按F9键）都将返回新的数值。 【语法格式】=RANDBETWEEN(bottom,top),(6) AVERAGE函数 【主要功能】计算所有参数的算术平均值。 【语法格式】=AVERAGE(number1,number2,.)。 例如：A2=100、A3=60、A4=90、A5=95、A6=78，则这组数据的平均值为=AVERAGE(A2:A6)，返回值为84.6
16、。通过此函数可以计算学生成绩的平均分或用于其他计算平均值的方面。,(7) QUARTILE四分位数 【主要功能】返回一组数据的四分位数。四分位数通常用于在考试成绩之类的数据集中对总体进行分组。 【语法格式】=QUARTILE(array,quart)。 例如：如果某班英语考试成绩为A2=79、A3=88、A4=95、A5=89、A6=70、A7=65、A8=90和A9=96，则公式=QUARTILE(A2:A9,3)，返回值为91.25，即第三个四分位数（第75个百分排位）为91.25。 (8) STDEV函数 【主要功能】计算给定样本的标准偏差。它反映了数据相对于平均值（mean）的离散程度。 【语法格式】=STDEV(number1, number2, .)。 【例5-13】假设一班考试的成绩样本为A2=75、A3=86、A4=78、A5=96、A6=69、A7=92、A8=84和A9=100，则估算所有成绩标准偏差的公式为=STDEV(A2:A9)，其结果等于10.7038044。上述结果反映了一班成绩波动情况（数值越小，说明该班学生间的
