第四章 Excel 2010的使用

第四章Excel2010的使用制作人：李龙龙、宋承继、李莹、刘引涛《计算机应用基础》系列课件诗有风雅颂，万物各有律；用其所长，顺其之美学习目标掌握统计函数等函数的使用方法及图表的操作2.1.掌握Excel中各种数据的输入方法及单元格的操作3.掌握分类汇总的使用方法及相应分析函数的使用能够熟练掌握Excel的数据分析功能并能熟练应用4.主要内容4.1Excel基础应用—制作“学生成绩表”4.2Excel综合应用—制作“成绩统计分析表”4.3Excel高级应用—连锁超市销售数据分析4.1Excel基础应用——制作“学生成绩表”4.1.1Word基础应用案例分析●1.任务的提出

学期结束时，班主任刘老师要为本班同学建立一张本学期学生学籍与成绩管理表。

困惑：如何按要求计算出每个学生的总分并按总分排名次，并计算出各科分数的平均分、各科的优秀率和及格率，对不及格的课程成绩加灰色图案显示，最后打印出来存档。

如何输入并美化成绩表？如何对成绩进行计算和处理？4.1.1Word基础应用案例分析学生成绩表●1.任务的提出4.1Excel基础应用——制作“学生成绩表”●2.解决方法应用Excel提供的“条件格式”功能，对不及格成绩显示特殊格式应用Excel提供的求和等函数与“数据排序”功能对成绩进行统计与排序应用Excel提供的RANK函数实现按总分排名次4.1.1Word基础应用案例分析4.1Excel基础应用——制作“学生成绩表”4.1.2相关知识点●1.

Excel2010的窗口组成4.1Excel基础应用——制作“学生成绩表”4.1.2相关知识点●2.

工作薄和工作表

在Excel中，一个Excel文件就是一个工作簿。工作簿是由多个工作表组成的，工作表是由一个个单元格组成的，单元格是组成工作簿的最小单位。

（1）账本——工作簿

所谓工作簿，是指用来存储并处理工作数据的文件。在一个工作簿中可以建立多个不同类型的工作表。

（2）账页——工作表

工作表是工作簿文件的一个组成部分。单击工作簿标签上的工作表名，可实现在同一工作簿文件中不同工作表之间的切换。

工作表默认是由1048576行和16384列所构成。4.1Excel基础应用——制作“学生成绩表”4.1.2相关知识点●3.

行、列和单元格4.1Excel基础应用——制作“学生成绩表”4.1.2相关知识点●4.

区域

区域的引用常用左上角、右下角单元格的引用来标志，中间用“：”间隔。引用多个区域用逗号隔开。如图所示数据系列引用可表示为B4、D4：H4、B6、D6：H6、B11、D11：H11。4.1Excel基础应用——制作“学生成绩表”4.1.2相关知识点●5.

常用数据类型及输入

在Excel中数据有多种类型，最常用的数据类型有文本型、数值型、日期型等。

文本型数据可以包括字母、数字、空格和符号，其对齐方式为左对齐；数值型数据包括0～9、()、+、−等符号，其对齐方式为右对齐。

快速输入有很多技巧，例如利用填充柄自动填充、自定义序列、按Ctrl+Enter键可以在不相邻的单元格中自动填充重复数据等。4.1Excel基础应用——制作“学生成绩表”4.1.2相关知识点●6.

单元格的格式化设置

单元格的格式化包括设置数据类型、单元格对齐方式、设置字体、设置单元格边框及底纹等。在“单元格格式设置”对话框中：“数字”选项卡，用于设置单元格中的不同数值类型。如：货币类型、文本类型、百分比类型等。“对齐”选项卡，用于设置单元格中数据的对齐方式。如：水平，垂直对齐、合并单元格、自动换行等。“字体”选项卡，用于设置单元格中数据的“字体”、“字形”、“字号”、“颜色”等。“边框”选项卡，用于设置单元格或表格的边框线。“图案”选项卡，用于设置单元格的底纹。4.1Excel基础应用——制作“学生成绩表”4.1.2相关知识点●7.

多工作表的操作

多工作表的操作，包括对工作表的重命名，工作表之间的复制、移动、插入、删除等，对工作表操作时一定要先选定工作表标签。

工作表之间数据的复制、粘贴、单元格引用等，都是在工作表单元格之间进行的。4.1Excel基础应用——制作“学生成绩表”4.1.2相关知识点●8.

公式和函数的使用Excel中的“公式”是指在单元格中执行计算功能的等式，所有公式都必须以“=”号开头，“=”后面是参与计算的运算数和运算符。每一个运算数可以是常量、单元格或区域引用、单元格名称或函数等。Excel提供了四种类型的运算符：算术运算符、比较运算符、文本运算符和引用运算符。Excel函数是一种预定义的内置公式，它使用一些称为参数的特定数值按特定的顺序或结构进行计算，然后返回结果。4.1Excel基础应用——制作“学生成绩表”4.1.2相关知识点●8.

公式和函数的使用所有函数都包含三部分：函数名、参数和圆括号，以求和函数SUM来说明。SUM是函数名称，从名称大略可知该函数的功能及用途是求和。圆括号用来括起参数，在函数中圆括号是不可以省略的。参数是函数在计算时所必须使用的数据。函数的参数不仅是数字类型，它还可以是字符型、逻辑值或是单元格引用。

如：SUM(B1,C3)，SUM(D2:G2)等。圆括号用于括起参数SUM（number1,number2,...）函数名称各参数之间用逗号分隔4.1Excel基础应用——制作“学生成绩表”4.1.3实现方法●1．输入“姓名”到“计算机基础”列数据

输入如图所示的学生学籍管理表，其中需要不同的数据类型。任务1：建立学生成绩表4.1Excel基础应用——制作“学生成绩表”4.1.3实现方法●2．输入“学号”列数据——填充序列（1）使用鼠标填充“学号”列数据。任务1：建立学生成绩表4.1Excel基础应用——制作“学生成绩表”4.1.3实现方法●2．输入“学号”列数据——填充序列（2）使用命令填充“学号”列数据。任务1：建立学生成绩表4.1Excel基础应用——制作“学生成绩表”4.1.3实现方法●3．特别显示不及格的成绩——条件格式

操作实例：黄牌警告——特别显示不及格的成绩。选定E2:H11区域，对成绩小于60分的成绩加灰色图案。任务1：建立学生成绩表4.1Excel基础应用——制作“学生成绩表”4.1.3实现方法●3．特别显示不及格的成绩——条件格式

操作实例：黄牌警告——特别显示不及格的成绩。选定E2:H11区域，对成绩小于60分的成绩加灰色图案。任务1：建立学生成绩表4.1Excel基础应用——制作“学生成绩表”4.1.3实现方法●3．特别显示不及格的成绩——条件格式

操作实例：黄牌警告——特别显示不及格的成绩。选定E2:H11区域，对成绩小于60分的成绩加灰色图案。任务1：建立学生成绩表格式设置结果4.1Excel基础应用——制作“学生成绩表”4.1.3实现方法●4．求各种分数——应用公式和函数Excel中的“公式”是指在单元格中执行计算功能的等式。Excel中的所有公式都必须以“＝”号开头，若无等号，Excel将其理解为正文，所以“＝”是公式中绝对不可缺少的一个运算符。“＝”后面是参与计算的运算数和运算符，每一个运算数可以是常量、单元格或区域引用、单元格名称或函数等。Excel提供了四种类型的运算符：算术运算符、比较运算符、文本运算符和引用运算符。任务1：建立学生成绩表4.1Excel基础应用——制作“学生成绩表”4.1.3实现方法●4．求各种分数——应用公式和函数

操作实例：求总分。求E2、F2、G2、H2四个单元格内数字的和。

操作步骤如下：

方法一：将鼠标移到I2单元格，单击“公式”选项卡中的“自动求和”按钮“∑”计算出I2单元格的分数，最后选中I2单元格，将光标移至右下角，当光标变为小黑十字时双击，就可以求出该列的总分。

方法二：使用SUM函数。其语法格式为SUM(Ref)，此处Ref为参与计算的单元格区域。将鼠标移到I2单元格，然后输入=SUM(E2:H2)后按Enter键，即可求出E2、F2、G2、H2四个单元格内数字的和。任务1：建立学生成绩表4.1Excel基础应用——制作“学生成绩表”4.1.3实现方法●4．求各种分数——应用公式和函数操作实例：求平均分。用AVERAGE函数，其语法格式为AVERAGE(Ref)，此处Ref为参与计算的单元格区域。例如，AVERAGE(E2:E11)是求E2:E11区域内数字的平均值。操作步骤如下：①将鼠标移到E12单元格。②输入=AVERAGE(E2:E11)后回车，即可求出E2:E11区域内数字的平均值。任务1：建立学生成绩表4.1Excel基础应用——制作“学生成绩表”4.1.3实现方法●4．求各种分数——应用公式和函数操作实例：求最高分、最低分。用MAX和MIN函数，语法格式分别为MAX(Ref)和MIN(Ref)，例如上例中求E2:E11的最高分和最低分的方法为：MAX(E2:E11)和MIN(E2:E11)。操作步骤如下：①将鼠标移到E13单元格。②输入“=MAX(E2:E11)”后按Enter键，即可求出E2:E11区域内数字的最大值。求最低分操作类似。任务1：建立学生成绩表4.1Excel基础应用——制作“学生成绩表”4.1.3实现方法●5．单元格引用的方法——相对引用和绝对引用

单元格引用是指公式中指明的一个单元格或一组单元格。（1）直接引用单元格名称（2）引用当前表中的单元格地址①相对引用：用单元格的行号和列号表示单元格地址②绝对引用：在单元格的行号、列号前面各加一个“$”符号③混合引用：在公式中同时使用相对引用和绝对引用④在当前工作簿中引用其他工作表中的单元格：在该单元格坐标或名称前加上其所在的工作表名称和叹号分隔符“！”⑤引用其他工作簿中的单元格：[工作簿名]工作表名！单元格坐标（或名称）任务1：建立学生成绩表4.1Excel基础应用——制作“学生成绩表”

公式中对单元格的引用分为相对引用、绝对引用和混合引用。相对引用用“H2”这样的方式来引用单元格是相对引用。相对引用是指当公式在复制或移动时，公式中引用单元格的地址会随着移动的位置自动改变。绝对引用在行号和列号前均加上“$”，如：“$H$2”这样的方式来引用单元格是绝对引用。当公式在复制或移动时，公式中引用单元格的地址不会随着公式的位置改变。混合引用混合引用是指单元格地址中既有相对引用，也有绝对引用。“$H2”，表示具有绝对列和相对行，当公式在复制或移动时，保持列不变，而行变化；H$2”表示具有相对列和绝对行，当公式在复制或移动时，保持行不变，而列变化。4.1.3实现方法●5．单元格引用的方法——相对引用和绝对引用任务1：建立学生成绩表4.1Excel基础应用——制作“学生成绩表”

在相对引用中，当把一个含有单元格地址的公式移动或复制到一个新的位置时，公式中的单元格地址会随之发生变化。例如，图4-11中I2单元格中的公式“I2=E2+F2+G2+H2”。4.1.3实现方法●5．单元格引用的方法——相对引用和绝对引用任务1：建立学生成绩表4.1Excel基础应用——制作“学生成绩表”4.1.3实现方法●5．单元格引用的方法——相对引用和绝对引用任务1：建立学生成绩表

公式“I2=E2+F2+G2+H2”在被复制到G3单元格时会自动变为“I3=E3+F3+G3+H3”，从而得到了如图4-12所示的结果。4.1Excel基础应用——制作“学生成绩表”4.1.3实现方法●6．成绩排行榜——数据排序

（1）使用“数据”选项卡中的升序按钮或降序按钮直接排序①选定要排序的单元格区域。②在“数据”选项卡中，单击“排序和筛选”选项组中的升序按钮

或降序按钮

，即可完成选定单元格区域的数据排序。采用此方法排序，数据的排列顺序以所选范围的第一列“升序”或“降序”排列。任务1：建立学生成绩表4.1Excel基础应用——制作“学生成绩表”4.1.3实现方法●6．成绩排行榜——数据排序

（2）单击“排序和筛选”选项组中的“排序”按钮排序。任务1：建立学生成绩表4.1Excel基础应用——制作“学生成绩表”4.1.3实现方法●7．排列名次任务1：建立学生成绩表

在学生学籍管理表中，需要按学生的总分排名次，得到成绩排行榜。可以使用Excel提供的RANK函数完成。RANK函数是排定名次的函数，用于返回一个数值在一组数值中的排序，排序时不改变该数值原来的位置。语法格式为：RANK(number,ref,order)共包括三个参数，其中：Number

为需要找到排位的数字。Ref为数字列表数组或对数字列表的引用。Ref中的非数值型参数将被忽略。Order

为一数字，指明排位的方式。如果Order为0或省略，按照降序排列；如果Order不为零，按照升序排列。4.1Excel基础应用——制作“学生成绩表”4.1.3实现方法●7．排列名次任务1：建立学生成绩表4.1Excel基础应用——制作“学生成绩表”4.1.3实现方法●8．工作薄的保存任务1：建立学生成绩表单击快速访问工具栏中的“

”保存命令按钮，或选择“文件”→“保存”命令。4.1Excel基础应用——制作“学生成绩表”4.1.3实现方法●9．工作表的打印任务1：建立学生成绩表①设置打印范围。②进行页面设置。图4-17“页面设置”对话框4.1Excel基础应用——制作“学生成绩表”4.1.3实现方法●9．工作表的打印任务1：建立学生成绩表③打印预览和打印。4.1Excel基础应用——制作“学生成绩表”4.1.3实现方法●1．调整行高和列宽任务2：调整和美化成绩表（1）用鼠标调整行高和列宽（2）用选项卡命令调整行高和列宽

在“开始”选项卡中单击“单元格”选项组的“格式”下拉列表，选择“行高”命令。4.1Excel基础应用——制作“学生成绩表”4.1.3实现方法●2．增减行、列、单元格任务2：调整和美化成绩表（1）增加一行或一列——行或列的插入（2）插入空白的单元格（3）行、列和单元格的删除4.1Excel基础应用——制作“学生成绩表”4.1.3实现方法●3．美化表格——单元格格式的用法任务2：调整和美化成绩表（1）设置单元格字体格式4.1Excel基础应用——制作“学生成绩表”4.1.3实现方法●3．美化表格——单元格格式的用法任务2：调整和美化成绩表（2）设置数值显示格式

①直接输入法：在普通样式中，单元格默认的数字格式为G/通用，表示用哪一种格式输入数字，数字就会以该种格式显示。②通过“数字”选项卡输入：在“开始”选项卡中单击“数字”选项组的对话框启动器，显示“设置单元格格式”对话框的“数字”选项卡。③使用格式工具栏的数字格式按钮对数字进行格式设置。4.1Excel基础应用——制作“学生成绩表”4.1.3实现方法●3．美化表格——单元格格式的用法任务2：调整和美化成绩表（3）对齐单元格的数据

①设置单元格数据的水平对齐方式。②设置单元格数据的垂直对齐方式③将工作表中学生姓名用字是两个字的与3个字的左右对齐——“分散对齐”。4.1Excel基础应用——制作“学生成绩表”4.1.3实现方法●3．美化表格——单元格格式的用法任务2：调整和美化成绩表（4）改变单元格中文本的方向

单元格中的数据默认为“横排”但是数据除了横着放以外，还可以“竖着放”，也可以旋转。4.1Excel基础应用——制作“学生成绩表”4.1.3实现方法●3．美化表格——单元格格式的用法任务2：调整和美化成绩表（5）标题处理——合并单元格与跨列居中4.1Excel基础应用——制作“学生成绩表”4.1.3实现方法●3．美化表格——单元格格式的用法任务2：调整和美化成绩表（6）为单元格设置边框和底纹

为单元格加上边框：①选择要添加边框的单元格或区域。②在“开始”选项卡中单击“单元格”选项组的“格式”按钮，从下拉列表中选择“设置单元格格式”命令，单击“边框”选项卡。③选择边框、线条样式和颜色。

给单元格设置图案和底纹：①选择需要格式化的单元格或区域。②在“开始”选项卡中单击“单元格”选项组的“格式”按钮，从下拉列表中选择“设置单元格格式”命令，单击“填充”选项卡。③从“颜色”和“图案”调色板中选色颜色和图案，“示例”框将显示设置情况。4.1Excel基础应用——制作“学生成绩表”4.1.4案例总结本案例主要通过3个工作任务介绍了工作表的操作。Excel中有很多快速输入数据的技巧，熟练掌握这些技巧可以提高输入速度。在输入数据时，要注意数据单元格的数据分类。Excel中对工作表的格式化操作包括：工作表中各种类型数据的格式化、字体格式、行高和列宽、数据的对齐方式、表格的边框和底纹等。4.1Excel基础应用——制作“学生成绩表”4.1.3实现方法●4．使用条件格式——让行列更清晰任务2：调整和美化成绩表给单元格逐行设置不同的底纹颜色①选中数据区中所有的单元格。

②在“开始”选项卡中单击“样式”选项组的“条件格式”按钮，选择“新建规则”命令，在打开的对话框中，选择规则类型为“使用公式确定要设置格式的单元格”，然后在下方的输入栏中输入公式：=MOD(ROW(),2)。③

单击下方的“格式”按钮，在弹出的“设置单元格格式”对话框中单击“填充”选项卡，指定单元格的填充颜色，并选择“边框”选项卡，为单元格加上外边框。

④最后连续单击“确定”按钮。4.1Excel基础应用——制作“学生成绩表”4.1.3实现方法任务3：多工作表操作一个工作簿中可以包含多张工作表。文档窗口下方的Sheet1、Sheet2、Sheet3为工作表的名称。文档窗口只能显示一张工作表的内容，称为当前工作表。当前工作表的名称下有下划线，且为白色填充。单击名称标签可切换工作表，单击工作表切换按钮可显示被隐藏的工作表名称。对于工作表，可以进行更改名称、删除未用的工作表、工作表的移动和复制、插入空工作表等操作，方法为：单击右键，从快捷菜单中选择相应的命令。4.1Excel基础应用——制作“学生成绩表”4.1.3实现方法任务3：多工作表操作在工作表的计算操作中，需要用到同一工作簿文件中其他工作表中的数据时，可在公式中引用其他工作表中的单元格。引用格式如下：〈工作表名〉！〈单元格地址〉。例如，若想在工作表Sheet1的D6单元格中放入C6与工作表Sheet2中单元格B2的乘积，可在Sheet1的D6单元格中输入“=C6*Sheet2！B2”。此外，也可以引用不同工作簿文件中的单元格，此时需注明工作簿的文件名。4.1Excel基础应用——制作“学生成绩表”4.1.4案例总结在进行公式和函数计算时，要熟悉公式的输入规则、函数的输入方法、单元格的3种引用方式和跨工作表和工作簿的单元格引用等，还应注意以下几点：①公式是对单元格中数据进行计算的等式，公式必须以“=”开始。②函数的引用形式为：函数名(参数1，参数2，…)，参数之间必须用逗号隔开。如果单独使用函数，需在函数名称前面输入“=”构成公式。③公式中的单元格引用可分为相对引用、绝对引用和混合引用3种。要特别注意这3种引用的适用场合。4.1Excel基础应用——制作“学生成绩表”4.2.1“成绩统计分析表”案例分析●1.任务的提出

在上一案例中，刘老师已经顺利完成了“学生成绩表”的制作，现在他要根据“学生成绩表”中的数据，制作“成绩统计分析表”、“各科成绩等级表”此外还要根据“成绩统计分析表”中的数据制作“成绩统计分析图”，以便对全班的成绩进行各种统计分析。当前工作表中没有计算所需要的源数据，如何分析和统计数据？如何快速地将每位学生的分数换算成相应的成绩等级？能否快速准确地统计人数

4.2Excel综合应用——制作“成绩统计分析表”困惑4.2.1“成绩统计分析表”案例分析●1.任务的提出

4.2Excel综合应用——制作“成绩统计分析表”4.2.1“成绩统计分析表”案例分析●2.解决方法要引用其他工作表的数据，只要从源数据所在工作表中选择数据区域即可统计人数，可以使用COUNT、COUNTIF等函数来完成制作“各科成绩等级表”则必须使用IF函数

4.2Excel综合应用——制作“成绩统计分析表”4.2.2相关知识点●1.函数与函数语法函数是一种预先定义好的、经常使用的内置公式。Excel提供了很多内部函数，用户需要时，可按照函数的格式直接引用。函数由函数名和参数组成，其形式为：

函数名（参数1，参数2，…）

①函数名可以大写也可以小写，当有两个以上的参数时，参数之间要用逗号隔开。例如，函数SUM（10，20，30），其中SUM是函数名，表示函数的功能，10、20、30是参数，表示函数运算的数据。②参数可以是数字、文本、逻辑值、数组或单元格引用。

4.2Excel综合应用——制作“成绩统计分析表”4.2.2相关知识点●2.数学与三角函数（1）取整函数INT格式：INT（number）功能：将数值向下取整为最接近的整数。（2）取余函数MOD格式：MOD（number,divisor）功能：返回两数相除后的余数，结果正负号与除数相同。参数：number为被除数，divisor为除数，若为0，则返回#DIV/0!。（3）四舍五入函数ROUND格式：ROUND(number,num_digits)功能：按指定位数对数值进行四舍五入。参数：number为需要四舍五入的数字，num_digits为指定的位数，按此位数进行四舍五入。

4.2Excel综合应用——制作“成绩统计分析表”4.2.2相关知识点●3.统计函数（1）求和函数格式：SUM(number1,number2,…)功能：返回参数列表中所有数值之和。参数：number1,number2,…为1～30个需求和的参数，可为数字、文本方式的数字、文本、逻辑值、单元格引用或单元格区域的引用等。（2）求平均函数AVERAGE格式：AVERAGE（number1,number2,…）功能：返回参数列表中所有数值的平均值。（3）计数函数COUNT格式：COUNT（number1,number2,…）功能：返回参数列表中数字的个数。

4.2Excel综合应用——制作“成绩统计分析表”4.2.2相关知识点●3.统计函数（4）COUNTA（number1,number2,…）格式：COUNTA功能：返回参数组中非空值的数目。利用函数COUNTA可以计算数组或单元格区域中数据项的个数。（5）最大值函数MAX格式：MAX（number1，number2，…）功能：返回一组数中的最大值，忽略逻辑值及文本字符。（6）最小值函数MIN格式：MIN（number1，number2，…）功能：返回一组数中的最小值，忽略逻辑值及文本字符。

4.2Excel综合应用——制作“成绩统计分析表”4.2.2相关知识点●4.逻辑函数（1）条件函数IF格式：IF（logical_test,value_if_true,value_if_false）功能：执行真假值判断，根据对指定条件进行逻辑判断的真假而返回不同的结果。参数：logical_test为条件表达式；value_if_true是logical_test为true时的返回值；value_if_false是logical_test为false时的返回值。如：IF（A10﹤﹦100，"正常"，"超支"）

4.2Excel综合应用——制作“成绩统计分析表”4.2.2相关知识点●4.逻辑函数（1）条件函数IF【例4-4】

根据学生成绩来确定A、B、C、D、E五个等级，如表4-1所示。此时，可使用下列嵌套IF函数：IF（成绩﹥89,"A",IF（成绩﹥79,"B",IF（成绩﹥69,"C",IF（成绩﹥59,"D","E"））））。

4.2Excel综合应用——制作“成绩统计分析表”4.2.2相关知识点●4.逻辑函数（2）取反函数NOT格式：NOT（logical）功能：对参数值求反。当要确保一个值不等于某一特定值时，可以使用NOT函数。参数：logical为逻辑值的常量、变量或公式。（3）真值函数TRUE格式：TURE()功能：返回逻辑真值。（4）假值函数FALSE格式：FALSE()功能：返回逻辑假值。

4.2Excel综合应用——制作“成绩统计分析表”4.2.2相关知识点●4.逻辑函数（5）与函数AND格式：AND（logical1,logical2,…）功能：当参数的逻辑值为真时返回TRUE；只要一个参数的逻辑值为假即返回FALSE。参数：logical1,logical2,…表示待检测的1～30个条件值，各条件值或为TRUE，或为FALSE。（6）或函数OR格式：OR(logical1,logical2,…)功能：在其参数组中，任何一个参数逻辑值为TRUE，即返回TRUE。参数：logical1,logical2,…为1～30个要测试的条件，这些条件的结果是TRUE或FALSE。

4.2Excel综合应用——制作“成绩统计分析表”4.2.2相关知识点●5.数据库函数常用的数据库函数有：

DSUM（database,field,criteria）：对数据库中的某个字段的数字求和。

DAVERAGE（database,field,criteria）：对数据库中的某个字段的数字求平均值。

DCOUNT（database,field,criteria）：统计数据库中的某个字段的数字个数。

DMAX（database,field,criteria）：求数据库中的某个字段的最大数。

DMIN（database,field,criteria）：求数据库中的某个字段的最小数。参数：

Database为包含数据清单的区域，必须包括字段名行。

Field为要操作的字段或字段名所在的单元格引用，包括字段名（外加西文引号）；要操作的字段相对数据清单第一列的偏移量。

Criteria为包含条件的单元格区域。条件区域的建立同高级筛选。

4.2Excel综合应用——制作“成绩统计分析表”4.2.2相关知识点●6.日期与时间函数（1）日期函数DATE格式：DATE（year,month,day）功能：返回某一指定日期的序列数。（2）取年份函数YEAR格式：YEAR（serial_number）功能：返回日期序列数所代表的年份（1900～9999）。（3）取月份函数MONTH格式：MONTH（serial_number）功能：将序列数转换为对应的月份数。（4）取日函数DAY格式：DAY（serial_number）功能：将序列数转换为对应的日。

4.2Excel综合应用——制作“成绩统计分析表”4.2.2相关知识点●6.日期与时间函数（5）取日期函数TODAY格式：TODAY()功能：返回计算机系统内部时钟的当前日期。（6）取当前时间函数NOW格式：NOW()功能：返回计算机系统内部时钟的当前日期和时间。（7）取指定时间函数TIME格式：TIME（hour,minute,second）功能：返回指定时间的系列号，系列号为0～0.99999999的小数。

4.2Excel综合应用——制作“成绩统计分析表”4.2.2相关知识点●7.图表Excel能将工作表中的数据以各种统计图表的形式显示，使得数据更加直观、易懂。Excel提供了11种标准图表类型。

4.2Excel综合应用——制作“成绩统计分析表”4.2.3实现方法●1.求及格率、优秀率（1）E3:E12中是本班的大学英语成绩，求英语的及格率。操作步骤如下：①将鼠标移到E16单元格，并输入公式=COUNTIF(E3:E12,">=60")/COUNT(E3:E12)后回车。②把鼠标指针移动到填充柄上按下鼠标左键向右拖动鼠标即可算出其他课程的及格率。③在“开始”选项卡中单击“数字”选项组中的“百分比样式”按钮，将及格率设置成百分比样式，单击“数字”选项组中的“增加小数位数”按钮，将及格率保留2位小数。任务1：建立成绩统计分析表

4.2Excel综合应用——制作“成绩统计分析表”4.2.3实现方法●1.求及格率、优秀率（2）E3:E12中是本班的大学英语成绩，求英语的优秀率。操作步骤如下：①将鼠标移到E17单元格，并输入公式=COUNTIF(E3:E12,">=90")/COUNT(E3:E12)后回车。②把鼠标指针移动到填充柄上按下鼠标左键向右拖动鼠标即可算出其他课程的优秀率和及格率。③在“开始”选项卡中单击“数字”选项组中的“百分比样式”按钮，将优秀率设置成百分比样式，单击“数字”选项组中的的“增加小数位数”按钮，将优秀率保留2位小数。任务1：建立成绩统计分析表

4.2Excel综合应用——制作“成绩统计分析表”4.2.3实现方法●2.统计各分数段的人数①在“成绩统计分析表”中，选择目标单元格E18（对应“90～100人数”）。②在编辑栏处输入“=COUNTIF(E3:E12,">=90")”，按Enter键确认。③在“成绩统计分析表”中，选择目标单元格E19（对应“80～89人数”）。④在编辑栏处输入“=COUNTIF(E3:E12,">=80")-E18”，按Enter键确认。⑤在“成绩统计分析表”中，选择目标单元格E20（对应“70～79人数”）。⑥在编辑栏处输入“=COUNTIF(E3:E12,">=70")-E18-E19”，按Enter键确认。⑦在“成绩统计分析表”中，选择目标单元格E21（对应“60～69人数”）。⑧在编辑栏处输入“=COUNTIF(E3:E12,">=60")-E18-E19-E20”，按Enter键确认。⑨在“成绩统计分析表”中，选择目标单元格E22（对应“0～59人数”）。⑩在编辑栏处输入“=COUNTIF(E3:E12,"<60")”，按Enter键确认。任务1：建立成绩统计分析表

4.2Excel综合应用——制作“成绩统计分析表”4.2.3实现方法任务1：建立成绩统计分析表统计分析结果：

4.2Excel综合应用——制作“成绩统计分析表”4.2.3实现方法●1.单元格数据的删除与清除操作实例29：将“成绩统计分析表”工作表复制一份，并将复制后的工作表更名为“各科等级表”。在“各科等级表”中清除“大学英语”、“高等数学”、“大学语文”及“计算机应用”列中的分数内容，并清除总分、“名次”列的所有属性；删除“平均分”、“最高分”、“最低分”等下面各行的所有单元格区域。方法：（1）在“开始”选项卡中单击“编辑”选项组中的“清除”按钮，在下拉列表中选择“清除内容”命令，或按Del键，只清除选定单元格的内容，而保留了单元格的格式。（2）在“开始”选项卡中单击“编辑”选项组中的“清除”按钮，在下拉列表中选择“全部清除”命令，此时这两列中的内容和格式全部被清除。任务2：用IF函数制作“各科等级表”

4.2Excel综合应用——制作“成绩统计分析表”4.2.3实现方法●2.条件函数IF的使用条件函数IF最多可以嵌套7层，用value_if_true及value_if_false参数可以构造复杂的检测条件。现在以“根据学生分数评定成绩等级”为例，说明IF嵌套函数的使用方法。分数与等级的对应关系如表4-2所示。任务2：用IF函数制作“各科等级表”

4.2Excel综合应用——制作“成绩统计分析表”4.2.3实现方法●2.条件函数IF的使用操作实例30：按表4-1所示的分数与等级的对应关系，利用IF嵌套函数根据“成绩统计分析表”中的大学英语、高等数学、大学语文、计算机基础4门课程的分数，在“各科等级表”的对应科目中设置相应的等级。操作步骤如下：①在“各科等级表”工作表中，选择目标单元格E3。②在E3中输入“=IF('成绩统计分析表'!E3>=90,"优秀",IF('成绩统计分析表'!E3>=80,"良好",IF('成绩统计分析表'!E3>=70,"中等",IF('成绩统计分析表'!E3>=60,"及格","不及格"))))”，其中第一个IF函数中value_if_false参数共嵌套了4层IF函数，从第一层到第四层的输入情况如图4-33所示。任务2：用IF函数制作“各科等级表”

4.2Excel综合应用——制作“成绩统计分析表”4.2.3实现方法任务3：分类汇总在分类汇总框中，单击“全部删除”命令按钮可取消分类汇总的结果。

4.2Excel综合应用——制作“成绩统计分析表”4.2.3实现方法●2.条件函数IF的使用任务2：用IF函数制作“各科等级表”③单击“确定”按钮后，即可设置“李丽丽的大学英语”课程所对应的等级。

4.2Excel综合应用——制作“成绩统计分析表”4.2.3实现方法●2.条件函数IF的使用④使用公式填充方式，填充至H3处，即可得到李丽丽的高等数学、大学语文、计算机基础3门课程所对应的等级。⑤选定区域E3:H3，拖动填充手柄至E12:H12处，即可得到其他同学的大学英语、高等数学、大学语文、计算机基础4门课程所对应的等级。任务2：用IF函数制作“各科等级表”

4.2Excel综合应用——制作“成绩统计分析表”4.2.3实现方法Excel提供的分类汇总功能，即对所有具有相同值的行进行求和、计数、求均值等计算。分类汇总时必须先按所依据的列进行排序。操作实例31：为了比较同学中党员与非党员的学习情况，现要求根据是否为党员对大学英语、高等数学、大学语文、计算机基础4门课程和总分这五项进行汇总（即求这五项的平均成绩）。①在成绩统计分析表中，按“是否党员”列排序。②选择“数据”→“分类汇总”命令，在分类汇总对话框（见图4-34）中指定分类字段为“是否党员”、汇总方式为“平均值”、选定的汇总项为“大学英语”、“高等数学”、“大学语文”、“计算机基础”、“总分”。单击“确定”按钮，分类汇总结果如图4-35所示。任务3：分类汇总

4.2Excel综合应用——制作“成绩统计分析表”4.2.3实现方法任务3：分类汇总

4.2Excel综合应用——制作“成绩统计分析表”4.2.3实现方法任务4：制作成绩统计分析图Excel中的图表分两种，一种是嵌入式图表，它和创建图表的数据源放置在同一张工作表中，打印时也同时打印；另一种是独立图表，它是一张独立的图表工作表，打印时也将与数据表分开打印。图表的生成有两种途径：利用图表工具生成图表或直接按F11键快速创建图表。

4.2Excel综合应用——制作“成绩统计分析表”4.2.3实现方法任务4：制作成绩统计分析图●1.利用图表工具生成图表操作步骤如下：

（1）选定需生成图表的数据区域。

4.2Excel综合应用——制作“成绩统计分析表”4.2.3实现方法任务4：制作成绩统计分析图●1.利用图表工具生成图表操作步骤如下：

（2）设置图表的类型。

打开“图表”对话框从中选择所需图表类型。

4.2Excel综合应用——制作“成绩统计分析表”4.2.3实现方法任务4：制作成绩统计分析图●1.利用图表工具生成图表操作步骤如下：

（3）设置图表数据来源。

4.2Excel综合应用——制作“成绩统计分析表”4.2.3实现方法任务4：制作成绩统计分析图●1.利用图表工具生成图表操作步骤如下：

（4）设置图表选项。

在“图表工具/布局”选项卡中，通过“标签”、“坐标轴”、“背景”选项组中的按钮或命令，用户可以设置一些数据系列以外的选项，包括可对图表标题、坐标轴标题、图例、数据标签、坐标轴、网格线、绘图区等，如图4-39所示。图4-39“图表工具/布局”选项卡

4.2Excel综合应用——制作“成绩统计分析表”4.2.3实现方法任务4：制作成绩统计分析图●1.利用图表工具生成图表操作步骤如下：

（5）设置图表位置。若选取“新工作表”，则工作簿会插入一张新的工作表来存放图表，图4-40中“新工作表”右栏可设置工作表的标志名称。若选取“对象位于”，表示要将图表放在现有的工作表中，成为工作表中的一个图表对象。可在右侧的下拉列表窗口中选取欲存到哪一个工作表，结果如图4-41所示。

4.2Excel综合应用——制作“成绩统计分析表”4.2.3实现方法任务4：制作成绩统计分析图●1.利用图表工具生成图表操作步骤如下：

（5）设置图表位置。

4.2Excel综合应用——制作“成绩统计分析表”4.2.3实现方法任务4：制作成绩统计分析图●1.利用图表工具生成图表操作步骤如下：

（5）设置图表位置。本例选取“新工作表”，转换成独立图表放在Chart1中，结果如图4-42所示。图4-42转换成独立图表

4.2Excel综合应用——制作“成绩统计分析表”4.2.3实现方法任务4：制作成绩统计分析图●2.利用快捷键F11快速建立图表接续上例，选择数据区域B2、E2:H2、B18:B22、E18:H22，按F11键，则Excel会以默认的图表类型（柱状图）快速产生一份图表文件，其结果如图4-42所示。

4.2Excel综合应用——制作“成绩统计分析表”4.2.3实现方法任务4：制作成绩统计分析图●3.格式化图表图表建立完成后，如果显示的效果不太美观，可以对图表的外观进行适当地格式化，即对图表的各个对象进行一些必要的修饰，使其更协调、更美观。因此，在格式化图表之前，必须先熟悉图表的组成以及选择图表对象的方法。操作实例33：将操作实例34中生成的“成绩统计分析图”中的各图表对象进行格式化设置。图表的组成及各对象的名称：图表区、绘图区、图表标题、数据轴标题、分类轴标题、图例等，如图4-43所示。

4.2Excel综合应用——制作“成绩统计分析表”4.2.3实现方法任务4：制作成绩统计分析图●3.格式化图表

4.2Excel综合应用——制作“成绩统计分析表”4.2.3实现方法任务4：制作成绩统计分析图●3.格式化图表选择图表对象进行格式化设置，通常可采用以下4种方法：①双击图表对象，直接打开格式设置对话框，这种方法最方便、快捷，也最常用。②用鼠标指向图表对象，单击鼠标右键，从弹出的快捷菜单中选择格式设置命令。③选定图表对象，此时“图表工具/格式”选项卡中会出现相应的格式设置命令。

4.2Excel综合应用——制作“成绩统计分析表”4.2.3实现方法任务4：制作成绩统计分析图●3.格式化图表④在“图表工具/格式”或“图表工具/布局”选项卡的“当前所选内容”选项组（见图4-44）中，单击“图表元素”下拉列表中选择图表对象，再单击下方的“设置所选内容格式”按钮，打开对象格式设置对话框。这种方法对于一些不太容易从图表中直接选择的对象，如网格线、分类轴等的选择非常有效。

4.2Excel综合应用——制作“成绩统计分析表”4.2.3实现方法任务4：制作成绩统计分析图●4.修改图表（1）更改图表类型操作实例：把如图4-41所示的柱形图改用“（XY）散点图”来表示成绩统计图。操作步骤如下：①在条形图的图表区内单击鼠标左键，选取条形图对象，此时条形图周围会出现8个控点。②单击“图表工具/设计”选项卡的“更改图表类型”按钮，从弹出的对话框中选取要变换的图表类型，如“（XY）散点图”，则条形图就会变为“（XY）散点图”，如图4-45所示。

4.2Excel综合应用——制作“成绩统计分析表”4.2.3实现方法任务4：制作成绩统计分析图●4.修改图表（1）更改图表类型图4-45用“（XY）散点图”表示的成绩统计图

4.2Excel综合应用——制作“成绩统计分析表”4.2.3实现方法任务4：制作成绩统计分析图●4.修改图表（2）更改数据区域操作实例35：更改数据区域，只查看大学英语和高等数学两门课的情况。操作步骤如下：①单击“图表工具/设计”选项卡“数据”选项组中的“选择数据”按钮，弹出“选择源数据”对话框。②到工作表中重新选取数据范围，假定数据区域为B2、E2:F2、B18:B22、E18:F22，如图4-46所示。③单击“确定”按钮即完成更改，如图4-47所示。

4.2Excel综合应用——制作“成绩统计分析表”4.2.3实现方法任务4：制作成绩统计分析图●4.修改图表（2）更改数据区域图4-46更改数据区域为B2，E2:F2，B18:B22，E18:F22与“选择源数据”对话框

4.2Excel综合应用——制作“成绩统计分析表”4.2.3实现方法任务4：制作成绩统计分析图●4.修改图表（2）更改数据区域图4-47更改数据区域示意图

4.2Excel综合应用——制作“成绩统计分析表”4.2.4案例总结本案例主要通过统计函数COUNT、COUNTA、COUNTIF、条件函数IF的使用来介绍函数的用法，通过具体图表的创建、格式化、图表的修改来介绍图表的用法。1．几个统计函数的区别COUNT、COUNTA、COUNTIF函数用于统计指定范围内单元格的个数。

COUNT函数返回包含数字以及参数列表中数字型的单元格的个数。

COUNTA函数返回参数列表中非空值的单元格个数，单元格的类型不限。

COUNTIF函数返回指定区域内满足给定条件的单元格个数。在使用COUNTIF函数时应该注意，当公式需要复制时，如果参数Range的引用固定不变，通常应使用绝对引用或区域命名方式实现；如果参数Criteria是表达式或字，必须在两边加上西文双引号。

4.2Excel综合应用——制作“成绩统计分析表”4.2.4案例总结2．条件函数IF条件函数IF用于判断给出的条件是否满足，如果满足返回逻辑值为真时的值，不满足则返回逻辑值为假时的值。当逻辑判断给出的条件多于两个时，通常采用IF函数嵌套，即将一个IF函数的返回值作为另一个IF函数的参数。3．图表图表比数据更易于表达数据之间的关系以及数据变化的趋势。对图表的操作主要包括：创建各种类型的图表、图表的修改、图表的格式化等。制作图表时，应了解表现不同的数据关系时如何选择合适的图表类型，特别要注意正确选择源数据。图表既可以插入到工作表中，生成嵌入图表，也可以生成一张单独的工作表。在对图表进行格式化时，要注意正确选择图表中的对象，不同对象的格式化设置选项是不同的。

4.2Excel综合应用——制作“成绩统计分析表”4.3.1连锁超市销售案例分析●1.任务的提出小张和小李在华润万家连锁超市总公司的销售部实习，本周的工作任务是统计上周周六各超市饮料的销售情况并进行分析，具体要求是统计各连锁超市的销售额、毛利润、各区域的饮料销售情况和各区域销售最好的饮料。已制作工作表：“销售记录”工作表记录了上周周六各超市的销售情况；“饮料价格”工作表给出了每种饮料的“单位”、“进价”和“售价”；“超市”工作表给出了各超市的名称和各超市所在的地区。

4.3Excel高级应用——连锁超市销售数据分析4.3.1连锁超市销售案例分析●1.任务的提出图4-48各超市的销售记录图4-49饮料价格图4-50超市信息

4.3Excel高级应用——连锁超市销售数据分析4.3.1连锁超市销售案例分析●1.任务的提出困惑：（1）如何让Excel根据饮料名称就能自动查找该饮料的“单位”、“进价”和“售价”等信息，并引用到“销售记录”工作表的相应单元格中？（2）如何从很长的销售记录中能快速进行一些特别的查询操作？

4.3Excel高级应用——连锁超市销售数据分析4.3.1连锁超市销售案例分析●2.解决方法查找函数VLOOKUP能够在数据区域的第1列中查找指定的数值，并返回数据区域当前行中指定列处的数值统计各超市或各区的销售情况，则可以用分类汇总或数据透视表来实现进行数据查询可以对数据进行排序或利用自动筛选或高级筛选来实现

4.3Excel高级应用——连锁超市销售数据分析4.3.2相关知识点●1.VLOOKUP函数格式：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)功能：查找数据区域首列满足条件的元素，并返回数据区域当前行中指定列处的值。参数：lookup_value：查找的内容。

table_array：查找的区域。

col_index_num：查找区域中的第几列。

range_lookup：精确查找或模糊查找，FALSE表示是模糊查找；TRUE或忽略表示是精确查找。注意：要查找的对象（参数1）一定要定义在查找数据区域（参数2）的第l列。

4.3Excel高级应用——连锁超市销售数据分析4.3.2相关知识点●2.分类汇总分类汇总是指对工作表中的某一项数据进行分类，再对需要汇总的数据进行汇总计算。在分类汇总前要先对分类字段进行排序。●3.数据透视表数据透视表是一种交互式工作表，用于对现有工作表进行汇总和分析。创建数据透视表后，可以按不同的需要、依不同的关系来提取和组织数据。

4.3Excel高级应用——连锁超市销售数据分析4.3.2相关知识点●4.数据筛选数据筛选是指从数据库或数据清单众多的数据行中找出满足一定条件的几行或几列数据。数据筛选将把数据清单或数据库中所有不满足条件的记录行暂时隐藏起来，只显示那些满足条件的记录行。筛选前与筛选后的工作表都是同一个，筛选后只是把原工作表中不满足条件的记录行隐藏起来而已。Excel提供了2种不同的方式用于数据筛选，它们是自动筛选和高级筛选。自动筛选可以很轻松地显示数据表中满足条件的记录行，高级筛选能提供多条件的、复杂的条件查询。

4.3Excel高级应用——连锁超市销售数据分析4.3.2相关知识点●5.固定表头冻结窗格的功能是将某些行、列的位置固定，不随垂直或水平滚动条上滑块的移动而移动。该功能在编辑行、列数据很多的Excel工作表时很有用。例如，一张Excel工作表中，存放了4000多个地球土壤化学样品的30多种元素的含量分析结果。第一行作为标题行，为了查看方便，希望在移动垂直滚动条滑块时，第一行固定不动。这就需要使用冻结窗格功能固定表头。

4.3Excel高级应用——连锁超市销售数据分析4.3.3实现方法●1.创建“单位”的查找公式操作实例36：用VLOOKUP函数填写工作表中的“单位”。任务1：用VLOOKUP函数计算“单位”、“进价”和“售价”等

4.3Excel高级应用——连锁超市销售数据分析4.3.3实现方法●1.创建“单位”的查找公式操作实例36：用VLOOKUP函数填写工作表中的“单位”。任务1：用VLOOKUP函数计算“单位”、“进价”和“售价”等

4.3Excel高级应用——连锁超市销售数据分析4.3.3实现方法●2.使用复制公式的方式查找“进价”和“售价”操作实例37：用VLOOKUP函数填写操作实例38工作表中的“进价”和“售价”，并计算出“销售额”和“毛利润”。操作步骤如下：①使用复制公式的方式，从D3沿着列拖动时到D171（即最后一行），即可确定出“销售记录”工作表中各行所对应的“单位”。②按照与上面查找“单位”相同的方法，完成下面的内容：在G2单元格中输入公式：=VLOOKUP(D2,饮料价格!$B$2:$E$18,3,FALSE)，并用公式复制的方式沿着列拖动到最后一行。在H2单元格中输入公式：=VLOOKUP(D2,饮料价格!$B$2:$E$18,4,FALSE)，并用公式复制的方式沿着列拖动到最后一行。任务1：用VLOOKUP函数计算“单位”、“进价”和“售价”等

4.3Excel高级应用——连锁超市销售数据分析4.3.3实现方法●2.使用复制公式的方式查找“进价”和“售价”操作实例37：用VLOOKUP函数填写操作实例38工作表中的“进价”和“售价”，并计算出“销售额”和“毛利润”。操作步骤如下：③用公式计算“销售额”和“毛利润”两列的值。在I2单元格中输入公式：=H2*E2（即销售额=售价×数量），并用公式复制的方式沿着列拖动到最后一行。在J2单元格中输入公式：=(H2-G2)*E2（即毛利润=（售价-进价）×数量，并用公式复制的方式沿着列拖动到最后一行。最后，计算出结果的“销售记录”工作表，如图4-53所示。任务1：用VLOOKUP函数计算“单位”、“进价”和“售价”等

4.3Excel高级应用——连锁超市销售数据分析4.3.3实现方法任务1：用VLOOKUP函数计算“单位”、“进价”和“售价”等图4-53计算出结果的“销售记录”工作表

4.3Excel高级应用——连锁超市销售数据分析4.3.3实现方法操作实例38：续上例，用“分类汇总”统计上周六的销售额和毛利润。操作步骤如下：①建立表“销售记录”的两个副本“销售记录②”和“销售记录③”。②在“销售记录②”工作表中按“店名”排序。③在菜单栏中选择“数据”→“分类汇总”命令，打开“分类汇总”对话框，按“店名”分类，然后对销售额和毛利润进行分类汇总，如图4-54所示。任务2：统计各连锁超市和各个区中各种饮料的“销售额”和“毛利润”

4.3Excel高级应用——连锁超市销售数据分析4.3.3实现方法任务2：统计各连锁超市和各个区中各种饮料的“销售额”和“毛利润”

4.3Excel高级应用——连锁超市销售数据分析4.3.3实现方法任务2：统计各连锁超市和各个区中各种饮料的“销售额”和“毛利润”④单击“确定”按钮后显示出汇总结果，如图4-55所示。图4-55汇总结果

4.3Excel高级应用——连锁超市销售数据分析4.3.3实现方法任务3：用“数据透视表”分析各区各饮料的销售情况和畅销饮料操作实例39：用“数据透视表”统计各个区每种饮料的销售情况。操作步骤如下：①单击“销售记录”工作表数据清单中的任一单元格。②切换到“插入”选项卡，单击“表格”选项组中“数据透视表”按钮，打开如图4-56所示的“创建数据透视表”对话框。③在该对话框中选择要分析的数据，建立数据透视表的数据源区域，本例选择整个“销售记录”工作表。④选择放置数据透视表的位置。

4.3Excel高级应用——连锁超市销售数据分析4.3.3实现方法任务3：用“数据透视表”分析各区各饮料的销售情况和畅销饮料图4-56“创建数据透视表”对话框

4.3Excel高级应用——连锁超市销售数据分析4.3.3实现方法任务3：用“数据透视表”分析各区各饮料的销售情况和畅销饮料在“创建数据透视表”对话框中，单击“确定”按钮，即可在工作表中插入数据透视表，出现数据透视表区域，如图4-57所示，同时打开如图4-58所示“数据透视表