中职计算机应用基础第9章 公式和函数与数据管理课件

1、(中职）计算机应用基础第9章 公式和函数与数据管理ppt课件第9章 公式和函数与数据管理9.1 【案例27】使用公式计算“学生成绩表”学习目标打开“Excel工作薄”文件夹中的“学生成绩表”，如右上图所示，要求根据“平时”、“期中”、“期末”成绩以及相应的“比例系数”，计算“总评”成绩，使用公式进行计算，并显示出公式的计算方式，最终结果右下图所示。相关知识1输入公式2公式中的运算符和运算顺序3公式的显示和隐藏4相对引用5绝对引用6混合引用7快速改变引用类型8“A1”和“R1C1”引用方式9三维引用10引用其他工作簿中的单元格1. 输入公式 （1）直接输入公式 选中要输入公式的单元格键入“=”表

2、示开始输入公式键入包含要计算的单元格地址以及相应的操作符单击按Enter键或单击“编辑栏”中的“输入”按钮1. 输入公式（2）选择单元格地址输入公式选中要输入公式的单元格，键入“=”表示开始输入公式用鼠标单击要在公式中加入的单元格地址输入运算符和公式中的数字单击按Enter键或单击“编辑栏”中的“输入”按钮2. 公式中的运算符和运算顺序 公式中的运算符：算术运算符：+（加）、-（减）、*（乘）、/（除）、%（百分比）、（乘方）比较运算符：=（等于）、（大于）、=（大于等于）、=（小于等于）、“”（不等于）文本运算符：“&”，叫做“连字符”引用运算符：包括：区域、联合和交叉，分别以“：”、“，”

3、和空格表示运算顺序：当有多个运算符参加运算时，Excel按照下面的顺序进行运算：引用运算符（区域、联合、交叉）、负号（）、百分比（）、乘方（）、乘（*）和除（）、加（+）和减（-）、连接运算符（&）、比较运算符（=、=，=、）3. 公式的显示和隐藏（1）隐藏公式选定想隐藏公式的单元格区域，单击“格式”“单元格”菜单命令，调出“单元格格式”对话框中，单击“保护”选项卡选中“隐藏”复选框，单击“确定”按钮单击“工具”“保护”“保护工作表”菜单命令，调出“保护工作表”对话框，在“密码”框中输入密码，单击“确定”按钮3. 公式的显示和隐藏（2）取消隐藏公式单击“工具”“保护”“撤消工作表保护” 菜单命

4、令调出“撤消工作表保护”对话框单击“格式”“单元格”菜单命令，调出“单元格格式”对话框，单击“保护”选项卡。清除“隐藏”复选框4. 相对引用 Excel的相对引用可以使输入的公式重复用到其他单元格的计算中时，从而减少了繁琐的公式输入工作，在复制的过程中公式中单元格的地址自动发生了变化。在F4单元格中输入公式“=C4+D4+E4” 。将F4中的公式复制到单元格F5中，则公式变成“=C5+D5+E5”复制到I4单元格时，公式变成“=F4+G4+H4”5. 绝对引用 在单元格的引用过程中，希望公式复制到别的单元格时，公式中的单元格地址不随活动单元格发生变化，这时就要用到绝对引用。在Excel中在行号

5、和列号前加“$”表示绝对引用。在B1单元格中输入公式“=$A$1+$A$2”当把B1中的公式复制到B2中时，公式没有发生变化。将其复制到D3单元格时，也没有发生变化。6. 混合引用 在公式中用到单元格地址时，参数中行采用相对引用，列采用绝对引用 当公式单元因插入、复制等原因引起行、列地址变化时，公式中相对地址部分随公式发生变化，绝对地址不随公式发生变化。例如：在B2单元格中输入公式“=$A2+$A3”，当把B2中的公式复制到C2中时，公式为“=$A2+$A3” 7. 快速改变引用类型 使用F4键，可以快速地改变引用类型的输入方式选择单元格C3， 然后键入“=E5”。按F4键，编辑栏中的引用类型

6、变为“=$E$5”。再按一次F4键，将引用变为混合引用，此时列是相对的，行是绝对的，公式变为“=E$5”。第三次按F4键，将引用变为另一种混合引用，此时列是绝对的，行是相对的，公式变为“=$E5”。第四次按F4键，返回到原来的相对引用格式。如果在一个公式中有多个引用，则这种操作只对选中和引用的单元格起作用。8. A1和R1C1引用方式（1） “A1”引用方式 是以列号在前，行号在后来表示单元格的地址。这种引用是Excel默认的引用地址的方式 8. A1和R1C1引用方式（2） “R1C1”引用方式 相对引用的格式是：R数字C数字R后面的数字表示从当前活动单元格移动的行数，正数为向下移，负数为向

7、上移动。C后面的数字表示从当前活动单元格移动的列数，正数为向右移，负数为向左移动。例如：当前单元格是E6，R1C1表示E列右移一列，6行下移一行，这时它所表示的单元格是F7。8. A1和R1C1引用方式（2） “R1C1”引用方式绝对引用的格式是：R数字C数字。图9-1-19 使用“R1C1引用样式”R后面的数字表示引用单元格所在的行数，C后面的数字当引用单元格所在的列数。如R6C2表示无论活动单元格在哪里，它都要引用B列6行交叉处的单元格，即B6单元格，这种表示方式和$B$6的表示方式是相同的 8. A1和R1C1引用方式（2） “R1C1”引用方式选择“R1C1”方式的方法单击“工具”“选

8、项”菜单命令在调出的“选项”对话框中选择“常规”标签，在“设置”栏内选择“R1C1引用样式”复选框 9. 三维引用 在一个工作簿中从不同的工作表引用单元格 三维引用的一般格式为：“工作表名!:单元格地址”，工作表名后的“!”是系统自动加上的 10. 引用其他工作簿中的单元格 例如：在单元格“D5”中引用在目录“c:my documents”下的文件“xu.xls”中的“Sheet1”的“C4”单元格，则引用公式为：=c: my documents xu.xlsSheet1!C4 9.2 【案例28】对“学生成绩表”进行统计对“学生成绩表”中的数据进行统计，使用函数统计各项成绩的“最高分”、“最

9、低分”、“总分”、“平均分”、“优秀人数”和“优秀率”，并根据每个学生的总评成绩，给出“评价”，完成后的效果下图所示。学习目标1直接输入函数 2使用插入函数菜单命令3常用函数的格式4使用自动求和按钮相关知识1. 直接输入函数 选定要输入函数的单元格在编辑框中输入一个等号“=”输入函数本身，例如“SUM(B2:B6)” 按下Enter键或单击“编辑栏”上的“确认”按钮2. 使用插入函数菜单命令 选定要输入函数的单元格单击“插入”“函数”菜单命令调出“插入函数”对话框从“或选择类别”下拉列表框中选择要输入的函数分类，例如“常用函数”从“选择函数”列表框中选择所需要的函数，例如，选择求和函数“SUM

10、” 单击“确定”按钮，系统显示“函数参数”对话框，按要求输入相关参数3. 常用函数的格式 （1）SUM（）函数：求和函数。语法：SUM（Numberl，Number2, Numbern）其中Numberl，Number2, Numbern为1到n个需要求和的参数。功能：返回参数中所有数值之和。3. 常用函数的格式（2）AND（）函数：逻辑与函数。语法：AND(logical1，logical2，)。其中logical1，logical2， 表示待检测的 1 到 30 个条件值，各条件值可为 TRUE（真） 或 FALSE（假）。功能：所有参数的逻辑值为真时返回TRUE（真）；只要有一个参数的逻

11、辑值为假，则返回FALSE（假）。 3. 常用函数的格式（3）AVERAGE（）函数：平均值函数。语法：AVERAGE（Numberl，Number2，Numbern）其中Numberl，Number2，Numbern为1到n个需要求平均值的参数。功能：返回参数中所有数值的平均值。 3. 常用函数的格式（4）MAX（）函数：最大值函数。语法：MAX（Numberl，Number2，Numbern）其中Numberl，Number2，Numbern为1到n个需要求最大值的参数。功能： 返回参数中所有数值的最大值。3. 常用函数的格式（5）MIN（）函数：最小值函数。语法：MIN（Numberl，

12、Number2，Numbern）其中Numberl，Number2，Numbern为1到n个需要求最小值的参数。 功能：返回参数中所有数值的最小值。3. 常用函数的格式（6）IF（）函数：判断函数。语法：IF（logical-test,value-if-true,value-if-false）其中logical-test是任何计算结果为TRUE或FALSE的数值或表达式；value-if-true是logical-test为TRUE时函数的返回值，如果logical-test为TRUE时并且省略value-if-true，则返回TRUE；value-if-false是logical-test为

13、FALSE时函数的返回值；如果logical-test为FALSE时并且省略value-if-false，则返回FALSE。函数 IF 可以嵌套七层，用 value_if_false 及 value_if_true 参数可以构造复杂的检测条件。在计算参数 value_if_true 和 value_if_false 后，函数 IF 返回相应语句执行后的返回值。 功能：指定要执行的逻辑检验。3. 常用函数的格式（7）SUMIF（）函数：条件求和函数。语法：SUMIF（range,criteria,sum-range）其中range是用于条件判断的单元格区域；criteria为单元格区域求和的条件

14、；sum-range是需要求和的实际单元格。功能：将符合给定条件的若干单元格求和。3. 常用函数的格式（8）COUNT（）函数：计数函数。语法：COUNT（Numberl，Number2，Numbern）其中Numberl，Number2Numbern为1到n个参数，但只对数值类型的数据进行统计。功能：返回参数中的数值参数和包含数值参数的个数。3. 常用函数的格式（9）COUNTIF（）函数：条件计数函数。语法：COUNTIF (range,criteria) 其中range为需要计算其中满足条件的单元格数目的单元格区域。criteria确定哪些单元格将被计算在内的条件，其形式为数字、表达式或

15、文本。功能：计算满足给定条件的区间内的非空单元格个数。3. 常用函数的格式（10）DATE函数用途：返回代表特定日期的序列号。语法：DATE(year,month,day)参数：year为一到四位，根据使用的日期系统解释该参数。默认情况下，Excel for Windows 使用1900日期系统，而Excel for Macintosh 使用1904日期系统。Month 代表每年中月份的数字。如果所输入的月份大于12，将从指定年份的一月份执行加法运算。Day 代表在该月份中第几天的数字。如果 day 大于该月份的最大天数时，将从指定月份的第一天开始往上累加。注意：Excel按顺序的序列号保存日

16、期，这样就可以对其进行计算。如果工作簿使用的是1900日期系统，则Excel 会将1900年1月1日保存为序列号1。同理，会将2006年1月1日保存为序列号38718，因为该日期距离1900 年1月1日为38718天。 3. 常用函数的格式（11）YEAR函数用途：返回某日期的年份。其结果为1900到9999之间的一个整数。语法：YEAR(serial_number)参数：Serial_number 是一个日期值，其中包含要查找的年份。日期有多种输入方式：带引号的文本串（例如2006/01/30）、序列号（例如，如果使用1900日期系统则38747表示2006年1月30日）或其他公式或函数的结

17、果（例如 DATEVALUE(2006/1/30)）。 3. 常用函数的格式（12）MONTH函数用途：返回以序列号表示的日期中的月份，它是介于 1（一月）和12（十二月）之间的整数。语法：MONTH(serial_number)参数：Serial_number 表示一个日期值，其中包含着要查找的月份。日期输入方式也有多种（同YEAR函数的参数）。 3. 常用函数的格式（13）DAY函数用途：返回用序列号（整数1到31）表示的某日期的天数，用整数1到31表示。语法：DAY(serial_number)参数：Serial_number 是要查找的天数日期，日期输入方式也有多种（同YEAR函数的参

18、数）。 3. 常用函数的格式（14）TODAY函数用途：返回系统当前日期的序列号。参数：无语法：TODAY()说明：当重新打开文档时或工作表重新计算时，以获取新的日期。如昨天在工作表的一个单元格中输入了TODAY函数，显示的时间是2006-8-8，则今天再次打开该工作表时，该单元格中的时间将会是2006-8-9。 3. 常用函数的格式（15）NOW函数用途：返回当前日期和时间所对应的序列号。语法：NOW()参数：无说明：当重新打开文档时或工作表重新计算时，日期系统会自动转换以获取最新的系统日期和时间。3. 常用函数的格式（16）TIME函数用途：返回某一特定时间的小数值，它返回的小数值从0到0

19、.99999999 之间，代表0:00:00（12:00:00 AM）到23:59:59（11:59:59 PM）之间的时间。语法：TIME(hour,minute,second)参数：Hour是0到23之间的数，代表小时；Minute是0到59之间的数，代表分；Second 是0到59之间的数，代表秒。3. 常用函数的格式（17）HOUR函数用途：返回时间值的小时数。即介于0（12:00 AM）到23（11:00 PM） 之间的一个整数。语法：HOUR(serial_number)参数：Serial_number 表示一个时间值，其中包含着要返回的小时数。它有多种输入方式：带引号的文本串（如

20、6:45PM）、十进制数（如0.78125 表示6:45PM）或其他公式或函数的结果（如TIMEVALUE(6:45 PM)）。3. 常用函数的格式（18）MINUTE函数用途：返回时间值中的分钟，即介于0到59之间的一个整数。语法：MINUTE(serial_number)参数：Serial_number 是一个时间值，其中包含着要查找的分钟数。关于时间的输入方式见HOUR函数的参数Serial_number。 3. 常用函数的格式（19）SECOND函数用途：返回时间值的秒数（为0至59之间的一个整数）。语法：SECOND(serial_number)参数：Serial_number 表示

21、一个时间值，其中包含要查找的秒数。关于时间的输入方式见HOUR函数的参数Serial_number。实例：公式“=SECOND(3:30:26 PM)”返回26，公式“=SECOND(0.016)”返回2。 3. 常用函数的格式（20）WEEKDAY函数用途：返回某日期的星期数。在默认情况下，它的值为1(星期天)到7(星期六)之间的一个整数。语法：WEEKDAY(serial_number,return_type)参数：Serial_number 代表要查找的日期，或日期的序列号，以了解该日期为星期几；Return_type为确定返回值类型的数字，如下表所示。 4. 使用自动求和按钮在工作表窗

22、口中的常用工具栏中有一个“自动求和”按钮。 利用该按钮，可以对工作表中所设定的单元格自动求和。”自动求和”按钮实际上代表了工作表函数中的“SUM（ ）”函数，利用该函数可以将一个累加公式转换为一个简洁的公式。 9.3 【案例29】分析“学生成绩表”中的数据打开案例29的“学生成绩表”，按照图1所示的效果建立数据清单，要求插入 “性别”列，删除“比例系数”下面的内容，但要保留“总评”下面的数据，将该工作表命名为“数据清单”。将“数据清单”工作表复制一张备份工作表，命名为“分类汇总”，分别计算出男生和女生的“期中”、“期末”和“总评”成绩的平均分，效果如图2所示。学习目标（一）图1图2学习目标（二

23、）将“数据清单”工作表复制一张备份工作表，命名为“自动筛选”，筛选出“期末”成绩介于80分到90分之间的记录，效果如图3所示。将“数据清单”工作表复制一张备份工作表，命名为“高级筛选”，筛选出“期末”成绩大于80分记录，显示在数据下方，效果如图4所示。图3图41建立数据清单2简单排序3多列排序与自定义排序顺序4自动筛选5高级筛选6建立分类汇总相关知识1. 数据清单 是包含列标题的一组连续数据行的工作表，必须要由两个部分构成：表结构和纯数据。在Excel 2003中对数据清单执行查询、排序、汇总等操作时，自动将数据清单视为数据库，数据清单中的列是数据库中的字段；数据清单中的列标志是数据库中的字段

24、名称；数据清单中的每一行对应数据库中的一个记录。1. 数据清单创建数据清单遵守的规则同一个数据清单中列标题必须是唯一的列标题与纯数据之间不能用分隔线或空行分开同一列数据的类型、格式等应相同在一个工作表上避免建立多个数据清单尽量避免将关键数据放到数据清单的左右两侧纯数据区不允许出现空行在工作表的数据清单与其他数据之间至少留出一空白行或一空白列2. 简单排序 （1） Excel的默认顺序数值：数字是从最小负数到最大正数。日期和时间则是根据它们所对应的序数值排序。文字：文字和包括数字的文字排序次序为： 0 1 2 3 4 5 6 7 8 9（空格）!”# $&（）* + ， - ： ； ?“ ” A

25、 B C D E F GH IJ K LM N O P Q R S TU V WXYZ逻辑值：逻辑值FALSE在TRUE之前。错误值：Error values所有的错误值都是相等的空格：Blanks总是排在最后2. 简单排序（2）排序原则如果对某一列排序，那么在该列上有完全相同项的行将保持它们的原始次序隐藏行不会被移动，除非它们是分级显示的一部分如果按一列以上作排序，当“主要关键字”列中有完全相同项的行时，会根据指定的“次要关键字”列作排序。当“次要关键字”列中有完全相同项的行时，会根据指定的“第三关键字”列作排序。2. 简单排序（3）简单排序的方法 在常用工具栏中提供了两个排序按钮：“升序排

26、序”：（从小到大排序）和“降序排序” （从大到小排序）。在数据清单中单击某一字段名根据需要，可以单击“常用”工具栏中的“升序排序”或“降序排序”按钮3. 多列排序与自定义排序顺序（1）多列排序选择数据清单中的任一单元格（如果想对某个区域进行排序，则选择该区域）单击“数据”“排序”菜单命令，调出“排序”对话框单击“主要关键字”下拉列表框，从中选择想排序的字段名选择“升序”或“降序”单选项确定排序的方式如果要增加额外的排序序列，可以在“次要关键字”框中选择想排序的字段名。对于特别复杂的数据清单，还可以在“第三关键字”框中选择想排序的字段名为了防止数据清单的标题被加入到其余部分进行排序，可以选择“列

27、表”中的“有标题行”单选钮单击“确定”按钮3. 多列排序与自定义排序顺序（2）应用自定义排序顺序 选择数据清单中的任一单元格单击“数据”“排序”菜单命令，调出“排序”对话框单击“排序”对话框中的“选项”按钮，调出“排序选项”对话框，用鼠标单击“自定义排序次序”下拉列表框，从中选择任一序列作为排序依据。单击“确定”按钮返回到“排序”对话框中，再次单击“确定”按钮就按指定的排序方式进行排序4. 自动筛选 （1）进行自动筛选的方法选中整个数据清单单击“数据”“筛选”“自动筛选”菜单命令。在列标题的右边就会出现自动筛选箭头。单击字段名右边的下拉列表按钮4. 自动筛选（2）自动筛选前10个 进行自动筛选

28、，使自动筛选的下拉箭头出现在列标题的右侧，单击此箭头，出现下拉列表框。单击“前10个”选项，调出“自动筛选前10个”对话框在第1个下拉列表框中选择“最大”还是“最小”，在第2个下拉列表框中选择数字，这个数字在1500之间，在第3个下拉列表框中选择“项”还是“百分比”单击“确定”按钮4. 自动筛选（3）多个条件的筛选从多个下拉列表框中选择了条件后，这些被选中的条件具有“与”和“或”的关系如果对筛选所提供筛选条件不满意，则可单击“自定义”选项，调出“自定义自动筛选方式”对话框，从中选择所需要的条件，单击“确定”按钮，就可以完成条件的设置，同时完成筛选4. 自动筛选（4）移去筛选单击设定筛选条件的列

29、旁边的下拉列表按钮，从下拉列表中单击“全部”选项，即可移去列的筛选。单击“数据”“筛选”“全部显示”菜单命令，即可重新显示筛选数据清单中的所有行。5. 高级筛选 “数据区域”：执行高级筛选时，准备进行筛选的区域“条件区域”：筛选的条件写在一区域，“复制到”：筛选的结果放的区域5. 高级筛选进行高级筛选的步骤在工作表上远离筛选数据清单的区域建立“条件区域”，输入筛选的条件在数据清单中任意选定单元格单击“数据”“筛选”“高级筛选”菜单命令在“方式”栏中选定“将筛选结果复制到其他位置”单选钮。在“数据区域”框中，指定数据区域在“条件区域”框中，指定条件所写的区域，包括条件标记在“复制到”框中，指明将

30、筛选结果写在什么位置。单击“确定”按钮6. 建立分类汇总 将数据清单按要进行分类汇总的列进行排序单击数据清单中的某一数据，单击“数据” “分类汇总”菜单命令，调出“分类汇总”对话框。在“分类字段”下拉列表框中选择某一分类的关键字段。在“汇总方式”下拉列表框中选择汇总方式。在“选定汇总项”框中选择汇总项，可指定对其中哪些字段进行汇总可根据标出的功能选用对话框底部的三个可选项。单击“确定”按钮9.4 【案例30】建立“学生成绩表”图表打开案例29的“学生成绩表”，选中“数据清单”工作表，根据“姓名”和“总评”两列数据创建“簇状柱形图”，效果如右图所示。学习目标1建立图表2添加和删除图表中的数据3移

31、动、调整图表4改变图表类型5添加图表标题6设置图表中坐标轴格式7设置绘图区的背景图案8设置图表区格式9设置图例格式相关知识1. 建立图表 （1）用图表工具栏创建图表 “视图”“工具栏”“图表”菜单命令，调出图表工具栏选择用于创建图表的数据单击“图表”工具栏中的“图表类型”按钮右边的向下箭头选择所需要的图表类型，创建一个嵌入式图表单击图表，在图表四周出现8个黑色句柄，用鼠标拖动句柄可调整图表的大小，选中图表后可以移动图表到合适的位置。1. 建立图表 （2）使用“图表”向导创建图表 选择用于创建图表的数据单击常用工具栏中的“图表向导”按钮，或者选择“插入”“图表”菜单命令，调出“图表向导-4步骤之1-图表类型”对话框。2. 添加和删除图表中的数据（1）使用菜单添加图表数据 在工作表内输入要添加的数据，然后将其选定单击图表，使其处于选定状态单击“图表”“添加数据”菜单命令，调出“添加数据”对话框在“选定区域”文本框中输