《Excel表格数据分析及处理教程》由会员分享,可在线阅读,更多相关《Excel表格数据分析及处理教程(55页珍藏版)》请在金锄头文库上搜索。
1、Excel 2007数据分析及应用目目 录录常用函数常用函数4321分类汇分类汇数据透视数据透视图表制作图表制作第一章 常用函数u 函数的概述u 数学函数u 逻辑函数u 日期和时间函数u 文本函数u查找和引用函数第1节 函数的概述u 函数的基本概念u 函数的分类u 使用函数u 使用函数的优势u 获得Excel 函数的帮助预先定义,执行计算、分析 等处理数据任务的特殊公式分为12类、356个函数直接使用、利用“插入函数”对话框简化公式、实现特殊运算、实 现智能判断、提高工作效率使用“函数参数”对话框 使用Excel帮助如果函数以公式的形式出现,请在函数名称前面键入等号= =u 函数的结构M(A1
2、0,B5:B10,50,37)1.1 函数的基本概念函数名参数,可以是常量、逻辑值、 数组、错误值或单元格引用等Excel 2007的函数是指预先定义好,执 行计算、分析等处理数据任务的特殊公式1.2 函数的分类Excel 2007提供了的函数。可分为12类:分 类类功 能 简简 介 数据库库函数对对数据清单单中的数据进进行分析、查查找、计计算等 日期与时间时间对对日期和时间进时间进 行计计算、设设置及格式化处处理 工程函数用于工程数据分析与处处理 信息函数对单对单 元格或公式中数据类类型进进行判定 财务财务 函数进进行财务财务 分析及财务财务 数据的计计算 逻辑逻辑 函数进进行逻辑逻辑 判定
3、、条件检查检查 统计统计 函数对对工作表数据进进行统计统计 、分析 查查找函数查查找特定的数据或引用公式中的特定信息 文本函数对对公式、单单格中的字符、文本进进行格式化或运算 数学函数进进行数学计计算等 外部函数进进行外部函数调调用及数据库库的链链接查询查询 等功能 自定义义函数用户户用vba编编写,用于完成特定功能的函数u利用“插入函数”对话框使用函数1.3 使用函数选中要显示结果的单元格在“公式”选项卡“ 函数库”组,单击“ 插入函数”u简化公式函数可以使一些复杂的公式更易于使用, 使复杂的数学表达式输入更加简化。比如,要计 算B3:D7区域中数据的平均值时,使用公式计算 量大,需要写成:
4、 “=(B3+B4+B5+B6+B7+ C3+C4+C5+C6+C7+D3+D4+D5+D6+D7)/15” 而使用函数将相当简单,只要写成: “=AVERAGE(B3:D7)”1.4 使用函数的优势u实现特殊运算在日常的工作中,有一些计算使用公式不但 复杂,而且不具有特定的专业知识还无法表达, 那么就可以使用现有的函数进行特殊运算例如,按揭贷款的等额分期偿还额的计算 必须用到高等数学的知识,而使用PMT函数(计 算在固定利率下,贷款的等额分期偿还额)就相 当简单PMT(rate,nper,pv,fv,type)1.4 使用函数的优势贷款利率付款期数未来值现值付款时间u实现特殊运算1.4 使用
5、函数的优势贷款利率付款期数现值u实现智能判断用函数还可以实现智能判断例如,在学校,老师统计学生的考试成绩,如 果,其成绩大于等于80则为“优秀”,如果成绩大 于等于59且小于80则为“及格”,否则为“不及格”。 这类问题用人工计算很不方便,可以使用函数来 进行自动化处理1.4 使用函数的优势u实现智能判断1.4 使用函数的优势u提高工作效率在学校的期末结中,常会需要按学生的 考试成绩进行排位的问题,当然可以使用“排序” 按钮来完成,但是这样做比较麻烦,如果使用排 位函数RANK就比较方便,尤其是纪录很多、相 同分数很多的情况下,不用RANK函数其工作量 不可想像RANK(number,ref,
6、order)1.4 使用函数的优势需要排位的数字引用的区域,绝对地址排位的方式u提高工作效率1.4 使用函数的优势由于Excel 2007的函数众多,功能很强,所以我 们要使用Excel 的帮助文件。Excel 2007 提供了 帮助功能: “函数参数”对话框和Excel的帮助。函 数参数对话框是寻找帮助的最好办法。1.5 获得Excel 函数的帮助帮助的链接1.5 获得Excel 函数的帮助u使用Excel帮助有关函数的帮助非常全面。在每个函数的帮助 主题中都可以找到下面的内容 函数的语法 “注释”提供了函数的疑难解答 实例 相关函数1.5 获得Excel 函数的帮助u 数学函数 I.基本数
7、学函数 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 按
8、指定位数四舍五入数值 ROUNDUP 、ROUNDDOWN 按指定位数向上或向下四舍五入数值 TRUNC 将数值的小数部分截去,返回整数 INT 将数值向下舍入计取整 ODD、EVEN 将数值舍入为奇或偶数2.1 数学函数-求和函数M计算某一单元格区域中所有数值之和 BTOTAL返回数据清单或数据库中的分类汇 MIF根据指定条件对若干单元格、区域或引用求和 MPRODUCT计算数组间对应 的元素相乘,并返回乘积之和 MSQ计算所有参数的平方和 MX2MY2计算两数组中对应 数值的平方差之和 MX2PY2计算两数组中对应 数值的平方和的和 MXMY2计算两数组中对应 数值之差的平方和2.1 数学
9、函数-求和函数举例:某学校的考试成绩统计表,要统计女同 学的英语成绩和2.1 数学函数-求和函数举例:某学校的考试成绩统计表,要统计女同 学的英语成绩和。此例问题可以使用MIF 函数MIF(range,criteria,m_range)条件判断的单元格区域判定条件需要求和的单元格、区域或引用2.1 数学函数-求和函数举例:某学校的考试成绩统计表,要统计女同 学的英语成绩和。此例问题可以使用MIF 函数在求和的单元格输入公式:=MIF(B2:B4,”=女”,F2:F9)即得到结果2.1 数学函数-求和函数举例:COUNTIF功能:为满足指定条件的单元计数格式:Countif( 计数区域, “计数
10、条件” )= countif(D2:D9,“80”)逻辑函数是用来判断真假值,或者进行复合检 验的Excel函数。在Excel中提供了七种逻辑函数第3节 逻辑函数IF 对指定的条件计算结果为 TRUE 或 FALSE,返回不同的结果 IFERROR 如果公式计算出错误则 返回您指定的值;否则返回公式结果 AND 当AND的参数全部为TRUE时,返回结果为TRUE,否则为FALSE OR 当OR的参数中,如果任一参数为TRUE,返回结果 TRUE,否则 为FALSE NOT NOT函数用于对参数值求反 TRUE 返回逻辑值 TRUE FALSE 返回逻辑值 FALSE 第3节 逻辑函数举例:IF
11、在学校的管理工作中,常常要对学生的考试成 绩进行统计分析。假定现有某班级的考试成绩,我 们按照各科平均分对学生的考试成绩进行综合评定 。评定规则:如果平均分超过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
12、,IF(B690,”优秀“,”合格“),”不合格“)”不及格不及格E260E280E290E270及格及格中等中等良好良好优秀优秀为英语成绩建立五级分转换 1、if(E260,if(E270,if(E280,if(E290,优秀,良好),中等),及格),不及格)第3节 逻辑函数日期函数=TODAY() 逻辑函数=IF(F40,IF(F430,“,“即将到期“),“过期“)第3节 逻辑函数举例:OR ANDOR (logical1,logical2,logicaln)功能:当只要有一个参数的计算结果为TRUE时,OR函数的计 算结果为TRUE, 否则,计算结果为FALSEAND (logical
13、1,logical2,logicaln)功能:当所有参数的计算结果都是TRUE时,AND函数的计算 结果为TRUE, 否则,计算结果为FALSE第3节 逻辑函数举例:OR AND第3节 逻辑函数举例:OR AND第4节日期和时间函数DATA返回当前日期和时间NOW返回当前日期和时间TODAY返回当前日期YEAR返回日期中的年份MONTH返回日期中的月份DAY返回日期的天数HOUR返回时间值 的小时数第4节日期和时间函数第5节 文本函数LEFT根据所指定的字符数,返回文本字符串中左边的几个字符MID返回文本字符串中从指定位置开始的特定数目的字符RIGHT根据所指定的字符数,返回文本字符串中右边的
14、几个字符LOWER将一个文字串中的所有大写字母转换为 小写字母,但不改 变文本中的非字母的字符UPPER将文本转换成大写形式PROPER 将文本字符串的首字母及任何非字母字符之后的首字母转 换成大写。将其余的字母转换成小写Trim清除字符串中的空白。EXACT 比较两个字符串,如果它们完全相同,则返回 TRUE;否 则,返回 FALSE第5节 文本函数商品编号品名 单位商品规格上报数量0101001冠心宁支,10ml3000000101014骨瓜支,2ml:10mg2000000101016醒脑静10ML支,10ml2000000101017醒脑静2ML支,2ml1840000103001扶1
15、0袋盒,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万909
16、7元,本月累计销售金额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瓶 ,奥曲