EXCEL在会计中的应用第03章.ppt

1、【本章知识点】,公式的规则与编辑 公式本身及结果的查看 常见公式错误信息及原因 名称的含义、作用与用法 建立名称的几种不同方式 单元格地址引用的三种方式 多表单元格之间的多维引用 数组公式的建立规则与应用,本章目录,3.1 公式及其应用 3.2 单元格的引用 3.3名称及其应用 3.4数组公式及其应用 小结 习题,3.1 公式及其应用,3.1.1 公式的规则 所有的Excel公式都具有相同的基本结构：一个等号（=）后面跟随一个或多个运算码，运算码可以是值、单元格引用、单元格区域、区域名称或者函数名，其间以一个或多个运算符连接。这些运算符包括算术运算符、逻辑运算符或者字符连接符（&）等。 Exc

2、el在一个公式中允许使用的最大字符数为1024个。公式中运算符与运算码之间可以加入空格，以便分隔公式中的各个部分，从而提高公式的可读性。 说明：在写长公式时，可以将公式多行显示，操作时只要单击希望断行的位置，然后按下【Alt+Enter】组合键即可。,3.1.2 公式的编辑,1公式的输入 （1）选中要输入公式的单元格； （2）输入“=”作为准备输入公式的开始； （3）输入组成该公式的所有运算码和运算符； （4）按回车键对输入的公式表示确认。 2公式的修改 （1）按F2键，可以直接编辑修改单元格中的内容； （2）双击该单元格，可以直接编辑修改单元格中的内容； （3）选择需要进行编辑的单元格，然后

3、单击编辑栏，在编辑栏中对公式进行修改。 （4）如果公式的单元格返回一个错误，Excel会在单元格的左上角显示一个小方块。激活单元格，可以看到一个智能标签。单击该智能标签，可选择一个选项来更正错误。 当编辑结束后按【Enter】键即可完成操作。如果要取消编辑操作，可以按Esc键。 3公式的移动和复制 公式移动和复制的操作方法与单元格移动和复制类似，,3.1.3 公式的运算符号与优先顺序,Excel公式根据其运算符的类型可以分为四组：数学公式、比较公式、字符公式和引用公式。每组公式都有其自己的运算符和使用方式 。 数学公式 比较公式 字符公式 引用公式,3.1.4 公式结果的查看,1.查看公式的结

4、果 Excel中，公式输入完成后，按下【Enter】，公式的结果会直接在单元格中显示，需要查看公式时，选中单元格，在编辑框中可以看到公式。 2.公式和结果的切换 通过单击“工具”/“选项”命令，在出现的“选项”对跨框中选择“视图”标签，选中“公式”复选框可以来显示所有的公式本身，而不是在单元格显示公式结果。 3.以结果替换公式 在Excel中，公式和结果是两种不同的类型，当希望把公式得到的结果固定下来时，可以把公式转换成它的值，这样可以提高大型工作表重新计算的速度，并可以释放此工作表使用的一部分内存空间 。,3.1.5 公式返回的错误信息返回值,3.1.6 公式的循环引用问题,在输入公式的过程

5、中，有时候会引起一个循环引用。不管是直接引用或者是间接引用，当一个公式引用自己的单元格里面的数据时往往会发生循环引用。比如，在单元格A6里面输入=Sum（A1:A6）就会创建一个循环引用，因为该公式引用了包含该公式的单元格。 大多数循环引用错误是由简单排版错误或者不正确的范围定义引起的 。某些情况下，循环引用设置的合适，可以相当于一个函数，它等同于VBA编程语言中的Do-Loop语句。 有意图的循环引用把递归或者迭代引入到问题中。每一个循环引用计算的中间“答案”都会作用于后续的计算，最后结果收敛到最终值。,3.2 单元格的引用,3.2.1 对单元格地址的三种引用方式 绝对引用 相对引用 混合引

6、用 （F4切换） 3.2.2 跨表格数据的引用 1.引用同一工作薄其他工作表中的单元格或区域 工作表标签名!单元格地址 2.引用同一工作薄多张工作表的相同单元格或区域 第一个工作表名：最后一个工作表名! 单元格地址 3.不同工作薄之间的数据引用 工作簿名工作表标签名! 单元格地址,3.2.3 单元格引用的综合应用举例,实例1：计算提成 绝对引用与相对引用的适当选取 实例2：产品费用分配 混合引用的巧妙使用 实例3：学生成绩计算 跨工作表/簿之间的单元格引用,3.3名称及其应用,3.3.1 使用名称的好处 1. 使公式含义更容易理解。 2. 提高公式编辑的准确性。 3. 快速定位到特定位置。 4

7、. 名称可以作为变量来使用。 5.可以方便地应用于所有的工作表。 6.可以方便地使用另一个工作薄中的定义名称，或者定义一个引用了其他工作薄中单元格的名称。 7.使用区域名称比单元格地址更容易创建和保持宏。,3.3.2 名称的命名规则,（1）名称的第一个字符必须是字母或下划线，不能使用单元格地址或阿拉伯数字。 （2）名称中的字符可以是字母、数字，不能含有空格，可以使用下划线和句点。 （3）名称的长度不能超过255个字符。 （4）名称中不能使用除下划线和句点以外的其它任何符号。 （5）名称中的字母不区分大小写。例如，如果已经创建了名称Sales，接着又在同一工作薄中创建了名称SALES，则第二个名

8、称将替换第一个。 （6）命名时，不要使用Excel内部名称。,3.3.3 定义单元格区域的名称,Excel中名称的定义有三种基本方法，分别是：利用“插入”“名称”“定义”命令；利用“插入”“名称”“指定”命令；利用名称框；另外还可以对已经定义的名称进行合并。 1. 使用定义命令：可以任意定义名称 .使用指定命令：根据表格的行标题或列标题定义名称 3.使用名称框：定义名称的快捷方式 4.合并名称：将多个已有的名称合并为一个新名称,3.3.4 定义常量名称,在Excel中，除了为单元格或区域命名以外，还可对常量和公式定义名称，以便使用。 1.为常量数值定义名称 2.为公式定义名称 3. 为文本常数

9、定义名称 说明：没有引用范围的名称无法在名称框或“定位”对话框（按【F5】可以进入）中显示出来，因为这些常数不能驻留在任何实质性的位置上。但是它们可以出现在“粘贴名称”对话框中，因为以后需要在公式中使用这些名称。,3.3.5 名称的查看、修改和删除,1.名称清单的查看 定义了很多名称之后，有时可能弄不清楚这些名称所引用的到底是哪些单元格或者单元格区域。为此，可以将工作表中定义的所有名称清单输入到某一工作表上，以备检查这些名称所引用的单元格区域。 2.名称的修改 随着工作的进行，有时会发现需要对单元格的名称进行修改操作。这里对名称的修改操作包括名称的重命名和更改名称指代的区域两种。 3.名称的删

10、除 对于工作表中无用的名称，可以进行删除操作。如果要删除一个名称，只需要在“定义名称”对话框中选择一个名称项目，然后点击“删除”按钮就可以了。,3.3.6 名称的使用,1利用名称快速定位单元格区域 方法一：在名称框中选择某个名称，按回车键，。 方法二：单击【F5】或者【Ctrl+G】键，打开“定位”对话框，选择某个名称，然后单击“确定”按钮。 2在建立公式或函数时使用名称 可以直接在公式或者函数中输入时使用已经定义好的名称。在公式中使用名称时，先输入等号（），再在公式中输入名称；在函数中使用名称时，先输入函数名以及左括号，然后输入名称作为函数的某个参数。 3在现有的公式上套用事后定义的名称 4

11、 .使用名称查询数据 5 .将整行或整列定义名称,3.3.7 名称应用的综合举例,1学生成绩计算 为单个单元格定义名称 2节日倒计时 为常量和公式定义名称的使用 3库存计算 为整列定义名称并为相对单元格定义名称 4公司上半年产品汇总计算 为多张工作表的相同单元格区域定义名称,3.4数组公式及其应用,3.4.1 数组公式的建立 1.数组公式的输入 数组公式与一般公式不同之处在于它被括在大括号（）中，其输入步骤如下： （1）选中一个单元格或者单元格区域。 说明：如果数组公式只是返回一个结果，需要选择保存用来保存结果的那一个单元格；如果数组公式返回多个结果，则需要选中需要保留数组公式计算结果的单元格

12、区域。 （2）按照前面介绍的公式输入规则，输入公式的内容。 （3）公式输完后，按【Ctrl+Shift+Enter】组合键结束操作。,2.使用数组公式的好处,使用数组公式具有以下优越性： （1）数组公式可以节省存储空间。 （2）可以保证在同一个范围内的公式具有同一性。 （3）减少了意外覆盖公式的可能性，因为数组公式中无法修改一个单元格的内容。 （4）可以完全防止一位新手篡改你的公式，因为无法删除数组公式中的一部分。,3.数组常量的输入,在数组公式中，通常都使用单元格区域引用，但是也可以直接键入数值数组，这样输入的数值数组被称为数组常量。 数组常量的中输入内容要遵守一定的规则，具体包括：数组常量

13、中可包含数字、文本、逻辑值（如 TRUE、FALSE 或错误值 #N/A）。例如，1，3，4；TRUE，FALSE，TRUE。 另外，数组常量中的数字可以是整数、小数或科学记数格式，而文本必须包含在半角的双引号之内，如Tuesday。数组常量中不能含有单元格引用，不能含有特殊字符#、）分开。 （3）数值输完后，按【Ctrl+Shift+Enter】组合键结束操作。,3.4.2 数组公式的编辑,数组公式在编辑方面，与一般公式的一大不同之处在于：不能单独对数组公式所涉及的单元格区域中的某一单元格进行编辑、清除或者移动操作。具体来说，包括以下问题： （1）不能修改组成数组公式的任一个别单元格的内容（

14、但可修改整个数组公式）。 （2）不能移动组成数组公式某一部分的单元格（但可以移动整个数组公式）。 （3）不能删除组成数组公式某一部分的单元格（可以删除整个数组公式）。 （4）不能向一个数组范围内插入新的单元格，包括插入新行或新列。,1. 修改数组公式,数组公式输入完毕后，如果又发现错误需要进行修改，则需按以下步骤操作： （1）选中任意一个包含数组公式的单元格； （2）单击编辑栏或按【F2】键，使编辑栏处于活动状态，此时大括号（ ）会从数组公式上去掉； （3）编辑修改数组公式内容； （4）修改完毕后，按【Ctrl+Shift+Enter】组合键结束操作。,1. 修改数组公式,2. 移动数组公式

15、要把数组公式移到另一个位置，需要先选中整个数组公式所包括的范围，然后把整个区域拖放到目标位置，也可通过“剪切”和“粘贴”菜单命令进行。 3. 删除数组公式 删除数组公式的方法是：首先选定存放数组公式的所有单元格，单后按【Delete】键。,3.4.3 二维数组的应用,在实际应用中，往往会涉及到多行或者多列的数据处理，这就是所谓的二维数组。 Excel支持二维数组的各种运算，如加、减、乘、除等。在实际应用中，二维数组的应用是非常广泛的。 合理的应用二维数组运算，能够提高数据处理的能力，特别是在不同工作表之间的数据汇总时非常高效。,3.4.4 数组公式的扩展,在公式中使用数组作为参数时，一般来说所

16、用的数组必须是同维的。但是，如果数组参数或数组区域的维数不匹配，Excel也会自动扩展该参数。如图3-53所示， A1：D9单元格区域为某公司销售产品的原价格，目前因为原材料涨价，公司准备为所有产品涨价，其中涨价幅度存储在F2单元格中（目前为5%）；图中H1：K9就是利用数组公式计算出来的涨价后的价格。,3.4.5 数组公式的应用举例,1.实例1：单个单元格使用数组公式 详见本书教材和相关素材 2. 实例2：用数组公式计算多个数据区域的和 详见本书教材和相关素材 3. 实例3：数组公式的扩展应用 详见本书教材和相关素材 数组的应用非常灵活，尤其是可以作为很多函数的参数，它在简化数据运算，提高数

17、据处理效率方面能发挥很大的作用，这在后续相关章节会有更为详细的应用和说明。,小 结,本章介绍Excel中公式输入、编辑、修改与保密，引用的各种方式以及名称的定义、修改、删除与应用等相关知识。 输入公式之前必须先输入“=”；如果公式中有多个运算符的话，Excel按照预定的运算符优先级顺序进行计算。 公式编辑中，经常会因为各种不同原因，产生各种不同错误，必须掌握常见错误信息的返回值，并根据实际错误原因进行修改。 在Excel中，一般情况下要避免在公式中出现“循环引用”，但是，有时候也故意利用公式中循环引用，实现某些特定功能，注意此时需要把“选项”/“重新计算”/“迭代计算”复选框选中。 Excel提供了三种引用方式：绝对引用、相对引用和混合引用。 单元格的引用也可以跨表格，可以引用同一工作薄中不同工作表的数据，也可以引用不同工作薄中的数据。,小 结 （续）,Excel中可以使用名称来表示单元格、单元格区域、常量或公式，创建名称时必须遵循名称的命名规则。名称一旦定义之后，可以在公式、运算符、图表中使用名称。 Excel可以在公式中使用数组，数组公式建立完之后一定要按【Ctrl+Shift+Enter】组合键确定，使用数组公式一定要注意不能像单个公式那样修改和删