数据管理与决策第七讲

EXCEL培训理解函数字符函数日期函数统计函数数学函数查找函数课后习题EXCEL函数应用理解函数数据规则什么是函数？结果函数的格式SUM(1，3，5)=9函数名数据结果EXCEL函数的结构•Excel函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。Excel函数结构:也有一些函数是没有参数的,如ROW()左右括号成对出现单一结构嵌套结构参数与参数之间使用半角逗号进行分隔使用公式公式中的运算符1.算术运算符算术运算符号运算符含义示例+(加号)加2+3=5-(减号)减3-1=2*(星号乘3*2=6/(斜杠)除6/2=3%(百分号)百分号50%^(脱字号)乘方4^3=43=64使用公式公式中的运算符2.文本运算符“&”号,可以将文本连接起来.3.比较运算符比较运算符运算符含义示例=(等号)相等B1=C1,若B1中单元格内的值确实与C1中的值相等,则产生逻辑真值TRUE,若不相等,则产生逻辑假值FALSE(小于号)小于B1C1(大于号)大于B1C1,若B1中数值为6,C1中数值为4,则条件成立产生逻辑真值TRUE,否则产生逻辑假值FALSE=(大于等于号)大于等于B1=C1(不等号)不等于B1C1=(小于等于号)小于等于B1=C1使用公式公式中的运算符4.引用运算符引用运算符运算符含义示例:区域运算符,产生对包括在丙相引用之间的所有单元格的引用(A5:A15),联合运算符,将多个引用合并为一个引用SUM(A5:A15,C5:C15)(空格)交叉运算符,产生对两个引用构有的单元格的引用(B7:D7C6:C8)例:A4=B4+C4+C4+E4+F4,可写为:A4=SUM(B4:F4)使用公式公式中的运算顺序•如果在公式中要同时使用多个运算符,则应该了解运算符的优先级.算术运算符的优先级是先乘幂运算,再乘、除运算,最后为加、减运算。相同优先级的运算符按从左到右的次序进行运算。•公式中出现不同类型的运算符混用时,运算次序是：引用运算符——算术运算符——文本运算符。如果需要改变次序,可将先要计算的部分括上圆括号。使用公式引用单元格1.相对引用相对引用指公式中的单元格位置将随着公式单元格的位置而改变2.绝对引用绝对引用是指公式和函数中的位置是固定不变的.绝对引用是在列字母和行数字之前都加上美元符号”$”,如$A$4,$C$63.混合引用•混合引用是指在一个单元格引用中,既有绝对引用,也有相对引用.•例:行变列不变:$A4•列变行不变:A$4创建公式公式是指对工作表中的数据进行分析和处理的一个等式,不仅用于算术计算,也用于逻辑运算、日期和时间的计算等。一个公式必须具备3个条件：1.以等式开头;2.含有运算符和运算对象;3.能产生结果公式的3个基本组成部分:1.数值:包括数字和文本2.单元格引用3.操作符•创建公式创建公式的方法1.使用编辑栏2.直接在单元格中输入创建3种类型的公式1.创建简单公式;2.创建包含引用或名称的公式3.创建包含函数的公式使用函数函数是指预定义的内置公式,可以进行数学、文本和逻辑的运算或者查找工作表的信息。函数是一个已经提供给用户的公式,并且有一个描述性的名称。函数结构1.函数名称：如果要查看可用函数的列表,可以单击一个单元格并按Shift+F3键；2.参数：参数可以是数字、文本、逻辑值（TRUE或FALSE）、数值、错误值（如#N/A）或单元格引用。3.参数工具提示：在输入函数时,会出现一个带有语法和参数的工具提示。4.输入公式：“插入函数”对话框。函数的参数函数的参数是一个函数体中括号内的部分逻辑值:FALSE:需要完全一致TRUE:相似值亦可转换使用函数函数的类型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)创建快捷方式,以便打开文档或网络驱动器或连接连接INTERNETCOUNT(Value1,Value2,…)计算参数表中的数字参数和包含数字的单元格的个数MAX(number1,number2,…)返回一组数值中的最大值,忽略逻辑值和文字符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是每一笔贷款项的利率,360是款项的数目,-300000是360笔款项的当前金额(在计算支付金额时,表示当前和将来金额的变量至少有一个通常是负数)注:Rate贷款利率。Nper该项贷款的付款总数。Pv现值,或一系列未来付款的当前值的累积和,也称为本金。Fv为未来值,或在最后一次付款后希望得到的现金余额,如果省略fv则假设其值为零,也就是一笔贷款的未来值为零。Type数字0或1,用以指定各期的付款时间是在期初还是期末。使用函数函数的类型3.时间和日期函数:主要用于分析和处理日期值和时间值,包括DATE、DATEVALUE、DAY、HOUR、TIME、TODAY、WEEKDAY和YEAR等。例：函数DATA语法：DATE(year,month,day)•在单元格中插入当前日期和时间•插入静态的日期和时间•当前日期选取一个单元格,并按Ctrl+;•当前时间选取一个单元格,并按Ctrl+Shift+；•当前日期和时间选取一个单元格,并按Ctrl+;,然后按空格键,最后按Ctrl+Shift+;•插入会更新的日期和时间•使用TODAY和NOW函数完成这一工作。使用函数函数的类型•4.数学与三角函数:5.统计函数:统计函数是用来对数据区域进行统计分析。主要用于进行各种各样的计算,包括ABS、ASIN、COMBINE、COSLOG、PI、ROUND、SIN、TAN和TUUNC等。例：ABS函数语法：ABS(number)•函数是一种特殊的公式,因此所有的函数要以“=”开始。•使用方法如下：1.选中要输入函数的单元格2.单击[Insert]菜单中的命令,弹出如图所示的对话框：使用函数3.在上图的对话框中,选择[函数分类]下面的函数类型及[函数名]下面的对应函数名。4.单击[确定]按钮,弹出如图所示的相应的调用函数对话框：使用函数限制：突破：使用定义名称。当参数为单元格区域引用时，可使用一对括号来包含多个单元格区域的引用部分形成合并区域引用作为函数参数。1、提前将单元格格式设置为文本后再输入数字2、在输入数字时用半角单引号开头。公式内容的长度不能超过1024个字符；公式中函数的嵌套不能超过7层；公式中函数的参数不能超过30个；数字计算精度为15位。在实际工作中，许多用户在计算数字时会设置单元格格式为2为小数的数值、货币格式或者是整数值。但在使用公式进行数据处理的过程中，经常会发现几个单元格之和与合计单元格显示的数据之和不相等。因为excel将数值按照15位计算精度带入计算所致。如图，在excel选项中勾选“将精度设为所显示的精度”，可以解决上述情况的发生，但这样也降低了工作薄中所有工作表的计算精度，但不影响其他工作薄。此外，也可以用ROUND函数对数值进行修约后再求和。方法一：选中包含公式的单元格，单击“公式”选项卡中的“公式求值”按钮，弹出“公式求值”对话框，然后多次单击“求值”按钮，“求值”域中将按照公式计算的顺序逐步地显示公式的计算过程。方法二：F9功能键除了可以重算工作薄外，还可以用于查看公式的运算结果。当一个函数公式比较长时，可以用F9键对各个部分进行分段计算，以便公式差错和更正。在分段选择时，注意要包含一个完整的运算对象。按F9功能键切换数据的筛选请使用若要查找?（问号）任何单个字符例如，sm?th查找“smith”和“smyth”*（星号）任何字符数例如，*east查找“Northeast”和“Southeast”~（波形符）后跟?、*或~问号、星号或波形符例如，“fy91~?”将会查找“fy91?”通配符以下通配符可作为筛选以及查找和替换内容时的比较条件:•分类汇总的概述•分类汇总是对数据清单进行数据分析的一种方法.•Data/Subtotal分类汇总1.确保要进行分类汇总的数据为下列格式：第一行的每一列都有标志，并且同一列中应包含相似的数据，在区域中没有空行或空列。2.单击要分类汇总的列中的单元格。在上面的示例中，应单击“运动”列（列B）中的单元格。3.单击“升序排序”或“降序排序”。4.在“数据”菜单上，单击“分类汇总”。5.在“分类字段”框中，单击要分类汇总的列。在上面示例中，应单击“运动”列。6.在“汇总方式”框中，单击所需的用于计算分类汇总的汇总函数。7.在“选定汇总项”框中，选中包含了要进行分类汇总的数值的每一列的复选框。在上面的示例中，应选中“销售”列。分类汇总①要分类汇总的列②分类汇总步骤:8.如果想在每个分类汇总后有一个自动分页符，请选中“每组数据分页”复选框。9.如果希望分类汇总结果出现在分类汇总的行的上方，而不是在行的下方，请清除“汇总结果显示在数据下方”复选框。10.单击“确定”。11.注释可再次使用“分类汇总”命令来添加多个具有不同汇总函数的分类汇总。若要防止覆盖已存在的分类汇总，请清除“替换当前分类汇总”复选框。分类汇总①要分类汇总的列②分类汇总•删除分类汇总•删除分类汇总时，MicrosoftExcel也删除分级显示以及随分类汇总一起插入列表中的所有分页符。•在含有分类汇总的列表中，单击任一单元格。•在“数据”菜单上，单击“分类汇总”。•单击“全部删除”。分类汇总•删除分类汇总•删除分类汇总时，MicrosoftExcel也删除分级显示以及随分类汇总一起插入列表中的所有分页符。•在含有分类汇总的列表中，单击任一单元格。•在“数据”菜单上，单击“分类汇总”。•单击“全部删除”。分类汇总字符函数Left/Right:截取字符串函数•用途:根据指定的字符数返回文本串中的第一个或前几个字符。•语法：Left(文本，返回的字符数)Right(文本，返回的字符数)•参数：返回的字符数必须大于或等于0•实例：返回客户姓名中的姓氏A1单元格字符串“张三”,使用Left(A1,1)得到结果“张”字符函数Left/Right:综合应用•返回身份证号中的“出生年份”信息•例如：A1单元格有身份证号码“352624198208226319”•使用Right(Left(A1,10),4)得到结果1982字符函数Mid:截取字符串函数•用途:Mid返回文本串中从指定位置开始的特定数目的字符，该数目可以由用户指定.•语法：Mid(文本串，开始位置，返回字符的个数)•参数：开始位置是文本中要提取的第一个字符的位置，文本中第一个字符的开始位置为1，以此类推。•例如：A1单元格有身份证号码“352624198208226319”•使用Mid(A1,7,4)得到结果”1982”字符函数LEN:计算字符串长度•用途:LEN返回文本的字符数•语法：LEN(文本)•参数：此函数用于双字节字符，且空格也将作为字符进