数据查询汇总与分析实例

数据查询汇总与分析实例参考书：王兴德著，管理决策模型55例，上海交通大学出版社，2000，学校书号：F201092一、数据库数据库NORTHWIND.MDB。8个表：产品（77个记录）、类别（8个记录）、供应商（29个记录）、雇员（9个记录）、客户（91个记录）、运货商（3个记录）、订单（830个记录）与订单明细（2157个记录）。前6个表记录着关于该公司所经销的各种产品及其分类、供应商、雇员、客户以及运货商等实体的静态数据，后两个表记录着该公司从1996年7月4日至1998年5月6日期间发生的所有的客户订货业务数据。二、确定各月销往各地区的各类商品月销售额要求：1、在Northwind公司数据库所保存的订货数据的基础上制作一个关于该公司在1996年7月~1998年5月间各月销往各个地区的各种类别产品的月销售额汇总表。2、绘制一个从该公司1996年7月~1998年4月期间该公司月销售随时间变化的折线图形，并在该图形中添加一条直线趋势线，再求出该趋势线的斜率与截距。3、求出对于该公司1998年6月月销售额的预测值。解：首先，通过MicrosoftQuery的帮助，在Excel工作表中生成一个记载着Northwind公司从1996年7月4日至1998年5月6日期间所有各个订购日期发生的订货业务的完整列表，其中包含“订购日期”、“类别名称”、“地区”、“数量”、“单价”和“折扣”等六列，再增加一个“销售额”列；然后，在上述数据列表的基础上，应用Excel的汇总功能，在另一个工作表中生成所需要的、销往不同地区的不同类型产品的月销售额数据。打开一个新工作簿文件，选择菜单“数据”-“获取外部数据”-“新建查询”，在运行的MicrosoftQuery的“选择数据表”对话框中，选择数据库MORTHWIND，接下来跟着查询向导选择“订单”中的“订购日期”、“订单明细”中的“单价”、“数量”和“折扣”，“类别”中的“类别名称”以及“客户”中的“地区”等。在看到警告之后，确定，添加“产品”表即可。返回Excel，并增加一个销售额字段，输入相应的公式（=b2*c2*(1-d2)）。双击复制。接下来用“数据透视表”进行汇总。将“订单日期”作为行，“类别名称”作为列，“客户地区”作为页，“销售额”作为求和汇总；然后对订单日期进行“分组及分级显示”-“组合”，将“月”和“年”都选中状态。绘制全部商品月销售额的变化折线图形。1、透视图的调整（说明透视图的灵活性）；2、复制到新sheet（不在透视图上绘图，因为透视图是可以调整和变化的，一旦透视图产生了变化，图的意义就不清楚了）；3、订购日期的调整（“编辑-定位-定位条件-空值”；键入等号-向上方向键-Ctrk+enter；A2&b2）。4、绘制折线图（由于日期数据都是文字-字符-，所以这个图形只能使用折线图形式来绘制。）5、添加趋势线（选中曲线，“图表”菜单中的“添加趋势线”，一些选项：显示公式、R）确定Northwind公司1998年6月的总销售额的预测值。利用拟合直线的斜率合截距或利用forecast()函数。（给出前面各个月的拟合值）三、按照年度销售额确定产品的排名榜假定Northwind公司将从每年7月1日至下一年6月30日规定为一个会计年度，要求：1、针对1996与1997两个年度（不考虑数据库中目前缺少1997年度最后两个月份销售数据的事实），对于该公司所经销的每个产品类别生成一个显示其中各种产品销售额排名榜的汇总表。2、在针对1996与1997两个年度的、显示全部77种产品总排名榜的汇总表中截取出只包含前20种产品的销售额汇总表，并在此表的基础上确定两个年度中排名升幅度最大的两种产品与降幅度最大的两种产品。3、在将所有产品按销售额作降序排列的基础上绘制一个表明1996与1997两个年度中产品累计销售额百分比随产品个数百分比变化的曲线图形，在图中添加一条与20%这个产品个数百分比值对应的垂直参考线，并标出曲线与该参考线相交处的高度。解：1、用数据透视表完成而不用保留查询结果的方法。数据-数据透视表-外部数据源；获取数据-“Northwind”-订单.订购日期、类别.类别名称、产品.产品名称、订单明细.数量、单价、折扣-….-在MicrosoftQuery中浏览数据；记录-添加列-字段：订单明细.单价*订单明细.数量*（1-订单明细.折扣）-列标：销售额-返回到Excel；订购日期作为行，销售量作为汇总，列暂缺，类别名称和产品名称作为页；订购日期分组显示（数据-分组显示-起始时间：1996/7/1，步长或依据为唯一的“日”，天数：365）；订购日期改为列，产品名称改为行；现在可以选择任一类别，对B列或C列按降序按钮即可，还可以选中透视表的任意单元格用鼠标右键选中“分页显示”。2、针对1996与1997年度制作销售额居于前20位的产品的销售额总汇表并确定排名升幅度最大的两种产品和降幅度最大的两种产品。复制1中得到的表的非总计部分到一个新sheet，将表按96年度降序排列，在D1列键入“96年度名次”，在D2以后键入1、2、….，然后再将表按97年度降序排列，在E1键入“97年度名次”，在E2以后键入1、2、….，在F1键入“名次升降”，在F2键入=D2-E2，复制到F列中。用分别用D、E列前20名的数据绘制散点图。3、绘制一个表明1996年度与1997年度中产品累计销售额百分比随产品个数百分比变化的曲线图形。在一个新工作簿的范围C7:C83中列出77种产品按96年度销售额的降序排列后的名次，在E7:E83中列出这些产品的96年度销售额，再在D7与F7中键入以下公式：D7：=C7/$C$83，F7:SUM($E$7:E7)/SUM($E$7:$E$83)，并将它们分别在D列和F列复制。利用D列和F列作图，X轴是商品总数百分比，Y轴是销售额百分比。将96与97年度的图作在一个坐标系下。与Pareto曲线相应地在管理学中有所谓“80-20规则”，对于当前的销售额统计问题来说，该规则的意义是：一个公司在正常情况下前20%的商品（从销售额最大的商品开始向下排列的）的销售额大约应该占到其总销售额的80%左右。现在Northwind公司的两个年度的销售额情况都没有符合2-8规则，这表明该公司在市场营销方面存在较大的问题，对于重要产品的推销力度不够大。四、制作表示不同地区销售模式的可选式图形要求：生成一个带有控制面板的可选式图形，其控制面板具有不同地区单选钮，操作者可以通过在控制面板上的选择使图形显示出Northwind公司在各地区的产品类别销售额的变化模式。解：1、在数据透视表下，选择订单.货主地区，类别.类别名称，产品.产品名称，订单明细.单价、数量和折扣等，回到Query添加一个订单明细.单价*订单明细.数量*（1-订单明细.折扣）字段。将货主地区作为行，类别名称作为列，销售量作为数据。2、在A16中键入“=INDEX(A5:A11,$A$13)”并拷贝到该行其他单元格中。利用窗体工具（视图.工具栏.窗体）制作一个带7个单选钮的分组框，单选钮以A13为链接（右键单选钮.设置控制格式）；利用16行的数据作一个柱形图。3、结果分析。五、确定下月产品短缺数量与所需要补充的采购资金假定Northwind公司的采购部门每月初都在过去一个月中各种产品销售数量的基础上，根据一个给定的销售量月增长率（该增长率考虑了应付不确定需求所需要准备的机动库存量）估算出当月每种产品的需求量，然后根据库存量与当月需求量之间的短缺数量制定各种产品的补充采购计划。1998年5月初公司各种产品的库存量保存在其数据库的产品表的“库存量”字段中。要求：1、假定销售量月增长率为15%，在这个前提下按照公司的上述传统做法，在1998年5月6日估算出当月（从5月7日到6月6日）所需要的库存补充采购资金。2、制作一个公司当月所需库存补充采购资金随销售量月增长率变化的曲线图形。由于Northwind数据库中，单价字段（产品中或订单明细中）无法区分购入价与销售价，这里理解为销售价，于是约定每种产品的购入价等于其销售价的95%。解：1、在数据透视表中选择产品.产品名称、库存量、单价，订单.订购日期，订单明细.数量。对订购日期进行过滤，只选出最后一个月的（98.4.6-98.5.5）记录，回到Query。2、在Query中删除订购日期，选黑“数量”，单击“总计”按钮一次，获得各产品的月总订购量。3、回到Excel，将产品名称作为列，将数量、库存量和单价都作为汇总数据，在结果表中，将“数据”拖到“汇总”单元格，（汇总数据由行变成列）。4、将结果拷入另一个sheet中，修改列名并添加3个列：98.6需求量、短缺商品、购入单价。在D2:D4中键入数据和结果公式。结果表明，在月销售增长率为15%的情况下，下个月需要补充的产品有29个，需要补充的资金为122644.9元。（用前一个月各种产品实际销量和给定的月增长率来估计下一个月各种产品的销量，肯定有不准确的地方。特别是，对于前一个月没有发生销售的产品，完全失去了考虑的依据）5、制作一个关于所需采购随销售量月增长率变化的曲线图形。在I5:K11中用模拟运算表。数据点格式，数据标志格式六、按运费大小将所有订货交易分组要求：1、对Northwind公司近两年来所发生的所有订货交易进行统计，生成一个针对不同销往地区、对于不同运货费给出这些运货费发生次数的数据透视表。2、将Northwind公司所有的订货交易按照运货费从小到大以100元的间距加以分组，在此基础上对于不同的销往地区绘制出各个组中的订货次数随组变化的曲线。解：1、在数据透视表下，选择订单.货主地区、运货费，将运货费作为行，货主地区作为页，再将运货费作为汇总数据，在数据透视表窗口的菜单.字段下，将“求和项：运货费”改为“计数项：运货费”；2、点击运货费列的任意单元格，数据.分组及分级显示，间隔选择10。再将数据透视表中的运货费数据项拖到汇总数据区中，将“计数项：运货费2”改为“求和项：运货费2”（右键运货费2的区域，选择“字段设置”），使两个运货费并列（拖“数据”单元格到“汇总”单元格），将结果拷贝到新表，增加组号列、累计运货次数百分比列和累计运货费用百分比列。基尼曲线下方曲边三角形的面积与45度直线下的直角三角形面积之比为基尼系数。在经济学中，常用基尼系数来说明社会成员财富分配不均匀的现象。基尼系数越小，社会成员的财富分配不均匀程度越大。在目前的运货问题中，计算结果表明Northwind公司的运货费基尼系数等于。七、对运货费、运货期与发货延迟期之间的关系的研究Northwind公司管理当局感到在每笔客户订货业务中所发生的运货费与发货不及时的状况以及运货期的长短等方面存在着一定的问题，但不清楚这些问题的性质。要求：从公司历史数据中找出在这三个量之间所存在的问题。解：1、建立查询：订单的订单ID、订购日期、发货日期、到货日期、货主城市和运货费的六个字段。返回到Excel。将不缺发货日期的记录拷贝到一个新表（按发货日期排序，然后拷贝）2、添加字段“运货期”（到货日期-发货日期），“发货延迟期”（发货日期-订购日期）。按运货期排序，排除运货期为零和负的41个记录，将“货主城市”、“运货费”、“运货期”和“发货延迟期”等4个字段拷贝到一个新表。3、建立一个数据透视表：运货期为行，运货费为汇总数据，选择平均值汇总。此处平均值的含义是将所有具有相同运货期的所有订货交易中的运货费进行平均。建立平均运货费随运货期的变化散点图。（说明无关性的直观图）4、将运货费变为行，运货期和延迟作为数据汇总，将运货费分组显示。作三组数据的折线图。5、将货主城市作为行，运货费、运货期和延迟作为汇总数据，作4组数据的折线图，使用主y轴表示运费，使用次y轴显示时间天数。6、使用基于分组运费的运货期与延迟作为列，作三点图（延迟为y轴），得线性关系，再加入基于城市的运货期与延迟数据，仍然集中在直线附近。linest()是数组函数，值一行2列，用ctrl+shift+enter执行，得斜率和截距。八