数据管理与决策第七讲

《数据管理与决策第七讲》由会员分享，可在线阅读，更多相关《数据管理与决策第七讲（44页珍藏版）》请在装配图网上搜索。

1、EXCELEXCEL培训培训理解函数字符函数日期函数统计函数数学函数查找函数课后习题EXCELEXCEL函数应用函数应用理解函数理解函数数 据规则什 么 是 函 数？结果函 数 的 格 式SUM(1，3，5)=9函 数 名数 据结 果EXCEL函数的结构 Excel 函数即是预先定义，执行计算、分析等处理数据任务的特殊公式特殊公式。Excel 函数结构:也有一些函数是没有参数的,如ROW()左右括号成对出现单一结构嵌套结构参数与参数之间使用半角逗号进行分隔使用公式公式中的运算符1.算术运算符算术运算符号算术运算符号运算符含义运算符含义示示 例例+(加号加号)加加2+3=5-(减号减号)减减3-

2、1=2*(星号星号乘乘3*2=6/(斜杠斜杠)除除6/2=3%(百分号百分号)百分号百分号50%(脱字号脱字号)乘方乘方43=43=64使用公式公式中的运算符2.文本运算符 “&”号,可以将文本连接起来.3.比较运算符比较运算符比较运算符运算符含义运算符含义示示 例例=(等号等号)相等相等B1=C1,若若B1中单元格内的值确实与中单元格内的值确实与C1中的值相中的值相等等,则产生逻辑真值则产生逻辑真值TRUE,若不相等若不相等,则产生逻辑则产生逻辑假值假值FALSE(小于号小于号)小于小于B1(大于号大于号)大于大于B1C1,若若B1中数值为中数值为6,C1中数值为中数值为4,则条件成则条件成

3、立产生逻辑真值立产生逻辑真值TRUE,否则产生逻辑假值否则产生逻辑假值FALSE=(大于等于号大于等于号)大于等于大于等于B1=C1(不等号不等号)不等于不等于B1C1=(小于等于号小于等于号)小于等于小于等于B1=C1使用公式公式中的运算符4.引用运算符引用运算符引用运算符运算符含义运算符含义示示 例例:区域运算符区域运算符,产生对包产生对包括在丙相引用之间的括在丙相引用之间的所有单元格的引用所有单元格的引用(A5:A15),联合运算符联合运算符,将多个引将多个引用合并为一个引用用合并为一个引用SUM(A5:A15,C5:C15)(空格空格)交叉运算符交叉运算符,产生对两产生对两个引用构有的

4、单元格个引用构有的单元格的引用的引用(B7:D7 C6:C8)例:A4=B4+C4+C4+E4+F4,可写为:A4=SUM(B4:F4)使用公式公式中的运算顺序 如果在公式中要同时使用多个运算符,则应该了解运算符的优先级.算术运算符的优先级是先乘幂运算,再乘、除运算,最后为加、减运算。相同优先级的运算符按从左到右的次序进行运算。公式中出现不同类型的运算符混用时,运算次序是：引用运算符算术运算符文本运算符。如果需要改变次序,可将先要计算的部分括上圆括号。使用公式引用单元格1.相对引用 相对引用指公式中的单元格位置将随着公式单元格的位置而改变2.绝对引用 绝对引用是指公式和函数中的位置是固定不变的

5、.绝对引用是在列字母和行数字之前都加上美元符号”$”,如$A$4,$C$63.混合引用 混合引用是指在一个单元格引用中,既有绝对引用,也有相对引用.例:行变列不变:$A4 列变行不变:A$4创建公式公式是指对工作表中的数据进行分析和处理的一个等式,不仅用于算术计算,也用于逻辑运算、日期和时间的计算等。一个公式必须具备3个条件：1.以等式开头;2.含有运算符和运算对象;3.能产生结果公式的3个基本组成部分:1.数值:包括数字和文本2.单元格引用3.操作符 创建公式创建公式的方法1.使用编辑栏2.直接在单元格中输入创建3种类型的公式1.创建简单公式;2.创建包含引用或名称的公式3.创建包含函数的公

6、式使用函数函数是指预定义的内置公式,可以进行数学、文本和逻辑的运算或者查找工作表的信息。函数是一个已经提供给用户的公式,并且有一个描述性的名称。函数结构1.函数名称：如果要查看可用函数的列表,可以单击一个单元格并按Shift+F3键；2.参数：参数可以是数字、文本、逻辑值（TRUE或FALSE）、数值、错误值（如#N/A）或单元格引用。3.参数工具提示：在输入函数时,会出现一个带有语法和参数的工具提示。4.输入公式：“插入函数”对话框。函数的参数函数的参数是一个函数体中括号内的部分逻辑值:FALSE:需要完全一致 TRUE:相似值亦可转换使用函数函数的类型 1.常用函数:常用函数指经常使用的函

7、数,如求和或计算算术平均值等.包括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

8、_true,Value_if_false)执行真假判断执行真假判断,根据对指定条件进行逻辑评价的真假根据对指定条件进行逻辑评价的真假而返回不同的结果。而返回不同的结果。HYPERLINK(Link_location,Friendly_name)创建快捷方式创建快捷方式,以便打开文档或网络驱动器或连接连以便打开文档或网络驱动器或连接连接接INTERNETCOUNT(Value1,Value2,)计算参数表中的数字参数和包含数字的单元格的个数计算参数表中的数字参数和包含数字的单元格的个数MAX(number1,number2,)返回一组数值中的最大值返回一组数值中的最大值,忽略逻辑值和文字符忽略逻

9、辑值和文字符SIN(number)返回纪念品定角度的正弦值返回纪念品定角度的正弦值SUMIF(Range,Criteria,Sum_range)根据指定条伯对若干单元格求和根据指定条伯对若干单元格求和PMT(Rate,Nper,Pv,Fv,Type)返回在固定利率下返回在固定利率下,投资或贷款的等额分期偿还额投资或贷款的等额分期偿还额使用函数函数的类型 2.财务函数:财务函数用于财务的计算,它可以根据利率、贷款金额和期限计算出所要支付的金额,它们的变量紧密相互关联。如：PMT(Rate,Nper,Pv,Fv,Type)=PMT（0.006,360,-300000)其中,0.006是每一笔贷款项

10、的利率,360是款项的数目,-300000是360笔款项的当前金额(在计算支付金额时,表示当前和将来金额的变量至少有一个通常是负数)注:Rate 贷款利率。Nper 该项贷款的付款总数。Pv 现值,或一系列未来付款的当前值的累积和,也称为本金。Fv 为未来值,或在最后一次付款后希望得到的现金余额,如果省略 fv则假设其值为零,也就是一笔贷款的未来值为零。Type 数字 0 或 1,用以指定各期的付款时间是在期初还是期末。使用函数函数的类型 3.时间和日期函数:主要用于分析和处理日期值和时间值,包括DATE、DATEVALUE、DAY、HOUR、TIME、TODAY、WEEKDAY 和 YEAR

11、等。例：函数DATA语法：DATE(year,month,day)在单元格中插入当前日期和时间 插入静态的日期和时间当前日期 选取一个单元格,并按 Ctrl+;当前时间 选取一个单元格,并按 Ctrl+Shift+；当前日期和时间 选取一个单元格,并按 Ctrl+;,然后按空格键,最后按 Ctrl+Shift+;插入会更新的日期和时间使用 TODAY 和 NOW 函数完成这一工作。使用函数函数的类型 4.数学与三角函数:5.统计函数:统计函数是用来对数据区域进行统计分析。主要用于进行各种各样的计算,包括ABS、ASIN、COMBINE、COSLOG、PI、ROUND、SIN、TAN 和TUUN

12、C等。例：ABS函数语法：ABS(number)函数是一种特殊的公式,因此所有的函数要以“=”开始。使用方法如下：1.选中要输入函数的单元格 2.单击Insert菜单中的 命令,弹出如图所示的对话框：使用函数3.在上图的对话框中,选择函数分类下面的函数类型及函数名下面的对应函数名。4.单击确定按钮,弹出如图所示的相应的调用函数对话框：使用函数限制：限制：突破：突破：使用定义名称。使用定义名称。当参数为单元格区域引用时，当参数为单元格区域引用时，可使用一对括号来包含多个单可使用一对括号来包含多个单元格区域的引用部分形成合并元格区域的引用部分形成合并区域引用作为函数参数。区域引用作为函数参数。1

13、1、提前将单元格格式设置为文、提前将单元格格式设置为文本后再输入数字本后再输入数字2 2、在输入数字时用半角单引号、在输入数字时用半角单引号开头。开头。公式内容的长度不能超过公式内容的长度不能超过10241024个个字符；字符；公式中函数的嵌套不能超过公式中函数的嵌套不能超过7 7层；层；公式中函数的参数不能超过公式中函数的参数不能超过3030个；个；数字计算精度为数字计算精度为1515位。位。在实际工作中，许多用户在计算在实际工作中，许多用户在计算数字时会设置单元格格式为数字时会设置单元格格式为2 2为小数的为小数的数值、货币格式或者是整数值。但在数值、货币格式或者是整数值。但在使用公式进行

14、数据处理的过程中，经使用公式进行数据处理的过程中，经常会发现几个单元格之和与合计单元常会发现几个单元格之和与合计单元格显示的数据之和不相等。格显示的数据之和不相等。因为因为excelexcel将数值按照将数值按照1515位计算精位计算精度带入计算所致。如图，在度带入计算所致。如图，在excelexcel选项选项中勾选中勾选“将精度设为所显示的精度将精度设为所显示的精度”，可以解决上述情况的发生，但这样也降可以解决上述情况的发生，但这样也降低了工作薄中所有工作表的计算精度，低了工作薄中所有工作表的计算精度，但不影响其他工作薄。但不影响其他工作薄。此外，也可以用此外，也可以用ROUNDROUND函

15、数对数值函数对数值进行修约后再求和。进行修约后再求和。方法一：方法一：选中包含公式的单元格，单击选中包含公式的单元格，单击“公式公式”选项卡中的选项卡中的“公式求值公式求值”按钮，弹出按钮，弹出“公式公式求值求值”对话框，然后多次单击对话框，然后多次单击“求值求值”按钮，按钮，“求值求值”域中将按照公式计算的顺序逐步地域中将按照公式计算的顺序逐步地显示公式的计算过程。显示公式的计算过程。方法二：方法二：功能键除了可以重算工作薄外，功能键除了可以重算工作薄外，还可以用于查看公式的运算结果。还可以用于查看公式的运算结果。当一个函数公式比较长时，可以用当一个函数公式比较长时，可以用键对各个部分进行分

16、段计算，以便键对各个部分进行分段计算，以便公式差错和更正。在分段选择时，注意公式差错和更正。在分段选择时，注意要包含一个完整的运算对象。要包含一个完整的运算对象。按按功能键切换功能键切换数据的筛选请使用若要查找?（问号）任何单个字符例如，sm?th 查找“smith”和“smyth”*（星号）任何字符数例如，*east 查找“Northeast”和“Southeast”（波形符）后跟?、*或 问号、星号或波形符例如，“fy91?”将会查找“fy91?”通配符通配符以下通配符可作为筛选以及查找和替换内容时的比较条件:分类汇总的概述分类汇总是对数据清单进行数据分析的一种方法.Data/Subtot

17、al分类汇总1.确保要进行分类汇总的数据为下列格式：第一行的每一列都有标志，并且同一列中应包含相似的数据，在区域中没有空行或空列。2.单击要分类汇总的列中的单元格。在上面的示例中，应单击“运动”列（列 B）中的单元格。3.单击“升序排序”或“降序排序”。4.在“数据”菜单上，单击“分类汇总”。5.在“分类字段”框中，单击要分类汇总的列。在上面示例中，应单击“运动”列。6.在“汇总方式”框中，单击所需的用于计算分类汇总的汇总函数。7.在“选定汇总项”框中，选中包含了要进行分类汇总的数值的每一列的复选框。在上面的示例中，应选中“销售”列。分类汇总 要分类汇总的列 分类汇总步骤步骤:8.如果想在每个

18、分类汇总后有一个自动分页符，请选中“每组数据分页”复选框。9.如果希望分类汇总结果出现在分类汇总的行的上方，而不是在行的下方，请清除“汇总结果显示在数据下方”复选框。10.单击“确定”。11.注释 可再次使用“分类汇总”命令来添加多个具有不同汇总函数的分类汇总。若要防止覆盖已存在的分类汇总，请清除“替换当前分类汇总”复选框。分类汇总 要分类汇总的列 分类汇总删除分类汇总删除分类汇总时，Microsoft Excel 也删除分级显示以及随分类汇总一起插入列表中的所有分页符。在含有分类汇总的列表中，单击任一单元格。在“数据”菜单上，单击“分类汇总”。单击“全部删除”。分类汇总删除分类汇总删除分类汇

19、总时，Microsoft Excel 也删除分级显示以及随分类汇总一起插入列表中的所有分页符。在含有分类汇总的列表中，单击任一单元格。在“数据”菜单上，单击“分类汇总”。单击“全部删除”。分类汇总字符函数字符函数Left/Right:截取字符串函数用途:根据指定的字符数返回文本串中的第一个或前几个字符。语法：Left(文本，返回的字符数)Right(文本，返回的字符数)参数：返回的字符数必须大于或等于0实例：返回客户姓名中的姓氏 A1单元格字符串“张三”,使用Left(A1,1)得到结果“张”字符函数字符函数Left/Right:综合应用返回身份证号中的“出生年份”信息例如：A1单元格有身份证

20、号码“352624198208226319”使用Right(Left(A1,10),4)得到结果1982字符函数字符函数Mid:截取字符串函数用途:Mid返回文本串中从指定位置开始的特定数目的字符，该数目可以由用户指定.语法：Mid(文本串，开始位置，返回字符的个数)参数：开始位置是文本中要提取的第一个字符的位置，文本中第一个字符的开始位置为1，以此类推。例如：A1单元格有身份证号码“352624198208226319”使用Mid(A1,7,4)得到结果”1982”字符函数字符函数LEN:计算字符串长度用途:LEN返回文本的字符数 语法：LEN(文本)参数：此函数用于双字节字符，且空格也将作

21、为字符进行统计。例如：检查身份证号码是否输入了18位。A1单元格有身份证号码“352624198208226319”使用LEN(A1)得到结果18类似的函数LENB，返回单字节字符。例如：LENB(“ABC”)返回 3，LENB(“张三”)返回4字符函数字符函数TRIM:删除指定字符串多余的前导和后缀的空格用途:删除前导和后缀的空格 语法：TRIM(文本)参数：字符串例如：A1单元格有姓名“张三 ”使用TRIM（A1）得到结果“张三”字符函数字符函数Replace:替换指定位置处的任意文本用途:REPLACE使用其他文本串并根据所指定的字符数替换另一文本串中的部分文本。语法：Replace(需

22、要替换其部分字符的文本，被替换字符的起始位置，替换的字符个数，用于替换到原文本中去的字符)例如：A1单元格有公司名称“广东XXX有限公司”，将“广东”替换成“广东省”。使用Replace(A1,1,2,”广东省”)得到结果”广东省XXX有限公司”字符函数字符函数SUBSTITUTE:替换指定文本用途:在文字串中用新文本替代旧文本。如果需要在一个文字串中替换指定文本，可以使用SUBSTITUTE；如果需要在某一字符串中替换指定位置处的任意文本，就应该使用函数REPLACE。语法：SUBSTITUTE(需要替换其部分字符的文本，需被替换旧文本，用于替换的文本，第几次出现的旧文本)参数：如果不输入第

23、四个参数将替换所有旧文本。例如：A1单元格有客户编码”G000G001”,将第二个”G”替换成”0”.使用SUBSTITUTE(A1,G,“0,2)得到结果”G0000001”使用SUBSTITUTE(A1,G,“0)得到结果”00000001”字符函数字符函数Replace、SUBSTITUTE综合应用案例说明：将下图中凌乱的编码进行整理使用函数：=REPLACE(SUBSTITUTE(A1,),2,1,M)字符函数字符函数Rept:补位函数用途:按照给定的次数重复显示文本。可以通过REPT函数对单元格进行重复填充。语法：Rept(需要重复显示的文本，重复显示的次数)参数：重复显示的次数需要

24、为正数。实例：Rept（”很好”,2）返回“很好很好”。字符函数字符函数Rept函数与LEN函数的综合应用使用函数：=REPT(0,6-LEN(A1)&A1日期日期函数函数NOW/TODAY():返回当前日期用途:返回当前电脑的日期和时间语法：NOW()参数：此函数为无参数函数实例：=NOW()返回“2013-11-05 11:00”相对应的函数：TODAY()返回“2013-11-05”YEAR:返回给定数值的年份用途:返回给定数值年份信息语法：YEAR（值）参数：参数值应为日期类型的单元格实例：例如：A1单元格有日期“2013-11-5”YEAR（A1）返回 值“2013”日期日期函数函数

25、MONTH:返回给定数值的月份用途:返回给定数值月份信息语法：MONTH（值）参数：参数值应为日期类型的单元格实例：例如：A1单元格有日期“2013-11-5”MONTH（A1）返回 值“11”日期日期函数函数DAY:返回给定数值的天信息用途:返回给定数值月份天信息语法：DAY（值）参数：参数值应为日期类型的单元格实例：例如：A1单元格有日期“2013-11-5”DAY（A1）返回 值“5”相类似的函数：HOUR（时）、MINUT（分）、SECOND(秒)日期日期函数函数WEEKDAY:返回某日期的星期数用途:在默认情况下，它的值为1(星期天)到7(星期六)之间的一个整数语法：DAY（值，返回类型）参数：值是要返回日期数的日期。返回值类型例如：WEEKDAY(“2013-11-07”,2)返回4 WEEKDAY(“2013-11-07”,1)返回5日期日期函数函数财务函数用于财务的计算,它可以根据利率、贷款金额和期限计算出所要支付的金额,它们的变量紧密相互关联。如：PMT(Rate,Nper,Pv,Fv,Type)=PMT（0.006,360,-300000)其中,0.006是每一笔贷款项的利率,360是款项的数目,-300000是360笔款项的当前金额(在计算支付金额时,表示当前和将来金额的变量至少有一个通常是负数)财务函数财务函数演讲完毕，谢谢观看！