excel销售数据分析实训练习

excel销售数据解析-实训练习excel销售数据解析-实训练习PAGE/NUMPAGESexcel销售数据解析-实训练习excel销售数据解析-实训练习EXCEL实训练习三商场销售数据的解析办理问题的提出小李在深圳市开了若干家饮料连锁店，为了提高管理水平，他打算用Excel工作表来管理销售数据。以下图是他制作的各饮料店的销售记录流水帐表。2为了统计“毛利润”，他必定去“饮料基本信息”表中查找每种饮料的“进价”和“售价”。这个工作量实在太大，而且还简单出错。现在希望：能否输入饮料名称后，让Excel依照这个名称自动去查找该饮料的“单位”、“进价”、“售价”等信息，并存放到表“销售记录”的相应列中。32．解决平时情况下，若是不借助其他方法的帮助，要想在Excel中解决这个问题，只能到“饮料基本信息”表中一条一条地查找各种饮料的“进价”和“售价”。若是不想这么做，你有什么更好的方法吗？这个实质需求，开发Excel的师，已经为我们想到了。在Excel中有一个函数，就是特地为解决这类问题的，这个函数就是VLOOKUP。小李这个问题，可利用Excel中的查找函数VLOOKUP来解决。它的功能是，在数据地域的第一列中查找指定的数值，并返回数据地域当前行中指定列处的数值。下面来看看应用VLOOKUP函数是如何解决上述问题的。4实现方法本领例要解决以下几个问题：1．在“销售记录”工作表中用VLOOKUP函数计算饮料的“单位”、“进价”和“售价”等信息，并计算出工作表中的“销售额”和“毛利润”等信息。2．用“分类汇总”统计出各连锁店和各个区中各种饮料的“销售额”、“毛利润”。3．用“数据透视表”解析各个区中每种饮料的销售情况和各个区中销售情况最好的饮料。4．别的，为了提高效率、防备出错，小李还想制作一张能够从下拉列表中选择饮料名称，并能自动计算出顾客应交款及应找回款的“新销售记录”工作表。54．制作过程STEP1—VLOOKUP函数的使用设计目标参照以下图，依照“销售记录”表中的“饮料名称”列，利用VLOOKUP函数在“饮料基本信息”表中查找其他列（单位、进价和售价）的值。依照以上两个表中的数据获取下图中的结果6最后计算销售额、毛利润、毛利率7（1）VLOOKUP函数是干什么用的VLOOKUP函数的功能：查找数据地域首列满足条件的元素，并返回数据地域当前行中指定列处的值。VLOOKUP的语法：VLOOKUP(lookup_value,table_arr①查找什么？②到哪个地域查找？③查找地域④精确查找第几列的值？还是模糊查找？注意：要查找的对象（参数①）必然要定义在查找数据地域（参数②）的第一列。下面来看看VLOOKUP函数是怎么用的。8（2）如何查找“单位”？打开文件“饮料销售(素材).xls”，将文件另存为“姓名_饮料销售.xls”。在“销售记录”工作表中，选中F3单元格→选择“插入→函数→VLOOKUP”→单击“确定”。由于要依照饮料的名称查找“单位”，因此VLOOKUP函数的第一个参数应入选择饮料名称“D3”。在“Table_array”地域中选择“饮料价格”工作表中的B2:E44。由于“单位”数据存放在“饮料信息”数据地域的第2列，因此输入数字“2”。由于要求饮料名称精确般配，因此最后一个参数输入“FALSE”。9单击“确定”，能够看到函数正确地返回了“一致奶茶”的单位“瓶”。复制公式,出现了什么问题？？？为什么？如何解决呢？提示：注意在复制公式时，若是沿着列拖动时，列标要用绝对引用，想想看为什么？更正公式后，重新复制公式。10（3）创办地域名称选择“饮料名称”工作表。选中饮料名称、单位、进价、售价所在的地域，即单元格地域B2:E44。在名称框中输入“自己的姓名＋A”，必然要按回温馨提示：车确也能够采用以下方法定义地域名称：认。选择菜单“插入→名称→定义”命令，打开“定义名称”对话框，思考问题：1）地域的定义能够包含“序号”列吗？为什么？2）若是名称定义错误了，如何将其删除。11（4）利用地域名查找“进价”和“售价”选择“销售记录”工作表→选中G3单元格→输入等号“=”→选择函数“VLOOKUP”→单击“确定”。由于要依照饮料的名称查找“售价”，因此VLOOKUP函数的第一个参数应入选择饮料名称“D3”。在“Table_array”地域中输入“姓名A”。由于“进价”数据存放在第3列，因此输入数字“3”。由于要求饮料名称精确般配，因此最后一个参数输入“FALSE”单击“确定”，能够看到函数正确地返回了“一致奶茶”的“进价”数据“1.9”。怎么样，对于VLOOKUP函数你是复制公式，看一看有什么不同样？？？同样道理在H3单元格中建立查找饮料名称“售价”12的公式。13（5）计算销售额、毛利润温馨提示：表格中数据之间的关系为：销售额＝售价*数量毛利润＝(售价－进价)×数量14STEP2—分类汇总第一，建立表“销售记录”的二个副本“销售记录(2)”、“销售记录(3)”和“销售记录(4)”。温馨提示：按住Ctrl键拖动工作表标签15（1）依照“所在区”进行分类汇总在表“销售记录(2)”中按“所在区”对销售额和毛温馨提示：选择“所在区”列中的任意单元格单击“升序”按钮进行排序（排序的作用是什么？）汇总，汇总结果显示在数据下方（结果见“饮料销售.xls”）。将销售记录(2)更名为“所在区汇总”结果拜会样例中的工作表——“所在区汇总利润进行分类(样例)”16（2）依照“饮料名称”进行分类汇总在表“销售记录(3)”中按“饮料名称”对销售额和毛利润进行分类汇总（汇总方式为求和）对汇总结果中的毛利润列按降序排序，找出毛利润最大的饮料。将销售记录(3)更名为“饮料名称汇总”结果拜会样例中的工作表——“饮料名称汇总”温馨提示：第一依照“饮料名称”对“销售额”和“毛利润”进行分类汇总；17（3）用“嵌套分类汇总”统计各个区和各饮料店的饮料“销售额”和“毛利润”在“销售记录(4)”工作表中对“主要重点字”选择“所在区”，“次要重点字”选择“饮料店”排序。进行第一次“分类汇总”（分类字段为“所在区”）。进行第二次“分类汇总”（分类字段为“饮料店”）。将销售记录(4)更名为“嵌套汇总”结果拜会样例中的工作表——“嵌套汇总”温馨提示：第一，选择“数据”→“排序”命令，在主要重点字中选择“所在区”，在次要重点字中选择“饮料店”；18STEP3—利用数据透视表实现统计解析设计目标以以下图所示，利用数据透视表生成三维数据统计报表。利用排序，找出销售额最大的饮料19（1）为“销售记录”表创办数据透视表要求：为表“销售记录”建立数据透视表，将数据透视表显示在新工作表中，并将数据透视表命名为“销售统计”行显示“饮料名称”，列显示“所在区”，“销售额”显示在数据区。结果拜会样例中的“销售统计”工作表。温馨提示：选中“销售记录”工作表的任意单元格，选择“数据”→“数据透视表和数据透视图”；单击“下一步”，选择销售数据所在区域；单击“下一步”，打开“数据透视表和数据透视表视图导游之3”；单击“布局”按钮，打开“数据透视表和数据透视表视图导游—布局”对话框，20（2）依照数据透视表找出销售额最大的饮料对毛利润按降序排序，找出销售额最大的三种饮料。结果拜会样例中的“销售统计”工作表温馨提示：选中“总计”列的数值部分（不包含最后一行数值）21（3）在“销售统计”工作表中，找出各个区“销售额”最大的饮料在“销售统计”工作表中，用MAX函数找出每个区“销售额”最大的饮料的“销售额”。在“销售统计”工作表中，用VLOOKUP函数找出各区“最大销售额”所对应的“饮料名称”。22STEP4—用“两轴线-柱图”比较“销售额”和“毛利润”设计目标现在，小李想用“两轴线-柱图”比较“南山区”、“福田区”和“罗湖区”的销售额和毛利润之间的关系。以以下图所示，用两轴线柱图表来比较销售额和毛利润：23（1）制作“两轴线图”在表“所在区汇总”分别选择“所在区”、“销售额”和“毛利润”三列（以以下图）所示；单击“图表导游”按钮，打开“图表导游-4-图表种类”对话框，选择“自定义种类”选项卡，选择“两轴线-柱图”。单击“下一步”，打开“图表导游-4-图表源数据”对话框；24单击“下一步”，打开“图表导游-4-图表选项”对话框，按以下图进行设置；单击“下一步”，在“图表地址”对话框中，选择“作为其中的对象插入”，单击“完成”按钮。25（2）美化图表参照“饮料销售(样例).xls”，完成对图表的设置（格式能够自定义）。26STEP5—利用数据有效性制作更方便、更合用的“新销售记录”工作表设计目标在“饮料销售.xls”中制作一张“新销售记录表”，并应用数据有效性设置，使得在填写了销售“数量”和选取了“饮料名称”后，能够自动计算出“销售额”、“毛利润”和“毛利率”。27（1）制作“销售记录”表副本在“饮料销售.xls”中建立一个“销售记录”表的副本，并将其重命名为“新销售记录”，尔后将其前五列的内容删除（只保留标题行），以以下图所示。并增加3个新列“实收”、“应收”和“找回”28（2）对“饮料名称”应用数据有效性设置选中“饮料价格”表中的“饮料名称”地域，并将其定义为“饮料名称”，以以下图所示。选中“新销售记录”的第4列（“饮料名称”列），尔后再选择“数据”→“有效性”打开数据有效性对话框。在有效性条件中选择“序列”。在“本源”中填写“=饮料名称”（注意：“饮料名称”是定义的“饮料名称”地域），如图下所示。2930（3）数据有效性的使用制作完成，以以下图所示，试一试看，是不是在填写了销售“数量”和采用了“饮料名称”后，能够自动计算出“销售额”、“毛利润”和“毛利率”，很方便吧！31（4）对“饮料店”列进行数据有效性设置参照“饮料名称”的设置对“饮料店”进行设置（5）创办“应收”和“找回”列的公式，算法以下：应收=销售额，找回=实收－应收。32STEP6—让查找公式更圆满（1）隐蔽列在“新销售记录”工作表中将不需要显示的列（“单位”、“进价”、“销售额”）隐蔽起来。（2）前面的结果出弊端在上面应用了查找函数VLOOKUP的“销售记录”表中，若是把D3单元格中的饮料名称删去今后，能够看到F3、G3、H3单元格（即：单位、售价和进价）中均返回错误值“#N/A”。大家能够试一试看，当VLOOKUP函数在“饮料价格”表中没有找到D3单元格中的饮料名称时都要返回错误值“#N/A”。能不能够让当VLOOKUP函数在“饮料信息”表中没有找到D3单元格中的饮料名称时不返回错误值“#N/A”，而只什么都不显示（即显示空格）呢？问题：33利用IF函数和ISERROR函数，使“销售额”、“毛收入”和“毛利润”三列的值，在没有输入饮料名称时，不显示“#VALUE!”错误值。34（2）解决方法利用IF和ISERROR函数能够解决上面的问题。试一试看，你能解决这个问题吗？上面的问题变成两种情况：若是当“VLOOKUP(D3,ylmc,2,FALSE)”部分返回错误值的话，则在F3中显示空字符串。若是“VLOOKUP(D3,ylmc,2,FALSE)”部分工作正常，则在F3单元格中显示饮料对应的“售温馨提示：ISERROR(value)函数：当变量value是错误值“#N/A”时，返回逻辑真（TRUE），因此，当它与函数IF结合在一起使用时，能够用于在公式中查出错误值。IF函数与ISERROR函数结合使用的通用表示法:IF(ISERROR(VALUE),””,VALUE)出错吗？ISERROR（？）D3,ylmc,2,FALSE)的值）。否是价”（即VLOOKUP(35空字符串VLOOKUP(C2,姓名A,3,FALSE)STEP7—冻结窗口保留标题行在“销售记录”工作表中，当销售记录增加后，为了保留标题行的内容，可单击A1单元格，执行“窗口”→“冻结窗口”命令，当转动屏幕时，能够把第一行（即表头行）的内容素来保留在窗口中。36重点难点（1）重点VLOOKUP函数的使用。地域名称的定义分类汇总数据透视表（2）难点VLOOKUP函数的参数的选择数据透视表37总结与常有问题本领例经过对学生信息表数据的办理，介绍了查找与引用类函数VLOOKUP的用法及分类汇总和数据透视表的用法。（一）你知道了吗？经过本领例的学习，你能回答以下问题吗？1．VLOOKUP函数是干什么的？2．用VLOOKUP函数进行查找时，所要查找的元素为什么须定要在所定义的数据地域的第一列？3．VLOOKUP函数中第二个参数的含义是什么？在定义第二个参数时要注意什么？4．在对VLOOKPU函数沿列进行复制时，一般情况下，为什么列标要用绝对地址？5．如何定义数据地域，如何删除数据地域？6．用VLOOKPU函数时，能够不定义数据地域吗？7．什么是分类汇总？它有什么作用？8．在分类汇总从前要注意什么？（二）常有问题及办理方法下面把大家在本领例的学习过程中简单遇到的一些问题及办理方法列于下表：常有问题可能原因办理方法38将其删除，重新定义，方法：选择“插入”“数据区→“名称”→域”名称无该“数据地域”“定义”，打法重新定名称已被定义。开“定义名称”义。对话框，选择已定义的名称，单击“删除”命令。（1）没有把要重新定义“数据地域”，把要查找的对象定查找的内容定VLOOKUP义在“数据区义在“数据区函数返回错域”的第一列。域”的第一列。误值（2）查找的内“#N/A”。用ISERROR容在定义的“数函数使错误值据地域”中不存不显示。在。使用“填充要查找的对象在使用“填充柄”沿列拖（VLOOKUP柄”沿列拖动动复制公式函数的第一个前先将第一个时出现错误参数）列标没有参数的列标绝39值“#N/A”。用绝对引用。对引用。1）在“分类先按要分类的汇总”从前没先字段排序，然按要“分类”的后再进行“分字段排序。类汇总”。2）诚然已先分类汇总结按要“分类”的果不正确。字段排序，但在在“分类汇总”“分类汇总”时，“分类”时，分类字段选字段选择已排择不正确（没有序的字段。选择已排序的字段）。40课后作业：学生成绩的盘问和统计请大家用查找函数VLOOKUP完成下面的案例。（1）问题的提出：新学期刚开始，班主任王老师遇到了一个难题，学校依照学生填报的志愿重分了班。教务处给出了一个只包含新班级学生的学号的Excel表格，见素材。现在他要填写新班级学生的信息。依照过去的经验，他只能一个一个去查找尔后粘贴了，但这既简单出错，工作量又很大。于是他向教Excel的老师请教，经过老师的指点，才知道用VLOOKUP能够很简单地解决这个问题。要求：（A）在表“一班(新)”中，依照学号，查找“姓名”、“大学英语”和总分。B）别的学校要对上学期“大学英语”考试成绩是“优秀”和“优秀”的学生进行奖励。奖励方法见素材中表“奖品及加分”，其中“大学英语”考试成绩是“优秀”的同学总分增加2%、“优秀”的总分增加1%。（2）解决方法请利用“学生信息(素材).xls”，在表“一班(新)”中，依照学号，应用VLOOKUP函数填写出一班学生的“姓名”、“大学英语”和“总分”，对没有成绩41的填写“缺考”。用IF函数填写一班学生的“英语等级”，对缺考的学生填写“英语缺考”。英语成绩>=90，“优秀”80<=英语成绩<90，“优秀”70<=英语成绩<80，“一般”60<=英语成绩<70，“及格”英语成绩<60，“不及格”依照表“奖品及加分”，用VLOOKUP函数计算一班学生的“奖品”和“加分率”，其中“加分率”用百分比格式表示。用IF和ISERROR函数，使英语等级不是“优秀”和“优秀”的学生的“奖品”为空、“加分率”是0，而不显示错误值“#N/A”。计算出每个学生的“新总分”。提示：新总分=总分×（1+加分率）。42