Excel实验wgy.docx
《Excel实验wgy.docx》由会员分享，可在线阅读，更多相关《Excel实验wgy.docx（32页珍藏版）》请在冰豆网上搜索。
Excel实验wgy
第4章中文Excel2003实验操作
EXCEL2003是微软公司推出的Office2003办公软件系列产品之一，是一个功能强大的电子表格软件，专门用于现代理财和数据分析等操作。
可以对大数据量的表格进行各种处理，用各种类型的图表形象地表示数据，具有数据库管理功能。
EXCEL直观易学，广泛应用于众多领域，成为一般办公人员必不可少的应用软件之一。
4．1基础操作实验
实验一EXCEL2003基本操作
一、实验目的
1．掌握中文EXCEL2003的启动、退出及工作簿的保存操作
2．掌握中文EXCEL2003工作表的数据输入及编辑操作，掌握自动填充数据方法。
3．掌握工作表的格式排版操作
4．掌握工作表的插入、删除、移动、复制、重命名操作。
二、实验内容
1、在EXCEL中输入数据。
实验要求：
建立如图4-1-1所示的信息管理专业学生成绩表。
图41-1 信息管理专业学生成绩表
操作步骤：
（1）进入E盘窗口，在窗口的空白处单击鼠标右键，选择“新建|文件夹”命令，输入文件夹名“电子表格实验”，按Enter键完成操作。
（2）从桌面任务栏上的开始菜单启动Excel2003，进入Excel窗口。
（3）单击单元格，输入成绩表中第一行及第二行的内容。
（4）在A3单元格中输入1，在A4单元格中输入2，从A3拖动鼠标至A4，选定A3：
A4单元格区域，将鼠标指针移到选定区域右下角的填充柄处，当鼠标指针变为“+”时，向下拖动至A16单元格，松开鼠标左键，A5至A16的单元格中的数据自动填充。
（5）在B3单元格输入“’200708101”，在B4单元格中输入“’200708102”，从B3拖动鼠标至B4，选定B3：
B4单元格区域，将鼠标指针移到选定区域右下角的填充柄处，当鼠标指针变为“+”时，向下拖动至B16单元格，松开鼠标左键，B5至B16的单元格中的数据自动填充。
（6）在D3：
F17单元中输入成绩数据。
2、编辑修改工作表
实验要求：
（1）
将“王武艺”同学的“大学英语”成绩改为86分；
（2）在“武中山”前添加“郑凯”同学的成绩记录“20070815郑凯、54、68、70”。
操作步骤：
（1）双击E9单元格，将“王武艺”同学的“大学英语”成绩改为86分。
图4-2插入对话框
（2）将光标停留在“武中山”单元格上，单击右键，在弹出的快捷菜单中选择“插入”命令，打开“插入”对话框，如图4-2，选中“整行”单选按钮，则在该处插入一行空单元格，输入“郑凯”的记录。
（3）重新对“序号”列数据进行自动填充。
3、表格格式化
实验要求：
（1）将标题文字格式为：
黑体、18号、加粗、在单元格中水平和垂直方向均为居中显示；
（2）给工作表加边框线：
外框为粗线，内框为细线；
（3）“姓名”所在行添加底纹：
底纹“浅绿”；
（4）对于不及格的成绩设为红色、加粗倾斜；单元格底纹为“浅灰色”。
（5）设标题行行高为27（36像素），表头行高为21，“姓名”列的宽度为10字符，其他所有列的列宽为最合适的列宽。
（6）设置表格中数据格式。
数据结构、大学英语、信息管理学三列中的数字格式设为“数据值”格式：
小数位设为1位；“学号”列的格式设为“文本”。
操作步骤：
（1）
图4-4单元格格式对话框“对齐”选项
图4-3单元格格式对话框“字体”选项
选定单元格区域A1：
F1，单击右键，在弹出的快捷菜单中选择“设置单元格格式”命令，打开“单元格格式”对话框，选择“字体”选项卡，如图4-3所示，“字体”选择“黑体”，“字形”选择“加粗”，“字号”选择“18”；再单击“对齐”选项卡，如图4-4所示，“水平对齐”选择“居中”，“垂直对齐”选择“居中”，选择“合并单元格”复选框，单击“确定”按钮。
（2）选定单元格区域A2：
F17，执行“格式｜单元格”菜单命令，打开“单元格格式”对话框，如图4-5所示，单击“边框”选项卡，单击“样式”中的“粗线”，单击“预置”栏中的“外边框”，单击“样式”中的“细线”，单击“预置”栏中的“内部”，单击“确定”按钮。
图4-5单元格格式对话框“边框”选项
（3）选定“姓名”所在行A2：
F2，单击右键，在弹出的快捷菜单中选择“设置单元格格式”，单击“图案”选项卡，如图4-6所示，选中“浅绿”，单击“确定”按钮。
图4-6单元格格式对话框“图案”选项
（4）选定单元格区域D3：
F17，执行“格式|条件格式”菜单命令，出现如图4-7所示的“条件格式”对话框。
在第3个文本框中输入“0”，第4个文本框中输入“59”，再单击“格式”按钮，在打开的“单元格格式”对话框的“字体”选项卡中设置“颜色”为红色，“字形”为“加粗倾斜”，单击“图案”选项卡，选择“浅灰色”，最后单击“确定”按钮。
图4-7条件格式对话框
图4-8行高设置对话框对话框
（5）选中标题行，单击“格式|行|行高”菜单，在弹出的行高对话框中输入“27”，如图4-8所示，，单击“确定”按钮完成设置。
选中表头行，相同的方法设置表头行高为21；选中姓名所在列，单击“格式|列|列宽”菜单，在弹出的对话框中输入“10”，如图4-9所示，单击“确定”按钮完成设置。
按住“Ctrl”键，同时选中A列、B列、D列、E列、F列，然后单击“格式|列宽|最合适的列宽”菜单，则所选各列自动调整为合适的列宽。
图4-9列宽设置对话框
（6）设置表格中数据格式。
选中D3：
F17区域，单击鼠标右键，单击“设置单元格格式”菜单，在弹出的单元格格式对话框中选中“数字”选项卡，在“分类”文本框中选中“数值”，在“小数位数中”选择“1”，如图4-10所示，单击“确定”完成设置。
选中“学号”所在列，右击鼠标，单击“设置单元格格式”菜单，在弹出的单元格格式对话框中选中“数字”选项卡，在“分类”文本框中选中“文本”，单击“确定”完成设置。
图4-10单元格格式对话框“数字”选项
4、使用自动套用格式
实验要求：
将工作表使用自动套用格式的“古典2”进行格式化。
操作步骤：
（1）选定A1：
F17单元格区域，单击“格式|自动套用格式”菜单命令，弹出“自动套用格式”对话框，如图4-11所示，单击“古典2”示例，单击“选项”按钮，单击“数字”和“列宽/行高”复选框，使其处于不选中状态，其它复选框处于选中状态，单击“确定”按钮。
图4-11自动套用格式对话框
5、保存工作簿
实验要求：
将文件保存在E盘下的“电子表格实验”文件夹中，文件命名为“EXCEL实验一.xls”
操作步骤：
执行“文件|保存”菜单命令，在“保存位置”下拉列表框中选择“E：
＼电子表格实验”，输入文件名“EXCEL实验一.xls”，如图4-12所示：
单击“保存”按钮完成操作。
图4-12保存文件对话框
6、插入、删除、移动、复制、重命名工作表
实验要求：
（1）将工作表“Sheet1”重新命名为“2009_2010第一学期”；
（2）复制““2009_2010第一学期”工作表，重命名为“2009_2010第二学期”，并将其放在“2009_2010第一学期”之后，
（3）将“Sheet2”和“Sheet3”工作表删除；
（4）在“2009_2010第一学期”和“2009_2010第二学期”之间插入一张新工作表，重新命名为“第三学年总评”，将“第三学年总评”工作表移至最后。
（5）保存工作簿并退出Excel.
操作步骤：
（1）右击“Sheet1”工作表标签，在弹出的快捷菜单中单击“重命名”命令，然后在“Sheet1”位置输入“2009-2010第一学期”则将工作表“Sheet1”重新改名为“2009_2010第一学期”。
图4-13移动或复制工作表
（2）右击工作表“2009_2010第一学期”标签，在弹出的快捷菜单中单击“移动或复制工作表”命令，在弹出的“移动或复制工作表”对话框中选中“2009_2010第一学期”，单击“建立副本”复选框，使其处于选中状态，如图4-13所示，单击“确定”按钮，则复制了工作表，系统自动命名为“2009_2010第一学期
（2）”；右击“2009_2010第一学期
（2）”标签，在弹出的快捷菜单中单击“重命名”命令，将其文件名改为“2009_2010第二学期”；单击工作表“2009_2010第二学期”标签，在弹出的快捷菜单中单击“移动或复制工作表”命令，在弹出的“移动或复制工作表”对话框中选中“移至最后”命令，单击“确定”按钮，则将工作表“2009_2010第二学期”移到了最后；
图4-14插入工作表对话框
（3）选中“Sheet2”及“Sheet3”工作表标签，击右键，在弹出的快捷菜单中单击“删除”命令，则删除了“Sheet2”及“Sheet3”工作表
（4）右击工作表“2009_2010第一学期”标签，在弹出的快捷菜单中单击“插入”命令，弹出“插入”对话框，选中“常用”选项卡中的“工作表”选项，如图4-14所示，单击“确定”按钮。
双击刚插入的“Sheet4”工作表标签，将其重命名为“第三学年总评”。
选中“第三学年总评”工作表标签，拖动鼠标将其移到最后位置（5）单击“文件|保存”菜单，则将文件进行了保存。
单击“文件|关闭”菜单，则关闭该文件，单击“文件|退出”菜单，则退出该Excel。
通过完成上述操作后的结果如图4-15所示：
图4-15完成编辑排版后的工作表
三、实验小结
1、在工作表中输入数据时，有的数据显示的格式可能与图中所示不同，这是单元格中数字格式设置的不同造成的。
一般情况下，在输入数据时可不必考虑，等输入完成后，可以通过设置单元格的格式来作相应的调整。
2、在输入数据时，如果使用回车键，默认情况下，活动单元格下移，也可以使用小键盘上的上、下、左、右键改变活动单元格的移动方向。
3、若在输入数字后，单元格中显示“########”，则表示单元格的列宽太小，无法显示整个数字，这时，只要加大列宽即可。
4、在输入全部由数字组成的文本，如邮政编码，电话号码，学号等，必须在数字前面加单引号（’）。
5、输入真分数时，应在前面用0和一个空格引导。
如输入“01/2”表示二分之一，输入“1/2”则表示输入“1月2日”。
输入假分数时应在其整数部分及分数部份之间加一个空格，如“11/2”表示“一又二分之一”，在单元格中显示“11/2”，在编辑栏中显示“1.5”。
6、若要在单元格中换行，按“ALT+Enter”组合键即可。
7、若要在单元格中输入当前日期，可以使用“CTRL+；”组合键，若要在单元格中输入当前时间，可以使用“CTRL+Shift+：
”组合键。
8、输入序列：
通过选定相应的单元格并拖动填充柄，或者使用“序列”命令（指向“编辑”菜单上的“填充”，再单击“序列”），可以快速填充多种类型的数据序列。
9、在行或列中复制数据：
通过拖动单元格填充柄，可将某个单元格的内容复制到同一行或同一列的其他单元格中。
10、创建自定义填充序列：
可以为常用文本项创建自定义填充序列。
单击“工具|选项”菜单命令，在打开的选项对话框中选择“自定义序列”选项卡，如图4-16所示，单击“添加”按钮，在“输入序列”文本框中输入自定义的序列，然后单击“确定”按钮。
图4-16“选项”对话框中的“自定义序列标签
11、在工作簿内移动工作表时，可直接拖动工作表标签至目标位置；复制工作表时，可按住“Ctrl”键，然后拖动工作表标签至目标位置。
12、在不同工作簿间移动或复制工作表，可选打开有关的工作簿，然后利用“窗口|重排窗口”菜单命令，将窗口进行垂直并排，然后再利用拖动的方法，在工作簿间移动或复制工作表。
4．1．2实验二公式与函数
一、实验目的
1、掌握Excel2003中常用函数的使用方法
2、掌握Excel2003中公式的输入方法。
3、掌握单元格的绝对地址与相对地址的使用
二、实验内容
1、计算总数和平均数
实验要求：
新建一工作簿，将其命名为“EXCEL实验二.XLS”，将“EXCEL实验一.XLS”中的“2009_2010第一学期”工作表中的全部内容复制到“EXCEL实验二.XLS”工作簿的“Sheet1”工作表中。
在工作簿“EXCEL实验二.XLS”的工作表“Sheet1”中进行以下操作：
（1）在表格的最后插入一列“总分”和一列“平均分”；
（2）计算“总分”：
总分=数据结构+大学英语+信息管理学
（3）计算“平均分”：
平均分=总分/3或平均分=AVERAGE（D3：
F3）
（4）将D4单元格的成绩修改为“60”、将D5单元格的成绩修改为“98”，将F5单元格的成绩修改为“96”，观察对应的总分及平均分的变化。
操作步骤：
（1）单击G2单元格，输入文字“总分”，单击H2单元格，输入文字“平均分”；
（2）单击G3单元格，使其成为活动单元格；单击“插入|函数”菜单，弹出插入函数对话框，如图4-17所示，
图4-17“插入函数”对话框
（3）在对话框中的“或选择类别”列表框中选中“常用函数”选项，在“选择函数”列表框中选中“SUM”函数，单击“确定”按钮，弹出“函数参数”对话框，如图4-18所示：
图4-18“函数参数”对话框
（4）单击对话框中的“Number1”编辑框，输入D3：
F3（或者单击“Number1”编辑框右边的红色箭头按钮，在工作表中选择D3：
F3单元格区域，单击红色箭头按钮返回“函数参数”对话框），单击“确定“按钮”完成对D3：
F3单元格总分的计算。
（5）拖动G3单击格的填充柄至G17单元格，则G4：
G17单元格区域自动填充了对应单元格的总分数据。
（6）单击H3单元格，使其成为活动单元格；单击“插入|函数”菜单，弹出插入函数对话框。
（7）在对话框中的“或选择类别”列表框中选中“常用函数”选项，在“选择函数”列表框中选中“AVERAGE”函数，单击“确定”按钮，弹出“函数参数”对话框。
（8）单击对话框中的“Number1”编辑框，输入D3：
F3，单击“确定“按钮”完成对D3：
F3单元格平均分的计算。
（9）拖动H3单击格的填充柄至H17单元格，则H4：
H17单元格区域自动填充了对应单元格的总分数据。
（10）单击D4单元格，将其修改为“60”，单击D5单元格，将其修改为“98”，单击F5单元格，将其修改为“96”，则对应的单元格的总分及平均分自动进行了计算，数据进行了更新。
2、计算最大值、最小值和排名
实验要求：
（1）在表格的最后插入一列“名次”和一列“等级”；
（2）在“名次”列利用函数计算每个同学的成绩名次
（3）在表格的D18，E18，F18单元格分别计算三门课程成绩的最高分，在表格的D19，E19，F19单元格分别计算三门课程成绩的最低分。
（4）将D13单元格的成绩修改为42，观察对应的最大值及最小值及名次的变化。
操作步骤：
（1）单击I2单元格，输入文字“名次”，单击J2单元格，输入文字“等级”；
（2）单击I3单元格，使其成为活动单元格，单击“编辑|函数”菜单，弹出“插入函数”对话框，如图4-17所示；
（3）在“选择类别”列表框中单击选中“统计”选项，在“选择函数”列表框中单击选中“RANK”函数，单击“确定”按钮，弹出“函数参数”对话框，如图4-18所示；
（4）在“Number1”编辑框中输入G3，在“Ref”编辑栏中输入“$G$3:
$G$17”，如图4-19所示，单击“确定”按钮。
图4-19在函数参数对话框输入参数
（5）拖动I3单元格右下角的填充柄至I17单击格，则I3：
I17单元格区域自动填充了名次数据。
（6）在C18，C19单元格分别输入“最大值”、“最小值”；
（7）选中D18单元格，然后单击“插入|函数”菜单，弹出“插入函数”对话框，如图4-17所示；
（8）在“选择类别”列表框中单击选中“统计”选项，在“选择函数”列表框中单击选中“MAX”函数，单击“确定”按钮，弹出“函数参数”对话框，如图4-18所示；
（9）单击对话框中的“Number1”编辑框，输入D3：
D17，单击“确定“按钮”完成对D3：
D17单元格最大值的计算。
（10）拖动D18单击格的填充柄至F18单元格，则E18：
F18单元格区域自动填充了对应单元格的最大值数据。
（11）选中D19单元格，然后单击“插入|函数”菜单，弹出“插入函数”对话框，如图4-17所示；
（12）在“选择类别”列表框中单击选中“统计”选项，在“选择函数”列表框中单击选中“MIN”函数，单击“确定”按钮，弹出“函数参数”对话框，如图4-18所示；
（13）单击对话框中的“Number1”编辑框，输入D3：
D17，单击“确定“按钮”完成对D3：
D17单元格最小值的计算。
（14）拖动D19单击格的填充柄至F19单元格，则D19：
F19单元格区域自动填充了对应单元格的最小值数据。
（15）将D13单元格的内容修改为42，则最小值单元格D19及名次列的数据发生了改变。
上述计算完成后的工作表如图4-20所示：
图4-20计算完成后的工作表
3、根据条件进行计算
实验要求：
（1）根据上述工作表中每个人的“平均分”判断每个人的等级（若平均分<60,则等级为“不及格”，若70>平均分≥60，则等级为“及格”，若80>平均分≥70，则等级为“中”，若90>平均分≥80，则等级为“良”，若100≥平均分≥90，则等级为“优”）。
（2）在D20单元格中计算等级为“优”的人数。
操作步骤：
（1）选中J3单元格，在J3单元格的“编辑栏”中输入公式“=IF（H3<60,"不及格",IF（H3<70,"及格",IF（H3<80,"中",IF（H3<90,"良",IF（H3<=100,"优"）））））”，单击“编辑栏”的按钮“√”，则J3单元格根据公式自动计算填充。
（2）拖动J3单元格右下角的填充柄至J17单元格，则J3：
J17单元格区域自动填充了对应单元格的“等级”数据。
（3）单击D20单元格，然后选择“插入|函数”菜单，弹出“插入函数”对话框，如图4-17所示；
（4）在“选择类别”列表框中单击选中“统计”选项，在“选择函数”列表框中单击选中“COUNTIF”函数，单击“确定”按钮，弹出“函数参数”对话框，如图4-18所示；
（5）在对话框的“Range”编辑栏中输入“J3：
J17”，在“Criteria”编辑栏中输入“优”，如图4-21所示，单击“确定”按钮。
图4-21“函数参数”对话框
计算完成后的工作表如图4-22所示：
图4-22计算完成后的工作表
4、跨工作表的计算
实验要求：
（1）建立如图4-23所示的“2009_2010第二学期”工作表；
（2）根据图4-22所示的“2009_2010第一学期”工作表和“2009_2010第二学期”工作表中的数据，计算“第三学年总评”工作表中的总评分，如图4-24所示，其中总评分=第一学期平均分×60%+第二学期平均分×40%。
图4-232009_2010第二学期工作表
图4-24第三学年总评工作表
操作步骤：
（1）复制“2009_2010第一学期”工作表的内容，然后粘贴到“2009_2010第二学期”工作表中，修改和输入D列、E列、F列单元格中的数据，则建立了如图4-23所示的工作表。
（2）单击“第三学年总评”工作表标签，使其成为活动工作表。
（3）单击D3单元格，使其成为活动单元格；
（4）在“编辑栏”中输入“='2009_2010第一学期'!
H3*60%+'2009_2010第二学期'!
H3*40%”，然后单击“编辑栏”的按钮“√”，则D3单元格根据公式自动计算填充。
（5）拖动D3单元格右下角的填充柄至D17单击格，则D3：
D17单元格区域自动填充了总评分。
三、实验小结
1、EXCEL为用户提供了大量函数，这些函数是EXCEL2003的精髓与核心，通过使用公式和函数，可以自动完成各种复杂的计算并显示出结果。
函数可以单独使用，也可以出现在公式中，所有的函数都由函数名以及用一对圆括号扩起来的一系列参数组成，各参数之间用逗号分隔。
2、在EXCEL中，公式总是以“=”号开始的，后面接表达式。
3、在公式与函数的使用中可以引用单元格地址，以代表对应单元格的内容，单元格的地址引用有4种表示方式：
（1）相对地址引用：
相对地址是指直接使用单元格的列标与行号的地址。
当把一个含有单元格相对地址的公式复制到其它单元格时，公式中的单元格地址也随之改变。
（2）绝对地址引用：
绝对地址是在相对地址的列标与行号之前加$符，如$B$6。
当把一个含有单元格绝对地址的公式复制到其它单元格时，公式中的单元格地址保持不变。
（3）混合地址引用：
混合地址是指既含有相对地址，又含有绝对地址，如$B7。
在复制含有单元格混合地址的公式时，只有行或列保持不变，而列或行发生改变。
如$B7就表明B列会随着新的位置改变，而行不发生改变。
4、跨工作表的单元格地址引用：
在公式使用中可能会使用到另一工作表的数据，这时，另一工作表的单元格地址引用格式为：
工作表名！
单元格地址。
4．1．3实验三EXCEL2003图表操作
一、实验目的
1、掌握用图表向导建立不同图表的方法
2、掌握图表的编辑方法。
3、掌握图表的格式化操作
二、实验内容
1、创建图表
实验要求：
（1）新建一工作簿，将其命名为“EXCEL实验三.xls“”，在其“Sheet1”工作表中输入如下数据：
表1：
信息学院学生人数统计表:
专业
2006级
2007级
2008级
2009级
专业统计
计算机
63
82
90
80
315
信息管理
58
76
84
90
308
软件工程
36
40
66
72
214
年级统计
157
198
240
242
837
（2）建立如图4-25所示的“分专业年级人数统计图表”。
图4-25分专业年级人数统计图表
操作步骤：
（1）新建一工作簿，在“Sheet1”中输入上述“表1”的内容，保存命名为“EXCEL实验三.xls”。
（2）在工作簿“EXCEL实验三.xls”的“sheet1”工作表中，选取A2：
E5单元格区域选择“插入|图表”菜单，弹出“图表向导_4步骤之1_图表类型”对话框，选择“标准类型”选项卡，在“图表类型”列表框中选择“柱型图”，在“子图表类型”列表框中选择“簇状柱形图”，如图4-26所示，单击“下一步”按钮。
图4-26图表向导_4步骤之1_图表类型对话框
（3）在弹出的“源数据”对话框中，单击选中“列”单选按钮，如图4-27所示，单击“下一步”按钮。
图4-27“源数据”对话框
（4）在弹出的“图表向导_4步骤之3_图表选项”对话框中，选择“标题”选项卡，在“图表标题”文本框中输入“分专业年级人数统计图表”，在“分类（X）轴”文本框中输入“专业”，在“数值（Y）轴”文本框中输入“人数”，如图4-28所示，单击“下一步”按钮。
图4-28“图表向导_4步骤之3_图表选项”对话框
（5）在弹出的“图表向导_4步骤之4_图表位置”对话框中，选择“作为其中的对象插入”单选按钮，并在右侧的下拉列表框中选择“Sheet1”，如图4-29所示，单击“完成”按钮。
则建立了如图4-25所示的分专业年级人数统计图表。
图4-29“图
升级会员 