EXCEL教程——公式与函数教程(1)

1、Excel高级教程,公式与函数,公式与函数,一、引用 二、公式 三、函数 四、几个重要函数 五、注意公式中的数值转换 六、应用实例 七、小结,公式与函数引用,什么是引用？ 引用的作用在于标识工作表上的单元格或者单元格区域，并指明公式中所使用的数据的位置。通过引用，可以在公式中使用工作表不同部分的数据，或者在多个公式中使用同一个单元格的数值。还可以引用同一个工作簿不同工作表的单元格、不同工作簿的单元格、甚至其他应用程序的数值。后两种成为外部引用和远程引用，在本课程中不作介绍，有兴趣的同学可以参考相关资料。,公式与函数引用,引用方式 A 1引用类型。此为默认的引用方式。这种类型用字母标志列（从A到

2、IV，共256列），用数字标志行（从1到65536）。如果要引用单元格，必须输入列字母和行数字；单元格区域的引用则按格式：左上角单元格的引用：右下角单元格的引用。,公式与函数引用,引用方式 表：A 1引用示例,公式与函数引用,引用方式 R1C1引用。此方式行和列都是数字。在宏中计算行和列的位置时，或者需要显示单元格相对引用时，R1C1引用是很有用的。在此方式中，单元格的位置由“R”加行数字和“C”加列数字来标识。,公式与函数引用,引用方式 表：R1C1引用示例,公式与函数公式,公式语法 公式中元素的结构或次序决定了最终的计算结果。Microsoft Excel 中的公式遵循一个特定的语法或次序

3、：最前面是等号（=），后面是参与计算的元素（操作数），这些参与计算的元素又是通过运算符隔开的。 每个操作数可以是不改变的数值（常量数值）、单元格或引用单元格区域、标志、名称、或工作表函数。即常量、单元格地址、名称和函数 。 运算符是为了对公式中的元素进行某种运算而规定的符号。Excel有4种运算符：算术运算符、比较运算符、文本运算符和引用运算符。,公式与函数公式,公式语法 算术运算符 要完成基本的数学运算，如加法、减法和乘法，连接数字和产生数字结果等，可使用以下算术运算符：,公式与函数公式,公式语法 比较操作符 可以使用下列操作符比较两个值。当用操作符比较两个值时，结果是一个逻辑值，不是 TR

4、UE 就是 FALSE。,公式与函数公式,公式语法 文本串联符 使用和号（&）加入或连接一个或更多字符串以产生一大片文本。,公式与函数公式,公式语法 引用操作符 引用以下运算符可以将单元格区域合并计算。,公式与函数公式,公式语法,公式与函数公式,公式的输入 用公式进行计算时首先要输入计算公式。输入公式的操作类似于输入文字型数据，但在输入公式时必须以等号（）开始，后面才是公式的表达式。在单元格中输入公式的方法如下： 选取要输入公式的单元格； 在编辑栏上的输入框中输入一个等号（），再键入公式的表达式； 单击输入按钮 ,或者按回车键即可得到该单元格的运算结果。,公式与函数日期和时间的处理,在Exce

5、l中，日期和时间也被看作数值，因此它们也能用公式进行数值计算。但在对日期和时间进行处理时，有以下几点需要注意： 不能在公式中直接输入日期或时间格式的数据，而要将日期或时间用双引号括起来，否则系统将作出错处理； 计算公式时，Excel先将日期和时间转换成相应的数字，再进行数值计算； Excel不能识别自定义的时间或日期格式，因此只能采用内置的格式，如“月/日/年”等； 当在Excel中输入日期时，年份为两位数。若输入的年份数字为0029，Excel将其解释为2000年2029年；若输入的年份数为3099，则解释为19301999。,公式与函数出错信息,用户如果进行了错误的公式操作，Excel为用

6、户提供了许多出错信息，以提示用户注意解决，下面列出部分提示供用户参考： “# # # #”：并不表示公式出错，它表示单元格中输入的数值或公式太长，单元格容纳不下，此时用户可以用鼠标来修改列宽； “DIV/0”：表示公式中的除数为0，用户只要将除数变为非零数即可。 “N/A”：这是专门为用户准备的，如果某个单元格中没有可用数值，用户应在单元格内填入“N/A”，否则Excel就会默认为数值0。 “NAME?”：用以提示用户，Excel不能识别输入的文本，只需将输入的文本改为在Excel能识别的形式即可。 “REF!”：提示用户，用户所使用的单元格引用内容无效。 “VALUE”：当用户使用的操作数的

7、数据类型，与运算符所要求的数据类型不匹配时，就会出现这个错误标识。,公式与函数应用计算公式,实例一:学生成绩表的计算,公式与函数应用计算公式,公式与函数应用计算公式,公式与函数应用计算公式,公式与函数应用计算公式,公式与函数应用计算公式,实例二：文本连接,公式与函数应用计算公式,公式与函数函数,函数的定义 函数是一些预定义的公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。 例如，SUM 函数对单元格或单元格区域进行加法运算，PMT 函数在给定的利率、贷款期限和本金数额基础上计算偿还额。,公式与函数函数,参数 参数可以是数字、文本、形如 TRUE 或 FALSE 的逻辑值、数组、

8、形如 #N/A 的错误值或单元格引用。给定的参数必须能产生有效的值。参数也可以是常量、公式或其它函数。 结构 函数的结构以函数名称开始，后面是左圆括号、以逗号分隔的参数和右圆括号。如果函数以公式的形式出现，请在函数名称前面键入等号（=）。在创建包含函数的公式时，公式选项板将提供相关的帮助。,公式与函数函数,公式与函数函数,注意事项 函数是一种特殊的公式，因此所有的函数与公式一样要以“”开头； 函数与公式不同，公式是对工作表的数据进行分析的等式，可以引用同一工作表中的其他单元格、同一工作簿内的不同工作表中的单元格，以及其他工作簿的工作表的单元格；而函数是已经预定义的内置公式，使用特定数值作参数，

9、并按特定的语法顺序进行计算； 函数名与括号之间没有空格，括号要紧跟数字之后，参数之间用逗号隔开，逗号与参数之间也不能插入空格或其他字符；,公式与函数函数,注意事项（续） 用户必须通过合法的参数数值，否则就没办法在单元格中输入函数； 如果函数参数行后面带有省略号（），表明用户可以在该函数中使用多个这种数据类型的参数，例如对函数AVERAGE(number1,number2,)，用户可以按以下方式运用：AVERAGE(5,6) ，AVERAGE(1, 2,3)等； 如果函数名称后面带有一组空格号，则表明该函数不需要任何参数，但在使用该函数是必须带括号，以便Excel能够识别该函数。,公式与函数函数

10、,函数的输入 手工输入 对于单变量函数或者简单函数，可以用手工输入的方法。手工输入法同在单元格中输入公式的方法一样，可以先在编辑栏中输入等号（），然后输入函数语句。当然，如果你对函数很熟悉，对于多变量函数和复杂函数也可以用手工输入法。,公式与函数函数,函数的输入 利用函数向导输入 对于较复杂或参数较多的函数，可以使用函数向导来输入。这样可以按照向导的提示一步一步的输入复杂的函数，避免在输入过程中产生误操作。这种方法的步骤如下： 选中要输入函数的单元格； 单击“插入”菜单选择“函数”命令，或单击工具栏上的“粘贴函数”按钮 ，显示“粘贴函数”对话框（下图）；,公式与函数函数,公式与函数函数,在“函

11、数分类”列表框中选择需要的函数形式； 在“函数名”列表框中选择所需要的函数； 单击“确定”按钮，打开所选函数对话框，在其中的文本框内输入函数的参数； 单击“确定”按钮完成函数的输入。,公式与函数函数,公式与函数函数,公式与函数函数,函数分类 Excel提供了300多个功能强大的函数，按其用途可以分为以下几类： 财务函数 财务函数可以用来进行一般的财务计算，可以进行确定贷款的支付额、投资的未来值以及股票的价值等财务计算。详见附录常用财务函数。 DB(cost,salvage,life,period,month)、PMT(rate,nper,pv,fv,type)、 ),公式与函数函数,数学和三角

12、函数 数学和三角函数是进行数据处理时最常用的工具。这些函数能够完成一些简单的数学和三角函数计算。 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,公式与函数函数,函数嵌套 在某些情况下，您可能需要将某函数作为另一函数

13、的参数使用。例如，下面的公式使用了嵌套的 AVERAGE 函数并将结果与值 50 进行了比较。,公式与函数函数,函数嵌套 有效的返回值 当嵌套函数作为参数使用时，它返回的数值类型必须与参数使用的数值类型相同。例如，如果参数返回一个 TRUE 或 FALSE 值，那么嵌套函数也必须返回一个 TRUE 或 FALSE 值。否则，Microsoft Excel 将显示 #VALUE! 错误值。 嵌套级别限制 公式可包含多达七级的嵌套函数。当函数 B 在函数 A 中用作参数时，函数 B 则为第二级函数。例如，AVERAGE 函数和 SUM 函数都是第二级函数，因为它们都是 IF 函数的参数。在 AVE

14、RAGE 函数中嵌套的函数则为第三级函数，以此类推。,公式与函数函数,自定义函数 自定义函数，也叫用户定义函数，是Excel最富有创意和吸引力的功能之一，下面我们在Visual Basic模块中创建一个函数。 在下面的例子中，我们要给每个人的金额乘一个系数，如果是上班时的工作餐，就打六折；如果是加班时的工作餐，就打五折；如果是休息日来就餐，就打九折。,公式与函数函数,自定义函数 首先打开“工具”菜单，单击“宏”命令中的“Visual Basic编辑器”，进入Visual Basic编辑环境，在“工程VBAobject”栏中的当前表上单击鼠标右键，选择“插入”-“模块”，在右边栏创建下面的函数r

15、rr， 代码如下： Function rrr(tatol, rr) If rr = 上班 Then rrr = 0.6 * tatol ElseIf rr = 加班 Then rrr = 0.5 * tatol ElseIf rr = 休息日 Then rrr = 0.9 * tatol End If End Function,公式与函数函数,自定义函数,公式与函数函数,自定义函数 这时关闭编辑器，只要我们在相应的列中输入rrr(F，B)，那么打完折后的金额就算出来了。,公式与函数数组公式,数组 是单元格的集合或一组要处理的值的集合。用户可以用一个数组公式进行多个输入操作，并计算多个结果，每个

16、结果显示在一个单元格中。数组公式与单值公式的区别是数组公式可以同时计算两个或两个以上的计算结果。 如果不想在工作表的单个单元格里输入每个常量值，则可用数组常量来代替引用。 一些内置函数就是数组公式，因此必须将其作为数组输入才可得到正确结果。,公式与函数数组公式,数组公式的输入 输入数组公式的方法如下： 选择要放入公式计算结果的单元格； 输入公式“D3:D8+E3:E8+F3:F8+G3:G8+H3:H8+I3:I8”，此时不要单击“输入”按钮 ；,公式与函数数组公式,公式与函数数组公式,按下Shift+Ctrl+Enter键，就会看到公式被一对大括号“”括住，同时在已选的单元格显示计算结果。,

17、公式与函数数组公式,数组公式的编辑 数组包含了许多单元格，这些单元格形成一个整体。不能对数组里的某个单元格进行编辑。在编辑数组之前，必须选取整个数组。选取步骤如下： 选取数组中任一单元格； 选择“编辑”菜单中的“定位”命令，显示“定位”对话框；,公式与函数数组公式,公式与函数数组公式,单击“定位条件”按钮，将会出现“定位条件”对话框； 选择“当前数组”选项，按“确定”按钮，回到表格中就可以看到数组已经选中了。,公式与函数数组公式,在选取了数组公式之后，就可以对数组公式进行编辑了。步骤如下： 选择要编辑的数组； 单击“公式”编辑栏或按F2键，可以看到代表数组的大括号消失，然后就可以编辑公式了；

18、对公式进行编辑后，按下Shift+Ctrl+Enter键，完成数组公式的编辑。,公式与函数几个重要函数,Text函数 TEXT是一个常用的工作表函数，能将一数值转换为按指定数字格式来表示的文本。 语法：TEXT(value,format_text) Value 若为数值、计算结果为数值的公式，或对数值单元格的引用。 Format_text 所要选用的文本型数字格式。即在“单元格格式”对话框中打开“数字”选项卡的“分类”列表框中显示的格式。Format_text 不能包含星号(*)，也不能是常规型。,公式与函数几个重要函数,Text函数 示例,公式与函数几个重要函数,SumIf函数 语法 SUMIF(range,criteria,sum_range) Range 为用于条件判断的单元格区域。 Criteria 为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、32、32 或 apples。 Sum_range 是需要求和的实际单元格。,公式与函数几个重要函数,说明 只有在区域中相应的单元格符合条件的情况下，sum_range 中