项目十二使用Excel公式和函数.ppt

项目十二 使用Excel公式和函数 Excel强大的计算功能主要依赖于其公式和函数，利用公式和函数可以对表格中的数据进行各种计算和处理，从而提高我们在制作复杂表格时的工作效率及计算准确率。 知识目标知识目标u了解公式和函数的概念，以及公式中的运算符。u掌握公式的创建、移动、复制与修改方法。u了解审核公式的方法，以及公式中错误提示的含义。u掌握函数的使用方法。u掌握设置单元格引用的方法。 能力目标能力目标u能够使用公式对工作表数据进行计算操作。u能够将公式复制或移动到工作表的其他地方，以便快捷计算工作表数据。u能够正确地使用函数对工作表数据进行计算、分析和管理。u能够在公式中正确设置单元格引用。 本项目任务列表l任务一 使用公式 l任务二 使用函数 l任务三 单元格引用 任务一 使用公式 l一、认识公式和函数l二、公式中的运算符 l三、公式的创建、移动、复制与修改 l四、公式中的错误与审核 利用公式和函数可以对表格中的数据进行各种计算和处理。本任务中我们首先认识一下公式和函数，以及了解一下公式中的运算符，然后学习公式的创建、移动、符合和修改方法，最后了解一下审核公式的方法。 一、认识公式和函数 要输入公式必需先输入“=”，然后再在其后输入表达式，否则Excel会将输入的内容作为文本型数据处理。表达式由运算符和参与运算的操作数组成。运算符可以是算术运算符、比较运算符、文本运算符和引用运算符；操作数可以是常量、单元格引用和函数等。 函数是预先定义好的表达式，它必须包含在公式中。每个函数都由函数名和参数组成，其中函数名表示将执行的操作（如求平均值函数AVERAGE），参数表示函数将作用的值的单元格地址，通常是一个单元格区域（如A2：B7单元格区域），也可以是更为复杂的内容。在公式中合理地使用函数，可以完成诸如求和、逻辑判断和财务分析等众多数据处理功能 。二、公式中的运算符 运算符是用来对公式中的元素进行运算而规定的特殊符号。Excel包含4种类型的运算符：算术运算符、比较运算符、文本运算符和引用运算符。u算术运算符算术运算符 算术运算符有6个，其作用是完成基本的数学运算，并产生数字结果。 u比较运算符比较运算符 比较运算符有6个，它们的作用是比较两个值，并得出一个逻辑值，即“TRUE（真）”或“FALSE（假）”。 u文本运算符文本运算符 使用文本运算符“”（与号）可将两个或多个文本值串起来产生一个连续的文本值。例如：输入“祝你”“快乐、开心！”会生成“祝你快乐、开心！”。 u引用运算符引用运算符 引用运算符有3个，它们的作用是将单元格区域进行合并计算。 三、公式的创建、移动、复制与修改 1创建公式创建公式 要创建公式，可以直接在单元格中输入，也可以在编辑栏中输入，输入方法与输入普通数据相似。 也可在输入等号后单击要引用的单元格，然后输入运算符，再单击要引用的单元格（引用的单元格周围会出现不同颜色的边框线，它与单元格地址的颜色一致，便于用户查看）。 2移动与复制公式移动与复制公式 移动和复制公式的操作与移动、复制单元格内容的操作方法是一样的。所不同的是，移动公式时，公式内的单元格引用不会更改，而复制公式时，单元格引用会根据所用引用类型而变化，即系统会自动改变公式中引用的单元格地址。 利用填充柄复制公式3修改或删除公式修改或删除公式 要修改公式，可单击含有公式的单元格，然后在编辑栏中进行修改，或双击单元格后直接在单元格中进行修改，修改完毕按【Enter】键确认。 删除公式是指将单元格中应用的公式删除，而保留公式的运算结果。 四、公式中的错误与审核 1公式错误代码公式错误代码 在使用公式时，经常会遇到公式的返回值为一段代码的情况，如“#”、“#VALUE”等。了解这些代码的含义，用户就可以知道在公式使用过程中出现了什么样的错误。下面是一些常见的错误代码产生的原因。u“#”：输入到单元格中的数值或公式计算结果太长，单元格容纳不下。增加单元格宽度可以解决这个问题。另外，日期运算结果为负值也会出现这种情况，此时可以改变单元格的格式，比如改为文本格式。u“#DIV/0!”：除数引用了零值单元格或空单元格。u“#N/A”：公式中没有可用数值，或缺少函数参数。u“#NAME?”：公式中引用了无法识别的名称，或删除了公式正在使用的名称。例如，函数的名称拼写错误，使用了没有被定义的区域或单元格名称，引用文本时没有加引号等。u“#NULL!”：使用了不正确的区域运算符或引用的单元格区域的交集为空。例如，输入公式“=A1:B4 C1:D4”，因为这两个单元格区域交集为空，所以回车后返回值为“#NULL!”。u“#NUM!”：公式产生的结果数字太大或太小，Excel无法表示出来，例如，输入公式“=10309”，由于运算结果太大，公式返回错误；或在需要数字参数的函数中使用了无法接受的参数，例如，在输入开平方的公式(SQRT)时，引用了负值的单元格或直接使用了负值。u“#RFF”：公式引用的单元格被删除，并且系统无法自动调整，或链接的数据不可用。u“#VALUE”：当公式需要数字或逻辑值时，却输入了文本；为需要单个值（而不是区域）的运算符或函数提供了区域引用；输入数组公式后，没有按【CtrlShiftEnter】组合键确认。2“公式审核公式审核”组组 在使用公式的过程中，有时可能会因人为疏忽，或是表达式的设置错误，导致计算结果发生错误。使用Excel提供的审核功能可以方便地检查公式、分析数据流向和来源、纠正错误、把握公式和值的关联关系等。在“公式”选项卡上的“公式审核”组中可以看到审核按钮。 3查找与公式相关的单元格查找与公式相关的单元格追踪引用单元格 追踪从属单元格 4追踪导致公式错误的单元格追踪导致公式错误的单元格任务实施 下面通过计算工作表中的数据，练习公式的使用方法。 一、计算一、计算2011春季成绩表数据春季成绩表数据 拖动填充柄计算其他学生总分打开素材文件，输入公式并利用填充柄复制公式进行计算。计算平均分 打开素材文件，输入公式并复制，计算出“证件统计表”中各年份的毕业生人数和证件的合计。 输入公式并向下复制二、计算毕业生取得证书数二、计算毕业生取得证书数输入公式并向右复制任务二 使用函数 l一、常用函数讲解 l二、函数应用方法 本任务中，我们先了解一下常用函数的类型，然后学习函数的使用方法。 一、常用函数讲解二、函数应用方法 使用函数时，应首先确认已在单元格中输入了“=”号，即已进入公式编辑状态。接下来可输入函数名称，再紧跟着一对括号，括号内为一个或多个参数，参数之间要用逗号来分隔。用户可以在单元格中手工输入函数，也可以使用函数向导输入函数。 1直接输入函数直接输入函数 手工输入一般用于参数比较单一、简单的函数，即用户能记住函数的名称、参数等，此时可直接在单元格中输入函数。 2使用向导输入函数使用向导输入函数 如果不能确定函数的拼写或参数，可以使用函数向导输入函数。 在工作表中选择要进行计算的单元格区域任务实施 下面大家通过计算工作表中的数据，练习公式和函数的使用方法。 一、计算彩电销售统计表数据一、计算彩电销售统计表数据 输入公式并向下复制输入公式并向下复制输入公式并向右复制输入公式并向下复制 假设机试和笔试成绩都大于等于60，则在“考试结果”列的相应单元格中显示“合格”，否则为“不合格”；假设笔试或机试成绩小于60，则在“不及格科目”列的相应单元格显示科目名称“笔试”或“机试”，机试和笔试成绩均大于等于60，则显示“无”。下面利用If和AND函数计算“考绩情况表”中的“考试结果”和“不及格科目”项。 二、分析考试结果数据二、分析考试结果数据输入公式并向下复制输入公式并向下复制三、计算工资表数据三、计算工资表数据 从2011年9月1日起，个人所得税是月收入超过3500起征。 个人所得税税前工资（总工资社保）免征额税率速算扣除数 其中税率如下表所示。 下面利用公式和函数计算出“工资表”中的“应发合计”、“个人所得税”、“扣款合计”和“实发合计”值。 输入公式并向下复制计算“应发合计”输入公式并向下复制，计算“个人所得税”输入公式并向下复制，计算“扣款合计”输入公式并向下复制，计算“实发工资” 四、计算成绩表数据并设置级别四、计算成绩表数据并设置级别 下面计算成绩表中的平均分，然后以“平均分”为依据判断成绩的优、良、中与不及格。 拖动填充柄向下复制公式输入公式并向下复制任务三 单元格引用 l一、相同或不同工作簿、工作表中的引用 l二、相对引用、绝对引用和混合引用 引用的作用是通过标识工作表中的单元格或单元格区域，来指明公式中所使用的数据的位置。通过单元格的引用，可以在一个公式中使用工作表不同部分的数据，或者在多个公式中使用一个单元格中的数据，还可以引用同一个工作簿中不同工作表中的单元格，甚至还可以引用不同工作簿中的数据。当公式中引用的单元格数值发生变化时，公式会自动更新其所在单元格内容，即更新其计算结果。 一、相同或不同工作簿、工作表中的引用u引用不同工作表间的单元格引用不同工作表间的单元格 在同一工作簿中，不同工作表中的单元格可以相互引用，它的表示方法为：“工作表名称!单元格或单元格区域地址”。如：Sheet2!F8:F16u引用不同工作簿中的单元格引用不同工作簿中的单元格 在当前工作表中引用不同工作簿中的单元格的表示方法为：工作簿名称工作簿名称.xlsx工作表名称！单元格（或单元格区域）地址工作表名称！单元格（或单元格区域）地址二、相对引用、绝对引用和混合引用 Excel提供了相对引用、绝对引用和混合引用三种引用类型，用户可以根据实际情况选择引用的类型。 1相对引用相对引用 相对引用指的是单元格的相对地址，其引用形式为直接用列标和行号表示单元格，例如B5，或用引用运算符表示单元格区域，如B5：D15。如果公式所在单元格的位置改变，引用也随之改变。默认情况下，公式使用相对引用，如前面讲解的复制公式就是如此。 引用单元格区域时，应先输入单元格区域起始位置的单元格地址，然后输入引用运算符，再输入单元格区域结束位置的单元格地址。 2绝对引用绝对引用 绝对引用是指引用单元格的精确地址，与包含公式的单元格位置无关，其引用形式为在列标和行号的前面都加上“$”符号。不论将公式复制或移动到什么位置，引用的单元格地址的行和列都不会改变。 向下复制输入公式时，绝对引用不变 引用中既包含绝对引用又包含相对引用的称为混合引用，如A$1或$A1等，用于表示列变行不变或列不变行变的引用。 如果公式所在单元格的位置改变，则相对引用改变，而绝对引用不变。 编辑公式时，输入单元格地址后，按【F4】键可在绝对引用、相对引用和混合引用之间切换。3混合引用混合引用任务实施 下面分别计算表格和某超市商品促销折算表中的数据，练习单元格引用在实际工作中的用法。 一、使用公式计算表格数据一、使用公式计算表格数据 根据F列中各单元格的说明，分别在其右侧的单元格中输入相应公式 打开素材文件，根据提示输入公式进行计算。根据J1单元格的说明，在J2单元格输入公式并向下复制二、计算乐玛超市促销折价表数据二、计算乐玛超市促销折价表数据 计算排在第一位的商品的原价总额 计算排在第一位的商品的折后价 计算排在第一位的商品的折后总额 打开素材文件，根据提示输入公式进行计算，然后复制公式。向下拖动G4：I4单元格右下角的填充柄至I184单元格后释放鼠标，得到其他商品的原价总额、折后价和折后总额