EXCEL数据处理 第03章 公式、引用与名称.ppt

第第 3 3 章章公式、引用与名称公式、引用与名称【本章知识点】n公式的规则与编辑公式的规则与编辑n公式本身及结果的查看公式本身及结果的查看n常见公式错误信息及原因常见公式错误信息及原因n名称的含义、作用与用法名称的含义、作用与用法n建立名称的几种不同方式建立名称的几种不同方式n单元格地址引用的三种方式单元格地址引用的三种方式n多表单元格之间的多维引用多表单元格之间的多维引用n数组公式的建立规则与应用数组公式的建立规则与应用EXCEL数据处理电子教案2中国水利水电出版社本章目录3.1 公式及其应用3.2 单元格的引用3.3名称及其应用3.4数组公式及其应用小结习题EXCEL数据处理电子教案3中国水利水电出版社3.1 公式及其应用3.1.1 公式的规则n所有的Excel公式都具有相同的基本结构：一个等号（=）后面跟随一个或多个运算码，运算码可以是值、单元格引用、单元格区域、区域名称或者函数名，其间以一个或多个运算符连接。这些运算符包括算术运算符、逻辑运算符或者字符连接符（&）等。nExcel在一个公式中允许使用的最大字符数为1024个。公式中运算符与运算码之间可以加入空格，以便分隔公式中的各个部分，从而提高公式的可读性。n说明：说明：在写长公式时，可以将公式多行显示，操作时只要单击希望断行的位置，然后按下【Alt+Enter】组合键即可。EXCEL数据处理电子教案4中国水利水电出版社3.1.2 公式的编辑1公式的输入（1）选中要输入公式的单元格；（2）输入“=”作为准备输入公式的开始；（3）输入组成该公式的所有运算码和运算符；（4）按回车键对输入的公式表示确认。2公式的修改（1）按F2键，可以直接编辑修改单元格中的内容；（2）双击该单元格，可以直接编辑修改单元格中的内容；（3）选择需要进行编辑的单元格，然后单击编辑栏，在编辑栏中对公式进行修改。（4）如果公式的单元格返回一个错误，Excel会在单元格的左上角显示一个小方块。激活单元格，可以看到一个智能标签。单击该智能标签，可选择一个选项来更正错误。当编辑结束后按【Enter】键即可完成操作。如果要取消编辑操作，可以按Esc键。3公式的移动和复制公式移动和复制的操作方法与单元格移动和复制类似，EXCEL数据处理电子教案5中国水利水电出版社3.1.3 公式的运算符号与优先顺序nExcel公式根据其运算符的类型可以分为四组：数学公式、比较公式、字符公式和引用公式。每组公式都有其自己的运算符和使用方式。1.数学公式2.比较公式3.字符公式4.引用公式EXCEL数据处理电子教案6中国水利水电出版社3.1.4 公式结果的查看1.查看公式的结果nExcel中，公式输入完成后，按下【Enter】，公式的结果会直接在单元格中显示，需要查看公式时，选中单元格，在编辑框中可以看到公式。2.公式和结果的切换n通过单击“工具”/“选项”命令，在出现的“选项”对跨框中选择“视图”标签，选中“公式”复选框可以来显示所有的公式本身，而不是在单元格显示公式结果。3.以结果替换公式n在Excel中，公式和结果是两种不同的类型，当希望把公式得到的结果固定下来时，可以把公式转换成它的值，这样可以提高大型工作表重新计算的速度，并可以释放此工作表使用的一部分内存空间。EXCEL数据处理电子教案7中国水利水电出版社3.1.5 公式返回的错误信息返回值EXCEL数据处理电子教案8中国水利水电出版社3.1.6 公式的循环引用问题n在输入公式的过程中，有时候会引起一个循环引用。不管是直接引用或者是间接引用，当一个公式引用自己的单元格里面的数据时往往会发生循环引用。比如，在单元格A6里面输入=Sum（A1:A6）就会创建一个循环引用，因为该公式引用了包含该公式的单元格。n大多数循环引用错误是由简单排版错误或者不正确的范围定义引起的。某些情况下，循环引用设置的合适，可以相当于一个函数，它等同于VBA编程语言中的Do-Loop语句。n有意图的循环引用把递归或者迭代引入到问题中。每一个循环引用计算的中间“答案”都会作用于后续的计算，最后结果收敛到最终值。EXCEL数据处理电子教案9中国水利水电出版社3.2 单元格的引用3.2.1 对单元格地址的三种引用方式n绝对引用 相对引用 混合引用 （F4切换）3.2.2 跨表格数据的引用1.引用同一工作薄其他工作表中的单元格或区域n工作表标签名工作表标签名!单元格地址单元格地址2.引用同一工作薄多张工作表的相同单元格或区域n第一个工作表名：最后一个工作表名第一个工作表名：最后一个工作表名!单元格地址单元格地址3.不同工作薄之间的数据引用n 工作簿名工作簿名工作表标签名工作表标签名!单元格地址单元格地址EXCEL数据处理电子教案10中国水利水电出版社3.2.3 单元格引用的综合应用举例实例1：计算提成 绝对引用与相对引用的适当选取实例2：产品费用分配 混合引用的巧妙使用实例3：学生成绩计算 跨工作表/簿之间的单元格引用EXCEL数据处理电子教案11中国水利水电出版社3.3名称及其应用3.3.1 3.3.1 使用名称的好处使用名称的好处使用名称的好处使用名称的好处1.使公式含义更容易理解。2.提高公式编辑的准确性。3.快速定位到特定位置。4.名称可以作为变量来使用。5.可以方便地应用于所有的工作表。6.可以方便地使用另一个工作薄中的定义名称，或者定义一个引用了其他工作薄中单元格的名称。7.使用区域名称比单元格地址更容易创建和保持宏。EXCEL数据处理电子教案12中国水利水电出版社3.3.2 名称的命名规则（1）名称的第一个字符必须是字母或下划线，不能使用单元格地址或阿拉伯数字。（2）名称中的字符可以是字母、数字，不能含有空格，可以使用下划线和句点。（3）名称的长度不能超过255个字符。（4）名称中不能使用除下划线和句点以外的其它任何符号。（5）名称中的字母不区分大小写。例如，如果已经创建了名称Sales，接着又在同一工作薄中创建了名称SALES，则第二个名称将替换第一个。（6）命名时，不要使用Excel内部名称。EXCEL数据处理电子教案13中国水利水电出版社3.3.3 定义单元格区域的名称nExcel中名称的定义有三种基本方法，分别是：利用“插入”“名称”“定义”命令；利用“插入”“名称”“指定”命令；利用名称框；另外还可以对已经定义的名称进行合并。1.使用定义命令：可以任意定义名称.使用指定命令：根据表格的行标题或列标题定义名称 3.使用名称框：定义名称的快捷方式 4.合并名称：将多个已有的名称合并为一个新名称EXCEL数据处理电子教案14中国水利水电出版社3.3.4 定义常量名称在Excel中，除了为单元格或区域命名以外，还可对常量和公式定义名称，以便使用。1.为常量数值定义名称2.为公式定义名称3.为文本常数定义名称说明：说明：没有引用范围的名称无法在名称框或“定位”对话框（按【F5】可以进入）中显示出来，因为这些常数不能驻留在任何实质性的位置上。但是它们可以出现在“粘贴名称”对话框中，因为以后需要在公式中使用这些名称。EXCEL数据处理电子教案15中国水利水电出版社3.3.5 名称的查看、修改和删除1.名称清单的查看n定义了很多名称之后，有时可能弄不清楚这些名称所引用的到底是哪些单元格或者单元格区域。为此，可以将工作表中定义的所有名称清单输入到某一工作表上，以备检查这些名称所引用的单元格区域。2.名称的修改n随着工作的进行，有时会发现需要对单元格的名称进行修改操作。这里对名称的修改操作包括名称的重命名和更改名称指代的区域两种。3.名称的删除n对于工作表中无用的名称，可以进行删除操作。如果要删除一个名称，只需要在“定义名称”对话框中选择一个名称项目，然后点击“删除”按钮就可以了。EXCEL数据处理电子教案16中国水利水电出版社3.3.6 名称的使用1 1利用名称快速定位单元格区域利用名称快速定位单元格区域利用名称快速定位单元格区域利用名称快速定位单元格区域n方法一：在名称框中选择某个名称，按回车键，。n方法二：单击【F5】或者【Ctrl+G】键，打开“定位”对话框，选择某个名称，然后单击“确定”按钮。2 2在建立公式或函数时使用名称在建立公式或函数时使用名称在建立公式或函数时使用名称在建立公式或函数时使用名称n可以直接在公式或者函数中输入时使用已经定义好的名称。在公式中使用名称时，先输入等号（），再在公式中输入名称；在函数中使用名称时，先输入函数名以及左括号，然后输入名称作为函数的某个参数。3 3在现有的公式上套用事后定义的名称在现有的公式上套用事后定义的名称在现有的公式上套用事后定义的名称在现有的公式上套用事后定义的名称 4.4.使用名称查询数据使用名称查询数据使用名称查询数据使用名称查询数据 5.5.将整行或整列定义名称将整行或整列定义名称将整行或整列定义名称将整行或整列定义名称EXCEL数据处理电子教案17中国水利水电出版社3.3.7 名称应用的综合举例1学生成绩计算为单个单元格定义名称2节日倒计时为常量和公式定义名称的使用3库存计算为整列定义名称并为相对单元格定义名称4公司上半年产品汇总计算为多张工作表的相同单元格区域定义名称EXCEL数据处理电子教案18中国水利水电出版社3.4数组公式及其应用3.4.1 数组公式的建立1.数组公式的输入数组公式与一般公式不同之处在于它被括在大括号（）中，其输入步骤如下：（1）选中一个单元格或者单元格区域。说明：说明：如果数组公式只是返回一个结果，需要选择保存用来保存结果的那一个单元格；如果数组公式返回多个结果，则需要选中需要保留数组公式计算结果的单元格区域。（2）按照前面介绍的公式输入规则，输入公式的内容。（3）公式输完后，按【Ctrl+Shift+Enter】组合键结束操作。EXCEL数据处理电子教案19中国水利水电出版社2.使用数组公式的好处使用数组公式具有以下优越性：（1）数组公式可以节省存储空间。（2）可以保证在同一个范围内的公式具有同一性。（3）减少了意外覆盖公式的可能性，因为数组公式中无法修改一个单元格的内容。（4）可以完全防止一位新手篡改你的公式，因为无法删除数组公式中的一部分。EXCEL数据处理电子教案20中国水利水电出版社3.数组常量的输入n在数组公式中，通常都使用单元格区域引用，但是也可以直接键入数值数组，这样输入的数值数组被称为数组常量。n数组常量的中输入内容要遵守一定的规则，具体包括：数组常量中可包含数字、文本、逻辑值（如 TRUE、FALSE 或错误值#N/A）。例如，1，3，4；TRUE，FALSE，TRUE。n另外，数组常量中的数字可以是整数、小数或科学记数格式，而文本必须包含在半角的双引号之内，如Tuesday。数组常量中不能含有单元格引用，不能含有特殊字符#、&、*、括号或百分号%等，不能含有长度不等的行或列。n数组公式可以按与非数组公式相同的方式使用常量，但是必须按特定格式输入数组常量，数组常量的输入步骤如下：（1）选中需要输入数组常量的单元格区域。（2）在公式中输入数值，并用大括号（）把这些数值括起来。注意：注意：输入数值时，不同列的数值用逗号（,）分开，不同行数值用分号（;）分开。（3）数值输完后，按【Ctrl+Shift+Enter】组合键结束操作。EXCEL数据处理电子教案21中国水利水电出版社3.4.2 数组公式的编辑数组公式在编辑方面，与一般公式的一大不同之处在于：不能单独对数组公式所涉及的单元格区域中的某一单元格进行编辑、清除或者移动操作。具体来说，包括以下问题：（1）不能修改组成数组公式的任一个别单元格的内容（但可修改整个数组公式）。（2）不能移动组成数组公式某一部分的单元格（但可以移动整个数组公式）。（3）不能删除组成数组公式某一部分的单元格（可以删除整个数组公式）。（4）不能向一个数组范围内插入新的单元格，包括插入新行或新列。EXCEL数据处理电子教案22中国水利水电出版社1.修改数组公式数组公式输入完毕后，如果又发现错误需要进行修改，则需按以下步骤操作：（1）选中任意一个包含数组公式的单元格；（2）单击编辑栏或按【F2】键，使编辑栏处于活动状态，此时大括号（）会从数组公式上去掉；（3）编辑修改数组公式内容；（4）修改完毕后，按【Ctrl+Shift+Enter】组合键结束操作。EXCEL数据处理电子教案23中国水利水电出版社2.移动数组公式要把数组公式移到另一个位置，需要先选中整个数组公式所包括的范围，然后把整个区域拖放到目标位置，也可通过“剪切”和“粘贴”菜单命令进行。3.删除数组公式删除数组公式的方法是：首先选定存放数组公式的所有单元格，单后按【Delete】键。EXCEL数据处理电子教案24中国水利水电出版社3.4.3 二维数组的应用n在实际应用中，往往会涉及到多行或者多列的数据处理，这就是所谓的二维数组。nExcel支持二维数组的各种运算，如加、减、乘、除等。在实际应用中，二维数组的应用是非常广泛的。n合理的应用二维数组运算，能够提高数据处理的能力，特别是在不同工作表之间的数据汇总时非常高效。EXCEL数据处理电子教案25中国水利水电出版社3.4.4 数组公式的扩展n在公式中使用数组作为参数时，一般来说所用的数组必须是同维的。但是，如果数组参数或数组区域的维数不匹配，Excel也会自动扩展该参数。如图3-53所示，A1：D9单元格区域为某公司销售产品的原价格，目前因为原材料涨价，公司准备为所有产品涨价，其中涨价幅度存储在F2单元格中（目前为5%）；图中H1：K9就是利用数组公式计算出来的涨价后的价格。EXCEL数据处理电子教案26中国水利水电出版社3.4.5 数组公式的应用举例1.实例1：单个单元格使用数组公式n详见本书教材和相关素材2.实例2：用数组公式计算多个数据区域的和n详见本书教材和相关素材3.实例3：数组公式的扩展应用n详见本书教材和相关素材n数组的应用非常灵活，尤其是可以作为很多函数的参数，它在简化数据运算，提高数据处理效率方面能发挥很大的作用，这在后续相关章节会有更为详细的应用和说明。EXCEL数据处理电子教案27中国水利水电出版社小小 结结n本章介绍Excel中公式输入、编辑、修改与保密，引用的各种方式以及名称的定义、修改、删除与应用等相关知识。n输入公式之前必须先输入“=”；如果公式中有多个运算符的话，Excel按照预定的运算符优先级顺序进行计算。n公式编辑中，经常会因为各种不同原因，产生各种不同错误，必须掌握常见错误信息的返回值，并根据实际错误原因进行修改。n在Excel中，一般情况下要避免在公式中出现“循环引用”，但是，有时候也故意利用公式中循环引用，实现某些特定功能，注意此时需要把“选项”/“重新计算”/“迭代计算”复选框选中。nExcel提供了三种引用方式：绝对引用、相对引用和混合引用。n单元格的引用也可以跨表格，可以引用同一工作薄中不同工作表的数据，也可以引用不同工作薄中的数据。EXCEL数据处理电子教案28中国水利水电出版社小小 结结 （续）（续）nExcel中可以使用名称来表示单元格、单元格区域、常量或公式，创建名称时必须遵循名称的命名规则。名称一旦定义之后，可以在公式、运算符、图表中使用名称。nExcel可以在公式中使用数组，数组公式建立完之后一定要按【Ctrl+Shift+Enter】组合键确定，使用数组公式一定要注意不能像单个公式那样修改和删除。在实际应用中，合理的应用二维数组运算，可以大大提高数据运算能力。n总之，通过本章学习，读者应能正确地进行引用，合理编辑公式，熟练建立名称。n熟练使用这些操作，可以大大提高Excel数据处理效率。EXCEL数据处理电子教案29中国水利水电出版社习习 题题1Excel中在进行公式输入时，需要注意哪些问题？。2什么是公式中的单元格循环引用？如果想使用该功能，需要注意什么设置？3Excel中对单元格地址的引用有哪些方式？如何进行这些方式直接的快速切换？4名称的定义主要有那几种方法？它们主要使用场合有什么不同？5建立数组公式时要注意什么事项？如何删除数组公式？6上机自行将本章的所有实例操作一遍。712 上机练习习题 -具体要求详见教材和本书素材EXCEL数据处理电子教案30中国水利水电出版社