2022年数据查询汇总与分析实例 .pdf

《2022年数据查询汇总与分析实例 .pdf》由会员分享，可在线阅读，更多相关《2022年数据查询汇总与分析实例 .pdf（4页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、数据查询汇总与分析实例参考书： 王兴德著， 管理决策模型55 例，上海交通大学出版社，2000，学校书号： F20 1092 一、数据库数据库 NORTHWIND.MDB。8 个表：产品（ 77 个记录）、类别（ 8 个记录）、供应商（ 29 个记录） 、雇员（ 9 个记录）、客户（ 91 个记录）、运货商（ 3 个记录）、订单（ 830 个记录）与订单明细（ 2157 个记录）。前 6 个表记录着关于该公司所经销的各种产品及其分类、供应商、雇员、 客户以及运货商等实体的静态数据，后两个表记录着该公司从1996 年 7 月 4 日至 1998年 5 月 6 日期间发生的所有的客户订货业务数据。

2、二、确定各月销往各地区的各类商品月销售额要求：1、 在 Northwind 公司数据库所保存的订货数据的基础上制作一个关于该公司在1996 年 7 月1998 年 5 月间各月销往各个地区的各种类别产品的月销售额汇总表。2、绘制一个从该公司1996 年 7 月1998 年 4 月期间该公司月销售随时间变化的折线图形，并在该图形中添加一条直线趋势线，再求出该趋势线的斜率与截距。3、求出对于该公司1998 年 6 月月销售额的预测值。解：首先，通过Microsoft Query 的帮助，在Excel工作表中生成一个记载着Northwind 公司从 1996 年 7月 4 日至 1998 年 5 月

3、 6 日期间所有各个订购日期发生的订货业务的完整列表，其中包含“订购日期” 、 “类别名称”、 “地区”、 “数量”、 “单价”和“折扣”等六列，再增加一个“销售额”列；然后，在上述数据列表的基础上，应用Excel 的汇总功能，在另一个工作表中生成所需要的、销往不同地区的不同类型产品的月销售额数据。打开一个新工作簿文件，选择菜单“数据”- “获取外部数据” - “新建查询”， 在运行的 Microsoft Query 的“选择数据表” 对话框中， 选择数据库MORTHWIND，接下来跟着查询向导选择“订单”中的“订购日期” 、 “订单明细”中的“单价”、 “数量”和“折扣” ， “类别”中的“

4、类别名称”以及“客户”中的“地区”等。在看到警告之后，确定，添加“产品”表即可。返回Excel，并增加一个销售额字段，输入相应的公式（ =b2*c2*(1-d2) ） 。双击复制。接下来用“数据透视表”进行汇总。将“订单日期”作为行，“类别名称”作为列， “客户地区”作为页，“销售额”作为求和汇总；然后对订单日期进行“分组及分级显示”-“组合”，将“月”和“年”都选中状态。绘制全部商品月销售额的变化折线图形。1、透视图的调整（说明透视图的灵活性）；2、复制到新sheet（不在透视图上绘图，因为透视图是可以调整和变化的，一旦透视图产生了变化，图的意义就不清楚了）；3、 订购日期的调整 （ “编辑

5、 -定位 -定位条件 -空值” ； 键入等号 -向上方向键 -Ctrk+enter ； A2&b2） 。4、 绘制折线图 （由于日期数据都是文字-字符 -， 所以这个图形只能使用折线图形式来绘制。）5、添加趋势线（选中曲线，“图表”菜单中的“添加趋势线”，一些选项：显示公式、R）确定 Northwind 公司 1998 年 6 月的总销售额的预测值。利用拟合直线的斜率合截距或利用forecast()函数。 （给出前面各个月的拟合值）三、按照年度销售额确定产品的排名榜假定 Northwind 公司将从每年7 月 1 日至下一年6 月 30 日规定为一个会计年度，要求：1、针对 1996 与 19

6、97 两个年度（不考虑数据库中目前缺少1997 年度最后两个月份销售数据的事实），对于该公司所经销的每个产品类别生成一个显示其中各种产品销售额排名榜的名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 4 页 - - - - - - - - - 汇总表。2、在针对1996 与 1997 两个年度的、显示全部77 种产品总排名榜的汇总表中截取出只包含前 20 种产品的销售额汇总表，并在此表的基础上确定两个年度中排名升幅度最大的两种产品与降幅度最大的两种产品。3、在将所有产品按销

7、售额作降序排列的基础上绘制一个表明1996 与 1997 两个年度中产品累计销售额百分比随产品个数百分比变化的曲线图形，在图中添加一条与20%这个产品个数百分比值对应的垂直参考线，并标出曲线与该参考线相交处的高度。解：1、用数据透视表完成而不用保留查询结果的方法。数据 -数据透视表 -外部数据源；获取数据-“Northwind ”-订单 .订购日期、类别.类别名称、产品 .产品名称、订单明细.数量、单价、折扣-,.-在 Microsoft Query 中浏览数据；记录-添加列-字段：订单明细.单价 * 订单明细 .数量 * （1-订单明细 .折扣） -列标：销售额-返回到Excel；订购日期作

8、为行，销售量作为汇总，列暂缺， 类别名称和产品名称作为页；订购日期分组显示（数据 -分组显示 -起始时间： 1996/7/1 ，步长或依据为唯一的“日”，天数： 365） ；订购日期改为列，产品名称改为行；现在可以选择任一类别，对B 列或 C 列按降序按钮即可，还可以选中透视表的任意单元格用鼠标右键选中“分页显示”。2、针对 1996 与 1997 年度制作销售额居于前20 位的产品的销售额总汇表并确定排名升幅度最大的两种产品和降幅度最大的两种产品。复制 1 中得到的表的非总计部分到一个新sheet， 将表按 96 年度降序排列， 在 D1 列键入“ 96年度名次”，在 D2 以后键入 1、2

9、、, .，然后再将表按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)，并将

10、它们分别在D 列和 F 列复制。利用D 列和 F列作图，X轴是商品总数百分比，Y轴是销售额百分比。将 96 与 97 年度的图作在一个坐标系下。与 Pareto 曲线相应地在管理学中有所谓“80-20 规则”，对于当前的销售额统计问题来说，该规则的意义是： 一个公司在正常情况下前20%的商品（从销售额最大的商品开始向下排列的）的销售额大约应该占到其总销售额的80%左右。 现在 Northwind 公司的两个年度的销售额情况都没有符合2-8 规则， 这表明该公司在市场营销方面存在较大的问题，对于重要产品的推销力度不够大。四、制作表示不同地区销售模式的可选式图形要求： 生成一个带有控制面板的可选式

11、图形，其控制面板具有不同地区单选钮，操作者可以通过在控制面板上的选择使图形显示出Northwind公司在各地区的产品类别销售额的变化模式。解：1、在数据透视表下，选择订单 .货主地区， 类别 .类别名称， 产品 .产品名称， 订单明细 .单价、数量和折扣等，回到Query 添加一个订单明细.单价 *订单明细 .数量 *（1-订单明细 .折扣）字段。将货主地区作为行，类别名称作为列，销售量作为数据。2、在 A16 中键入“ =INDEX(A5:A11,$A$13)”并拷贝到该行其他单元格中。利用窗体工具（视图. 工具栏 . 窗体）制作一个带7 个单选钮的分组框，单选钮以 A13 为链接（右键单选

12、钮 . 设置控制格式） ；利用 16 行的数据作一个柱形图。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 4 页 - - - - - - - - - 3、结果分析。五、确定下月产品短缺数量与所需要补充的采购资金假定 Northwind 公司的采购部门每月初都在过去一个月中各种产品销售数量的基础上，根据一个给定的销售量月增长率（该增长率考虑了应付不确定需求所需要准备的机动库存量）估算出当月每种产品的需求量，然后根据库存量与当月需求量之间的短缺数量制定各种产品的补充采购计划

13、。 1998 年 5 月初公司各种产品的库存量保存在其数据库的产品表的“库存量”字段中。要求：1、假定销售量月增长率为15%，在这个前提下按照公司的上述传统做法，在1998 年 5 月 6日估算出当月（从5 月 7 日到 6 月 6 日）所需要的库存补充采购资金。2、制作一个公司当月所需库存补充采购资金随销售量月增长率变化的曲线图形。由于 Northwind 数据库中，单价字段（产品中或订单明细中）无法区分购入价与销售价，这里理解为销售价，于是约定每种产品的购入价等于其销售价的95%。解：1、在数据透视表中选择产品.产品名称、库存量、单价，订单.订购日期，订单明细.数量。对订购日期进行过滤，只

14、选出最后一个月的（98.4.6-98.5.5）记录，回到Query。2、在 Query 中删除订购日期，选黑“数量”，单击“总计”按钮一次，获得各产品的月总订购量。3、回到Excel，将产品名称作为列，将数量、库存量和单价都作为汇总数据，在结果表中，将“数据”拖到“汇总”单元格，（汇总数据由行变成列）。4、将结果拷入另一个sheet 中，修改列名并添加3 个列： 98.6 需求量、短缺商品、购入单价。在 D2:D4 中键入数据和结果公式。结果表明，在月销售增长率为15%的情况下，下个月需要补充的产品有29 个，需要补充的资金为122644.9 元。 （用前一个月各种产品实际销量和给定的月增长率

15、来估计下一个月各种产品的销量，肯定有不准确的地方。特别是， 对于前一个月没有发生销售的产品，完全失去了考虑的依据）5、制作一个关于所需采购随销售量月增长率变化的曲线图形。在I5:K11 中用模拟运算表。数据点格式，数据标志格式六、按运费大小将所有订货交易分组要求：1、对 Northwind公司近两年来所发生的所有订货交易进行统计，生成一个针对不同销往地区、对于不同运货费给出这些运货费发生次数的数据透视表。2、将 Northwind 公司所有的订货交易按照运货费从小到大以100 元的间距加以分组，在此基础上对于不同的销往地区绘制出各个组中的订货次数随组变化的曲线。解：1、在数据透视表下，选择订单

16、.货主地区、运货费，将运货费作为行，货主地区作为页，再将运货费作为汇总数据，在数据透视表窗口的菜单.字段下， 将“求和项：运货费”改为“计数项：运货费” ；2、点击运货费列的任意单元格，数据.分组及分级显示，间隔选择 10。再将数据透视表中的运货费数据项拖到汇总数据区中，将“计数项：运货费2”改为“求和项：运货费2” （右键运货费 2 的区域，选择“字段设置”） ，使两个运货费并列（拖“数据”单元格到“汇总”单元格） ， 将结果拷贝到新表，增加组号列、 累计运货次数百分比列和累计运货费用百分比列。基尼曲线下方曲边三角形的面积与45 度直线下的直角三角形面积之比为基尼系数。在经济学中， 常用基尼

17、系数来说明社会成员财富分配不均匀的现象。基尼系数越小， 社会成员的财富分配不均匀程度越大。在目前的运货问题中，计算结果表明Northwind 公司的运货费基尼系数等于。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 4 页 - - - - - - - - - 七、对运货费、运货期与发货延迟期之间的关系的研究Northwind 公司管理当局感到在每笔客户订货业务中所发生的运货费与发货不及时的状况以及运货期的长短等方面存在着一定的问题，但不清楚这些问题的性质。要求： 从公司历

18、史数据中找出在这三个量之间所存在的问题。解：1、建立查询：订单的订单ID、订购日期、发货日期、到货日期、货主城市和运货费的六个字段。 返回到 Excel。将不缺发货日期的记录拷贝到一个新表（按发货日期排序，然后拷贝）2、添加字段“运货期” （到货日期 -发货日期）， “发货延迟期” （发货日期 -订购日期）。按运货期排序， 排除运货期为零和负的41 个记录， 将“货主城市” 、 “运货费”、 “运货期” 和“发货延迟期”等4 个字段拷贝到一个新表。3、建立一个数据透视表：运货期为行，运货费为汇总数据，选择平均值汇总。此处平均值的含义是将所有具有相同运货期的所有订货交易中的运货费进行平均。建立平

19、均运货费随运货期的变化散点图。 （说明无关性的直观图）4、将运货费变为行，运货期和延迟作为数据汇总，将运货费分组显示。作三组数据的折线图。5、将货主城市作为行，运货费、运货期和延迟作为汇总数据，作4 组数据的折线图，使用主 y 轴表示运费，使用次y 轴显示时间天数。6、使用基于分组运费的运货期与延迟作为列，作三点图（延迟为y 轴） ，得线性关系，再加入基于城市的运货期与延迟数据，仍然集中在直线附近。linest()是数组函数，值一行2 列，用 ctrl+shift+enter 执行，得斜率和截距。八、绘制表示不同地区不同产品类别三年销售额的可选式图形（略）名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 4 页 - - - - - - - - -