1、第8章 Excel综合案例分析处理第8章 Excel综合案例分析处理8.1 任务任务书书8.2 任务示任务示范范8.3 知识拓展知识拓展第8章 Excel综合案例分析处理8.1任务书任务书1.创建数据透视表创建数据透视表本任务是通过对工资表设计创建数据透视表，利用切片器进行分类查看，并转化为数据透视图。通过本任务可熟练掌握数据透视图及分列函数的使用。2.使用分列规范日期使用分列规范日期本任务是通过对工资表中的日期信息进行分列来提取出年、月、日，对不规范的日期格式进行规范化处理。第8章 Excel综合案例分析处理3.冻结和拆分窗格冻结和拆分窗格本任务是对工资表中的窗口进行拆分和冻结，以方便查看比
2、较大的数据区域。4.插入页眉页脚插入页眉页脚本任务是对工资表中的页眉和页脚进行设置，以打印出符合规范的报表。5.调整工资调整工资本任务是对工资表中的基本工资增加500元，并通过选择性粘贴来实现。第8章 Excel综合案例分析处理6.生成工资条并打印输出生成工资条并打印输出本任务是对工资表进行美化，然后通过排序生成工资条，并打印输出。7.错误屏蔽错误屏蔽本任务是对计算表内的常见错误进行屏蔽，掌握错误的处理方法。第8章 Excel综合案例分析处理8.2任任 务务 示示 范范1.创建数据透视表创建数据透视表【任务实施】【任务实施】(1)插入数据透视表，统计不同地区、不同部门、不同职务等级的人数。数据
3、透视表最终效果如图8-1所示。第8章 Excel综合案例分析处理图8-1数据透视表最终效果第8章 Excel综合案例分析处理操作步骤操作步骤1：新建一个工作表，取名“数据透视表”，选择“插入”选项卡，单击“数据透视表”按钮，在弹出的“创建数据透视表”窗口进行设置。首先选择数据源，将“工资表”所有的数据都选中，可以单击其中一个标题行单元格，然后按下快捷键“Ctrl+A”即可选中整个列表区域。接着选择放置数据表的位置为现有工作表，在位置区域单击“选择”按钮，选中“数据透视表”的A1单元格，如图8-2所示。设置完成后，单击“确定”按钮。第8章 Excel综合案例分析处理图8-2选择分析数据，选择数据
4、透视表放置位置第8章 Excel综合案例分析处理操作步骤操作步骤2：在该工作表右侧会出现数据透视表字段列表，勾选需要进行数据透视的字段(地区、部门、职务等级和姓名)，并分别放置到报表筛选、行标签、列标签和数值框中，如图8-3所示。因为我们要统计人数，因此通过单击右键对“姓名”字段进行值字段设置，设置汇总方式为“计数”，如图8-4所示。第8章 Excel综合案例分析处理图8-3数据透视表字段列表1图8-4值字段设置第8章 Excel综合案例分析处理(2)根据数据透视表，按不同地区、部门和职务统计其实发金额的合计，并使用切片器工具对各类数据进行选择。切片器最终效果如图8-5所示。图8-5切片器最终
5、效果第8章 Excel综合案例分析处理操作步骤操作步骤1：选择“插入”选项卡，单击“数据透视表”按钮，在弹出的“创建数据透视表”窗口进行设置。首先选择数据源，将“工资表”所有的数据都选中，可以单击其中一个标题行单元格，然后按下快捷键“Ctrl+A”即可选中整个列表区域。接着选择放置数据表的位置为现有工作表，在位置区域单击“选择”按钮，选中“数据透视表”的A12单元格，如图8-6所示。设置完成后，单击“确定”按钮。第8章 Excel综合案例分析处理图8-6创建数据透视表第8章 Excel综合案例分析处理操作步骤操作步骤2：在该工作表右侧会出现数据透视表字段列表，勾选需要进行数据透视的字段(地区、
6、部门、职务等级和实发金额)，并分别放置到报表筛选、行标签、列标签和数值框中，如图8-7所示。默认的汇总方式为求和。第8章 Excel综合案例分析处理图8-7数据透视表字段列表2第8章 Excel综合案例分析处理操作步骤操作步骤3：选中该数据透视表，选择数据透视表工具中的“选项”，单击“插入切片器”按钮，设置切片器。勾选地区、部门和职务等级，创建3个切片器，如图8-8所示。第8章 Excel综合案例分析处理图8-8切片器设置第8章 Excel综合案例分析处理操作步骤操作步骤4：要求显示杭州地区，职务等级为科级的销售人员实发金额和。分别设置地区切片器中的杭州，部门切片器中的销售部，职务等级切片器中
7、的科级，就可以统计出相应的金额了，如图8-9所示。图8-9根据需要显示透视信息第8章 Excel综合案例分析处理(3)根据所得的数据透视表产生数据透视图。操作步骤操作步骤1：光标单击已经存在的数据透视表某一个单元格，在数据透视表工具中的“选项”选项卡中单击“数据透视图”按钮，选择图表类型为簇状柱形图，单击“确定”按钮。操作步骤操作步骤2：选中该图表，适当调整大小，该图表会根据数据透视表的筛选而更新变化，也可以自己进行选择显示，如图8-10所示。第8章 Excel综合案例分析处理图8-10数据透视图第8章 Excel综合案例分析处理2.使用分列规范日期使用分列规范日期【任务实施】【任务实施】(1
8、)规范化日期数据，利用分列来拆分出生日期中的年、月、日，分成3列显示。操作步骤操作步骤1：单击“分列”工作表，在身份证后面有3列，分别提取年、月和日的信息。选中身份证下的数据，选择第一个身份证号码，按快捷键“Ctrl+Shift+”即可，再选择“数据”选项卡，单击“分列”按钮，打开分列向导，第一步设置分列方式，这里选择“固定宽度”，如图8-11所示。第8章 Excel综合案例分析处理图8-11分列固定宽度第8章 Excel综合案例分析处理操作步骤操作步骤2：因为身份证从第7位到第13位数字为出生的年、月、日信息，对于这样规范的信息，可以直接去分割。插入4条分列线，单击“下一步”按钮，如图8-1
9、2所示。第8章 Excel综合案例分析处理图8-12设置字段宽度1第8章 Excel综合案例分析处理操作步骤操作步骤3：设置不导入的列，此处需要将第一列和最后一列设置为“不导入此列(跳过)”。这两列忽略，目标区域设置为C2单元格，如图8-13所示。说明：如果不设置目标区域，分列效果将体现在选中的这列数据上。第8章 Excel综合案例分析处理图8-13选择不导入的列第8章 Excel综合案例分析处理操作步骤操作步骤4：单击“完成”按钮即可完成分列，这里年、月、日3列均为常规格式。分好的效果如图8-14所示。图8-14分列最终结果第8章 Excel综合案例分析处理(2)利用分列，从身份证提取年、月
10、、日8位数字，转化为YMD日期格式。操作步骤操作步骤1：复制身份证这列到H列，将列标题改成出生日期。选中下面的数据区域，单击“分列”按钮，设置分列类型为“固定宽度”，单击“下一步”按钮。操作步骤操作步骤2：设置两条分列线，将8位日期隔出来，单击“下一步”按钮，如图8-15所示。第8章 Excel综合案例分析处理图8-15设置字段宽度2第8章 Excel综合案例分析处理操作步骤操作步骤3：设置前后两部分为“不导入此列(跳过)”，在下面预览中显示“忽略列”，然后设置中间的这部分为日期“YMD”格式，单击“完成”按钮，如图8-16所示。图8-16设置日期格式第8章 Excel综合案例分析处理(3)对
11、“年，月，日”或“月，日，年”等不规范的日期规范化。操作步骤：操作步骤：选中不规范的日期单元格，选择“分列”按钮，直接选择到第三步，在日期后面的下拉框中选择格式，YMD表示年、月、日，MDY表示月、日、年，系统会自动去判断分割符。设置目标区域，可以看到规范化的日期。第8章 Excel综合案例分析处理3.冻结和拆分窗格冻结和拆分窗格【任务实施】【任务实施】(1)冻结前两行和前两列。操作步骤操作步骤：选择工资表，选中C3单元格，单击“视图”选项卡中的“冻结窗格”按钮，选择冻结和拆分窗口即可。这样向下查看或向右查看时，前两行和左两列都是不变的，方便查看。如要取消冻结，可选择冻结窗格的取消冻结窗格。第
12、8章 Excel综合案例分析处理(2)拆分窗口。操作步骤：操作步骤：因为表格比较大，需要拆成左、右两部分查看。如果没有标题合并单元格，那么只能选择第1行某个单元格，单击“视图”选项卡中的“拆分”按钮进行左、右拆分；如果现在有标题行，那么可以选择第2行的某个单元格进行拆分，窗口会根据选中单元格的左上角作为拆分的交叉点进行窗口拆分，整个窗口会拆分成四部分，每一个区域都能通过垂直和水平滚动条进行整个表格的查看浏览。如要取消拆分，再次单击“拆分”按钮即可。第8章 Excel综合案例分析处理4.插入页眉和页脚插入页眉和页脚【任务实施】【任务实施】(1)在页眉处居中位置输入“工资表”，设置字体为“楷体”“
13、加粗”。操作步骤：操作步骤：插入页眉和页脚会进入页面视图，而冻结窗格与页眉视图两者不能共存，软件会自动取消冻结。在页眉区域居中位置输入“工作表”，设置字体为“楷体”“加粗”。第8章 Excel综合案例分析处理(2)在页脚处居中位置输入“制表人：姓名”，右侧插入“当天日期”。操作步骤：操作步骤：转到页脚区域，在居中位置输入“制表人：姓名”，右侧单击“当前日期”按钮即可插入日期。该页眉、页脚内容只有在打印输出或页面视图才能看到。第8章 Excel综合案例分析处理5.调整工资调整工资【任务实施】【任务实施】(1)新建一张工作表，取名“调整后工资表”，并复制原“工资表”的内容。操作步骤：操作步骤：这里
14、有两种操作方法新建工作表，重命名为“调整后工资表”，选中整张表格，按快捷键“Ctrl+C”复制整张表，然后选择调整后工资表的A1单元格，按快捷键“Ctrl+V”粘贴。第8章 Excel综合案例分析处理选择“工资表”标签，单击右键，在弹出的菜单中选择“移动”或“复制”，在弹出的对话框中设置创建副本，选择要创建在哪张工作表前或最后，创建的名称默认为“工资表(2)”，双击工作表标签重命名为“调整后工资表”。第8章 Excel综合案例分析处理(2)通过选择性粘贴，每人的基本工资都增加500元。操作步骤：操作步骤：在空白单元格输入500，复制该单元格，整个单元格边上会出现蚂蚁线；然后选中所有基本工资数据
15、单元格，单击右键在弹出的菜单中选择“选择性粘贴”，打开对话框，设置粘贴为“公式”，运算为“加”，如图8-17所示。第8章 Excel综合案例分析处理图8-17选择性粘贴第8章 Excel综合案例分析处理6.生成工资条并打印输出生成工资条并打印输出【任务实施】【任务实施】(1)新建“工资条”工作表，方法同上。(2)采用排序法，生成工资条。操作步骤操作步骤1：在后面增加一列辅助列，利用等差数列填充数值，166为有信息的区域，在66后输入1.5、2.5，选中这两个单元格，继续以等差序列、步长为1填充，一直到66.5。第8章 Excel综合案例分析处理操作步骤操作步骤2：将标题行复制到下面空白区域，以
16、辅助列为主要关键字，进行重新排序，最后删除辅助列。操作步骤操作步骤3：设置自动套用样式为中等深浅2。操作步骤操作步骤4：页面设置中，纸张方向为“横向”。打印标题，进行设置打印区域和打印比例的调整。第8章 Excel综合案例分析处理7.错误屏蔽错误屏蔽【任务实施】【任务实施】(1)屏蔽除零错误#DIV/0！。已知总价和数量计算单价，采用除法，如果数量为空，则会出现#DIV/0！错误。为避免此类错误，可采用IF语句判断是否有错误，如果有则显示为空，即“”。操作步骤：操作步骤：选择“错误屏蔽”工作表，在单价下的单元格中输入公式“=IF(ISERROR(K12/L12),K12/L12)”。第8章 E
17、xcel综合案例分析处理(2)屏蔽无结果错误#N/A。通过查找函数VLOOKUP可以根据姓名找到所对应的身份证号码，当输入的姓名不在表中时会出现#N/A错误。为了屏蔽此类错误，可以采用ISNA()函数来判断是否有结果，如没有找到，可显示为“查无此人”。操作步骤：操作步骤：在L18单元格输入公式“=IF(ISNA(VLOOKUP(K18，A:B，2，0)，查无此人，VLOOKUP(K18，A:B，2，0)”。第8章 Excel综合案例分析处理8.3知知 识识 拓拓 展展(1)会计中常会把0值表示为“-”。此处要求对工资表中的0值或空白处填充为“-”。方法1设置单元格为会计专用，并设置货币符号为无
18、。操作步骤：操作步骤：选中所有金额单元格，设置单元格格式，按下快捷键“Ctrl+L”打开“单元格格式”对话框，选择会计专用，设置货币符号为无。第8章 Excel综合案例分析处理方法2替换该区域的0值为“-”。操作步骤：操作步骤：利用查找替换，选中所有金额单元格，按下快捷键“Ctrl+H”打开“替换”对话框，将查找内容设置为0，并替换为“-”。第8章 Excel综合案例分析处理(2)合并计算。合并计算是指将多个相似格式的工作表或数据区域，按指定的方式如求和、计数、平均值、乘积等进行自动匹配计算。Excel提供了几种方法来合并计算数据，最灵活的方法是创建公式，该公式引用的是将进行合并的数据区域中的
19、每个单元格。引用了多张工作表中的单元格的公式被称为三维公式。第8章 Excel综合案例分析处理实例：对“Excel案例(素材2).xlsx”中的“成绩表”工作表进行计算，利用合并计算，计算出成绩表中各选手的总成绩。总成绩为预赛、半决赛和决赛成绩的平均成绩。操作步骤：操作步骤：选中B39:D47区域，单击“数据”“合并计算”调出“合并计算”对话框，选择“函数(F)”为“求和”；将鼠标置于“引用位置(R)”文本框中，选中B3:D11区域，单击“添加”按钮；将鼠标置于“引用位置(R)”文本框中，选中B15:D23区域，单击“添加”按钮；将鼠标置于“引用位置(R)”文本框中，选中B27:D35区域，单
20、击“确定”按钮。第8章 Excel综合案例分析处理(3)奇偶数个数的统计。实例：对“Excel案例(素材2).xlsx”中的“奇偶数”工作表进行统计，统计A1:A10中奇数和偶数的个数并放到D2和D3单元格中。操作步骤：操作步骤：选中D2单元格，输入公式奇数个数统计的数组公式“=SUM(IF(MOD(A1:A10,2)0,1,0)”，同时按下“Ctrl+Shift+Enter”组合键锁定数组公式。选中D3单元格，输入偶数个数统计的数组公式“=SUM(IF(MOD(A1:A10,2)=0,1,0)”，同时按下“Ctrl+Shift+Enter”组合键锁定数组公式。第8章 Excel综合案例分析处
21、理(4)闰平年的判断。实例：对“Excel案例(素材2).xlsx”中的“奇偶数”工作表进行判断，判断A列的年份是否为闰年，填入B列。使用函数判断年份是否为闰年，如果是，则结果保存为“闰年”；如果不是，则结果保存为“平年”，并将结果保存在“是否为闰年”列中。说明：闰年是指能被4整除但不能被100整除，或者能被400整除的年份。第8章 Excel综合案例分析处理操作步骤：操作步骤：选中A2单元格，输入公式“=IF(OR(AND(MOD(A2,4)=0,MOD(A2,100)0),MOD(A2,400)=0)，闰年，平年)”，单击“确定”按钮，并双击填充柄完成闰平年的判断。第8章 Excel综合案
22、例分析处理(5)数据有效性。实例1：下拉菜单输入的实现。有时候在各列各行中都输入同样的几个值，比如输入学生的成绩等级时需要输入4个值：优秀、良好、合格、不合格。操作步骤：操作步骤：选中A列，单击“数据”“数据有效性”打开“数据有效性”对话框，选择“设置”选项卡，在“允许”下拉菜单中选择“序列”，在“数据来源”中输入“优秀，良好，合格，不合格”(注意要用英文输入状态下的逗号分隔)；选中“忽略空值”和“提供下拉菜单”两个复选框；单击“输入信息”选项卡，选中“选定单元格显示输入信息”，在“输入信息”中输入“请在这里选择”。第8章 Excel综合案例分析处理实例2：数据唯一性检验。通常情况下，身份证号
23、码应该是唯一的，为了防止重复输入，可用“数据有效性”来提示数据输入人员。操作步骤：操作步骤：选中A列，单击“数据”“数据有效性”打开“数据有效性”对话框，在“设置”标签中单击“允许”右侧的下拉按钮，在随后弹出的快捷菜单中选择“自定义”选项，然后在下面的“公式”方框中输入公式“=COUNTIF(B:B，B2)=1”，单击“确定”按钮返回。以后在上述单元格中输入了重复的身份证号码时，系统会弹出提示对话框，并拒绝接受输入的号码。第8章 Excel综合案例分析处理实例3：自动实现中英文输入法转换。有时在输入数据时，需要在不同行或不同列之间分别输入中文和英文，输入过程需要不断地切换输入法的中英文类型，造成输入速度缓慢。对于这种问题，可以通过Excel“数据有效性”的设置来实现在输入数据时完成输入法的中英文自动转换。操作步骤：操作步骤：假设在A列输入学生的中文名，B列输入学生的英文名。先选定B列，单击“数据”“数据有效性”打开“数据有效性”对话框，选择“输入法”对话框，在“模式”下拉菜单中选择“关闭(英文模式)”，然后单击“确定”按钮即可。
