Excel表格处理技巧.docx
《Excel表格处理技巧.docx》由会员分享，可在线阅读，更多相关《Excel表格处理技巧.docx（22页珍藏版）》请在冰豆网上搜索。
Excel表格处理技巧
第4章表格处理软件Excel2003
电子表格软件Excel是进行数据处理的常用软件。
主要以表格的方式来完成数据的输入、计算、分析、制作图表和统计。
本章是主教材的第4章的配套实验，通过10个实验分别介绍表格的编排、数据的计算、统计分析和图表的生成等Excel的操作方法和步骤。
4。
1Excel2003的基础知识
实验4—1Excel的启动、退出和界面介绍
1.实验目的
①掌握Excel应用程序快捷方式的建立。
②通过快捷方式掌握Excel的启动和退出.
③熟悉Excel的工作界面。
④练习选择工作表和单元格区域的方法。
2.实验内容
建立Excel的快捷方式,并利用该快捷方式启动Excel，观察Excel用户界面，切换工作表标签，确认与取消单元格输入内容,查看选中工作表及单元格区域情况.
3.实验步骤
①执行“开始”｜“搜索"｜“文件或文件夹”命令,打开“搜索结果"窗口，搜索的Excel应用程序文件名为“Excel。
exe"。
②在搜索结果中选定Excel应用程序文件，在桌面为其创建快捷方式。
③双击“Excel快捷方式"图标，打开Excel应用程序，认识Excel工作界面：
标题栏、菜单栏、工具栏、编辑栏、工作表标签、工作表窗口、单元格和任务窗格等，如图4-1所示。
④单击Sheet1工作表标签,在A1单元格中输入字符“大学计算机基础"，然后单击编辑按钮“√”确认输入,同时查看名称框中的单元格名称“A1”；在A2单元格中输入字符“上机实验”,然后单击“×”按钮取消输入.
⑤分别单击“全选”、“行号”、“列标"按钮，查看选中全部工作表、一行、一列的情况。
按下Shift键,单击A1单元格,再单击F8单元格，查看选择连续单元格区域的情况;按下Ctrl键,选中A1:
B7和C3:
E6单元格区域,查看选择不连续单元格区域的情况。
分别单击Sheet2和Sheet3工作表标签，在不同的工作表中输入不同的数据，掌握切换不同工作表的方法.
单击工具栏上的“关闭”按钮或执行“文件"|“关闭"命令，将新建的工作簿文件保存到“D:
\练习”文件夹中（如果该文件夹不存在，请自建），文件名为“基础知识。
xls”,退出Excel应用程序.
4。
2工作簿的建立和管理
实验4-2保存、打开和保护工作簿
1.实验目的
①掌握工作簿打开和保存的方法。
②掌握工作簿设置打开、修改权限密码的方法。
2.实验内容
在磁盘上建立一个名称为“管理工作簿。
xls”的工作薄，并将它设置权限密码。
3.实验步骤
①在“D:
\练习”文件夹中双击打开名称为“基础知识.xls”的工作簿。
②执行“文件”｜“另存为”命令，在“另存为”对话框中,将工作簿文件名改为“管理工作簿.xls".
③单击“另存为"对话框中“工具”按钮，在弹出的菜单中选择“常规选项”命令，设置打开权限密码为“12345"，设置修改权限密码为“54321”。
④执行“文件"｜“关闭”命令,退出Excel应用程序。
⑤在“D：
\练习”文件夹中双击“管理工作簿.xls”文件，分别输入打开权限密码和修改权限密码,查看两种密码的不同权限。
4。
3工作表的建立
实验4-3Excel数据的输入
1.实验目的
①掌握数值、文本、日期等数据的输入。
②掌握自动填充数据的方法。
2.实验内容
在“输入数据”工作薄的sheet1中输入如图4—2所示的数据内容.
3.实验步骤
1启动Excel2003窗口，新建一个工作簿文件.
2强制换行:
单击“Sheet1”工作表标签，在单元格A1中输入“河南财经政法大学”。
“河南财经”输完后，按“Alt+Enter"键，对单元格中的内容进行强制换行，再输入“政法大学”.
3
自动换行：
在单元格A2中输入“2008北京奥运会"，此时文本长度超出单元格宽度，若单元格A2中有内容,则超出单元格宽度的文本内容会被隐藏掉。
可设置长文本在单元格中自动换行.选定单元格A2，执行“格式"｜“单元格”命令,在弹出的如图4-3所示的“单元格格式"对话框中选择“对齐”选项卡,在“文本控制”区域选中“自动换行”复选框。
4输入文本型和数值型数据：
在单元格A3中输入电话号码，若直接输入“0371*******”,单元格中显示3716456199，故应将其作为文本输入.输入时，先输入英文的单撇号“’”,即键入:
“’0371*******”;输入数值型数据时，若输入数据的长度超过单元格宽度，Excel2003自动改用科学计数法表示，在C3单元格中输入“410105************"，数据显示为“4。
1011E+17"。
5输入分数:
若在单元格A4中直接输入“3/5"，则显示为“3月5日"，输入分数时，应先输入0和一个空格，再输分数，即在单元格A4中键入：
“03/5。
"
6输入日期和时间:
在单元格B4中直接输入“4/5",则显示为“4月5日”；在C4单元格按下“Ctrl"+“；"组合键输入当前日期；在D4按下“Ctrl”+“Shift"+“；”组合键输入当前时间。
7自动填充系统定义好的序列：
在A5单元格中输入“Sunday",选中A5单元格,将鼠标指针移向填充柄向右拖动到要填充数据的最后一个单元格G5,依次填充序列中其他内容。
8
填充数列：
首先在起始的两个单元格区域A6和B6分别输入开始的两个数据“1”和“4”，然后选定这两个单元格区域，将鼠标指针移向该单元格区域右下角的填充柄向右拖动到要填充数据的最后一个单元格G6即可。
9填充等比序列：
在A7单元格中输入初始数据3，将鼠标指针指向A7单元格右下角的填充柄,并按住鼠标右键再向右拖动到要填充的单元格位置G7,松开右键时，在出现的快捷菜单中选择“序列"命令，在出现的的“序列"对话框中,选中序列产生在“行”，序列类型选择“等比序列”,步长值设置为“2”,终止值设置“96"，如图4—4所示，然后单击“确定"按钮。
10填充自定义序列:
●首先选择“工具”｜“选项"命令,出现如图4-5所示的“选项”对话框.单击“自定义序列”选项卡，在“自定义序列”列表框中选中“新序列”，在“输入序列”列表框中分别输入“北京”、“上海”、“郑州”、“南京"、“杭州”,每输入一项按一下回车键.输入完后单击“添加”按钮，将输入的数据系列添加到“自定义序列"中,然后单击“确定”按钮,关闭对话框。
●
在单元格A8中输入“北京”，将鼠标指针移向该单元格区域右下角的填充柄向右拖动到要填充数据的最后一个单元格G8,可以依次自动填充序列中各项内容.
保存工作簿：
将工作簿文件以“数据输入”文件名保存到“D：
\练习”文件夹中。
4.实验结果样式
实验结果样式如图4-2所示。
实验4—4Excel工作表的计算
1.实验目的
①掌握单元格引用.
②掌握公式进行数据运算。
③掌握内置的常用函数的使用.
2.实验内容
建立一个名为“数据计算”的工作簿,计算和编辑表中的数据.
3.实验步骤
1在“D：
\练习"文件夹中创建一个文件名为“数据计算"的工作簿。
2在“Sheet1”工作表中输入如图4—6所示的数据内容.
提示:
“职工号”列中的数据用自动填充序列方式填充。
3
图4。
6“教师工资表”原始数据
计算课时费：
课时费=课时数×小时课酬.
提示：
小时课酬利用单元格地址绝对引用方法计算.
4计算应发工资：
应发工资=基本工资+课时费.
5利用COUNT函数计算总人数。
6利用COUNTIF函数分别计算教授、副教授、讲师的人数。
7利用AVERAGE函数分别计算基本工资、课时数、课时费、应发工资的平均值.
提示：
计算以上这些字段的最高值和最低值的方法和计算平均值的参数一样，最高值的函数名为Max，最低值的函数名为Min。
4.实验结果样式
实验结果样式如图4-7所示。
4。
4工作表的编辑和格式化操作
实验4—5工作表的编辑
1.实验目的
①掌握工作表的数据修改、复制、移动和删除。
②掌握工作表中单元格、行、列的插入和删除.
③学会为单元格添加批注.
2.实验内容
建立一个名为“编辑工作表”的工作簿,对其中的名称为“工资表”的工作表进行数据编辑,按要求进行数据输入、编辑，修改、复制、删除表格内容，对表格进行格式设置。
3.实验步骤
1在“D：
\练习”文件夹中建立一个名称为“编辑工作表”的工作薄。
2在“工资表”中输入数据内容,如图4—8所示。
3将其中的工作表“Sheet1"改名为“工资表”。
4将姓名为“刘朝阳"所在的分公司改为“北京”。
5删除姓名为“杨柳青”的行，将“部门”这一列移动到“分公司”后面。
6在首行前插入两行，在A1单元格中输入文字“诚信财务软件公司"，在A2单元格中输入文字“员工薪水表"。
7分别将姓名为“杜永宁"、“于洋”、“赵玲玲”的单元格插入批注“分公司经理”.
8计算员工薪水：
薪水=工作时数×小时报酬.
9选中A1：
H1单元格区域，单击“格式”工具栏上的“合并并居中"工具按钮，设置字体为“黑体"、加粗、18号字;选中A2:
H2单元格区域,再次单击“格式”工具栏上的“合并并居中"工具按钮，使两行标题都跨行居中显示.
10保存所做编辑操作。
4.
实验结果样式
实验结果样式如图4-9所示.
实验4—6工作表的格式化
1.实验目的
学会设置数据的显示格式。
②学会设置表格的边框和底纹。
掌握条件格式的用法。
2.
实验内容
对实验4-4建立的“数据计算”工作薄中的工作表的数据进行格式化设置，使用条件格式功能将“应发工资”介于5000元和6000元之间的数据加上灰色底纹，给表格加上合适的边框和底纹。
3.实验步骤
1打开“数据计算”工作簿，用鼠标右键单击“Sheet1"工作表标签，出现的快捷菜单中选择“移动或复制工作表”命令，在“移动或复制工作表”对话框中的“工作簿”下拉列表框中选择“新工作簿”，在“建立副本”复选框中打上勾，单击确定按钮，如图4—11所示，这时就将“Sheet1”工作表复制到新的工作簿了。
2关闭“数据计算”工作簿，将新工作簿的“Sheet1"工作表标签改名为“工资表”。
3在第一行插入标题“教师工资表”,并将A1：
H1单元格区域跨行居中显示，设置标题字体为华文彩云、红色、加粗、20号字，黄色加上12。
5灰色图案底纹。
4将工作表中所有数据居中对齐显示.
5将“应发工资”前面加上“￥"符号，并利用条件格式命令将“应发工资”介于5000元和6000元之间的数据加上灰色底纹：
选定H3:
H15单元格区域，执行“格式”|“条件格式"菜单命令,在弹出的如图4—10所示的“条件格式”对话框中的“条件1"下拉列表框中选择“单元格数值”，在条件运算符下拉列表框中选择“介于"，在条件值框中输入“5000”到“6000"，单击“格式"按钮，在“单元格格式”对话框选择“图案"选项卡，然后选择灰色底纹，单击“确定”按钮，返回“条件格式”对话框，再按一次“确定”按钮即可。
6
选定A17:
A19单元格区域，利用“格式”|“单元格”|“边框”命令,给这些单元格加上两条交叉的斜线
7选中A2：
H19单元格区域,利用“格式”｜“单元格”｜“边框”命令,将数据表加上紫罗兰颜色的双线外边框，蓝色单线内边框。
8保存工作簿,文件名为“教师工资表".
4.实验结果样式
实验结果样式如图4—12所示。
4。
4数据管理
实验4—7数据管理
1.实验目的
①了解数据管理功能.
②掌握数据排序的方法。
③掌握数据筛选的方法。
掌握分类汇总的方法。
2.实验内容
在工作表中进行数据排序、筛选和分类汇总,并进行简单的图表制作。
3.实验步骤
1打开实验4—5所建的“编辑工作表"工作簿，将它以“数据管理”文件名另存.
2将工作表标签“Sheet2”重命名为“数据排序”，将“Sheet3”重命名为“自动筛选”，添加二个工作表，一个命名为“高级筛选”，一个命名为“分类汇总”。
3将“工资表”表中的A3：
H17单元格区域的数据复制到其他四个工作表的A1：
G15区域。
4
在“数据排序”工作表进行数据排序:
单击“数据排序”工作表中的任意单元格，执行“数据”｜“排序”命令，在出现的“排序”对话框中按“薪水"为第一关键字升序，“小时报酬”为第二关键字降序,“工作时数”为第三关键字”升序进行排序。
排序后的结果如图4-13所示。
5在“自动筛选”工作表进行自动筛选：
单击“自动筛选”工作表中的的任意单元格，执行“数据”|“筛选"｜“自动筛选”命令，筛选出分公司=“郑州”并且“薪水”大于等于6000元并小于等于20000元的员工.筛选结果如图4—14所示。
6
在“高级筛选”工作表中筛选出工作时数等于160，或者薪水小于5000元的人员名单：
●
单击“高级筛选”工作表标签,在j1：
k3单元格将筛选条件输入到如图4—15所示的条件区域。
●
单击数据区域中的任意单元格，选择“数据”｜“筛选”｜“高级筛选”命令，出现如图4—16所示的“高级筛选”对话框，选中“将筛选结果复制到其他位置”单选按钮,设置“列表区域”地址范围为$A$1:
$H$15，“条件区域”地址范围为＄J$1：
＄K$3，选择将筛选后的结果“复制到"A17单元格，在“选择不重复的记录”复选框中打上勾,单击“确定”按钮。
高级筛选后的结果如图4-17所示。
7
在“分类汇总”工作表中按照“分公司”以“最大值”和“求和”的方式对“薪水”进行分类汇总：
●按“分公司”排序：
单击“分类汇总"工作表中的“分公司”字段下的任意一个单元格（如：
D4单元格），单击常用工具栏中的升序排序按钮。
●计算每个分公司中薪水的最大值：
执行“数据”｜“分类汇总”命令，在弹出的如图4—18所示的“分类汇总”对话框中选择“分类字段”为“分公司”，“汇总方式”为“最大值”,选定“汇总项”为“薪水",单击“确定”按钮。
●计算每个分公司薪水的总值：
执行“数据”｜“分类汇总”命令，在弹出的如图4—19所示的“分类汇总”对话框中选择“分类字段”为“分公司"，“汇总方式”为“求和”，选定“汇总项"为“薪水"。
将“替换当前分类汇总”复选框对勾去掉,单击“确定”按钮。
汇总结果如图4-20.
8
以“薪水”为数据，在“原始数据清单"工作表中生成如图4-22所示的“分裂型饼图”：
●单击“工资表"的工作表标签,选择B3:
B17和H3:
H17单元格区域。
●单击常用工具栏的“图表向导”按钮，在弹出的如图4-21所示的“图表向导步骤1—图表类型”对话框中选择“自定义图表类型”选项卡下的“分裂的饼图”，然后一直单击图表向导的“下一步”，在图表向导最后的步骤中单击“完成",即可建立成图表.
4。
6制作图表
实验4—8Excel数据图表化
1.实验目的
掌握嵌入图表和独立图表的创建。
掌握图表的编辑。
掌握图表的格式化.
2.实验内容
为实验4—6所建的“教师工资表”中的“基本工资"和“应发工资”数据生成“簇状柱形图”图表,并编辑图表、格式化图表.
3.实验步骤
1打开实验4—6所建的“教师工资表”工作薄.
2建立各位教师的“基本工资”和“应发工资”的簇状柱形图表：
●建立图表的数据在“工资表"的B2:
B15、E2：
E15和H2：
H15单元格区域选择。
●图表为“标准类型"选项卡下的的“柱形图”，子图表类型为“簇状柱形图”.
●图表标题为“教师工资表",分类（X）轴标题为“姓名"，数值（Y）轴标题为“工资”，其它选项选择默认值.建立的图表如图4—23所示。
3对“教师工资表”图表进行如下格式化操作：
●
修饰图表的边框和底纹：
选定图表区,单击鼠标右键，在弹出的快捷菜单中选定“图表区格式"命令，在出现如图4-24所示的“图表区格式”对话框中单击“图案”选项卡，边框选定“自定义”、“圆角”，并选用最粗的红色线形;单击“填充效果”按钮，选用“雨后初晴"颜色填充。
●设置图表标题的格式：
在图表标题区单击鼠标右键，选中“图表标题格式”命令，将标题设置为黑体、加粗倾斜、黄色14号字。
●改变图例的位置：
在图例区单击鼠标右键，选中“图例格式"命令，单击“图案”选项卡，将图例边框改为带阴影的边框，单击“位置"选项卡，将图例位置移动到图表底部。
●改变图表的位置:
在图表区单击鼠标右键,选中“位置”命令,将图表“作为新工作表插入"，使嵌入式图表变独立的图表。
4.实验结果样式
实验结果样式如图4—25所示。
4.7综合应用实例
实验4—9多张工作表的编辑
1.实验目的
①掌握多张工作表的数据输入与编辑。
②掌握多张工作表的地址引用。
③掌握条件格式的应用。
2.实验内容
在多张工作表中进行数据输入、计算和表格修饰。
3.实验步骤
1创建一个文件名为“多张工作表编辑”的工作簿.
2将工作表标签“Sheet1”重命名为“平时成绩",将“Sheet2”重命名为“期中成绩"，将“Sheet3”重命名为“期末成绩”,添加一个工作表为“总评成绩"。
3同时向工作表中输入相同的数据：
单击第一张工作表“平时成绩”的工作表标签，然后按下Shift键，再单击最后一张工作表“总评成绩"的工作表标签，使四张工作表全部选中，成为一个工作组，这时在任意一个工作表中输入数据,可以同时向四张工作表输入如图4—13中所示的相同的数据。
注意：
●“平时成绩”、“期中成绩”、“期末成绩”三张工作表的每位同学每门功课的成绩要按照图4—26所示分别输入。
●
在“总评成绩”工作表要单独增加“总分"和“平均"二列字段,如图4—27所示。
4计算“总评成绩”的各门功课的成绩：
总评成绩=平时+期中成绩×10％+期末成绩×70%，计算“总评成绩”的总分和平均分，并按平均分降序排序。
5设置“总评成绩”工作表的标题格式为“合并及居中”设置标题字体为黑体、18号、加粗、蓝色,底纹颜色为浅青绿加12。
5灰色图案；将“总评成绩”工作表中的所有数据都设置成整数，字体为隶书、14号;居中对齐.
6将“总评成绩”各门功课成绩小于60分的成绩加上灰色底纹。
7将“总评成绩”各门功课大于等于85分的成绩将字体加粗、倾斜、红颜色.
8将“总评成绩”工作表中的单元格的行高和列宽调整为最合适的位置。
9设置“总评成绩”工作表内外边框的线形都为蓝色单线。
10将“总评成绩”工作表的每个人的平均分制作成“分离性三维饼图”图表,并将各数据标志填上平均分数。
4.实验结果样式
实验结果样式如图4—28所示。
实验4—10国华电气厂产量报表
1.实验目的
①熟练掌握工作表的计算.
②掌握工作表的修饰。
③熟练掌握图表的制作.
2。
实验内容
在工作表中进行数据输入、计算、制作图表和表格修饰.
3。
实验步骤
1创建一个文件名为“产量报表”的工作簿.
2在“Sheet1”表中输入如图4—29所示的数据.
3设置标题格式：
酸橙色底纹,红色字体,居中,黑体,字号18，加粗.
4设置整个表格加边框和底纹:
边框为粉红，底纹为黄色。
5设置单元格格式：
●所有单元格垂直对齐为居中。
●数值型单元格格式设为千分分隔样式。
●各个等级的产品数量单元格无小数。
●“产品合格率"单元格保留2位小数并右对齐；其他单元格的内容居中。
6使用函数：
●计算所有分厂的产品数量：
单击B8单元格,输入公式:
“=SUM（B3：
E7）"。
●计算产品合格率:
单击F3单元格，输入公式“=SUM（B3：
D3）/SUM（B3:
E3）"。
●计算各等级所占百分比：
如一等产品所占百分比公式为：
“=SUM（B3：
B7）/＄F$8"。
7建立“单位”、“一分厂”、“三分厂"、“五分厂"（共4行）第四季度产量（不含“产品合格率（％）”）的折线图.
8保存文件.
4.实验结果
实验结果样式如图4—30所示。
升级会员 