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

《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瓶 ,奥曲