（中职）计算机应用综合实训及习题项目5电子课件

1、正版可修改PPT课件（中职）计算机应用综合实训及习题项目5电子课件项目5 Excel 2010电子表格应用一、知识要点首 页1.工作簿 Excel 2010建立的文件是工作簿文件，默认的工作簿文件的扩展名是“.xlsx”，一个工作簿文件可以包含255个工作表。新建的Excel 2010文件，默认的工作簿文件为“工作簿X”（X代表1、2、3、），每个工作簿文件中默认包括3个工作表，分别是Sheet1、Sheet2、Sheet3，保存文件时，保存的是工作簿文件。2.工作表 工作表是工作簿的一部分，使用工作表可以对数据进行组织和分析，它是由若干行和列构成的表格，主要由单元格、行号、列标和工作表标签组

2、成。一个工作表共有1048576行16384列。3.单元格 单元格是Excel工作簿的最小组成单位，工作表中的每一行和每一列交叉处的长方形区域就是一个单元格。每一个单元格可以通过其所在的行号和列标标识，如A4单元格表示位于第4行，第1列的交叉位置的单元格。单击一个单元格，则该单元格就成为当前单元格，即活动单元格，此时当前单元格的框线变为粗黑线。4.公式 公式是Excel工作表中进行数值计算和分析的等式。公式是以“=”开头，由运算项和运算符组成。简单的公式有加、减、乘、除等，复杂的公式可能包含函数、引用、运算符、和常量等。5.函数 函数是Excel中预定义好的一些公式，函数由函数名和参数组成，表

3、示为：函数名（参数1，参数2，）其中参数可以有多个，以逗号分隔，表示其中的参数是可选参数，没有的参数是必需的。6.引用 Excel中对单元格的引用包括相对引用、绝对引用和混合引用三种。（1）绝对引用：单元格的绝对引用总是在指定位置引用单元格。（2）相对引用：单元格的相对引用是基于包含公式和引用的单元格的相对位置而言。（3）混合引用：是指对单元格的引用中既有相对引用，也有绝对引用。7.图表 图表是指对数据和信息可以直观展示并起到关键作用的图形结构。图表主要由图表区、绘图区、图表标题、数值轴、分类轴、数据系列、网格线以及图例等组成的。Excel2010提供了10种图表类型，主要有柱形图、折线图、饼

4、图、条形图、面积图、XY散点图、曲面图、圆环图、气泡图及雷达图等。8.工作簿的基本操作 （1）新建工作簿 单击“文件”菜单的“新建”菜单项，在出现的“可用模板”中，单击选定“空白工作簿”，再单击右下角的“创建”按钮，可创建空白工作簿。 （2）保存工作簿 执行“文件”菜单中的“保存”或“另存为”命令。 （3）打开工作簿。 双击要打开的工作簿文件 （4）隐藏工作簿 在“视图”选项卡的“窗口”命令组中，单击“隐藏”按钮。 （5）保护工作簿 在“审阅”选项卡的“更改”命令组中，单击“保护工作簿”。9.工作表的基本操作（1）插入工作表（2）删除工作表（3）重命名工作表（4）移动和复制工作表（5）显示或隐

5、藏工作表（6）保护工作表（7）拆分工作表（8）冻结或取消冻结窗口10.输入数据 （1）输入字符型数据 字符型数据在单元格中默认左对齐，应注意的是学号、职工号、电话号码、身份证号码等这类特殊数字作为字符型数据进行显示时，输入时要在数字前加上英文输入状态下单撇“”。 （2）输入数字型数据 正数的正号可以省略，所有的单元格都采用默认的通用数字格式，当数字的长度超过单元格的宽度时，Excel自动使用科学计数法来表示输入的数字；输入分数时，输入顺序为“0空格分子/分母”，否则系统视为日期型数据。数字型数据在单元格中默认右对齐。 （3）输入日期和时 当在单元格中需要输入可识别的日期数据时，可输入“年/月/

6、日”的格式；输入时间的格式是“ 时：分：秒”。 （4）快速填充序列数据 填充柄是位于选定单元格或单元格区域右下方的小黑方块，把鼠标指针指向填充柄，当鼠标指针变为“ ”形状时，向下拖动鼠标即可填充数据。11.单元格的选定 （1）选择整行或整列的三种方法 单击行号或列号。 鼠标在行号或列号上移动选择连续的多行或多列。 按Ctrl键单击行号或列号，选择不相邻的多行或多列。 （2）选择一个区域的三种方法 单击起始单元格，按左键移动鼠标选择。 按Shift键的同时按箭头键以扩展选定区域。 单击第一个单元格，按Shift键单击最后一个单元格。12.单元格的编辑 （1）插入单元格、行、列 （2）删除单元格、

7、行、列 （3）单元格的合并 选定要合并的单元格区域，在“开始”选项卡的“对齐方式”命令组中，单击“合并后居中”下拉按钮，选择执行其中的“合并后居中”或“合并单元格”或者“跨越合并”命令。 （4）在单元格中添加、编辑、删除批注 添加批注。在“审阅”选项卡的“批注”命令组中，单击“新建批注”按钮，或者右击单元格，在弹出的快捷菜单中，执行“插入批注”命令。 编辑或删除批注。单击选定所要编辑或要删除的批注，在“审阅”选项卡的“批注”命令组中，单击“编辑批注”按钮，或者单击“删除”按钮。也可以右击该单元格，在快捷菜单中执行“编辑批注”命令或“删除批注”命令。13.格式化工作表 （1）设置单元格格式。 在

8、“开始”选项卡的“单元格”命令组中，单击“格式”下拉按钮，执行“设置单元格格式”命令。 （2）设置列宽和行高。 鼠标拖动行或列号的边线；或者在“开始”选项卡的“单元格”命令组中，单击“格式”下拉 按钮，执行“行高”或“列宽”命令。 （3）设置条件格式。 在“开始”选项卡的“样式”命令组中，单击“条件格式”按钮。14.创建和修饰图表 （1）创建图表 选定数据区域，在“插入”选项卡的“图表”命令组中，单击要插入的图表类型按钮。单击“图表工具”，利用对话框中的“设计”、“布局”、“格式”选项卡内的命令编辑和修改图表；或者右击图表，利用弹出的快捷菜单命令编辑和修改图表。 （2）创建迷你图 选定数据区域

9、，在“插入”选项卡的“迷你图”命令组中，单击要插入的迷你图类型按钮。通过“迷你图工具设计”选项上的命令修饰迷你图。执行“迷你图工具设计”选项上的“清除”命令，可以清除迷你图。15.数据透视表与数据透视图（1）创建数据透视表 在“插入”选项卡的“表格”命令组中，单击“数据透视表”下拉按钮，执行“数据透视表”命令。（2）创建数据透视图 在“插入”选项卡的“表格”命令组中，单击“数据透视表”下拉按钮，执行“数据透视图”命令。二、 技能实训1.自定义填充“春、夏、秋、冬”序列 用户可以通过对话框自定义填充数据，自定义设置的填充数据可以更加准确，更加快速地完成数据录入工作。操作步骤如下：（1）启动Exc

10、el2010，系统会自动新建一个名为“工作簿”的空白工作簿，选定“Sheet1”工作表。（2）单击 “文件”菜单下的“选项”命令。（3）在弹出“Excel选项”对话框，切换到“高级”选项卡，拖动垂直滑块至对话框底部；在“常规”区域中单击“编辑自定义列表”按钮。 如图5-2所示。（4）弹出“自定义序列”对话框，在“自定义序列”列表框中，选择“新序列”列表项；在“输入序列”文本框中输入“春,夏,秋,冬”序列。如图5-3所示。（5）单击“添加”按钮，可以看到刚刚输入的新序列被添加到“自定义序列”中，确认操作后，单击“确定”按钮即可。（6）返回到“Excel选项”对话框，确认操作后单击“确定”按钮。（

11、7）返回到工作表编辑界面，在准备使用填充序列的单元格输入自定义设置好的填充内容 “春”。（8）选中准备填充内容的单元格区域，并且将鼠标指针移动到填充柄上，拖动鼠标至准备填充的单元格位置。释放鼠标，可以看到准备填充的内容已经被填充至所需的列中。通过上述方法即可完成使用对话框自定义填充数据的操作。如图5-4所示。2.Excel2010工作表套用系统自带的表格格式 （1）打开素材文件“项目5素材教师绩效考核xlsx”，切换到“Sheet1”工作表。 （2）选中单元格区域A2：D14，单击“样式”按钮，在展开的“样式”组中单击“套用表格格式”按钮，在弹出的下拉列表中选择合适的表格格式，例如：选择“表样

12、式浅色18”。 （3）在弹出“套用表格式”对话框，在“表数据的来源”文本框中显示了用户选中的单元格区域“A2：D14”，单击“确定”按钮，返回工作表中即可完成套用Excel2010自带的表格格式3.Excel2010工作表中插入背景图片 为“项目5素材教师绩效考核xlsx”中的“Sheet1”工作表插入背景图片。操作步聚如下： 打开素材文件“项目5素材教师绩效考核xlsx”，切换到“Sheet1”工作表。切换到“页面布局”选项卡，然后单击“页面设置”组中“背景”按钮。弹出“工作表背景”对话框，选择要插入背景图片的保存位置“项目5素材图片”,选择“1.jpg”并设置为工作表背景的图片文件。选择完

13、毕单击“插入”按钮即可，设置效果如图5-6所示。4.数据的查找和替换 查找 “项目5素材教师绩效考核xlsx”的“Sheet2”表中的“商贸系”，然后替换为“经贸系”。操作步骤如下： 打开素材文件“教师绩效考核xlsx”，切换到“sheet2”工作表。（1）切换到“开始”选项卡，单击“编辑”组中“查找和选择”按钮，在弹出的下拉列表中选择“替换”选项。（2）弹出“查找和替换”对话框，切换到“替换”选项卡，在“查找内容”文本框中输入“商贸系”，在“替换为”文本框中输入“经贸系”。（3）单击“查找内容”文本框右侧的“格式”按钮，然后从弹出的下拉列表中选择“清除查找格式”选项。（4）单击 按钮，此时光

14、标定位在了要查找的内容上，并在对话框中显示了具体的查找的结果，如图5-7所示。（5）单击 按钮，弹出“Microsoft Excel”对话框，并显示替换结果。如图所示。（6）单击“确定”按钮，返回“查找和替换”对话框，替换完毕，单击“关闭”按钮即可。5.利用条件格式查找“重复值”。 （1）利用条件格式查找“项目5素材教师绩效考核xlsx”中“sheet2”表中A1列的重复值。操作步骤如下： （2）打开素材文件“项目5素材教师绩效考核xlsx”，切换到“sheet2”工作表。选中要查找重复值的数据区域，选中数据区域A3:A14单元格。 （3）在“开始”选项卡的“样式”组中，单击“条件格式”下拉按

15、钮，在展开的下拉菜单中，单击“突出显示单元格规则”，展开下一级菜单，如图5-8所示。 （4）单击“重复值”，打开“重复值”对话框。 （5）在“重复值”对话框中，在左侧的下拉列表中选择“重复”，在右侧的下拉列表中选择相应的条件格式，这里保留默认的“浅红填充色深红色文本”。如图5-9所示，单击“确定”按钮，即可完成重复值的查找。6.设置数据的有效性 在“项目5素材2015年度员工评分表.xlsx”中的“年终评分表”工作表中设置“职称”数据的有效性。设置职称数据只能为“高级工程师,工程师,助理工程师”，操作步骤如下：（1）打开素材文件“项目5素材2015年度员工评分表.xlsx”，切换到“年终评分表

16、”。（2）选取数据为职称所在的单元格的单元格区域，切换到“数据”选项卡，在“数据工具”组中，单击“数据有效性”按钮。如图5-10所示。（3）弹出“数据有效性”对话框，在“有效条件”选项组中，单击“允许”下拉箭头；在弹出的下拉列表中，选择允许用户输入的值“序列”， 同时勾选忽略空值和提供下拉箭头两项。如图5-11所示。（4）在“来源”的对话框中输入：助理工程师,工程师,高级工程师。如图5-12所示。注意:数据输入以英文半角状态下的逗号分隔。（5）单元格中设置了数据的有效性后，就可以从下拉列表中选择相应的职称类别。如图5-13所示。（6）当用户在“职称”单元格区域中，输入超出所设置的有效性的数据，

17、如“工人”，系统会弹出对话框，提示“输入值非法，其他用户已经限定了可以输入该单元格的数值。”如图5-14所示。7.美化Excel工作表对“项目5素材2015年度员工评分表.xlsx”的“年终评分表”进行格式化设置，效果如图5-15所示。操作步骤如下：（1）打开“项目5素材2015年度员工评分表.xlsx”，切换到“年终评分表”。（2）自动填充数据“2015A1001、2015A1002、2015A1018”，在A3单元格中输入“2015A1001”，选中A3单元格，向下拖动填充柄，自动填充编号。（3）选中A1：I1单元格，单击“开始”选项卡“对方方式”组中的“合并并居中”按钮、“居中”按钮和“

18、垂直居中”按钮。或者单击“开始”选项卡“对方方式”右下角的对话框启动器，打开“设置单元格格式”对话框，选择“对齐”选项卡，在“水平位置”和“垂直对齐”下拉列表中均选择“居中”，勾选“合并单元格”复选框，单击“确定”按钮。如图5-16所示。（4）在“开始”选项卡的“字体”组中，将标题设置为华文楷体、24磅、加粗、浅绿色。（5）将单元格区域A2:I20的对齐方式设置“水平居中”，在“开始”选项卡的“字体”组中，设置字体为华文中宋、12磅、浅绿色。（6）调整行高与列宽 选中整个表格，在“开始”选项卡的“单元格”组中，单击“格式”按钮，在下拉列表中选择“自动调整行高”。选中第一列，在“开始”选项卡的“

19、单元格”组中，单击“格式”按钮，在下拉菜单中选择“列宽”，在弹出的“列宽”对话框中，输入列宽值“14”，单击“确定”按钮。将第1列的值设置为14，然后将其余各列值设为9。（7）添加边框 选中数据区域A2:I20单元格，在“开始”选项卡的“字体”组中，单击“边框”按钮旁的下三角按钮，在下拉列表中选择“其他边框”。在弹出的“设置单元格格式”对话框中选择“边框”选项卡，在左侧的线条中选择“粗实线”，在左侧的颜色中选择“红色”，单击预置区域的“外边框”按钮，然后再在左侧的线条中“细实线”，在左侧颜色中选择“黄色”，单击预置区域的“内边框”按钮。（8）填充底纹选中数据区域A1:I1单元格，在“开始”选项

20、卡的“字体”组中，单击“填充颜色”按钮旁的下三角按钮，在下拉列表中选择“黄色”，为标题行添加黄色底纹。选中数据区域A2：I20单元格，用同样的方法为该数据区域设置填充颜色为“紫色”。（9）添加批注为“2015年度员工评分表.xlsx”的“年终评分表”工作表标题行添加批注。选中数据区域A1：I1单元格，单击“审阅”选项卡“批注”组中的“新建批注”按钮；或者单击右键，从弹出的快捷菜单中选择“插入批注”命令。在批注框中输入批注内容“部分员工”，此时在单元格右上角会出现红色的三角形，当光标移动到此处时，会显示批注内容。如图5-18所示。8.公式与函数的使用 打开“项目5素材业务奖金计算表xlsx”工作

21、簿文件 。（1）计算每位员工完成业务的超额数值。操作步骤如下：切换到“业务奖金”工作表。在单元格G4中输入“=F4-E4”，按下回车键完成输入，此时单元格G4中显示计算结果。选中G4单元格，向下拖动填充柄到G13单元格，即可求出所有员工的超额数值。如图5-19所示。（2）计算每位员工的提成率。根据“项目5素材业务奖金计算表.xlsx”的“奖金标准”工作表中的业务奖金发放标准，如图5-20所示。使用了IF嵌套使用方法。第1个判断超额数是否大于0，如果大于0，则继续判断，否则返回0。第2个IF判断超额数是否小于等于5000，如果为真则返回2%，否则进行第3个IF判断，超额数是否大于5000，如果为

22、真则返回5%，否则进行第4个IF判断，如果超额数小于10000，则返回值为10%。操作步骤如下：切换到“业务奖金”工作表。选中单元格H4，输入公式“=IF(G60,IF(G63000,G6=5000),5%,10%)”,如图5-21所示，即根据超额数量多少返回相应的提成率。选中H4单元格，向下拖动填充柄到H13单元格，即可得到每位员工的提成率。（3）计算每位员工的累进差额 员工的累进差额根据“奖金标准”表中的数据来计算，奖金比例为2%，累进差额为0；奖金比例为5%，累进差额为150；奖金比例为10%，累进差额为650。使用LOOKUP函数来查找符合条件的数值。操作步骤如下：切换到工作表“业务奖

23、金”中。选中单元格I4，输入公式=LOOKUP（业务奖金！H4，奖金标准！$D$4：$D$6,奖金标准！$E$4:$E$6),按下回车键，即可看到单元格I4中返回的累进差额值，如图5-22所示。选中I4单元格,向下拖动填充柄到I13，即可将公式填充到单元格区域I5：I13中，即可得到每位员工的累进差额。（4）计算每位员工的奖金每位员工的资金=超额*提成率累进差额，操作步骤如下：切换到工作表“业务资金”中，在J4单元格输入“G4*H4I4”，按下回车键完成输入，如图5-23所示。选中单元格I4，向下动填充柄到J13,即可得到每位员工的奖金。（5）计算各员工所得奖金的排名函数说明：“RANK”函数

24、功能是返回结果集分区内指定字段的值的排名。“RANK”函数的格式为：（number,ref,order），参数number是需要计算其排位的一个数字；ref是包含一组数字的数组或引用；order为一数字，指明排位的方式，如果order为或省略，则按降序排列的数据清单进行排位，如果order不为，ref当作按升序排列的数据清单进行排位。操作步骤如下：切换到“业务奖金”工作表中。选中K4单元格，输入公式“=RANK(J4,$J$4:$J$13)”，按回车键完成输入操作。然后利用鼠标拖动的方法向下填充公式，将公式填充到单元格区域K5：K13中，得出各员工所得奖金的排名。（6）计算合计，操作步骤如下：

25、切换到“业务资金”工作表中。选中E14单元格，输入公式“=E4+E5+E6+E7+E8+E9+E10+E11+E12+E13”，按下回车键，即可将结果显示在单元格E14中。选中E14单元格，按下“Ctrl+C”组合键进行复制；选中单元格F14，按下“Ctrl+V”组合键，即可将公式复制到单元格F14中。（7）计算各分部的实际完成额操作步骤如下：切换到“业务资金”工作表。选中E17单元格，输入公式“=SUMIF(D4:D13,”河南分部”,F4:F13)”，按下回车键，返回河南分部实际完成的合计值。如图5-24所示。选中单元格E19，输入公式“=SUMIF(D4:D13,”西南分部”,F4:F1

26、3)”，按下回车键，返回西南分部实际完成的合计值。选中单元格E19，输入公式“=SUMIF(D4:D13,”东北分部”,F4:F13)”，按下回车键，返回东北分部实际完成的合计值。（8）利用文本函数判断员工的“性别” 函数说明：员工性别可以根据员工身份证信息来判断，当身份证号第17位如果是奇数，则性别为男；如果是偶数，则性别为女。使用MID函数提取身份证中第17位，然后使用MOD函数求这个数除以2的余数，以判断奇偶性。最后利用IF函数将结果填至单元格中。操作步骤如下：切换到“员工信息”工作表中。在E4单元格中输入公式“=IF(MOD(MID(G4,17,1),2),女,男)”，按下回车键即可看

27、到E4单元格返回的性别值。选中单元格E4，向下拖动填充柄到E13，将公式填充到单元格区域E5：E13中，得出每名员工的性别值。（9）利用文本函数计算出员工的“出生日期” 函数说明：身份证号中的第710位为出生的年份，第1112位为出生的月份，第1314位为出生日期。使用MID函数分别提取身份证中的年、月、日，再通过CONCATENATE函数将年、月、日和短横线“-”连接起来。操作步骤如下：切换到“员工信息”工作表中。在F4单元格中输入公式=CONCATENATE(MID(G4,7,4),-,MID(G4,11,2),-,MID(G4,13,2)”。按下回车键，即可看到单元格F4中返回的日期值。

28、选中F4单元格，向下拖动填充柄到F13单元格中，得出每名员工的出生日期。9.创建Excel2010图表 打开素材 “项目5素材2015年度员工评分表.xlsx”工作簿，绘制三维簇状图，效果如图5-25所示。操作步骤如下：（1）选定“年终评分表”。（2）计算员工“年终总分”: 在I3单元格中输入公式“=E3+F3+H3+G3”后按回车键，计算出第一名员工的年终总分。选中I3单元格，向下拖动填充柄，自动填充其余员工的年终总分。（3）选中绘制图表的数据区域B3：B20和I3：I20单元格。在“插入”选项卡“图表”组中，选择“柱形图”中“三维簇状柱形图”。（4）在“图表工具”的“设计”选项卡“数据”组

29、中，单击“选择数据”按钮。（5）打开“选择数据源”对话框，单击“切换行列”按钮，在“水平（分类）轴标签”选择折叠对话框，在工作表上选择所需的单元格I2，然后单击按钮将对话框还原为正常大小，单击“确定”按钮。横坐标轴名字设置为“年终总分”。（6）选中“图表标题”，把名称更改为“员工年终评分表”，设置“字体”格式为颜色为“华文隶书”、字号为“22”、加粗。（7）在“图表工具”的“设计”选项卡的“布局”组中单击“布局3”，图表布局设置为如图所示5-26所示。（8）双击图表区，在弹出的快捷菜单中选择“设置图表区域格式”命令。在“设置图表区域格式”对话框中的“填充”区域选择“图片或纹理填充”里的“画布”

30、纹理。如图5-27所示。（9）右击图例区，在弹出的快捷菜单中选择“设置图例格式”命令。在“设置图例区”格式对话框中的“填充”区域选择“图片或纹理填充”里的“花束”纹理。（10）选择绘图区，在“图表工具”的“布局”选项卡的“标签”组中，选择“数据标签”下的“显示打开所选内容的数据标签”。如图5-28所示。10.高级统计分析 打开 “项目5素材2015年度员工评分表.xlsx” 工作簿文件。（1）多条件排序 按“年终总分”降序，“性别”升序进行排序，操作步骤如下：复制“年终评分表”工作表，命名为“多条件排序”。选择要排序的单元格A2：I20，单击“数据”选项卡“排序和筛选”组的“排序”按钮。在打开

31、的“排序”对话框中，主要关键字为“年终总分”，次序为“降序”。单击“添加条件”按钮，条件列表中新增一行，在次要关键字中选择“性别”，次序为“升序”。工作表中数据将按照年终总分为主关键字，性别为次关键字排序，如图5-29所示。单击“确定”按钮。（2）自动筛选 筛选出各种奖分均大于或等于75分的员工。操作步骤如下：复制“年终评分表”工作表，命名为“自动筛选”。选定“自动筛选”工作表，选中要筛选的数据列表A2：I20，或者单击数据列表中的任一单元格。单击“数据”选项卡“排序和筛选”组中“筛选”按钮。在数据列表的每个列标题旁均出现一个筛选箭头。进入自动筛选。如图5-30所示。单击“出勤奖分”列的箭头，打开“筛选器选择列表”，在“数字筛选”子菜单中选择“大于或等于”命令，打开“自定义自动筛选方式”对话框，数