EXCEL第2章公式与函数.ppt
39页
第第2章章 Excel公式与函数基础公式与函数基础 2.1 公式公式v在Excel中，公式是对工作表中数据进行计算的等式，以等号（=）开始例如：Ø=5*2+8Ø=2*PI()*A1 §其中，A1单元格的数据是一个圆的半径，该公式可以计算圆的周长 2.1.1 公式的组成公式的组成v公式通常由运算符和参与运算的操作数组成操作数可以是常量、单元格引用、函数等，其间以一个或者多个运算符连接ü常量：直接输入到公式中的数字或者文本，是不用计算的值ü单元格引用：引用某一单元格或单元格区域中的数据，可以是当前工作表的单元格、同一工作簿中其他工作表中的单元格、其他工作簿中工作表中的单元格ü工作表函数：包括函数及它们的参数，例如PI() 函数、SUM()或AVERAGE()函数等ü运算符：是连接公式中的基本元素并完成特定计算的符号，例如“+”、“/”等不同的运算符完成不同的运算 2.1.2 公式的输入和编辑公式的输入和编辑vExcel公式必须以等号（=）开始1．输入公式v 在Excel中输入公式时首先应选中要输入公式的单元格，然后在其中输入“=”，接着根据需要输入表达式，最后按回车键对输入的内容进行确定。
v例如在单元格D3中输入公式“=10+5*2 ”，输入完成按下回车键，在该单元格中即可显示该公式的运算结果 2.1.2 公式的输入和编辑公式的输入和编辑v也可以先选中要输入公式的单元格，然后在编辑栏中输入公式，输入完成后回车得到的结果是相等的v如果再次选中公式所在的单元格，就会看到只有编辑栏才会显示公式，而单元格内则一直显示计算的结果v如果在单元格中没有输入“=”，而是直接输入“10+5*2”，系统就会认为用户只是输入了一个简单的表达式，按下回车键后该单元格中显示的仍然是“10+5*2” 2.1.2 公式的输入和编辑公式的输入和编辑v如果在输入的公式中包含单元格的引用，可以使用鼠标选择的方法辅助输入公式，这种方法更加简单迅捷并且不易出错v例如，制作成绩表，如果要在单元格H3中输入公式“=C3+D3+E3+F3+G3”：（1）单击H3单元格，输入等号“=”2）单击C3单元格，该单元格的引用自动添加到公式中3）输入加号“+” 2.1.2 公式的输入和编辑公式的输入和编辑（4）单击D3单元格，该单元格的引用自动添加到公式中 2.1.2 公式的输入和编辑公式的输入和编辑（5）继续输入加号“+”，单击E3单元格；输入加号“+”，单击F3单元格；输入加号“+”，单击G3单元格。
6）按回车键即可在单元格中显示公式的计算结果 2.1.2 公式的输入和编辑公式的输入和编辑2．显示公式v输入公式后，系统将自动计算其结果并在单元格中显示出来v如果需要将公式显示在单元格中，可以通过“Excel选项”对话框进行设置v单击“Office”按钮，单击“Excel选项”按钮，在弹出的对话框中选择“高级”选项卡，在“此工作表的显示选项”栏中启用“在单元格中显示公式而非计算结果”复选框单击“确定”按钮，即可在单元格中显示公式 2.1.2 公式的输入和编辑公式的输入和编辑3．编辑公式v输入到单元格中的公式可以再进行编辑，就像编辑单元格中的其他内容一样当对工作表作了某些改动后，需要对公式进行调整以适应工作表的改动，或者输入公式后，在计算的过程中发现工作表中某单元格中的公式有错误，就需要对公式进行调整或修改具体的操作步骤如下：（1）选定包含要修改公式的单元格，这时在编辑栏中将显示出该公式；（2）在编辑栏中对公式进行修改；（3）修改完毕按回车键即可 2.1.2 公式的输入和编辑公式的输入和编辑v修改公式时也可在含有公式的单元格上双击，直接进入公式编辑状态，然后直接在单元格区域中对公式进行修改。
v有时当公式出现错误时，可以根据错误提示对公式进行编辑在出现公式错误的单元格旁会出现一个错误提示按钮 ，单击该按钮可以打开一个菜单，从中可以选择相关操作v删除公式的方法很简单，选取要删除公式的单元格或单元格区域，按“Delete”键即可删除公式，单元格中的计算结果同时被删除 2.1.2 公式的输入和编辑公式的输入和编辑4．移动公式v可使用拖动的方法移动公式到其他的单元格中（1）选定包含公式的单元格，这时单元格的周围会出现一个黑色的边框2）要移动该单元格中的公式可将鼠标放在单元格边框上，当鼠标指针变为四向箭头时按下鼠标左键，拖动鼠标指针到目标单元格3）释放鼠标左键，公式移动完毕 移动公式后，公式中的单元格引用不会发生变化 2.1.2 公式的输入和编辑公式的输入和编辑5．复制公式v 在Excel 中，可以将已经编辑好的公式复制到其他单元格中复制公式时，单元格引用将会根据所用引用类型而变化v 复制公式可使用“开始”选项卡中的“复制”和“粘贴”按钮来复制公式v 复制公式也可以使用“填充柄”，相当于批量复制公式，v 根据复制的需要，有时在复制内容时不需要复制单元格的格式，或只想复制公式。
这时可以使用【选择性粘贴】命令来完成复制操作 2.1.3 公式中的运算符公式中的运算符vExcel 2007公式中，运算符有算术运算符、比较运算符、文本连接运算符和引用运算符四种类型 1. 算术运算符v算术运算符能够完成基本的数学运算（如加法、减法、乘法、除法）、合并数字以及生成数值结果算术运算符含义示例+（加号）加法2+1-（减号）减法，负数2-1，-6*（乘号）乘法3*5/（除号）除法9/2%（百分号）百分比50%^（乘幂号）乘方3^2 2.1.3 公式中的运算符公式中的运算符2.比较运算符v比较运算符能够比较两个或者多个数字、文本串、单元格内容、函数结果的大小关系，比较的结果为逻辑值：TRUE或者FALSE比较运算符含义示例=（等号）等于A1=B1>（大于号）大于A1>B1<（小于号）小于A1
2.1.3 公式中的运算符公式中的运算符4.引用运算符v引用运算符可以把两个单元格或者区域结合起来生成一个联合引用引用运算符含义示例：（冒号） 区域运算符，生成对两个引用之间所有单元格的引用（包括这两个引用）A5:A8，（逗号） 联合运算符，将多个引用合并为一个引用SUM(A5:A10,B5:B10)（引用A5:A10和B5:B10两个单元区域） （空格） 交集运算符，产生对两个引用中共有的单元格的引用SUM(A1:F1 B1:B3)(引用A1:F1和 B1:B3两个单元格区域相交的B1单元格) 2.1.3 公式中的运算符公式中的运算符运算符（优先级从高到低）说明：区域运算符，联合运算符空格交集运算符-负号%百分比^乘幂*和/乘法和除法+和-加法和减法&文本连接符=,>,<,>=,<=,<>比较运算符u如果在一个公式中包含了多个运算符，就要按照一定的顺序进行计算公式的计算顺序与运算符的优先级有关 2.1.4 单元格引用单元格引用v在使用公式和函数进行计算时，往往需要引用单元格中的数据v通过引用可以在公式中使用同一个工作表中不同部分的数据，或者在多个公式中使用同一单元格或区域的数值，还可以引用相同工作簿中不同工作表上的单元格和其他工作簿中的单元格数据。
1 1．．A1A1引用样式和引用样式和R1C1R1C1引用样式引用样式u 默认情况下Excel使用A1引用样式，就是采用列的字母标识和行的数字标识u若想引用单元格区域，则需要输入区域左上角的单元格引用标识，后面跟一个冒号，接着输入区域右下角的单元格引用标识例如，B3:E6引用的是从B3到E6所在区域的所有单元格 1 1．．A1A1引用样式和引用样式和R1C1R1C1引用样式引用样式p在Excel中有时也使用R1C1引用样式R1C1引用是使用“R”加行数字和“C”加列数字来确定单元格的位置例如，R3C5引用的是第3行和第5列交叉处的单元格；R3C4:R5C8引用的是第3行4列到第5行8列之间的所有单元格p系统默认使用的是A1引用样式因此在工作表中要使用R1C1引用样式则需要重新进行一些设置 2 2．相对引用．相对引用n相对引用是指引用单元格的相对地址，即被引用的单元格与公式所在的单元格之间的位置是相对的n如果公式所在的单元格的位置发生了变化，那么引用的单元格的位置也会相应地发生变化n所以当带有相对引用的公式被复制到其他单元格时，公式内引用的单元格将变成与目标单元格相对位置上的单元格。
3 3．绝对引用．绝对引用n 绝对引用是指被引用的单元格与公式所在的单元格之间的位置关系是绝对的，即不管公式被复制到什么位置，公式中所引用的还是原来单元格区域的数据在某些操作中如果不希望调整引用位置，则可使用绝对引用nA1样式的单元格绝对引用是在单元格的行和列前加上“$”符号，例如“$B$5”表示B列5行交叉处的单元格；可以通过按“F4”键进行切换n R1C1样式中单元格的绝对引用是直接在“R”的后面加行号，在“C”的后面加列号，如R3C4则表示第3行第4列交叉处的单元格 4 4．混合引用．混合引用p 混合引用就是在单元格引用中既有绝对引用又有相对引用，例如“$A1”、 “A$1”p列绝对、行相对混合引用采用“$A1”、 “$B1”等形式公式的计算结果不随公式所在单元格的列位置变化而改变，而随公式所在单元格的行位置变化而改变p 列相对、行绝对混合引用采用A$1、B$1等形式公式的计算结果不随公式所在单元格的行位置变化而改变，而随公式所在单元格的列位置变化而改变p 在某些情况下，复制公式时只需改变行或者只需改变列，这时就需要使用混合引用 5 5．引用其他工作表或工作簿中的单元格．引用其他工作表或工作簿中的单元格p 在单元格引用中，不仅仅是引用当前工作表中的单元格，有时需要引用同一工作簿中其他工作表中的单元格。
这就需要使用叹号“！”来实现，格式为： =工作表名称！单元格地址即在单元格地址前加上工作表名称和叹号p如果要引用不同工作簿中某一工作表的单元格，则需要使用如下格式：=[工作簿名称] 工作表名称！单元格地址即用[ ]将工作簿名称括起来 2.1.5 使用名称使用名称 1、定义名称常量 在引用一个常量时，通常会想到将这个常量输入到某一单元格中，然后在公式中引用这个单元格但在Excel中可以给一个并没有出现在单元格中的条目命名 例如，如果在工作表的公式中要用到一个贷款利率，又不希望使用单元格引用的方式来使用贷款利率的具体数值，则可以定义一个常量名称 2.1.5 使用名称使用名称 2、定义公式名称 在Excel中可以为公式创建名称，其方法与为常量定义名称类似所命名的公式也不会出现在工作表中，但可以使用“名称管理器”查看与编辑 例如，可以创建一个名为“总销售额”的命名公式，通过每月的销售额来计算一年总的预销售额，而每月的销售额被输入到A2至A13单元格区域中，则名称“总销售额”可以使用公式“=SUM(A2:A13)” 2.1.5 使用名称使用名称 3、定义区域名称 在Excel中还可以为区域定义名称，这样就可以方便地引用一个或多个单元格区域，并且可以使用“区域交叉”进行区域的重叠引用。
4、使用名称 名称定义好了以后，在公式和函数中就可以使用名称了使用名称的目的就是使公式更简洁、更好理解 名称的使用主要有两种方法，一种是直接手工输入，另一种是粘贴名称 2.1.6 公式的错误和审核公式的错误和审核 1、 Excel常见错误 错误值产生的原因#####!公式计算的结果太长，单元格容纳不下#DIV/O除数为零当公式被空单元格除时也会出现这个错误#N/A公式中无可用的数值或者缺少函数参数#NAME?公式中引用了一个无法识别的名称当删除一个公式正在使用的名称或者在使用文本时有不相称的引用，也会返回这种错误#NULL!使用了不正确的区域运算或者不正确的单元格引用#NUM!在需要数字参数的函数中使用了不能接受的参数，或者公式的计算结果的数字太大 或太小而无法表示#RFF!公式中引用了一个无效的单元格如果单元格从工作表中被删除就会出现这个错误#VALUE!公式中含有一个错误类型的参数或者操作数 2.1.6 公式的错误和审核公式的错误和审核 2、 使用错误检查 Excel使用特定的规则来检查公式中的错误这些规则虽然不能保证工作表中没有错误，但对发现错误却非常有帮助。
错误检查规则可以单独打开或关闭 在Excel中可以使用两种方法检查错误：一是像使用拼写检查器那样一次检查一个错误，二是检查当前工作表中的所有错误一旦发现错误，在单元格的左上角会显示一个三角这两种方法检查到错误后都会显示相同的选项 当单击包含错误的单元格时，在单元格旁边就会出现一个错误提示按钮 ，单击该按钮会打开一个菜单，显示错误检查的相关命令使用这些命令可以查看错误的信息、相关帮助、显示计算步骤、忽略错误、转到编辑栏中编辑公式，以及设置错误检查选项等 2.1.6 公式的错误和审核公式的错误和审核 3．处理循环引用• 如果公式引用自己所在的单元格，则不论是直接引用还是间接引用，都称为“循环引用”•例如在A1单元格中输入公式“=A1+2”，这就是一个循环引用则完成公式输入后按回车键确认时，Excel会弹出一个警告对话框，提示产生了循环引用 2.1.6 公式的错误和审核公式的错误和审核 4．审核公式 使用Excel中提供的多种公式审核功能，可以追踪引用单元格和从属单元格，可以使用监视窗口监视公式及其结果 （1）追踪引用单元格 （2）追踪从属单元格 （3）使用监视窗口 2.1.7 使用数组公式使用数组公式 前面介绍的公式都是执行单个计算并且返回单个结果的情况，而数组公式可以执行多个计算并且返回多个结果。
数组公式作用于两组或多组被称为数组参数的数值，每组数值参数必须具有相同数目的行和列ctrl+shift+enter) 2.2 函数函数 函数是Excel提供的内部工具，是一些预定义的公式2.2.1 函数结构 函数的一般结构是： 函数名（参数1，参数2，……） 其中，函数名是函数的名称，每个函数名是惟一标识一个函数的参数就是函数的输入值，用来计算所需的数据参数可以是常量、单元格引用、数组、逻辑值或者是其他的函数 按照参数的数量和使用区分，函数可以分为无参数型和有参数型 2.2.2 函数的输入函数的输入p要在工作表中使用函数必须先输入函数p函数的输入方法 1、手工输入 2、使用函数向导输入 3．利用公式工具栏输入函数 2.2.3 函数的作用函数的作用v简化公式 函数可以使一些复杂的公式更易于使用，使复杂的数学表达式输入更加简化例如 AVERAGE( )函数v实现特殊功能 使用函数可以实现普通公式无法完成的功能利用函数还可以实现判断功能v减少工作量，提高工作效率 2.2.4 函数中的参数函数中的参数v常量作为参数v逻辑值作为参数v数组型参数v错误值作为参数v单元格引用作为参数v嵌套其他函数v名称作为参数v将整行或整列作为参数 键入键入 。
