Microsoft Word 计算机应用基础教程正文.doc

Transcription

1 单元 4 Excel 2003 的使用技巧 会对数据表进行数据有效性设置 ; 会利用相关函数对数据表中数据进行统计计算 ; 会对数据清单中的数据进行排序 筛选及分类汇总 ; 能够利用数据透视表对数据清单汇总 绘制图表 任务一学生成绩录入与数据有效性控制 在录入大量数据记录的过程中, 不可避免地出现录入错误的现象 为了尽可能地减少录 入错误, 可对数据表单元格区域设置有效性校验条件, 一旦出现录入错误, 系统会自动提示, 以便修改 案例 1 数据填充与有效性设置 场景描述 教务员小张经常要录入该系的各门课程的成绩单, 为了快速录入且减少错误, 小张想到利用数据填充和数据有效性等工具来实现他的要求 请打开 学生成绩表.xls, 根据小张的要求进行如下设置 : (1) 序号从 1 自动填充到 21 (2) 学号列从 填充到 (3) 在性别列 D2:D21 区域设置数据有效性 要求 : 当用户选中 性别 列除标题外的任一单元格时, 在其右侧显示一个下拉列表框箭头, 并提供 男 和 女 的选项供用户选择 (4) 设置课程列数据的有效性, 要求 : 当光标定位于相应列的单元格时, 显示输入信息 : 标题 成绩, 请输入 0~100 之间有效成绩 当用户输入数据不在指定的范围内, 系统自动弹出错误对话框, 错误信息 成绩必须在 0~100 之间, 停止 样式, 错误对话框的标题为 错误 1. 案例分析案例中 (1) (2) 的要求可以通过自动填充或手动填充来实现 ;(3) (4) 中的要求可以

2 129 通过 数据 菜单下的 有效性 命令来实现 2. 相关知识点 (1) 工作簿 工作表 单元格 ( 单元格区域 ) Excel 文档称为 Excel 工作簿, 其扩展名是.xls, 每一个工作簿可包含多张工作表 工作表默认的标签名称是 Sheet1 Sheet2 Sheet3, 当然也可以重新命名 每张工作表有 256 列 行, 列号用字母 A B 标识, 行号用 标识 单元格地址用列号加行号表示, 如 A1 AB100 A65536 等 连续单元格组成的矩形区域用 左上角单元格地址 : 右下角单元格 地址表示 如 A1:D100 表示从 A1 单元格到 D100 单元格之间的矩形区域 (2) 文本型数值数据的输入在中文 Excel 中, 文本是指当作字符串处理的数据 对于不参与运算的数字型数据有时必须把它们作为文本型数据处理, 否则可能显示不正确或造成某些数据位的丢失 如身份证号 ( 若作为数值型数据处理, 身份证右边几位数将作 0 处理 ) 课程代码 ( 若作为数值型数据左面的 0 将丢失 ) 等 在输入这些作为文本的数字型数据时, 应先输入英文单引号, 再输入数字串 当单元格内的数据比较长而列宽有限时, 可以按 Alt+Enter 组合键 ( 或在单元格格式中设置 自动换行 ) 实现单元格内换行, 单元格高度自动增加, 以容纳多行数据 (3) 数据填充当工作表中同行或同列数据有规律时, 可以使用填充功能快速输入数据 单击 编辑 填充 向下 ( 上 ) 填充 / 向右 ( 左 ) 填充 命令, 实现数据快速复制 ; 单击 编辑 填充 序列 命令, 根据需要可在 序列 对话框选择 等差序列 等比序列 日期 之一进行数据填充 对于日期选项, 还可按 年 月 日 工作日 进行数据填充, 如图 4-1 所示 图 4-1 填充序列 (4) 数据的有效性对某单元格区域预先设置好数据有效性条件, 一旦用户输入数据不符合有效性条件时, 系统会自动弹出错误信息对话框, 以提示用户修改输入数据, 这样可减少人工输入错误 单击 数据 有效性 命令, 弹出 数据有效性 对话框, 如图 4-2 所示, 在 设置 选项卡的 有效性条件 选项组中, 在 允许 下拉列表框中, 选择其中一项进行设置 如允许 整数 可设置整数范围 ; 选择 序列 可定义数据序列 ; 选择 自定义 可自定义数据应满足条件 详见 3 4 中具体步骤

3 130 计算机应用基础教程 图 4-2 数据有效性 3. 实现方法 (1) 打开 学生成绩表.xls 文件 在 成绩表 工作表的单元格 A2 中输入 1 选择单元格区域 A2:A21 单击 编辑 填充 序列 命令 在 序列 对话框的 步长值 文本框中输入 1 确定 最便捷方法 : 按住 Ctrl 键同时拖动 A2 右下角的填充柄到 A21 即可 (2) 在单元格 B2 中, 直接输入数据 , 按住 Ctrl 键同时拖动 B2 右下角的填充柄到 B21 即可 若单元格数据显示为 2.01E+12, 是因数据太大而以指数形式显示 ( ) 这时只需在 单元格格式 对话框的 数字 选项卡中, 在 分类 列表中选择 数值, 设置 小数位数 为 0, 如图 4-3 所示, 单击 确定 按钮 图 4-3 数字 选项卡 (3) 选择单元格区域 D2:D21, 单击 数据 有效性 命令, 弹出 数据有效性 对话框, 在 设置 选项卡的 有效性条件 选项组的 允许 下拉列表框中选择 序列, 在 来源

4 131 框中输入 男, 女 ( 逗号必须是英文状态的逗号, ), 同时选中 忽略空值 和 提供下拉箭 头 复选框, 设置如图 4-4 所示, 单击 确定 按钮 图 4-4 有效性设置 (4) 选择 E-I 列, 在如图 4-4 所示 设置 选项卡中的 允许 下拉列表框中选择 整数, 在 数据 下拉列表框中选择 介于, 在 最小值 框中输入 0, 在 最大值 框中输入 100; 在 输入信息 选项卡中, 标题 框中输入 成绩, 输入信息 框中输入 请输入 0~100 之间有效成绩 ; 在 出错警告 选项卡中, 标题 框中输入 错误, 错误信息 框中输入 成绩必须在 0~100 之间, 样式 列表中选择 停止, 单击 确定 按钮, 如图 4-5 所示 图 4-5 成绩有效性 删除标题 E1-I1 单元格的有效性设置 ( 这里的设置不合情理 ): 选择 E1-I1 单元格区域, 在如图 4-5 所示的数据有效性设置中, 单击 全部清除 按钮, 即可清除这里的有效性设置 将光标移到相关列, 在相关单元格中输入任意不符合要求的值, 观察有效性设置效果

5 132 计算机应用基础教程 4. 课堂实践 (1) 打开 填充.xls 文件, 按下列要求完成相应的操作 : 1 在 身份证号 列输入你的身份证号码, 观察作为文本和作为数值有何不同? 2 学号 列按末位递增 1 向下填充 ; 3 入学日期 列按月递增 1 向下填充 ; 4 高级班入学日期 列按年份递增 1 向下填充 ; 5 入学成绩 列按预测趋势向下填充 ; 6 将 姓名 列中 10 位同学添加到 ( 导入 ) 自定义序列中, 删除后 9 位同学, 向下拖动 A2 单元格的填充柄, 能否填充? 7 在 数据有效性 工作表中, 在 职务 列设置数据有效性条件, 当鼠标选择该列的任一单元格时, 在其右侧显示一个下拉列表框箭头, 并提供 处长 科长 科员 列表选项供用户选择 (2) 实践案例 1 5. 评价与总结 老师鼓励同学 ( 一般 2 位一起 ) 主动上台演示部分或全部操作, 增强学生学习的成就感 必要时老师点评 鼓励同学总结本案例相关知识点, 学生或老师补充 6. 课外延伸在 数据有效性 对话框的 设置 选项卡中 允许 列表框选择 自定义 选项, 如何实现案例 1 中的有效性要求? 试试看!( 提示 : 用到逻辑函数 and 及 or) 提示 : 显示或隐藏填充柄 : 单击 工具 选项 命令, 在 编辑 选项卡中选中或清除 单元格拖放功能 复选框 在这里还可设置回车后光标移动方向 输入分数时, 应在分数前加 0 和一个空格, 这样可以区别于日期 例如 : 输入 0 1/3 表示分数 1/3 在输入 二又三分之一 时, 输入 2 1/3 即可 如果要输入当天的日期, 可按下 Ctrl+; 组合键, 如果要输入当前时间, 可按下 Ctrl+Shift+; 组合键 任务二学生成绩表的统计与分析 在某门课程教学结束时, 教师通常都要对教学进行评价, 对学生的平时考查成绩 期末 成绩 平时表现分数等进行统计计算, 最后给每一位同学一个最终成绩或等级 这些繁琐的工 作其实利用 Excel 函数计算很容易就搞定了

6 133 案例 2 成绩计算与统计 场景描述 辅导员小张要对所带班级学生的学习情况进行摸底, 为下学期奖学金评定做准备工作 打开 成绩计算.xls 文件, 根据以下要求进行各种统计计算 : (1) 在 总分 列中计算每个同学所学课程的总分 (2) 在 平均成绩 行, 统计每门课程的平均分 (3) 在 E25,E26 中统计男生人数和女生人数 (4) 在 总评 A 列中, 根据总分给每一位同学评定一个等级, 标准是 : 总分在 350 分以上 ( 包括 350 分 ) 为 合格, 总分在 350 分以下为 不合格 (5) 在 总评 B 列中, 评定等级标准是 : 总分在 390 分以上 ( 包括 390 分 ) 为 A, 总分在 390 分以下为 C, 总分大于等于 350 且小于 390, 等级为 B (6)E27,E28 分别统计男生高数总分及女生高数总分, 并进行验证 高数总分 = 男生高数总分 + 女生高数总分 是否成立 1. 案例分析 Excel 系统提供大量的函数, 给人们计算带来了极大方便 函数 Sum() 及 Average() 可以完成 (1) (2) 中的任务 ; 函数 Count() 与 Countif() 可以进行计数和有条件的计数 ;if() 函数可以进行逻辑判断, 根据判定的不同结果从而返回不同的值 Sumif() 函数可以进行有条件的求和, 如 (6) 中的要求 2. 相关知识点 Excel 提供了大量的系统函数, 功能非常丰富 按照其功能来划分主要有统计函数 日期与时间函数 数学与三角函数 财务函数 逻辑函数 文本函数 数据库函数等 在输入函数时, 必须以 = 开头, 输入函数的一般格式为 : = 函数名 ( 参数 1, 参数 2, ) (1) 常用函数 1 SUM(number1,number2,...), 返回参数之和 其中 :number1,number2,... 为 1 到 30 个需要求和的参数, 可以是数字 逻辑值及数字的文本表达式或数组 如 :=SUM(1,3,5);=SUM(1,A2:D10,TRUE) 等 2 AVERAGE(number1,number2,...), 返回参数的平均值 其中 :number1,number2,... 可以是数字, 也可以是包含数字的名称 数组或引用 如 :=AVERAGE(A1:D10,5) 3 COUNT(value1,value2,...), 返回包含数字以及包含参数列表中的数字的单元格的个数 其中 :value1,value2,... 为包含或引用各种类型数据的参数 (1 到 30 个 ) 说明 : 只有数字类型的数据才被计算 如 :=COUNT(1,3,"abc") 返回函数值 2 4 COUNTA(value1,value2,...), 返回参数列表中非空值的单元格个数

7 134 计算机应用基础教程 5 COUNTIF(range,criteria), 返回区域中满足给定条件的单元格的个数 其中 :range 是统计的单元格区域 ; criteria 为确定哪些单元格将被计算在内的条件 其形式可以为数字 表达式或文本 例如, 条件可以表示为 32 "32" ">32" 或 "apples" 等 6 SUMIF(range,criteria,sum_range), 返回满足给定条件的若干单元格的和 其中 :range 为用于条件判断的单元格区域 ; criteria 为确定哪些单元格将被相加求和的条件 ; sum_range 是需要求和的实际单元格 ( 区域 ) (2) 常用数学函数 1 ROUND(number,num_digits), 返回某个数字按指定位数进行四舍五入后的数字 其中 :number 是要进行四舍五入的数字,num_digits 指定位数, 按此位数进行四舍五入 如 :=ROUND(12.75,1) 对 四舍五入保留一位小数返回值 12.8;=ROUND(12.75,-1) 返回值 10;=ROUND(12.75,0) 返回值 13 2 TRUNC(number,num_digits), 返回某个数字按指定位数截取后的数字 其中 :number 是需要截取的数字,num_digits 指定位数, 按此位数进行截取 num_digits 的默认值为 0, 则返回截断小数位后的整数 如 :=TRUNC(12.6) 返回值 12;=TRUNC(-12.6) 返回值 RAND(), 返回大于等于 0 及小于 1 的均匀分布随机数, 每次重新计算时都将返回一个新的数值 随机抽题 抽奖等可能用到此函数 如 :=RAND(), 返回介于 0 到 1 之间的一个随机数 ( 变量 );=RAND()* , 返回大于等于 100 但小于 200 的一个随机数 ( 变量 ) 常用数学函数还有取整函数 INT 平方根函数 SQRT 绝对值函数 ABS 幂函数 POWER 等, 详细请参考 Excel 帮助中的相关实例 ( 在 Excel 中, 按 F1 键后在 搜索 框中输入关键字, 回车 ) (3) 逻辑函数 1 IF(logical_test,value_if_true,value_if_false), 执行真假值判断, 根据逻辑计算的真假值, 返回不同结果 其中 :logical_test 表示计算结果为 TRUE 或 FALSE 的任意值或表达式 ; value_if_true 是 logical_test 为 TRUE 时返回的值,value_if_false 是 logical_test 为 FALSE 时返回的值 如 :=if(5>3, "Y","N") 返回 Y, 而 =if(5>30, "Y","N") 返回 N 2 AND(logical1,logical2,...), 所有参数的逻辑值为真时, 返回 TRUE; 只要有一个参数的逻辑值为假, 即返回 FALSE 如 :=AND(5>3, 100>50) 返回 TRUE, 而 :=AND(5>3, 100>500) 返回 FALSE 3 OR(logical1,logical2,...), 所有参数的逻辑值均为 FALSE 时, 才返回 FALSE; 只要有一个参数的逻辑值为真, 即返回 TRUE 如 :=OR(5>3, 100>500) 返回 TRUE, 而 =OR(5>30, 100>500) 返回 FALSE

8 135 符串 (4) 文本与数据函数 1 LEN(text), 返回文本字符串中的字符数 如 :=len("abcd") 返回 4,=LEN(" 英语 abc") 返回 5 2 MID(text,start_num,num_chars), 返回文本字符串中从指定位置开始的特定数目的字 其中 :text 是包含要提取字符的文本字符串,start_num 是文本中要提取的第一个字符的 位置,num_chars 指定希望 MID 从文本中返回字符的个数 字格式 如 :=MID(" 英语 abc123",3,3) 返回 abc 3 TEXT(value,format_text), 将数值转换为按指定数字格式表示的文本 其中 :value 为数值 或计算结果为数字公式或对包含数字值的单元格的引用 ; Format_text 为 单元格格式 对话框的 数字 选项卡的 分类 框中的文本形式的数 如 :=TEXT(20000,"$0.00"), 返回值 $ VALUE(text), 返回由数字文本字符串转换成的数字 其中 :text 为带引号的文本, 或对需要进行文本转换的单元格的引用 说明 :text 可以是 Microsoft Excel 中可识别的任意常数 日期或时间格式 如果 Text 不 是这些格式的数据, 则函数 VALUE 返回错误值 #VALUE! 如 :=VALUE("$1,000"), 返回数值 1000 常用的文本函数还有 LOWER( 将文本转换成小写形式 ) UPPER( 将文本转换成大写形 式 ) TRIM( 除了单词之间的单个空格外, 清除文本中所有的空格 ) 等, 更多文本函数请参考 帮助 文本与数据函数 中有关函数 3. 实现方法 (1) 将光标定位于 J2 单元格中, 单击工具栏上的 自动求和 按钮, 回车 再拖动 单元格 J2 右下角的填充柄直到 J22 单元格 (2) 将光标定位于 E23 单元格中, 单击数据编辑栏上的插入函数 f x 按钮, 在弹出的 插入函数 对话框中, 在 选择类别 列表框中选择 常用函数, 在 选择函数 框中选 择 AVERAGE, 确定 后弹出 函数参数 对话框, 如图 4-6 所示 单击 Number1 框右边 的按钮, 圈选求平均值的单元格区域后回车 ( 或再次单击按钮 ), 确定 即可 将公式 横向填充到 F23:I23 即可 (3) 将光标定位于 E25 单元格中, 单击数据编辑栏上的插入函数 f x 按钮, 弹出 插 入函数 对话框, 在 搜索函数 框中输入 countif 后单击 转到 按钮, 选择 COUNTIF 后 确定, 如图 4-7 所示 在弹出的 函数参数 对话框的 Range 框中圈选 D2:D22, 在 Criteria 框中输入 男 或单击性别为男的单元格 ( 如 D2), 确定 同样, 在 E26 中可以计算出女 同学的人数 (4) 在单元格 K2 中插入 IF 函数, 在 Logical_test 输入判定条件 J2>=350, 计算结果 可能为 TRUE 或 FALSE, 在 Value_if_true 框中输入判定条件为 TRUE 时返回值 合格, 在 Value_if_false 框中输入判定条件为 False 时返回值 不合格, 如图 4-8 所示

9 136 计算机应用基础教程 图 4-6 函数参数 图 4-7 插入函数 图 4-8 if 函数 (5) 在 总评 B 列中, 将总分分成了三段, 总评 B 有三个值 (A B C), 而一个 if 函数只能 一分为二, 所以在这里要用两个 if 套在一起来解决 一分为三 的问题 操作步骤 : 在 L2 单元格中, 插入 IF 函数, 在 Logical_test 输入 J2>=390,Value_if_true 框中输入 A, 光标定位在 Value_if_false 框中, 单击数据编辑栏最左边的 IF 函数, 又弹出新的 函数参数 对话框, 在从上到下的三个框中分别输入 J2>=350 B C, 单击 确

10 137 定 按钮 将公式向下填充到其他单元格即可 其实, 也可以直接在单元格中输入公式 =IF(J2>=390,"A",IF(J2>=350,"B","C")) (6) 在单元格 E27 中, 插入 SUMIF 函数, 在弹出的 函数参数 对话框的 Range 框中, 圈选包括 性别 列及 高数 列在内的数据区域 ( 范围圈大一点也没关系 ), 在 Criteria 框输入 男, 在 Sum_range 框中输入实际求和的单元格区域 E2:E22, 确定 计算结果为 777, 如图 4-9 所示 同理, 在 E28 中计算女生高数总分为 770 经验证, 式子 高数总分 = 男生高数总分 + 女生高数总分 两边结果都是 1447, 说明上述两个函数的计算过程是正确的 图 4-9 sumif 函数 Excel 内容十分丰富, 函数功能很强 在学习或操作过程中有疑难问题时, 查询系统帮助是一个非常有效的解决问题的办法 查询帮助方法如下 : 在 Excel 窗口中, 单击 帮助 命令, 弹出 Excel 帮助窗格, 在 搜索 框中输入要查询问题的关键字 ( 如 SUMIF), 按回车键或单击右边的 开始搜索 按钮, 如图 4-10 所示 在 搜索结果 窗格中单击相关主题即可 ( 如 SUMIF 工作簿函数 ) 也可以在 Excel 帮助窗格中单击 目录, 在 目录 列表框中选择要查找的主题 如查询相关函数帮助 : 单击 目录 使用数据 函数引用, 在这里可查看任何函数帮助信息, 如图 4-11 所示 图 4-10 帮助窗格图 4-11 帮助目录

11 138 计算机应用基础教程 4. 课堂实践 (1) 实践本例, 并总结解决问题思路 (2) 打开 工资表 1.xls 文件, 在 Sheet1 工作表 D 列的左边插入一空列, 在插入列首单元格中输入栏目名 部门名称, 并根据 部门代码 用 IF 函数填入部门名称, 其中 : 部门代码 A02 表示 技术部, 部门代码 B01 表示 生产部, 部门代码 B03 表示 销售部 ; 根据部门代码, 填写部门名称 5. 评价与总结 第一组同学 (2 位为一组 ) 主动上台演示 (1)~(3) 第二组同学主动上台演示 (4)~(6) 鼓励同学总结本案例解决思路, 学生或老师补充 6. 课外延伸时间日期函数在计算 统计中也常用到 ; 信息函数在排除错误时也会用到 下面介绍最常用的几个, 以示说明 (1) 时间日期函数 1 Now(), 返回当前日期和时间所对应的序列号或日期时间数据 ( 与单元格格式有关 ) 如 :=NOW() 说明 :Microsoft Excel 可将日期存储为可用于计算的序列号 默认情况下,1900 年 1 月 1 日的序列号是 1, 每向后一天序号加 1 序列号中小数点右边的数字表示时间, 左边的数字表示日期 2 TODAY(), 返回当前日期的序列号或日期格式数据 如 = TODAY() 3 DATE(year,month,day), 返回代表特定日期的序列号 如果在输入函数前, 单元格格式为 常规, 则结果将显示为日期数据 如 :=date(2010,11,12) 4 YEAR(serial_number), 返回某日期对应的年份 如 =year(now()) 与 year 函数具有相同参数的日期时间函数还有 month day hour minute time 函数 (2) 信息函数信息函数可以用于对工作表中的单元格数据类型 错误类型等进行判断, 从而采取不同处理手段 如在 D1 中有公式 :=AVERAGE(A1:C1), 而 A1:C1 单元格区域中为空, 这时在 D1 单元格中出现了错误信息 #DIV/0!, 它提示 0 不能作为除数, 如图 4-12 所示 这是因为 A1:C1 单元格区域中每一个单元格都为空, 所以在计数时为 0, 而 0 不能作为除数, 一旦在相关单元格中输入数值, 错误就没有了 要排除这种显示错误, 需用到信息函数 在 D1 中可以输入函数 :=IF(ISERROR(AVERAGE(A1:C1))," ",AVERAGE(A1:C1)), 这样就让错误显示为空, 这种方法在设计 Excel 计算模板中用得较多 1 ISERROR(VALUE), 当 VALUE 为任意错误值时返回 TRUE 如 :=ISERROR(D1), 返回 TRUE

12 139 图 4-12 错误信息 2 ISNUMBER(VALUE), 当 VALUE 为数字时返回 TRUE 3 ISTEXT(VALUE), 当 VALUE 为文本时返回 TRUE 其他信息函数请参考帮助 (3) 课外练习 1 打开 gzb.xls, 在 Sheet1 中 编号 列右边插入一列, 将 编号 列数据转换成文本类型数据 ( 用 text 函数 ), 将转换后的数据粘贴到相应单元格中 ; 在 出生年份 列计算员工的出生年份 2 请打开工作簿文件 tjb.xls, 并按指定要求完成如下操作 : A. 在 I1 单元格求出年龄不超过 45 岁 ( 包括 45 岁 ) 的人数 ; B. 在 I2 单元格求出年龄不超过 45 岁 ( 包括 45 岁 ) 的人数占全体员工的百分比, 并设置该单元格的数字格式为百分比, 小数位数为 0; C. 在 I3 单元格中求出平均年龄并使用 ROUND 函数取整 ; D. 在 I4 单元格中求出教授人数 案例 3 成绩分析 场景描述 辅导员张老师除了要完成上述案例 1 中任务外, 还要对班级的各位同学的总分进行排名, 计算各门课程的男女生的平均分, 来分析男女学生在学习各门课程时的成绩差异 打开 成绩计算 2.xls, 根据要求完成以下任务 : (1) 由于 两课 任课教师改卷时统计错误, 每位同学少加 10, 请给每位同学该课成绩加上 10 分, 并交换姓名与学号两列数据的次序 ( 即姓名设为第 1 列, 学号设为第 2 列 ) (2) 统计每名学生本学期所取得的总学分 (3) 统计每名学生总分在全班同学中所占名次 ( 按降序方式计算 ) (4) 在工作表的相应单元格中统计各门课程的男女的平均分 1. 案例分析案例 (1) 中涉及插入列 删除列 复制 复制移动操作 使用公式在原来分数的基础上, 加上 10, 然后用选择性粘贴 ( 选择值 ) 粘贴到 两课 列即可 案例 (2) 在公式中采用绝对引用计算, 然后再填充即可 案例 (3) 使用 Rank 函数可用来计算排名问题, 不过要注意正确使用相对引用和绝对引用 案例 (4) 中需要自己输入公式, 如男生某门课程平均分等于男生总分除以男生人数, 注意正确使用相对引用与绝对引用, 以便应用公式填充 2. 相关知识点 (1) 复制 移动单元格 ( 区域 ) 中的内容 1) 选择单元格区域

13 140 计算机应用基础教程 1 选择连续单元格区域 : 单击单元格区域左上角的单元格 +Shift 键 + 单击单元格区域右下角的单元格 2 选择不连续单元格区域 : 选择第 1 个单元格区域 +Ctrl 键 + 选择下一个单元格区域 3 选择工作表的行 ( 列 ) 区域 : 鼠标在行号 ( 列号 ) 拖动即可 4 选定整个工作表 : 单击行号和列号左上角的交叉处 2) 选择性粘贴复制内容时, 有时需要复制全部内容, 有时仅需要复制单元格的格式 公式 值 有效性验证等, 有时需要将内容进行转置 ( 原来行变成复制后的列, 列变成复制后的行 ), 这时就要用到选择性粘贴 在粘贴时, 单击 编辑 选择性粘贴 命令 在 选择性粘贴 对话框中选择相关选项即可, 如图 4-13 所示 图 4-13 选择性粘贴 (2) 公式公式由 = 开头, 后跟由运算符 函数 常量 单元格引用等构成的有意义的表达式 如 =sum(a1:c5)/10+2^3, 这里 ^ 表示乘方运算 提示 : 公式中除使用常用算术运算符外, 还有文本运算符及关系运算符等 文本运算符 & ( 字符串连接符 ), 如 ="abc"&"def", 结果为 abcdef 关系运算符 :< <= > >= <>( 不等于 ), 如 5<>6, 比较的结果为 TRUE (3) 单元格的引用公式中对单元格的引用分为绝对引用 相对引用和混和引用, 在不同的应用中将视情况选择不同的单元格引用方式 1 单元格的绝对引用 : 是指对含有公式的单元格进行复制, 不论复制到什么位置, 公式中所引用的单元格地址都不发生变化 如公式 :=SUM($E$2:$I$2), 就是对单元格的绝对引用实例 绝对引用单元格地址的列号和行号前都加 $ 符号, 如 $E$2 $A$1 等 2 单元格的相对引用 : 是指将包含公式的单元格复制到其他单元格时, 公式中所引用的单元格地址也随之发生变化 例如, 在任务 2 的案例 1 中, 将工作表中 J2 单元格中的计算公式 SUM(E2:I2) 复制到 J10 单元格中就变成了 SUM(E10:I10)

14 141 相对引用的公式被复制到其他单元格时, 公式所在单元格与引用单元格之间仍保持行列 相对位置关系不变, 相当于对单元格做平移操作 于是相对引用单元格地址变化有如下规律 : 原行号 + 行地址偏移量 新行地址 原列号 + 列地址偏移量 新列地址 3 单元格的混合引用 : 是指在公式引用的单元格地址中, 既有绝对地址引用又有相对地 址引用 如本例单元格 K2 中有公式 =SUM(N$3:R$3), 当将公式复制到 L2 中时, 公式将变 为 =SUM(O$3:S$3), 对单元格列的引用为相对引用, 对行的引用为绝对引用 公式复制时, 相当于向右平移一个单元格的位置, 绝对引用的地址不变 在编写公式时, 若希望公式在复制时单元格行号不发生改变, 则在公式引用的单元格地 址行号前加一个美元符号 ($); 若希望复制时单元格列号不发生改变, 则在公式引用的单元格 地址列号前加一个美元符号 ($); 若希望公式复制时单元格行号 列号都不发生改变, 则在公 式引用的单元格地址行号 列号前都加一个美元符号 ($); 若希望公式复制时单元格行号 列 号都发生改变, 则在公式引用的单元格地址行号 列号前都不加美元符号 ($) 3. 实现方法 (1) 在 两课 右边插入一空白列, 在 G2 中输入公式 =F2+10, 然后双击 G2 单元格 的填充柄, 将公式填充到 G3:G22 区域中 选择单元格区域 G2:G22, 单击 编辑 复制 命 令, 选择 F2 单元格, 单击 编辑 选择性粘贴 命令, 在弹出的如图 4-13 所示 选择性粘贴 对话框中单击 值 选项, 单击 确定 按钮 选择 学号 列, 将鼠标移到选定列的边缘, 按下左键将该列数据移动到 G 列, 将姓名 列移动到 A 列, 再将 G 列 ( 学号 ) 内容移到 B 列 删除 G 空白列 (2) 在 K2 中输入公式 =SUM($N$3:$R$3), 拖动填充柄向下填充直到 K22 即可 由 于填充时, 列未发生变化, 所以公式也可以改写为 =SUM(N$3:R$3) (3) 单击 J2 单元格, 单击数据编辑栏上的插入函数 f x 按钮, 弹出 插入函数 对 话框, 在 搜索函数 框输入 RANK, 单击 转到 按钮, 确定 后弹出 函数参数 对话 框, 如图 4-14 所示 由于公式在填充时, 总分随着学生而变, 而每个分数都要与全体分数相 比较, 所以在 Number 中使用相对引用,Ref 中行号使用绝对引用, 当然列号也可使用绝对引 用 Order 框中输入 0 或省略按降序算, 确定 后将公式填充到其他单元格即可 图 4-14 Rank 函数参数

15 142 计算机应用基础教程 (4) 选择单元格 D24, 单击数据编辑栏上的插入函数 f x 按钮, 弹出 插入函数 对 话框, 在 搜索函数 框输入 SUMIF, 单击 转到 按钮, 确定 后弹出 函数参数 对话 框 为了能进行横向公式填充, 所以在 Range 框中必须输入 $C1:$H22 或 $C$1:$H$22, 即单元 格列地址必须使用绝对引用 ; 同理, 在 Criteria 框中输入 $C$4; 在 Sum_range 框中输入 D2:D22 ( 因为计算不同科目时, 列要发生变化 ), 如图 4-15 所示 确定 后, 再编辑 D24 中的公式, 在编辑栏中的公式后接着输入 /COUNTIF($C1:$C22,$C$4) 回车即可 然后, 再将公式填充 到 E24:H24 区域中 同理, 在 D25 中可直接输入公式 : =SUMIF($C1:$H22,$C$7,D1:D22)/COUNTIF($C1:$C22,$C$7) 进行计算, 然后再填充 图 4-15 混合引用 4. 课堂实践打开 成绩计算 2.xls 文件, 按本案例各项要求, 完成各步计算 5. 评价与总结 第一组同学 (2 位为一组 ) 主动上台演示 (1)~(2) 操作, 并找同学点评 ; 第二组同学上台演示 (3)~(4) 操作, 同学或老师点评 ; 鼓励同学总结本案例主要知识点, 学生或老师补充 6. 课外延伸打开工作簿文件 cfb.xls, 用公式完成绘制九九乘法表 : 在 B2:J10 区域制作, 要求在 B2 单元格输入公式, 然后复制到其他单元格. 案例 4 成绩表格式化 场景描述 张老师对成绩表的计算 统计等工作都已完成, 他还想对成绩表进行格式化工作, 如设置单元格数据格式 字体 对齐方式 边框底纹等, 还需要将那些不及格的同学用红颜色标示出来 打开 成绩表 3.xls 工作簿文件, 按照张老师格式化工作表的要求完成如下设置 : (1) 在表头的上方插入一行空行, 在 A1 中输入 学生成绩统计表 作为表标题, 标题

16 143 设置为黑体 蓝色 字号大小 16 合并单元格区域 A1:K1 水平居中, 单元格底纹为浅青绿 图案样式为 细对角线条纹 条纹颜色为金色 (2) 字段名区域格式设置 : 宋体 加粗 字号大小 12, 对齐方式 : 水平 垂直居中 ;A2:K23 区域对齐方式设置为 : 水平 垂直居中 ; 区域 B3:B23 水平对齐方式为 : 分散对齐 (3) 单元格区域 E3:J23 中的数据保留一位小数,D3:D23 单元格区域数据按日期型 样式显示 (4) 给区域 A2:K23 添加红色粗实线外边框, 内部为蓝色细实线 (5) 将单元格区域 E2:I23 中小于 60 的数据用红色显示, 将需要补考同学的姓名用红色显示, 以方便浏览 (6) 将 2~23 行的行高设置为 22,D 列列宽设置为 14 (7) 将当前工作表的标签名重命名为 学生成绩统计表, 设置工作表标签颜色为红色, 复制 学生成绩统计表 到 成绩计算 2.xls 中去, 放置在所有工作表之后 1. 案例分析本案例的前四个问题主要是单元格 ( 区域 ) 的格式化问题, 在 单元格格式 对话框中可以完成 ;(5) 中主要是条件格式问题 ; 最后一个问题是对工作表进行复制 移动等操作 2. 相关知识点 (1) 单元格的格式化单击 格式 单元格 命令, 弹出 单元格格式 对话框, 如图 4-16 所示 在该对话框中可以对单元格 ( 区域 ) 数据的数字显示方式 对齐方式 字体 边框 图案等进行设置 图 4-16 单元格格式 数字格式 : 在 数字 选项卡中, 可为单元格数据设置显示格式, 如 : 常规 数值 会计专用 日期 文本 特殊等格式, 还可自定义显示格式, 如图 4-16 所示 对齐方式 : 在 对齐 选项卡中, 可为单元格数据设置对齐方式, 如 : 水平对齐 垂直对齐 自动换行 合并单元格等

17 144 计算机应用基础教程 (2) 条件格式就是根据本单元格或其他单元格中的数据是否满足一定条件来设置单元格的数据格式, 如将单元格区域 E2;I23 中小于 60 的数据用红色显示, 若单元格内数据值小于 60, 则字体显示为红色, 否则不变 操作步骤 : 选择要设置的单元格 ( 区域 ) 单击 格式 条件格式 命令 在 条件格式 对话框中, 构造要满足的条件 单击 格式 按钮 在弹出的对话框中设置字体 边框 图案等, 如图 4-17 所示 在 条件格式 对话框中, 通过 添加 删除 按钮可以添加 删除条件 若是由其他单元格的值来设置当前单元格的条件格式, 必须在 单元格数值 所在的下拉列表框中选择 公式, 当 公式 计算结果为 TRUE 时, 当前单元格才设置为所要的格式 详见下面的实现方法 图 4-17 条件格式 3. 实现方法 (1) 选择 成绩表 的第一行 ( 单击行号 ), 单击 插入 行 命令, 然后在 A1 单元格中输入 学生成绩统计表 ; 选择单元格区域 A1:K1 单击 格式 单元格 命令, 弹出 单元格格式 对话框, 如图 4-16 所示 在 字体 选项卡中设字体为黑体 蓝色 字号 16; 在 对齐 选项卡中的 文本对齐方式 的 水平对齐 下拉列表框中选择 居中, 在 文本控制 选项组选中 合并单元格, 如图 4-18 所示 ; 在 图案 选项卡中, 在 颜色 面板中选择 浅青绿, 在 图案 下拉面板的上方选择 细对角线条纹 样式, 在下方选择 金色, 如图 4-19 所示, 确定 即可 图 4-18 单元格格式 对齐

18 145 图 4-19 单元格格式 图案 (2) 选择字段名区域 A2:K2 单击右键 单击快捷菜单中的 设置单元格格式 命令 在 单元格格式 对话框的 字体 选项卡中设置字体 : 宋体 加粗 字号大小 12; 在 对齐 选项卡中设置文本对齐方式 : 水平对齐 居中 垂直对齐 居中 ; 选择单元格区域 B3:B23, 同前面操作一样, 在 对齐 选项卡中的 水平对齐 选项中, 从下拉列表框中选择 分散对齐 (3) 选择单元格区域 E3:J23 单击右键, 单击 设置单元格格式 命令 在 单元格格式 对话框的 数字 选项卡的 分类 列表中选择 数值, 在 小数位数 框中设置为 1, 如图 4-16 所示 选择单元格区域 D3:D23, 同样在 单元格格式 对话框 数字 选项卡的 分类 列表中选择 日期, 在右边 类型 框中选择 , 确定 (4) 选择单元格区域 A2:K23, 在如图 4-16 所示的 单元格格式 对话框中, 选择 边框 选项卡, 在 线条 框的 样式 中选粗实线, 在 颜色 列表框中单击红色, 单击 外边框 按钮, 同样, 选择蓝色细实线, 单击 内部 按钮, 单击 确定 按钮 (5) 选择单元格区域 E2:I23 单击 格式 条件格式 命令 在 条件格式 对话框的第 1 个列表框中选择 单元格数值, 在第 2 个列表框中选择运算符 小于, 在第 3 个框中输入 60 单击 格式 按钮 在 单元格格式 对话框的 字体 选项卡的 颜色 框中选择红色 确定, 如图 4-17 所示 选择姓名数据区域 B3:B23 在如图 4-20 所示 条件格式 对话框中, 在第 1 个列表框中选择 公式, 在第 2 个文本框内输入 =OR(E3<60,F3<60,G3<60,H3<60,I3<60), 单击 格式 按钮 在 单元格格式 对话框的 字体 选项卡的 颜色 框中选择红色, 如图 4-20 所示 确定 当公式 =OR(E3<60,F3<60,G3<60,H3<60,I3<60) 结果为 TRUE 时 ( 只要有一门不及格 ), 某同学的名字即显示为红色 (6) 选择行区域 2~23 行 单击 格式 行 行高 命令 在 行高 对话框中输入 22 ; 同样, 选择 D 列 单击 格式 列 列宽 命令 在 列宽 对话框中输入 14 确定 (7) 打开 成绩计算 2.xls, 右键单击 成绩计算 3 的 成绩表 标签 单击 重命名 命令, 输入 学生成绩统计表 右键单击 学生成绩统计表 标签 单击快捷菜单 移动或

19 146 计算机应用基础教程 复制工作表 命令 在 工作簿 列表框中选择 成绩计算 2.xls, 在 下列选定工作表之前 框中选择 ( 移至最后 ) 选项 选中 建立副本 选项 确定, 如图 4-21 所示 图 4-20 条件格式 公式 图 4-21 移动或复制工作表 右键单击 学生成绩统计表 标签 单击 工作表标签颜色 命令 在 设置工作表标签颜色 对话框中, 选择红色 确定 4. 课堂实践 (1) 实践案例 (1)~(4) (2) 实践案例 (5)~(7) 5. 评价与总结 第一组同学 (2 位为一组 ) 主动上台演示 (1)~(4) 操作中部分操作 第二组同学上台演示 (5)~(7) 全部或部分操作, 同学或老师点评 鼓励同学总结本案例主要知识点, 学生或老师补充 6. 课外延伸 (1) 打开电子工作簿文件 sjb.xls, 按以下要求完成相关操作 : A. 在单元格 F3 内计算年收入在 元及以下者扣税金 3%, 年收入在 80001~ 元者扣税金 6%, 年收入在 元及以上者扣税金 10%, 然后复制到 F4:F17, 税金 的数值取小数点后 2 位

20 147 B. 单元格区域 A1:F1 跨行居中, 字体设置 : 隶书 加粗 大小 16 C. 区域 A2:F17 添加红色双实线外边框, 内部蓝色细实线, 对齐方式 : 水平对齐 垂直对齐均为居中 D. 将年收入大于等于 10 万的姓名用红色显示, 年收入小于 8 万的姓名用蓝色显示 (2) 电子工作簿文件 okbs.xls 是某公司举行卡拉 OK 大奖赛评分数据清单 : A. 打开工作簿文件 OKBS.XLS, 格式化要求 :A2:K12 区域 : 字体 : 黑体, 字号 :16, 粗体, 水平对齐方式为居中 B. 按照比赛规则, 去掉一个最高分 去掉一个最低分, 剩余分数的平均分作为选手的最后得分, 用函数计算, 最后得分保留两位小数 C. 当评委的分值比 8 个评委的平均数高 10% 以上, 则该评委的分值以红色显示 ; 当评委的分值比 8 个评委的平均数低 10% 时, 则该评委的分值以蓝色显示 任务三投资理财 Excel 中提供了功能齐全的财务函数, 利用这些函数可轻松解决如存款与借款终值的计算 问题 房屋贷款计算问题 年利率的计算问题 本利与折现问题等 案例 5 投资理财与贷款计算 场景描述 邻居小王是某公司的一名注册会计师, 公司为了扩大业务规划新建一栋办公楼, 需要向当地银行贷款, 领导要他计算公司的月还款金额 ; 平时同事进行一系列的投资理财活动, 也总向他请教一些问题, 问题归纳如下 : (1) 按当前年利率 5% 计算, 且假定年利率保持不变, 公司贷款 1000 万元,10 年还清, 问平均每月还款多少万元 (2) 同事老张进行投资理财活动, 按照银行零存整的年利率为 3%, 老张账户现有 10 万元, 以后每月初存入 2000 元, 问 5 年后连本带息能拿多少 ( 扣除 20% 的利息税 )? (3) 同事老李进行投资炒房, 银行贷款的年利率为 6%, 老李以后每月能还款 5000 元, 10 年还完, 按这样的偿还能力, 问老李现在能从银行贷款多少? 1. 案例分析在本案例中, 可应用 PMT() 函数计算基于固定利率及等额分期付款方式, 返回贷款的每期付款额来解决 (1) 中的问题 FV() 函数可用于解决基于固定利率及等额分期付款方式, 返回某项投资的未来值, 可解决 (2) 中的问题 PV() 函数可用于返回投资的现值 现值为一系列未来付款的当前值的累积和, 可解决 (3) 中的问题 2. 相关知识点 (1)PV(Rate,Nper,Pmt,Fv,Type) 函数

21 148 计算机应用基础教程 返回投资的现值 现值为一系列未来付款的当前值的累积和 例如, 借入方的借入款即为贷出方贷款的现值 Rate 为各期利率 例如, 如果按 10% 的年利率借入一笔贷款来购买汽车, 并按月偿还贷款, 则月利率为 10%/12( 即 0.83%) Nper 为总投资 ( 或贷款 ) 期, 即该项投资 ( 或贷款 ) 的付款期总数 例如, 对于一笔 4 年期按月偿还的汽车贷款, 共有 4*12( 即 48) 个偿款期数 Pmt 为各期所应支付的金额, 其数值在整个年金期间保持不变, 如每月支付 2000 元, 则 Pmt 值为 PMT 的符号 : 支出为负, 收入为正 Fv 为未来值, 或在最后一次支付后希望得到的现金余额, 如果省略 Fv, 则假设其值为零 例如, 如果需要在 18 年后支付 $50,000, 则 $50,000 就是未来值 Type 数字为 1 或 0, 用以指定各期的付款时间是在期初还是期末 0 或省略表示期末支付, 1 表示期初支付 (2)PMT(Rate,Nper,Pv,Fv,Type) 函数基于固定利率及等额分期付款方式, 返回贷款的每期付款额 参数详细信息同 PV() 函数 (3)FV(Rate,Nper,Pmt,Pv,Type) 函数基于固定利率及等额分期付款方式, 返回某项投资的未来值 参数详细信息 PV() 函数 3. 实现方法 (1) 打开电子工作簿文件 投资理财.xls, 光标定位于 B8 单元格中, 单击数据编辑栏上的插入函数 f x 按钮, 弹出 插入函数 对话框, 在 搜索函数 框中输入 PMT 单击 转到 按钮, 单击 确定 按钮 在弹出的 函数参数 对话框的 Rate 框中输入 A4/12, 在 Nper 框中输入 A5*12, 在 PV 框中输入 1000 或 A6, 如图 4-22 所示, 单击 确定 按钮 图 4-22 PMT 函数 (2) 光标定位于 B19 中, 单击数据编辑栏上的插入函数 f x 按钮, 弹出 插入函数 对话框, 在 搜索函数 框中输入 FV, 单击 转到 按钮, 确定 在弹出的 函数参数

22 149 对话框的 Rate 框中输入 A14/12, 在 Nper 框中输入 A15*12, 在 PMT 框中输入 -2000, 在 Type 框中输入 1, 如图 4-23 所示, 单击 确定 按钮 在 B20 中, 输入公式 =A *5*12+(B19-A *60)*0.8 ( 即成本 +80% 的利息 ), 即可算出税后本息总数 图 4-23 FV 函数 (3) 光标定位于 B30 中, 单击数据编辑栏上的插入函数 f x 按钮, 弹出 插入函数 对话框, 在 搜索函数 框中输入 PV, 单击 转到 按钮, 单击 确定 按钮 在弹出的 函数参数 对话框的 Rate 框中输入 A26/12, 在 Nper 框中输入 A27*12, 在 PMT 框中输入 -A28, 如图 4-24 所示, 单击 确定 按钮 图 4-24 PV 函数 4. 课堂实践实践本案例 5. 评价与总结 鼓励同学主动报告各小题的计算结果, 如果不正确错在什么地方?

23 150 计算机应用基础教程 鼓励同学总结本案例主要知识点, 学生或老师补充 6. 课外延伸 (1) 访问某企业财务室, 咨询他们平时在实际财务工作中还常用到哪些财务函数, 试列举一个案例 (2) 研究 IPMT() 函数 ISPMT() 及 NPV() 的用法及意义, 试举例 任务四产品销售数据管理 Excel 系统不仅具有丰富的计算和表格功能, 还提供了方便快捷的数据管理功能 如对数 据清单进行排序 筛选 分类汇总等 案例 6 产品销售数据统计与分析 场景描述 乐佳电器公司主要经营各种家用电器业务, 销售业务遍及广东省的东南西北各地区 公司总经理为了及时掌握各个业务员及各种电器的销售情况, 不时会要求销售部管理人员进行数据统计汇总等数据管理 分析工作 经常性数据分析管理工作可以归纳为以下几个方面 : 电子工作簿文件 电器销售表.xls 是公司三 四季度的电器产品销售数据清单, 打开此文件按下面要求完成各项任务 : (1) 对销售表进行排序, 要求 : 主关键词为 地区, 地区按自定义排序次序 东部 南部 西部 北部 升序排列, 次关键词为 产品 降序, 第三关键词为 季度 升序, 在 Sheet1 中完成 (2) 分别按 产品 和 销售员 对不同产品的订购量及不同销售员的订购量进行统计汇总, 并将汇总数据复制到 Sheet2 工作表的空白地方, 在 Sheet2 中完成 (3) 用自动筛选筛选出订货时间在 ~ 时间段的电视机订货记录, 在 Sheet3 中完成 (4) 用高级筛选筛选出订货时间在 ~ 时间段且地区为西部的订货记录, 在 Sheet4 中完成, 条件区域放在以 I1 为左上角的连续区域中 (5) 用高级筛选筛选出姓 李 的或姓 刘 的销售员在四季度里的订货记录, 要求 : 筛选条件放在以 I1 为左上角的单元格区域中, 筛选记录放在以 I6 为左上角的连续单元格区域中, 在 Sheet5 中完成 (6) 创建透视表以反映不同地区不同月份不同产品的订购量情况, 要求 : 将地区放在页上, 订货日期 销售员放在行上, 产品字段放在列上, 订货量放在数据上, 汇总方式为 求和, 位置为新工作表, 并对订货字段按月组合, 透视表改名为 销售数据透视表 (7) 页面设置与打印要求 : 自定义打印区域为 A1:H75, 打印的每一页数据都要有列标题, 缩放比例为 90, 自定义页眉 第 3 4 季度电器销售表, 自定义页脚 第 X 页共 Y 页, 打印预览, 观察设置效果

24 案例分析本案例主要涉及到数据清单的排序 自动筛选 高级筛选 分类汇总及透视表的相关功能 利用 数据 菜单下的 排序 筛选 分类汇总 数据透视表和数据透视图 命令可以完成本案例中的任务 2. 相关知识点 (1) 数据清单 Excel 数据清单是一个特殊的表格, 是包含列标题的一组连续数据行的工作表 数据清单由两部分组成, 即表结构和纯数据 表结构就是数据清单中的第一行, 即为列标题 数据清单的每一列称为一个字段, 列标题称为字段名, 从数据清单第二行开始的每一行都称为一条记录, 如图 4-25 所示 字段 字段名 记录 图 4-25 数据清单 数据清单可以同一般工作表一样地创建和编辑 数据清单也可以以记录为单位进行编辑 操作步骤如下 : 单击 数据 记录单 命令, 弹出记录编辑对话框, 如图 4-26 所示 利用对话框中的 新建 删除 条件 按钮可分别创建记录 删除记录 查询记录 右上角分数 19/29 表示记录清单共有 29 条记录, 当前记录为第 19 条记录 图 4-26 记录编辑框

25 152 计算机应用基础教程 (2) 数据清单的排序根据需要, 有时要对数据清单进行排序, 排序可按一个字段排序, 也可按多个字段排序 排序操作步骤如下 : 单击数据清单的任意字段名, 单击 数据 排序 命令, 在 排序 对话框中, 选择 主要关键字 升序 / 降序, 还可选择 次要关键字 升序 / 降序, 依此类推, 如图 4-27 左图所示 在 我的数据区域 选项组中 有标题行 选项告诉系统数据清单有标题, 排序时要将标题排除在外 单击 选项 按钮, 弹出 排序选项 对话框, 如图 4-27 右图所示 在 排序选项 对话框中, 可自定义排序次序, 还可选择排序方法, 如按 字母排序 笔划排序 等 图 4-27 排序 (3) 数据筛选 1) 自动筛选自动筛选只能将筛选出的记录在原位置上显示, 并且一次只能对一个字段设置筛选条件 若筛选涉及到多个字段条件时, 必须经过多次自动筛选才能完成筛选任务 详细操作步骤见实现方法 2) 高级筛选高级筛选可一次完成筛选条件较为复杂的记录筛选 高级筛选首先要设置好筛选条件区域, 如果筛选时要求同时满足多个条件, 则称这些条件为 与 的关系 ; 如果筛选只要求满足多个条件之一时, 则称这些条件为 或 关系 多个字段的条件处于同一行上时, 表示多个条件之间是 与 的关系, 多个字段的条件处于不同行上时, 表示多个条件是 或 的关系 条件区域设置如图 4-28 所示, 左边条件区域中两个条件是 与 的关系, 右边条件区域中两个是 或 的关系 详细操作步骤详见实现方法 图 4-28 条件区域 (4) 分类汇总 分类汇总就是先将记录按某个字段进行分类, 然后在每一类进行汇总 ( 如计数 求和 求平均值等 ), 如汇总不同职称教师在某年发表的论文数 分类汇总的操作步骤如下 :

26 153 先按分类字段排序 光标定位于任一字段名上 单击 数据 分类汇总 命令 详细操作步骤参见实现方法 (5) 数据透视表分类汇总只能解决按一个字段分类汇总问题, 如要解决按多个字段分类汇总的问题则分类汇总将无能为力, 这时可以用数据透视表功能轻松解决上述问题, 如统计不同职称 不同学历 不同性别教师发表论文数 透视表制作步骤如下 : 单击 数据 数据透视表和数据透视图 命令 数据透视表和数据透视图向导 详细操作步骤见实现方法 3. 实现方法 (1) 排序 操作步骤如下 : 1 单击 工具 选项 命令, 弹出 选项 对话框, 在 自定义序列 选项卡中, 添加自定义序列 东部, 南部, 西部, 北部 2 光标定位于数据清单的任意字段名上, 单击 数据 排序 命令, 在 排序 对话框中, 主要关键字 框中选择 地区 升序, 次要关键字 框中选择 产品 降序, 第三关键字 框中选择 季度 升序 单击 选项 按钮, 弹出 排序选项 对话框, 在 自定义排序次序 对话框中, 选择 东部, 南部, 西部, 北部 自定义序列, 如图 4-29 所示, 单击 确定 按钮返回, 单击 确定 按钮 图 4-29 排序选项 (2) 分类汇总 操作步骤如下 : 1 选择 Sheet2 工作表 光标定位于 产品 字段名上, 单击工具栏上的 升序排序 按钮 单击 数据 分类汇总 命令 在 分类汇总 对话框中, 在 分类字段 列表框中选择 产品, 在 汇总方式 中选择 求和 项, 在 选定汇总项 中选中 订货量, 如图 4-30 所示 确定 单击分类汇总窗口中左上角的 1 2 3, 可以显示不同级别的汇总数据, 如图 4-31 所示 单击图 4-30 中 全部删除 按钮可删除汇总数据行 单击分类汇总窗口左上角的 2 按钮, 选择要复制的数据区域如图 4-31 所示, 单击 编辑 定位 命令 单击 定位 对话框中的 定位条件 按钮 在 定位条件 对话框中选中 可见单元格 选项 单击工具栏上的 复制 按钮 选择 Sheet2 中的空白单元格 单击工具栏上的 粘贴 按钮

27 154 计算机应用基础教程 图 4-30 分类汇总对话框 图 4-31 分类汇总 2 选择 Sheet2 工作表 光标定位于 销售员 字段名上, 单击工具栏上的 升序排序 按钮重新对记录排序 单击 数据 分类汇总 命令 在 分类汇总 对话框中, 在 分类字段 下拉列表框中选择 销售员, 在 汇总方式 中选择 求和 项, 在 选定汇总项 中选中 订货量 确定 其他操作同 1 最后结果如图 4-32 所示 图 4-32 按 产品 销售员 汇总结果 (3) 自动筛选 操作步骤如下 : 1 选择 Sheet3 工作表 光标定位于 产品 字段名上 单击 数据 筛选 自动筛选 命令 单击 产品 右侧的下拉按钮 在下拉列表框中选择 电视机, 筛选结果如图 4-33 所示 图 4-33 自动筛选

28 155 2 单击 订货日期 右侧下拉按钮, 在下拉列表框中选择 自定义 选项, 弹出 自定义自动筛选方式 对话框, 定义筛选条件为订货日期大于等于 且小于等于 , 如图 4-34 所示, 确定 图 4-34 自定义自动筛选方式 (4) 高级筛选 操作步骤如下 : 选择 Sheet4 工作表, 在单元格区域 I1:K2 设置筛选条件如图 4-35 所示 单击 数据 高级筛选 命令, 弹出 高级筛选 对话框, 在对话框的 列表区域 框中圈选数据清单的区域如 $A$1:$H$101, 在 条件区域 框中圈选条件区域如 $I$1:$K$2, 确定 (5) 高级筛选 操作步骤如下 : 选择 Sheet5 工作表, 在单元格区域 I1:K3 设置筛选条件如图 4-36 所示, 单击 数据 高级筛选 命令, 弹出 高级筛选 对话框, 在对话框的 列表区域 框中圈选数据清单的区域如 $A$1:$H$101, 在 条件区域 框中圈选条件区域如 $I$1:$K$3, 在 方式 框选择 将筛选结果复制到其他位置, 在 复制到 框中输入 I6, 如图 4-37 所示, 确定 图 4-35 筛选条件图 4-36 或筛选条件 图 4-37 高级筛选

29 156 计算机应用基础教程 (6) 透视表创建与编辑 操作步骤如下 : 1 单击 数据 数据透视表和数据透视图 命令 在 数据透视表和数据透视图向导 3 步骤之 1 对话框的 数据源类型 框中选择 Excel 数据列表或数据库, 在 所创建的报表类型 框中选择 数据透视表 在 数据透视表和数据透视图向导 3 步骤之 2 对话框的 选定区域 框中圈选数据源范围如 $A$1:$H$101 在 数据透视表和数据透视图向导 3 步骤之 3 的 数据透视表显示位置 中选择 新建工作表, 如图 4-38 所示 单击 布局 按钮 在 数据透视表和数据透视图向导 布局 对话框中, 将 地区 拖到页上 销售员 拖到行上 订货日期 拖到行上 产品 拖到列上 订货量 拖到数据上, 如图 4-39 所示 确定, 完成, 汇总结果如图 4-40 所示 图 4-38 数据透视表和数据透视图向导 3 步骤之 3 图 4-39 数据透视表和数据透视图向导 - 布局 2 右键单击 订货日期, 单击快捷菜单中的 组及显示明细数据 组合 命令 在 分组 对话框的 步长 列表框中选择 月, 确定 3 单击如图 4-38 所示对话框中的 选项 按钮, 弹出如图 4-41 所示 数据透视表选项 对话框, 在 名称 框中输入 销售数据透视表 还可以在该对话框选择其他选项, 选中或取消 行总计 列总计 等 4 数据透视表编辑 : 利用 数据透视表 工具栏上的按钮及菜单功能可方便地对透视表进行编辑, 如图 4-42 所示 如单击 数据透视表 工具栏上的 数据透视表 按钮, 单击菜单中的 数据透视表向导 命令可根据向导对数据透视表进行编辑 ; 单击工具栏上的 字段设置 按钮 可对透视表的 汇总方式 进行设置 ( 如求和 平均值 计数等 )

30 157 图 4-40 透视表明细 图 4-41 数据透视表选项 图 4-42 数据透视表 工具栏 (7) 页面设置 操作步骤如下 : 单击 文件 页面设置 命令, 弹出 页面设置 对话框 在 页面 选项卡中设置纸张方向 缩放比例 ( 如 90%) 在 页边距 选项卡中设置 上 下 左 右 页眉 页脚 等边距 在 页眉 / 页脚 选项卡中, 单击 自定义页眉 按钮 输入 第 3 4 季度电器销售表 单击 自定义页脚 按钮, 插入 第 &[ 页码 ] 页共 &[ 总页数 ] 页 在 工作表 选项卡中, 打印区域 圈选 A1:H75 打印标题: 单击列标题行 勾选 网格线, 确

31 158 计算机应用基础教程 定, 如图 4-43 所示 单击对话框上的 打印预览 按钮可预览设置效果, 单击 打印 按钮 可实现打印 图 4-43 页面设置 4. 课堂实践 (1) 实践本案例 (1) (2) (2) 实践本案例 (3) (4) (5) (3) 实践本案例 (6) (7) (4) 打开工作簿文件 Student.xls, 完成下列操作 : 1 从工作表 Sheet1 的数据清单中筛选出所有 2008 级至 2009 级中文专业学生的记录, 并将筛选结果 ( 包括标题行 ) 复制到以 Sheet1!A155 为左上角的区域中 ( 高级筛选 ) 2 使用分类汇总, 统计不同专业的人数, 将汇总结果复制到空白区域, 在 Sheet2 中完成 3 创建透视表, 统计不同专业 不同性别 不同年级的人数, 年级放在页上, 性别放在列上, 专业放在行上, 性别放在数据上, 汇总方式为计数, 横向求和 ( 纵向不求和 ), 透视表重命名为 学生统计表 5. 评价与总结 根据时间情况让同学主动上台演示课堂实践的部分或全部操作 鼓励多位同学分别总结本案例中某一部分主要知识点, 学生或老师补充 6. 课外延伸 (1) 根据本案例 (6) 中的透视表, 将年销售业绩最好 ( 订货量最大 ) 与销售业绩最差销售员所在行用的图案颜色分别设置为浅绿与浅黄色 订货量最大产品与订货量最少产品名称分别用红色和蓝色显示 (2) 用函数方法统计不同销售员不同产品的订货量

32 159 任务五教工信息管理 Excel 系统不仅提供了功能齐全的计算功能 数据管理功能, 还提供了快捷灵活的图表功能 在实际学习 工作中, 经常要进行各种各样的数据统计, 然后生成报表, 数据密密麻麻看了让人生畏, 而且产生的效果也不好 但如果将统计数据用图表展示出来, 将给人们耳目一新的感觉 案例 7 教工信息统计与分析 场景描述 志远学院是一所教师众多的高校, 人力资源部门经常需要对教师队伍的学历 职称构成 科研论文等数据进行统计和分析, 以便为领导引进人才 科学用人提供科学依据 教师人数众多, 统计和管理大量数据工作是很辛苦的事情 但如果能熟练地应用 Excel 中的各种统计函数 数据透视表及图表功能, 一定能起到事半功倍的效果 下面就以学院部分教师的科研数据为例, 一窥人力资源部门的部分日常管理工作 打开 teacher_inf.xls 文件, 完成下列各项工作 : (1) 统计博士或教授某年度发表论文之和, 统计女副教授某年度发表论文的平均值 条件区域放在 Sheet1 的 G1:J4 的某空白处, 在单元格区域 I7:I8 中统计 (2) 筛选女教师发表论文最多的记录到 Sheet1 的以 A33 为左上角的单元格区域中 (3) 将女教师发表论文最多的教师所在的行添加浅绿色底纹 (4) 查询发表论文最多的是哪位教师? 查询夏雪老师发表多少篇论文 (5) 在 Sheet2 的 H2:J5 区域中统计不同职称 人数 比例 发表论文的平均值 (6) 以 (5) 中统计的数据为依据, 以 职称 人数 两列数据为数据源, 绘制三维饼图, 并显示 值 和 百分比, 标题为 教师结构统计图 标题的字号设置为 14, 其他字号设置为 10 (7) 以 (5) 中统计的数据为依据, 以 职称 人数 平均发表论文 三列数据为数据源, 绘制三维簇状柱形图, 标题为 科研水平统计图, 图例在底部, 并进行如下设置 : 图表区图案用 橡牙色 填充, 绘图的背景墙图案填充效果为 纹理 : 新闻纸 ; 数值轴格式设置为 : 刻度 最大值为 48, 主刻度单位为 6,XY 平面交于 0; 平均发表论文 系列用 花束 填充, 并显示数据标示的值 ; 从图表中删除 百分比 系列, 然后再添加 百分比 系列, 体会添加 删除数据系列的方法 (8) 保护工作簿的结构, 使用户不能插入 删除 移动 重命名工作表 ; 对 Sheet2 中数据统计区 G1:J5 进行保护, 使得统计表区数据不能编辑不能删除并隐藏公式 1. 案例分析本案例 (1) (2) 等涉及到数据库统计函数知识点, 如统计博士或教授发表论文之和等 ; (3) 中的问题可以用前面已学过的条件格式知识来解决 ;(4) 中涉及到数据查询函数 vlookup

33 160 计算机应用基础教程 与 lookup 的应用问题 ;(5) 可以用前面学习过的 Countif Sumif 等进行统计, 也可以用本案例学习的数据库函数进行统计 (6) (7) 主要涉及到创建和编辑图表的有关知识 ; 最后一个问题涉及到保护工作簿及工作表问题 2. 相关知识点 (1) 数据库函数数据库函数是专门用于数据清单带有附加统计条件的一类统计函数, 在实际统计工作中有着广泛的应用 常用的数据库函数有 DCOUNTA(DCOUNT) DSUM DMAX DMIN DAVERAGE 等, 这些函数的参数都是 Database,Field,Criteria, 因此只要掌握其中一个函数的使用方法, 也就掌握了这类数据库函数的用法 1 DCOUNTA(database,field,criteria), 返回数据库或列表的列中满足指定条件的非空单元格个数 其中 : database 是构成列表或数据库的单元格区域, 如 A1:E31; Field 指定函数所使用的数据列 field 可以是文本, 如 职称, 也可以是代表列表中数据列位置的数字 :1 表示第一列,2 表示第二列, 等等 ; criteria 为一组包含给定条件的单元格区域, 如 H1:H2 具体使用方法请参见 3. 实现方法 部分的相关内容 2 DSUM(database,field,criteria), 返回列表或数据库的列中满足指定条件的数字之和 参数说明同 DCOUNTA 3 DMAX(database,field,criteria), 返回列表或数据库的列中满足指定条件的最大数值 参数说明同 DCOUNTA 4 DMIN(database,field,criteria), 返回列表或数据库的列中满足指定条件的最小数 5 DAVERAGE(database,field,criteria), 返回列表或数据库中满足指定条件的列中数值的平均值 (2) 查找函数 1 LOOKUP(lookup_value,lookup_vector,result_vector), 返回第二个单行区域或单列区域中相同位置的数值 其中 : lookup_value 为函数 LOOKUP 在第一个向量中所要查找的数值 lookup_value 可以为数字 文本 逻辑值或包含数值的名称或引用 lookup_vector 为只包含一行或一列的区域 如, 查找发表论文最多的是哪位教师 : =LOOKUP(MAX(E2:E31),E2:E31,A2:A31), 返回第一个向量中最大值对应行与第二个向量对应列交叉单元格的值 李文如, 要求 E 列排序 提示 : lookup_vector 的数值必须按升序排序 : A~Z FALSE TRUE; 否则, 函数 LOOKUP 不能返回正确的结果 如果函数 LOOKUP 找不到 lookup_value, 则查找 lookup_vector 中小于或等于 lookup_value 的最大数值 如果 lookup_value 小于 lookup_vector 中的最小值, 函数 LOOKUP 返回错误值 #N/A 2 VLOOKUP(lookup_value,table_array,col_index_num,range_lookup), 返回第一列要查找

34 161 值所在行与指定列交叉处的单元格的值 其中 : lookup_value 为需要在数据区域第一列中查找的值 ; table_array 为需要在其中查找数据的数据表区域 ; col_index_num 为 table_array 中待返回匹配值的列序号 col_index_num 为 1 时, 返回 table_array 第一列中的数值 ;col_index_num 为 2 时, 返回 table_array 第二列中的数值, 以此 类推 range_lookup 为一逻辑值, 指明函数 VLOOKUP 返回时是精确匹配还是近似匹配 如果 为 TRUE 或省略, 则返回近似匹配值, 也就是说, 如果找不到精确匹配值, 则返回小于 lookup_value 的最大数值, 这时要求 table_array 的第一列中的数值必须按升序排列 ; 如果 range_value 为 FALSE, 函数 VLOOKUP 将返回精确匹配值 如果找不到, 则返回错误值 #N/A, 这时 table_array 不必按第一列进行排序 在 teacher_inf 表中要查找 夏雪 的学位可用公式 :=VLOOKUP(" 夏雪 ",A1:E7,3,FALSE); 若要查找 夏雪 发表的论文数可用公式 :=VLOOKUP(" 夏雪 ",A1:E7,5,FALSE) 提示 : 如果函数 VLOOKUP 找不到 lookup_value 且 range_lookup 为 TRUE, 则返回小于等 于 lookup_value 的最大值 如果 lookup_value 小于 table_array 第一列中的最小数值, 函数 VLOOKUP 返回错误 值 #N/A 如果函数 VLOOKUP 找不到 lookup_value 且 range_lookup 为 FALSE, 函数 VLOOKUP 返回错误值 #N/A (3) 图表的创建与编辑 1) 图表的创建 操作步骤如下 : 选择数据区域 单击工具栏上的 图表向导 按钮 在 图表向导 4 步骤之 1 图表 类型 对话框中选择图表类型及子类型 在 图表向导 4 步骤之 2 图表数据源 对话框中 选择数据区域 系列产生在 行 / 列 在 图表向导 4 步骤之 3 图表选项 对话框中设置 图表选项, 如标题 图例等 在 图表向导 4 步骤之 4 图表位置 对话框中选择图表位置 完成 2) 图表的编辑 操作步骤如下 : 单击 图表区 空白处 单击 图表 图表类型 数据源 图表选项 位置 命令, 进 入相应对话框重新对图表相关项进行设置 双击 图表区 空白处, 弹出 图表格式区 对话 框, 进行相关项的设置 双击 绘图区, 在 绘图区格式 对话框进行图案设置 双击 数 值轴, 在 坐标轴格式 对话框中对坐标轴格式进行设置 图表的创建与编辑的实例参见 3. 实现方法 3. 实现方法 (1) 操作步骤如下 : 设置条件区域如图 4-44 所示 光标定位于 I7 中 单击编辑栏左边的插入函数 f x 按钮 在 插入函数 的 选择类别 框中选 数据库, 在 选择函数 列表框中选中 DSUM 确定 在 函数参数 的 Database 框中圈选数据清单范围, 在 Field 框中单击求和字段

35 162 计算机应用基础教程 名单元格 E1 或直接输入该列序号 5, 在 Criteria 框中圈选条件区域 G1:H3, 如图 4-45 所示 确定 图 4-44 数据库函数条件区域 图 4-45 DSUM 函数参考 同样方法, 在 I8 输入公式 =DAVERAGE(A1:E31,E1,I1:J2), 求女副教授发表论文的平均值 (2) 操作步骤如下 : 1 单击 K2 单元格, 用 DMAX 求出 性别 为女发表论文的最大值 64 2 以 性别 为女且发表论文 篇数 为 64 作为条件进行高级筛选, 将筛选的结果复制到 A33 开始的单元格即可 (3) 操作步骤如下 : 选择单元格区域 A2:E31 单击 格式 条件格式 命令 在 条件 1 左框中选择 公式, 右框中输入 =AND($B2=" 女 ", $E2=MAX($E$2:$E$31)) 单击 格式 按钮 设置单元格底纹为 浅绿色, 如图 4-46 所示 确定 图 4-46 复杂条件格式设置 (4) 操作步骤如下 : 1 用 LOOKUP 函数查找发表论文为 64 篇的是谁 : 对数据清单按 篇数 升序排序 光标定位于 I9 中 单击编辑栏左边的插入函数 f x 按钮 在 插入函数 的 选择类别 框中选择 查找与引用, 在 选择函数 列表框中选中 LOOKUP 确定 在 选定参数

36 163 对话框中, 选择具有 3 个参数的项 确定 在 函数参数 对话框中, 在 Lookup_value 框中输入 K2, 在 Lookup_vector 框中圈选查找区域 E2:E31, 在 Result_vector 框中圈选返回向量 A2:A31, 如图 4-47 所示 确定 2 用 VLOOKUP 函数查找 夏雪 发表了几篇论文 : 光标定位于 I10 中 单击编辑栏左边的插入函数 f x 按钮 在 插入函数 的 选择类别 框中选择 查找与引用, 在 选择函数 列表框中选中 VLOOKUP 确定 在 函数参数 对话框中, 在 Lookup_value 框中输入 A6( 或直接输入 夏雪 ), 在 Table_array 框中圈选查找区域如 A1:E31, 在 Col_index_num 框中输入 5( 待返回匹配值所在列的序号 ), 在 Range_lookup 框中输入 FALSE 如图 4-48 所示 确定 图 4-47 LOOKUP 参数 图 4-48 VLOOKUP 参数 (5) 操作步骤如下 : 1 单击工作表标签 Sheet2, 在 G9:J10 中设置条件 ( 如职称 : 教授等 ), 用 DCOUNTA 计算教授 副教授 讲师 助教人数, 如在单元格 H2 中输入计算教授人数公式 : =DCOUNTA (A1:D31,D1,G9:G10) 其实用 COUNTIF 函数更方便 2 计算不同职称发表论文平均值用 DAVERAGE 函数, 这里用公式 SUMIF/ 人数计算教授发表论文的平均值 : 在单元格 J2 中输入公式 : =SUMIF(D$1:E$31,G2,E$1:E$31)/H2, 然后将公式向下填充直到 J5 即可, 保留 1 位小数点

37 164 计算机应用基础教程 3 计算不同职称人数所占百分比 : 在 I2 中输入公式 =H2/SUM(H$2:H$5)*100, 回车后 将公式向下填充直到 I5 即可, 如图 4-49 所示 图 4-49 统计 (6) 绘制三维饼图 操作步骤如下 : 选择数据源区域 G1:H5 单击工具栏上的 图表向导 按钮 在 图表向导 4 步骤之 1 图表类型 对话框的图表类型及子类型框中分别选择 饼图 和 三维饼图 在 图表向导 4 步骤之 2 图表数据源 对话框中选择数据区域 系列产生在 列 在 图表向导 4 步骤之 3 图表选项 对话框中设置标题 教师结构统计图 图例: 底部 数据标志 中勾选 值 百分比 选项 在 图表向导 4 步骤之 4 图表位置 对话框中选择 作为其中对象 插入 完成 双击图表区, 在 图表区格式 对话框中设置字号为 10 双击图表标题, 在 图表标题格式 对话框中设置字号为 14 结果如图 4-50 所示 图 4-50 三维饼图 (7) 绘制三维簇状柱形图及编辑 操作步骤如下 : 1 选择数据区域 G1:J5, 按照 (6) 中的步骤, 绘制三维簇状柱形图如图 4-51 所示 单击图表区, 单击工具栏上 填充颜色 右边的下拉按钮, 在填充颜色模板中选择 象牙色 ; 双击绘图区的背景墙, 在 背景墙格式 对话框中单击 填充效果 按钮, 在 纹理 选项卡中选择 新闻纸 样式, 确定 返回, 确定 2 双击 平均发表论文 系列, 在 数据系列 对话框的 图案 选项卡中设置用 花束 样式填充, 在 数据标志 选项卡中勾选 值 选项 3 双击 数值轴, 在 数值轴格式 对话框的 刻度 选项卡中, 取消勾选 自动设置 中的 最大值 及 主要刻度单位 选项, 并在相应的框中分别输入 48 6, 设置如图 4-52 所示, 确定 4 单击 百分比 系列, 按键盘上的 Delete 键即可删除 百分比 系列 反之, 复制数据表中 百分比 列的相应数据 ( 包括字段名 ), 单击 图表区, 按 Ctrl+V 组合键粘贴即可添加 百分比 系列

38 165 图 4-51 三维簇状柱形图 图 4-52 坐标轴格式 (8) 保护工作簿 工作表 操作步骤如下 : 1 单击 工具 保护 保护工作簿 命令, 在 保护工作簿 对话框中选中 结构 选项, 在 密码 框中输入密码 ( 可选 ), 确定 2 单击 Sheet2 的 全选 按钮, 单击 格式 单元格 命令, 在 单元格格式 的 保护 选项卡中取消 锁定 隐藏 选项, 确定 选择要保护的区域 G1:J5 单击 格式 单元格 命令, 在 单元格格式 的 保护 选项卡中选中 锁定 隐藏 选项, 确定 单击 工具 保护 保护工作表 命令 在 保护工作表 对话框中, 勾选 保护工作表及锁定的单元格内容 选定锁定单元格 选定未锁定的单元格 选项, 如图 4-53 所示 确定 4. 课堂实践 (1) 实践本案例 (1) (2) (2) 实践本案例 (3) (4) (5) (3) 实践本案例 (6) (7) (8) (4) 为了方便对后面数据的编辑, 需要对数据窗口进行拆分, 使得在编辑后面的记录时始终看到 Sheet3 中的第一行列标题

39 166 计算机应用基础教程 图 4-53 保护工作表 5. 评价与总结 根据时间情况让同学主动上台演示课堂实践的部分或全部操作 鼓励多位同学分别总结本案例中某一部分主要知识点, 学生或老师补充 6. 课外延伸 (1) 频率分析在实际统计工作中, 经常需要分析一批数据在各个区间的分布情况, 如老师分析考试成绩在各个分数段里的人数, 销售人员统计销售数据的分布情况等 这时需要用到 Excel 中的数据分析工具或频度分析函数 1) 频度分析函数 FREQUENCY(data_array,bins_array) 计算一列垂直数组在给定各区间段内数据的分布频率 由于函数 FREQUENCY 返回一个数组, 所以必须以数组公式的形式输入 其中 : data_array 为用于计算频率的数组或对一组数值的引用 ; bins_array 为间隔的数组或对间隔的引用, 也就是分隔区间的分段点, 由每个数据区间的最大值 一组数据构成 如 : 在本案例数据表 Sheet3 中统计发表论文数在 0~9 10~19 20~39 40~59 60 篇以上各区间里的人数 操作步骤如下 : 在 分段点 G4:G7 单元格区域中分别输入分段点 (9, ), 选择单元格区域 I4:I8, 插入 FREQUENCY 函数, 在 函数参数 对话框的 Data_array 框中圈选 E2:E31, Bins_array 圈选 G4:G7, 如图 4-54 所示,Ctrl+Shift+Enter 三键同时按下 2) 数据分析工具利用数据分析工具同样可解决上述数据分布问题, 操作步骤如下 : 单击 工具 加载宏 命令, 在 加载宏 对话框中勾选 加载分析库 选项, 确定 单击 工具 数据分析 命令 在 数据分析 中选择 直方图 选项, 确定 在 直方图 对话框 输入 内的 输入区域 框中圈选 E2:E31, 在 接收区域 ( 即分段点区域 ) 框中选择 $G$4:$G$7, 在 输出区域 框中输入 K3, 确定 两种操作方法统计结果如图 4-55 所示

40 167 图 4-54 函数参数 图 4-55 频度分析 (2) 课外练习 1 用 FREQUENCY 函数和数据分析两种方法统计 6. 课外延伸 中频度分析问题 2 利用 1 的统计结果, 以 论文区间 统计人数 两列数据为数据源, 创建 簇状柱形图 3 利用透视表的方法统计不同职称 不同学历人员发表论文的平均数, 然后利用统计结果创建 簇状柱形图 Excel 功能很强大, 还可以用它求解规划问题 求解方程, 以及进行各种数据分析等 但由于种种原因, 许多应用案例不能在此一一列举, 读者在遇到这些问题时可查找帮助或到互联网上查询相关帮助信息 思考与练习 单选题 1. 在 Excel 中选择了内嵌图表后, 改变它的大小的方法是 ( ) A. 用鼠标拖拉图表边框上的控制点 B. 用鼠标拖拉图表边框 C. 按 键或 键 D. 按 + 键或 - 键 2. 在 Excel 中, 保存工作簿时屏幕若出现 另存为 对话框, 则说明 ( ) A. 该文件作了修改 B. 该文件不能保存 C. 该文件未保存过 D. 该文件已经保存过

41 168 计算机应用基础教程 来区别 3. 为了区别 数字 与 数字字符串 数据,Excel 要求在输入项前添加 ( ) 符号 A.@ B. ( 双引号 ) C.# D. ( 英文单引号 ) 4. 在 Excel 中, 下面操作序列将选取 ( ) 区域 : 单击 A2 单元格, 按 F8 键, 单击 E8 单元格, 单击 D9 单元格, 再按 F8 键 A.A2:D9 C.E8:D9 B.A2:E8 D.A2:E9 5. 作为数据的一种表示形式, 图表是动态的, 当改变了其中 ( ) 之后,Excel 会自 动更新图表 A.X 轴上的数据 B. 标题的内容 C. 所依赖的数据 D.Y 轴上的数据 6. 已知 D2 单元格的内容为 =B2*C2, 当 D2 单元格被复制到 E3 单元格时,E3 单元格的 内容为 ( ) A.=C3*D3 C.=C2*D2 B.=B3*C3 D.=B2*C2 7. 在 Excel 中, 使用 高级筛选 命令前, 我们必须为之指定一个条件区域, 以便显示 出符合条件的行 ; 如果要对于不同的列指定一系列不同的条件, 则所有的条件应在条件区域的 ( ) 输入 误的 A. 同一列中 B. 同一行中 C. 不同的行中 D. 不同的列中 8. 向 Excel 工作表的单元格里输入公式, 运算符有优先顺序, 下列 ( ) 说法是错 A. 字符串连接优先于关系运算 B. 百分比优先于乘方 C. 乘方优先于负号 D. 乘和除优先于加和减 9. 在 Excel 中, 下列 ( ) 是输入的正确公式形式 A.=8^2 C.==sum(d1:d2) B.>=b2*d3+1 D.= c7+c1 10. 在数据清单设置条件时, 当一列数据的条件格式根据另一列数据设置时, 在 条件 格式 对话框的 条件 1 框的左边列表框中选择 ( A. 单元格数值 B. 公式 11. 在 Excel 工作表中已输入的数据如下所示 : A B C D E % =$A$1*C1 ) 来构造条件 % 如将 D1 单元格中的公式复制到 D2 单元格中, 则 D2 单元格的值为 ( ) A.#### B.2 C.4 D 当向 Excel 工作表单元格输入公式时, 使用单元格地址 D$2 引用 D 列 2 行单元格,

42 169 该单元格的引用称为 ( ) A. 交叉地址引用 B. 混合地址引用 C. 相对地址引用 D. 绝对地址引用 13. 在 Excel 中, 在排序列中有空白单元格的行, 在排序后的数据清单中将看到该空白行 ( ) A. 被置于数据清单的最后行 B. 被置于数据清单的第一行 C. 不显示 D. 被置于数据清单的原位置 14. 在 Excel 中, 在单元格 D5 中有公式 =$B$1+C4, 删除 A 列后,C5 单元格中的公 式变为 ( ) A.$A1+B4 B.=$A$1+B4 C.=$A$1+C4 D.=$B$1+C4 15. 公式 =OR(10>8^2,20>40,TRUE>FALSE) 的值为 ( ) A.TRUE B.FALSE