函数与公式的概念.doc

《函数与公式的概念.doc》由会员分享，可在线阅读，更多相关《函数与公式的概念.doc（14页珍藏版）》请在金锄头文库上搜索。

1、函数与公式的概念 什么是函数? Excel函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。以常用的求和函数SUM为例，它的语法是“SUM(number1,number2,.)”。其中“SUM”称为函数名称，一个函数只有唯一的一个名称，它决定了函数的功能和用途。函数名称后紧跟左括号，接着是用逗号分隔的称为参数的内容，最后用一个右括号表示函数结束。 参数是函数中最复杂的组成部分，它规定了函数的运算对象、顺序或结构等。使得用户可以对某个单元格或区域进行处理，如分析存款利息、确定成绩名次、计算三角函数值等。 按照函数的来源，Excel函数可以分为内置函数和扩展函数两大类。前者只要启动了Exc

2、el，用户就可以使用它们;而后者必须通过单击“工具加载宏”菜单命令加载，然后才能像内置函数那样使用。 如果函数要以公式的形式出现，它必须有两个组成部分，一个是函数名称前面的等号，另一个则是函数本身。一、初步了解Excel公式 1. 什么是公式 前面已经介绍过，我们把“公式”列为不同于“数值”和“文本”之外的第三种数据类型。公式的共同特点是以“=”号开头，它可以是简单的数学式，也可以是包含各种Excel函数的式子。 2. 公式由哪些元素组成 输入到单元格中的公式均由等号开头，等号后面由如下五种元素组成： 运算符：例如“+”或者“*”号。 单元格引用：它包括单个的单元格或多个单元格组成的范围，以及

3、命名的单元格区域。这些单元格或范围可以是同一工作表中的，也可以是同一工作薄其他工作表中的，甚至是其他工作薄工作表中的。 数值或文本：前面介绍过的两种数据类型。例如，“100”或“新悦国际教育集团”。 工作表函数：可以是Excel内置的函数，如SUM或MAX，也可以是自定义的函数。 括号：即“(”和“)”。它们用来控制公式中各表达式被处理的优先权。 了解公式的组成是创建公式必备的基础。另外，Excel工作表中的公式最多可以由1024个字符组成，我们日常所创建的公式，超过100个字符的已经非常少，因此这个长度足够我们使用了。 看看公式长什么样子一、在公式中使用引用 我们在往单元格中输入公式回车以后

4、，单元格中会显示公式计算的结果，如果要查看公式，可以选中该单元格，单元格中的公式则会显示在编辑栏中。 我们先来看看如果不使用引用将会有什么弊端。 1. 不使用引用的弊端 在图1所示的工作表中，A列和B列单元格分别存放着“单价”和“数量”数据，显然它们都是“数值”类型的，为了求出“FX82MS”的“金额”数值，可以在D2单元格中输入公式“=250.5*12”，回车后D2单元格显示该公式的计算结果“3006”。 图 1 这个公式中并没使用引用，而是使用的具体数值。下面我们来分析一下不使用引用的两个缺点： 首先，假设“单价”或“数量”的值改变了，我们必须对公式进行修改，这让人觉得这个公式相当死板，毫

5、无灵活性。 其次，假设我们想求出图1中后面的各种商品的“金额”数值，必须重新逐个输入公式，这样不仅费时费力，而且还容易出错。 如果在公式中使用引用则可以很好地克服上述的两个缺点，下面我们仍利用图1的表格进行说明。 Excel 公式位置的引用 一个引用位置代表工作表上的一个或者一组单元格，引用位置告诉Excel在哪些单元格中查找公式中要用的数值。通过使用引用位置，我们可以在一个公式中使用工作表上不同部分的数据，也可以在几个公式中使用同一个单元格中的数值。 我们也可以引用同一个工作簿上其它工作表中的单元格，或者引用其它工作簿，也可以引用其他应用程序中的数据。引用其他工作簿中的单元格称为外部引用。引

6、用其他应用程序中的数据称为远程引用。单元格引用位置基于工作表中的行号和列标。 7.4.1 单元格地址的输入 在公式中输入单元格地址最准确的方法是使用单元格指针。我们虽然可以输入一个完整的公式，但在输入过程中很可能有输入错误或者读错屏幕单元地址，例如，我们很可能将“B23”输入为“B22”。因此，在我们将单元格指针指向正确的单元格时，实际上已经把活动的单元格地址移到公式中的相应位置了，从而也就避免了错误的发生。在利用单元格指针输入单元格地址的时候，最得力的助手就是使用鼠标。 使用鼠标输入的过程如下： （1）选择要输入公式的单元格，在编辑栏的输入框中输入一个等号“=”。 （2）用鼠标指向单元格地址

7、，然后单击选中单元格地址。 （3）输入运算符号，如果输入完毕，按下“Enter”键或者单击编辑栏上的“确认”按钮。如果没有输入完毕，则继续输入公式。 例如，我们要在单元格“B2”中输入公式“=A1+A2+C6”，则可将鼠标指向单元格“B2”，然后键入一个“=”号，接着将鼠标指向“A1”单击，再键入“”号，重复这一过程直到将全部公式输入进去。 7.4.2 相对地址引用 在输入公式的过程中，除非我们特别指明，Excel一般是使用相对地址来引用单元格的位置。所谓相对地址是指：当把一个含有单元格地址的公式拷贝到一个新的位置或者用一个公式填入一个范围时，公式中的单元格地址会随着改变。例如在上一节中，输入

8、的公式实际上代表了如下的含义：将单元格“A1”中的内容放置到“B2”单元格中，然后分别和“A2”、“C6”单元格中的数字相加并把结果放回到“B2”单元格中。使用相对引用就好像告诉一个向我们问路的人：从现在的位置，向前再走三个路口就到了。 例如，我们将上例中的公式“=A1+A2+C6”分别拷贝到单元格“C2”、“D2”、“B3”和“B4”中。图7-5显示了拷贝后的公式，从中看到相对引用的变化。 7.4.3 绝对地址引用 在一般情况下，拷贝单元格地址时，是使用相对地址方式，但在某些情况下，我们不希望单元格地址变动。在这种情况下，就必须使用绝对地址引用。 所谓绝对地址引用，就是指：要把公式拷贝或者填

9、入到新位置，并且使公式中的固定单元格地址保持不变。在Excel中，是通过对单元格地址的“冻结”来达到此目的，也就是在列号和行号前面添加美元符号“”。 下面以图7-6来中的“b2”单元格来说明绝对地址引用。例如，公式“=A1*A3”中的“A1”是不能改变的。我们就必须使其变成绝对地址引用，公式改变为“=$A$1*A3”，当将公式拷贝时就不会被当作相对地址引用了，从图7-6的“C2”单元格可看到发生的变化。 7.4.4 混合地址引用 在某些情况下，我们需要在拷贝公式时只有行保持或者只有列保持不变。在这种情况下，就要使用混合地址引用。所谓混合地址引用是指：在一个单元格地址引用中，既有绝对地址引用，同

10、时也包含有相对单元格地址引用。例如，单元格地址“$A5”就表明保持“列”不发生变化，但“行”会随着新的拷贝位置发生变化；同理，单元格地址“A$5”表明保持“行”不发生变化，但“列”会随着新的拷贝位置发生变化。图7-7是混合地址引用的范例。 7.4.5 三维地址引用 前面我们学习过，Microsoft Excel 2000中文版的所有工作是以工作簿展开的。比如，要对一年的12个月销售情况进行汇总，而这些数据是分布在12张工作表中的，要完成这些销售数据的汇总，就必须要能够读取（引用）在每张表格中的数据，这也就引出了“三维地址引用”这一新概念。 所谓三维地址引用是指：在一本工作簿中从不同的工作表引用

11、单元格。三维引用的一般格式为：工作表名！：单元格地址，工作表名后的“！”是系统自动加上的。例如我在第二张工作表的“B2”单元格输入公式“=Sheet1!:A1+A2”，则表明要引用工作表“Sheet1中的单元格B1和工作表Sheet2中的单元格B2相加，结果放到工作表Sheet2中的B2单元格。 利用三维地址引用，可以一次性将一本工作簿中指定的工作表的特定单元格进行汇总。 函数的参数 函数右边括号中的部分称为参数，假如一个函数可以使用多个参数，那么参数与参数之间使用半角逗号进行分隔。 参数可以是常量(数字和文本)、逻辑值(例如TRUE或FALSE)、数组、错误值(例如#N/A)或单元格引用(例

12、如E1:H1)，甚至可以是另一个或几个函数等。参数的类型和位置必须满足函数语法的要求，否则将返回错误信息。 (1)常量 常量是直接输入到单元格或公式中的数字或文本，或由名称所代表的数字或文本值，例如数字“2890.56”、日期“2003-8-19”和文本“黎明”都是常量。但是公式或由公式计算出的结果都不是常量，因为只要公式的参数发生了变化，它自身或计算出来的结果就会发生变化。 (2)逻辑值 逻辑值是比较特殊的一类参数，它只有TRUE(真)或FALSE(假)两种类型。例如在公式“=IF(A3=0,A2/A3)”中，“A3=0”就是一个可以返回TRUE(真)或FALSE(假)两种结果的参数。当“A

13、3=0”为TRUE(真)时在公式所在单元格中填入“0”，否则在单元格中填入“A2/A3”的计算结果。 (3)数组 数组用于可产生多个结果，或可以对存放在行和列中的一组参数进行计算的公式。Excel中有常量和区域两类数组。前者放在“”(按下Ctrl+Shift+Enter组合键自动生成)内部，而且内部各列的数值要用逗号“,”隔开，各行的数值要用分号“;”隔开。假如你要表示第1行中的56、78、89和第2行中的90、76、80，就应该建立一个2行3列的常量数组“56,78,89;90,76,80。 区域数组是一个矩形的单元格区域，该区域中的单元格共用一个公式。例如公式“=TREND(B1:B3,A

14、1:A3)”作为数组公式使用时，它所引用的矩形单元格区域“B1:B3,A1:A3”就是一个区域数组。 (4)错误值 使用错误值作为参数的主要是信息函数，例如“ERROR.TYPE”函数就是以错误值作为参数。它的语法为“ERROR.TYPE(error_val)”，如果其中的参数是#NUM!，则返回数值“6”。 (5)单元格引用 单元格引用是函数中最常见的参数，引用的目的在于标识工作表单元格或单元格区域，并指明公式或函数所使用的数据的位置，便于它们使用工作表各处的数据，或者在多个函数中使用同一个单元格的数据。还可以引用同一工作簿不同工作表的单元格，甚至引用其他工作簿中的数据。 根据公式所在单元格的位置发生变化时，单元格引用的变化情况，我们可以引用分为相对引用、绝对引用和混合引用三种类型。以存放在F2单元格中的公式“=SUM(A2:E2)”为例，当公式由F2单元格复制到F3单元格以后，公式中的引用也会变化为“=SUM(A3:E3)”。若公式自F列向下继续复制，“行标”每增加1行，公式中的行标也自动加1。 如果上述公式改为“=SUM($A $3:$E $3)”，则无论公式复制到何处，其引用的位置始终是“A3:E3”区域。 混合引用有“绝对列和相对行”，或是“绝对行和相对列”两