大学计算机基础实践教程4.docx

1、大学计算机基础实践教程4大学计算机基础实践教程（文科）第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）右键单击“Sheet

2、1”工作表标签，选择“重命名”命令，输入“差旅费”。实验十 Excel 2003 数据计算及图表制作实验目的1熟悉格式化操作和输入公式的基本方法。2掌握图表的建立和编辑方法。操作指导案例1如图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单元格区域内，如图4-7所示。图4-7移动图表到A8:E18区域案例2如图4-8所示，按照下列内容建立一张

5、工作表，并完成下列操作：1将标题行“职工工资表”（A1:G1）合并居中，并将格式设为黑体，字号20。2在“路程”与“沈梅”之间插入一条记录，数据为：刘怡 F 230.60 100.00 0.00 15.86。3用公式求出实发工资（实发工资=基本工资+奖金+补贴-房租）。4将所有性别为M的改为男，F改为女。5将工作表Sheet2重命名为“工资表”，并复制职工工资表到该工作表A2:G16区域（不包括标题）。6在工作表“工资表”中以“姓名”和“实发工资”为数据区建立一个簇状柱形图图表，刻度最大值为1500，主要刻度单位为150，图表位置放在A17:J35区域内。图4-8工作图表第1-5题的操作此处省

6、略，具体操作可参考前面的例子或教材的相关内容，注意第4题做完后，计算实发工资公式中的F会改为女，应在公式中将女再改为F，重新计算实发工资即可。第6题经过相关的操作，可以得到原始的簇状柱形图图表，如图4-9所示。此时只要用鼠标左键双击该图表左边的“数值轴”，就会出现图4-10所示的“坐标轴格式”对话框，在“刻度”选项卡中，将“最大值”修改为1500，“主要刻度单位”修改为150即可（注意修改值后，该两处的复选框选中状态自动消失，不要再选中）。图4-9原始的簇状柱形图图4-10“坐标轴格式”对话框实验十一Excel 2003 公式与函数实验目的1.熟悉公式及函数的使用。2.掌握复制公式及函数的方法

7、操作指导案例1如图4-11所示，按照下列内容建立一张工作表，并完成下列操作：1计算所有学生的总分。2用COUNTIF ( )统计各学科90分的人数。3将统计结果按科目用簇状柱形图表示出来存放到sheet1中。图4-11 各科成绩工作表计算总分操作步骤如下：（1）在J2单元格中插入函数SUM。（2）用“填充柄”复制其他单元格。使用函数COUNTIF作统计计算操作步骤如下：（1）在C9单元格中插入统计函数COUNTIF，出现如图4-12所示的对话框，在范围Range的文本框中输入C2:C8，条件Criteria的文本框中输入=90，按“确定”按钮得到所求结果。（2）求其他单元格统计结果，用“填充柄

8、”复制。图4-12“函数参数”对话框建立簇状柱形图表操作步骤如下：（1）根据题目分析可以知道，作图需要选择的数据源是各科目和90分人数的统计结果，如图4-13所示。（2）其余操作可仿照建立分离型饼图操作步骤，只是在“图表向导”步骤之2，选择“系列”选项卡，使用鼠标单击“名称”文本框右侧的折叠按钮以折叠文本框，再单击工作表中A9单元格，然后再单击折叠按钮展开文本框（也可以在“名称”文本框中输入90分人数），以替代图例“系列1”，如图4-14所示。图4-13 选择数据源图4-14 选择数据源案例2如图4-15所示，按照下列内容建立一张工作表，并完成下列操作：1用RANK( )函数根据平均分字段完成

9、“名次”字段的填写（注：一定要按照先填写I2：I5之中某一单元格公式后，其它单元格复制（可用填充柄）的方法完成此题，最高分填1（第1名），依次类推）。2将A1:I5单元格内容居中，并将英语、数学、化学、总分、平均分五列单元格中小于60的单元格的字体用红色表示（注：随单元格中数据值的变化，该单元格字体发生变化）。图4-15工作表使用RANK( )函数计算名次操作步骤如下：（1）在I2单元格中插入统计函数RANK( )，出现如图4-16所示的对话框，在指定的数字Number(用来比较该数字在一列数字中相对其他数值的大小)的文本框中输入G2，在Ref（对一组数的引用）的文本框中输入$G$2:$g$5

10、，单击“确定”按钮得到所求结果。图4-16“函数参数”对话框（2）求其他单元格统计结果，用“填充柄”复制。使用条件格式显示数据操作步骤如下：（1）依题意选中C2:G5区域。（2）单击【格式】，选择“条件格式”命令，出现图4-17所示的对话框，在左侧的下拉列表框中选择“小于”，在右侧的下拉列表框中输入60，单击“格式”按钮，在出现的“单元格格式”对话框中，选择字体颜色为“红色”，再返回“条件格式”对话框，单击“确定”按钮。图4-17“条件格式”对话框案例3如图4-18所示，按照下列内容建立一张工作表，并完成下列操作：1利用公式填写评语，条件是，月考1与月考4成绩比较，如月考1大于月考4，由评语为

11、“成绩退步，严重警告中”，如果月考4大于月考1成绩40分以上，则评语为“进步神速，希更加努力”，除此以外的情况评语都为“进步平平，望加倍努力”。2修正表格里设定的错误的公式，并重新更正原有数据（提示：使用AVERAGE算出平均分）。3将4次月考平均成绩大于550的同学的姓名以蓝色加粗突出表示（提示：利用求平均公式以及筛选功能）。4根据表格的特点，给表格制作一张能够反映某同学4次月考成绩升降情况的独立图表（要求：图表类型为簇状柱形图，图表标题为：月考成绩情况表，显示数据标志，其他的可取舍，图表名称为：图表1）。5删除工作表Sheet2、Sheet3。6标题部分使用楷体，加粗。并给表格加上蓝色表格

12、边框线（标题除外），使表格看起来更美观。图4-18工作表使用逻辑函数IF填写评语操作步骤如下：（1）单击H3单元格，根据题意在编辑栏内输入=IF(F3-C340,进步神速,希更加努力,进步平平，望加倍努力)，按回车键完成H3单元格的评语（注意在英文输入法下，输入上述非中文符号）。（2）利用“填充柄”完成其他单元格的评语，如图4-19所示。图4-19填写评语使用函数AVERAGE求平均分及个人平均成绩操作步骤如下：（1）在C13单元格插入函数AVERAGE，确认所求区域C3:C12无误后，按回车键。（2）利用“填充柄”求得D3:F3单元格区域的平均分。（3）与上面同理，使用函数AVERAGE，可

13、以求得G3:G12区域的个人平均成绩。自动筛选及格式化操作这里省略。制作簇状柱形图的独立图表操作步骤如下：（1）依据题意选中姓名和四次月考成绩作为制作图表的数据源，如图4-20所示。（2）其余操作可仿照建立分离型饼图操作步骤进行，注意在向导步骤之3的“标题”选项卡中，将“图表标题”内填写“月考成绩情况表”，在“数据标志”选项卡中选中“值”，在向导步骤之4，选中“作为新工作表插入”单选项，将“Chart1”改为“图表1”，完成后如图4-21所示。图4-20选择数据源图4-21独立工作图表该题删除工作表及格式化操作省略，边框线的设置参见教材4.2.2。实验十二 Excel 2003 数据管理实验目

14、的1.掌握工作表中筛选记录的方法。2.掌握排序及分类汇总的方法。操作指导案例1如图4-22所示，按照下列内容建立一张工作表，并完成下列操作：1计算表中的实发工资=基本工资+补助工资-扣款。2利用自动筛选，筛选出“人事科”中基本工资大于或等于1000并且补助工资小于180的人员。3将筛选出的符合条件的最后一行字体设置为黑体、字形设置为粗体。 图4-22数据清单计算实发工资操作步骤如下：（1）在G2单元格中输入=D2+E2-F2，按回车键后求得G2单元格的实发工资。（2）再利用“填充柄”完成其他单元格的实发工资。自动筛选操作步骤如下：（1）在数据清单中单击任一个单元格，单击菜单【数据】，选择“筛选

15、”|“自动筛选”命令后，如图4-23所示。 图4-23 执行“自动筛选”后的数据清单（2）单击“科室”右侧的按钮，在出现的下拉列表中选择“人事科”如图4-24所示。（3）单击“基本工资”右侧的按钮，在出现的下拉列表中选择“自定义”，打开如图4-25所示的“自定义自动筛选方式”对话框，在左侧的下拉列表框中选择“大于或等于”，在右侧的下拉列表框中输入1000，单击“确定”按钮。图4-24筛选人事科图4-25“自定义自动筛选方式”对话框（4）同理，可以筛选出补助工资小于180的人员，筛选最终结果如图4-26所示。图4-26自动筛选最终结果Excel的格式化操作与Word相同，在这里字体格式化操作步骤

16、不再赘述。案例2如图4-27所示，按照下列内容建立一张工作表，并完成下列操作：1计算出每一种商品的价值（价值=单价*数量），填入相应的单元格中。2将“单价”栏中的数据设置为货币样式，并将列宽设定为20。3按商品名称的拼音字母顺序进行升序排列，并按商品名称对数量进行分类汇总求和（替换当前分类汇总，汇总结果显示在数据下方）。图4-27工作表计算出每一种商品的价值操作步骤如下：（1）在D3单元格中输入=B3*C3，按回车键。（2）用“填充柄”复制公式求其他单元格的结果。设置货币样式及列宽操作步骤如下：（1）将B3:B12区域选中，单击菜单【格式】，选择“单元格”命令，出现图4-28所示的“单元格格式

17、”对话框，在“数字”选项卡的“分类”中，选择“货币”，在“货币符号”中，选择“￥”，单击“确定”按钮。（2）单击菜单【格式】，选择“列”|“列宽”命令，在出现的“列宽”对话框中，输入20，单击“确定”按钮。图4-28“单元格格式”对话框 排序及分类汇总操作步骤如下： （1）在进行分类汇总前，应先对数据清单进行排序，在数据清单中单击任一单元格，单击菜单【数据】，选择“排序”命令，出现图4-29所示的对话框，在“主要关键字”的下拉列表框中，选择“商品名称”，单击“确定”按钮。图4-29“排序”对话框（2）接着单击菜单【数据】，选择“分类汇总”命令，出现图4-30所示的对话框，在“分类字段”的下拉列

18、表框中，选择“商品名称”，在“汇总方式”中，选择“求和”，在“选定汇总项”中，选择“数量”，最后单击“确定”按钮。图4-30“分类汇总”对话框验证性实验实验1如图4-31所示，按照下列内容建立一张工作表，并完成下列操作：1将标题改为红色楷体GB2312，16号字，标题行行高设为30。2将歌手编号用001、002、010来表示，并居中（提示：把格式设为文本）。3求出每位选手的平均得分（保留两位小数）。4按平均得分将名次填入相应单元格（注意：不得改动各条记录间的顺序）。5将平均得分最高的前3名的所有信息用红色表示（使用条件格式设置）。6给整个表格加上蓝色细实线作为表格线，标题除外。图4-31工作表实验2如图4-32所示，按照下列内容建立一张工作表，并完成下列操作：1. 计算学生的总分。2计算学生的平均分（结果保留一位小数）。3用Countif( )函数统计各学科成绩20分的人数。4将学生的姓名和平均分用簇状柱形图表示出来存放到sheet1中。图4-32工作表