excel销售数据分析-实训练习.doc

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