第3次课-公式基础.ppt

公式基础及名称使用,主讲：刘彬Email:jsj081203,输入与编辑公式,公式：是对工作表中的数值进行计算的计算式，作用是对输入的数据执行数学运算。公式是工作表的重要组成部分，如果没有公式，工作表就只能存放数据，而不能处理数据。公式通常由三部分组成：等号、参数、和运算符。其中参数可以是单元格地址或数值、逻辑值等。例如：=（A3+B3+C3）/4,二、输入与编辑公式,输入公式有两种方法：键入公式:选择单元格，先键入=，再输入公式。例如，=b3*b4-b5如果需要对输入的公式进行编辑，先选择要编辑的单元格，按F2进入编辑方式后，通过移动上下左右光标进行编辑。,运算符号与类别,文本连接运算符(&)加入或连接一个或更多文本字符串以产生一串文本。,逻辑符号的最终结果要么为true要么为false,逻辑判断公式中，常常见到条件相*或相+，也常见到and和or，对比and和*=if(and(A10,A10)*(A10,A18),B1,C1)以上两种情况是完全可以互换的（等价的）。由此，初学函数容易产生这么一种理解：*就是and（而且的意思）+就是or（或者的意思,比较以下数组公式：=sum(if(and(A1:A20,A1:A20)*(A1:A28),A1:A2)前者只有当A1、A2都介于0和8之间时才返回A1:A2的和，否则得到0；后者则分开了，比如A1介于0和8之间，A2不介于0和8之间，那么求得的结果是A1:A2的和，而不是0。,单元格和范围的引用,引用方式：相对引用绝对引用混合引用,Excel中输入文本型数据需用双引号包含起来,相对地址与绝对地址,相对地址：指公式从工作表的一个位置复制、移动、填充后到另一个位置的同时，公式中的单元格地址也自动调整。如：A1绝对地址：指公式从工作表的一个位置复制、移动、填充到另一个位置的同时，公式中的单元格地址不调整。如$A$1,$B$1,相对引用、绝对引用、混合引用可以由F4键切换,举例：访问者名单.xls,处理公式的循环使用,公式自身直接或间接引用自身数值。就会发生循环使用。例如在B3中输入=A1+A2-B3。并非所有的循环都需要更正。例如某公司决定将净利润的3%捐给希望工程，要求计算利润,D2中=A2-B2-C2-E2E2中输入=D20*0.03,该公式造成了循环使用,为了得到正确结果，需要将：工具选项重新计算迭代计算。设为打开。,隐藏公式选中公式或多个公式格式单元格单元格格式保护选中”隐藏”复选项工具保护保护工作表,EXCEL调试公式的工具和方法调试：纠正计算机程序错误的过程。它不仅仅包括公式报错，还包括因使用公式不当而产生错误的结果。公式问题及解决办法公式一般会包括以下6种错误：1、语法错误。如函数名称错误或者括号不匹配。2、逻辑错误。公式不返回错误，但是它所包含的逻辑错误可能会导致返回一个不正确的结果。,3、不正确的引用错误：公式的逻辑值是正确的，但是公式使用了不正确的单元格引用。4、循环引用。公式直接或间接引用了它自己的单元格。5、数组公式输入错误。当输入一个数组公式时，必须使用ctrl+shift+enter作为结束标志，如果没有这么做，就会产生错误。6、不完全计算错误。公式没有完全计算。要保证公式完整计算，可使用ctrl+Alt+F9.,括号不匹配1)公式中，每一个左括号必须有一个右括号相对应，如果不匹配，EXCEL通常会补允许你输入。该规则的一个例外情况是使用一个函数的简单公式，excel会接受这个公式并提供缺少的括号例如:=sum(a1:b52)左右括号数量相同，但匹配不合适。例如：=upper(left(a1,1)&right(lower(a1),len(a1)-1)通常，处于错误位置的括号会引起一个语法错误。提示参数过多或参数过少。,要注意EXCEL自动纠错功能不是每次都是正确的。,如果要计算a1:a4,b1:b4之和的平均值。当输入公式=AVERAGE(SUM(A1:A4,SUM(B1:B4)，考虑它的结果！参考模拟表,#!原因：如果单元格所含的数字、日期或时间比单元格宽，或者单元格的日期时间公式产生了一个负值，就会产生#!错误。解决方法：如果单元格所含的数字、日期或时间比单元格宽,可以通过拖动列表之间的宽度来修改列宽。如果使用的是1900年的日期系统，那么Excel中的日期和时间必须为正值，用较早的日期或者时间值减去较晚的日期或者时间值就会导致#!错误。如果公式正确，也可以将单元格的格式改为非日期和时间型来显示该值。,#VALUE!当使用错误的参数或运算对象类型时，或者当公式自动更正功能不能更正公式时，将产生错误值#VALUE!。原因一：在需要数字或逻辑值时输入了文本，Excel不能将文本转换为正确的数据类型。解决方法：确认公式或函数所需的运算符或参数正确，并且公式引用的单元格中包含有效的数值。,原因二：将单元格引用、公式或函数作为数组常量输入。解决方法：确认数组常量不是单元格引用、公式或函数。原因三：赋予需要单一数值的运算符或函数一个数值区域。解决方法：将数值区域改为单一数值。修改数值区域，使其包含公式所在的数据行或列。,#DIV/O!当公式被零除时，将会产生错误值#DIV/O!。原因一：在公式中，除数使用了指向空单元格或包含零值单元格的单元格引用(在Excel中如果运算对象是空白单元格，Excel将此空值当作零值)。解决方法：修改单元格引用，或者在用作除数的单元格中输入为零的值。原因二：输入的公式中包含明显的除数零，例如：=5/0。解决方法：将零改为非零值。,#NAME?在公式中使用了Excel不能识别的文本时将产生错误值#NAME?。原因一：删除了公式中使用的名称，或者使用了不存在的名称。解决方法：确认使用的名称确实存在。选择菜单插入定义命令，如果所需名称没有被列出，请使用定义命令添加相应的名称。原因二：名称的拼写错误。解决方法：修改拼写错误的名称。,原因三：在公式中输入文本时没有使用双引号。解决方法：Excel将其解释为名称，而不理会用户准备将其用作文本的想法，将公式中的文本括在双引号中。原因五：在区域的引用中缺少冒号。解决方法：确认公式中，使用的所有区域引用都使用冒号。例如：SUM(A2:B34)。,#N/A原因：当在函数或公式中没有可用数值时，将产生错误值#N/A。在使用查找函数HLOOKUPvlookup函数时，若无可用之值，也会发生此类错误。#REF!当单元格引用无效时将产生错误值#REF!。例如，若再A2中输入=A1-1.如果把公式复制到A1中。则出错#REF!,#NUM!当公式或函数中某个数字有问题时将产生。原因一：在需要数字参数的函数中使用了不能接受的参数。解决方法：确认函数中使用的参数类型正确无误。原因二：使用了迭代计算的工作表函数，例如：IRR或RATE，并且函数不能产生有效的结果。解决方法：为工作表函数使用不同的初始值。原因三：由公式产生的数字太大或太小，Excel不能表示。解决方法：修改公式，使其结果在有效数字范围之间。,#NULL!当试图为两个并不相交的区域指定交叉点时将产生错误值#NULL!。原因：使用了不正确的区域运算符或不正确的单元格引用。解决方法：如果要引用两个不相交的区域，请使用联合运算符逗号(，)。公式要对两个区域求和，请确认在引用这两个区域时，使用逗号。如：SUM(A1:A13，D12:D23)。如果没有使用逗号，Excel将试图对同时属于两个区域的单元格求和，但是由于A1:A13和D12:D23并不相交，所以他们没有共同的单元格。,要注意公式的实际数值与显示数值参考模拟数据表,在C2:C4中输入=1/3，在C6中输入=sum(C2:C4),结果显示为1，而不是0.99999,浮点数错误由于计算机没有无限的精确性。EXCEL使用8个字节二进制格式存储数据，它表示15位的精度。有些数字使用8个字节是不能精确表示的，因此只能近似数存储。例如在A1中输入：=(5.1-5.2)+1。结果显示0.9.但如果在单元格格式中显示15位小数，EXCEL计算公式的结果是0.899999999999999。,一般情况下，这类误差不会引起任何问题。然而，当要进行逻辑判断时，就会出现问题例如输入=A1=0.9结果显示为FALSE处理此类错误的一个方案是使用round()函数。=round(a1,1)=0.9下面是关于精度的另外一个例子：输入下面的公式：=(1.333-1.233)-(1.334-1.234)结果显示为：-2.22044604925031E-16(非常接近0的数)如果公式在A1中，以下结果将显示NOTZERO.=IF(a1=0,”ZERO”,”NOTZERO”),处理这类非常接近0的四舍五入错误的一种方式为如下公式：=if(abs(a1)1E-6,”ZERO”,”NOTZERO”)该公式使用小于运算符将数字的绝对值和一个非常小的数值进行比较。结果将返回zero,观察公式通过显示公式，而不是公式的结果,可以让我们对不熟悉的工作簿熟悉起来。首先：可以在窗口菜单中选择”新建窗口”。然后：工具选项视图公式。注意：可以使用ctrl+组合键在公式视图和普通视图之间切换。最后：通过并排窗口完成比较。注意并排窗口只对2003版本有效。,使用excel的公式求值命令工具公式审核公式求值，这个特性是可以看到每一步的执行情况。缺点是执行一次很费时间。另外一种对公式的某一部分求值方法：选择包含公式的单元格按F2进入编辑状态。使用鼠标选择突出公式的某一部分。按F9。,引用其他工作表或工作簿,同一工作簿不同工作表的方法：工作表名称后用感叹号。例如：=sheet2！A1不同工作簿的工作表：把单元格引用加上工作簿名(方括号内)、工作表名和感叹号。具体方式如下：例如：=budget.xlssheet1!A1,若工作簿名称中包含一个或多个空格，则必须给它们(表单名)加上单引号。例如：=学生成绩.xlssheet1!A1若引用工作簿已经关闭，则必须使用引用工作簿的绝对路径=D:Excel学生成绩.xlssheet1!A1,已知某学期学生成绩如下表所示，现要求在另一张工作表中对该部分同学的考试成绩做简单的统计分析。要求找出各科平均分，最高分、及格率。,跨工作表引用公式的输入方法：1、直接人工按正确格式输入。2、在输入过程中，当涉及到跨工作表引用时，可直接切换到该工作表，用鼠标拖动选择。,使用链接恢复被破坏文