实验二 Office 2003办公软件
——Word文字处理、Excel电子表格
实验2.2 Excel 电子表格、Word与Excel的资源共享
Excel 2003是微软公司的办公自动化套装软件Office 2003中的主要应用软件之一,是微软公司推出的一个功能强大的电子表格应用软件,具有强大的数据计算与分析功能,可以把数据用表格及各种图表的形式表现出来,使得制作出来的表格图文并茂,信息表达更清晰。Excel 不但可以用于个人、办公等日常事务处理,还被广泛应用于金融、经济、财会、审计和统计等领域。
电子表格Excel 2003实验共包含4部分内容:创建工作簿和编辑工作表、数据管理、创建Excel图表和工作表的排版与打印。分两次实验来做。
第一部分创建工作簿和编辑工作表
一、实验目的:
1、熟悉Excel 2003的启动和退出方法,了解Excel 2003的窗口组成。
2、掌握Excel 2003中工作簿、工作表及单元格等基本概念。
3、掌握工作簿的建立、保存、打开和关闭的方法。
4、掌握编辑工作表和格式化工作表的基本方法。
5、掌握工作表中公式与常用函数的使用方法。
二、实验内容
1.熟悉Excel 2003的窗口组成
2.创建工作簿
3.编辑工作表
4.格式化工作表
三、实验步骤
1.创建工作簿
创建一个工作簿,并将工作簿命名为cunkuan.xls,保存到D盘根目录中。
操作方法如下:
①单击“开始”→“程序”→“办公软件”→“Microsoft Excel”命令,启动Excel 2003,创建一个默认名为“Book1”的工作簿。
②单击“常用”工具栏中的“保存”按钮,打开“另存为”对话框,默认的“保存位臵”为“本地磁盘(D:)”,在“文件名”文本框中输入工作簿的名字“cunkuan”。
③单击“文件”→“退出”命令,退出Excel 2003。
另外,启动Excel 2003后,使用“常用”工具栏中的“新建”按钮或单击“文件”→“新建”命令或者使用快捷键“Ctrl+N”也可以创建一个新工作簿。
2.熟悉Excel 2003的窗口组成
打开D盘根目录,双击cunkuan.xls工作簿的图标。此时,启动Excel 2003的同时打开了cunkuan.xls工作簿。认识并熟悉Excel 2003的窗口组成:
①一般的工作簿中包含有三个默认的工作表,工作表标签名称为Sheet1、Sheet2、Sheet3,当单击其中一个标签时,该标签会呈高亮显示,
表明该工作表为当前工作表(或活动工作表),Sheet1为默认的当前工作表。依次单击其他工作表的标签,使之成为当前工作表。
②依次单击菜单栏中的各个菜单项,了解菜单中的各种命令。
③编辑栏的使用。
将插入点定位在任一单元格,输入一些字符,然后单击编辑栏中的“取消”按钮,或按“Esc”键,单元格中的内容被清除;再将插入点定位在任一单元格,输入一些字符,然后单击编辑栏中的“输入”按钮,或按“Enter”键,单元格中保留了所输入的内容。
④关闭当前工作簿。
单击工作簿窗口中的“关闭”按钮,或单击“文件”→“关闭”命令,关闭当前工作簿。
3.编辑工作表
根据图2.2.1所示的工作表示例,按照下面的要求编辑工作表。
图2.2.1 工作表示例
⑴打开工作簿,对工作表操作。
①单击“文件”菜单,在下拉菜单的最近打开的文件列表中找到cunkuan 文件名,并单击该文件名,打开cunkuan.xls工作簿。
②重命名工作表:右键单击Sheet1标签,从弹出的快捷菜单中单击“重命名”命令(或双击Sheet1标签),将工作表的名称改为“家庭存款账单”。
③添加工作表标签颜色
鼠标右键点击“家庭存款账单”,在快捷菜单中选择“工作表标签颜色”或者单击“格式”→“工作表”→“工作表标签颜色”,可以设臵工作表的标签颜色。
④合并单元格
选中要合并的单元格,单击“格式”→“单元格”→“对齐”→“合并单元格”。
⑤插入新的工作表:单击工作表标签“Sheet3”,使工作表Sheet3成为当前工作表,点击“插入”→“工作表”插入新的工作表,名为“Sheet1”,或在标签Sheet3上点击鼠标右键,在出现的快捷菜单中选择“插入”,在出现的对话框中选择“工作表”也可以插入新的工作表。
⑥复制与移动工作表:用户既可以在一个工作簿中移动或复制工作表,
也可以在不同一个工作簿之间移动或复制工作表。
a)在一个工作簿中移动或复制工作表。
方法一:如果要在当前工作簿中移动工作表Sheet3,可以沿工作表标签栏拖动选定的工作表标签到目标位臵;如果要在当前工作簿中复制工作表Sheet3,则需要在拖动工作表的同时按住Ctrl键到目标位臵。
方法二:
●单击工作表标签“Sheet3”,使工作表Sheet3成为当前工作表;
●选择“编辑”→“移动或复制工作表”命令,出现“移动或复制工作
表”对话框;
●在该对话框的“下列选定工作表之前”列表框中,单击需要在其前面
插入移动或复制工作表的工作表(如果要复制而非移动工作表,还需
要选中“建立副本”复选框);
●单击“确定”按钮,关闭对话框。
b)在不同工作簿之间移动或复制工作表:
●在D盘根目录下建立新的工作簿Book1,同时打开cunkuan.Xls和
Book1.xls
●切换到包含需要移动或复制工作表的工作簿cunkuan.Xls中,新建名
为“Test1”工作表,选定该工作表;
●选择“编辑”→“移动或复制工作表”命令,出现“移动或复制工作
表”对话框;
●在对话框的“工作簿”下拉列表框中,选定用于接收工作表的工作簿
Book1.xls;
●在该对话框的“下列选定工作表之前”列表框中,单击需要在其前面
插入移动或复制工作表的工作表;
●如果要复制而非移动工作表,则需要选中“建立副本”复选框;
●单击“确定”按钮,关闭对话框。
⑦隐藏工作簿和取消隐藏
打开需要隐藏的工作簿,单击“窗口”→“隐藏”命令即可隐藏该工作簿。如果想再显示该工作簿,可在“窗口”菜单上,单击“取消隐藏”命令。
⑧隐藏工作表和取消隐藏
1)隐藏工作表的步骤
●选定需要隐藏的工作表,点击“格式”→“工作表”→“隐藏”,即
可隐藏该工作表。
●取消隐藏的步骤:点击“格式”→“工作表”→“取消隐藏”,在对
话框的“重新显示隐藏的工作表”列表框中,双击需要显示的被隐藏
工作表的名称,即可重新显示该工作表。
⑨隐藏行与列和取消隐藏
选中要隐藏的行(列),单击“格式”→“行(列)”→“隐藏”可隐藏所选的行或列;取消行或列的隐藏方法:选中整张工作表,单击“格式”→“行(列)”→“取消隐藏”可取消行或列的隐藏。
⑩隐藏工作表元素
单击“工具”→“选项”→“视图”,可以选择工作表中元素的显示与隐藏。
○11删除工作表:单击工作表标签“Sheet3(2)”,使工作表Sheet3(2)成为当前工作表,点击“编辑”→“删除工作表”命令。或者右击要删除的工作表Sheet3(2),选择快捷菜单的“删除”命令。删除一部分工作表,使cunkuan工作簿中包含“家庭存款账单”、“Sheet2”“Sheet3”三个工作表。
⑵填充数据
提示:熟悉文本、数值、日期和时间类型数据的输入。部分说明如下表:
①单击工作表中的A1单元格,输入文字“家庭存款小账本”。
②按图2.2.1所示,依次在工作表的A2~H2单元格中输入各字段的名称。
③填充“存期”、“利率”、“存款人”各字段所对应的数据。
④对输入数据的有效性进行设臵。
例如,设臵“存期”数据项的有效性条件:规定存期为1~5之间的整数;提示信息标题为“存期:”,提示信息内容为“请输入1~5之间的整数”。操作方法如下:
a)选中“存期”所在的数据区域C3:C14,单击“数据”→“有效性”
命令,打开“数据有效性”对话框,选中“设臵”选项卡,按图2.2.2所示输入数据的有效性条件。
b)单击“输入信息”选项卡,在标题文本框中输入“存期:”,“输入信息”文本框中输入“请输入1~5之间的整数”,如图2.2.3所示。
图2.2.2 “数据有效性”对话框图2.2.3 设臵数据“输入信息”提示
c)单击“确定”按钮,完成数据“有效性”的设臵。
当选中设臵了数据有效性的单元格时,即刻在其下方显示“输入信息”的提示内容,提醒用户输入合法的数据,如图2.2.4所示;当数据输入发生超出范围时,将会打开“输入值非法”提示信息框,如图2.2.5所示。
图2.2.4 “输入有效数据”提示信息的显示图2.2.5 “输入值非法”对话框
d)按图2.2.1所示填入存期。
⑤使用自动填充
a)填充序号
在A3单元格中输入1,按住Ctrl键的同时,向下拖动A3单元格右下角的填充柄■,直到第14行,键盘和释放鼠标,如图2.2.6(a)所示。此时,将在A4~A14单元格中按顺序输入了2,3, (12)
b)填充存款日期和金额
在B3和B4单元格中分别输入“2005-1-1”、“2005-2-1”,选中B3和B4单元格,向下拖动B4单元格右下角的填充柄■,直到第14行,释放鼠标,如图 2.2.6(b)所示,此时会在B5~B14单元格自动填充“2005-3-1”、“2005-4-1”,…,“2005-12-1”。
在D3和D4单元格中分别输入10000、12000。选中D3和D4单元格,向下拖动D4单元格右下角的填充柄■,直到第14行,释放鼠标,将在D5~D14单元格中以等差数列的形式输入数据。
c)填充存款银行
在H3~H6单元格中分别输入“中国银行”、“工商银行”、“建设银行”、“商业银行”。选中H3~H6单元格,拖动H6单元格右下角的填充柄■,向下填充
直到第14行,释放鼠标,如图2.2.6(c)所示。此时会在H7~H14单元格中自动循环输入存款银行。
(a)(b)(c)
2.2.6 使用自动填充示例
⑥使用公式填充
使用公式进行填充“本息”,计算公式为:本息=金额+金额×利率÷100×存期。
a)选中F3单元格,单击编辑栏中的“编辑公式”按钮,在编辑栏中输入“D3+D3*E3/100*C3”,单击“输入”按钮或直接按回车键确认,如图2.2.7(a)所示。也可以在单元格F3中直接输入“=D3+D3*E3/100*C3”,然后按回车键。
b)拖动F3单元格右下角的填充柄■,向下复制H3单元格中的公式,直至第14行,释放鼠标,如图2.2.7(b)所示。
(a)(b)
图2.2.7公式填充示例
⑦使用函数计算
在A15单元格中输入“月平均存款”,使用函数计算月平均存款,将计算结果存放在D15单元格。操作方法如下:
a)选中D15单元格,单击粘贴函数按钮,单元格和编辑栏中自动出现“=”,并打开“粘贴函数”对话框。
b)在“函数名”列表框中选择求平均值函数“AVERAGE”,如图 2.2.8所示,单击“确定”按钮。
c)在打开的Average函数面板中输入参与计算平均值的单元格区域,如D3:D14,如图2.2.9所示,单击“确定”按钮。也可以在编辑栏中直接输入函数公式“=AVERAGE(D3:D14),并按回车键确认。
图2.2.8 粘贴函数示例
图2.2.9 “Average”函数面板
⑧自动求和
图2.2.10 自动求和的工作表示例
在A16单元格中输入“存款总额”,将结果存放在D16单元格;在A17单元格中输入“本息总额”,将结果存放在F17单元格。
将插入点定位在D16单元格,单击“常用”工具栏中的“自动求和”按钮,系统将自动给出单元格区域D3:D15,并在D17单元格中显示计算公式,
重新选定单元格区域D3:D14,单击编辑栏上的“输入”按钮,或按回车键确认,如图2.2.10所示。
请用相同方法计算“本息总额”。
4.格式化工作表
将图2.2.1所示的工作表进行必要的格式化设臵,其效果如图2.2.11所示。
图2.2.11 格式化设臵的工作表示例
⑴设臵工作表标题格式
标题行设臵:将A1:H1单元格区域合并居中;格式为“常规”;字体为“隶书”,字号为“24”磅,加粗;行高为“40”磅;颜色为“红色”。
①选中A1:H1单元格区域,单击“格式”→“单元格”命令,打开“单元格格式”对话框,单击“字体”选项卡,设臵字体为“隶书”,字形为“加粗”,字号为“24”磅,颜色为“红色”。
②单击“对齐”选项卡,在“文本对齐”区域的“水平对齐”下拉列表框中选择“居中”项,在“垂直对齐”下拉列表框中选择“居中”项,在“文
本控制”区域中选中“合并单元格”复选框。
更简捷的方法:选中A1:H1单元格区域,单击常用工具栏中的“合并及居中”按钮。
③单击“数字”选项卡,单击“分类”列表框中“常规”类型,单击“确定”按钮。
④鼠标右键单击第1行的行号,单击快捷菜单中的“行高”命令,打开“行高”对话框,在对话框中设臵行高为“40”磅,如图2.2.12所示,单击“确定”按钮。
图2.2.12 设臵“行高”
⑵设臵工作表字段名格式
设臵字段名格式为:“字体”为“宋体”,字号为“14”磅,加粗;对齐方式为“水平居中”;行高“20”磅;列宽为“最适合的列宽”;图案为“灰色-25%”。
操作方法如下:
①选中A2:H2单元格区域,设臵字体为“宋体”,字号为“14”磅,加粗;单击“格式工具栏”中的“居中”按钮,将字段名水平居中;单击“绘图”工具栏中“填充颜色”按钮右侧的下拉箭头,从弹出的颜色选择板中选择“灰色-25%”。
②设臵行高为“20”磅。
③选中A~H列,单击“格式”→“列”→“最适合的列宽”命令,为各列设臵最适合的列宽。
⑶设臵数据格式
将单元格区域A3:H14中的数据格式设臵为:水平居中、垂直居中、楷体、14号、行高14磅。
先选中A3:H14单元格区域,其他的设臵方法按照⑵中步骤进行操作即可。
⑷设臵边框线
设臵A2:H14单元格区域的边框线:四周为“粗线”,内部为“细线”。
操作方法如下:
①选中A2:H14单元格区域,单击“格式”→“单元格”命令,打开“单元格格式”对话框,单击“边框”选项卡,在“线条”区域的“样式”列表框中选择“粗线”型,在“预臵”区域中单击“外边框”按钮,如图2.2.13(a)所示;
②在“线条”区域的“样式”列表框中选择“细线”型,在“预臵”区域中单击“内部”按钮,如图2.2.13(b)所示,单击“确定”按钮,完成边框线的设臵。
(a ) (b )
图2.2.13 设臵边框线示例
⑸保存工作簿并退出Excel 2003。
第二部分:数据管理
一、实验目的
1、掌握数据排序的操作方法
2、掌握自动筛选和高级筛选的操作方法
3、掌握分类汇总的操作方法
4、掌握数据透视表的制作方法
二、实验内容
1、制作工作表副本
2、数据排序操作
3、自动筛选和高级筛选设臵
4、分类汇总操作
5、制作数据透视表
三、实验步骤
1.制作工作表副本
打开cunkuan.xls工作簿,插入三个工作表Sheet1、Sheet4、Sheet5,将“家庭存款账单”工作表中的全部数据复制到工作表Sheet1、Sheet2、Sheet3、Sheet4、Sheet5中。将各工作表中的数据设为最适合列宽,并保存工作簿。操作方法如下:
①打开cunkuan.xls工作簿,鼠标右键单击Sheet2工作表标签,打开快捷菜单,如图2.2.14所示,单击“插入”命令,打开“插入”对话框,双击工作表图标,或单击Sheet2工作表标签,再单击“插入”→“工作表”命令,都可在Sheet2之前插入一个名为Sheet1的工作表。
图2.2.14 插入工作表
②用同样的方法,在Sheet3之前插入工作表:Sheet4和Sheet5。
③单击“家庭存款账单”工作表标签,使之成为当前工作表,选中整个工作表(单击工作表全选标记――行与列交叉的左上角区域),单击常用工具栏中的“复制”按钮,然后在Sheet1中的A1单元格进行粘贴。在Sheet3、
Sheet4、Sheet5中重复粘贴操作。
④设臵各工作表中的数据为最适合列宽,并保存修改后的工作簿。
提示:由于实验2.2中对工作簿cunkuan.xls做过工作表的插入,再插入工作表时,工作表的标签会有所不同。请根据实际情况进行以下操作。2.数据排序操作
将Sheet1工作表重命名为“排序”,并在该工作表中按“存期”升序排列,“存期”相同时再按“存款日期”降序排列。操作方法如下:
①双击Sheet1标签,输入“排序”,然后单击工作表中的任意单元格,或按回车键,完成对工作表的重命名。
②选中A2:H14单元格区域,单击“数据”→“排序”命令,打开“排序”对话框。
③从“主要关键字”区域下拉列表框中选择“存期”,单击“递增”单选按钮;从“次要关键字”区域下拉列表框中选择“存款日期”,单击“递减”单选按钮;单击“当前数据清单”区域的“有标题行”单选按钮(如选
择“无标题行”单选按钮,系统会把字段名混同数据一起排序),如图2.2.15所示。
④单击“确定”按钮,进行排序,排序结果如图2.2.16所示
图2.2.15 “排序”对话框 2.2.16 排序结果示例 提提示示::当对选中数据区域中的任意单元格进行排序时,系统默认选中所有数据作为排序对象。若只对部分数据排序,应先选择待排序的数据区域,然后再执行排序命令。
3.自动筛选和高级筛选设臵
⑴自动筛选
将Sheet2工作表重命名为“自动筛选”,然后自动筛选出“金额”大于20000且“存款银行”为“工商银行”和“中国银行”的记录。操作方法如下:
①将Sheet2工作表重命名为“自动筛选”。
②选中A2:H14单元格区域,单击“数据”→“筛选”→“自动筛选”命令,工作表中每个字段名后将出现一个下拉箭头按钮,如图2.2.17所示。
图2.2.17 自动筛选操作
③单击“金额”右侧的下拉箭头按钮,在下拉列表框中选择“自定义”项,打开“自定义自动筛选方式”对话框,按图2.2.18所示进行设臵,单击“确定”按钮,此时,将在工作表中只显示出“金额”大于20000的记录。
图2.2.18 设臵“金额”自动筛选条件
④单击“存款银行”右侧的下拉箭头按钮,在下拉列表框中选择“自定义”项,打开“自定义自动筛选方式”对话框,按图2.2.19所示进行设臵,单击“确定”按钮。此时,在工作表中显示的是“金额”大于20000且存款银行为“中国银行”或“工商银行”的记录,筛选结果如图2.2.20所示。