OFFICE情景案例教学(案例12)

《OFFICE情景案例教学》1《OFFICE情景案例教学》《OFFICE情景案例教学》2第二部分Excel2003情景案例•案例9资源整合：管理文件档案•案例10财女再造：制作工资表•案例11塑造企业形象：设计发货单•案例12运筹帷幄：销售统计与分析•案例13市场调研：制作调查问卷•案例14桃李增华：统计和分析学生成绩《OFFICE情景案例教学》3案例12运筹帷幄：销售统计与分析教学意义:1、熟悉市场分析过程中经常使用的方法2、进一步熟练分析汇总的操作；3、掌握应用数据透视表和数据透视图进行数据分析；4、熟练掌握数据曲线的绘制；5、学会根据某一数据列创建查询的方法；6、学习根据趋势线的公式进行预测的方法。教学重点、难点1、数据清点应用2、SUMIF、VLOOKUP、HLOOKUP(参考)、RANK函数的应用3、数据透视表的应用4、方案管理(参考)《OFFICE情景案例教学》413.1销售情况表的建立一、建立销售情况表1、常规的输入方法：2、使用记录清单添加销售数据：（1）记录清单：一个数据库（也被称为一个表），是以具有相同结构方式存储的数据集合。在Excel2003中，数据库是作为一个数据清单来看待。我们可以理解数据清单就是数据库。《OFFICE情景案例教学》5在一个数据库中，信息按记录存储。每个记录中包含信息内容的各项，称为字段。例如，公司的客户名录中，每一条客户信息就是一个记录，它由字段组成。所有记录的同一字段存放相似的信息（例如，公司名称、街道地址、电话号码等）。MicrosoftExcel2003提供了一整套功能强大的命令集，使得管理数据清单（数据库）变得非常容易。数据清单可以完成下列工作：排序——在数据清单中，针对某些列的数据，我们可以用数据菜单中的排序命令来重新组织行的顺序。可以选择数据和选择排序次序，或建立和使用一个自定义排序次序。《OFFICE情景案例教学》6筛选——可以利用“数据”菜单中的“筛选”命令来对清单中的指定数据进行查找和其它工作。一个经筛选的清单仅显示那些包含了某一特定值或符合一组条件的行，暂时隐藏其它行。数据记录单——一个数据记录单提供了一个简单的方法，让我们从清单或数据库中查看、更改、增加和删除记录，或用你指定的条件来查找特定的记录。自动分类汇总——利用“数据”菜单的“分类汇总”命令，在清单中插入分类汇总行，汇总你所选的任意数据。当插入了分类汇总后，MicrosoftExcel自动在清单底部插入一个“总计”行。《OFFICE情景案例教学》7数据清单的组成：·数据清单中的列是数据库中的字段·数据清单中的列标志是数据库中的字段名称·数据清单中的每一行对应数据库中的一个记录（2）使用数据清单：“数据”—“记录单”……单击“新建”，输入记录内容本案例要求一:在所给的工作簿中的”1月销售情况表”中添加一”销售额”字段,并计算内容《OFFICE情景案例教学》8二、建立月销售汇总表1、SUMIF函数根据指定条件对若干单元格求和。语法SUMIF(range,criteria,sum_range)Range为用于条件判断的单元格区域。Criteria为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为32、32、32或apples。Sum_range是需要求和的实际单元格。《OFFICE情景案例教学》9•说明•1）只有在区域中相应的单元格符合条件的情况下，sum_range中的单元格才求和。•2）如果忽略了sum_range，则对区域中的单元格求和。•MicrosoftExcel还提供了其他一些函数，它们可根据条件来分析数据。例如，如果要计算单元格区域内某个文本字符串或数字出现的次数，则可使用COUNTIF函数。如果要让公式根据某一条件返回两个数值中的某一值（例如，根据指定销售额返回销售红利），则可使用IF函数。《OFFICE情景案例教学》10•2、本例求和：•=SUMIF('1月销售情况表'!E$3:E$23,A2,'1月销售情况表'!H$3:H$23)《OFFICE情景案例教学》1113.2业绩奖金的计算•一、统计累计销售业绩•在“1月业绩奖金表”中统计“本月销售业绩”•计算一月的销售业绩：•=VLOOKUP(A3,月销售汇总表!A$2:B$13,2,0)《OFFICE情景案例教学》12VLOOKUP函数：•格式：=VLOOKUP($C$1,Sheet1!A3:M25,3,0)要在数据区域的第一列中查找的数据要查找的数据的数据区域返回匹配值的列序号为一逻辑值，说明见下面若为真（1）或省略时，返回近似匹配值，即如果找不到精确匹配值时，则返回小于查找值的最大数值；若为假（0）时，返回精确匹配值，即如果找不到精确匹配值时，则返回错误值“#N/A”。功能：搜索表区域首列满足条件的元素，确定待检索单元格在区域中的行序号，再进一步返回选定单元格的值。《OFFICE情景案例教学》13•2、设定奖金百分比•补充：HLOOKUP函数•功能：在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。•HLOOKUP中的H代表“行”。•语法•HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)•Lookup_value为需要在数据表第一行中进行查找的数值。•Lookup_value可以为数值、引用或文本字符串。《OFFICE情景案例教学》14•Table_array为需要在其中查找数据的数据表(即在何处找)。可以使用对区域或区域名称的引用。•Table_array的第一行的数值可以为文本、数字或逻辑值。•如果range_lookup为TRUE，则table_array的第一行的数值必须按升序排列：...-2、-1、0、1、2、…、A-Z、FALSE、TRUE；否则，函数HLOOKUP将不能给出正确的数值。如果range_lookup为FALSE，则table_array不必进行排序。《OFFICE情景案例教学》15•文本不区分大小写。•可以用下面的方法实现数值从左到右的升序排列：选定数值，在“数据”菜单中单击“排序”，再单击“选项”，然后单击“按行排序”选项，最后单击“确定”。在“排序依据”下拉列表框中，选择相应的行选项，然后单击“升序”选项。•Row_index_num为table_array中待返回的匹配值的行序号。Row_index_num为1时，返回table_array第一行的数值，row_index_num为2时，返回table_array第二行的数值，以此类推。如果row_index_num小于1，函数HLOOKUP返回错误值#VALUE!；如果row_index_num大于table-array的行数，函数HLOOKUP返回错误值#REF!。《OFFICE情景案例教学》16•Range_lookup为一逻辑值，指明函数HLOOKUP查找时是精确匹配，还是近似匹配。如果为TRUE或省略，则返回近似匹配值。也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值。如果range_value为FALSE，函数HLOOKUP将查找精确匹配值，如果找不到，则返回错误值#N/A!。•说明：•如果函数HLOOKUP找不到lookup_value，且range_lookup为TRUE，则使用小于lookup_value的最大值。•如果函数HLOOKUP小于table_array第一行中的最小数值，函数HLOOKUP返回错误值#N/A!。《OFFICE情景案例教学》17•本例的公式：•=HLOOKUP(C3,奖金标准!A$3:F$4,2,TRUE)•3、确定奖励奖金：《OFFICE情景案例教学》1813.3销售情况的分析•一、制作销售排行榜•在“月销售汇总表”增加一列，“名次”•利用RANK（）函数•=RANK(B2,B$2:B$13,0)•RANK函数•功能：返回一个数字在数字列表中的排位。数字的排位是其大小与列表中其他值的比值（如果列表已排过序，则数字的排位就是它当前的位置）。《OFFICE情景案例教学》19•语法：RANK(number,ref,order)•其中：Number为需要找到排位的数字。•Ref为数字列表数组或对数字列表的引用。Ref中的非数值型参数将被忽略。•Order为一数字，指明排位的方式。如果order为0（零）或省略，按照降序排列的列表；如果order不为零，按照升序排列的列表。•说明：•函数RANK对重复数的排位相同。但重复数的存在将影响后续数值的排位。例如，在一列按升序排列的整数中，如果整数10出现两次，其排位为5，则11的排位为7（没有排位为6的数值）。《OFFICE情景案例教学》20•二、计算业务员销售细目•1、计算业务员销售细目：•（1）建立数据透视表：•数据透视表适用于多元数据的分析和观测。它可以不断地变换纬度分析数据，方便人们分析数据的不同需要。•对数据源的要求：A、第一行必须包含每列的标题。数据区域内不应有任何空行或空列；•B、每列应仅包含一种类型的数据；•C、如果数据源中含有自己创建的自动分类汇总和总计，应在创建报表之前删除。《OFFICE情景案例教学》21•本例情况•数据源：“1月销售情况表”中的A2：H23行字段：业务员列字段：产品品牌•数据：销售额求和《OFFICE情景案例教学》22•（2）改变透视表的显示方式•1）透视表工具：字段设置表选项《OFFICE情景案例教学》23•2、以图表方式显示销售细目•以前数据透视表，作对应的数据透视图：•注意：若图形不同可更改图表类型；对换行列。三、统计业务员每周销售业绩•1、建立数据透视表：•数据源：“1月销售情况表”中的A2：H23行字段：日期列字段：业务员•数据：销售额求和•并将新生成的工作表命名为：同销售业绩《OFFICE情景案例教学》24•2、设置分组：•右单击“日期”字段中的任一行，选“组及分组显示”—“组合”……•四、查找超级业务员•做“自动筛选”•（下面是本节的案例）《OFFICE情景案例教学》25案例：销售统计与分析一、导入销售数据记录：二、销售分析1分类汇总（必须先排序）2对全年各月进行分类《OFFICE情景案例教学》263建立数据透视表：•数据透视表适用于多元数据的分析和观测。它可以不断地变换纬度分析数据，方便人们分析数据的不同需要。•对数据源的要求：（1）第一行必须包含每列的标题。数据区域内不应有任何空行或空列；•（2）每列应仅包含一种类型的数据；•（3）如果数据源中含有自己创建的自动分类汇总和总计，应在创建报表之前删除。设置透视表格式：《OFFICE情景案例教学》274、绘制数据透视图5、使用数据曲线进行分析：