实训指导模块四 电子表格处理软件Excel.docx
- 文档编号:9062140
- 上传时间:2023-05-16
- 格式:DOCX
- 页数:52
- 大小:867.15KB
实训指导模块四 电子表格处理软件Excel.docx
《实训指导模块四 电子表格处理软件Excel.docx》由会员分享,可在线阅读,更多相关《实训指导模块四 电子表格处理软件Excel.docx(52页珍藏版)》请在冰点文库上搜索。
实训指导模块四电子表格处理软件Excel
第4章电子表格处理软件Excel2003
实训一工作表的编辑与格式化——制作销售统计表
一、任务介绍
又到了年末,颐星数码城要求商城内所有商家填写一份销售统计表用做销售量调查统计。
数码城办公室的主任助理Joyce需要制作销售统计表的模板供商家填写(除商品信息外其余信息要求手工填写),如图4-1所示。
金鑫电脑公司的销售统计员根据要求填写了该表,如图4-2所示。
图4-1销售统计表模板
图4-2转置粘贴数据并计算各季度销售总量
◆根据要求制作并修饰销售统计表。
◆转置粘贴各季度销售量,计算各季度销售总量。
二、任务分析
为了顺利完成本次工作任务,Joyce和各商家的销售统计员需要应用到一些知识。
◆统计报表的标准样式。
◆Excel电子表格处理软件中的数据输入、转置粘贴、表格的格式化、合计公式的使用、直线的绘制。
◆Excel文件的保存。
◆Excel电子表格的排版及打印。
三、任务分解
本任务可以分解为以下个子任务:
子任务1建立销售统计表
子任务2格式化销售统计表
子任务3保存文件
子任务4输入数据并求合计
子任务5排版并打印销售统计表
四、任务指导
子任务1、建立销售统计表
(1)启动Excel2003,新建工作簿文件。
(2)在sheet1相应的单元格中输入如图4-3所示的内容。
图4-3在sheet1中建立销售统计表
技巧提示:
A4:
A10中的序号使用序列填充的方法,有多种方法,如下:
方法1:
使用填充句柄填充等差数列,步骤如下:
步骤1:
在A4和A5分别输入1和2;
步骤2:
选中A4:
A5;
步骤3:
拖动填充句柄至A10。
方法2:
使用Ctrl和填充句柄填充,步骤如下:
步骤1:
在A4输入1;
步骤2:
拖动填充句柄至A10,同时按住键盘Ctrl键直至松开鼠标左键。
方法3:
使用菜单命令填充,步骤如下:
步骤1:
在A4输入1;
步骤2:
选中A4:
A10;
步骤3:
执行菜单“编辑”—“填充”—“序列”命令,依下图在对话框中设置:
图4-4填充序列对话框
注:
因为选中了A4:
A10区域,所以无需填写终止值,如果步骤2缺省,则需要指定终止值即7。
提示2:
B4:
B10中的产品编号使用输入文本字符及填充的方法,步骤如下:
步骤1:
在B4输入“’01101”(注意英文半角单引号);
步骤2:
拖动填充句柄至B10。
提示3:
E3:
H3可使用自定义填充的方法,步骤如下:
步骤1:
在E3输入“一季度”;
步骤2:
执行菜单“工具”→“选项”命令,打开“自定义序列”选项卡,选中“自定义序列”单选框中的“新序列”,在右侧输入序列中输入如下图所示的序列,回车换行,完成后单击“添加”按钮,确定:
图4-5选项对话框的自定义序列选项卡
步骤3:
选中E3,拖动句柄至H3。
子任务2、格式化销售统计表(格式化效果如图4-1所示)
(1)格式化标题:
合并A1:
H1并将标题居中,设置标题格式为黑体、18号、加粗。
步骤1:
选择A1:
H1,单击工具栏的“合并及居中”按钮;
步骤2:
在工具栏中选择字体为18号黑体、加粗。
(2)格式化表头:
设置第三行表头格式为楷体、12号、加粗、居中,并为表头单元格填充灰色-25%的底纹。
步骤1:
选择A3和G3;
步骤2:
在工具栏中选择字体为12号楷体、加粗、居中,填充颜色为“灰色-25%”。
(3)格式化数据:
合并A11:
D11,设置A4:
H11格式为宋体、12号、居中。
步骤1:
选择A11:
D11,单击工具栏的“合并及居中”按钮;
步骤2:
选择A4:
H11,在工具栏中选择字体为12号宋体、居中。
(4)格式化表格其他部分:
设置第二行的格式为仿宋、12号、加粗,“制表人:
”单元格的格式为宋体、12号。
步骤1:
选择A2:
H2,在工具栏中选择字体为12号仿宋、加粗、居中;
步骤2:
选择G14,在工具栏中选择字体为12号宋体。
步骤3:
使用绘图工具栏的直线工具在相应的内容后面绘制直线,并适当地调整其位置。
(5)添加表格线:
为A3:
H11添加单线边框线。
步骤1:
选择A3:
H11;
步骤2:
单击工具栏“边框”按钮下拉菜单中的“所有框线”命令。
子任务3、保存文件.
将文件以“EXLX01.xls”为文件名保存在教师指定的文件夹中。
步骤1:
单击工具栏的“保存”按钮;
步骤2:
在“保存位置”中选择指定文件夹,将文件名修改为“EXLX01”,文件类型选择“Microsoftoffice工作簿”;
步骤3:
单击“保存”。
子任务4、输入数据并求合计(格式化效果如图4-2所示)
(1)从教师提供的“报表”文档中将所需的数据转置粘贴到文档中,完成销售数据的数据。
步骤1:
打开“报表”文档,选择B1:
H6;
步骤2:
单击工具栏的“复制”按钮;
步骤3:
打开EXLX01文档,单击C4单元格,再单击编辑菜单的“选择性粘贴”按钮,选中“转置”,单击确定。
(2)在E11:
H11分别求出一季度~四季度的销售合计总量。
步骤1:
选择E11:
H11;
步骤2:
单击工具栏的“自动求和”按钮。
(3)将文件以“EX01”为文件名另存在同一文件夹中。
步骤1:
单击文件菜单的的“另存为”按钮;
步骤2:
在“保存位置”中选择指定文件夹,将文件名修改为“EX01”,文件类型选择“Microsoftoffice工作簿”;
步骤3:
单击“保存”。
至此,EXLX01文档的保存效果如图4-1所示,EX01文档如图4-2所示。
子任务5、排版并打印销售统计表
为了更好的视觉效果和充分利用纸张将销售统计表打印出来,需要在一页A4纸内排列,效果如下图所示。
图4-6在一张A4纸中排列3份销售统计表
(1)设置标题行的行高为25、第2行行高为20,第A列的宽度为5、第B-D列的宽度为10、第E-H列的宽度为12。
步骤1:
重新打开EXLX01文档,单击行号1,在快捷菜单中选择“行高”命令,在对话框中输入25,单击确定。
同样方法设置第2行行高为20;
步骤2:
单击列号A,在快捷菜单中选择“列宽”命令,在对话框中输入5,回车确定。
拖动鼠标选择列号B-D,同样方法在对话框中输入10。
同样方法设置第E-H列的宽度为12。
(2)通过设置页面设置纸张为A4、方向为纵向,上下左右页边距各为1、页眉页脚边距为0,在工作表中复制出2个销售统计表。
步骤1:
执行菜单“文件”—“页面设置”命令,打开页面设置对话框:
在“页面”选项卡中设置纸张方向为纵向,纸张大小为A4;在“页边距”选项卡中设置上下左右页边距各为1、页眉页脚边距为0;确定;
步骤2:
拖动选择第1~14行,单击工具栏的“复制”按钮;单击A17,单击工具栏的“粘贴”按钮;同样方法在A33处粘贴。
(3)保存文件。
步骤:
单击工具栏的“保存”按钮。
至此,EXLX01文档的保存效果如图4-6所示。
(4)打印销售统计表。
步骤1:
执行“文件”菜单中的“打印”命令。
注意:
在打印文档前,请确认以下准备工作是否完成:
(1)操作系统中已安装打印机。
(2)打印机与计算机连接正常。
(3)确保打印机电源打开。
步骤2:
在出现的“打印内容”对话框中设置,如图4-7。
图4-7设置打印各选项
在“打印内容”对话框中主要需要设置“名称”、“打印内容”等选项。
(1)名称:
即为选择您使用的打印机,包括连接到本计算机的打印机或者网络打印机。
(2)打印内容:
指示打印的内容是选定的单元格区域、整个工作簿的所有工作表、选定的某个或某几个工作表。
五、实训小结
在本次实训中我们将制作销售统计表这项工作分解为4个子任务,通过建立销售统计表,格式化销售统计表,然后将文件保存;在表中输入数据并求销售量合计,并将文件另保存;最后对文档进行必要的页面排版,打印多份销售统计表。
通过这次实训,我们可以基本掌握Excel电子表格处理软件中的文档的建立与保存、数据的输入(填充)与基本计算、表格的格式化、文档的排版及打印等相关知识。
六、课后拓展练习
利用电子表格处理软件Excel建立如下所示的成绩表及后面的成绩分析统计表,其中单元格的高度与宽度可适当调整、记录行数任意,但务必保证所有内容可以在一页中显示。
然后在工作簿中复制出三张相同的工作表,将四张工作表的标签分别更名为:
08生物
(1)班、08生物
(2)班、08生物(3)班、08生物(4)班。
图4-8学生成绩表
实训二公式与函数的使用——制作成绩考核登记表
一、任务介绍
学院教务部门要求各系部制作统一的成绩考核登记表,记录学生整个学期的学习综合情况。
根据学院要求,生物系根据该系的具体情况制作了该系计算机应用基础课程的成绩考核登记表(如图4-9所示),并在成绩表后设计了成绩统计分析表格(如图4-10所示),从而全面体现各班该门课程学习的总体情况。
08
(1)班的班委会根据该班学生08-09学年第1学期的计算机应用基础课程的考核情况填写了相应的信息及各项目的成绩,现需要统计各个大项并作全班成绩分析。
图4-9成绩考核登记表样板
图4-10成绩考核登记表后的成绩分析
◆计算平时作业的综合评价成绩、学期总评成绩及对应的综合排名和等级。
◆统计分析各个项目的(男/女)总分、平均分、最高分、最低分、及格人数及及格率、优秀人数及优秀率、平时成绩与总评成绩的各分数段人数等。
二、任务分析
为了对计算机应用基础课程的成绩做全面的统计分析,生物系08
(1)班的班干部需要应用到公式和函数的相关知识。
此外,由于班级人数较多,成绩考核登记表需要多页排版。
◆基本公式的输入、编辑和填充。
◆逻辑函数IF的使用。
◆统计函数AVERAGE、MAX、MIN、RANK及COUNTIF、FREQUENCY的使用。
◆数学与三角函数SUM和SUMIF的使用。
◆多页电子表格的排版。
三、任务分解
本任务可以分解为以下个子任务:
子任务1计算平时作业的综合评价、学期总评并统计各分数段人数
子任务2计算个人综合排名及等级
子任务3统计各项的(男/女)总分、平均分、最高分及最低分
子任务4统计各项及格人数与及格率、优秀人数与优秀率
子任务5为多页成绩考核登记表排版
四、任务指导
子任务1、在文件EXLX02.xls中计算平时作业的综合评价、学期总评并统计各分数段人数
(1)用公式计算平时作业的综合评价与学期总评,结果保留一位小数,公式如下:
综合评价=个人作业的平均*40%+小组作业的平均*60%
学期总评=综合评价*30%+机试*40%+笔试*20%+考勤+课堂表现。
步骤1:
选择H7,单击编辑栏;
步骤2:
在编辑栏中输入:
=((D7+E7)/2)*40%+((F7+G7)/2)*60%
方法2:
=AVERAGE(D7,E7)*40%+AVERAGE(F7,G7)*60%;
步骤3:
执行菜单“格式”—“单元格”命令,打开“数字”选项卡,选择“数值”分类,并定义小数位数为1;
步骤4:
双击H7的填充句柄;
步骤5~8:
学期总评的计算方法与综合评价相似,提供参考公式如下:
=H7*30%+I7*40%+J7*20%+K7+L7
(2)用统计函数FREQUENCY计算平时成绩及学期总评的分布情况,其中60分以下(不含60)为“不及格”、60~85分(不含85)为“良好”、85分及以上为“优秀”。
步骤1:
在M60和M61分别输入59、84;
步骤2:
选择N60:
N62区域,执行菜单“插入”→“函数”命令,选择“统计”类别中的FREQUENCY函数;
步骤3:
单击Data_array后面的输入框,选择H7:
H56作为参数,单击Bins_array后面的输入框,选择M60:
M61作为参数;
步骤4:
然后同时按下Shift+Ctrl+Enter输入函数;
图4-11使用FREQUENCY函数计算平时成绩的分布
步骤5~8:
学期总评分布的计算方法与平时成绩相似,提供参考参数如下图所示:
图4-12使用FREQUENCY函数计算学期总评分布的参数
子任务2、计算个人综合排名及等级
(1)用统计函数RANK计算每个学生学期总评的综合排名。
步骤1:
选择N7,单击编辑栏旁边的“插入函数”按钮,选择“统计”类别中的RANK函数;
步骤2:
单击Number后面的输入框,选择M7作为参数,单击Ref后面的输入框,选择$M$7:
$M$56作为参数,忽略参数Order,确定;
步骤3:
双击H7的填充句柄;
(2)用逻辑函数IF分析每个学生成绩等级,其中60分以下(不含60)为“不及格”、60~85分(不含85)为“良好”、85分及以上为“优秀”,检查与前面的统计结果是否相同,如果不相同是为什么?
步骤1:
选择O7;
步骤2:
在编辑栏输入:
=IF(M7<60,"不及格",IF(M7<85,"良好","优秀"));
步骤3:
双击H7的填充句柄。
提示:
学期总评的计算实际结果均为一位小数,故FREQUENCY函数的分段点应该改为59.9和84.9才能计算出正确结果。
子任务3、统计各项的(男/女)总分、平均分、最高分及最低分
(1)用数学与三角函数SUM计算各种考核方式的成绩之和。
步骤1:
选择D58;
步骤2:
单击自动求和按钮;
步骤3:
将参数修改为D7:
D56;
步骤4:
拖动D58的填充句柄至J58。
(2)用数学与三角函数SUMIF统计男、女生各项总分。
步骤1:
选择D59,单击编辑栏旁边的“插入函数”按钮,选择“数学与三角函数”类别中的SUMIF函数;
步骤2:
逐个输入如下图所示的三个参数,然后确定;
图4-13使用SUMIF函数统计男生第一次个人作业总分
步骤3:
拖动D59的填充句柄至J59;
步骤4~6:
统计女生各项总分的方法与上相似,其中D60单元格的公式参考如下:
=SUMIF($C$7:
$C$56,"女",D7:
D56)。
(3)用统计函数AVERAGE、MAX、MIN分别统计各项平均分、最高分和最低分。
步骤1:
选择D61;
步骤2:
在自动求和按钮的下拉菜单中选择“平均值”;
步骤3:
将参数修改为D7:
D56;
步骤4:
拖动D61的填充句柄至J61;
步骤5~12:
计算最高分和最低分的方法与上相似。
子任务4、统计各项及格人数与及格率、优秀人数与优秀率
(1)用COUNTIF函数统计各项及格人数与优秀人数。
步骤1:
选择D64,单击编辑栏旁边的“插入函数”按钮,选择“统计”类别中的COUNTIF函数;
步骤2:
逐个输入如下图所示的三个参数,然后确定;
图4-14使用COUNIF函数统计第一次个人作业的及格人数
步骤3:
拖动D64的填充句柄至J64;
步骤4~步骤6:
优秀人数的统计与上相似,其中D66单元格的公式参数如下图所示:
图4-15使用COUNIF函数统计第一次个人作业的优秀人数
(2)用公式计算各项成绩的及格率与优秀率,结果用百分比表示。
步骤1:
选择D65,在编辑栏输入:
=D64/50;
步骤2:
单击工具栏的“百分比样式”按钮;
步骤3:
拖动D65的填充句柄至J65;
步骤4~步骤6:
优秀率的计算与上相似。
子任务5、为多页成绩考核登记表排版
由于成绩考核登记表需要打印上交,需要对表格进行排版。
(1)设置纸张大小为A4、方向为纵向,上下页边距为2、左右为1.5,页眉边距为0、页脚边距为1,自定义页脚内容为日期左对齐、页码右对齐,参考效果如图所示:
图4-16页脚样张
步骤1:
执行菜单“文件”—“页面设置”命令,打开页面设置对话框,在“页面”选项卡中设置纸张方向为纵向,纸张大小为A4;在“页边距”选项卡中设置上下页边距各为2、左右页边距各为1.5,页眉边距为0、页脚边距为1;
步骤2:
在“页眉/页脚”选项卡单击“自定义页脚”按钮,在“左”框中单击插入当前日期的按钮,在“右”框中输入“第”、“页”并在两个字中间单击插入页码的按钮:
图4-17设置页脚对话框
(2)设置1~6行为打印标题行。
在第2页中页应当能够看到表格的标题和表头,因此需要将1~6行设置为打印标题行。
步骤1:
执行菜单“文件”→“页面设置”命令,打开页面设置对话框;
步骤2:
在“工作表”选项卡中单击顶端标题行后面的输入框,拖动选择工作表的1~6行,确定。
在预览效果中可以看到每一页都有标题和表头。
五、实训小结
在本次实训中我们将制作成绩考核登记表这项工作分解为5个子任务,在已经建立好表格的情况下通过使用基本公式及逻辑函数、统计函数和数学与三角函数统计分析各项数据,全面、多角度地评价了全班的学习情况。
通过这次实训,我们可以基本掌握Excel电子表格处理软件中的公式的基本编辑及常见函数的使用。
六、课后拓展练习
调查你班上十位同学及他们亲人的收入情况,根据调查的数据建立收入一览表(样张如下图所示),然后利用所学的公式和函数作如下统计(结合样张,仅作参考,对应素材“收入一览表.xls”文档):
1、根据编号和姓名分别统计总人数
2、求男职工的人数
3、求年龄大于30岁的人数
4、求公务员年收入的总和
5、求最低年收入
6、求第2高年收入
7、求张越的收入排在第几多
8、按年收入进行排名
9、统计年龄<30岁,30(包括30)岁到40岁,40(包括40)岁到50岁,50岁以上的各阶段人数
10、根据以下条件填写代号:
男医生代号为MD,工人或公务员代号均为W,其余的为空白
11、根据姓名求姓氏
12、查找陈醉的年龄
图4-18收入一览表
实训三财务函数的使用——制作存贷款预算表
一、任务介绍
良好的理财手段是保证生活质量的便捷途径,存贷款及投资预算和方案选择能帮助我们合理地理财。
不论我们是贷款,还是参加银行的零存整取或是投资,Excel中提供的财务函数能帮助我们快速计算出按照既定方案需要付出的代价或是可以获取的回报。
在这个实训中,首先我们需要学习如何根据既定方案计算简单的贷款代价和存款(投资)回报。
然后我们就可以调整方案的细节(如存、贷款年限、考虑利率的变化有等因素),决定我们的理财方案。
◆下图是根据每月存款额、年利率及存款期限计算出的最终存款额:
图4-19存款计算表1
◆根据每月存款额、年利率、存款期限及期望值计算出的现值存入数额:
图4-20存款计算表2
◆根据每月存款额、年利率、存款期限计算出的税后本息总额:
图4-21存款计算表3
◆根据贷款总额、年利率及贷款期限计算出的每月还款额:
图4-22还款计算表1
◆根据一项已经购买了的保险年金的月底回报、投资收益率、投资年限计算出的该份保险年金的价值,然后与购买金额对比看是否合算:
图4-23投资预算表
以及,
◆当每月存款额与存款期限固定不变时,不同的年利率对应的存款总额:
图4-24零存整取单变量模拟运算表
◆当存款期限为10年不变,不同每月存款额及年利率对应的存款总额:
图4-25零存整取双变量模拟运算表
◆当贷款总额与贷款期限固定时不同的年利率对应的月偿还额,或是贷款总额与年利率固定时不同的贷款年限对应的月偿还额,或是贷款年限10年不变时不同的贷款总额及年利率对应的月偿还额:
图4-26贷款单变量及双变量模拟运算表
二、任务分析
为了顺利完成本次工作任务,我们除了需要巩固复习函数的基本编辑与使用等基本知识外,还需要具备以下知识。
◆对基本存贷款及投资方式的了解及相应的计算方法。
◆财务函数FV、PV及PMT的使用。
◆单变量模拟运算的计算方法。
◆双变量模拟运算的计算方法。
三、任务分解
本任务可以分解为以下个子任务:
子任务1存款预算
子任务2贷款预算
子任务3投资预算
子任务4存款方案选择
子任务5贷款方案选择
四、任务指导
打开EXLX05.xls文件,在相应工作表中根据存贷款方案完成预算。
子任务1、存款预算:
(1)某人每月存款3000元,共累计存款20年(240个月),已知存款利率为2.50%,计算最终存款额,结果添加货币符号“¥”并保留2位小数。
步骤1:
在C6单元格中插入FV函数,参数如下图所示:
图4-27存款计算表1的FV函数的相关参数及结果
注意条件中的每月存款额应为负值,否则计算结果将为负值。
步骤2:
单击结果单元格,选择“格式”—“单元格”命令,在“数字”选项卡中单击“货币”分类,设置小数位数为2。
(2)某人预算在10年后使存款数额达到300000元,现在每月存入2000元,年利率为4.125%,计算其开始应存入银行的现值数额,结果添加货币符号“¥”并保留2位小数。
提示:
在C14单元格中插入PV函数,参数如下图所示:
图4-28存款计算表2的PV函数的相关参数及结果
设置货币类型与小数位数的方法与上相同,不再赘述。
(3)某人参加银行的零存整取储蓄,每月存入1500元,年利率为3%,请在相应单元格用函数公式计算出2年期满后的减除利息税后的本息总和,设置单元格格式例如为:
¥1500.22。
(注:
利息税为6%,必须使用公式,取2位小数位数)
步骤1:
在C18:
C20单元格分别输入相应的参数3%、24、-1500;
步骤2:
在C21单元格输入函数,参加下列提示:
提示:
税后本息总和=本息总和-利息税
利息税=利息*利率
利息=本息总和-本金
即:
税后本息总和=本息总和-(本息总和-本金)*利率。
根据上述公式,在C21单元格输入如下所示的公式即可:
=FV(C18/12,C19,C20)-(FV(C18/12,C19,C20)-C19*(-C20))*6%
注意公式中的本金计算,引用C20单元格内容应添加负号。
子任务2、贷款预算
某人向银行贷款100000元,贷款年限为10年,已知贷款年利率为8%,在C6单元格中利用函数计算出“每月应还款”,结果保留2位小数。
提示:
在C6单元格中输入PMT函数,参数如下图所示:
图4-29贷款计算表的PMT函数的相关参数及结果
子任务3、投资预算
假设要购买一项保险年金,该保险可以在今后20年内,每月末回报¥600.此项年金的购买成本为80000,假定投资回报率为8%。
该项投资合算吗?
提示:
在C7单元格输入PV函数:
=PV(C4/12,C5*12,C3)。
计算结果为¥-71,732.58即总收益率为¥-71,732.58,而购买成本为80000元,因此该项投资不合算。
子任务4、存款方案选择
(1)已知一项零存整取方案的每月存款额为1500元,存款期限为120个月,请根据年利率的不同计算得到的存款总额。
步骤1:
在C7单元格中输入FV函数:
=FV(B7/12,C5,-C4);
步骤2:
选择B7:
C11单元格区域,选择“数据”—“单元格”命令,在“输入引用列单元格”后面输入框中单击,选择B7单元格,单击确定。
结果如图4-24所示。
(2)已知一项零存整取方案的存款期限为120个月,请根据年利率的不同和月存款额的不同计算得到的存款总额。
步骤1:
在B15单元格输入FV函数:
=FV(B22/12,120,G15);
步骤2:
选择B15:
F21单元格区域,选择“数据”—“单元格”命令,在“输入引用行单元格”后面输入框中单击,选择G15单元格,单击确定;在“输入引用列单元格”后面输入框中单击,选择B22单元格,单击确定。
结果如图4-25所示。
子任务5、贷款方案选择
(1)已知一项贷款方案的贷款额为100000元,贷款期限为120个月,请根据年利率的
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 实训指导模块四 电子表格处理软件Excel 指导 模块 电子表格 处理软件 Excel