Excel表格数据分析及处理教程

Excel2007数据分析及应用目录常用函数4321分类汇总数据透视图表制作第一章常用函数函数的概述数学函数逻辑函数日期和时间函数文本函数查找和引用函数第1节函数的概述函数的基本概念函数的分类使用函数使用函数的优势获得Excel函数的帮助预先定义，执行计算、分析等处理数据任务的特殊公式分为12类、356个函数直接使用、利用“插入函数”对话框简化公式、实现特殊运算、实现智能判断、提高工作效率使用“函数参数”对话框使用Excel帮助如果函数以公式的形式出现，请在函数名称前面键入等号=函数的结构SUM(A10,B5:B10,50,37)1.1函数的基本概念函数名参数，可以是常量、逻辑值、数组、错误值或单元格引用等Excel2007的函数是指预先定义好，执行计算、分析等处理数据任务的特殊公式1.2函数的分类Excel2007提供了的函数。可分为12类：分类功能简介数据库函数对数据清单中的数据进行分析、查找、计算等日期与时间对日期和时间进行计算、设置及格式化处理工程函数用于工程数据分析与处理信息函数对单元格或公式中数据类型进行判定财务函数进行财务分析及财务数据的计算逻辑函数进行逻辑判定、条件检查统计函数对工作表数据进行统计、分析查找函数查找特定的数据或引用公式中的特定信息文本函数对公式、单格中的字符、文本进行格式化或运算数学函数进行数学计算等外部函数进行外部函数调用及数据库的链接查询等功能自定义函数用户用vba编写，用于完成特定功能的函数利用“插入函数”对话框使用函数1.3使用函数选中要显示结果的单元格在“公式”选项卡“函数库”组，单击“插入函数”简化公式函数可以使一些复杂的公式更易于使用，使复杂的数学表达式输入更加简化。比如，要计算B3:D7区域中数据的平均值时，使用公式计算量大，需要写成：“=(B3+B4+B5+B6+B7+C3+C4+C5+C6+C7+D3+D4+D5+D6+D7)/15”而使用函数将相当简单，只要写成：“=AVERAGE(B3:D7)”1.4使用函数的优势实现特殊运算在日常的工作中，有一些计算使用公式不但复杂，而且不具有特定的专业知识还无法表达，那么就可以使用现有的函数进行特殊运算例如，按揭贷款的等额分期偿还额的计算必须用到高等数学的知识，而使用PMT函数(计算在固定利率下，贷款的等额分期偿还额)就相当简单PMT(rate,nper,pv,fv,type)1.4使用函数的优势贷款利率付款期总数未来值现值付款时间实现特殊运算1.4使用函数的优势贷款利率付款期总数现值实现智能判断用函数还可以实现智能判断例如，在学校，老师统计学生的考试成绩，如果，其成绩大于等于80则为“优秀”,如果成绩大于等于59且小于80则为“及格”,否则为“不及格”。这类问题用人工计算很不方便，可以使用函数来进行自动化处理1.4使用函数的优势实现智能判断1.4使用函数的优势提高工作效率在学校的期末总结中，常会需要按学生的考试成绩进行排位的问题，当然可以使用“排序”按钮来完成，但是这样做比较麻烦，如果使用排位函数RANK就比较方便，尤其是纪录很多、相同分数很多的情况下，不用RANK函数其工作量不可想像RANK(number,ref,order)1.4使用函数的优势需要排位的数字引用的区域，绝对地址排位的方式提高工作效率1.4使用函数的优势由于Excel2007的函数众多，功能很强，所以我们要使用Excel的帮助文件。Excel2007提供了帮助功能：“函数参数”对话框和Excel的帮助。函数参数对话框是寻找帮助的最好办法。1.5获得Excel函数的帮助帮助的链接1.5获得Excel函数的帮助使用Excel帮助有关函数的帮助非常全面。在每个函数的帮助主题中都可以找到下面的内容–函数的语法–“注释”提供了函数的疑难解答–实例–相关函数1.5获得Excel函数的帮助数学函数I.基本数学函数II.舍入和取整函数III.求和函数第2节数学函数2.1数学函数-基本数学函数举例：用函数RAND函数为学生随机排号学校组织考试时，经常需要将学生的学号打并随机编排考号，使用RAND随机函数实现以上功能。RAND函数的格式：RAND()在单元格D2内输入公式“=RAND()”并向下填充，这样在D列各单元格内生存了0-1之间的任意数2.1数学函数-基本数学函数举例：用函数RAND函数为学生随机排号按列D“排序”,可以看到B、C二列已无序排列,再在A2单元格中输入第一个考号,并按序列向下填充,将D列删除或清空数据2.1数学函数-舍入和取整函数ROUND按指定位数四舍五入数值ROUNDUP、ROUNDDOWN按指定位数向上或向下四舍五入数值TRUNC将数值的小数部分截去，返回整数INT将数值向下舍入计取整ODD、EVEN将数值舍入为奇或偶数2.1数学函数-求和函数SUM计算某一单元格区域中所有数值之和SUBTOTAL返回数据清单或数据库中的分类汇总SUMIF根据指定条件对若干单元格、区域或引用求和SUMPRODUCT计算数组间对应的元素相乘，并返回乘积之和SUMSQ计算所有参数的平方和SUMX2MY2计算两数组中对应数值的平方差之和SUMX2PY2计算两数组中对应数值的平方和的总和SUMXMY2计算两数组中对应数值之差的平方和2.1数学函数-求和函数举例：某学校的考试成绩统计表，要统计女同学的英语成绩总和2.1数学函数-求和函数举例：某学校的考试成绩统计表，要统计女同学的英语成绩总和。此例问题可以使用SUMIF函数SUMIF(range，criteria，sum_range)条件判断的单元格区域判定条件需要求和的单元格、区域或引用2.1数学函数-求和函数举例：某学校的考试成绩统计表，要统计女同学的英语成绩总和。此例问题可以使用SUMIF函数在求和的单元格输入公式：=SUMIF(B2:B4,”=女”,F2:F9)即得到结果2.1数学函数-求和函数举例：COUNTIF功能：为满足指定条件的单元计数格式：Countif(计数区域,“计数条件”)=countif（D2:D9,“80”）逻辑函数是用来判断真假值，或者进行复合检验的Excel函数。在Excel中提供了七种逻辑函数第3节逻辑函数IF对指定的条件计算结果为TRUE或FALSE，返回不同的结果IFERROR如果公式计算出错误则返回您指定的值；否则返回公式结果AND当AND的参数全部为TRUE时，返回结果为TRUE，否则为FALSEOR当OR的参数中，如果任一参数为TRUE，返回结果TRUE，否则为FALSENOTNOT函数用于对参数值求反TRUE返回逻辑值TRUEFALSE返回逻辑值FALSE第3节逻辑函数举例：IF在学校的管理工作中，常常要对学生的考试成绩进行统计分析。假定现有某班级的考试成绩，我们按照各科平均分对学生的考试成绩进行综合评定。评定规则：如果平均分超过90时，评定为优秀，如果平均分小于90并且超过60则认为是合格的，否则记作不合格第3节逻辑函数举例：根据规则，我们在综合评定中使用IF函数IF(logical_test,value_if_true,value_if_false)logical_test为TRUE时返回的值测试表达式logical_test为FALSE时返回的值第3节逻辑函数举例：根据规则，我们在综合评定中使用IF函数(以B7为例）：“=IF(B660,IF(B690,”优秀“,”合格“),”不合格“)”不及格E260E280E290E270及格中等良好优秀为英语成绩建立五级分转换1、if(E260,if(E270,if(E280,if(E290,优秀，良好)，中等)，及格)，不及格）第3节逻辑函数日期函数=TODAY()逻辑函数=IF(F40,IF(F430,,即将到期),过期)第3节逻辑函数举例：ORANDOR(logical1,logical2,……,logicaln)功能：当只要有一个参数的计算结果为TRUE时,OR函数的计算结果为TRUE,否则，计算结果为FALSEAND(logical1,logical2,……,logicaln)功能：当所有参数的计算结果都是TRUE时,AND函数的计算结果为TRUE,否则，计算结果为FALSE第3节逻辑函数举例：ORAND第3节逻辑函数举例：ORAND第4节日期和时间函数DATA返回当前日期和时间NOW返回当前日期和时间TODAY返回当前日期YEAR返回日期中的年份MONTH返回日期中的月份DAY返回日期的天数HOUR返回时间值的小时数第4节日期和时间函数第5节文本函数LEFT根据所指定的字符数，返回文本字符串中左边的几个字符MID返回文本字符串中从指定位置开始的特定数目的字符RIGHT根据所指定的字符数，返回文本字符串中右边的几个字符LOWER将一个文字串中的所有大写字母转换为小写字母，但不改变文本中的非字母的字符UPPER将文本转换成大写形式PROPER将文本字符串的首字母及任何非字母字符之后的首字母转换成大写。将其余的字母转换成小写Trim清除字符串中的空白。EXACT比较两个字符串，如果它们完全相同，则返回TRUE；否则，返回FALSE第5节文本函数商品编号品名单位商品规格上报数量0101001冠心宁支,10ml3000000101014骨瓜支,2ml:10mg2000000101016醒脑静10ML支,10ml2000000101017醒脑静2ML支,2ml1840000103001扶10袋盒,50mg*10袋2477000103002扶6袋盒,50mg*6袋00103010扶16袋盒,50mg*16袋001050091MG注射用胸腺五肽瓶,1mg0010501210MG注射用胸腺五肽瓶,10mg2554000105010胸腺五肽1MG（预充式）支,1ml：1mg2360000105011胸腺五肽10MG（预充式）支,1ml：10mg4521000103005双丹片（48片）瓶，00103006双丹片（72片）瓶，00203008尼群地平（48粒）盒，00203009尼群地平（60粒）瓶，0第5节文本函数尊敬的公司领导您好：今日销售100笔，销售金额2000万9097元,本月累计销售金额40000万2321元各药品本月累计销售明细如下：全部第一段第二段尊敬的公司领导您好：今日销售100笔，销售金额2000万9097元,本月累计销售金额40000万2321元各药品本月累计销售明细如下：冠心宁330600支,骨瓜822000支,醒脑静10ML166560支,醒脑静2ML84000支,扶10袋47700盒,扶6袋0盒,扶16袋0盒,1MG注射用胸腺五肽0瓶,10MG注射用胸腺五肽400瓶,胸腺五肽1MG（预充式）6000支,胸腺五肽10MG（预充式）2100支,双丹片（48片）0瓶，双丹片（72片）0瓶，尼群地平（48粒）0盒，尼群地平（60粒）0瓶，肠蠕医用胶贴3200袋，5*3.5止血纱1200片，5*7.5止血纱4600片,4UG加压素106200支,15UG加压素5400支,胸腺法新6400瓶,奥曲肽0.05MG（中和）9000瓶,奥曲肽0.01MG（中和）0瓶,生长抑素3.0MG（中和）300瓶,尊敬的公司领导您好：今日销售100笔，销售金额2000万9097元,本月累计销售金额40000万2321元各药品本月累计销售明细如下：冠心宁330600支,骨瓜822000支,醒脑静10ML166560支,醒脑静2ML84000支,扶10袋47700盒,扶6袋0盒,扶16袋0盒,1MG注射用胸腺五肽0瓶,10MG注射用胸腺五肽400瓶,胸腺五肽1MG（预充式）6000支,胸腺五肽10MG（预充式）2100支,双丹片（48片）0瓶，双丹片（72片）0瓶，尼群地平（48粒）0盒，尼群地平（60粒）0瓶，肠蠕医用胶贴3200袋，5*3.5止血纱1200片，5*7.5止血纱4600片,4UG加压素106200支,15UG加压素5400支,胸腺法新6400瓶,奥曲肽0.