数据管理与决策第七讲

单击此处编辑母版标题样式,单击此处编辑母版文本样式,二级,三级,四级,五级,*,*,EXCEL,培训,理解函数,字符函数,日期函数,统计函数,数学函数,课后习题,EXCEL,函数应用,1,理解函数,数 据,规则,什 么 是 函 数？,结果,函 数 的 格 式,SUM(1,，,3,，,5),= 9,函 数 名,数 据,结 果,2,EXCEL,函数的结构,Excel,函数即是预先定义，执行计算、分析等处理数据任务的,特殊公式,。,Excel,函数结构,:,也有一些函数是没有参数的,如,ROW(),左右括号成对出现,单一结构,嵌套结构,参数与参数之间使用,半角逗号,进行分隔,3,使用公式,公式中的运算符,算术运算符,算术运算符号,运算符含义,示 例,+(,加号,),加,2+3=5,-(,减号,),减,3-1=2,*,(,星号,乘,3*2=6,/(,斜杠,),除,6/2=3,%(,百分号,),百分号,50%,(,脱字号,),乘方,43=4,3,=64,4,使用公式,公式中的运算符,2.,文本运算符,“,&”,号,可以将文本连接起来,.,3.,比较运算符,比较运算符,运算符含义,示 例,=(,等号,),相等,B1=C1,若,B1,中单元格内的值确实与,C1,中的值相等,则产生逻辑真值,TRUE,若不相等,则产生逻辑假值,FALSE,(,小于号,),小于,B1(,大于号,),大于,B1C1,若,B1,中数值为,6,C1,中数值为,4,则条件成立产生逻辑真值,TRUE,否则产生逻辑假值,FALSE,=(,大于等于号,),大于等于,B1=C1,(,不等号,),不等于,B1C1,=(,小于等于号,),小于等于,B1=C1,5,使用公式,公式中的运算符,4.,引用运算符,引用运算符,运算符含义,示 例,:,区域运算符,产生对包括在丙相引用之间的所有单元格的引用,(A5:A15),联合运算符,将多个引用合并为一个引用,SUM(A5:A15,C5:C15),(,空格,),交叉运算符,产生对两个引用构有的单元格的引用,(B7:D7 C6:C8),例,:A4=B4+C4+C4+E4+F4,可写为,: A4=SUM(B4:F4),6,使用公式,公式中的运算顺序,如果在公式中要同时使用多个运算符,则应该了解运算符的优先级,.,算术运算符的优先级是先乘幂运算,再乘、除运算,最后为加、减运算。相同优先级的运算符按从左到右的次序进行运算。,公式中出现不同类型的运算符混用时,运算次序是：引用运算符,算术运算符,文本运算符。如果需要改变次序,可将先要计算的部分括上圆括号。,7,使用公式,引用单元格,相对引用,相对引用指公式中的单元格位置将随着公式单元格的位置而改变,2.,绝对引用,绝对,引用是指公式和函数中的位置是固定不变的,.,绝对引用是在列字母和行数字之前都加上美元符号,”$”,如,$A$4,$C$6,3.,混合引用,混合,引用是指在一个单元格引用中,既有绝对引用,也有相对引用,.,例,:,行变列不变,: $A4,列变行不变,:A$4,8,创建公式,公式是指对工作表中的数据进行分析和处理的一个等式,不仅用于算术计算,也用于逻辑运算、日期和时间的计算等。,一个公式必须具备,3,个条件：,以等式开头,;,2.,含有运算符和运算对象,;,3.,能产生结果,公式的,3,个基本组成部分,:,数值,:,包括数字和文本,单元格引用,操作符,9,创建公式,创建公式的方法,使用编辑栏,直接在单元格中输入,创建,3,种类型的公式,创建简单公式,;,2.,创建包含引用或名称的公式,3.,创建包含函数的公式,10,使用函数,函数是指预定义的内置公式,可以进行数学、文本和逻辑的运算或者查找工作表的信息。,函数是一个已经提供给用户的公式,并且有一个描述性的名称。,函数结构,函数名称：如果要查看可用函数的列表,可以单击一个单元格并按,Shift+F3,键；,参数：参数可以是数字、文本、逻辑值（,TRUE,或,FALSE,）、数值、错误值（如,#N/A,）或单元格引用。,参数工具提示：在输入函数时,会出现一个带有语法和参数的工具提示。,输入公式：“插入函数”对话框。,函数的参数,函数的参数是一个函数体中括号内的部分,逻辑值,: FALSE :,需要完全一致,TRUE:,相似值亦可转换,11,使用函数,函数的类型,1,.,常用函数,:,常用函数指经常使用的函数,如求和或计算算术平均值等,.,包括,SUM,AVERAGE, ISPMT,IF,HYPERLINK,COUNT,MAX,SIN, SUMIF,和,PMT.,它们的语法和作用如下表,:,语法,作用,SUM(number1,number2, ),返回单元格区域中所有数值的和,ISPMT(Rate,Per,Nper,Pv),返回普通,(,无提保,),的利息偿还,AVERAGE(Number1,Number2, ),计算参数的算术平均数,参数可以是数值或包含数值的名称数组和引用,IF(Logical_test,Value_if_true,Value_if_false),执行真假判断,根据对指定条件进行逻辑评价的真假而返回不同的结果。,HYPERLINK(Link_location,Friendly_name),创建快捷方式,以便打开文档或网络驱动器或连接连接,INTERNET,COUNT(Value1,Value2, ),计算参数表中的数字参数和包含数字的单元格的个数,MAX(number1,number2, ),返回一组数值中的最大值,忽略逻辑值和文字符,SIN (number),返回纪念品定角度的正弦值,SUMIF (Range, Criteria, Sum_range),根据指定条伯对若干单元格求和,PMT(Rate,Nper,Pv,Fv,Type),返回在固定利率下,投资或贷款的等额分期偿还额,12,使用函数,函数的类型,2.,财务函数,:,财务函数用于财务的计算,它可以根据利率、贷款金额和期限计算出所要支付的金额,它们的变量紧密相互关联。,如：,PMT(Rate,Nper,Pv,Fv,Type),=PMT,（,0.006,360,-300000),其中,0.006,是每一笔贷款项的利率,360,是款项的数目,-300000,是,360,笔款项的当前金额,(,在计算支付金额时,表示当前和将来金额的变量至少有一个通常是负数,),注,:,Rate,贷款利率。,Nper,该项贷款的付款总数。,Pv,现值,或一系列未来付款的当前值的累积和,也称为本金。,Fv,为未来值,或在最后一次付款后希望得到的现金余额,如果省略,fv,则假设其值为零,也就是一笔贷款的未来值为零。,Type,数字,0,或,1,用以指定各期的付款时间是在期初还是期末。,13,使用函数,函数的类型,3,.,时间和日期函数,:,主要用于分析和处理日期值和时间值,包括,DATE,、,DATEVALUE,、,DAY,、,HOUR,、,TIME,、,TODAY,、,WEEKDAY,和,YEAR,等。,例：函数,DATA,语法：,DATE(year,month,day),14,在单元格中插入当前日期和时间,插入静态的日期和时间,当前日期 选取一个单元格,并按,Ctrl+;,当前时间 选取一个单元格,并按,Ctrl+Shift+,；,当前日期和时间 选取一个单元格,并按,Ctrl+;,然后按空格键,最后按,Ctrl+Shift+;,插入会更新的日期和时间,使用,TODAY,和,NOW,函数完成这一工作。,小贴士,15,使用函数,函数的类型,4,.,数学与三角函数,:,5,.,统计函数,:,统计函数是用来对数据区域进行统计分析。,主要用于进行各种各样的计算,包括,ABS,、,ASIN,、,COMBINE,、,COSLOG,、,PI,、,ROUND,、,SIN,、,TAN,和,TUUNC,等。,例：,ABS,函数,语法：,ABS(number),16,函数是一种特殊的公式,因此所有的函数要以“,=”,开始。,使用方法如下：,1.,选中要输入函数的单元格,2.,单击,Insert,菜单中的 命令,弹出如图所示的对话框：,使用函数,17,3.,在上图的对话框中,选择,函数分类,下面的函数类型及,函数名,下面的对应函数名。,4.,单击,确定,按钮,弹出如图所示的相应的调用,函数对话框：,使用函数,18,函数与公式通用技巧,限制：,突破：,使用定义名称。,当参数为单元格区域引用时，可使用一对括号来包含多个单元格区域的引用部分形成合并区域引用作为函数参数。,1,、提前将单元格格式设置为文本后再输入数字,2,、在输入数字时用半角单引号开头。,公式内容的长度不能超过,1024,个字符；,公式中函数的嵌套不能超过,7,层；,公式中函数的参数不能超过,30,个；,数字计算精度为,15,位。,19,函数与公式通用技巧,改变工作表的计算精度,在实际工作中，许多用户在计算数字时会设置单元格格式为,2,为小数的数值、货币格式或者是整数值。但在使用公式进行数据处理的过程中，经常会发现几个单元格之和与合计单元格显示的数据之和不相等。,因为,excel,将数值按照,15,位计算精度带入计算所致。如图，在,excel,选项中勾选“将精度设为所显示的精度”，可以解决上述情况的发生，但这样也降低了工作薄中所有工作表的计算精度，但不影响其他工作薄。,此外，也可以用,ROUND,函数对数值进行修约后再求和。,20,函数与公式通用技巧,分布查看公式计算结果,方法一：,选中包含公式的单元格，单击“公式”选项卡中的“公式求值”按钮，弹出“公式求值”对话框，然后多次单击“求值”按钮，“求值”域中将按照公式计算的顺序逐步地显示公式的计算过程。,方法二：,功能键除了可以重算工作薄外，还可以用于查看公式的运算结果。,当一个函数公式比较长时，可以用,键对各个部分进行分段计算，以便公式差错和更正。在分段选择时，注意要包含一个完整的运算对象。,按,功能键切换,21,数据的筛选,请使用,若要查找,?,（问号）,任何单个字符,例如，,sm?th,查找“,smith”,和“,smyth”,*（星号）,任何字符数,例如，*,east,查找“,Northeast”,和“,Southeast”,（波形符）后跟,?,、* 或,问号、星号或波形符,例如，“,fy91?”,将会查找“,fy91?”,通配符,以下通配符可作为筛选以及查找和替换内容时的比较条件,:,22,分类汇总的概述,分类汇总是对数据清单进行数据分析的一种方法,.,Data/ Subtotal,分类汇总,23,确保要进行分类汇总的数据为下列格式：第一行的每一列都有标志，并且同一列中应包含相似的数据，在区域中没有空行或空列。,单击要分类汇总的列中的单元格。在上面的示例中，应单击“运动”列（列,B,）中的单元格。,单击“升序排序” 或“降序排序”。,在“数据”菜单上，单击“分类汇总”。,在“分类字段”框中，单击要分类汇总的列。在上面示例中，应单击“运动”列。,在“汇总方式”框中，单击所需的用于计算分类汇总的汇总函数。,在“选定汇总项”框中，选中包含了要进行分类汇总的数值的每一列的复选框。在上面的示例中，应选中“销售”列。,分类汇总,要分类汇总的列,分类汇总,步骤,:,24,如果想在每个分类汇总后有一个自动分页符，请选中“每组数据分页”复选框。,如果希望分类汇总结果出现在分类汇总的行的上方，而不是在行的下方，请清除“汇总结果显示在数据下方”复选框。,单击“确定”。,注释 可再次使用“分类汇总”命令来添加多个具有不同汇总函数的分类汇总。若要防止覆盖已存在的分类汇总，请清除“替换当前分类汇总”复选框。,分类汇总,要分类汇总的列,分类汇总,25,删除分类汇总,删除分类汇总时，,Microsoft Excel,也删除分级显示以及随分类汇总一起插入列表中的所有分页符。,在含有分类汇总的列表中，单击任一单元格。,在“数据”菜单上，单击“分类汇总”。,单击“全部删除”。,分类汇总,26,删除分类汇总,删除分类汇总时，,Microsoft Excel,也删除分级显示以及随分类汇总一起插入列表中的所有分页符。,在含有分类汇总的列表中，单击任一单元格。,在“数据”菜单上，单击“分类汇总”。,单击“全部删除”。,分类汇总,27,字符函数,Left/Right:,截取字符串函数,用途,:,根据指定的字符数返回文本串中的第一个或前几个字符。,语法：,Left(,文本，返回的字符数,),Right(,文本，返回的字符数,),参数：返回的字符数必须大于或等于,0,实例：返回客户姓名中的姓氏,A1,单元格字符串“,张三,”,使用,Left(A1,1),得到结果,“,张,”,28,字符函数,Left/Right:,综合应用,返回身份证号中的“,出生年份,”信息,例如：,A1,单元格有身份证号码“,352624,1982,”,使用,Right(,Left(A1,10),4),得到结果,1982,29,字符函数,Mid:,截取字符串函数,用途,: Mid,返回文本串中从指定位置开始的特定数目的字符，该数目可以由用户指定,.,语法：,Mid(,文本串，开始位置，返回字符的个数,),参数：开始位置是文本中要提取的第一个字符的位置，文本中第一个字符的开始位置为,1,，以此类推。,例如：,A1,单元格有身份证号码“,352624,1982,”,使用,Mid(A1,7,4),得到结果,”1982”,30,字符函数,LEN:,计算字符串长度,用途,: LEN,返回文本的字符数,语法：,LEN(,文本,),参数：此函数用于,双字节,字符，且空格也将作为字符进行统计。,例如,：检查身份证号码是否输入了,18,位,。,A1,单元格有身份证号码“,352624,1982,”,使用,LEN(A1),得到,结果,18,类似的函数,LENB,，返回,单字节,字符。,例如：,LENB(“ABC”),返回,3,，,LENB(“,张三,”),返回,4,31,字符函数,TRIM:,删除指定字符串多余的前导和后缀的空格,用途,:,删除,前导,和后缀的空格,语法：,TRIM(,文本,),参数：字符串,例如,：,A1,单元格,有姓名,“ 张三 ”,使用,TRIM,（,A1,）,得到结果,“张三”,32,字符函数,Replace:,替换指定位置处的任意文本,用途,: REPLACE,使用其他文本串并根据所指定的字符数替换另一文本串中的部分文本。,语法：,Replace(,需要替换其部分字符的文本,，,被替换字符的起始位置,，,替换的字符个数,，,用于替换到原文本中去的字符,),例如,：,A1,单元格,有公司名称,“,广东,XXX,有限公司,”，,将,“广东”,替换成,“广东省”,。,使用,Replace(A1,1,2,”,广东省,”),得到结果,”,广东,省,XXX,有限公司,”,33,字符函数,SUBSTITUTE:,替换指定文本,用途,:,在文字串中用新文本替代旧文本。如果需要在一个文字串中替换指定文本，可以使用,SUBSTITUTE,；如果需要在某一字符串中替换指定位置处的任意文本，就应该使用函数,REPLACE,。,语法：,SUBSTITUTE,(,需要替换其部分字符的文本，需被替换旧文本，用于替换的文本，,第几次出现的旧文本,),参数：如果不输入第四个参数将替换所有旧文本。,例如,：,A1,单元格有客户编码,”,G000G001,”,将第二个,”G”,替换成,”0”.,使用,SUBSTITUTE(A1,G,“0,2),得到结果,”G0000001,”,使用,SUBSTITUTE(A1,G,“0,),得到结果,”00000001,”,34,字符函数,Replace,、,SUBSTITUTE,综合应用,案例说明：将下图中凌乱的编码进行整理,使用函数：,=,REPLACE(,SUBSTITUTE(A1, ,),2,1,M ),35,字符函数,Rept:,补位函数,用途,:,按照给定的次数重复显示文本。可以通过,REPT,函数对单元格进行重复填充。,语法：,Rept(,需要重复显示的文本，重复显示的次数,),参数：重复显示的次数需要为正数。,实例：,Rept,（,”,很好,”,2,）,返回“,很好很好,”。,36,字符函数,Rept,函数与,LEN,函数的综合应用,使用函数：,=,REPT(0,6-,LEN(A1),) &,A1,37,日期函数,NOW/TODAY():,返回当前日期,用途,:,返回当前电脑的日期和时间,语法：,NOW(),参数：此函数为无参数函数,实例：,=NOW,(),返回,“,2013-11-05 11:00,”,相对应的函数：,TODAY,(),返回“,2013-11-05,”,38,YEAR:,返回给定数值的年份,用途,:,返回给定数值年份信息,语法：,YEAR,（值）,参数：参数值应为日期类型的单元格,实例：例如：,A1,单元格有日期“,2013-11-5,”,YEAR,（,A1,）返回 值,“,2013,”,日期函数,39,MONTH:,返回给定数值的月份,用途,:,返回给定数值月份信息,语法：,MONTH,（值）,参数：参数值应为日期类型的单元格,实例：例如：,A1,单元格有日期“,2013-11-5,”,MONTH,（,A1,）返回 值,“,11,”,日期函数,40,DAY:,返回给定数值的天信息,用途,:,返回给定数值月份天信息,语法：,DAY,（值）,参数：参数值应为日期类型的单元格,实例：例如：,A1,单元格有日期“,2013-11-5,”,DAY,（,A1,）返回 值,“,5,”,相类似的函数：,HOUR,（时）、,MINUT,（分）、,SECOND(,秒,),日期函数,41,WEEKDAY:,返回某日期的星期数,用途,:,在默认情况下，它的值为,1(,星期天,),到,7(,星期六,),之间的一个整数,语法：,DAY,（值，返回类型）,参数：值是要返回日期数的日期。返回值类型,例如：,WEEKDAY,(,“,2013-11-07,”,2,),返回,4,WEEKDAY,(,“,2013-11-07,”,1),返回,5,日期函数,42,财务函数用于财务的计算,它可以根据利率、贷款金额和期限计算出所要支付的金额,它们的变量紧密相互关联。,如：,PMT(Rate,Nper,Pv,Fv,Type),=PMT,（,0.006,360,-300000),其中,0.006,是每一笔贷款项的利率,360,是款项的数目,-300000,是,360,笔款项的当前金额,(,在计算支付金额时,表示当前和将来金额的变量至少有一个通常是负数,),财务函数,43,