1、大学计算机基础实践教程（文科）第 4 章电子表格处理软件 Excel 2003实验九 Excel 2003 建立工作表操作实验目的 1掌握建立工作表的一般方法。2掌握管理工作表的一般方法。操作指导案例 1如图 4-1 所示，按照下列内容建立一张工作表，并完成下列操作： 1合并A1:E1单元格，内容居中。2. 删除Sheet3表。3. 将Sheet1重命名为“差旅费”。图 4-1 工作表内容合并单元格操作步骤如下：（1） 选中A1:E1区域。（2） 单击工具栏上的“合并及居中” 按钮。删除和重命名工作表操作步骤如下：（1） 右键单击“Sheet3”工作表标签，选择“删除”命令。（2） 右键单击“
2、Sheet1”工作表标签，选择“重命名”命令，输入“差旅费”。实验十 Excel 2003 数据计算及图表制作实验目的1. 熟悉格式化操作和输入公式的基本方法。2. 掌握图表的建立和编辑方法。操作指导案例 116如图 4-2 所示，按照下列内容建立一张工作表，并完成下列操作： 1计算“人数”列的“合计”项和“所占比例”列（所占比例=人数/合计），结果保留2位小数。2选取“某大学在校生人数情况表”的“年级”列（不含“合计”列）和“所占比例” 列，建立“分离型饼图”（系列产生在“列”），标题为“在校生人数分年级比例图”，使该图 表位于“某大学在校生人数情况表”的A8:E18单元格区域内。图 4-2
3、 某大学在校生人数情况表计算“人数”列的“合计”项和“所占比例”列操作步骤如下：（1） 单击B7 单元格，再单击工具栏上的“自动求和”按钮，或单击菜单【插入】， 选择“函数”命令，然后再选择“SUM” 函数，如图 4-3 所示，在确认求和区域为B3:B6 无误后，按回车键。图 4-3 使用SUM 函数求和（2） 如图 4-4 所示，在C3 单元格中输入=B3/$B$7，因为求所占比例的分母在复制公式时，是不变化的，所以必须对B7 单元格使用绝对引用。图 4-4 输入公式（3） 在B7 单元格输入公式按回车键求得结果，对于“数值”型数据，Excel 默认保留2 位小数，否则，也可以单击菜单【格式
4、】，选择“单元格”命令，在“单元格格式”对话框中的“数字”选项卡中，选择“数值”，对“小数位数”重新进行设置。其他单元格的公式用“填充柄”复制，如图 4-5 所示。图 4-5 求所占比例建立分离型饼图操作步骤如下：（1） 按下Ctrl 键，同时选中“年级”列和“所占比例”列。（2） 单击【插入】，选择“图表”命令，在打开的“图表向导”对话框中，先选择“饼图”中的“分离型饼图”，再按下“按下不放可查看示例”按钮，观察最终作图效果，如图 4-6 所示，如果确认没有问题，按向导的步骤一步一步的往下做。图 4-6“图表向导”对话框（3） 依题意，将完成的图表通过移动和压缩放置在 A8:E18 单元格区
5、域内，如图 4-7所示。图 4-7 移动图表到 A8:E18 区域案例 2如图4-8所示，按照下列内容建立一张工作表，并完成下列操作：1将标题行“职工工资表”（A1:G1）合并居中，并将格式设为黑体，字号20。2在“路程”与“沈梅”之间插入一条记录，数据为：刘怡 F 230.60 100.00 0.00 15.86。3. 用公式求出实发工资（实发工资=基本工资+奖金+补贴-房租）。4. 将所有性别为M 的改为男，F 改为女。5. 将工作表 Sheet2 重命名为“工资表”，并复制职工工资表到该工作表 A2:G16 区域（不包括标题）。6. 在工作表“工资表”中以“姓名”和“实发工资”为数据区建
6、立一个簇状柱形图图表，刻度最大值为 1500，主要刻度单位为 150，图表位置放在A17:J35 区域内。图 4-8 工作图表第 1-5 题的操作此处省略，具体操作可参考前面的例子或教材的相关内容，注意第 4 题做完后，计算实发工资公式中的F 会改为女，应在公式中将女再改为F，重新计算实发工资即可。第 6 题经过相关的操作，可以得到原始的簇状柱形图图表，如图4-9 所示。此时只要用鼠标左键双击该图表左边的“数值轴”，就会出现图4-10 所示的“坐标轴格式”对话框，在 “刻度”选项卡中，将“最大值”修改为1500，“主要刻度单位”修改为150 即可（注意修改值后，该两处的复选框选中状态自动消失，
7、不要再选中）。图 4-9 原始的簇状柱形图图 4-10“坐标轴格式”对话框实验十一 Excel 2003 公式与函数实验目的1. 熟悉公式及函数的使用。2. 掌握复制公式及函数的方法操作指导案例 1如图4-11所示，按照下列内容建立一张工作表，并完成下列操作：1. 计算所有学生的总分。2. 用COUNTIF ( )统计各学科90分的人数。3. 将统计结果按科目用簇状柱形图表示出来存放到sheet1中。图 4-11 各科成绩工作表计算总分操作步骤如下：（1） 在 J2 单元格中插入函数 SUM。（2） 用“填充柄”复制其他单元格。使用函数 COUNTIF 作统计计算操作步骤如下：（1） 在 C9
8、 单元格中插入统计函数 COUNTIF，出现如图 4-12 所示的对话框，在范围Range 的文本框中输入 C2:C8，条件 Criteria 的文本框中输入=90，按“确定”按钮得到所求结果。（2） 求其他单元格统计结果，用“填充柄”复制。图 4-12“函数参数”对话框建立簇状柱形图表操作步骤如下：（1） 根据题目分析可以知道，作图需要选择的数据源是各科目和90 分人数的统计结果，如图 4-13 所示。（2） 其余操作可仿照建立分离型饼图操作步骤，只是在“图表向导”步骤之 2，选择“系列”选项卡，使用鼠标单击“名称”文本框右侧的折叠按钮以折叠文本框，再单击工作表中A9 单元格，然后再单击折叠
9、按钮展开文本框（也可以在“名称”文本框中输入90 分人数），以替代图例“系列 1”，如图 4-14 所示。图 4-13 选择数据源图 4-14 选择数据源案例 2如图4-15所示，按照下列内容建立一张工作表，并完成下列操作：1. 用RANK( )函数根据平均分字段完成“名次”字段的填写（注：一定要按照先填写I2： I5之中某一单元格公式后，其它单元格复制（可用填充柄）的方法完成此题，最高分填1（第 1名），依次类推）。2. 将A1:I5单元格内容居中，并将英语、数学、化学、总分、平均分五列单元格中小于60的单元格的字体用红色表示（注：随单元格中数据值的变化，该单元格字体发生变化）。图 4-15
10、 工作表使用 RANK( )函数计算名次操作步骤如下：（1） 在 I2 单元格中插入统计函数RANK( )，出现如图 4-16 所示的对话框，在指定的数字 Number(用来比较该数字在一列数字中相对其他数值的大小)的文本框中输入 G2，在 Ref（对一组数的引用）的文本框中输入$G$2:$g$5，单击“确定”按钮得到所求结果。图 4-16“函数参数”对话框（2） 求其他单元格统计结果，用“填充柄”复制。使用条件格式显示数据操作步骤如下：（1） 依题意选中C2:G5 区域。（2） 单击【格式】，选择“条件格式”命令，出现图4-17 所示的对话框，在左侧的下拉列表框中选择“小于”，在右侧的下拉列
11、表框中输入60，单击“格式”按钮，在出现的“单元格格式”对话框中，选择字体颜色为“红色”，再返回“条件格式”对话框，单击“确定”按钮。图 4-17“条件格式”对话框案例 3如图4-18所示，按照下列内容建立一张工作表，并完成下列操作：1. 利用公式填写评语，条件是，月考 1 与月考 4 成绩比较，如月考 1 大于月考 4，由评语为“成绩退步，严重警告中”，如果月考4 大于月考 1 成绩 40 分以上，则评语为“进步神速，希更加努力”，除此以外的情况评语都为“进步平平，望加倍努力”。2. 修正表格里设定的错误的公式，并重新更正原有数据（提示：使用AVERAGE 算出平均分）。3. 将 4 次月考
12、平均成绩大于 550 的同学的姓名以蓝色加粗突出表示（提示：利用求平均公式以及筛选功能）。4. 根据表格的特点，给表格制作一张能够反映某同学4 次月考成绩升降情况的独立图表（要求：图表类型为簇状柱形图，图表标题为：月考成绩情况表，显示数据标志，其他的可取舍，图表名称为：图表 1）。5. 删除工作表Sheet2、Sheet3。6. 标题部分使用楷体_GB2312，加粗。并给表格加上蓝色表格边框线（标题除外），使表格看起来更美观。图 4-18 工作表使用逻辑函数IF 填写评语操作步骤如下：（1） 单击 H3 单元格，根据题意在编辑栏内输入 =IF(F3-C340,进步神速,希更加努力,进步平平，望
13、加倍努力)，按回车键完成H3 单元格的评语（注意在英文输入法下，输入上述非中文符号）。（2） 利用“填充柄”完成其他单元格的评语，如图4-19 所示。图 4-19 填写评语使用函数 AVERAGE 求平均分及个人平均成绩操作步骤如下：（1） 在C13 单元格插入函数 AVERAGE，确认所求区域 C3:C12 无误后，按回车键。（2） 利用“填充柄”求得D3:F3 单元格区域的平均分。（3） 与上面同理，使用函数AVERAGE，可以求得 G3:G12 区域的个人平均成绩。自动筛选及格式化操作这里省略。制作簇状柱形图的独立图表操作步骤如下：（1） 依据题意选中姓名和四次月考成绩作为制作图表的数据
14、源，如图4-20 所示。（2） 其余操作可仿照建立分离型饼图操作步骤进行，注意在向导步骤之3 的“标题”选项卡中，将“图表标题”内填写“月考成绩情况表”，在“数据标志”选项卡中选中“值”，在向导步骤之 4，选中“作为新工作表插入”单选项，将“Chart1”改为“图表1”，完成后如图 4-21 所示。图 4-20 选择数据源图 4-21 独立工作图表该题删除工作表及格式化操作省略，边框线的设置参见教材4.2.2。实验十二 Excel 2003 数据管理实验目的1. 掌握工作表中筛选记录的方法。2. 掌握排序及分类汇总的方法。操作指导案例 1如图 4-22 所示，按照下列内容建立一张工作表，并完成
15、下列操作： 1计算表中的实发工资=基本工资+补助工资-扣款。2. 利用自动筛选，筛选出“人事科”中基本工资大于或等于 1000 并且补助工资小于180 的人员。3. 将筛选出的符合条件的最后一行字体设置为黑体、字形设置为粗体。图 4-22 数据清单计算实发工资操作步骤如下：（1） 在G2 单元格中输入=D2+E2-F2，按回车键后求得G2 单元格的实发工资。（2） 再利用“填充柄”完成其他单元格的实发工资。自动筛选操作步骤如下：（1） 在数据清单中单击任一个单元格，单击菜单【数据】，选择“筛选”|“自动筛选” 命令后，如图 4-23 所示。图 4-23 执行“自动筛选”后的数据清单（2） 单击
16、“科室”右侧的按钮，在出现的下拉列表中选择“人事科”如图4-24 所示。（3） 单击“基本工资”右侧的按钮，在出现的下拉列表中选择“自定义”，打开如图4-25所示的“自定义自动筛选方式”对话框，在左侧的下拉列表框中选择“大于或等于”，在右侧的下拉列表框中输入 1000，单击“确定”按钮。图 4-24 筛选人事科图 4-25“自定义自动筛选方式”对话框（4） 同理，可以筛选出补助工资小于180 的人员，筛选最终结果如图 4-26 所示。图 4-26 自动筛选最终结果Excel 的格式化操作与Word 相同，在这里字体格式化操作步骤不再赘述。案例 2如图 4-27 所示，按照下列内容建立一张工作表
17、，并完成下列操作： 1计算出每一种商品的价值（价值=单价*数量），填入相应的单元格中。2. 将“单价”栏中的数据设置为货币样式，并将列宽设定为20。3. 按商品名称的拼音字母顺序进行升序排列，并按商品名称对数量进行分类汇总求和（替换当前分类汇总，汇总结果显示在数据下方）。图 4-27 工作表计算出每一种商品的价值操作步骤如下：（1） 在D3 单元格中输入=B3*C3，按回车键。（2） 用“填充柄”复制公式求其他单元格的结果。设置货币样式及列宽操作步骤如下：（1）将B3:B12 区域选中，单击菜单【格式】，选择“单元格”命令，出现图4-28 所示的“单元格格式”对话框，在“数字”选项卡的“分类”
18、中，选择“货币，”在“货币符号”中，选择“￥”，单击“确定”按钮。（2） 单击菜单【格式】，选择“列”|“列宽”命令，在出现的“列宽”对话框中，输入20，单击“确定”按钮。图 4-28“单元格格式”对话框排序及分类汇总操作步骤如下：（1） 在进行分类汇总前，应先对数据清单进行排序，在数据清单中单击任一单元格，单击菜单【数据】，选择“排序”命令，出现图4-29 所示的对话框，在“主要关键字”的下拉列表框中，选择“商品名称”，单击“确定”按钮。图 4-29“排序”对话框（2） 接着单击菜单【数据】，选择“分类汇总”命令，出现图 4-30 所示的对话框，在“ 分类字段”的下拉列表框中，选择“商品名称
19、”，在“汇总方式”中，选择“求和”，在“选定汇总项”中，选择“数量”，最后单击“确定”按钮。图 4-30“分类汇总”对话框验证性实验实验1如图 4-31 所示，按照下列内容建立一张工作表，并完成下列操作： 1将标题改为红色楷体GB2312，16 号字，标题行行高设为 30。2. 将歌手编号用 001、002、010 来表示，并居中（提示：把格式设为文本）。3. 求出每位选手的平均得分（保留两位小数）。4. 按平均得分将名次填入相应单元格（注意：不得改动各条记录间的顺序）。5. 将平均得分最高的前 3 名的所有信息用红色表示（使用条件格式设置）。6. 给整个表格加上蓝色细实线作为表格线，标题除外。实验2图 4-31 工作表如图 4-32 所示，按照下列内容建立一张工作表，并完成下列操作：1. 计 算 学 生 的 总 分 。2计算学生的平均分（结果保留一位小数）。 3用Countif( )函数统计各学科成绩20分的人数。4将学生的姓名和平均分用簇状柱形图表示出来存放到sheet1中。图 4-32 工作表
