EXCEL教程——公式与函数教程ppt课件

ExcelExcel高级教程高级教程公式与函数公式与函数Excel高级教程公式与函数1公式与函数一、引用二、公式三、函数四、几个重要函数五、注意公式中的数值转换六、应用实例七、小结公式与函数一、引用2公式与函数引用什么是引用？引用的作用在于标识工作表上的单元格或者单元格区域，并指明公式中所使用的数据的位置。通过引用，可以在公式中使用工作表不同部分的数据，或者在多个公式中使用同一个单元格的数值。还可以引用同一个工作簿不同工作表的单元格、不同工作簿的单元格、甚至其他应用程序的数值。后两种成为外部引用和远程引用，在本课程中不作介绍，有兴趣的同学可以参考相关资料。公式与函数引用什么是引用？3公式与函数引用引用方式引用方式 A 1引用类型。此为默认的引用方式。这种类型用字母标志列（从A到IV，共256列），用数字标志行（从1到65536）。如果要引用单元格，必须输入列字母和行数字；单元格区域的引用则按格式：左上角单元格的引用：右下角单元格的引用。公式与函数引用引用方式4公式与函数引用引用方式引用方式表：表：A 1引用示例引用示例 引用内容引用内容引用方式引用方式引用内容引用内容引用方式引用方式在列B和行28中的单元格B28行2中所有单元格2:2列A中从行2到12中的单元格区域A2:A12列B中所有单元格B:B行2中从列B到列H的单元格区域B2:H2从行2到行12所有单元格2:12列B行2到列H行12的矩形区域B2:H12从列B到列H中所有单元格B:H公式与函数引用引用方式引用内容引用方式引用内容引用方式在5公式与函数引用引用方式引用方式 R1C1 R1C1引用引用。此方式行和列都是数字。在宏中计算行和列的位置时，或者需要显示单元格相对引用时，R1C1引用是很有用的。在此方式中，单元格的位置由“R”加行数字和“C”加列数字来标识。公式与函数引用引用方式6公式与函数引用引用方式引用方式表：表：R1C1R1C1引用示例引用示例 引引 用用含含 义R-3C对在同一列上面3行的单元格的相对引用R2C2对在下面两行右面两列的单元格的相对引用R2C2对在工作表的第2行第2列的单元格的绝对引用（即$B$2）R-1对活动单元格整个上面一行单元格区域的相对引用R对当前行的绝对引用公式与函数引用引用方式引用含义R7公式与函数公式公式语法公式中元素的结构或次序决定了最终的计算结果。Microsoft Excel 中的公式遵循一个特定的语法或次序：最前面是等号（=），后面是参与计算的元素（操作数），这些参与计算的元素又是通过运算符隔开的。每个操作数可以是不改变的数值（常量数值）、单元格或引用单元格区域、标志、名称、或工作表函数。即常量、单元格地址、名称和函数。运算符是为了对公式中的元素进行某种运算而规定的符号。Excel有4种运算符：算术运算符、比较运算符、文本运算符和引用运算符。公式与函数公式公式语法8公式与函数公式公式语法算术运算符算术运算符 要完成基本的数学运算，如加法、减法和乘法，连接数字和产生数字结果等，可使用以下算术运算符：算算术运算符运算符含含义示例示例+（加号）加3+3（减号）减 311*（星号）乘3*3/（斜杠）除3/3%（百分号）百分比20%（脱字符）乘方32（与 3*3 相同）公式与函数公式公式语法算术运算符含义示例+（加号）加39公式与函数公式公式语法比较操作符比较操作符 可以使用下列操作符比较两个值。当用操作符比较两个值时，结果是一个逻辑值，不是 TRUE 就是 FALSE。比比较运算符运算符含含义示例示例=（等号）等于A1=B1（大于号）大于A1B1（小于号）小于A1=（大于等于号）大于等于A1=B1=（小于等于号）小于等于A1=B1不等于不等于A1B1公式与函数公式公式语法比较运算符含义示例=（等号）等于10公式与函数公式公式语法文本串联符文本串联符 使用和号（&）加入或连接一个或更多字符串以产生一大片文本。文本运算文本运算符符含含义示例示例&(ampersand)将两个文本值连接或串起来产生一个连续的文本值North&wind 产生 Northwind公式与函数公式公式语法文本运算符含义示例&(amper11公式与函数公式公式语法引用操作符引用操作符 引用以下运算符可以将单元格区域合并计算。引用运算符引用运算符含含义示例示例:(colon)区域运算符，对两个引用之间，包括两个引用在内的所有单元格进行引用B5:B15，（逗号）联合操作符将多个引用合并为一个引用SUM(B5:B15,D5:D15)公式与函数公式公式语法引用运算符含义示例:(colon12公式与函数公式公式语法公式与函数公式公式语法13公式与函数公式公式的输入用公式进行计算时首先要输入计算公式。输入公式的操作类似于输入文字型数据，但在输入公式时必须以等号（）开始，后面才是公式的表达式。在单元格中输入公式的方法如下：选取要输入公式的单元格；在编辑栏上的输入框中输入一个等号（），再键入公式的表达式；单击输入按钮 ,或者按回车键即可得到该单元格的运算结果。公式与函数公式公式的输入14公式与函数日期和时间的处理在Excel中，日期和时间也被看作数值，因此它们也能用公式进行数值计算。但在对日期和时间进行处理时，有以下几点需要注意：不能在公式中直接输入日期或时间格式的数据，而要将日期或时间用双引号括起来，否则系统将作出错处理；计算公式时，Excel先将日期和时间转换成相应的数字，再进行数值计算；Excel不能识别自定义的时间或日期格式，因此只能采用内置的格式，如“月/日/年”等；当在Excel中输入日期时，年份为两位数。若输入的年份数字为0029，Excel将其解释为2000年2029年；若输入的年份数为3099，则解释为19301999。公式与函数日期和时间的处理在Excel中，日期和时间也被15公式与函数出错信息用户如果进行了错误的公式操作，Excel为用户提供了许多出错信息，以提示用户注意解决，下面列出部分提示供用户参考：“#”：并不表示公式出错，它表示单元格中输入的数值或公式太长，单元格容纳不下，此时用户可以用鼠标来修改列宽；“DIV/0”：表示公式中的除数为0，用户只要将除数变为非零数即可。“N/A”：这是专门为用户准备的，如果某个单元格中没有可用数值，用户应在单元格内填入“N/A”，否则Excel就会默认为数值0。“NAME?”：用以提示用户，Excel不能识别输入的文本，只需将输入的文本改为在Excel能识别的形式即可。“REF!”：提示用户，用户所使用的单元格引用内容无效。“VALUE”：当用户使用的操作数的数据类型，与运算符所要求的数据类型不匹配时，就会出现这个错误标识。公式与函数出错信息用户如果进行了错误的公式操作，Exce16公式与函数应用计算公式实例一:学生成绩表的计算高一（高一（1）班期末考）班期末考试成成绩表表学号学号姓名姓名性性别语文文数学数学英英语物理物理化学化学政治政治101李文李文男男848256898978108陈开开男男455670806564105张开良开良男男564356788956103刘一刘一女女892365568089104林毅婷林毅婷女女786945207056102梁小群梁小群女女724578364065公式与函数应用计算公式实例一:学生成绩表的计算高一（1）班17公式与函数应用计算公式公式与函数应用计算公式18公式与函数应用计算公式公式与函数应用计算公式19公式与函数应用计算公式公式与函数应用计算公式20公式与函数应用计算公式公式与函数应用计算公式21公式与函数应用计算公式实例二：文本连接实例二：文本连接公式与函数应用计算公式实例二：文本连接22公式与函数应用计算公式公式与函数应用计算公式23公式与函数函数函数的定义函数的定义函数是一些预定义的公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。例如，SUM 函数对单元格或单元格区域进行加法运算，PMT 函数在给定的利率、贷款期限和本金数额基础上计算偿还额。公式与函数函数函数的定义24公式与函数函数参数参数 参数可以是数字、文本、形如 TRUE 或 FALSE 的逻辑值、数组、形如#N/A 的错误值或单元格引用。给定的参数必须能产生有效的值。参数也可以是常量、公式或其它函数。结构结构 函数的结构以函数名称开始，后面是左圆括号、以逗号分隔的参数和右圆括号。如果函数以公式的形式出现，请在函数名称前面键入等号（=）。在创建包含函数的公式时，公式选项板将提供相关的帮助。公式与函数函数参数参数可以是数字、文本、形如TR25公式与函数函数公式与函数函数26公式与函数函数注意事项 函数是一种特殊的公式，因此所有的函数与公式一样要以“”开头；函数与公式不同，公式是对工作表的数据进行分析的等式，可以引用同一工作表中的其他单元格、同一工作簿内的不同工作表中的单元格，以及其他工作簿的工作表的单元格；而函数是已经预定义的内置公式，使用特定数值作参数，并按特定的语法顺序进行计算；函数名与括号之间没有空格，括号要紧跟数字之后，参数之间用逗号隔开，逗号与参数之间也不能插入空格或其他字符；公式与函数函数注意事项27公式与函数函数注意事项（续）用户必须通过合法的参数数值，否则就没办法在单元格中输入函数；如果函数参数行后面带有省略号（），表明用户可以在该函数中使用多个这种数据类型的参数，例如对函数AVERAGE(number1,number2,)，用户可以按以下方式运用：AVERAGE(5,6)，AVERAGE(1,2,3)等；如果函数名称后面带有一组空格号，则表明该函数不需要任何参数，但在使用该函数是必须带括号，以便Excel能够识别该函数。公式与函数函数注意事项（续）28公式与函数函数函数的输入函数的输入手工输入手工输入对于单变量函数或者简单函数，可以用手工输入的方法。手工输入法同在单元格中输入公式的方法一样，可以先在编辑栏中输入等号（），然后输入函数语句。当然，如果你对函数很熟悉，对于多变量函数和复杂函数也可以用手工输入法。公式与函数函数函数的输入29公式与函数函数函数的输入利用函数向导输入利用函数向导输入对于较复杂或参数较多的函数，可以使用函数向导来输入。这样可以按照向导的提示一步一步的输入复杂的函数，避免在输入过程中产生误操作。这种方法的步骤如下：选中要输入函数的单元格；单击“插入”菜单选择“函数”命令，或单击工具栏上的“粘贴函数”按钮 ，显示“粘贴函数”对话框（下图）；公式与函数函数函数的输入30公式与函数函数公式与函数函数31公式与函数函数在“函数分类”列表框中选择需要的函数形式；在“函数名”列表框中选择所需要的函数；单击“确定”按钮，打开所选函数对话框，在其中的文本框内输入函数的参数；单击“确定”按钮完成函数的输入。公式与函数函数在“函数分类”列表框中选择需要的函数形式；32公式与函数函数公式与函数函数33公式与函数函数公式与函数函数34公式与函数函数函数分类函数分类 Excel提供了300多个功能强大的函数，按其用途可以分为以下几类：财务函数财务函数 财务函数可以用来进行一般的财务计算，可以进行确定贷款的支付额、投资的未来值以及股票的价值等财务计算。详见附录常用财务函数。DB(cost,salvage,life,period,month)、PMT(rate,nper,pv,fv,type)、)公式与函数函数函数分类35公式与函数函数数学和三角函数数学和三角函数 数学和三角函数是进行数据处理时最常用的工具。这些函数能够完成一些简单的数学和三角函数计算。ABS(number)、EXP(number)、COS(number)、SQRT(number)、公式与函数函数数学和三角函数36公式与函数函数统计函数统计函数 统计函数用于对数据区域进行统计分析，它能计算均值、方差，预测发展趋势。AVERAGE(number1,number2,)、AVEDEV(number1,number2,)、MAX(number1,number2,)、VAR(number1,number2,)、RSQ(known-ys,known-xs公式与函数函数统计函数37公式与函数函数函数嵌套在某些情况下，您可能需要将某函数作为另一函数的参数使用。例如，下面的公式使用了嵌套的 AVERAGE 函数并将结果与值 50 进行了比较。公式与函数函数函数嵌套38公式与函数函数函数嵌套函数嵌套有效的返回值有效的返回值当嵌套函数作为参数使用时，它返回的数值类型必须与参数使用的数值类型相同。例如，如果参数返回一个TRUE或FALSE值，那么嵌套函数也必须返回一个TRUE或FALSE值。否则，MicrosoftExcel将显示#VALUE!错误值。嵌套级别限制嵌套级别限制公式可包含多达七级的嵌套函数。当函数B在函数A中用作参数时，函数B则为第二级函数。例如，AVERAGE函数和SUM函数都是第二级函数，因为它们都是IF函数的参数。在AVERAGE函数中嵌套的函数则为第三级函数，以此类推。公式与函数函数函数嵌套39公式与函数函数自定义函数自定义函数自定义函数，也叫用户定义函数，是Excel最富有创意和吸引力的功能之一，下面我们在VisualBasic模块中创建一个函数。在下面的例子中，我们要给每个人的金额乘一个系数，如果是上班时的工作餐，就打六折；如果是加班时的工作餐，就打五折；如果是休息日来就餐，就打九折。公式与函数函数自定义函数40公式与函数函数自定义函数自定义函数首先打开“工具”菜单，单击“宏”命令中的“VisualBasic编辑器”，进入VisualBasic编辑环境，在“工程VBAobject”栏中的当前表上单击鼠标右键，选择“插入”-“模块”，在右边栏创建下面的函数rrr，代码如下：Functionrrr(tatol,rr)Ifrr=上班Thenrrr=0.6*tatolElseIfrr=加班Thenrrr=0.5*tatolElseIfrr=休息日Thenrrr=0.9*tatolEndIfEndFunction公式与函数函数自定义函数41公式与函数函数自定义函数自定义函数公式与函数函数自定义函数42公式与函数函数自定义函数自定义函数这时关闭编辑器，只要我们在相应的列中输入rrr(F，B)，那么打完折后的金额就算出来了。公式与函数函数自定义函数43公式与函数数组公式数组是单元格的集合或一组要处理的值的集合。用户可以用一个数组公式进行多个输入操作，并计算多个结果，每个结果显示在一个单元格中。数组公式与单值公式的区别是数组公式可以同时计算两个或两个以上的计算结果。如果不想在工作表的单个单元格里输入每个常量值，则可用数组常量来代替引用。一些内置函数就是数组公式，因此必须将其作为数组输入才可得到正确结果。公式与函数数组公式数组是单元格的集合或一组要处理的值44公式与函数数组公式数组公式的输入输入数组公式的方法如下：选择要放入公式计算结果的单元格；输入公式“D3:D8+E3:E8+F3:F8+G3:G8+H3:H8+I3:I8”，此时不要单击“输入”按钮；公式与函数数组公式数组公式的输入45公式与函数数组公式公式与函数数组公式46公式与函数数组公式按下Shift+Ctrl+Enter键，就会看到公式被一对大括号“”括住，同时在已选的单元格显示计算结果。公式与函数数组公式按下Shift+Ctrl+Enter键47公式与函数数组公式数组公式的编辑数组公式的编辑 数组包含了许多单元格，这些单元格形成一个整体。不能对数组里的某个单元格进行编辑。在编辑数组之前，必须选取整个数组。选取步骤如下：选取数组中任一单元格；选择“编辑”菜单中的“定位”命令，显示“定位”对话框；公式与函数数组公式数组公式的编辑48公式与函数数组公式公式与函数数组公式49公式与函数数组公式单击“定位条件”按钮，将会出现“定位条件”对话框；选择“当前数组”选项，按“确定”按钮，回到表格中就可以看到数组已经选中了。公式与函数数组公式单击“定位条件”按钮，将会出现“定位条50公式与函数数组公式在选取了数组公式之后，就可以对数组公式进行编辑了。步骤如下：选择要编辑的数组；单击“公式”编辑栏或按F2键，可以看到代表数组的大括号消失，然后就可以编辑公式了；对公式进行编辑后，按下Shift+Ctrl+Enter键，完成数组公式的编辑。公式与函数数组公式在选取了数组公式之后，就可以对数组公式51公式与函数几个重要函数Text函数TEXT是一个常用的工作表函数，能将一数值转换为按指定数字格式来表示的文本。语法：TEXT(value,format_text)Value若为数值、计算结果为数值的公式，或对数值单元格的引用。Format_text所要选用的文本型数字格式。即在“单元格格式”对话框中打开“数字”选项卡的“分类”列表框中显示的格式。Format_text不能包含星号(*)，也不能是常规型。公式与函数几个重要函数Text函数52公式与函数几个重要函数Text函数示例公式与函数几个重要函数Text函数示例53公式与函数几个重要函数SumIf函数语法语法SUMIF(range,criteria,sum_range)Range为用于条件判断的单元格区域。Criteria为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为32、32、32或apples。Sum_range是需要求和的实际单元格。公式与函数几个重要函数SumIf函数54公式与函数几个重要函数说明说明只有在区域中相应的单元格符合条件的情况下，sum_range中的单元格才求和。如果忽略了sum_range，则对区域中的单元格求和。MicrosoftExcel还提供了其他一些函数，它们可根据条件来分析数据。例如，如果要计算单元格区域内某个文本字符串或数字出现的次数，则可使用COUNTIF函数。如果要让公式根据某一条件返回两个数值中的某一值（例如，根据指定销售额返回销售红利），则可使用IF函数。公式与函数几个重要函数说明55公式与函数几个重要函数SumIf函数示例下表是某同学的成绩单，该生所在学校实行学分制，只有当某门课的成绩不低于60分，方能取得该门课的学分。现通过计算该生已获得的学分来说明SumIf函数的用法。公式与函数几个重要函数SumIf函数示例56公式与函数几个重要函数SumIf函数公式与函数几个重要函数SumIf函数57公式与函数几个重要函数SumIf函数 示例公式与函数几个重要函数SumIf函数示例58公式与函数几个重要函数中文Excel2000还提供有其他可根据条件来分析数据的工作表函数：用于计算单元格区域内某个文本字符串或数字出现次数的COUNTIF；用于要让公式根据某一条件返回两个数值中的某一个值（例如，根据指定销售额返回销售红利）的IF。公式与函数几个重要函数中文Excel2000还提供有其59公式与函数注意公式中的数值转换对于初学者来说，在公式应用中要准确的输入字符是很困难的。不过，尽管每个运算符都需要特定类型的数值与之对应，但如果输入数值的类型与所需的不同，中文Excel2000还是有可能做些力所能及的转换，并完成计算，虽说不是所有的转换都能成功，但下表中所列的常见输入错误都能被很好地处理。公式与函数注意公式中的数值转换对于初学者来说，在公式应用60公式与函数注意公式中的数值转换 公式与函数注意公式中的数值转换61