《应用公式与函数》PPT课件.ppt

第5章应用公式与函数,5.1使用公式5.1.1什么是公式5.1.2了解公式运算符及其优先级5.1.3输入公式5.1.4编辑公式5.1.5使用数组公式5.2单元的格引用5.3使用函数5.3.1什么是函数5.3.2插入函数,5.3.3应用嵌套函数5.4审核公式与函数5.4.1公式与函数运算错误简介5.4.2监视窗口5.4.3追踪引用与从属单元格5.4.4显示与隐藏公式5.4.5错误检查5.5定义与使用名称5.5.1定义单元格名称5.5.2应用名称5.5.3管理名称,5.1使用公式,计算是Excel程序的主要功能之一，Excel的计算功能需要借助公式函数来实现。那么，在Excel中使用的公式与我们日常使用的公式有没有区别呢？该如何向Excel中输入、修改公式呢？这就是本节要介绍的内容。,公式是用数学符号表示几个量之间关系的式子。在Excel中，公式是指可以对工作表进行计算和处理的计算式。一个完成的公式，需要有等号(=)、运算符和参数，该参数可以是常量数值，也可以是单元格引用或单元格区域引用，或是函数，如图5-1所示的公式。其作用是计算A3单元格中的数值乘以3.256与单元格区域B2:C3中所有数据的最小值的和。,5.1.1什么是公式,图5-1Excel公式的结构,常量数值：是指值不能改变的量。运算符：指公式中对各元素进行计算的符号，如+、*、/、%、等。单元格引用：用于表示单元格在工作表中所处位置的坐标集，如A3、D5等。单元格区域引用：是指左上角的单元格与右下角的单元格地址所组成的矩形方阵，如B2:C3单元格区域包含B2、B3、C2、C3四个单元格。,函数：是指使用一些称为参数的特定数值来按特定的顺序或结构执行计算的计算式。在Excel程序中预定义了很多函数，如用于财务的，有计算日与时间的，有数学与三角函数等。,Excel公式中的各种运算符及其运算中的优先级顺序，如表5-1所示。,5.1.2了解公式运算符及其优先级,表5-1Excel公式中的运算符优先等级顺序,图5-2输入等号,图5-3输入数值,Excel中的公式书写顺序与大家日常习惯的公式书写略有不同，需要用户先输入一个等号(=)，然后才输入运算式。步骤1启动Excel软件，然后在Sheet工作表中输入如图5-2所示的数据，接着选中E3单元格，并输入等号(=)。步骤2输入第一个参数，并在数值后输入运算符“+”，如图5-3所示。,5.1.3输入公式,步骤3单击C3单元格，输入第二个参数，如图5-4所示。步骤4输入运算符“+”，单击D3单元格，输入第三个参数，如图5-5所示。步骤2输入第一个参数，并在数值后输入运算符“+”，如图5-3所示。步骤5公式输入完毕后，按下Enter键即可在E3单元格中得出计算结果了。同时，鼠标光标向下移动到E4单元格中，如图5-6所示。,图5-5输入第三个参数,图5-4输入第二个参数,图5-6得出公式结果,在确认输入的公式后，如果发现公式中的参数或运算符有误，用户可以使用下述几种方法进行修改。1鼠标双击法双击含有错误公式的单元格，这时会显示出公式，并进入公式的编辑状态，接着修改公式中的错误，按下Enter键确认修改后的公式即可。2利用编辑栏选中含有错误公式的单元格，这时会在编辑栏中显示公式，将鼠标光标定位到编辑栏中，接着修改公式，再按下Enter键确认。3按功能键F2选中含有错误公式的单元格，按下F2键在单元格中显示出公式，并进入公式的编辑状态，接着修改公式，再按下Enter键确认。,5.1.4编辑公式,图5-8查看数组公式的计算结果,图5-7输入数组公式,数组公式是一个典型的公式，它可以进行多个计算，并返回多个结果。数组公式需要有两组或两组以上的数组参数，并且每组数组参数必须具有相同数目的行和列。使用数组公式的方法如下。步骤1选中E3:E6单元格区域，然后再输入数值公式“=B3:B6+C3:C6+D3:D6”，如图5-7所示。步骤2按下Ctrl+Enter组合键结束输入，得出计算结果，如图5-8所示。,5.1.5使用数组公式,5.2单元格引用,在公式中使用单元格引用共有3种情况，分别是相对引用、绝对引用和混合引用，下面将逐一进行介绍。,图5-9输入公式,图5-10复制公式,1相对引用在公式中的相对单元格引用包含公式和单元格引用的相对位置。如图5-9所示，在H3单元格中输入公式“=B3+C3+D3+E3+F3+G3”，这时表示Excel将在H3单元格左边的第2个单元格、第3个单元格、第4个单元格、第5个单元格、第6个单元格和第7个单元格中查找数据，并把它们相加，然后把相加的值赋予H3。此处，B3、C3、D3、E3、F3、G3就是相对于公式所在的单元格H3数据的相对位置。如果公式所在的单元格的位置改变，引用也随之改变。如果多行或多列地复制公式，引用会自动调整。在默认情况下，新公式会使用相对引用。例如，将H3单元格内的公式复制到H4单元格，将自动从“=B3+C3+D3+E3+F3+G3”调整为“=B4+C4+D4+E4+F4+G4”，如图5-10所示。,2绝对引用绝对引用和相对引用的不同之处在于：复制公式时使用绝对引用，单元格引用不会发生变化。在列号和行号前分别加上符号“$”，例如在H5单元格内输入公式“=$B$5+$C$5+$D$5+$E$5+$F$5+$G$5”，表示对B5、C5、D5、E5、F5、G5单元格的绝对引用，如图5-11所示。,如果公式所在的单元格位置改变，引用不改变。例如，将H5单元格内的公式复制到H6单元格，公式不发生变化，还是“=$B$5+$C$5+$D$5+$E$5+$F$5+$G$5”，如图5-12所示。,图5-11输入含有绝对引用地址的公式,图5-12公式没有变化,3混合引用混合引用包含相对引用和绝对引用，即具有绝对列和相对行，或者绝对行和相对列。绝对引用列采用“$A1”等形式，绝对引用行采用“A$1”等形式。如果公式所在的单元格的位置改变，则相对引用改变，而绝对引用不变。例如在H7单元格中输入公式“=$F3*G$3”，如图5-13所示。如果公式所在单元格的位置改变，则相对引用改变，而绝对引用不变。例如，将H7单元格内的公式复制到H8单元格中，公式中相对引用改变，而绝对引用不变，公式变为“=$F4*G$3”，如图5-14所示。,图5-13输入含有混合引用地址的公式,图5-14复制公式发生变化,5.3使用函数,使用Excel2010的内置函数，用户可以把结构复杂的计算公式简单化。,函数是Excel预先定义好的特殊公式。一般由函数名称、“(”、函数参数和“)”组成，结构形式如下。函数名称(Number1,Number2,.)如果在函数前面加上符号“=”，它就变成公式了。如图5-15所示，是在E3单元格中使用SUM函数计算的结果。,5.3.1什么是函数,图5-15使用SUM函数,在Excel2010中提供了300多个内置函数，为了方便用户使用，下面把这些函数划分为12种类型，如表5-2所示。,表5-2Excel2010的函数类型,图5-16单击“插入函数”按钮,在前面学习了公式的使用方法。那么，如何在Excel2010中插入函数呢？下面介绍三种方法供用户使用。1使用”插入函数”对话框使用“插入函数”对话框插入函数的操作步骤如下。步骤1选中要插入函数的单元格，然后在“公式”选项卡下的“函数库”组中单击“插入函数”按钮，如图5-16所示。步骤2弹出“插入函数”对话框，然后在“或选择类别”下拉列表框中选择“常用函数”选项，接着在“选择函数”列表框中选择SUM选项，单击“确定”按钮，如图5-17所示。,5.3.2插入函数,图5-17“插入函数”对话框,步骤3弹出“函数参数”对话框，然后单击Number1文本框设置参与运算的单元格地址，如图5-18所示。,图5-18“函数参数”对话框,图5-19查看计算结果,步骤4单击“确定”按钮，计算结果如图5-19所示。,2使用功能区中的函数命令使用功能区中的函数命令插入函数的方法如下所示。步骤1在工作表中选中要输入公式的单元格，然后在“公式”选项卡下的“函数库”组中单击“自动求和”按钮，如图5-20所示。步骤2在E4单元格中插入SUM函数，默认参数为B4:D4，如图5-21所示。要想改变该函数的默认参数，拖动鼠标重新选择单元格区域，按下Enter键确认公式即可。3直接输入函数如果用户对要使用的函数非常了解，可以在单元格或编辑栏中直接输入函数。例如选中F5单元格，然后在编辑栏中输入“=SUM(”，这时，就会出现该函数的语法提示，如图5-22所示，根据提示输入参数即可。,图5-20单击“自动求和”按钮,图5-21插入SUM函数,图5-22直接输入函数,嵌套函数是指在某些情况下，用户可能需要将某函数作为另一函数的参数使用，这一函数就是嵌套函数。下面以在IF函数中嵌套函数为例进行介绍，具体操作步骤如下。,5.3.3应用嵌套函数,步骤1在F2单元格中输入“评级”，如图5-23所示。步骤2在F3单元格中输入嵌套公式“=IF(E3=240000,”优“,(IF(AND(E3=200000,E3230000),”良“,”一般“)”，按下Enter键确认，如图5-24所示。,图5-23添加“评级”列,步骤3向下填充公式，对其他店铺进行评级，如图5-25所示。,图5-24使用嵌套公式,图5-25填充公式,5.4审核公式与函数,在Excel中使用公式和函数时，不一定每次返回的都是正确的结果，也有可能出现“#”、“#NUM!”、“#NAME?”、“#N/A”等错误值，这时就需要用户检查公式和函数，找出出错原因，彻底解决问题。,默认情况下，在Excel2010中进行数据计算时，Excel会使用一些规则来帮助用户检查公式中的错误，如果公式错误，会在每个存在错误公式的单元格的左上角上出现一个绿三角，对不同错误，出现不同的提示符。那么这些错误值是如何产生的？如何解决呢？这就是本节要介绍的内容了。1#错误当工作表中出现“#”错误值时，说明工作表中出现了问题。1)列宽不足以显示内容如果是列宽不足以显示单元格中的内容，用户可执行下列一项或多项操作来解决该问题。(1)增加列宽增加列宽最简单的方法是将光标移动到要调整宽度列的列标右框线上，当光标变成十字形状时，按下鼠标左键向右拖动到目标位置，释放鼠标左键即可调整列宽。(2)缩小字体填充缩小字体填充是指在不调整单元格大小的情况下，缩小字体的字号。选中要进行缩,5.4.1公式与函数运算错误简介,图5-26“设置单元格格式”对话框,小字体填充的单元格，并右击该单元格，从弹出的快捷菜单中选择“设置单元格格式”命令。弹出“设置单元格格式”对话框，切换到“对齐”选项卡，在“文本控制”选项组中选中“缩小字体填充”复选框，再单击“确定”按钮，如图5-26所示。,(3)应用不同的数字格式在某些情况下，用户可以通过更改单元格中数字的格式，使其适合现有单元格的宽度。例如，将数据由货币形式转化为常规形式。2)日期和时间为负数如果上述方法不能解决问题，可能是因为输入了带负值的日期或时间，这时，用户可以通过下述方法进行调整。如果使用的是1900年日期系统，必须保证Excel2010中的日期和时间为正值。如果对日期和时间进行减法运算，应确保建立的公式是正确的。,如果公式是正确的，但结果仍然是负值，用户可以通过将该单元格的格式设置为非日期或时间格式来显示该值。2#NUM!错误在Excel2010中，如果公式或函数中使用了无效的数值，则会返回“#NUM!”错误值，检查可能存在的原因，解决该问题。1)在需要数字参数的函数中使用了无法接受的参数如果在需要数字参数的函数中使用了无法接受的参数，例如，要在某个单元格中输入1000元，应该输入数字“1000”，然后设置输入的数据格式为货币形式，而不是在单元格中输入字符“￥1000”。所以在需要数字参数的函数中要确保函数中使用的参数是数字。2)数值无法在Excel中表示如果公式所得出的数字太大或太小，无法在Excel中表示，则会返回“#NUM!”错误值。这时需要更改公式，使其结果介于1*10307到1*10307之间。3)迭代函数无法得到结果如果在工作表中使用了进行迭代的函数(如IRR或RATE)，并且函数无法得到结果，也会返回“#NUM!”错误值，这时，用户可以修改函数参数，使用不同的起始值来解决问题，或者更改Excel迭代公式的次数来解决该问题。方法是打开“Excel选项”对话框，在左侧导航窗格中选择“公式”选项，然后在右侧窗格中的“计算选项”选项组中选中“启用迭代计算”复选框，接着单击“最多迭代次数”文本框右侧的微调按钮，设置重新计算的最大次数，并在“最大误差”文本框中设置两次计算结果之间可以接受的最大误差，最后单击“确定”按钮即可，如图5-27所示。,图5-27设置计算选项,3#NAME?错误如果Excel2010无法识别公式中的文本时，将会出现“#NAME?”错误值，检查可能存在的原因，解决该问题。1)单元格引用错误(1)使用了不存在的单元格名称如果在公式中使用了不存在的单元格名称，则会返回“#NAME?”错误值。(2)单元格名称拼写错误如果公式中引用的单元格名称拼写错误，也会返回“#NAME?”错误值。(3)区域引用中漏掉了冒号(:)在使用公式时需要确保公式中的所有区域引用都使用了冒号；例如，SUM(F3:F8)。(4)引用的另一张工作表未使用单引号如果公式中引用了其他工作簿或工作表的值或单元格，并且这些工作簿或工作表的名称中包含非字母字符或空格，那么必须用单引号()将名称引起来。,2)函数拼写错误在输入公式时，如果输入函数名称不正确或者是不存在的内部函数，则会返回“#NAME?”错误值。例如，将公式“=SUM(F3:F8)”不小心输入为“=SUN(F3:F8)”，即会出现该错误。为了避免这类错误，建议用户使用“插入函数”向导对话框插入需要的函数。3)在公式中输入文本时没有使用双引号在设置函数参数时，如果输入的文本内容没有带双引号，这时，Excel会将其解释为单元格名称，从而出现“#NAME?”错误值。所以，用户要将公式中的文本用双引号括起来。4)没有加载“欧元转换工具”加载宏在使用EUROCONVERT函数时，需要加载“欧元转换工具”宏。用户可以通过下述设置来加载“欧元转换工具”宏。步骤1在Excel窗口中打开“Excel选项”对话框，然后在左侧导航窗格中选择“自定义功能区”选项，接着在“从下列位置选择命令”下拉列表框中选择“不在功能区中的命令”选项，并在下方列表框中选择“加载宏”选项，再单击“添加”按钮，将其添加到快速访问工具栏中，再单击“确定”按钮，如图5-28所示。步骤2返回到Excel工作簿中，单击新添加的“加载宏”按钮，如图5-29所示。步骤3弹出“加载宏”对话框，在“可用加载宏”列表框中选中“欧元工具”复选框，再单击“确定”按钮，如图5-30所示。,图5-28添加“加载宏”按钮,图5-29单击“加载宏”按钮,图5-30“加载宏”对话框,5)打开调用用户定义的函数(UDF)的工作簿用户定义的函数(UDF)是从工作簿中调用的，但是在客户端计算机上不可用。开发人员可以通过多种方法来实现UDF。有关详细信息可参阅VisualBasic帮助文件和MicrosoftOfficeSharePointServer2010软件开发工具包(SDK)。4#REF!错误如果单元格在引用无效时，会返回“#REF!”错误值，用户检查可能存在这些原因。1)公式所引用的单元格被删除如果删除其他公式所引用的单元格，或将已移动的单元格粘贴到其他公式所引用的单元格上，则会出现“#REF!”错误值。这时，用户可以更改公式，或者在删除或粘贴单元格之后立即单击“撤消”按钮来恢复工作表中的单元格。2)链接错误如果使用的对象链接和嵌入(OLE)链接所指向的程序未运行时引起的错误，需要启,动该程序。如果是链接到了不可用的动态数据交换(DDE)主题引起的错误，如“系统”，需要确保使用的是正确的DDE主题。5#N/A错误在Excel2010中，如果数值对函数或公式不可用时，会出现“#N/A”错误值，检查可能存在的原因，解决该问题。1)遗漏数据当在公式中遗漏数据时，Excel2010程序会自动使用#N/A或NA()代替遗漏的数据，从而返回“#N/A”错误值，这时用新数据取代“#N/A”即可。2)函数参数设置不正确(1)lookup_value参数如果对HLOOKUP、LOOKUP、MATCH或VLOOKUP等函数的lookup_value参数赋予了不正确的值，也会返回“#N/A”错误值。需要确保lookup_value参数值的类型正确，例如，采用值或单元格引用，而不是区域引用。(2)range_lookup参数默认情况下，在表中查找信息的函数必须按升序排序。但是VLOOKUP和HLOOKUP工作表函数中包含一个range_lookup参数，因此允许函数在未进行排序的表中查找完全匹配的值。要查找完全匹配的值，用户必须把range_lookup参数设置为False，否则将返回“#N/A”错误值。(3)match_type参数MATCH函数包含一个match_type参数，用于指定列表查找匹配结果时必须遵循的排序次序。如果要查找完全匹配的结果，请将match_type参数设置为0。,(4)数组公式中使用的参数的行数或列数与包含数组公式的区域的行数或列数不一致如果已在多个单元格中输入数组公式，需要确保公式所引用的区域具有相同的行数和列数，或者将数组公式输入到更少的单元格中。如果在高为15行的区域(C1:C15)中输入了数组公式，但公式引用的区域(A1:A10)高为10行，则区域C11:C15中将显示“#N/A”错误。要更正此错误，需要在较小的区域如C1:C10)中输入公式，或将公式所引用的区域更改为相同的行数(如A1:A15)。(5)内置或自定义工作表函数中省略了一个或多个必需参数使用函数时，最好输入函数中的所有参数。如果使用的自定义工作表函数不可用，也会返回“#N/A”错误值，因此需要用户确保包含工作表函数的工作簿已经打开且函数工作正常。6#VALUE!错误如果在公式中使用的参数或操作数的类型不正确时，则会返回“#VALUE!”错误值，检查可能存在的原因，解决该问题。1)把数字或逻辑值输入为文本当公式需要数字或逻辑值(例如True或False)时，用户却输入了文本。由于Excel2010无法将文本转换为正确的数据类型。因此，公式将返回“#VALUE!”错误值。所以需要用户确保公式或函数所需的操作数或参数正确无误，并且公式引用的单元格中包含有效的值。2)输入或编辑数组公式时的错误在输入或编辑数组公式时，确保数组常量不是单元格引用、公式或函数，然后选中包含的单元格或单元格区域，按F2键编辑公式，再按下Ctrl+Shift+Enter组合键确认数组公式。,3)为需要单个值的运算符或函数提供区域方法是将区域更改为单个值，或更改区域，使其包含公式所在的同一行或列。4)在某个矩阵工作表函数中使用了无效的矩阵矩阵是指数值或单元格区域的矩形数组，该数组与其他数组或区域相组合以计算出多个和或乘积。Excel软件预先定义了可生成和或乘积的矩阵函数。如果在公式中使用矩阵，需要确保矩阵的维度对于矩阵参数是正确的。7#DIV/0!错误在进行除法计算时，如果数据除以零(0)时，则会返回“#DIV/0!”错误值，检查下列可能的原因，并选择相应的解决方案。1)将除数更改为非零值如果输入公式中包含明显的除以零(0)的计算，如“=F3/0”，将除数更改为非零值即可。2)修改单元格引用如果公式中使用了对空白单元格或存放零的单元格的引用，也会出现“#DIV/0!”错误值，这时用户可以通过下述修改来解决错误值。将单元格引用更改为其他单元格。在单元格中输入一个非零值作为除数。使用IF工作表函数来防止显示错误值。例如，如果生成这种错误的公式是=A5/B3，则改用=IF(B3=0,A5/B3)。两个引号表示空文本字符串。在引用的单元格中输入值“#N/A”作为除数，这样便将公式的结果从“#DIV/0!”更改为“#N/A”，以表示除数不可用。,图5-31单击“监视窗口”按钮,使用“监视窗口”功能，用户可以在不同工作表中监视单元格数据和使用的公式。具体操作方法如下。步骤1选定一个含有公式的单元格，然后在“公式”选项卡下的“公式审核”组中单击“监视窗口”按钮，如图5-31所示。,5.4.2监视窗口,步骤2弹出“监视窗口”对话框，单击“添加监视”按钮，如图5-32所示。步骤3弹出“添加监视点”对话框，单击“添加”按钮即可将步骤1中选择的单元格设置为监视点了，如图5-33所示。,图5-32单击“添加监视”按钮,图5-33“添加监视点”对话框,图5-34单元格被添加到“监视窗口”中了,步骤4选择的单元格被添加到“监视窗口”中了，如图5-34所示。再单击“添加监视”按钮，用户可以继续添加监视点。步骤5如果要删除某个监视点，用户可以在“监视窗口”对话框中选择已经存在的监视点，然后单击“删除监视”按钮即可，如图5-35所示。,图5-35单击“删除监视”按钮,如果用户输入的公式比较复杂，引用的单元格也比较多，这样就增加了公式审查的难度。使用追踪公式功能即可解决上述问题，下面我们一起来研究一下吧。步骤1选择要追踪的公式所在的单元格，在“公式”选项卡下的“公式审核”组中单击“追踪引用单元格”按钮，如图5-36所示。步骤2在工作表中出现了一条由B3单元格指向E3单元格的蓝色箭头，表示E3单元格中的公式是引用B3、C3和D3单元格内的数据计算得出的，如图5-37所示。步骤3在“公式”选项卡下的“公式审核”组中单击“追踪从属单元格”按钮，如图5-38所示。步骤4从E3单元格中发出一条指向引用该单元格的公式所在的单元格，如图5-39所示。,5.4.3追踪引用与从属单元格,图5-36单击“追踪引用单元格”按钮,图5-37追踪引用的单元格,图5-38单击“追踪从属单元格”按钮,图5-39追踪从属单元格,步骤5如果要取消追踪引用单元格，用户可以先选定被追踪的公式单元格，然后在“公式”选项卡下的“公式审核”组中单击“移去箭头”按钮旁边的三角按钮，从弹出的菜单中选择“移去引用单元格追踪箭头”命令即可，如图5-40所示。,图5-40选择“移去引用单元格追踪箭头”命令,在Excel的默认状态下，输入的公式和调用的内部函数除了显示在编辑栏外，是不会在当前单元格显示的。如果想一边输入公式，一边进行公式的检查，这就需要显示公式了，具体操作步骤如下。,5.4.4显示与隐藏公式,图5-41单击“显示公式”按钮,步骤1在“公式”选项卡下的“公式审核”组中单击“显示公式”按钮，如图5-41所示。步骤2此时，工作表中存在公式的单元格都自动将公式显示出来，如图5-42所示。再次单击“显示公式”按钮，用户可以将公式隐藏起来。,如果含有公式的单元格返回错误提示，可以使用错误检查功能查错，具体操作步骤如下所示。步骤1选择含有错误提示的单元格，然后在“公式”选项卡下的“公式审核”组中单击“错误检查”按钮，如图5-43所示。步骤2弹出“错误检查”对话框，单击“显示计算步骤”按钮，如图5-44所示。步骤3弹出“公式求值”对话框，单击“求值”按钮或“步入”按钮，逐步求值，找出出错的具体位置，如图5-45所示。步骤4返回“错误检查”对话框中，单击“关于此错误的帮助”按钮，用户可以打开“Excel帮助”窗口，在此显示了该错误的相关信息及解决方法，如图5-46所示。步骤5在“