第4章 电子表格处理实验.docx
- 文档编号:11632134
- 上传时间:2023-03-29
- 格式:DOCX
- 页数:20
- 大小:673.43KB
第4章 电子表格处理实验.docx
《第4章 电子表格处理实验.docx》由会员分享,可在线阅读,更多相关《第4章 电子表格处理实验.docx(20页珍藏版)》请在冰豆网上搜索。
第4章电子表格处理实验
第4章电子表格处理实验
基础实验
实验目的:
通过一系列的基础实验,要求学生熟练掌握电子表格的常用操作。
1.熟练掌握电子表格的制作过程,熟悉数据的输入、数据的自动填充、数据的格式化、表格的修饰等。
2.熟练掌握电子表格中数据的运算,熟练使用常用的公式和函数。
3.熟练掌握电子表格中图表的制作、更改、修饰等操作。
4.熟练掌握电子表格中数据的排序、筛选、分类汇总等数据管理和分析。
基础实验1工作表的编辑和排版
本次实验要求学生新建一个文件“商品销售统计表.xls”,然后选择该文件中的第一个工作表(sheet1),对其进行编辑和排版,实验的操作结果如下图4.1所示。
图4.1基础实验1效果图
步骤1
新建电子表格。
在桌面上新建一个Excel文件(即工作簿),并将文件重命名为“商品销售统计表.xls”,然后选择该文件中的第一个工作表(sheet1),将工作表重命名为“实验一”。
步骤2
输入随机数据(注意数据类型)。
如果输入的单元格数据之间没有联系,则称这些数据为随机数据。
随机数据需要手工进行输入,原始数据如图4.2所示:
A1单元格数据“鞋类商品销售周报表”,第二行中所有的列名汉字,以及“月份”、“货品名称”、“颜色”、“数量”、“单价”、“折扣”列中的所有数据。
图4.2“鞋类商品销售周报表”原始数据
输入数据前,可以先根据数据的不同类型。
具体方法是,选中某列数据,单击“格式”菜单中的“单元格”命令,弹出的“单元格格式”对话框。
在该对话框中,选择“数字”选项卡,根据实际情况设置数据类型。
例如,设置“单价”列的数据类型,选择“分类”为“货币”,“小数位数”为“2”,“货币符号”为“¥”,如图4.3所示。
单元格格式对话框中的“数字”,“对齐”,“字体”,“边框”,“图案”选项卡可以分别对单元格进行具体的格式设置。
输入数据注意事项:
(1)Excel中常用的数据类型有三类:
数值、文本、日期。
对数值型数据,将自动除去整数位最高位的0和小数位低位的0。
当数值型数据超过11位时,将自动将数据转换为“科学记数”即指数数据。
货币型数据也和数值型数据类似,在输入“单价”列数据时,不用输入小数点后的0。
只需要输入有效数据位。
小数点后面的0,是在所有数据输入完成后,选中所有数据,点击增加小数点按钮
完成,或采用设置数据格式由计算机自动设置。
(2)对一些特殊的数值数据,例如身份证号码、最高位要求是0的学号或销售单号等符号数据信息时,必须用特殊方式输入。
第一种方法是在输入这些数据之前,设置该“单元格的格式”中“数字”属性,将“常规”属性,设置为“文本”属性。
如步骤二所示。
另一种方法是在输入数据的前面,添加英文符号“’”,例如表格中的销售单号“’02097”。
图4.3“单元格格式”对话框的“数字”选项卡
步骤3
自动填充有序数据。
如果单元格中的数据之间是有序数据,输入这些数据,可以采用自动填充的方法。
只需要输入一个数据,其它数据可以采用自动填充方式自动输入。
例如,输入工作表中的“销售单号”列数据,具体方法是:
单击鼠标左键选中B3单元格,打开“格式”菜单下“单元格”选项,弹出“单元格格式”窗口,选择“数字”选项。
将“常规”属性设置为“文本”属性。
然后在B3单元格中输入“02097”,将鼠标移动到B3单元格的右下角,当鼠标变成黑十字型(即填充柄)时,按住鼠标左键向下拖动,用该方法输入“销售单号”列所有数据。
自动填充数据注意事项:
(1)文本型数据只有两种自动填充方式:
如果是非数值数据,填充相同数据。
(2)如果是数类型文本数据,则可填充相同数据,或以步长1递增的数据。
如果是数值型和日期型数据,填充的数据可以按用户设置的任意序列方式填充。
序列的设置方法是:
打开“编辑”菜单下“填充”选项,单击“序列”命令,在弹出的“序列窗口”中,用户可以设置任意序列。
(3)在自动填充中,如果要填充的数据是相同、或是步长为1递增方式,可以用按“Ctrl”拖动和不按“Ctrl”拖动进行转换。
步骤4
使用公式和函数。
工作表中有两列数据都是通过计算得到的,而不是直接输入。
“金额”列的计算公式是:
金额=数量*单价*(1-折扣/100)。
具体方法是首先设置该列的数据类型为货币型,小数位数为“2”,货币符号为“¥”;然后单击鼠标左键选中H3单元格,输入“=E3*F3*(1-G3/100)”,按“回车键”确定;最后向下拖动自动填充柄,运算“金额”列的所有数据。
“销售额排名”列要用到RANK函数,该函数的格式是“=Rank(number,ref,[order])”,计算某个单元格数据number在数据区域ref中的排名,其中order省略的话为降序排名。
注意单元格数据会随着行数的变化而变化,因此需要使用相对地址;而排名范围的数据区域始终固定不变,因此需要使用绝对地址。
输入“销售额排名”列的具体方法是:
单击鼠标左键选中I3单元格,输入“=Rank(H3,$H$3:
$H$24)”,如图4.4所示,按“回车键”确定;然后向下拖动自动填充柄,运算“金额”列的所有数据。
图4.4单元格中输入RANK函数
另一方法是,单击鼠标左键选中H3单元格,单击
按钮,用选择RANK函数,在弹出的“函数参数”对话框中输入相应数据,如图4.5所示,确定即可,然后用填充柄自动填充整列。
图4.5设置RANK函数的参数
步骤5
标题的合并居中。
利用“合并与居中”工具制作表头,具体方法是:
按住鼠标左键,拖动鼠标选中第一行的A1到I1区域,单击“格式”工具栏上
按钮,合并表头所在单元格。
然后设置标题为宋体,18磅,双下划线,蓝色。
步骤6
设置表格的边框底纹。
选中需要设置格式的区域,在“单元格格式”对话框中设置单元格格式,可以设置单元格的“数字”、“对齐”、“字体”、“边框”、“图案”等属性。
例如,设置第二行为灰色底纹,外框线为粗实线,内框线为细实线,具体方法是:
选中第二行的A2:
I2区域,单击“格式”菜单中的“单元格”命令,弹出的“单元格格式”对话框。
在该对话框中,选择“图案”选项卡,设置颜色为“灰色-25%”;然后选择“边框”选项卡,“线条样式”为粗实线,点击“预置”的“外边框”,再选择“线条样式”为细实线,点击“预置”的“内边框”,在预览中可以看到效果,如图4.6所示。
图4.6“边框”选项卡
设置单元格格式注意事项:
(1)“单元格格式”窗口下的“数字”选项,可以为所选区域的数字设置小数位、在数值前添加货币符、将数字转换为百分比数字等。
(2)“单元格格式”窗口下的“对齐”选项,为所选单元格设置对齐方式。
本实验中将除表头以外,所有单元格的对齐方式设置为“水平对齐”居中,“垂直对齐”居中。
(3)“单元格格式”窗口下的“字体”选项,为所选字体设置格式。
(4)“单元格格式”窗口下的“边框”选项,为所选单元格设置边框。
在Excel中,默认所有单元格没有边框线。
本实验中为A2:
J20单元格区域设置边框线。
步骤7
设置条件格式。
使用条件格式可以对工作表中满足条件的格式设置格式,使之突出显示出来,一目了然。
例如,设置“金额”列中所有大于等于5000的数据为红色,加粗倾斜,具体方法是:
选择“金额”列H3:
H24,单击“格式”菜单中的“条件格式”命令,弹出的“条件格式”对话框,如图4.7所示。
在该对话框中,选择“单元格数值”“大于或等于”“5000”,然后单击“格式”按钮。
此时会进一步弹出“单元格格式”对话框,选择“字体”选项卡,“字形”为“加粗倾斜”,“颜色”为“红色”,预览中即显示设置的效果。
图4.7“条件格式”对话框
步骤8
将创建的电子表格保存,并退出Excel。
基础实验2工作表的图表制作
在基础实验1的基础上,进行各种类型图表的制作,学习图表的插入,编辑和修改,实验的操作结果如下图4.8所示。
图4.8基础实验2效果图
步骤1
工作表数据的复制。
本实验要用到基础实验1建立的表格(即Excel工作表“实验一”),因此需要复制已有表格中的部分数据到另一个工作表中,具体方法是:
打开文件“商品销售统计表.xls”,选择“实验一”工作表中的数据区域A2:
H7(即5月1日的销售数据),复制;然后选择“Sheet2”工作表中的A1单元格,粘贴,在出现的
按钮的下拉菜单中选择“保留源格式”的粘贴方式。
复制表格注意事项:
(1)选择性粘贴:
复制粘贴通常有以下两种选项,“保留源格式”的复制粘贴,源数据表的格式和数据均会保留;“匹配目标区域”的复制粘贴,此时源数据表的格式会丢失,新表的格式变化为所粘贴区域的格式。
复制粘贴时可能会出现单元格中显示“####”的情况,这表示单元格的宽度不够,将单元格所在列加宽即可正常显示。
(2)被复制的表格中含有公式或函数:
如果表格中含有公式或函数,粘贴后的表格,如果数据位置发生移动,则由公式或函数所计算的数据可能和原表格不一致,甚至提示公式或函数引用错误。
避免这种现象有两种方法:
一种是重新修改粘贴后的表格中的公式或函数。
另一种是粘贴表格时不直接用工具栏上“粘贴”按钮,而是采用“编辑”菜单下的“选择性粘贴”,用“选择性粘贴”下的“数值”或“值和数字格式”粘贴。
(3)工作表的完全复制:
如果复制是复制整个工作表,并且要求新表的格式和数据与源表完全不变,则可以采用完全复制的方法。
具体方法是:
用Ctrl+A全选整个源工作表,复制,然后选中目标工作表中的A1单元格,粘贴。
步骤2
建立柱形图。
选择“销售单号”和“金额”两列数据(即按住Ctrl,选择B1:
B6,H1:
H6两个区域),插入图表,弹出“图表向导”对话框。
按照步骤进行如下选择(选定后点击“下一步”按钮):
选择图表类型为“簇状柱形图”(图4.9),数据产生在“列”(图4.10),图表标题为“2009-5-1销售金额统计图”(图4.11),图表位置为“作为其中的对象插入”sheet2(图4.12),“确定”后生成图4.8所示的柱形图。
图4.9图表类型
图4.10图表源数据
图4.11图表选项
图4.12图表位置
图表向导使用注意事项:
(1)步骤1—图表类型可选择各种类型的图表,包括自定义图表类型。
一般反映对象的对比差异选择柱形图或条形图;强调对象的变化趋势选择折线图;反映对象整体与部分的比例大小用饼图。
(2)步骤2—图表源数据可根据实际需要选择数据区域,使用“行”还是“列”,数据系列的名称,值,分类标志等。
(3)步骤3—图表选项由六个选项卡可以具体设置图表的各处标题,坐标轴的格式及刻度大小,网格线格式,图例位置等等。
(4)步骤4—图表位置如果选择“作为新工作表插入”则新建图表产生在一个默认名称为“CHART1”的工作表中。
步骤3
调整柱形图。
用鼠标拖动生成的柱形图到A7:
H16区域,并调整图表大小使之刚好适合区域大小。
调整图表注意事项:
(1)选中图表,当鼠标变为
形状时,可拖动图表;按住CTRL键,可复制图表;当鼠标变为双向箭头时可调整图表大小。
(2)删除数据及图表。
如果删除图表中的某部分,选中图表中的某部分相应区域,按DELETE键;如果是删除整个图表,则可以选中整个图表,按DELETE键。
(3)选中图表或者图表的某个区域,单击鼠标右键,均可以弹出快捷菜单,可以通过快捷菜单中的不同命令对图表进行重新编辑、修改和美化等操作。
步骤4
建立饼图。
选择B4:
B6和H4:
H6两个区域(即休闲鞋的“销售单号”和“金额”两列数据,记得按住Ctrl键),插入图表,弹出“图表向导”对话框。
按照步骤进行如下选择(选定后点击“下一步”按钮):
选择图表类型为“三维饼图”,数据产生在“列”,图表标题为“休闲鞋销售比例图”,图例“靠左”,数据标志的数据标签包括“百分比”,图表位置为“作为其中的对象插入”sheet2,“确定”后生成柱形图。
生成饼图后可根据需要对图表进行调整,如调整饼图大小和位置,使之位于A17:
H24区域;选中饼图的各个区域,调整图表区格式、数据系列格式、数据标志格式、图例格式等。
调整后的饼图如图4.8所示。
步骤5
保存电子表格以及两个图表。
将Sheet2改名为“实验二”,然后单击“文件”的“保存”命令,并退出Excel。
基础实验3工作表的数据管理
根据基础实验1创建的表格,对其进行电子表格数据的筛选、排序、分类汇总等操作。
实验的效果如下图4.13和图4.14所示。
步骤1
工作表数据的复制。
打开文件“商品销售统计表.xls”,选择“实验一”工作表中的数据区域A2:
H24(除开“销售额排名”列),复制;然后选择“Sheet3”工作表中的A1单元格,粘贴,在出现的
按钮的下拉菜单中选择“值和数字格式”的粘贴方式。
图4.13自动筛选后的效果
图4.14分类汇总后的效果
步骤2
数据的自动筛选。
首先,单击鼠标左键选中表格中任意一个有数据信息的单元格,单击“数据”菜单下的“自动筛选”命令,此时,可以看到在每个字段名右边出现
筛选按钮。
下面实现筛选出5月1日或5月7日销售出的跑鞋的数据,分析得知,有两个筛选条件,一是对“日期”列筛选出“2009-5-1”或“2009-5-7”的记录;二是在此基础上对“货品名称”列筛选出“跑鞋”的记录。
(1)单击“日期”字段边上的“筛选”按钮,选择“自定义”选项。
打开“日期”筛选对话框,见图4.15。
第一个条件设置“日期”参数“等于”“2009-5-1”,第二个条件设置“日期”参数“等于”“2009-5-7”,注意这两个条件之间用“或”连接(选中对话框中的“或”单选钮),单击“确定”按钮。
图4.15第一次进行自动筛选
(2)在筛选过一次的工作表中再次进行筛选,单击“货品名称”字段边上的“筛选”按钮,可以在出现的下拉菜单中直接选择“跑鞋”,如图4.16示,即可得到最终结果。
筛选后效果见图4.13。
图4.16第二次进行自动筛选
筛选数据注意事项:
(1)如果要重新显示表格中的所有记录,可选择“数据”菜单的“筛选”子菜单中的“全部显示”命令。
“自动筛选”命令在第一次选择时,进入自动筛选;第二次选择该命令时,取消自动筛选功能,恢复工作表中的所有记录。
(2)用自动筛选器筛选数据,当筛选条件分布在不同列时,需要进行多次的筛选。
如果想一次性设置多个条件并一次性筛选出来,可以使用“高级筛选”。
例如,同样筛选出5月1日或5月7日销售出的跑鞋的数据,如果用高级筛选的话,条件区域如图4.17所示,高级筛选仅需使用一次筛选即可实现。
图4.17高级筛选的条件区域
(3)观察筛选结果可以发现,被筛选过的列旁边的筛选按钮为蓝色,没有被筛选过的列旁边的筛选按钮仍然为黑色。
(4)取消自动筛选可以重新显示工作表中的所有记录,可选择“数据”菜单的“筛选”子菜单中的“全部显示”命令(或选择筛选按钮,在列表中选择“全部”)。
“自动筛选”命令在第一次选择时,进入自动筛选;第二次选择该命令时,取消自动筛选功能,恢复工作表中的所有记录。
步骤3
数据的排序。
取消数据的自动筛选,使得工作表的数据全部显示。
下面对工作表的数据进行排序,如果排序的条件只有一个,可以用简单排序。
例如,只要求表格中数据按“日期”升序排序,可以单击鼠标左键选中A1单元格,然后单击工具栏上
按钮;降序,则单击
按钮。
如果排序的条件有多个,例如,按“货品名称”,“日期”、“销售单号”三个字段进行数据的升序排序,则可按如下步骤进行操作:
(1)选取需要排序的数据区域(A3:
H23)
(2)选择“数据”菜单中“排序”命令,这时弹出“排序”对话框。
(3)在“主要关键字”列表框中选择“货品名称”,单击“升序”;在“次要关键字”列表框中选择“日期”,单击“升序”;在“第三关键字”列表框中选择“销售单号”,单击“升序”,如图4.18所示。
(4)选择“有标题行”,单击“确定”按钮。
图4.18多重关键字排序
步骤4
数据的分类汇总。
注意,分类汇总前必须排序。
例如,按照“货品名称”对工作表中的数据进行排序,可按如下步骤进行操作:
(1)选中“货品名称”所在的C1单元格,然后单击工具栏上
按钮,即可实现。
可以看到数据按照“货品名称”分类,“跑鞋”记录在前,“休闲鞋”记录在后。
(2)将鼠标放在数据区域的任意一个单元格中,然后单击“数据”菜单下的“分类汇总”命令。
弹出“分类汇总”对话框,按对话框提示设置参数,如图4.19所示。
设置分类字段为“货品名称”(即排序关键字),汇总方式为“求和”,在“选定汇总项”中,选定“数量”和“金额”两个复选框,“汇总结果显示在数据下方”的复选框去掉(即汇总结果显示在上方,单击“确定”按钮,最后效果见实验二效果图4.14。
图4.19分类汇总
(3)单击
按钮可以展开或折叠相应级别的分类汇总项;如果要按同一个分类字段进行多次分类汇总,则“分类汇总”对话框中“替换当前分类汇总”前的复选框必须取消选中,否则只能产生一次分类汇总;在“分类汇总”对话框中,单击“全部删除”命令,即可恢复原工作表。
提高实验
实验目的:
通过一系列的提高实验,要求学生能自主学习和掌握电子表格中工作表的编辑和排版、公式和函数的使用、图表的使用,提高电子表格的综合应用能力。
提高实验1公式和函数的高级应用
公式和函数是Excel中重要的功能之一,利用公式和函数,可以实现多种表格的制作。
,如图4.20所示,使用常用的公式和函数制作阶梯形式的“九九乘法表”,实验要求如下:
1.使用公式和函数实现九九乘法表(注意公式中的相对引用、绝对引用、混合引用)。
2.要求用自动填充方式输入和运算数据(即使用通用的公式)。
3.对表格进行排版设置(如使用“单元格格式”添加边框,底纹等)。
图4.20九九乘法表
提高实验2工作表的综合制作
利用Excel电子表格软件制作一个“学生成绩统计表”,输入数据,使用函数并插入图表,如图4.21所示,实验要求如下:
1.输入各种类型的数据,设置正确的数据格式以及数据有效性。
2.使用常用公式和函数,计算总分,平均分,名次,总评,优秀率,男女生人数比以及男女生总分比。
3.设置单元格的边框和底纹,合并单元格,美化表格。
4.插入图表,制作“自定义类型”的柱形图和三维饼图,并修饰图表。
图4.21学生成绩统计表
习题
1.在Excel2003中,对于D5单元格,其绝对单元格表示方法为______。
A:
D5B:
D$5C:
$D$5D:
$D5
2.在Excel2003中,一个工作簿中默认包含______张工作表。
A:
3B:
2C:
5D:
4
3.在Excel2003中,C7单元格中有绝对引用=AVERAGE($C$3:
$C$6),把它复制到C8单元格后,双击它单元格中显示______。
A:
=AVERAGE(C3:
C6)B:
=AVERAGE($C$3:
$C$6)
C:
=AVERAGE($C$4:
$C$7)D:
=AVERAGE(C4:
C7)
4.在Excel2003中,当C7单元格中有相对引用=SUM(C3:
C6),把它复制到E7单元格后,双击它显示出______。
A:
=SUM(C3:
C6)B:
=SUM(C4:
C7)C:
=SUM(E3:
E6)D:
SUM(E3:
E7)
5.Excel2003中引用单元格时,单元格名称中列标前加上"$"符,而行标前不加;或者行标前加上"$"符,而列标前不加,这属于______。
A:
相对引用B:
绝对引用C:
混合引用D:
其它几个选项说法都不正确
6.在Excel2003中,下列______是正确的区域表示法。
A:
A1#D4B:
A1..D5C:
A1:
D4D:
A1>D4
7.在Excel2003中,不连续单元格选择,只要按住______的同时选择所要的单元格。
A:
CtrlB:
ShiftC:
AltD:
ESC
8.在Excel2003工作表中执行插入一行命令时,将在活动单元格的______插入一整行单元格。
A:
下边B:
上边C:
左边D:
右边
9.下面有关Excel2003工作表.工作簿的说法中,正确的是______。
A:
一个工作簿可包含多个工作表,缺省工作表名为Sheet1/Sheet2/Sheet3
B:
一个工作簿可包含多个工作表,缺省工作表名为Book1/Book2/Book3
C:
一个工作表可包含多个工作簿,缺省工作表名为Sheet1/Sheet2/Sheet3
D:
一个工作表可包含多个工作簿,缺省工作表名为Book1/Book2/Book3
10.在Excel2003中,不能在一个已打开的工作簿中增加新工作表的操作是______。
A:
右击工作表标签条中某个工作表名,从弹出菜单中选"插入"菜单项
B:
单击工作表标签条中某工作表名,从"插入"菜单中选"工作表"菜单项
C:
单击任意单元格,从"插入"菜单栏中选"工作表"菜单项
D:
单击工作表标签条中某个工作表名,从弹出菜单中选"插入"菜单项
11.已在Excel2003某工作表的F10单元格中输入了八月,再拖动该单元格的填充柄往上移动,请问在F9.F8.F7单元格会出的内容是______。
A:
九月.十月.十一月B:
七月.六月.五月
C:
五月.六月.七月D:
八月.八月.八月
12.已在Excel2003某工作表的F1.G1单元格中分别填入了3.5和4.5,并将这2个单元格选定,然后向左拖动填充柄,在E1.D1.C1中分别填入的数据是______。
A:
0.5.1.5.2.5B:
2.5.1.5.0.5C:
3.5.3.5.3.5D:
4.5.4.5.4.5
13.在Excel2003中进行排序操作时,最多可按_______关键字进行排序。
A:
1个B:
2个C:
3个D:
根据你选择的排序方式才能确定排序项目的个数
14.现已在Excel2003某工作表的B列输入了一系列数据,并知C列数据是B列数据的25%,请问下面_______计算公式不能满足该计算要求。
A:
=B2*25%B:
=B2*0.25C:
=B2*25/100D:
=B2*25
15.在Excel2003中,下列操作不能调整行高的是______。
A:
将鼠标移到两个行号之间,当鼠标指针变成垂直方向带箭头的“十”字时,上下移动鼠标可以调整行高
B:
右击行号,从弹出菜单中选“行高”
C:
单击行号以选中某一行,再从“格式”菜单中选“行”,接着从下级菜单中选“行高”
D:
右击某行中任意单元格(此时并没有选中整行),再从弹出菜单中选“行”
16.在Excel2003中,函数count(4,,)的值为______。
A:
1B:
2C:
3D:
4
17.当Excel2003工作表中未对小数位数进行特殊设置时,函数round(3*2.1+10,0)的值为______。
A:
6.3B:
10C:
16.3D:
16
18.当Excel2003工作表中未对小数位数进行特殊设置时,函数max(round(22.5,0),round(125.3,-1))的值为______。
A:
23B:
22C:
125D:
130
19.若需计算Excel2003某工作表中A1.B1.C1单元格的数据之和,需使用下述哪个计算公式______。
A:
=count(A1:
C1)B:
=sum(A1:
C1)C:
=su
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 第4章 电子表格处理实验 电子表格 处理 实验