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