1、Excel表格数据分析表格数据分析及处理教程及处理教程4321第一章第一章 常用函数常用函数u 函数的概述函数的概述u 数学函数数学函数u 逻辑函数逻辑函数u 日期和时间函数日期和时间函数u 文本函数文本函数u查找和引用函数查找和引用函数第第1节节 函数的概述函数的概述u 函数的基本概念函数的基本概念u 函数的分类函数的分类u 使用函数使用函数u 使用函数的优势使用函数的优势u 获得获得Excel 函数的帮助函数的帮助预先定义，执行计算、分析预先定义，执行计算、分析等处理数据任务的特殊公式等处理数据任务的特殊公式分为分为1212类、类、356356个函数个函数直接使用、利用直接使用、利用“插入
2、函数插入函数”对话框对话框简化公式、实现特殊运算、实简化公式、实现特殊运算、实现智能判断、提高工作效率现智能判断、提高工作效率使用使用“函数参数函数参数”对话框对话框使用使用ExcelExcel帮助帮助如果函数以公式的形式出现，请在函数名称前面键入等号 u 函数的结构函数的结构 SUM(A10,B5:B10,50,37)1.1 函数的基本概念函数的基本概念函数名函数名参数，可以是常量、逻辑值、参数，可以是常量、逻辑值、数组、错误值或单元格引用等数组、错误值或单元格引用等 Excel 2007的函数是指预先定义好，执行计算、分析等处理数据任务的特殊公式1.2 函数的分类函数的分类 Excel 2
3、007提供了的函数。可分为12类：分分 类类功功 能能 简简 介介数据库函数数据库函数对数据清单中的数据进行分析、查找、计算等对数据清单中的数据进行分析、查找、计算等日期与时间日期与时间对日期和时间进行计算、设置及格式化处理对日期和时间进行计算、设置及格式化处理工程函数工程函数用于工程数据分析与处理用于工程数据分析与处理信息函数信息函数对单元格或公式中数据类型进行判定对单元格或公式中数据类型进行判定财务函数财务函数进行财务分析及财务数据的计算进行财务分析及财务数据的计算逻辑函数逻辑函数进行逻辑判定、条件检查进行逻辑判定、条件检查统计函数统计函数对工作表数据进行统计、分析对工作表数据进行统计、分
4、析查找函数查找函数查找特定的数据或引用公式中的特定信息查找特定的数据或引用公式中的特定信息文本函数文本函数对公式、单格中的字符、文本进行格式化或运算对公式、单格中的字符、文本进行格式化或运算数学函数数学函数进行数学计算等进行数学计算等外部函数外部函数进行外部函数调用及数据库的链接查询等功能进行外部函数调用及数据库的链接查询等功能自定义函数自定义函数用户用用户用vba编写，用于完成特定功能的函数编写，用于完成特定功能的函数u利用利用“插入函数插入函数”对话框使用函数对话框使用函数 1.3 使用函数使用函数选中要显示结果的单元格在“公式”选项卡“函数库”组，单击“插入函数”u简化公式简化公式 函数
5、可以使一些复杂的公式更易于使用，使复杂的数学表达式输入更加简化。比如，要计算B3:D7区域中数据的平均值时，使用公式计算量大，需要写成：“=(B3+B4+B5+B6+B7+ C3+C4+C5+C6+C7+D3+D4+D5+D6+D7)/15”而使用函数将相当简单，只要写成：“=AVERAGE(B3:D7)” 1.4 使用函数的优势使用函数的优势u实现特殊运算实现特殊运算 在日常的工作中，有一些计算使用公式不但复杂，而且不具有特定的专业知识还无法表达，那么就可以使用现有的函数进行特殊运算 例如，按揭贷款的等额分期偿还额的计算必须用到高等数学的知识，而使用PMT函数(计算在固定利率下，贷款的等额分
6、期偿还额)就相当简单 PMT(rate,nper,pv,fv,type) 1.4 使用函数的优势使用函数的优势贷款利率贷款利率付款期总数付款期总数未来值未来值现值现值付款时间付款时间u实现特殊运算实现特殊运算 1.4 使用函数的优势使用函数的优势贷款利率贷款利率付款期总数付款期总数现值现值u实现智能判断实现智能判断 用函数还可以实现智能判断 例如，在学校，老师统计学生的考试成绩，如果，其成绩大于等于80则为“优秀”,如果成绩大于等于59且小于80则为“及格”,否则为“不及格”。这类问题用人工计算很不方便，可以使用函数来进行自动化处理 1.4 使用函数的优势使用函数的优势u实现智能判断实现智能判
7、断 1.4 使用函数的优势使用函数的优势u提高工作效率提高工作效率 在学校的期末总结中，常会需要按学生的考试成绩进行排位的问题，当然可以使用“排序”按钮来完成，但是这样做比较麻烦，如果使用排位函数RANK就比较方便，尤其是纪录很多、相同分数很多的情况下，不用RANK函数其工作量不可想像 RANK(number,ref,order)1.4 使用函数的优势使用函数的优势需要排位的数字需要排位的数字引用的区域，绝对地址引用的区域，绝对地址排位的方式排位的方式u提高工作效率提高工作效率1.4 使用函数的优势使用函数的优势 由于Excel 2007的函数众多，功能很强，所以我们要使用Excel 的帮助文
8、件。Excel 2007 提供了帮助功能： “函数参数”对话框和Excel的帮助。函数参数对话框是寻找帮助的最好办法。1.5 获得获得Excel 函数的帮助函数的帮助帮助的链接帮助的链接1.5 获得获得Excel 函数的帮助函数的帮助u使用使用Excel帮助帮助 有关函数的帮助非常全面。在每个函数的帮助主题中都可以找到下面的内容 函数的语法函数的语法 “注释注释”提供了函数的疑难解答提供了函数的疑难解答 实例实例 相关函数相关函数1.5 获得获得Excel 函数的帮助函数的帮助u 数学函数数学函数 基本数学函数 舍入和取整函数求和函数 第第2节节 数学函数数学函数2.1 数学函数数学函数-基本
9、数学函数基本数学函数 举例：举例：用函数RAND函数为学生随机排号排号学校组织考试时，经常需要将学生的学号打并随机编排考号，使用RAND随机函数实现以上功能。RAND函数的格式： RAND()在单元格D2内输入公式“=RAND()”并向下填充，这样在D列各单元格内生存了0-1之间的任意数2.1 数学函数数学函数-基本数学函数基本数学函数 举例：举例：用函数RAND函数为学生随机排号 按列D “排序”,可以看到B、C二列已无序排列,再在A2单元格中输入第一个考号,并按序列向下填充,将D列删除或清空数据2.1 数学函数数学函数-舍入和取整函数舍入和取整函数ROUND 按指定位数四舍五入数值ROUN
10、DUP 、ROUNDDOWN 按指定位数向上或向下四舍五入数值TRUNC 将数值的小数部分截去，返回整数INT 将数值向下舍入计取整ODD、EVEN 将数值舍入为奇或偶数2.1 数学函数数学函数-求和函数求和函数SUM计算某一单元格区域中所有数值之和SUBTOTAL返回数据清单或数据库中的分类汇总SUMIF根据指定条件对若干单元格、区域或引用求和SUMPRODUCT计算数组间对应的元素相乘，并返回乘积之和SUMSQ计算所有参数的平方和SUMX2MY2计算两数组中对应数值的平方差之和SUMX2PY2计算两数组中对应数值的平方和的总和SUMXMY2计算两数组中对应数值之差的平方和2.1 数学函数数
11、学函数-求和函数求和函数 举例：举例：某学校的考试成绩统计表，要统计女同学的英语成绩总和2.1 数学函数数学函数-求和函数求和函数 举例：举例：某学校的考试成绩统计表，要统计女同学的英语成绩总和。此例问题可以使用SUMIF函数 SUMIF(range，criteria，sum_range)条件判断的单元格区域条件判断的单元格区域判定条件判定条件需要求和的单元格、区域或引用需要求和的单元格、区域或引用2.1 数学函数数学函数-求和函数求和函数 举例：举例：某学校的考试成绩统计表，要统计女同学的英语成绩总和。此例问题可以使用SUMIF函数 在求和的单元格输入公式：在求和的单元格输入公式： =SUM
12、IF(B2:B4,”=女女”,F2:F9) 即得到结果即得到结果2.1 数学函数数学函数-求和函数求和函数 举例：举例：COUNTIF 功能：为满足指定条件的单元计数功能：为满足指定条件的单元计数 格式：格式：Countif( 计数区域计数区域, “计数条件计数条件” ) = countif（D2:D9,“80”） 逻辑函数是用来判断真假值，或者进行复合检验的Excel函数。在Excel中提供了七种逻辑函数第第3节节 逻辑函数逻辑函数IF 对指定的条件计算结果为 TRUE 或 FALSE，返回不同的结果IFERROR 如果公式计算出错误则返回您指定的值；否则返回公式结果AND 当AND的参数全
13、部为TRUE时，返回结果为TRUE，否则为FALSE OR 当OR的参数中，如果任一参数为TRUE，返回结果 TRUE，否则为FALSE NOT NOT函数用于对参数值求反TRUE 返回逻辑值 TRUE FALSE 返回逻辑值 FALSE 第第3节节 逻辑函数逻辑函数 举例举例：IF 在学校的管理工作中，常常要对学生的考试成绩进行统计分析。假定现有某班级的考试成绩，我们按照各科平均分对学生的考试成绩进行综合评定。评定规则：如果平均分超过90时，评定为优秀，如果平均分小于90并且超过60则认为是合格的，否则记作不合格第第3节节 逻辑函数逻辑函数 举例举例：根据规则，我们在综合评定中使用IF函数I
14、F(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,优秀，良好优秀，良好)，中等，中等)，及格，及格)，不及格），不及格）
15、第第3节节 逻辑函数逻辑函数日期函数日期函数=TODAY()逻辑函数逻辑函数=IF(F40,IF(F430,即将到期即将到期),过期过期)第第3节节 逻辑函数逻辑函数 举例举例：OR AND OR (logical1,logical2,logicaln) 功能功能：当只要有一个参数的计算结果为TRUE时,OR函数的计算结果为TRUE, 否则，计算结果为FALSE AND (logical1,logical2,logicaln) 功能功能：当所有参数的计算结果都是TRUE时,AND函数的计算结果为TRUE, 否则，计算结果为FALSE第第3节节 逻辑函数逻辑函数 举例举例：OR AND第第3节节
16、 逻辑函数逻辑函数 举例举例：OR AND第第4节日期和时间函数节日期和时间函数DATA返回当前日期和时间NOW返回当前日期和时间TODAY返回当前日期YEAR返回日期中的年份MONTH返回日期中的月份DAY返回日期的天数HOUR返回时间值的小时数第第4节日期和时间函数节日期和时间函数第第5节节 文本函数文本函数LEFT根据所指定的字符数，返回文本字符串中左边的几个字符MID返回文本字符串中从指定位置开始的特定数目的字符RIGHT根据所指定的字符数，返回文本字符串中右边的几个字符LOWER将一个文字串中的所有大写字母转换为小写字母，但不改变文本中的非字母的字符UPPER将文本转换成大写形式PR
17、OPER 将文本字符串的首字母及任何非字母字符之后的首字母转换成大写。将其余的字母转换成小写 Trim清除字符串中的空白。 EXACT 比较两个字符串，如果它们完全相同，则返回 TRUE；否则，返回 FALSE第第5节节 文本函数文本函数商品编号品名单位商品规格上报数量上报数量0101001冠心宁支,10ml3000000101014骨瓜支,2ml:10mg2000000101016醒脑静10ML支,10ml2000000101017醒脑静2ML支,2ml1840000103001扶10袋盒,50mg*10袋2477000103002扶6袋盒,50mg*6袋00103010扶16袋盒,50mg
18、*16袋001050091MG注射用胸腺五肽瓶,1mg0010501210MG注射用胸腺五肽瓶,10mg2554000105010胸腺五肽1MG（预充式）支,1ml：1mg2360000105011胸腺五肽10MG（预充式）支,1ml：10mg4521000103005双丹片（48片）瓶，00103006双丹片（72片）瓶，00203008尼群地平（48粒）盒，00203009尼群地平（60粒）瓶，0第第5节节 文本函数文本函数尊敬的公司领导您好：今日销售100笔，销售金额2000万9097元,本月累计销售金额40000万2321元各药品本月累计销售明细如下：全部第一段第二段尊敬的公司领导您好
19、：今日销售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支,15U
20、G加压素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瓶
21、，尼群地平（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瓶,第第6节节 查找和引用函数查找和引用函数-查找函数查找函数 LOOKUP返回一行或一列区域中或者数组中的某个值 VLOOKUP在表格数组首列查找指定的值，并返回表格数组当前行中指定列的值HLOOKUP在表格数组首行查找指定的值，并返回表格数组当前行中指定行的值CHOOSE可以根据给定的索引
22、值，从多达254个待选参数中选出相应的值第第6节节 查找函数查找函数第第6节节 查找和引用函数查找和引用函数-查找函数查找函数 把销量表的各产品销量快速导入到上报表的上报数据栏一列第第6节节 查找函数查找函数编号编号品名品名单位单位销量销量010501210MG注射用胸腺五肽瓶,10001050091MG注射用胸腺五肽瓶,2000103010扶16袋盒,3000103002扶6袋盒,4000101017醒脑静2ML支,5000103001扶10袋盒,8000101016醒脑静10ML支,10000101014骨瓜支,20000101001冠心宁支,3000 销量表商品编号商品编号品名品名单位单
23、位商品规格商品规格上报数量上报数量0101001冠心宁支,10ml30000101014骨瓜支,2ml:10mg20000101016醒脑静10ML支,10ml10000101017醒脑静2ML支,2ml5000103001扶10袋盒,50mg*10袋8000103002扶6袋盒,50mg*6袋4000103010扶16袋盒,50mg*16袋30001050091MG注射用胸腺五肽瓶,1mg200010501210MG注射用胸腺五肽瓶,10mg100 上报表指定查找对象指定查找对象第第6节节 查找函数查找函数第第6节节 查找和引用函数查找和引用函数-查找（新函数查找（新函数 第第6节节 查找函
24、数查找函数编号编号品名品名单位单位销量销量010501210MG注射用胸腺五肽瓶,10001050091MG注射用胸腺五肽瓶,2000103010扶16袋盒,3000103002扶6袋盒,4000101017醒脑静2ML支,5000103001扶10袋盒,8000101016醒脑静10ML支,10000101014骨瓜支,20000101001冠心宁支,3000 销量表（源）查找区域查找区域商品编号商品编号品名品名单位单位商品规格商品规格上报数量上报数量0101001冠心宁支,10ml30000101014骨瓜支,2ml:10mg20000101016醒脑静10ML支,10ml10000101
25、017醒脑静2ML支,2ml5000103001扶10袋盒,50mg*10袋8000103002扶6袋盒,50mg*6袋4000103010扶16袋盒,50mg*16袋30001050091MG注射用胸腺五肽瓶,1mg200010501210MG注射用胸腺五肽瓶,10mg100 上报表 指定查找对象指定查找对象在查找在查找区域中区域中的序列的序列返回值返回值第第6节节 查找函数查找函数第第6节节 查找和引用函数查找和引用函数-引用函数引用函数 ADDRESS按照给定的行号和列号，建立文本类型的单元格地址COLUMN返回指定单元格引用 的列号。 ROW 返回指定单元格引用 的行号。AREAS返回
26、引用中包含的区域个数COLUMNS返回数组或引用的列数 ROWS 返回引用或数组的行数。INDEX返回表格或区域中的数值或对数值的引用 第第7节节 引用函数引用函数第二章第二章 分类汇总分类汇总一、仅对某列进行分类汇总一、仅对某列进行分类汇总例如上例中需要对各城市的销售量进行分类汇总，方法如下： 1. 首先对数据按需要分类汇总的列（本例为“城市”列）进行排序。 选择“城市”列中的任意单元格，在Excel 2007中，选择功能区中“数据”选项卡，在“排序和筛选”组中单击 2. 选择数据区域中的某个单元格，Excel 2007，则在“数据”选项卡的“分级显示”组中单击“分类汇总”。 3. 在弹出的
27、“分类汇总”对话框中，在“分类字段”下选择“城市”，在“汇总方式”中选择某种汇总方式，可供选择的汇总方式有“求和”、“计数”、“平均值”等，本例中选择默认的“求和”。在“选定汇总项”下仅选择“销售额”。4.单击确定，Excel将按城市进行分类汇总。第二章第二章 分类汇总分类汇总二、对多列进行分类汇总二、对多列进行分类汇总 先按上述操作对“城市”进行分类汇总，再对“商品名称”列进行分类汇总（嵌套分类汇总）。 再打开“分类汇总”对话框，在“分类字段”下选择“商品名称”，取消选择“替换当前分类汇总”，单击“确定”。第二章第二章 分类汇总分类汇总第三章第三章 数据透视表数据透视表数据透视表操作示例数据
28、透视表操作示例ABCDEFGHI3序号序号日期日期姓名姓名品名品名单价单价数量数量金额金额运输方式运输方式409701 2003-3-1张三张三创维创维 3,080 5 15,400 汽车汽车509702 2003-3-1李四李四创维创维 3,600 7 25,200 轮船轮船609703 2003-3-1王五王五创维创维 3,200 2 6,400 火车火车709704 2003-3-1马六马六TCLTCL 3,080 5 15,400 轮船轮船809705 2003-3-1周七周七熊猫熊猫 3,300 6 19,800 汽车汽车909706 2003-3-1郑八郑八熊猫熊猫 3,300 4
29、 13,200 汽车汽车1009707 2003-3-1张三张三康佳康佳 2,980 3 8,940 火车火车1109708 2003-3-1李四李四TCLTCL 3,200 7 22,400 轮船轮船1209709 2003-3-1王五王五康佳康佳 3,200 5 16,000 汽车汽车1309710 2003-3-1马六马六TCLTCL 3,080 3 9,240 汽车汽车1409711 2003-2-24周七周七长虹长虹 3,600 1 3,600 汽车汽车1509712 2003-2-25郑八郑八创维创维 2,980 6 17,880 汽车汽车1609713 2003-2-26张三张三
30、TCLTCL 3,600 5 18,000 汽车汽车1709714 2003-2-27李四李四长虹长虹 3,200 4 12,800 火车火车1809715 2003-2-28王五王五熊猫熊猫 2,980 1 2,980 火车火车第三章第三章 数据透视表数据透视表产品销售产品销售产品金额D产品14694381 F产品6770790 C产品2051565 A产品1763400 G产品1302567 E产品1222200 B产品493493 合计55668284 第四章第四章 图表制作图表制作第四章第四章 图表制作图表制作第四章第四章 图表制作图表制作8. 一个人除非自己有信心，否则带给别人信心。
31、7、当一个人先从自己的内心开始奋斗，他就是个有价值的人。15. 人只能按照自己的条件去寻找终生伴侣。3. 人生一世，总有个追求，有个盼望，有个让自己珍视，让自己向往，让自己护卫，愿意为之活一遭，乃至愿意为之献身的东西，这就是价值了。12. 成功需要成本，时间也是一种成本，对时间的珍惜就是对成本的节约。8. 向着目标奔跑，何必在意折翼的翅膀，只要信心不死，就看的见方向，顺风适合行走，逆风更适合飞翔，人生路上什么都不怕，就怕自己投降。11、目标的坚定是性格中最必要的力量源泉之一，也是成功的利器之一。没有它，天才也会在矛盾无定的迷径中徒劳无功。6、时间，是靠自己去挤的。在你这一秒空闲的时候，时间又像
32、脚底抹了油似的溜走了，只有去抓住它，它似乎才会停下脚步这时，你就可以做自己想做的事情。5. 用行动祈祷比用言语更能够使上帝了解。10. 付出才会杰出;为别人创造价值，别人才愿意和你交往。5. 用行动祈祷比用言语更能够使上帝了解。14. 进入黑名单就意味着你不再是我看重的人，连陌生人都比不上。2. 选择了自由，就得忍受孤寂。18. 我的人生格言是：走别人的路，让别人无路可走。14. 这个社会是存在不公平的，不要抱怨，因为没有用!人总是在反省中进步的!5. 莫妒他长，妒长，则己终是短。莫护己短，护短，则己终不长。3. 贫穷是不需要计划的，致富才需要一个周密的计划10. 成功不是将来才有的，而是从决
33、定去做的那一刻起，持续累积而成。19、任何的限制，都是从自己的内心开始的。10. 你的强势之下缺少一种开放的心态。11. 生命中越珍贵的东西越爱迟到。7. 很多事先天注定，那是“命”;但你可以可以决定怎么面对，那是“运”!9. 行动不一定带来快乐，而无行动则决无快乐。4. 自己打败自己是最可悲的失败，自己战胜自己是最可贵的胜利。16、如果你希望成功，以恒心为良友，以经验为参谋，以小心为兄弟，以希望为哨兵。29、含泪播种的人一定能含笑收获。15. 锲而舍之，朽木不折;锲而不舍，金石可镂。人类学会走路，也得学会摔跤，而且只有经过摔跤他才能学会走路。8. 当你用烦恼心来面对事物时，你会觉得一切都是业障，世界也会变得丑陋可恨。
