4.6教学课件 高教版 中职 计算机文化基础教程（第二版）.ppt

《4.6教学课件 高教版 中职 计算机文化基础教程（第二版）.ppt》由会员分享，可在线阅读，更多相关《4.6教学课件 高教版 中职 计算机文化基础教程（第二版）.ppt（39页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第四章Excel 2003实用基础 4.6 公式的应用公式的应用返回目录返回目录4.6 公式的应用公式的应用4.6.1 案例说明案例说明4.6.2 在公式中使用运算符在公式中使用运算符4.6.3 单元格的引用单元格的引用4.6.4 名称的定义和应用名称的定义和应用4.6.6 函数函数4.6.7 案例总结案例总结返回目录返回目录4.6.1 案例说明案例说明本节案例通过制作一个学生成绩表，学习公式和本节案例通过制作一个学生成绩表，学习公式和函数的基础知识。具体知识点为：函数的基础知识。具体知识点为：在公式中使用运算符；在公式中使用运算符；在单元格中输入公式；在单元格中输入公式；公式运算或常用的错误

2、值；公式运算或常用的错误值；SUM()SUM()、IF()IF()、MIN()MIN()、MAX()MAX()、HOUR()HOUR()、TODAY()TODAY()、COUNT()COUNT()等函数；等函数；单元格的引用方式；单元格的引用方式；名称的定义方法；名称的定义方法；使用名称管理器管理使用名称管理器管理名称；名称；在公式中应用名称在公式中应用名称；返回目录返回目录4.6.2 在公式中使用运算符在公式中使用运算符步骤一：总分计算打开本案例文件打开本案例文件4.5.xls4.5.xls，将光标定位在，将光标定位在G2G2中，中，要计算学生的总分，此时输入要计算学生的总分，此时输入“=D

3、2+E2+F2”=D2+E2+F2”，被引用的单元格会显示彩色的边框，回车确定，被引用的单元格会显示彩色的边框，回车确定，在在G2G2单元格中得出总分，如图所示。单元格中得出总分，如图所示。返回目录返回目录4.6.2 在公式中使用运算符在公式中使用运算符提示在一个空单元格中键入一个等号时，在一个空单元格中键入一个等号时，ExcelExcel会默会默认为用户是在输入一个公式。输入参数时，如认为用户是在输入一个公式。输入参数时，如果是引用单元格可以直接单击选择单元格。在果是引用单元格可以直接单击选择单元格。在ExcelExcel中用中用“*”“*”代替乘号。代替乘号。返回目录返回目录4.6.2 在

4、公式中使用运算符在公式中使用运算符步骤二：复制公式同文本一样，公式也是可同文本一样，公式也是可以复制的。选中以复制的。选中G2G2单元单元格并复制（可以使用复制格并复制（可以使用复制按钮或者按钮或者ctrl+cctrl+c快捷键），快捷键），光标定位到光标定位到G3G3单元格，单元格，粘贴，此时会弹出一个粘粘贴，此时会弹出一个粘贴按钮。单击这个按钮，贴按钮。单击这个按钮，将弹出粘贴下拉列表，如将弹出粘贴下拉列表，如图所示，选择使用目标主图所示，选择使用目标主题即可。题即可。返回目录返回目录4.6.2 在公式中使用运算符在公式中使用运算符提示提示 在在EXCELEXCEL中，如果公式和函数不能计

5、算出结果中，如果公式和函数不能计算出结果 ，有时，有时会直接返回一个错误值，下页中的表列出了公式的单会直接返回一个错误值，下页中的表列出了公式的单元格可能出现的各类错误值。元格可能出现的各类错误值。返回目录返回目录4.6.2 在公式中使用运算符在公式中使用运算符错误值原因说明#该列列宽不够，或者包含一个无效的时间或日期#DIV/O!该公式使用了0作为除数，或者公式中使用了一个空单元格#N/A公式中引用的数据对函数或公式不可用#NAME?公式中使用了Excel不能辨认的文本或名称#NULL!公式中使用了一种不允许出现相交但却交叉了的两个区域#NUM!使用了无效的数字值#REF!公式引用了一个无效

6、的单元格#VALUE!函数中使用的变量或参数类型错误返回目录返回目录4.6.3 单元格的引用单元格的引用步骤一：相对公式引用在在4.6.24.6.2小节中制作好的小节中制作好的案例的基础上继续操作。案例的基础上继续操作。选中选中G2G2单元格并复制，单元格并复制，再选中再选中G5:G16G5:G16单元格区单元格区域，回车，此时域，回车，此时G4:G16G4:G16区域内计算出各名学生区域内计算出各名学生的总分的总分返回目录返回目录4.6.3 单元格的引用单元格的引用提示提示 只使用列标和行号来表示单元格的方法即是单元格的只使用列标和行号来表示单元格的方法即是单元格的相对引用，如相对引用，如C

7、5C5、A20A20。ExcelExcel一般是使用相对地址来一般是使用相对地址来引用单元格的位置。相对地址是指当把一个含有单元引用单元格的位置。相对地址是指当把一个含有单元格地址的公式复制到一个新的位置或者用一个公式填格地址的公式复制到一个新的位置或者用一个公式填入一个范围时，公式中的单元格地址会随着改变，如入一个范围时，公式中的单元格地址会随着改变，如此时此时G5G5单元格内显示为单元格内显示为“=D5+E5+F5”=D5+E5+F5”，其余单元格也，其余单元格也为相对引用自动调整。为相对引用自动调整。返回目录返回目录4.6.3 单元格的引用单元格的引用步骤二：将G2:G16区域内容删除步

8、骤三：绝对引用在在G2G2单元格内输入单元格内输入“=$D$2+$E$2+$F$2”=$D$2+$E$2+$F$2”步骤四：填充公式将光标放在将光标放在G2G2单元格右下角，使用填充柄拖动单元格右下角，使用填充柄拖动到到G16G16单元格内，此时显示出学生的成绩，如单元格内，此时显示出学生的成绩，如图图4-624-62所示，此时学生的成绩相同，均为所示，此时学生的成绩相同，均为G2G2单单元格所计算的结果。元格所计算的结果。返回目录返回目录4.6.3 单元格的引用单元格的引用 提示：提示：在单元格列号和行号前面添加在单元格列号和行号前面添加“$”$”符号后，单元格的引用为绝符号后，单元格的引用

9、为绝对引用。绝对引用的是固定的单元格或单元格区域，不会发生对引用。绝对引用的是固定的单元格或单元格区域，不会发生相对变化。相对变化。图图4-624-62返回目录返回目录4.6.3 单元格的引用单元格的引用步骤五：将G2:G16区域内容删除步骤六：输入混合引用公式在在G2G2单元格内输入单元格内输入“=D$2+E2+F2”=D$2+E2+F2”步骤七：填充混合引用将光标放在将光标放在G2G2单元格右下角，使用填充柄拖动单元格右下角，使用填充柄拖动到到G16G16单元格内，此时显示出学生的成绩，如单元格内，此时显示出学生的成绩，如图图4-634-63所示。所示。返回目录返回目录4.6.3 单元格的

10、引用单元格的引用图图4-634-63返回目录返回目录4.6.3 单元格的引用单元格的引用提示：除了相对引用和绝对引用，有时还可能需要进除了相对引用和绝对引用，有时还可能需要进行混合引用，在某些情况下，需要复制公式时行混合引用，在某些情况下，需要复制公式时只有行或者只有列保持不变，这就需要使用混只有行或者只有列保持不变，这就需要使用混合引用。混合引用是指在一个单元格地址引用合引用。混合引用是指在一个单元格地址引用中，既有相对地址引用，同时也包含绝对地址中，既有相对地址引用，同时也包含绝对地址引用。如引用。如G2G2单元格中单元格中“=D$2+E2+F2”=D$2+E2+F2”，此时不，此时不论公

11、式复制到哪一行，对于语文成绩始终引用论公式复制到哪一行，对于语文成绩始终引用了学生了学生“张三张三”的语文成绩。的语文成绩。返回目录返回目录4.6.3 单元格的引用单元格的引用步骤八：工作表名称引用单元工作表标签旁的插入工作表按钮，在工作单元工作表标签旁的插入工作表按钮，在工作薄中插入一个新的工作表，在工作表薄中插入一个新的工作表，在工作表“sheet2”sheet2”中输入中输入“=Sheet1!A1”=Sheet1!A1”，如图，如图4-644-64所示。所示。图图4-644-64返回目录返回目录4.6.3 单元格的引用单元格的引用步骤九：复制公式将光标定位在工作表将光标定位在工作表“sh

12、eet2”sheet2”中中A1A1单元格右单元格右下角，使用填充柄拖动到下角，使用填充柄拖动到B2B2，如图，如图4-65 4-65。图图4-654-65返回目录返回目录4.6.3 单元格的引用单元格的引用步骤十：填充公式继续拖动填充柄到继续拖动填充柄到B16B16，完成填充，如，完成填充，如图图4-664-66所示所示 。图图4-664-66返回目录返回目录4.6.3 单元格的引用单元格的引用提示：从一个工作薄中引用单元格的格式为：工作表从一个工作薄中引用单元格的格式为：工作表名称名称!单元格地址；从不同的工作薄中引用单元单元格地址；从不同的工作薄中引用单元格的格式为：格的格式为：工作簿名

13、称工作簿名称 工作表名称工作表名称!单元格单元格地址。工作表名称后的地址。工作表名称后的“！”符号是系统自动符号是系统自动加上的。加上的。返回目录返回目录4.6.4 名称的定义和引用名称的定义和引用步骤一：定义名称打开打开4.64.6工作簿，工作簿，选中选中D2:D16D2:D16，单，单击名称框，在其中击名称框，在其中输入输入“语文语文”，回，回车确定。此时将车确定。此时将D2:D16D2:D16单元各区单元各区域定义为域定义为“语文语文”。返回目录返回目录4.6.4 名称的定义和引用名称的定义和引用步骤二：定义名称步骤二：定义名称 单击单击【插入插入】菜单菜单【名名称称】，选择，选择【定义

14、定义】步骤三：定义数学列。步骤三：定义数学列。在弹出的对话框的在弹出的对话框的“名名称称”文本框内输入名称文本框内输入名称“数学数学”，引用位置为，引用位置为“=Sheet1!$E$2:$E$16=Sheet1!$E$2:$E$16”如图如图4-694-69所示，单击所示，单击确定按钮，将确定按钮，将E2:E16E2:E16单元格区域定义为数学。单元格区域定义为数学。图图4-694-69返回目录返回目录4.6.4 名称的定义和引用名称的定义和引用步骤四：步骤四：选选择择F1:F16F1:F16，在在【插插入入】菜菜单单中中选选择择【名名称称】，点点击击【指定指定】按钮按钮步骤五：指定名称步骤五

15、：指定名称 在弹出的对话框中选中在弹出的对话框中选中“首首行行”复选框，如图复选框，如图4-714-71所示，所示，单击单击“确定确定”按钮，此时工按钮，此时工作表中作表中F2:F16F2:F16区域被命名区域被命名为为“表头表头”。图图4-714-71返回目录返回目录4.6.4 名称的定义和引用名称的定义和引用提示在创建和编辑名称时需要注意名称的语法规则，在创建和编辑名称时需要注意名称的语法规则，名称的语法规则主要有几下几点：（名称的语法规则主要有几下几点：（1 1）名称中）名称中的第一个字符必须是字母、下线线；（的第一个字符必须是字母、下线线；（2 2）不能）不能用字母用字母C C、c c

16、、R R、r r定义名称；（定义名称；（3 3）名称不能）名称不能与单元格引用相同；（与单元格引用相同；（4 4）名称中不能出现空格；）名称中不能出现空格；（5 5）一个名称中最多）一个名称中最多255255个字符；（个字符；（6 6）名称可）名称可以包含大写和小写字母，在以包含大写和小写字母，在ExcelExcel中不区分大小中不区分大小写。写。返回目录返回目录4.6.4 名称的定义和引用名称的定义和引用步骤六：定义编辑公式在在G2G2单元格内输入单元格内输入“=语文语文+数学数学+外语外语”，则，则可以计算出该生的成绩，拖动填充柄到可以计算出该生的成绩，拖动填充柄到G16G16计计算其它学

17、生总分成绩。最终效果图如图算其它学生总分成绩。最终效果图如图4-724-72。图图4-724-72返回目录返回目录4.6.5 函数函数步骤一：计算总分打打 开开 4.6.44.6.4小小 节节 中中 制制 作作 好好 的的 案案 例例 文文 件件，将将G2:G16G2:G16数数据据删删除除，并并在在G2G2单单元元格格内内手手工工输输入入“=SUM(D2:F2)”=SUM(D2:F2)”，同同样样可可以以得得到到学学生生的的总总分分成成绩，如图绩，如图4-734-73所示。所示。图图4-734-73返回目录返回目录4.6.5 函数函数提示使使用用函函数数可可以以大大大大简简化化公公式式 ，并

18、并能能实实现现一一般般公公式式无无法法实实现现的的功功能能。典典型型的的函函数数的的一一般般结结构构为为：函函 数数 名名（参参 数数 1 1，参参 数数 2 2，）。“=SUM(D2:F2)”=SUM(D2:F2)”即即是是求求D2D2到到F2F2单单元元格格区区域域内内的数据的和。的数据的和。返回目录返回目录4.6.5 函数函数步骤二：计算平均值在在H1H1输输入入“平平均均值值”，选选择择H2H2单单元元格格。选选择择【插插入入】菜菜单单，点点击击【函函数数】按按钮钮，如如图图4-744-74所所示。示。图图4-744-74返回目录返回目录4.6.5 函数函数步骤三：在在弹弹出出的的“插

19、插入入函函数数”对对话话框框中中选选择择平平均均分分的的函数函数“AverageAverage（）（）”，如图，如图4-754-75所示所示 。图图4-754-75返回目录返回目录4.6.5 函数函数提示注意此时系统会自动将左侧的有数据的单元格注意此时系统会自动将左侧的有数据的单元格添加为计算参数，但在计算平均分时不应将总添加为计算参数，但在计算平均分时不应将总分计算在内，故应选择分计算在内，故应选择D2:F2D2:F2区域。区域。返回目录返回目录4.6.5 函数函数步骤四：在在弹弹出出的的“函函数数参参数数”对对话话框框中中设设置置相相应应的的区区域为域为“D2:F2“D2:F2“，如图，如

20、图4-764-76所示所示 。图图4-764-76返回目录返回目录4.6.5 函数函数提示：一般系统会自动添加参数，如果需要修改，可一般系统会自动添加参数，如果需要修改，可以单击旁边的区域按钮。以单击旁边的区域按钮。在函数参数对话框中单击左下角的在函数参数对话框中单击左下角的“有关该函有关该函数的帮助数的帮助”，可弹出函数的帮助信息。，可弹出函数的帮助信息。返回目录返回目录4.6.5 函数函数步骤步骤5 5：单击确定后，系统会自动计算出的平均值。拖动填充柄单击确定后，系统会自动计算出的平均值。拖动填充柄到到H16H16计算其它学生平均成绩。计算其它学生平均成绩。步骤步骤6 6：条件函数。在条件

21、函数。在I1I1单元格中输入单元格中输入“等级等级”，回车后，系统，回车后，系统会自动添加一列。会自动添加一列。步骤步骤7 7：光标定位在光标定位在I2I2单元格内，使用单元格内，使用“插入函数插入函数”按钮在其中按钮在其中插入插入IF()IF()函数，如图函数，如图4-774-77。返回目录返回目录4.6.5 函数函数步骤八：在在弹弹出出的的对对话话框框中中输输入入IFIF函函数数的的参参数数，如如图图4-784-78所所示示。光光标标定定位位在在第第一一个个参参数数，单单击击H2H2，并并输输入入“60”60”，即即意意味味着着平平均均分分大大于于6060分分为为判判断断条条件件，在在第第

22、二二个个和和第第三三个个参参数数中中输输入入“及及格格”和和“不不及及格格”，这这是是判判断断后后的的返返回回值。值。图图4-784-78返回目录返回目录4.6.5 函数函数步骤八：在在弹弹出出的的对对话话框框中中输输入入IFIF函函数数的的参参数数，如如图图4-784-78所所示示。光光标标定定位位在在第第一一个个参参数数，单单击击H2H2，并并输输入入“60”60”，即即意意味味着着平平均均分分大大于于6060分分为为判判断断条条件件，在在第第二二个个和和第第三三个个参参数数中中输输入入“及及格格”和和“不不及及格格”，这这是是判判断断后后的的返返回回值。值。图图4-784-78返回目录返

23、回目录4.6.5 函数函数提示：IFIF函数的第一个参数为判断的条件，第二个是函数的第一个参数为判断的条件，第二个是条件成立时的返回值，第三个为条件不成立时条件成立时的返回值，第三个为条件不成立时的返回值，可以省略不写。同样系统会的确定的返回值，可以省略不写。同样系统会的确定后自动计算该列的等级。后自动计算该列的等级。返回目录返回目录4.6.5 函数函数步骤九：插入日期公式将将光光标标定定位位于于H18H18，在在“公公式式”对对话话框框的的“函函数数库库”中中选选择择“TODAY()”TODAY()”函函数数，在在H18H18单单元元格格中插入当前日期中插入当前日期 。提示：在在ExcelE

24、xcel中，有些函数是不需要参数的，如中，有些函数是不需要参数的，如TODAYTODAY()()函数。一个函数可以不带参数；带一个参数；函数。一个函数可以不带参数；带一个参数；带固定数量的参数；带不确定数量的参数；含带固定数量的参数；带不确定数量的参数；含有可选参数。有可选参数。返回目录返回目录4.6.5 函数函数步骤十：最终效果返回目录返回目录4.6.6 案例总结案例总结在在ExcelExcel中使用公式和函数能够极大的提高数据计中使用公式和函数能够极大的提高数据计算速度。一个基本的算速度。一个基本的ExcelExcel公式主要包括运算符、公式主要包括运算符、单元格引用、值或字符串、工作表函

25、数以及它们单元格引用、值或字符串、工作表函数以及它们的参数。的参数。公式和函数可以被复制使用。首先选中需要复制公式和函数可以被复制使用。首先选中需要复制的单元格并复制，再单击目标单元格，选择性粘的单元格并复制，再单击目标单元格，选择性粘贴，在选项组中选择贴，在选项组中选择“公式公式”或或“公式和数字格公式和数字格式式”按钮即可。按钮即可。在在ExcelExcel中的函数库包括多种函数，用户可以选择中的函数库包括多种函数，用户可以选择相当的函数使用，在选择了一种函数后，系统会相当的函数使用，在选择了一种函数后，系统会在下方给出该函数的作用的语法规则。在下方给出该函数的作用的语法规则。返回目录返回目录4.6.6 案例总结案例总结函数说明Sum（）返回某一单元格区域中所有数字之和。Average（）返回参数的平均值（算术平均值）。Min（）返回一组值中的最小值。Max（）返回一组值中的最大值。Count（）返回包含数字的单元格的个数以及返回参数列表中的数字个数。利用函数 COUNT 可以计算单元格区域或数字数组中数字字段的输入项个数。Counta（）返回参数列表中非空值的单元格个数。利用函数 COUNTA 可以计算单元格区域或数组中包含数据的单元格个数If（）根据对指定的条件计算结果为 TRUE 或 FALSE，返回不同的结果。可以使用 IF 对数值和公式执行条件检测