Excel实训2 饮料销售数据分析(3)

Excel实训2 饮料店销售数据分析(3) 五 提高篇 （一）让查找公式更完美 1．前面的结果有缺陷 在上面应用了查找函数VLOOKUP的“销售记录”表中，如果把C2单元格中的饮料名称删去以后，可以看到D2、E2、H2单元格（即：单位、售价和进价）中均返回错误值“#N/A”。 大家可以试一下，当VLOOKUP函数在“饮料信息”表中没有找到C2单元格中的饮料名称时都要返回错误值“#N/A”。 能不能让当VLOOKUP函数在“饮料信息”表中没有找到C2单元格中的饮料名称时不返回错误值“#N/A”，而只什么都不显示（即显示空格）呢？ 2．解决办法 利用IF和ISERROR函数可以解决上面的问题。试试看，你能解决这个问题吗？ 提示：ISERROR(value)函数：当变量value是错误值“#N/A”时，返回逻辑真（TRUE），因此，当它与函数 IF 结合在一起使用时，可以用于在公式中查出错误值。 因此，上面的问题变成：如果当“VLOOKUP(C2,饮料基本信息,3,FALSE)”部分返回错误值的话，则在E2中显示空字符串；如果“VLOOKUP(C2,饮料基本信息,3,FALSE)”部分工作正常，则在E2单元格中显示饮料对应的“售价”（即VLOOKUP(C2,饮料基本信息,3,FALSE)的值）。 （二）使“销售额”、“毛收入”和“毛利润” 更完美 利用IF函数和ISERROR函数，使“销售额”、“毛收入”和“毛利润”三列的值，在没有输入饮料名称时，不显示“#VALUE!”错误值。 （三）利用数据有效性制作一张“饮料销售表” 在“饮料销售.xls”中制作一张“饮料销售表”，并应用数据有效性设置，使得在填写了销售“数量”和选取了“饮料名称”后，可以自动计算出“销售额”、“毛利润”和“毛利率”。 制作方法： 1．在“饮料销售.xls”中建立一个“销售记录”表的副本，并将其重命名为“饮料销售表”，然后将其前三列的内容删除（只保留标题行），如下图所示。 2．数据有效性设置 1． 选中“饮料基本信息”表中的“饮料名称”区域，并将其定义为“饮料名称”，如下图所示。 2． 选中“饮料销售表”的第3列（“饮料名称”列），然后再选择“数据”→“有效性”打开数据有效性对话框。 3． 在有效性条件中选择“序列”。 4． 在“来源”中填写“=饮料名称”（注意：“饮料名称”是定义的“饮料名称”区域），如下图所示。 制作完成，如图下图所示，试试看，是不是在填写了销售“数量”和选取了“饮料名称”后，可以自动计算出“销售额”、“毛利润”和“毛利率”，很方便吧！ 问题： 1．如何删除“数据有效性”设置？ 2．如何重新显示隐藏的列？ 六、 案例总结及常见问题 本案例通过饮料销售数据的处理，介绍了查找与引用类函数VLOOKUP的用法及分类汇总和数据透视表的用法。 （一）你知道了吗？ 通过本案例的学习，你能回答如下问题吗？ 1．VLOOKUP函数是干什么的？ 2．如何定义数据区域，如何删除数据区域？ 3．VLOOKUP函数中第二个参数的含义是什么？在定义第二个参数时要注意什么？ 4．当沿着列复制VLOOKUP函数时应注意什么？ 5．如何让VLOOKUP函数在没有查找到所查找的内容时不返回错误值“#N/A”，而显示“没找到”呢？ 6．什么是分类汇总？它有什么作用？ 7．在分类汇总之前要注意什么？ （二）常见问题及处理方法 下面把大家在本案例的学习过程中容易遇到的一些问题及处理方法列于下表： 常见问题 可能原因 处理方法 “数据区域”名称无法重新定义。 该“数据区域”名称已被定义。 将其删除，重新定义，方法： 选择“插入”→“名称”→“定义”，打开“定义名称”对话框，选择已定义的名称，单击“删除”命令。 VLOOKUP函数返回错误值“#N/A”。 （1）没有把要查找的对象定义在“数据区域”的第一列。 重新定义“数据区域”，把要查找的内容定义在“数据区域”的第一列。 （2）查找的内容在定义的“数据区域”中不存在。 用ISERROR函数使错误值不显示。 使用“填充柄”沿列拖动复制公式时出现错误值“#N/A”。 要查找的对象（VLOOKUP函数的第一个参数）列标没有用绝对引用。 在使用“填充柄”沿列拖动前先将第一个参数的列标绝对引用。 分类汇总结果不正确。 （1）在“分类汇总”之前没先按要“分类”的字段排序。 先按要分类的字段排序，然后再进行“分类汇总”。 （2）虽然已先按要“分类”的字段排序，但在“分类汇总”时，分类字段选择不正确（没有选择已排序的字段）。 在“分类汇总”时，“分类”字段选择已排序的字段。       课后作业 用查找函数VLOOKUP完成下面的员工考核统计、分析。 某公司要根据员工的考核情况对员工进行奖励。“员工考核表(素材).xls”给出了某公司员工考核情况，请将“员工考核表(素材).xls”另存为“员工考核表(学号姓名).xls”。并完成以下内容： 1． 利用IF函数，根据“员工考核表”中的考核分数，计算每名员工的“等级”。 其中：考核分数100~90为优秀；89~80为良好；79~70为中等；69~60为及格，59以下为不及格。 2．利用VLOOKUP函数，根据“奖品及调资率”表，计算每名员工的“奖品”和“调资率”，并将“调资率”设置为百分比。 3．计算每名员工的“新工资”，并将“新工资”设置为带人民币符号形式。 其中：新工资=工资×（1+调资率）。 4．利用COUNTIF函数，统计出“人数及工资统计”表中的“优秀”、“良好”、“中等”、“及格”和“不及格”人数（见样例）。 5．利用SUMIF函数，统计出“人数及工资统计”表中的各级别“新工资总计”（见样例）。 6．“人数及工资统计”表中利用图表向导，作出各级别人数统计图。图表类型选择“饼图” （见样例），要求： （1）图表标题为“员工考核统计图”。 （2）图表区域的填充效果用“麦浪滚滚”。 （3）“数据标志”选择“显示百分比及数据标志”（见样例）。 （4）图例位置在“底部”。 7．用分类汇总统计出部门的新工资总和，要求： （1）建立工作表“员工考核表”的副本“员工考核表（2）”。 （2）在“员工考核表（2）”中利用分类汇总统计出部门的新工资总和，并将汇总结果显示在数据下方（见样例）。 8．用“员工考核表”建立数据透视表，统计出各部门、各级别人数。要求： （1）将“部门”放到行上，“等级”放到列上。 （2）“新工资”放到数据区中（汇总方式选择“计数”）。 （3）数据透视表标签重命名为“各部门考核情况统计表”。