搜索
Excel高级教程——公式与函数公式与函数•一、引用•二、公式•三、函数•四、几个重要函数•五、注意公式中的数值转换•六、应用实例•七、小结公式与函数——引用•什么是引用？引用的作用在于标识工作表上的单元格或者单元格区域，并指明公式中所使用的数据的位置。通过引用，可以在公式中使用工作表不同部分的数据，或者在多个公式中使用同一个单元格的数值。还可以引用同一个工作簿不同工作表的单元格、不同工作簿的单元格、甚至其他应用程序的数值。后两种成为外部引用和远程引用，在本课程中不作介绍，有兴趣的同学可以参考相关资料。公式与函数——引用•引用方式A1引用类型。此为默认的引用方式。这种类型用字母标志列（从A到IV，共256列），用数字标志行（从1到65536）。如果要引用单元格，必须输入列字母和行数字；单元格区域的引用则按格式：左上角单元格的引用：右下角单元格的引用。公式与函数——引用•引用方式表：A1引用示例引用内容引用方式引用内容引用方式在列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公式与函数——引用•引用方式R1C1引用。此方式行和列都是数字。在宏中计算行和列的位置时，或者需要显示单元格相对引用时，R1C1引用是很有用的。在此方式中，单元格的位置由“R”加行数字和“C”加列数字来标识。公式与函数——引用•引用方式表：R1C1引用示例引用含义R[-3]C对在同一列上面3行的单元格的相对引用R[2]C[2]对在下面两行右面两列的单元格的相对引用R2C2对在工作表的第2行第2列的单元格的绝对引用（即$B$2）R[-1]对活动单元格整个上面一行单元格区域的相对引用R对当前行的绝对引用公式与函数——公式•公式语法–公式中元素的结构或次序决定了最终的计算结果。MicrosoftExcel中的公式遵循一个特定的语法或次序：最前面是等号（=），后面是参与计算的元素（操作数），这些参与计算的元素又是通过运算符隔开的。–每个操作数可以是不改变的数值（常量数值）、单元格或引用单元格区域、标志、名称、或工作表函数。即常量、单元格地址、名称和函数。–运算符是为了对公式中的元素进行某种运算而规定的符号。Excel有4种运算符：算术运算符、比较运算符、文本运算符和引用运算符。公式与函数——公式•公式语法–算术运算符要完成基本的数学运算，如加法、减法和乘法，连接数字和产生数字结果等，可使用以下算术运算符：算术运算符含义示例+（加号）加3+3–（减号）减3–1–1*（星号）乘3*3/（斜杠）除3/3%（百分号）百分比20%^（脱字符）乘方3^2（与3*3相同）公式与函数——公式•公式语法–比较操作符可以使用下列操作符比较两个值。当用操作符比较两个值时，结果是一个逻辑值，不是TRUE就是FALSE。比较运算符含义示例=（等号）等于A1=B1（大于号）大于A1B1（小于号）小于A1B1=（大于等于号）大于等于A1=B1=（小于等于号）小于等于A1=B1不等于不等于A1B1公式与函数——公式•公式语法–文本串联符使用和号（&）加入或连接一个或更多字符串以产生一大片文本。文本运算符含义示例&(ampersand)将两个文本值连接或串起来产生一个连续的文本值North&wind产生Northwind公式与函数——公式•公式语法–引用操作符引用以下运算符可以将单元格区域合并计算。引用运算符含义示例:(colon)区域运算符，对两个引用之间，包括两个引用在内的所有单元格进行引用B5:B15，（逗号）联合操作符将多个引用合并为一个引用SUM(B5:B15,D5:D15)公式与函数——公式•公式语法公式与函数——公式•公式的输入–用公式进行计算时首先要输入计算公式。输入公式的操作类似于输入文字型数据，但在输入公式时必须以等号（＝）开始，后面才是公式的表达式。在单元格中输入公式的方法如下：•选取要输入公式的单元格；•在编辑栏上的输入框中输入一个等号（＝），再键入公式的表达式；•单击输入按钮,或者按回车键即可得到该单元格的运算结果。公式与函数——日期和时间的处理–在Excel中，日期和时间也被看作数值，因此它们也能用公式进行数值计算。但在对日期和时间进行处理时，有以下几点需要注意：–不能在公式中直接输入日期或时间格式的数据，而要将日期或时间用双引号括起来，否则系统将作出错处理；–计算公式时，Excel先将日期和时间转换成相应的数字，再进行数值计算；–Excel不能识别自定义的时间或日期格式，因此只能采用内置的格式，如“月/日/年”等；–当在Excel中输入日期时，年份为两位数。若输入的年份数字为00~29，Excel将其解释为2000年~2029年；若输入的年份数为30~99，则解释为1930~1999。公式与函数——出错信息•用户如果进行了错误的公式操作，Excel为用户提供了许多出错信息，以提示用户注意解决，下面列出部分提示供用户参考：–“####”：并不表示公式出错，它表示单元格中输入的数值或公式太长，单元格容纳不下，此时用户可以用鼠标来修改列宽；–“＃DIV/0”：表示公式中的除数为0，用户只要将除数变为非零数即可。–“＃N/A”：这是专门为用户准备的，如果某个单元格中没有可用数值，用户应在单元格内填入“＃N/A”，否则Excel就会默认为数值0。–“＃NAME?”：用以提示用户，Excel不能识别输入的文本，只需将输入的文本改为在Excel能识别的形式即可。–“＃REF!”：提示用户，用户所使用的单元格引用内容无效。–“＃VALUE”：当用户使用的操作数的数据类型，与运算符所要求的数据类型不匹配时，就会出现这个错误标识。公式与函数—应用计算公式•实例一:学生成绩表的计算高一（1）班期末考试成绩表学号姓名性别语文数学英语物理化学政治101李文男848256898978108陈开男455670806564105张开良男564356788956103刘一女892365568089104林毅婷女786945207056102梁小群女724578364065公式与函数—应用计算公式公式与函数——应用计算公式公式与函数——应用计算公式公式与函数——应用计算公式公式与函数——应用计算公式实例二：文本连接公式与函数——应用计算公式公式与函数——函数•函数的定义–函数是一些预定义的公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。•例如，SUM函数对单元格或单元格区域进行加法运算，PMT函数在给定的利率、贷款期限和本金数额基础上计算偿还额。公式与函数——函数•参数参数可以是数字、文本、形如TRUE或FALSE的逻辑值、数组、形如#N/A的错误值或单元格引用。给定的参数必须能产生有效的值。参数也可以是常量、公式或其它函数。•结构函数的结构以函数名称开始，后面是左圆括号、以逗号分隔的参数和右圆括号。如果函数以公式的形式出现，请在函数名称前面键入等号（=）。在创建包含函数的公式时，公式选项板将提供相关的帮助。公式与函数——函数公式与函数——函数•注意事项–函数是一种特殊的公式，因此所有的函数与公式一样要以“＝”开头；–函数与公式不同，公式是对工作表的数据进行分析的等式，可以引用同一工作表中的其他单元格、同一工作簿内的不同工作表中的单元格，以及其他工作簿的工作表的单元格；而函数是已经预定义的内置公式，使用特定数值作参数，并按特定的语法顺序进行计算；–函数名与括号之间没有空格，括号要紧跟数字之后，参数之间用逗号隔开，逗号与参数之间也不能插入空格或其他字符；公式与函数——函数•注意事项（续）–用户必须通过合法的参数数值，否则就没办法在单元格中输入函数；–如果函数参数行后面带有省略号（…），表明用户可以在该函数中使用多个这种数据类型的参数，例如对函数AVERAGE(number1,number2,…)，用户可以按以下方式运用：AVERAGE(5,6)，AVERAGE(1,2,3)等；–如果函数名称后面带有一组空格号，则表明该函数不需要任何参数，但在使用该函数是必须带括号，以便Excel能够识别该函数。公式与函数——函数•函数的输入–手工输入•对于单变量函数或者简单函数，可以用手工输入的方法。手工输入法同在单元格中输入公式的方法一样，可以先在编辑栏中输入等号（＝），然后输入函数语句。当然，如果你对函数很熟悉，对于多变量函数和复杂函数也可以用手工输入法。公式与函数——函数•函数的输入–利用函数向导输入•对于较复杂或参数较多的函数，可以使用函数向导来输入。这样可以按照向导的提示一步一步的输入复杂的函数，避免在输入过程中产生误操作。这种方法的步骤如下：•选中要输入函数的单元格；•单击“插入”菜单选择“函数”命令，或单击工具栏上的“粘贴函数”按钮，显示“粘贴函数”对话框（下图）；公式与函数——函数公式与函数——函数•在“函数分类”列表框中选择需要的函数形式；•在“函数名”列表框中选择所需要的函数；•单击“确定”按钮，打开所选函数对话框，在其中的文本框内输入函数的参数；•单击“确定”按钮完成函数的输入。公式与函数——函数公式与函数——函数公式与函数——函数•函数分类Excel提供了300多个功能强大的函数，按其用途可以分为以下几类：–财务函数财务函数可以用来进行一般的财务计算，可以进行确定贷款的支付额、投资的未来值以及股票的价值等财务计算。详见附录《常用财务函数》。DB(cost,salvage,life,period,month)、PMT(rate,nper,pv,fv,type)、……–)公式与函数——函数–数学和三角函数数学和三角函数是进行数据处理时最常用的工具。这些函数能够完成一些简单的数学和三角函数计算。ABS(number)、EXP(number)、COS(number)、SQRT(number)、……公式与函数——函数–统计函数统计函数用于对数据区域进行统计分析，它能计算均值、方差，预测发展趋势。AVERAGE(number1,number2,…)、AVEDEV(number1,number2,…)、MAX(number1,number2,…)、VAR(number1,number2,…)、RSQ(known-ys,known-xs公式与函数——函数•函数嵌套–在某些情况下，您可能需要将某函数作为另一函数的参数使用。例如，下面的公式使用了嵌套的AVERAGE函数并将结果与值50进行了比较。公式与函数——函数•函数嵌套–有效的返回值当嵌套函数作为参数使用时，它返回的数值类型必须与参数使用的数值类型相同。例如，如果参数返回一个TRUE或FALSE值，那么嵌套函数也必须返回一个TRUE或FALSE值。否则，MicrosoftExcel将显示#VALUE!错误值。–嵌套级别限制公式可包含多达七级的嵌套函数。当函数B在函数A中用作参数时，函数B则为第二级函数。例如，AVERAGE函数和SUM函数都是第二级函数，因为它们都是IF函数的参数。在AVERAGE函数中嵌套的函数则为第三级函数，以此类推。公式与函数——函数•自定义函数–自定义函数，也叫用户定义函数，是Excel最富有创意和吸引力的功能之一，下面我们在VisualBasic模块中创建一个函数。–在下面的例子中，我们要给每个人的金额乘一个系数，如果是上班时的工作餐，就打六折；如果是加班时的工作餐，就打五折；如果是休息日来就餐，就打九折。公式与函数——函数•自定义函数–首先打开“工具”菜单，单击“宏”命令中的“VisualBasic编辑器”，进入VisualBasic编辑环境，在“工程－VBAobject”栏中的当前表上单击鼠标右键，选择“插入”-“模块”，在右边栏创建下面的函数rrr，–代码如下：Functionrrr(tatol,rr)Ifrr=上班Thenrrr=0.6*tatolElseIfrr=加班