（中职）办公软件应用第10章教学课件

1、YCF正版可修改PPT（中职）办公软件应用第10章教学课件第10章 公式和函数10.1 创建与编辑公式10.2 单元格的引用位置10.3 函数10.4 常用函数应用实例公式是对工作表中的数值进行计算和操作的等式，使用公式可以完成各种计算，例如计算半径为10的圆的面积时，就可以利用公式完成，如图10-1所示。10.1.1创建公式1.输入公式输入公式的操作类似于输入字符型、数值型数据，不同的是，在输入公式的时候总是以等号“”作为开头，然后是公式的表达式。在公式中可以包含各种运算符、常量、函数和单元格引用等。在单元格中输入公式的步骤如下。步骤1：选定要存放计算结果的单元格。步骤2：在单元格中输入一个

2、等号“”。步骤3：输入构成公式的表达式。步骤4：按回车键或者单击编辑栏中的“确认”按钮。10.1 创建与编辑公式下一页返回例如，在单元格A2中输入了数值“1024”，想计算1024的80%是多少？可以在A3中输入下列公式“A2*0.8”，如图10-2所示。按回车键或者单击编辑栏上的“确认”按钮（），结果如图10-3所示。如果想要取消输入的公式或者放弃对公式的修改，可以在输入公式或者修改公式过程中单击编辑栏的“取消”按钮（）。2.单元格中公式计算结果的显示单元格中输入公式并确认后，默认在单元格中显示的是公式计算的结果，而在编辑栏中显示的是输入的公式，如图10-3所示。也可以改变设置，在单元格中显

3、示输入的公式，具体设置步骤如下。步骤1：单击“工具”按钮，选择“选项”命令，弹出“选项”对话框，如图10-4所示。步骤2：选择“视图”选项卡，如图10-5所示。步骤3：选择“公式”复选框。10.1 创建与编辑公式上一页下一页返回步骤4：单击“确定”按钮。这时，就会看到单元格中显示的不再是公式的结果，而是公式本身，如图10-6所示。10.1.2编辑公式单元格中的公式也可以像单元格中的其他数据一样进行编辑，如修改、复制、移动等。1.修改公式修改公式同修改单元格中的数据的方法一样。先选定包含要修改公式的单元格，然后把光标定位在编辑栏中，根据需要进行修改，修改完后按回车键确认即可。2.复制公式下面以将

4、图10-7所示单元格D2中的公式复制到单元格D5中为例介绍如何复制公式，操作步骤如下。10.1 创建与编辑公式上一页下一页返回1）方法一步骤1：选定单元格D2。步骤2：选择“编辑”菜单中的“复制”命令，或按Ctrl+C键。步骤3：单击D5单元格。步骤4：单击“编辑”按钮，选择“选择性粘贴”命令，弹出“选择性粘贴”对话框，如图10-8所示。步骤5：在“选择性粘贴”对话框中选择“公式”单选按钮。步骤6：单击“确定”按钮，D5中显示165，即已将D2中的公式复制过来。2）方法二步骤1：选定单元格D2。步骤2：鼠标指向单元格右下角出现 （填充柄）。步骤3：拖动填充柄，一直到D5，松开鼠标，D5中显示1

5、65，即已将D2中的公式复制过来。10.1 创建与编辑公式上一页下一页返回3.移动公式下面以将图10-9所示单元格D2中的公式移动到单元格D10中为例介绍如何移动公式，操作步骤如下。步骤1：选定单元格D2。步骤2：将鼠标移到D2单元格的边框上，当鼠标变为4个方向的箭头时按住左键。步骤3：拖动鼠标到D10单元格。步骤4：释放左键。步骤5：D10中显示171，即已将D2中的公式移动过来。10.1.3公式中的运算符Excel的运算符有以下4类：算术运算符、比较运算符、文本运算符、单元格引用运算符。10.1 创建与编辑公式上一页下一页返回1.算术运算符完成基本的数学运算，常用的算术运算符有加（+）、减

6、（-）、乘（*）、除（/）、百分数（%）、乘方（），见表10-1。2.比较运算符用来比较两个数值的大小关系的运算符，比较运算符会根据公式判断条件，运算结果是成立（TRUE）或不成立（FALSE）。常用的比较运算符有等于（）、小于（）、大于（）、小于或等于（=）、大于或等于（=）、不等于（），见表10-2、表10-3。3.文本运算符用来将多个文本连接成组合文本，常用的文本运算符有&，利用“&”符号可以将一个字符串和另一个字符串连接起来，也可以将一个字符串和某一个单元格的内容连接起来，见表10-4。4.单元格引用运算符用来将单元格区域作为运算对象，常用的单元格引用运算符有：（冒号）和，（逗号），见

7、表10-5。10.1 创建与编辑公式上一页下一页返回5.公式中运算符的运算顺序每个运算符都有自己的运算优先级别，表10-6列出了各种运算符的优先级别。在进行运算时，按照公式中出现的运算符的优先级别从高到低进行运算。对于同一优先级别的运算符，按照从左到右的顺序进行。10.1.4公式返回的错误值与产生原因在使用公式进行计算时，有时会在单元格中看到“#NAME?”“#VALUE？”等信息。这些信息都是当使用公式时出现错误后，才返回的错误值，产生的原因见表10-7。10.1 创建与编辑公式上一页返回单元格的引用位置就是指单元格的地址，一个引用位置代表工作表上的一个或者一组单元格，Excel会到引用位置

8、查找公式中要用的数值。10.2.1单元格地址的输入可以手工输入一个完整的公式，但在输入过程中很容易输入错误的地址。因此，在公式中输入单元格地址最快捷的方法是：使用鼠标选定单元格从而获得单元格的地址。在输入公式过程中选定要参与计算的单元格，使单元格地址出现在公式中的相应位置，可以避免错误的发生。使用鼠标输入的过程如下。步骤1：选择要输入公式的单元格。步骤2：在单元格中输入一个“”号。步骤3：用鼠标单击公式中需要引用的单元格，该单元格地址出现在光标所在位置。步骤4：输入运算符号。10.2 单元格的引用位置下一页返回步骤5：单击另一个需要引用的单元格。步骤6：重复以上步骤，一直到公式输入完整。步骤7

9、：按回车键或者单击编辑栏上的“确认”按钮。例如，要在单元格C7中输入公式“C1+C3+C5”，则可以用鼠标选定单元格C7，然后输入一个“”号，再用鼠标单击单元格C1，再输入“+”号，重复这一过程直到输入完整个公式，按回车键。10.2.2单元格引用的种类1.相对引用所谓相对引用是指单元格引用会随着公式所在单元格位置的变化而变化，也就说，当把一个含有单元格引用的公式复制到另外一个位置时，公式中的单元格地址会随着改变。相对引用用列标加行号表示，如A4。下面以计算两个数的和为例说明相对引用在计算中的作用。（1）制作如图10-10所示的表格。10.2 单元格的引用位置上一页下一页返回（2）在C2单元格中

10、输入公式“A2+B2”，如图10-11所示。（3）选定C2单元格，按Ctrl+C键（复制公式），如图10-12所示。（4）选定C3单元格，再按Ctrl+V键（粘贴公式），可以发现公式中的单元格被自动调整了，如图10-13所示。2.绝对引用绝对引用是指引用固定位置的单元格，也就是指把公式复制到另外的位置时，单元格的引用位置不变。绝对引用用$+列标+$+行号来表示。在Excel 2003中，可以利用F4键在绝对引用和相对引用之间进行切换。当公式中使用了绝对引用时，无论把公式复制到什么位置，公式中所引用的单元格的位置都不会发生变化，如下例所示。（1）在单元格C2中使用绝对引用计算A2与B2的和，并复

11、制该公式，如图10-14所示。10.2 单元格的引用位置上一页下一页返回（2）单击C3单元格，再按Ctrl+V键（粘贴公式），可以发现公式中的单元格位置未被调整，如图10-15所示。3.混合引用在某些情况下，把公式复制到另外的位置时，需要只有行或者只有列保持不变。在这种情况下，就需要使用混合引用。所谓混合引用就是指在一个单元格引用中，既有绝对引用，也有相对引用。例如：单元格地址“$C6”就表明保持“列”不发生变化，但“行”会随着公式位置的变化而变化；同样，单元格地址“C$6”就表明“列”会随着公式位置的变化而变化，但保持“行”不发生变化。图10-16所示的是混合地址引用的例子。10.2.3使用

12、公式解决实际问题下面以计算销售人员工资为例来具体说明如何利用公式解决实际问题，具体操作步骤如下。步骤1：打开使用公式的工作簿，如图10-18所示。10.2 单元格的引用位置上一页下一页返回步骤2：分析要解决的问题，找到公式表达式。每个员工的工资提成+底薪，提成销售成绩*5%，销售成绩等于所销售的产品总金额。因此，只要找到计算销售产品总金额的公式，问题就基本解决了。销售成绩产品1个数*产品1单价+产品2个数*产品2单价+产品3个数*产品3单价+产品4个数*产品4单价，选定F3（存放销售成绩计算结果）、G3（存放提成计算结果）、I3（存放工资计算结果），分别输入公式“B3*$B$11+C3*$C$

13、11+D3*$D$11+E3*$E$11”“F3*0.05”“G3+H3”，之后在I3单元格中显示该员工的工资，如图10-19所示。步骤3：通过拖动填充柄的方法计算其他员工的工资，如图10-20所示。 10.2 单元格的引用位置上一页返回Excel函数是Excel系统预先定义好的、执行计算和分析等处理数据任务的特殊公式。利用函数既可以提高工作效率，也能减少错误的发生，还能缩小工作表所占的内存空间，更能实现其他方法无法实现的计算，因此只要能使用Excel系统提供的函数解决问题，就不要自己编写公式。函数中用来参与运算的数值称为参数，函数运算完成后会返回一个数值称之为函数值。若要使用函数，则应将它们

14、输入到工作表中的公式内。在公式中使用函数的字符次序称为语法，所有的函数都遵循相同的语法，若不遵守这种语法，则Excel将显示一条信息，指出在公式中有错误，如图10-21所示。如果公式以一个函数开始，则应该像其他公式一样，在函数名前面加一个等号。函数名后的一对括号必不可少，括号告诉Excel参数开始和结束的位置。注意：左右两个括号必须成对出现，而且括号前后不能有空格。在括号中指定参数（即运算的对象），参数可以是数字、字符、逻辑值、引用位置或其他函数等。10.3 函数下一页返回10. 3.1函数使用的规定1函数的语法格式语法格式：函数名（参数1，参数2，参数3，）函数名是函数的名称，每一个函数都有

15、自己唯一的函数名，它决定了函数的功能和用途。不同的函数的参数个数不同，应根据不同的函数进行分析。例如： SUM（B3，B6）求单元格B3和单元格B6的和。SUM（B3，B6，B7）求单元格B3、B6、B7的和。2.使用参数参数规定了函数的运算对象、顺序或结构等。参数总是位于函数名的右侧并用括号括起来。大多数参数的数据类型都是确定的。参数可以是能产生所需数据类型的任意值，常用的参数有下列3种类型。1）常量例1：SUM（2，12，100）10.3 函数上一页下一页返回计算结果：114（2、12、100这3个数的和）。2）单元格区域的引用例2：SUM（A1，A5）计算结果：等于单元格A1和单元格A5

16、的和。例3：SUM（A1：A5）计算结果：等于单元格A1到A5区域中所有单元格的和。3）函数例4：SUM（20，MAX（B1：B5），102，A6）计算结果：等于20、单元格B1到B5区域中的最大值、100（10的2次幂）、单元格A6的和。10.3.2输入函数在工作表中输入函数有两种较为常用的方法，一种是直接手动输入，另外一种是通过“插入函数”对话框输入。下面以最常用的求和函数为例进行介绍。10.3 函数上一页下一页返回1.手工输入函数手工输入函数的方法与在单元格中输入公式的方法一样。先输入一个“”，然后输入函数名、括号、参数即可。例如，在A7单元格中存放单元格A1到单元格A6的和，可以按以下

17、方法输入函数。步骤1：输入要参与求和运算的数据，如图10-22所示。步骤2：选定A7单元格，并输入“”，然后输入函数以及参数，如图10-23所示。步骤3：输入完成后直接按回车键即可，如图10-24所示。2.使用“插入函数”对话框输入函数使用手动输入函数固然简单，但是要求用户记住所有的函数名及其参数的使用方法，这比较困难。用户可以使用另一种输入函数的办法使用“插入函数”对话框，下面仍然以最常用的求和函数为例介绍如何使用“插入函数”对话框输入函数。例如：将某个同学各门课程的得分求和，计算结果存放在K2单元格中，可以按照以下步骤输入函数。10.3 函数上一页下一页返回步骤1：输入要参与求和运算的数据

18、，如图10-25所示。步骤2：选定K2单元格，输入“”后，可以单击“编辑栏”左侧的“函数”下拉按钮，如图10-26所示，选择要使用的函数，可以直接打开“函数参数”对话框。步骤3：在“函数参数”对话框中，单击第一个参数（Number1）旁边的工作表按钮 ，选定要参与求和运算的单元格区域，如图10-27所示。步骤4：单击“确定”按钮，在K2单元格中显示运算结果，如图10-30所示。10.3.3修改函数函数输入完成后，用户可以根据需要对其进行修改，操作步骤如下。步骤1：选定包含函数的单元格，用鼠标单击“编辑栏”，光标定位到“编辑栏”中，如图10-31所示。步骤2：根据需要进行修改，完成后，直接按回车

19、键即可。10.3 函数上一页返回10.4.1 Excel内置函数Excel 2003提供了大量的内置函数，按照功能进行划分，主要可以分为以下几类。（1）数学和三角函数：运用该类函数进行数学方面的运算。（2）统计函数：运用该类函数对选定区域内的数据进行统计分析。（3）日期与时间函数：运用该类函数可以分析和处理与日期和时间有关的值。（4）财务函数：运用该类函数可以进行一般的财务运算。（5）查询与引用函数：运用该类函数可以在数据区域中查找特定内容。（6）数据库函数：运用该类函数可以分析数据清单中的数值是否符合特定条件。（7）逻辑函数：运用该类函数可以进行逻辑判断。10.4 常用函数应用实例下一页返回

20、（8）信息函数：运用该类函数可以确定存储在单元格中的数据的类型。（9）文本函数：运用该类函数可以处理字符串。 10.4.2常用函数及应用实例Excel 2003提供了9种类型几百个内置函数，每个函数的使用各不相同。下面通过常用函数使用实例介绍函数的具体使用方法。1.SUM函数功能：用于计算多个参数之和。语法：SUM（number1，number2，）number1，number2，为130个需要求和的参数。例1：某学校期中考试，一共有8门考试科目，试计算每个学生本次考试的总分、各科总分、各科及格线上总分，操作步骤如下。10.4 常用函数应用实例上一页下一页返回步骤1：新建一个工作簿，在Shee

21、t1工作表中新建如图10-32所示的表格，并输入学生序号、姓名及各科成绩，选定K2单元格，用来存放总分。步骤2：在K2单元格中输入“=SUM（C2：J2）”，如图10-33所示。步骤3：通过拖动填充柄的方法，将K2单元格中的公式自动填充到K11，表示用同样的方法计算每个学生的总分，如图10-34所示。步骤4：用同样的方法计算各科总分，如图10-35所示。2.SUMIF函数功能：用于计算符合条件的单元格的和。语法：SUMIF（range，criteria，sum_range）range：用于指定条件判断的单元格区域。criteria：用于指定条件。sum_range：用于指定需要求和的单元格区域

22、。10.4 常用函数应用实例上一页下一页返回说明：只有当range中的相应单元格满足criteria中的条件时，才对sum_range中相应的单元格求和。例2：接例1，试计算各科及格线上的总分。语文、数学、英语3科的及格线为90，其余科目的及格线为60，操作步骤如下。步骤1：选定C14单元格，用来存放“语文”及格线上的总分。步骤2：在C14单元格中输入“=SUMIF（C2：C11，=90，C2：C11）”，按回车键，则在C14单元格中显示语文科目及格线上总分，如图10-36所示。步骤3：通过拖动填充柄的方法，将C14单元格中的公式自动填充到F14，表示用同样的方法计算数学科目、英语科目的及格线

23、上总分。步骤4：选定F14，将F14单元格中公式修改为“=SUMIF（F2：F11，=60，F2：F11）”，按回车键，在F14单元格中显示政治科目及格线上总分，如图10-37所示。10.4 常用函数应用实例上一页下一页返回步骤5：通过拖动填充柄的方法，将F14单元格中的公式自动填充到J14，表示用同样的方法计算剩余科目的及格线上总分，如图10-38所示。3.AVERAGE函数功能：用于对所有参数计算算术平均值。语法：AVERAGE（number1，number2，）number1，number2，为130个需要求平均值的参数。例3：接例2，计算每位学生的平均分和各科平均分。操作步骤如下。步骤

24、1：选定L2单元格，用来存放第一位学生的平均分。步骤2：在L2单元格中输入“=AVERAGE（C2：J2）”，按回车键，则在L2单元格中显示第一位学生的平均分，如图10-39所示。步骤3：通过拖动填充柄的方法，将L2单元格中的公式自动填充到L11，表示用同样的方法计算其余学生的平均分，如图10-40所示。10.4 常用函数应用实例上一页下一页返回步骤4：同样方法计算各科平均分，如图10-41所示。4.MAX函数功能：用于计算多个参数中的最大值。语法：MAX（number1，number2，）number1，number2，为130个需要求最大值的参数。例4：接例3，计算各科的最高分。操作步骤如

25、下。步骤1：选定C16单元格，用来存放第一科的最高分。步骤2：在C16单元格中输入“=MAX（C2：C11）”，按回车键，则在C16单元格中显示第一科的最高分，如图10-42所示。步骤3：通过拖动填充柄的方法，将C16单元格中的公式自动填充到J16，表示用同样的方法计算其余科目的最高分，如图10-43所示。10.4 常用函数应用实例上一页下一页返回5.MIN函数功能：用于计算多个参数中的的最小值。语法：MIN（number1，number2，）number1，number2，为130个需要求最小值的参数。例5：接例4，计算各科的最低分。操作步骤如下。步骤1：选定C17单元格，用来存放第一科的最

26、低分。步骤2：在C17单元格中输入“=MIN（C2：C11）”，按回车键，则在C17单元格中显示第一科的最低分，如图10-44所示。步骤3：通过拖动填充柄的方法，将C17单元格中的公式自动填充到J17，表示用同样的方法计算其余科目的最低分，如图10-45所示。6.COUNT函数功能：统计参数列表中数字数据的个数。10.4 常用函数应用实例上一页下一页返回语法：COUNT（number1，number2，）number1，number2，参数的个数最多为30个。说明：参数列表中数字类型的数据才能被统计在内。例6：接例5，试统计各科参加考试的人数。（1）选定C18单元格，用来存放参加第一科考试的人

27、数。（2）在C18单元格中输入“=COUNT（C2：C11）”，按回车键，则在C18单元格中显示参加第一科考试的人数，如图10-46所示。（3）通过拖动填充柄的方法，将C18单元格中的公式自动填充到J18，表示用同样的方法计算参加其余科目考试的人数，如图10-47所示。7.COUNTIF函数功能：统计符合条件的单元格的个数。语法：COUNTIF（range，criteria）10.4 常用函数应用实例上一页下一页返回range：需要统计的区域。criteria：用于指定条件。说明：只有当range中的相应单元格满足criteria中的条件时，才能被统计在内。例7：接例6，试统计各科及格线上的人

28、数。语文、数学、英语3科的及格线为90，其余科目的及格线为60。操作步骤如下。步骤1：选定C19单元格，用来存放“语文”及格线上的人数。步骤2：在C19单元格中输入“=COUNTIF（C2：C11，=90）”，按回车键，则在C19单元格中显示语文科目及格线上的人数，如图10-48所示。步骤3：通过拖动填充柄的方法，将C19单元格中的公式自动填充到F19，表示用同样的方法计算数学科目、英语科目的及格线上的人数。10.4 常用函数应用实例上一页下一页返回步骤4：选定F19，将F19单元格中的公式修改为“=COUNTIF（F2：F11，=60）”，按回车键，则在F19单元格中显示政治科目及格线上的人

29、数，如图10-49所示。步骤5：通过拖动填充柄的方法，将F19单元格中的公式自动填充到J19，表示用同样的方法计算剩余科目的及格线上的人数，如图10-50所示。8.RANK函数功能：返回一个数字在数字列表中的排位。语法：RANK（number1，ref，order）number1：指需要排位的数字。ref：指数字列表。order：为一个数字，指出排位的方式。10.4 常用函数应用实例上一页下一页返回说明：（1）如果order为0（可以省略），排位是按照降序排列的；如果order为非零数，排位是按照升序排列的。（2）为避免重复排位，ref应该使用绝对引用。例8：接例7，试计算每位同学在班级中的排名。操作步骤如下。步骤1：选定M2单元格，用来存放第一位同学在班级中的排名。步骤2：在M2单元格中输入“=RANK（K2，$K$2：$K$11，0）”，按回车键，则在M2单元格中显示第一位同学在班级中的排名，如图10-51所示。步骤3：通过拖动填充柄的方法，将M2单元格中的公式自动填充到M11，表示用同样的方法计算其余同学在班级中的排名。9.IF函数功能：执行真假值判断，根据逻辑计算的真假值，返回不同结果。10.4 常用函数应用实例上一页下一页返回语法：IF（logical_test，value_if_true，value_if_false）logical_test：表示计算结