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

Excel 2007,数据分析及应用,.,目 录,常用函数,4,3,2,1,分类汇总,数据透视,图表制作,.,第一章 常用函数,函数的概述,数学函数,逻辑函数,日期和时间函数,文本函数,查找和引用函数,.,第,1,节 函数的概述,函数的基本概念,函数的分类,使用函数,使用函数的优势,获得,Excel,函数的帮助,预先定义，执行计算、分析等处理数据任务的特殊公式,分为,12,类、,356,个函数,直接使用、利用“插入函数”对话框,简化公式、实现特殊运算、实现智能判断、提高工作效率,使用“函数参数”对话框,使用,Excel,帮助,.,如果函数以公式的形式出现，请在函数名称前面键入等号,=,函数的结构,SUM(A10,B5:B10,50,37),1.1,函数的基本概念,函数名,参数，可以是常量、逻辑值、数组、错误值或单元格引用等,Excel 2007,的函数是指预先定义好，执行计算、分析等处理数据任务的特殊公式,.,1.2,函数的分类,Excel 2007,提供了的函数。可分为,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,使用函数的优势,.,由于,Excel 2007,的函数众多，功能很强，所以我们要使用,Excel,的帮助文件。,Excel 2007,提供了帮助功能：“函数参数”对话框和,Excel,的帮助。函数参数对话框是寻找帮助的最好办法。,1.5,获得,Excel,函数的帮助,.,帮助的链接,1.5,获得,Excel,函数的帮助,.,使用,Excel,帮助,有关函数的帮助非常全面。在每个函数的帮助主题中都可以找到下面的内容,函数的语法,“注释”提供了函数的疑难解答,实例,相关函数,1.5,获得,Excel,函数的帮助,.,数学函数,基本数学函数,舍入和取整函数,求和函数,第,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，否则为FALSE,OR,当,OR的参数中，如果任一参数为TRUE，返回结果,TRUE，否则为FALSE,NOT,NOT函数用于对参数值求反,TRUE,返回逻辑值,TRUE,FALSE,返回逻辑值,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,”,优秀,“,”,合格,“),”,不合格,“),”,.,不及格,E260,E280,E290,E270,及格,中等,良好,优秀,为英语成绩建立五级分转换,1,、,if(E260,if(E270,if(E280,if(E290,优秀，良好,),，中等,),，及格,),，不及格）,.,第,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,节 逻辑函数,举例,：,OR AND,.,第,4,节日期和时间函数,DATA,返回当前日期和时间,NOW,返回当前日期和时间,TODAY,返回当前日期,YEAR,返回日期,中的年份,MONTH,返回日期中的月份,DAY,返回日期的天数,HOUR,返回时间值的小时数,.,第,4,节日期和时间函数,.,第,5,节 文本函数,LEFT,根据所指定的字符数,，,返回文本字符串中左边的几个字符,MID,返回文本字符串中从指定位置开始的特定数目的字符,RIGHT根据所指定的字符数,，,返回文本字符串中右边的几个字符,LOWER,将一个文字串中的所有大写字母转换为小写字母，但不改变文本中的非字母的字符,UPPER,将文本转换成大写形式,PROPER,将文本字符串的首字母及任何非字母字符之后的首字母转换成大写。将其余的字母转换成小写,Trim,清除字符串中的空白。,EXACT,比较两个字符串，如果它们完全相同，则返回,TRUE；否则，返回,FALSE,.,第,5,节 文本函数,商品编号,品名,单位,商品规格,上报数量,0101001,冠心宁,支,10ml,300000,0101014,骨瓜,支,2ml:10mg,200000,0101016,醒脑静,10ML,支,10ml,200000,0101017,醒脑静,2ML,支,2ml,184000,0103001,扶,10,袋,盒,50mg*10,袋,247700,0103002,扶,6,袋,盒,50mg*6,袋,0,0103010,扶,16,袋,盒,50mg*16,袋,0,0105009,1MG,注射用胸腺五肽,瓶,1mg,0,0105012,10MG,注射用胸腺五肽,瓶,10mg,255400,0105010,胸腺五肽,1MG,（预充式）,支,1ml,：,1mg,236000,0105011,胸腺五肽,10MG,（预充式）,支,1ml,：,10mg,452100,0103005,双丹片（,48,片）,瓶，,0,0103006,双丹片（,72,片）,瓶，,0,0203008,尼群地平（,48,粒）,盒，,0,0203009,尼群地平（,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,加压素,1,06200,支,15UG,加压素,5400,支,胸腺法新,6400,瓶,奥曲肽,0.05MG,（中和）,9000,瓶,奥曲肽,0.01MG,（中和）,0,瓶,生长抑素,3.0MG,（中和）,300,瓶,.,第,6,节 查找和引用函数,-,查找函数,LOOKUP,返回一行或一列区域中或者数组中的某个值,VLOOKUP,在表格数组首列查找指定的值，并返回表格数组当前行中指定列的值,HLOOKUP,在表格数组首行查找指定的值，并返回表格数组当前行中指定行的值,CHOOSE,可以根据给定的索引值，从多达254个待选参数中选出相应的值,第,6,节 查找函数,.,第,6,节 查找和引用函数,-,查找函数,把销量表的各产品销量快速导入到上报表的上报数据栏一列,第,6,节 查找函数,编号,品名,单位,销量,0105012,10MG,注射用胸腺五肽,瓶,100,0105009,1MG,注射用胸腺五肽,瓶,200,0103010,扶,16,袋,盒,300,0103002,扶,6,袋,盒,400,0101017,醒脑静,2ML,支,500,0103001,扶,10,袋,盒,800,0101016,醒脑静,10ML,支,1000,0101014,骨瓜,支,2000,0101001,冠心宁,支,3000,销量表,.,商品编号,品名,单位,商品规格,上报数量,0101001,冠心宁,支,10ml,3000,0101014,骨瓜,支,2ml:10mg,2000,0101016,醒脑静,10ML,支,10ml,1000,0101017,醒脑静,2ML,支,2ml,500,0103001,扶,10,袋,盒,50mg*10,袋,800,0103002,扶,6,袋,盒,50mg*6,袋,400,0103010,扶,16,袋,盒,50mg*16,袋,300,0105009,1MG,注射用胸腺五肽,瓶,1mg,200,0105012,10MG,注射用胸腺五肽,瓶,10mg,100,上报表,指定查找对象,第,6,节 查找函数,.,第,6,节 查找和引用函数,-,查找（新函数,第,6,节 查找函数,编号,品名,单位,销量,0105012,10MG,注射用胸腺五肽,瓶,100,0105009,1MG,注射用胸腺五肽,瓶,200,0103010,扶,16,袋,盒,300,0103002,扶,6,袋,盒,400,0101017,醒脑静,2ML,支,500,0103001,扶,10,袋,盒,800,0101016,醒脑静,10ML,支,1000,0101014,骨瓜,支,2000,0101001,冠心宁,支,3000,销量表（源）,查找区域,商品编号,品名,单位,商品规格,上报数量,0101001,冠心宁,支,10ml,3000,0101014,骨瓜,支,2ml:10mg,2000,0101016,醒脑静,10ML,支,10ml,1000,0101017,醒脑静,2ML,支,2ml,500,0103001,扶,10,袋,盒,50mg*10,袋,800,0103002,扶,6,袋,盒,50mg*6,袋,400,0103010,扶,16,袋,盒,50mg*16,袋,300,0105009,1MG,注射用胸腺五肽,瓶,1mg,200,0105012,10MG,注射用胸腺五肽,瓶,10mg,100,上报表,指定查找对象,在查找区域中的序列,返回值,.,第,6,节 查找函数,.,第,6,节 查找和引用函数,-,引用函数,ADDRESS,按照给定的行号和列号，建立文本类型的单元格地址,COLUMN,返回指定,单元格引用的列号。,ROW,返回指定,单元格引用的行号。,AREAS,返回引用中包含的区域个数,COLUMNS,返回数组或引用的列数,ROWS,返回引用或数组的行数。,INDEX,返回表格或区域中的数值或对数值的引用,第,7,节 引用函数,.,第二章 分类汇总,一、仅对某列进行分类汇总,例如上例中需要对各城市的销售量进行分类汇总，方法如下：1.,首先对数据按需要分类汇总的列（本例为“城市”列）进行排序。,选择“城市”列中的任意单元格，在,Excel 2007,中，选择功能区中“数据”选项卡，在“排序和筛选”组中单击,2.,选择数据区域中的某个单元格，,Excel 2007,，则在“数据”选项卡的“分级显示”组中单击“分类汇总”。,3.,在弹出的“分类汇总”对话框中，在“分类字段”下选择“城市”，在“汇总方式”中选择某种汇总方式，可供选择的汇总方式有“求和”、“计数”、“平均值”等，本例中选择默认的“求和”。在“选定汇总项”下仅选择“销售额”。,.,4.,单击确定，,Excel,将按城市进行分类汇总。,第二章 分类汇总,.,二、对多列进行分类汇总,先按上述操作对“城市”进行分类汇总，再对“商品名称”列进行分类汇总（嵌套分类汇总）。,再打开“分类汇总”对话框，在“分类字段”下选择“商品名称”，取消选择“替换当前分类汇总”，单击“确定”。,第二章 分类汇总,.,第三章 数据透视表,数据透视表操作示例,A,B,C,D,E,F,G,H,I,3,序号,日期,姓名,品名,单价,数量,金额,运输方式,4,09701,2003-3-1,张三,创维,3,080,5,15,400,汽车,5,09702,2003-3-1,李四,创维,3,600,7,25,200,轮船,6,09703,2003-3-1,王五,创维,3,200,2,6,400,火车,7,09704,2003-3-1,马六,TCL,3,080,5,15,400,轮船,8,09705,2003-3-1,周七,熊猫,3,300,6,19,800,汽车,9,09706,2003-3-1,郑八,熊猫,3,300,4,13,200,汽车,10,09707,2003-3-1,张三,康佳,2,980,3,8,940,火车,11,09708,2003-3-1,李四,TCL,3,200,7,22,400,轮船,12,09709,2003-3-1,王五,康佳,3,200,5,16,000,汽车,13,09710,2003-3-1,马六,TCL,3,080,3,9,240,汽车,14,09711,2003-2-24,周七,长虹,3,600,1,3,600,汽车,15,09712,2003-2-25,郑八,创维,2,980,6,17,880,汽车,16,09713,2003-2-26,张三,TCL,3,600,5,18,000,汽车,17,09714,2003-2-27,李四,长虹,3,200,4,12,800,火车,18,09715,2003-2-28,王五,熊猫,2,980,1,2,980,火车,.,第三章 数据透视表,.,产品销售,产品,金额,D,产品,14694381,F,产品,6770790,C,产品,2051565,A,产品,1763400,G,产品,1302567,E,产品,1222200,B,产品,493493,合计,55668284,第四章 图表制作,.,第四章 图表制作,.,第四章 图表制作,.,.,