《计算机应用案例教程》课件第5章

学习情境五电子表格Excel2003实训项目1表格的建立及格式化 实训项目2表格的计算及图表的制作 实训项目3Excel的数据库管理及统计功能实训项目4综合应用Excel处理数据

通过本章学习，应掌握电子表格Excel2003的建立、计算、图表和数据统计的操作方法，主要内容包括：

1．Excel2003的基本操作

在Excel中有很多快速输入数据的技巧，如自动填充、自定义序列等，熟练掌握这些技巧可以提高数据输入速度。同时，在输入数据时，要注意数据单元格的数据分类，如对于编号、邮编及电话号码等数据应该设置为文本型。

2．工作表的格式化

工作表的格式化及打印输出则包括字体格式、数据的对齐方式、表格的边框及底纹、行高列宽、页面设置和打印预览等。

3．工作表的计算

Excel最强大的是其计算功能，这功能主要是通过公式和函数来实现的。在进行公式和函数计算时，要熟悉公式的输入规则、函数参数的设置方法及单元格的引用方式，常用函数的使用要熟练掌握。

4．图表的建立及格式化

图表可以比具体的数字更直观地说明问题，因此在实际应用中，在使用表格的同时，通常还需要辅以图表加以说明。使用图表向导创建图表，然后移动图表位置、调整图表大小、设置图表区和绘图区格式，以及进行其他设置等格式化操作。

5．工作表的数据管理及统计

用户可以使用数据排序、筛选、分类汇总、创建数据透视表等对表格中的数据进行统计分析。

数据的筛选可以通过“自动筛选”和“高级筛选”两种方式进行。“自动筛选”可用于简单条件的筛选，“高级筛选”可用于复杂条件的筛选。筛选结果使工作表中满足设定条件的记录显示出来。分类汇总实际上是一种条件求和，很多统计类的问题都可以用“分类汇总”来完成。在进行分类汇总之前一定要对工作表按照分类字段进行排序，再进行汇总计算。

数据透视表是一种交互式工作表，用于对现有工作表进行汇总和分析。在使用数据透视表时，要注意数据区域的正确选取，根据需要设定正确的布局。任务单

【实训目的】

(1)掌握启动和退出Excel的操作方法。

(2)熟悉Excel的工作界面，理解工作簿、工作表和单元格的概念。

(3)熟练掌握Excel工作簿的建立和保存方法。

(4)熟练掌握Excel工作表的编辑操作。

(5)熟练掌握格式化工作表的操作。实训项目1表格的建立及格式化

【实训内容】

本例制作三个表格，分别为学生成绩表、产品月销售统计表和员工年度绩效考核表。

制作学生成绩表，并对表格进行格式化。

要求：

(1)新建一个工作簿文件，将当前的临时工作簿文件保存在D:\EXCEL文件夹中，命名为“学生成绩表.xls”。

(2)在Sheet1工作表中输入图5-1所示内容。任务一制作学生成绩表

图5-1“学生成绩表”原始数据

(3)将Sheet1中的表格内容复制到Sheet2相同的区域中，将工作表Sheet2重新命名为“学生成绩表”。

①设置“学生成绩表”工作表单元格的对齐方式及合并单元格。将标题行合并居中(水平居中、垂直居中)，将第2～16行数据居中。

②设置字体格式。将标题字体设置为“隶书、加粗、深蓝色、20号且加下划线”，其他的文字字体设置为“宋体、常规、12号”。③设置数字格式。将“数学”、“英语”和“计算机”列数据保留1位小数。

④设置边框、背景图案。为学生成绩表添加边框，边框为“深红色，外框粗线、内框细线”。为标题行加背景：“浅青色”；列标头加背景：“象牙色”。

⑤设置单元格的行高和列宽。将标题栏的行高设置为“30”，数据部分的行高设置为“18”，将“学号”列的列宽设置为“10”，其余列的列宽设置为“8”。⑥设置条件格式。对学生成绩表中数学、英语、计算机成绩大于或等于90分的数据用蓝色、加双下划线来标注，低于60元的数据用红色、加粗倾斜来标注。

⑦插入批注。给学生成绩表中李凯的姓名后加一个批注“班长”，给石慧娟的姓名后加一个批注“学习委员”。

(4)保存工作簿。

任务一学生成绩表制作效果如图5-2所示。

图5-2“学生成绩表”效果图

制作产品月销售统计表，并对表格进行格式化。

要求：

(1)新建一个工作簿文件，将当前的临时工作簿文件保存在D:\EXCEL文件夹中，命名为“产品月销售统计表.xls”。

(2)在Sheet1工作表中输入图5-3所示内容。任务二制作产品月销售统计表

图5-3“产品月销售统计表”原始数据

(3)将Sheet1中的表格内容复制到Sheet2相同的区域中，将工作表Sheet2重新命名为“产品月销售统计表”，并在该工作表中完成下面的操作。

①在“产品月销售统计表”工作表第2行前插入1行，输入当前日期(当前日期输入方法为<Ctrl+;>)，并将当前日期数据设置为“××××年××月××日”的格式。

②设置单元格的对齐方式及合并单元格。将标题行跨列垂直和水平居中，第2行(当前日期)右对齐，其余行数据居中。③设置字体格式。将标题字体设置为“黑体、加粗、深红色、18号”，其他的文字字体设置为“宋体、常规、12号”。

④设置单元格的行高和列宽。将标题栏的行高设置为“35”，数据部分的行高设置为“20”。将“名称”、“单价”列宽设置为“8”，其余列设置为最合适的列宽。

(4)保存工作簿。

图5-4“产品月销售统计表”效果图

制作员工年度绩效考核表，并对表格进行格式化。

要求：

(1)新建一个工作簿文件，将当前的临时工作簿文件保存在D:\EXCEL文件夹中，命名为“员工年度绩效考核表.xls”。

(2)在Sheet1工作表中输入图5-5所示内容，其中：

C3单元格内容为：工作计划考评(50分)；任务三 制作员工年度绩效考核表

D3单元格内容为：规章制度执行考评(30分)；

E3单元格内容为：出勤情况考评(10分)；

F3单元格内容为：团队与协作考评(10分)；

G3单元格内容为：年度绩效总分。

图5-5“员工年度绩效考核表”原始数据

(3)将Sheet1中的表格内容复制到Sheet2相同的区域中，将工作表Sheet2重新命名为“员工年度绩效考核表”，并在该工作表中完成下面的操作。

①设置单元格的对齐方式及合并单元格。参照图5-6所示设置单元格的对齐方式及合并单元格。

②设置字体格式。将标题字体设置为“宋体、加粗、黑色、18号”，其他的文字字体设置为“宋体、常规、12号”。

③设置边框、背景图案。为员工年度绩效考核表添加边框：外框双细线、内框单细线。第1行标题：茶色点状底纹；第2、3行文字(班级、姓名……)，黄色底纹；其他各行文字，浅绿色底纹。

④设置单元格的行高和列宽。将数据列宽设置为“8”。将标题栏的行高设置为“30”，数据部分的行高设置为最合适的行高。

(4)保存工作簿。

任务三员工年度绩效考核表最终效果如图5-6所示。

图5-6“员工年度绩效考核表”效果图

【操作提示】

1.新建工作簿

进入Excel，选择工具栏“文件”→“新建”命令，单击“空白工作簿”按钮。

2.保存工作簿

选择“文件”→“保存(另存为)”，在弹出的对话框中，设置存盘的位置及文件名。

3.插入工作表、复制表格的操作

(1)插入：选择【插入】→【工作表】命令即可。

(2)复制表格：通过复制和粘贴操作完成。

4.工作表的编辑及格式化操作

(1)在标题下插入一行：执行【插入】→【行】命令。

(2)标题格式：选中标题行，执行【格式】→【单元格】命令，选择“字体”或“图案”标签。

(3)合并单元格并居中：选中文本单元格，执行【格式】→【单元格】命令，选择“对齐”标签。

(4)表格内容自动换行：选中文本单元格，执行【格式】→【单元格】命令，选择“对齐”标签，选中“文本控制”中的“自动换行”。。

(5)设置表格边框线：选中表格，执行【格式】→【单元格】命令，选择“边框”标签。

(6)设置表格底纹：选中表格，执行【格式】→【单元格】命令，选择“图案”标签。

(7)重新命名工作表：在该工作表的快捷菜单中选“重命名”。

(8)输入当前日期：方法为<Ctrl+;

>。

(9)设置日期及数据格式：选中文本单元格，执行【格式】→【单元格】命令，选择“数字”标签，进行相应设置。

【实训目的】

(1)学习公式与函数的使用。

(2)掌握图表的制作方法和图表的格式化操作。

【实训内容】

对实训项目1中制作的三个表格：学生成绩表、产品月销售统计表和员工年度绩效考核表进行计算及制作图表。实训项目2表格的计算及图表的制作

打开“学生成绩表.xls”，并对表格按要求进行计算，如图5-7所示。

要求：

(1)添加“总分”和“总评”列，增加“平均分”、“最高分”、“最低分”、“总人数”以及“不及格人数”行，如图5-7所示。

(2)计算“总分”、“平均分”列数据。

①总分

=

数学

+

英语

+

计算机。

②平均分

=

(数学

+

英语

+

计算机)÷3。任务一学生成绩表的计算

图5-7“学生成绩表”的计算结果

(3)分别计算“数学”、“英语”、“计算机”三门课的“平均分”、“最高分”、“最低分”、“总人数”以及“不及格人数”。

(4)计算“总评”列数据。

总评：平均分≥90，优秀；平均分≥80，良好；平均分≥60，及格；否则，不及格。

(5)在“学生成绩表”中，对前5个学生制作如图5-8所示的“成绩图表”，并设置图表标题格式为“黑体、加粗、18号”，设置绘图区图案格式为双色(橙色和浅黄色)底纹格式，移动图表位置和调整图表大小到合适的位置。

图5-8学生成绩图表

(6)保存工作簿。

任务一学生成绩表的计算结果如图5-7所示。

打开“产品月销售统计表.xls”，并对表格按要求进行计算，如图5-9所示。任务二产品月销售统计表的计算

图5-9“产品月销售统计表”的计算结果要求：

(1)计算“产品月销售统计表”中“销售额”、“利润”列数据，将计算出的单元格数据设置为“￥”货币格式。

①销售额

=

单价

×

销售数量。

②利润

=

销售额

×

利润率(存放在E11单元格)。

(2)计算“合计”行数据。

(3)计算“占总销售额比例”列数据，将计算出的单元格数据设置为百分比格式。

占总销售额比例

=

销售额

÷

合计。

(4)设置单元格格式，对该表格使用自动套用格式中的“彩色2”。

(5)对“产品月销售统计表”中数据制作如图5-10所示的“产品销售额占总销售额比例”图表，并设置图表标题格式为“宋体、加粗、14号”，设置图表区图案格式为“填充效果”中“纹理”中的“新闻纸”，移动图表位置和调整图表大小到合适的位置。

图5-10“产品销售额占总销售额比例”图表样式

(6)保存工作簿。

任务二产品月销售统计表的计算结果如图5-9所示。

打开“员工年度绩效考核表.xls”，并对表格按要求进行计算。

要求：

(1)在“员工年度绩效考核表”中，利用SUM函数计算“工作计划”～“团队与协作考评”列数据。

任务三员工年度绩效考核表的计算

(2)利用RANK函数，根据“年度绩效总分”成绩计算“本人排名”。

(3)保存工作簿。

任务三员工年度绩效考核表的计算结果如图5-11所示。

图5-11“员工年度绩效考核表”的计算结果

【操作提示】

1.学生成绩表的计算

(1)插入“总分”和“总评”列：执行【插入】→【行】命令。

(2)计算“总分”、“平均分”列数据：单击“常用”工具栏中的“自动求和”按钮 完成或采用输入公式的方式。请尝试这两种方法。公式复制可采用向下拖曳填充柄的方式进行。

(3)“数学”、“英语”、“计算机”三门课的“平均分”、“最高分”、“最低分”、“总人数”以及“不及格人数”的计算可采用单击“常用”工具栏中的“自动求和”按钮 完成或采用插入函数的方式。函数如下：

数学“平均分”，D19单元格中输入“=AVERAGE(D3:D17)”

数学“最高分”，D20单元格中输入“=MAX(D3:D17)”

数学“最低分”，D21单元格中输入“=MIN(D3:D17)”

数学“总人数”，D22单元格中输入“=COUNT(D3:D17)”数学“不及格人数”，D23单元格中输入“=COUNTIF(D3:D17,"<60")”

然后采用公式复制的方法得到英语、计算机的平均分、最高分、最低分、不及格人数等数据。

(4)计算“总评”列数据。

I3单元格中输入以下公式：

“=IF(H3>=90,"优秀",IF(H3>=80,"良好",IF(H3>=60,"及格","不及格")))”

然后采用公式复制的方法得到其他学生的“总评”列数据。

(5)制作图表，选择前5个学生的“姓名”、“数学”、“英语”、“计算机”列数据，执行【插入】→【图表】命令或单击工具栏中的图表向导按钮 ，按照向导步骤指引制作族状柱形图表，对图表标题以及绘图区可单击鼠标右键设置格式。

2.产品月销售统计表的计算

(1)计算“产品月销售统计表”中“销售额”，使用相对地址引用，D4单元格中输入“=B4*C4”，然后采用公式复制的方法得到下方各行数据。

“利润”列数据计算应使用绝对地址引用，E4单元格中输入“=D4/$D$8”，然后采用公式复制的方法得到下方各行数据。

将计算出的单元格数据设置为“￥”货币格式的方法：选择数据区域，执行【格式】→【单元格】命令，选择“数字”标签，进行相应设置。

(2)计算“合计”行数据：单击“常用”工具栏中的“自动求和”按钮 完成。

(3)计算“占总销售额比例”列数据，使用绝对地址引用，F4单元格中输入“=D4/$D$8”，然后采用公式复制的方法得到下方各行数据。

计算出的单元格数据设置为百分比格式：选择数据区域，执行【格式】→【单元格】命令，选择“数字”标签，进行相应设置。

(4)设置单元格格式，对该表格使用自动套用格式中的“彩色2”：选择数据区域，执行【格式】→【自动套用格式】命令，选择“彩色2”。

(5)制作图表，选择“名称”和“占总销售额比例”列数据，执行【插入】→【图表】命令，执行【插入】→【图表】命令或单击工具栏中的图表向导按钮 ，按照向导步骤指引制作分离型三维饼图，对图表区单击鼠标右键，在快捷菜单中单击“图表区格式”，在“图案”选项卡中选择“填充效果”，在“纹理”中选择“新闻纸”。

3.员工年度绩效考核表的计算

(1)在“员工年度绩效考核表”中，在G4单元格中利用SUM函数计算“年度绩效总分”列数据，然后采用公式复制的方法得到下方各行数据。

(2)利用RANK函数计算“本人排名”列数据的方法：使用绝对地址引用，H4单元格中输入“=RANK(G4,$G$4:$G$12)”，然后采用公式复制的方法得到下方各行数据。

【实训目的】

(1)了解Excel的数据库管理功能。

(2)掌握数据记录的排序操作。

(3)掌握数据记录的筛选(自动筛选和高级筛选)操作。

(4)掌握数据表分类汇总的方法。

(5)掌握数据透视表的建立方法。

实训项目3Excel的数据库管理及统计功能

【实训内容】

在实训项目2完成的“学生成绩表”的基础上，完成排序、筛选、分类汇总和数据透视表等操作。

打开“学生成绩表.xls”，并对表格按要求进行排序和筛选操作。

要求：

(1)将实训项目2完成的“学生成绩表”复制到4个新表中，并将复制所得的新表分别重命名为“排序表”、“自动筛选表”、“自定义筛选表”以及“高级筛选表”。

任务一学生成绩表的排序和筛选

(2)在“排序表”中，以“平均分”为关键字按递减方式排序，若平均分相同，则按姓名的姓氏笔画升序排序，结果如图5-12所示。

图5-12“排序表”结果图

(3)在“自动筛选表”中，筛选出“英语”排在前5名的学生，结果如图5-13所示。

(4)在“自定义筛选表”中，筛选出“计算机”分数在80～90分之间的学生，结果如图5-14所示。

(5)在“高级筛选表”中，筛选出至少有一门课程不及格的学生，如图5-15所示。

图5-13“自动筛选表”结果图

图5-14“自定义筛选表”结果图

图5-15“高级筛选表”结果图

打开“学生成绩表.xls”，并对表格按要求进行分类汇总和制作数据透视表。

要求：

(1)将“学生成绩表”复制到2个新表中，并将复制所得的新表分别重命名为“分类汇总表”以及“数据透视表”。

(2)在“分类汇总表”中，按照性别分类汇总各列数据的平均值，结果如图5-16所示。任务二学生成绩表的分类汇总和制作数据透视表

图5-16“分类汇总表”结果图

(3)在“数据透视表”中，为学生成绩统计表建立数据透视表，如图5-17所示。

(4)保存工作簿。

【操作提示】

1.学生成绩表的排序和筛选

(1)数据排序。执行【数据】→【排序】命令，在弹出的对话框中根据要求进行设置，如图5-18所示。

(2)数据筛选。执行【数据】→【筛选】→【自动筛选(高级筛选)】命令，在弹出的对话框中根据要求进行设置，分别如图5-19～图5-21所示。

图5-17数据透视表

图5-18“排序”对话框

图5-19“自动筛选前10个”对话框

图5-20“自定义自动筛选方式”对话框

图5-21“高级筛选”对话框

2.学生成绩表的分类汇总和制作数据透视表

(1)数据分类汇总。先按照“性别”字段进行排序，然后执行【数据】→【分类汇总】命令，在弹出的对话框中根据要求进行设置，如图5-22所示。

(2)建立数据透视表。执行【数据】→【数据透视表】命令，根据数据透视表向导的指引完成数据透视表的建立。

图5-22“分类汇总”对话框

【实训目的】

(1)熟练掌握Excel的基本制表方式方法。

(2)熟练掌握Excel中公式和函数计算。

(3)熟练掌握Excel的图表制作。

(4)提高综合应用Excel处理数据的能力。实训项目4综合应用Excel处理数据

【实训内容】

建立“部门人员工资表.xls”，完成表格的输入、计算、排序、筛选、分类汇总，建立数据透视表等操作。

建立“部门人员工资表”，完成表格输入、计算以及格式化操作。

要求：

(1)新建一个工作簿文件，将当前的临时工作簿文件保存在D:\EXCEL文件夹中，命名为“部门人员工资表.xls”。

(2)在sheet1中输入数据，如图5-23所示。任务一部门人员工资表的建立以及格式化

图5-23“部门人员工资表”原始数据

(3)在表中完成计算

①“工龄补贴”：按工作一年补贴8元计算。

②“扣公积金”：按“职务工资”的一定比例扣除公积金，该比例数存放在J16单元格中，要求使用绝对地址的概念。

③应发工资

=

职务工资

+

津贴

+

奖金

+

工龄补贴

-

扣水电费

-

扣公积金

④求出每一列数据的平均值、最大值和最小值。⑤参考图5-24，在第二行前插入一行，输入制表日期和制表人，在“制表日期”后填写练习当日的系统日期，在“制表人”后填写制表人的姓名。

⑥参考图5-24，对表格进行格式化：第一行标题为“华文彩云、22号、加粗、水平方向和垂直方向居中、红色”，填充颜色为“冰蓝”。

图5-24完成计算及格式化后的“部门人员工资表”⑦第二行为“宋体、10号、水平居中、黑色，跨列合并”。

⑧第三行列标题文字为“宋体、12号、加粗、水平方向和垂直方向居中、黑色”，填充颜色为“浅青绿”。

⑨其余行文字为“宋体、12号、水平垂直居中、黑色”，数值保留一位小数，其中“应发工资”列保留两位小数，并带货币符号。

⑩参考图5-24设置表格边框。

(4)将Sheet1工作表名更