计算机基础教案电子表格软件Excel

第5章

电子表格软件Excel20005.1Excel2000基本操作5.2公式与函数5.3数据的管理与分析5.4图表的制作5.5工作表的打印设置本章要点

Excel2000的基本概念与基本操作工作表中数据的输入方法与技巧表格的格式化单元格的引用公式与函数数据的排序、自动筛选与高级筛选数据的分类汇总与数据透视表图表的制作工作表的打印设置

5.1Excel2000基本操作

5.1.1Excel2000的启动与退出1.启动

（1）单击“开始”菜单→“程序”命令，从程序的级联菜单中选择“MicrosoftExcel2000”。

（2）双击桌面的Excel2000的快捷图标。2．退出

（1）单击“文件”→“退出”命令。（2）单击Excel窗口右上角的“关闭”按钮（）。（3）双击窗口左上角处的控制菜单图标（标题栏中最左端的Excel图标）。（4）单击控制菜单图标，在下拉菜单击选择“关闭”菜单。（5）按〈Alt+F4〉快捷菜单。

5.1.2Excel2000的基本概念

1.Excel2000的窗口组成

标题栏菜单栏“常用”工具栏格式工具栏状态栏工作表标签垂直滚动条水平滚动条行号列标工作表区编辑栏2．工作簿的概念工作簿文件的扩展名为“.xls”

。工作簿由一个至多个工作表组成，默认情况下有：Sheet1、Sheet2和Sheet3

。一个工作簿最多可以有255个工作表。3．工作表的概念一个工作表由许多单元格组成。工作表分成若干行和若干列，行号由数字1～65536表示，列标由字母A～Z、AA、AB、AC…AZ、BA、BB…IV表示，共256列。每个工作表最大可达256列×65536行。4．单元格的概念（1）单元格的基本表示方法是：列标+行号。如：B4、E10、AK16等。（2）对于同一工作簿中不同工作表中单元格的表示方法：工作表名！单元格名。如：Sheet2!H8。（3）对于不同工作簿中不同工作表中的单元格的表示方法：[工作簿名]工作表名！单元格。如：[Book2]Sheet3!C2、[工资表]Sheet2!F5等。

5．单元格区域的概念（1）连续区域的表示方法：单元格之间以英文状态下的冒号（:）分隔，如D5:F11等。（2）不连续区域的表示方法：单元格或单元格区域间用英文状态下的逗号（,）分隔，如A2,B4,D3:F10表示A2、B4单元格及D3:F10区域。

5.1.3工作簿的新建、打开、保存和关闭

1.新建工作簿

单击“常用”工具栏上的【新建】按钮。2.打开工作簿单击“常用”工具栏中的【打开】按钮。3.保存工作簿

单击“常用”工具栏中的【保存】按钮。4.关闭工作簿单击文档窗口右上角的【关闭】按钮。5.1.4工作表中有关选定及编辑操作1.单元格区域的选定

（1）单个单元格的选定。（2）连续单元格区域的选定。（3）选定非连续的单元格或单元格区域：按Ctrl键2.行列的选定

（1）整行（或整列）的选定。

（2）连续行（或连续列）的选定。

（3）不连续行（或列）的选定。3．整个工作表的选定用鼠标单击工作表左上角行号和列标交叉的灰色区域，可选定整个工作表的所有单元格。

使用〈Ctrl+A〉快捷键。4．取消选定使用〈Esc〉键或在任一单元格中单击。

5．单元格的插入、删除（1）插入。选择菜单“插入”→“单元格”命令，再在右图所示的对话框选择移动的方向。（2）删除。

选择要删除的单元格，选择菜单“编辑”→“删除”命令，在下图所示的对话框中选择移动的方向。6．行、列的插入、删除（1）插入。先选定所要插入位置后面的行（列），选择菜单“插入”→“行”（“列”）命令。选定多少行则在所选定的行前插入多少行。（2）删除。选定所要删除的行（列），选择菜单“编辑”→“删除”命令。

7．工作表的插入、重命名、删除、复制和移动（1）插入。

右击工作表标签，在弹出的快捷菜单中选择“插入”命令，然后选择“插入”对话框中的“工作表”图标，单击【确定】按钮。（2）删除。右击要删除的工作表标签，在弹出的快捷菜单中选择“删除”命令。（3）重命名。重命名工作表有以下两种方法：右击要重命名的工作表标签，在弹出的快捷菜单中选择“重命名”命令，然后输入新的工作表名。双击要重命名的工作表标签，直接输入新的工作表名。（4）移动。移动工作表有以下两种方法：直接拖动工作表标签至目的位置。右击要移动的工作表标签，在弹出的快捷菜单中选择“移动或复制工作表”命令，将弹出“移动或复制工作表”对话框，选择要移动到某个目标工作簿中及该工作簿的某个工作表之前。（5）复制。移动工作表有以下两种方法：按〈Ctrl〉键拖动工作表标签至目的位置。右击要移动的工作表标签，在弹出的快捷菜单中选择“移动或复制工作表”命令，将弹出“移动或复制工作表”对话框，勾选“建立副本”复选框，选择要移动到某个目标工作簿中及该工作簿的某个工作表之前。移动复制5.1.5工作表中数据的输入与数据清单1．输入数据

（1）字符型数据（文本数据）的输入。字符型数据可以是数字、空格和非数字字符（中、英文字符）的组合。一个单元格最多可以输入32767个英文字符。数字字符（格式为左对齐）输入有以下两种方法：单击英文状态下的单引号后输入数字，例如：’123。选择要输入数字字符的区域，选择菜单“格式”→“单元格”命令，在弹出对话框的“数字”选项卡中选“文本”，设置所选区域数据类型为文本型，再输入数字。

（2）数值型数据的输入。数值型数据是指由0～9和特殊字符构成的数字。负数输入：先键入负号再输入数字，或输入括号，在括号内输入数字。例如：－6的输入法：(6)或－6。

分数输入：先键入0和空格再输入分数，直接输入为日期，例如：1/2的输法：01/2。

（3）日期型数据的输入。

以分数形式输入：例如：1月2日：1/2

当前系统日期的输入：Ctrl+；

当前系统时间的输入：Ctrl+Shift+；2．快速输入技巧

（1）自动增1数据的输入。1）数值型数据自动增1：输入第一个数据后，按住Ctrl键不放拖动填充柄进行填充。2）字符型数据自动增1：输入第一个数据后，直接拖动填充柄进行填充。

（2）等差序列与等比序列的输入。选择菜单“编辑”→“填充”→“序列”命令，在序列对话框中进行设置，如下图所示。（3）相同数据的输入。对于数值型数据，输入第一个数据后直接拖动填充柄进行填充。对于数字字符数据，输入第一个数据后按住〈Ctrl〉键不放拖动填充柄进行填充，其它字符型数据直接拖动填充柄即可输入相同数据。输入第一个数据后，选择包括第一个数据在内要填充的单元格区域，选择菜单“编辑”→“填充”命令，在其下级菜单中选择相应的填充方式。选定要输入相同数据的单元格或单元格区域，在最后一个单元格中输入内容后按〈Ctrl+Enter〉键，则每个单元格将会输入相同的内容。

【例5-1】制作如下图所示的课程表。

要求：运用自动填充和相同数据输入中的最后一种方法建立课程表。注意斜线表头的绘制。

（4）自定义序列的输入。

1）选择菜单“工具”→“选项”命令，选择如下图所示对话框中的“自定义序列”选项卡。2）在对话框的“输入序列”列表框中分别输入序列的每一项后按〈Enter〉键，全部输入完后单击【添加】按钮，将所定义的序列添加到左边的“自定义序列”的列表中。3）也可先在单元格中输入序列，单击该对话框中的“导入序列所在单元格”右边的折叠按钮，选定序列所在的单元格区域，最后单击【导入】按钮将数据导入到“自定义序列”列表中。4）单击【确定】按钮，完成自定义序列的制作。3．数据清单输入数据记录单的具体操作步骤如下：

1）输入表格的标题和表头（即所有的字段名）内容。2）选定表格的字段名所在的单元格区域，或将活动单元格定位于字段名所在行的下一个相邻行（中间不能出现空行），选择菜单“数据”→“记录单”命令。3）在各字段右边的文本框中输入所对应的内容，每输入完一条记录后单击【新建】按钮或使用〈Alt+W〉快捷键新增一条记录。4）单击【关闭】按钮，退出对话框，关闭数据记录单。

未输入记录的记录单已输入记录的记录单（1）添加记录单击【新建】按钮可以添加一条新记录。（2）删除记录单击【删除】按钮将当前记录单中显示的记录删除。（3）浏览记录通过单击【上一条】按钮或【下一条】按钮浏览记录当前记录总记录（4）查找记录单击记录单中的条件按钮，则记录单如右图所示，在对应字段处输入条件，如在姓名右边输入“李乐乐”（条件还可以更详细），单击【下一条】按钮（往下查找）或【上一条】按钮（往上查找）即可。5.1.6工作表的格式化1．调整行高、列宽

（1）用鼠标拖曳法设置行高、列宽。

（2）用菜单精确设置行高、列宽。

选择菜单“格式”→“行”（“列”）→“行高”（“列宽”）命令。

在打开的对话框中，输入行高（列宽）值。（3）设置最合适的行高、列宽选择“格式”→“行”（“列”）→“最合适的行高（列宽）”命令。用鼠标双击所选行（列）标的分界线，可以快速调整最合适的行高（列宽）。2．设置数字格式选择菜单“格式”→“单元格”命令，在弹出的“单元格格式”对话框中设置数字格式。

3）单击格式工具栏的相应按钮快速进行格式设置。

【货币样式】按钮：在数字前加“￥”货币符号。

【百分比样式】按钮：在数字乘以100并在后面加“%”号，如“13%”。

【千位分隔样式】按钮：从个位数开始每三位整数加一个逗号分隔。

【增加小数位数】按钮：每单击一次增加一位小数位。

【减少小数位数】按钮：每单击一次减少一位小数位。

3．设置字符格式可设置“字体”、“字形”、“字号”、“下划线”、“颜色”以及“特殊效果”（如上、下标等）也可以通过“格式”工具栏上的相应按钮进行设置。

4．设置对齐方式可设置所选单元格或单元格区域的文本对齐方式，文本方向，以及文本的特殊格式：如自动换行、缩小字体填充、合并单元格。

【合并及居中】按钮：相当于在“对齐”选项卡中选中了水平居中、垂直居中和合并单元格复选框。5．设置边框格式选定要设置边框的单元格或单元格区域，单击“边框”选项卡中的相应的边框按钮。所选区域加上边框所选区域中间单元格加边框加下边框取消所加边框为单元格添加斜线所选区域加左边框设置边框线型设置边框线条颜色选定好要添加边框的单元格或单元格区域后也可以单击“格式”工具栏的【边框】按钮来快速添加边框。其中黑实线为所选区域添加边框的位置。6．清除格式选择菜单“编辑”→“清除”→“格式”命令。

注意：“编辑”菜单下的“删除”与“清除”的区别。7．快速格式化（1）使用选择性粘贴。

先选定已经设置好格式的单元格，单击【复制】按钮，再选定要设置格式的单元格或单元格区域，选择菜单“编辑”→“选择性粘贴”命令弹出如右图所示的对话框，选择选项。

【例5-2】打开Eg5-2.xls，完成下列操作后以原文件名存盘。（1）将区域B3:B22的格式设置成与区域D3:D22相同的格式。（2）将区域D3:D22的值复制到区域C3:C22中。（3）将区域D3:D22的公式复制到区域F3:F22中。（4）将D3:D22的内容放置到以A23单元格为起始位置的行中。（5）清除D3:D22的格式。

最初效果最终效果（2）使用格式刷。8．条件格式化

1）选定要设置格式的单元格区域，选择菜单“格式”→“条件格式”命令，弹出如图所示的对话框。

2）在条件1的下面选择并输入条件，单击【格式】按钮，在弹出的“单元元格格式”对话框中设置格式，单击【确定】按钮。

3）若要设置多个条件，可单击【添加】按钮来增加条件。

【例5-3】打开Eg5-3.xls文件，完成下列操作后以原文件名存盘。最终效果如下图所示。（1）将“语文”和“数学”列中低于60分的成绩用红色、加粗的字体显示。（2）将“总分”列中高于180分的成绩用红色、加粗的字体显示，低于120的用蓝色、倾斜的字体显示。

条件格式化效果9．自动套用格式选择菜单“格式”→“自动套用格式”命令，在“自动套用格式”对话框中选择一种表格样式。

5.1.7实例制作公司员工工资表完成某公司员工工资表的制作及工资表的格式化。【例5-4】制作某公司员工工资表，部分效果如下图所示，输入表格数据后并根据下列要求设置表格中的数据格式。要求如下：（1）表格标题“员工工资表”字体为黑体、字号为20，在整个表格中合并单元格居中。（2）表头部分，即“部门”、“编号”，“姓名”等所在行的行高设置为20磅，并为该行填充“浅青绿”底纹。（3）“基础部”、“研发部”、“销售部”、“财务部”要求按样表所示设置为合并单元格居中（水平居中且垂直居中）。（4）数字区的格式设置为“会计专用”数字类型，小数位数2位，无货币符号。（5）给表格外框设置为蓝色的粗实线，内部框线为黑色细实线。（6）表格所有数据内容设置为水平方向居中和垂直方向居中。（7）重命名工作表为“工资表（1）”。（8）复制该表至Sheet2前，将基本工资在1500元及以上的所有数据用红色、倾斜的字体显示。（9）将“工资表”中的内容复制到Sheet2中，将表格的内容（标题除外）自动套用格式为“彩色2”。（10）以“工资表.xls”存盘。员工工资表效果5.2公式与函数

5.2.1单元格的引用1．单元格引用的概念【例5-5】打开Eg5-5.xls,如图5-27所示，求下列工作表中某班同学的总分。现在假设用公式来求总分。总分等于语文成绩加数学成绩，即总分=语文+数学，用单元格的形式来表示，即为E2=C2+D2

。因此在E2单元格中的公式中引用了C2和D2两个单元格。公式或函数中单元格的引用分相对引用、绝对引用和混和引用。

（1）相对引用。不管将公式复制到哪个单元格，公式中所引用的单元格与公式所在的单元格的相对位置保持不变。表示方法：列行。如：E2中的公式为：=C2+D2【例5-6】求下图所示学生成绩的总分。注意：D列和I列公式的变化规律和“选择性粘贴”的使用。（2）绝对引用。无论将公式复制到哪个单元格，公式所在的单元格和所引用的单元格的绝对位置不变。即公式永远不发生变化。表示方法：$列$行。在列标和行号前均有“$”符为绝对引用。（3）混合引用。相对引用和绝对相引的混合使用。在进行公式复制时，公式中相对引用的部分按相对引用的定义变化，而绝对引用的部分保持不变。表示方法：$列行（列不变行变，但行的相对位置不变）或列$行（列变行不变，但列的相对位置不变）。如：将D3中的公式“=B$1+$C3”复制到D4单元格后，D4的公式是什么？

【例5-7】打开Eg5-7.xls，效果如下图所示，在E4:E17中求出到期利息。

【例5-8】打开Eg5-8.xls，运用公式的复制方法，求出Sheet1中每个学生的分数，其中理论成绩占30%，实验成绩占70%，效果如下图所示。

2．常见的出错信息及其追踪（1）常见的出错信息。表5-1常见错误信息的意义

错误值意义#####单元格的数据长度超过了列宽#DIV/0!公式、函数中出现被零除的情况#REF!公式、函数中引用了无效的单元格#VALUE！公式、函数中操作数的数据类型不对#NAME？公式中使用了未经定义的文字内容#NULL！公式、函数中使用了没有相交的区域#NUM！公式、函数中某个数字有问题（2）“审核”工具追踪错误

选择菜单“工具”→“审核”→显示“审核”命令

①②③④⑤⑥⑦⑧⑨①追踪引用单元格②移去引用单元格追踪箭头③追踪从属单元格

④移去从属单元格追踪箭头

⑤取消所有追踪箭头

⑥追踪错误

⑦新批注

⑧圈释无效数据

⑨清除无效数据标识圈

5.2.2公式公式是通过数学运算符如+、－、*、/、^等将数据、单元格地址及函数连接起来式子。

（1）运算符

算术运算符：+（加）、－（减）、×（乘）、／（除）、^（乘幂）、%（求百分数）。

文字运算符：&（字符连接符），将两个文字连接起来生成一个新文字。如：“多媒体”&“计算机”的运算结果是“多媒体计算机”。

比较运算符：=（等于）、>（大于）、<（小于）、>=（大于等于）、<=（小于等于）、<>（不等于）。比较运算的结果是逻辑值，即TRUE（真）、FALSE（假）。

（2）输入公式

输入公式的步骤如下：1）单击要输入公式的单元格。2）输入“=”号（也可单击编辑栏左侧的“编辑公式”或【粘贴函数】按钮）。

3）输入公式内容后单击〈Enter〉键。

注意：对于运用数组公式的计算必须按〈Ctrl+Shift+Enter〉来确定。

【例5-9】打开Eg5-9.xls，在“备注”列中求每个客人的住店天数。效果如下图所示，

【例5-10】用数组求上例的住店天数，并将结果另存为Eg5-10.xls。

【例5-11】打开Eg5-11.xls文件，完成下列操作后以原文件名存盘。（1）完成“第一商场”、“第二商场”工作表中的“总计”、“销售额”和“利润”的计算。其中利润等于销售额的15%。（2）完成“总公司”的汇总。效果如下图所示。5.2.3函数1．常用数值函数（1）求和函数SUM

格式：SUM（number1,number2,…）功能：求参数中数值或所指定单元格区域的数值的和。说明：每个参数可以是数值、单元格引用坐标或函数。（2）求平均值函数AVERAGE格式：AVERAGE(number1,number2,…)功能：求参数中数值或所指定单元格区域的数值的平均值。（3）求最大值函数MAX格式：MAX(number1,number2,…)功能：求参数中数值或所指定单元格区域的数值的最大值。

（4）求最小值函数MIN格式：MIN(number1,number2,…)功能：求参数中数值或所指定单元格区域的数值的最小值。（5）求数字项个数函数COUNT格式：COUNT(number1,number2,…)功能：求参数中数值或所指定单元格区域的数字项的个数。（6）求数据项的个数函数COUNTA格式：

COUNTA(number1,number2,…)功能：求参数中数值或所指定单元格区域的数据项的个数。

说明：

上述函数中的每个参数可以是数值、单元格引用坐标或函数。

COUNT函数和COUNTA函数的区别是：COUNT函数统计的是数值型数据项的个数，而COUNTA函数统计的是数据项（包括数值型、字符型、逻辑型、日期型等数据项的个数）。相同点：空白单元格不统计。

函数中所有的参数均是在英文状态下的输入的。

【例5-12】打开Eg5-12.xls文件，在sheet1中按下列要求完成操作后以原文件名存盘，最终效果下图所示。（1）在“总分”所在列求出每个学生的总分。（2）在“平均分”所在列求出每个学生的平均分（只保留整数）。（3）在I1单元格中求出语文成绩中的最高分。（4）在I3单元格中求出数学成绩中的最低分。（5）在I5单元格中求出该班的学生人数。2．字符函数(1)左取字符串函数LEFT格式：LEFT（text,num_chars）功能：返回文本字符串text中从左边第一个字符取起的num_chars个的子字符串。说明：text是包含要提取字符的文本字符串；num_chars指定要提取的字符数。（2）右取字符串函数RIGHT格式：RIGHT（text,num_chars）功能：返回文本字符串text中从右边第一个字符取起的num_chars个的子字符串。（3）任意位置取字符串函数MID格式：RIGHT（text,start_num,num_chars）功能：返回文本字符串text中从start_num位置开始往后连续取num_chars个字符。

（4）查找子串函数FIND（区分大小字字母）格式：FIND（find_text,within_text,start_num）功能：返回要查找的字符串find_text在within_text中由第start_num个字符开始查找首次出现时的位置。（5）查找子串函数SEARCH（不区分大小写字母）格式：SEARCH（find_text,within_text,start_num）功能：返回要查找的字符串find_text在within_text中由第start_num个字符开始查找首次出现时的位置。【例5-13】打开Eg5-12.xls文件，在sheet2中按下列要求完成操作。（1）在B2:B133中求出“班次”列中相应的第1位编码。（2）在C2:C133中求出“班次”列中相应的最后1位编码。（3）在D2:D133中求出“班次”列中相应的第2位编码。（4）在E2:E133中求出“班次”列中相应的第2位编码和第3位编码。（5）在F7单元格求出“m”

在F5单元格的字符串中从第1个字符开始查找第一次出现的位置。（区分大小写）。（6）在F9单元格求出“m”

在F5单元格的字符串“MiriamMcGovern”中从第2个字符开始查找第一次出现的位置。（不区分大小写）。

效果如下图所示。3．数学函数（1）绝对值函数ABS格式：ABS（number）功能：返回参数number的绝对值。（2）求余数函数MOD格式：MOD（number,divisor）功能：返回参数number除以参数divisor所得的余数，结果的正负号与divisor的正负号相同。（3）求平方根函数SQRT

格式：SQRT（number）功能：返回参数number的平方根。（4）取整函数INT格式：INT（number）功能：返回参数number向下四舍五入后的整数值。（5）取整函数TRUNC格式：TRUNC（number）功能：返回参数number整数部分。（6）四舍五入函数ROUND格式：ROUND（number,num_digits）功能：返回参数number按四舍五入规则保留num_digits位小数的值。（7）随机函数RAND格式：RAND（）功能：返回一个0～1之间的随机数（包括0不包括1）。（8）字符转数值函数VALUE格式：VALUE（text）功能：将数字字符串转换成数字。说明：Text为带引号的文本，或对需要进行文本转换的单元格的引用。Text可以是

MicrosoftExcel中可识别的任意常数、日期或时间格式。如果Text不为这些格式，则函数

VALUE返回错误值

#VALUE!。（9）选择序列项函数CHOOSE格式：CHOOSE（number_index,number1,number2…）功能：返回number1，number2，…序列中由number_index指定的序列数。

【例5-14】打开Eg5-12.xls文件，在sheet3中完成下列操作后以原文件名存盘。（1）在B2:B16中分别求出A列（“原始值1”）相应数的绝对值。（2）在C2:C16中分别求出A列（“原始值1”）相应数的整数值。(INT)（3）在D2:D16中分别求出A列（“原始值1”）相应数的整数部分。（TRUNC）（4）在E2:E16中求出A列（“原始值1”）相应的数进行四舍五入后保留2位小数位数的值。（5）在F2:F16中求出B列（“绝对值”）中相应数的平方根。（6）在H2:H16中求出G列（“原始值2”）相应值被2除的余数。（7）在I2:I16中求出G列（“原始值2”）相应值被－2除的余数。（8）从A18单元格开始在该单元格所在行的连续单元格中生成10个20～100之间的随机整数。（9）在A21:D21分别求出A20:D20中相应值的数值型数据。效果如下图所示4．日期函数（1）系统日期和时间函数NOW格式：NOW（）功能：返回计算机系统内部时钟当前日期和时间的序列数。说明：该序列数是一个正实数。其中，整数部分代表当前日期，小数部分代表当前时间。（2）系统日期函数TODAY格式：TODAY（）功能：返回计算机系统内部时钟当前日期的序列数。（3）指定日期函数DATE格式：DATE（year,month,day）功能：返回指定日期的序列数。说明：year参数指年份，month参数指月份，day参数指日数。

（4）指定时间函数TIME格式：TIME（hour,minute,second）功能：返回指定时间的序列数。说明：hour参数指小时，minute参数指分，second参数指秒。注意：MicrosoftExcel将日期存储为序列号（称为序列值）。默认情况下，1900年

1月1日是序列号

1，2008年

1月

1日是序列号

39,448，这是因为它距

1900年

1月

1日有

39,448天。Excel将时间存储为小数，因为时间被看作天的一部分，时间用小数0～1表示。0点表示为0，12:00PM可表示为

0.5（因为此时是一天的一半），24:00点表示1。（5）计算年函数YEAR格式：YEAR（serial_number）功能：返回序列数所对应的年份。（6）计算月函数MONTH格式：MONTH（serial_number）功能：返回序列数所对应的月份。（7）计算日函数DAY格式：DAY（serial_number）功能：返回序列数所对应的日数。注意：HOUR（时）、MINUTE（分）、SECOND（秒）函数同YEAR、MONTH、DAY函数一样的用法。【例5-15】打开Eg5-13.xls，在Sheet1中完成下列操作后以原文件名存盘。效果如下图所示。（1）在F1单元格中求出系统的日期和时间。（2）在F2单元格中求出系统日期的年份。（3）在F3单元格求出系统日期的月份。（4）在F4单元格求出系统日期的天数。（5）在A2:A4区域中分别求出A1日期中第二、三、四季度的第一天。

5．逻辑函数与条件函数（1）逻辑“与”函数AND格式：AND（logical1,logical2,…）功能：当所有参数的逻辑值为TRUE时，结果才返回为TRUE，否则为FALSE。说明：参数logical1,logical2,…，是1到30个结果为TRUE或FALSE的表达式，一般是关系表达式。（2）逻辑“或”函数OR格式：OR（logical1,logical2,…）功能：当所有参数的逻辑值为FALSE时，结果才返回为FALSE，否则为TRUE。（3）逻辑“非”函数NOT格式：NOT（logical）功能：若logical为FALSE，则取反后返回TRUE；若logical为TRUE，则返回FALSE。（4）条件选择函数IF格式：IF（logical_test,value_if_true,value_if_false）功能：当逻辑测试条件logical_test为TRUE时，返回value_if_true的结果，否则返回value_if_false的结果。说明：一个IF函数只能得到两种结果，三种结果必须用到两个IF函数。因此要得到多种情况的结果，则必须用到IF的嵌套。IF函数嵌套指参数value_if_true和

value_if_false本身又是一个IF函数，一般放到value_if_false参数中去套IF函数。IF的嵌套最多只能套7层。【例5-16】打开Eg5-12.xls文件，在sheet4中完成下列操作后以原文件名存盘，效果如下图所示。（1）在B2:P2中分别求出对应第1行的值在区间[-10，10]中为“正常”，否则为“异常”。（2）在B3:P3中分别求出对应第1行的值不在区间[-10，10]中为“正常”，否则“异常”。

【例5-17】打开Eg5-12.xls文件，在Sheet5中按要求完成下列操作后以原文件名存盘，效果如下图所示。在G2:G25中求出当“平均分”列中分数在60分以下的等级为“不合格”，60至84的等级为“合格”，85分及以上的等级为“优秀”。

分析：先对平均分分段，再按顺序写出函数。注意：函数形式与分段的关系<85合格<60不合格>=85优秀平均分【例5-18】打开Eg5-12.xls文件,在Sheet6中的F4:F10区域中完成操作，若总分大于等300分为上线，否则为落榜，最终效果如下图所示。

6．频率分布函数FREQUENCY格式：FREQUENCY（data_array,bins_array）功能：以一列垂直数组返回某个区域中数据的频率分布。说明：频率分布是对于一组数据data_array和一组数值分段bins_array，求出该组数据落在各个分段内的数据个数（空白单元格和字符串不统计）。其中bins_array为数据区data_array的完整分段。注意：在函数中选择完区域后必须按〈Ctrl+Shift+Enter〉键来确定，不能直接单击【确定】按钮或按【Enter】键。

7．排名次（位）函数RANK格式：RANK（number,ref,order）功能：返回一个数值number在一组数值ref中的排位（名）。说明：

Number为需要排位的数字。

Ref

为包含一组数字的数组或引用。Ref中的非数值型参数将被忽略。（Ref参数必须使用绝对引用）

Order

为一数字，指明排位的方式。如果order为

0或省略，则按降序排，即第1名为最高值。如果

order不为零，则按升序排，第1名为最小值。

8．条件统计函数（1）条件计数函数COUNTIF格式：COUNTIF（range,criteria）功能：计算给定区域range内满足特定条件criteria的单元格的数目（记录个数）。说明：range指条件所在列（不包括列标题）的范围，criteria指条件（或条件所在的任一单元格引用）。（2）条件求和函数SUMIF格式：SUMIF（range,criteria,sum_range）功能：根据指定条件对满足条件的单元格的数据求和。说明：参数range和criteria用法同COUNTIF，sum_range指求和的范围。

【例5-19】打开Eg5-12.xls文件，在sheet7中完成下列操作后以原文件名存盘，效果如图5-46所示。（1）在I列中求出“平均分”在60分以下，60至75，76至85及85分以上的人数。（2）在“名次”列求出每个学生按“总分”从高分到低分的排名。（3）在H1和I1单元格中分别求男生的人数和平均分在75分及以上的人数。（4）在J1中求出女生的平均分的总和。

效果如下图所示。9．数据库函数数据库函数的格式：函数名（database,field,criteria）注意：数据库函数是以D开头的函数。对数据库函数必须建立条件区域。条件区以列标题（字段名）开始，条件区域同一行是与的关系（即各条件要同时满足），不同行之间是或的关系（即只要满足其中的一个条件。参数database为从字段名开始的整个数据清单（不包括数据清单的标题）。

参数

field为需要统计的字段名。

参数

criteria为建立的条件区，条件区不能多选，选多了则等于无条件。

在条件中可能使用通配符“？”和“*”。

（1）数据库求和函数DSUM功能：返回数据清单或数据库的指定列中，满足给定条件单元格中的数字之和。（2）数据库求平均值函数DAVERAGE功能：返回数据清单或数据库中满足给定条件的数据列中数值的平均值。（3）数据库求最大值函数DMAX功能：返回数据清单或数据库的指定列中，满足给定条件单元格中的最大数值。（4）数据库求最小值函数DMIN功能：返回数据清单或数据库的指定列中，满足给定条件单元格中的最小数值。（5）数据库计数函数（1）：DCOUNT功能：返回数据清单或数据库中满足一个或多个条件的记录数（或单元格数目）。（6）数据库计数函数（2）：DCOUNTA

功能：返回数据清单或数据库中满足一个或多个条件的记录数（或单元格数目）。【例5-20】打开Eg5-20.xls文件，在Sheet1中按下列要求完成操作后以原文件名存盘。（1）在Sheet1中的A17单元格求出副教授的总收入的总和。（2）在Sheet1中的D17单元格求出讲师的平均总收入。（3）在Sheet1中的A19单元格求出副教授职务工资不超过1600的岗位补贴的总和。（4）在Sheet1中的D19单元格求出讲师或奖励补贴超过140的职务工资的平均值。（5）在Sheet2中的H1单元格求出该班男生的人数。（6）在Sheet2中的H4单元格求出该班女生平均分在85分及以上的人数。（7）在Sheet2中的H7单元格求出该班语文成绩超过85分或数学成绩超过85分的人数。

（1）至（4）题效果如下图所示。（5）至（7）题效果如下图所示。10．金融函数（1）PV函数

格式：PV（rate,nper,pmt,fv,type）功能：返回投资的现值。现值为一系列未来付款当前值的累积和。说明：Rate为各期利率。

Nper

为总投资（或贷款）期，即该项投资（或贷款）的付款期总数。

pmt为各期所应付给（或得到）的金额。fv为未来值，或在最后一次支付后希望得到的现金余额，如果省略

fv，则假设其值为零（一笔贷款的未来值即为零）。

type数字

0或

1，用以指定各期的付款时间是在期初还是期末。如果省略

type，则假设其值为零（为期末付款）。

注意：若Rate为月利率，则Nper

必须以月偿还期。（为年的话乘12转换为月）若Nper为按月偿还，如果Rate为年利率，则Rate/12转换为月利率。（2）PMT函数格式：PMT（rate,nper,pv,fv,type）功能：基于固定利率及等额分期付款方式，返回投资或贷款的每期付款额。说明：

Pv

为现值，即从该项投资（或贷款）开始计算时已经入账的款项，或一系列未来付款当前值的累积和，也称为本金。

Fv

为未来值，或在最后一次付款后希望得到的现金余额，如果省略

fv，则假设其值为零（例如，一笔贷款的未来值即为零）。

其他参数同PV函数。注意rate与nper的单位。【例5-21】打开Eg5-20.xls文件，在Sheet1中完成下列操作后以原文件名存盘，效果如下图所示。（1）在E2单元格求出企业向银行的贷款额。（2）在E5单元格求企业每月给银行的偿还额。

10．LOOKUP函数格式：LOOKUP(lookup_value,lookup_vector,result_vector)

功能：函数

LOOKUP的向量形式是在单行区域或单列区域（向量）中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值。

说明：Lookup_value

为所要查找的数值。Lookup_vector为要查找的数值所对应的区域。result_vector为要查找数值相应的结果值。

【例5-22】打开Eg5-22.xls，根据A1:B6区域中给出的编号和协会名称，求出C9:D19区域中对应的协会名称。效果如下图所示。

6.2.4综合实例工资表数据的计算【例5-23】打开Eg5-23.xls，完成以下操作后以原文件名存盘。

（1）在I3:I16区域，求出每位员工的应发工资。（2）在M3:M16区域，若总收入(总收入=应发工资－扣水电费－扣保险－扣其它)超过2000元的则超过部分按7%交税，2000元及以下免税，求每位员工的实发工资。（3）在N3:N16区域按下列原则求出每位员工的工资级别：基本工资在1000元以下为1级，在1000元至1500元为2级，1500元以上为3级。（IF函数）

（4）在A18单元格求出“基础部”的人数（COUNTIF函数）（5）在A19单元格求出“研发部”的“实发工资”的总和。（SUMIF函数）（6）在D18单元格求出“基础部”的实发工资在2500元及以上的人数。（DCOUNT或DCOUNTA函数）（7）在D21单元格求出“奖金”超过1000元或附加工资在500元以上的实发工资的平均值。（DAVERAGE函数）（8）在G18单元格求出“研发部”的“基本工资”1500元及以上的实发工资的总和。（DSUM函数）（9）在F21单元格求出“研发部”的最低实发工资。（DMIN函数）（10）在H21单元格求出“基础部”的最高实发工资。（DMAX函数）（11）在B24:B26区域中统计出实发工资在1500元及以下、1500元以上至3000元、及3000元以上的人数。5.3.1数据的排序1．简单排序（1）按单个关键字排序。（2）3个及3个以内关键字（指字段名）的排序。单击数据清单中的任一单元格,选择菜单“数据”→“排序”命令，弹出“排序”对话框。5.3数据的管理与分析【例5-24】打开Eg5-24.xls，效果如下图所示，将Sheet1中的数据复制到Sheet2中并按性别关键字进行排序，要求女生在前男生在后。

【例5-25】打开Eg5-24.xls，将Sheet1中的数据复制到Sheet3中，先按系别递增排序，系别相同的记录按专业递增排序，专业相同再按总成绩从高分到低分排序。效果如下图所示。2．3个以上关键字的排序排序原则：对关键字进行分组，先排最不关键的关键字，后排最关键的关键字。

排序方法：将关键字按每三个分组，第1次排序排最后一组，第2次排序排倒数第2组，……，最后排第一组，每组中的主、次、第三关键字按先后次序排。排序的最后效果以最关键的关键字为主排序。【例5-26】将上例中Sheet1中的数据复制Sheet4中，对数据清单先按性别进行递增排序，要求性别相同的情况下按系别递增排序，系别相同再按专业递增排序，若专业相同再按总成绩递减排序。分析：

关键字：性别，系别，专业，

总成绩

排序时先排第2组，再排第1组。1组2组第1次排序第2次排序3．其它排序除了按指定关键字进行排序外，还可按自定义序列排序、区分大小写排序、按列（行）排序、按字母（笔画）排序。在“排序”对话框中单击【选项】按钮，在“排序选项”对话框中进行设置

【例5-27】将上例中的数据清单的记录按专业字段值为“应用、软件、微电子、自控”的顺序排列。

操作步骤：1）先定义序列：选择菜单“工具”→“选项”对话框中的“自定义序列”选项卡

2）在“排序”对话框中单击【选项】按钮，在“排序选项”对话框中的“自定义排序次序中选择所定义的序列。5.3.2条件区域的创建数据库函数和高级筛选都要建立条件区域，条件区域的建立方法如下：

1）第一行为条件字段标题，不管有多少个条件，将条件所对应的字段名依次复制到条件区第一行相邻的不同单元格中（字段名也可直接输入）。注意条件字段名必须与数据清单的字段名相同。2）紧接的第二行及以下各行为条件，条件放置的位置必须和相应的字段名相对应，条件必须和字段名所对应值的数据类型一致。3）同一行的条件互为“与”（AND）的关系，不同行之间为“或”（OR）的关系。【例5-28】筛选计算机系总成绩大于等于320分的所有记录，写出该条件区域。【例5-29】筛选数学成绩大于85分或者是英语成绩大于85分或者是政治成绩大于85分的所有记录，写出该条件区域。【例5-30】筛选男生的英语成绩大于等于60且英语成绩小于85的所有记录，写出该条件区域。

【例5-31】筛选女生的中的英语成绩大于80或女生数学成绩大于80的所有记录，写出该条件区域。5.3.3数据的筛选1．自动筛选1）单击数据清单中的任一单元格，选择菜单“数据”→“筛选”→“自动筛选”命令。2）单击各条件对应字段名右边的下拉按钮，设置条件，不同字段（即不同列之间）中设置的条件是“与”的关系。

3）取消自动筛选状态，再次选择菜单“数据”→“筛选”

→“自动筛选”命令，则所设置的条件也全部取消。

【例5-33】打开Eg5-33.xls，将Sheet1中的数据清单复制到Sheet2中，完成下列操作后以原文件名存盘：（1）使用自动筛选筛选出“软件”专业，“平均分”在80分以上的所有记录。（2）使用自动筛选选出“平均分”小于80或大于等于85分的微电子专业的男生的所有记录。

（1）（2）2．高级筛选先在数据清单外的空白区域建立条件区域，然后单击“数据”

→“筛选”

→“高级筛选”命令。【例5-34】将上例中Sheet2中的数据清单利用高级筛选，筛选出语文成绩大于85或数学成绩大于等于95或英语成绩大于90分的记录。条件区域：A15:C18，将筛选结果复制到以A19为左上角的区域，效果如下页图所示。

【例5-35】打开Eg5-33.xls，对Sheet3中的数据清单利用高级筛选，筛选出1960年及以前出生的职称为工程师的所有记录，并将筛选结果复制到H1为左上角的区域。

【例5-36】打开Eg5-33.xls，对Sheet4中的数据清单，利用高级筛选，筛选出所有始发时间介于早上6点到下午6点的所有记录，并将筛选结果复制到F1为左上角的区域。若把筛选条件改为始发时间介于下午6点到第二天早上6点，条件区域该如何建立？

5.3.4分类汇总分类汇总包括分类和汇总两种操作：先按分类字段进行分类（执行排序操作），然后再按照分类字段对指定的数值型字段进行某种方式的汇总（执行分类汇总操作）。

1．简单分类汇总【例5-37】打开Eg5-37.xls，对Sheet1中的数据清单进行分类汇总，按年龄分类求出各类年龄人员的最高身高和最重体重。效果如下页图所示。

2．复杂分类汇总【例5-38】将上例分类汇总前的数据复制到Sheet2中，对Sheet2中的数据清单进行分类汇总，按年龄分类求出各类年龄人员的人数、最低体重。效果如下图所示。

3．删除分类汇总单击数据清单中任一单元格，选择菜单“数据”→“分类汇总”命令，单击“分类汇总”对话框中的【全部删除】按钮。5.3.5数据透视表1.创建数据透视表按向导来创建数据透视表。【例5-39】打开Eg5-39.xls，对Sheet1中的数据清单创建数据透视表，班级放在页字段，性别放在行字段，数据项为语文成绩的平均成绩、英语成绩的最大值。

操作步骤如下：

1）单击数据清单中的任一单元格，选择菜单“数据”→“数据透视表和图表报告”命令，弹出“数据透视表和数据透视图向导—3步骤之1”，如下图所示。

2）单击【下一步】按钮，弹出“数据透视表和数据透视图向导—3步骤之2”对话框，如下图所示。此时自动选择数据区域，若区域不对则要单击折叠按钮重新选取。

3）单击【下一步】按钮，弹出“数据透视表和数据透视图向导—3步骤之3”对话框，如下图所示。

4）单击【版式】按钮，弹出下图所示的“版式”对话框。将右边的字段拖至相应的区域。注意数据区的字段一定是汇总字段。

5）单击【确定】按钮，返回到“数据透视表和数据透视图向导——3步骤之3”对话框。单击【完成】按钮。

最终效果如下图所示。2．编辑数据透视表【例5-40】（1）将上例中的“性别”字段拖至列字段区效果如何？比较放在行字段区与放在列字段区中的区别。（2）将“性别”字段放行，“班级”字段放列，效果如何？

【例5-41】打开Eg5-41.xls，根据Sheet1中的数据清单，作一个由三行组成的数据透视表，其中第一行为年龄，第二行为该年龄人员对应的最高身高，第三行为该年龄人员对应的平均体重，置于Sheet1中的F1单元格为左上角的区域中。

5.3.4综合实例工资表数据的处理【例5-42】

打开Eg5-42.xls，将Sheet1中的数据复制到Sheet2、Sheet3、Sheet4、Sheet5、Sheet6中，完成下列操作后以原文件名存盘。

（1）对Sheet2中的数据清单按“部门”排序递增排序，部门相同的记录再按“实发工资”从高到低排序。（2）对Sheet3中的数据清单进行自动筛选，筛选出基本工资小于1000或者基本工资大于1500元，奖金大于1200元的所有记录。

（3）对Sheet4中的数据清单利用高级筛选，筛选出基础部中基本工资超过1500元或基础部中的实发工资大于等于3000元的所有记录，并将筛选结果复制到Sheet4！A22为左上角的区域。（4）对Sheet5中的数据清单进行分类汇总，按部门分类求出各部门应发工资和实发工资的平均值。（5）根据Sheet6中的数据清单创建一个四行的数据透视表，第一行为部门，第二行为每个部门的人数，第三行为每个部门的应发工资的最大值，第四行为每个部门的实发工资的平均值，数据透视表结果置于新工作表中。

5.4.1图表创建方法1.图表的组成

图表主要由图表标题、数值轴、分类轴、数据系列、图例等组成，如下图所示。5.4图表的制作图表标题数值轴数值轴标题分类轴分类轴标题图例图表区绘图区数据系列背景墙数值轴主网格线2．图表的创建

Excel提供了图表的创建向导，向导分为4个步骤，依次为：图表类型、图表源数据、图表选项和图表位置。

图表的创建首先要注意数据源区域的选取，对于不连续的数据区域按Ctrl键进行选取，然后再根据图表向导创建图表。【例5-43】打开Eg5-43.xls，创建如上页图所示的簇状柱形图，要求系列产生在行，嵌入在当前工作表中，最后以原文件名存盘。

1）选定数据源区域A3:F7，单击常用工具栏上的【图表向导】按钮，或选择菜单“插入”→“图表”命令。2）进入图表向导步骤1，选择图表类型，本例中选择簇状柱形图，弹出如下图所示的对话框。图表类型分标准类型和自定义类型。标准类型有柱形图、条形图、折线图、饼图、XY散点图等

;自定义类型有彩色堆积图、彩色折线图、黑白饼图等。如下图所示。

3)单击【下一步】按钮，进入图表向导步骤2，选择数据源。弹出如下图所示的对话框（分“数据区域”和“系列”选项卡）。

4）单击【下一步】按钮，进入图表向导步骤3，设置图表选项。弹出如下图所示的对话框。

5）单击【下一步】按钮，进入图表向导步骤4，确定图表位置。弹出如下图所示的对话框。

3．图表工具栏选择菜单“视图”→“工具栏”→“图表”命令或鼠标右击任意工具栏，选择“图表”命令，显示图表工具栏。图表对象选定对象格式图表类型图例数据表按行按列向下斜排文字向上斜排文字5.4.2图表的编辑1．图表类型的