《EXCEL第3章_数据分类汇总分析.ppt》由会员分享，可在线阅读，更多相关《EXCEL第3章_数据分类汇总分析.ppt（70页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、Excel预测与决策分析预测与决策分析第三章第三章 数据分类汇总分析数据分类汇总分析n基础篇基础篇n数据分类汇总分析的意义和作用数据分类汇总分析的意义和作用n数据分类汇总的四种方法数据分类汇总的四种方法n数据透视表数据透视表nD函数和模拟运算表函数和模拟运算表n提高篇提高篇n利用利用DSUM函数和控件控制分类汇总结果函数和控件控制分类汇总结果n分类汇总数据的应用分类汇总数据的应用nExcel中的多维数据分析中的多维数据分析OLAP内容简介：内容简介：第一节第一节 数据分类汇总分析的意义和作用数据分类汇总分析的意义和作用n获得销售额分类统计值获得销售额分类统计值ABCABC公司公司1995199
2、5年不同省份不同类别商品的销售额总计值年不同省份不同类别商品的销售额总计值010000100002000020000300003000040000400005000050000艺术品艺术品 自行车自行车服装服装食品食品 儿童用品儿童用品体育用品体育用品瓷器瓷器类别类别销售额销售额江西江西安徽安徽江苏江苏广东广东福建福建浙江浙江山东 第一节第一节 数据分类汇总分析的意义和作用（续）数据分类汇总分析的意义和作用（续）n获得各类销售额排行榜获得各类销售额排行榜NorthwindNorthwind公司前十大客户销售额公司前十大客户销售额0 0300030006000600090009000120001
3、20001500015000正人资源正人资源五洲信托五洲信托嘉业嘉业艾德高科技艾德高科技幸义房屋幸义房屋迈多贸易迈多贸易百达电子百达电子悦海悦海上河工业上河工业池春建设池春建设客户公客户公司名称司名称销售额销售额 NorthwindNorthwind公司前十大产品销售额公司前十大产品销售额0 030003000600060009000900012000120001500015000猪肉猪肉猪肉干猪肉干鸭肉鸭肉绿茶绿茶白米白米苏澳奶酪苏澳奶酪运动饮料运动饮料山渣片山渣片墨鱼墨鱼牛肉干牛肉干产品产品名称名称销售额销售额 第一节第一节 数据分类汇总分析的意义和作用（续）数据分类汇总分析的意义和作用（
4、续）n获得各类销售额的时间序列获得各类销售额的时间序列NorthwindNorthwind公司不同产品的销售额时间序列公司不同产品的销售额时间序列y=39.401x-39.299y=39.401x-39.299R R2 2=0.4649=0.46490 02002004004006006008008001000100012001200140014001600160019961996年年7 7月月19961996年年9 9月月19961996年年1111月月19971997年年1 1月月19971997年年3 3月月19971997年年5 5月月19971997年年7 7月月19971997年年
5、9 9月月19971997年年1111月月19981998年年1 1月月19981998年年3 3月月时间时间销售额销售额花生花生 第一节第一节 数据分类汇总分析的意义和作用（续）数据分类汇总分析的意义和作用（续）n获得各经济量之间的相关性获得各经济量之间的相关性NorthwindNorthwind公司分段公司分段平均运货费与平均销售金额平均运货费与平均销售金额的依赖关系的依赖关系y=3.0652x+292.37y=3.0652x+292.37R R2 2=0.8469=0.84690 0200200400400600600800800100010000 0202040406060808010
6、0100120120140140160160180180200200平均平均运货费运货费平均销售额平均销售额 第一节第一节 数据分类汇总分析的意义和作用（续）数据分类汇总分析的意义和作用（续）n获得各种产品需求量的频率分布获得各种产品需求量的频率分布NorthwindNorthwind公司公司19971997年白米月销量分布图年白米月销量分布图0 01 12 23 34 45 5202035355050656580809595110110 125125 140140 155155 170170 185185 200200销量分组销量分组销售发生次数销售发生次数 第二节第二节数据分类汇总的四种方
7、法数据分类汇总的四种方法n数据查询的统计值功能数据查询的统计值功能n数据清单功能数据清单功能n数据透视表数据透视表nD函数与模拟运算表函数与模拟运算表第二节第二节数据分类汇总的四种方法数据分类汇总的四种方法n汇总字段汇总字段：待汇总的变量或字段。：待汇总的变量或字段。n销售额、销售数量、工作时间等。销售额、销售数量、工作时间等。n分类字段分类字段：对数据进行汇总时，以某个：对数据进行汇总时，以某个变量或字段的不同值为参考来对汇总字段变量或字段的不同值为参考来对汇总字段进行汇总。进行汇总。n参考不同产品类别参考不同产品类别(分类字段分类字段)汇总销售额（汇总汇总销售额（汇总字段）字段）n参考不同
8、的生产人员姓名（分类字段）汇总工作时参考不同的生产人员姓名（分类字段）汇总工作时间间 （汇总字段）（汇总字段）第二节第二节 数据分类汇总的四种方法（续）数据分类汇总的四种方法（续）ABC公司公司1995各省份、各类别商品净销售额总计值各省份、各类别商品净销售额总计值（图（图3-7）一、数据查询的统计值功能一、数据查询的统计值功能n从从Excel中启动中启动Microsoft Query，利用利用Query的统计的统计值功能，将数据汇总后返回值功能，将数据汇总后返回Exceln【例例3-1】ABC公司销售数据公司销售数据.dbf中保存着公司从中保存着公司从1994年到年到1997年三年的销售数据
9、，利用年三年的销售数据，利用Query软件软件的统计值功能汇总该公司的销售数据，并按照图的统计值功能汇总该公司的销售数据，并按照图3-7的形式，汇总出的形式，汇总出1995年不同省份、不同类别商品的年不同省份、不同类别商品的净销售额。净销售额。一、数据查询的统计值功能（续）一、数据查询的统计值功能（续）二、二、Excel数据清单功能数据清单功能n数据清单数据清单数据清单是包含相关数据的一系列工作表数据清单是包含相关数据的一系列工作表数据行数据行。数据清单的数据清单的首行为字段名首行为字段名，首行下的各行，首行下的各行是各个记录，数据清单中是各个记录，数据清单中不能出现空行不能出现空行。n数据清
10、单的数据清单的基本功能基本功能n排序排序n筛选筛选n分类总计分类总计二、二、Excel数据清单功能（续）数据清单功能（续）n利用利用Excel数据清单处理功能进行数据清单处理功能进行分类汇分类汇总总的一般步骤：的一般步骤：n获得数据清单；获得数据清单；n设定筛选条件；设定筛选条件；n筛选数据，将筛选结果复制到工作表其他位筛选数据，将筛选结果复制到工作表其他位置；置；n对经过筛选的数据按分类字段进行排序；对经过筛选的数据按分类字段进行排序；n对排序后的数据进行分类汇总。对排序后的数据进行分类汇总。二、二、Excel数据清单功能（续）数据清单功能（续）n【例例3-2】利用利用Excel的数据清单功
11、能，将的数据清单功能，将ABC公司的销售数公司的销售数据按照图据按照图3-7的形式，汇总出的形式，汇总出1995年不同年不同省份省份、不同、不同类别类别商商品的品的净销售额净销售额总计值。总计值。n 将上述汇总表选将上述汇总表选中第中第3级汇总，然后级汇总，然后选中所需数据区域，选中所需数据区域，单击单击“选定可见单元选定可见单元格格”（工具（工具-自定义自定义-编辑），再单击编辑），再单击“复复制制”，将光标移到空，将光标移到空白数据区域，单击粘白数据区域，单击粘贴，得到如图数据。贴，得到如图数据。n 根据右图数据再根据右图数据再加以调整，便可得到加以调整，便可得到如图如图3-7的数据。的数
12、据。第三节第三节 数据透视表数据透视表n数据透视表的基本功能数据透视表的基本功能n数据透视表结构的灵活性数据透视表结构的灵活性n数据透视图的灵活性数据透视图的灵活性n利用数据透视表生成时间序列利用数据透视表生成时间序列n统计不同规模销量的发生次数以及概率分布统计不同规模销量的发生次数以及概率分布n计算占同列数据总和的百分比计算占同列数据总和的百分比一、数据透视表的基本功能一、数据透视表的基本功能n数据透视表分类汇总的两种方法数据透视表分类汇总的两种方法n先将数据导入先将数据导入Excel成为数据清单，利用数成为数据清单，利用数据透视表汇总据透视表汇总对数据清单进行汇总对数据清单进行汇总n利用数
13、据透视表利用数据透视表直接从数据库中查询、并直接从数据库中查询、并汇总数据汇总数据一、数据透视表的基本功能（续）一、数据透视表的基本功能（续）n【例例3-3】利用利用Excel获取外部数据功能，获取获取外部数据功能，获取ABC公司公司1995年的销售数据，使用年的销售数据，使用Excel数据透数据透视表功能制作如图视表功能制作如图3-7所示的分类汇总所示的分类汇总表。表。提示：提示：启动启动excel-数据数据透视表和数据透视图数据数据透视表和数据透视图导入外部数据新建数据库查询，在导入外部数据新建数据库查询，在query获得汇总数据后返回获得汇总数据后返回excel，单击数据透视图的单击数据
14、透视图的“图表向导图表向导”，生成数据透视图。，生成数据透视图。一、数据透视表的基本功能（续）一、数据透视表的基本功能（续）一、数据透视表的基本功能（续）一、数据透视表的基本功能（续）0 050005000100001000015000150002000020000250002500030000300003500035000400004000045000450005000050000儿童用品儿童用品服装服装食品食品体育用品体育用品艺术品艺术品自行车自行车请将页字段拖至此处请将页字段拖至此处求和项求和项:净销售额净销售额类别类别安徽安徽广东广东江苏江苏江西江西山东山东省份省份n【例例3-4】直接
15、利用直接利用Excel数据透视表功能数据透视表功能从数据库中查询数据，并根据查询结果汇从数据库中查询数据，并根据查询结果汇总，制作出如图总，制作出如图3-7所示的分类汇总表。所示的分类汇总表。n提示：提示：启动启动excel-数据数据透视表和数数据数据透视表和数据透视图外部数据源据透视图外部数据源二、数据透视表结构的灵活性二、数据透视表结构的灵活性n改变数据透视表的行列结构改变数据透视表的行列结构n利用页域对数据进行分页汇总利用页域对数据进行分页汇总n新增、删除和修改行域、列域或页域字段（分类新增、删除和修改行域、列域或页域字段（分类字段）字段）n新增、删除和修改汇总字段新增、删除和修改汇总字
16、段n改变汇总字段的汇总方式改变汇总字段的汇总方式n同一个字段既作分类字段又作汇总字段同一个字段既作分类字段又作汇总字段二、数据透视表结构的灵活性（续）二、数据透视表结构的灵活性（续）n改变分类字段值的位置改变分类字段值的位置n对分类字段的值加以合并对分类字段的值加以合并n隐藏行域、列域的字段值隐藏行域、列域的字段值n展开汇总值，显示详细数据展开汇总值，显示详细数据三、数据透视图的灵活性三、数据透视图的灵活性n变换分类字段的位置变换分类字段的位置n将行、列分类字段换成页分类字段将行、列分类字段换成页分类字段n新增、删除分类字段新增、删除分类字段n新增、删除汇总字段新增、删除汇总字段n改变被汇总方
17、式改变被汇总方式n显示分类字段的部分值显示分类字段的部分值四、四、利用数据透视表生成时间序列利用数据透视表生成时间序列n时间序列时间序列:按照一定时间间隔汇总的数据序列按照一定时间间隔汇总的数据序列n【例例3-5】利用数据透视表，对利用数据透视表，对Northwind Trader公司的销售公司的销售数据数据按月按月汇总种汇总种各产品各产品销售额销售额。例例3-5 操作方法操作方法n数据进行数据查询数据进行数据查询-query，获得获得Northwind公司的公司的订购日期，产品名称，销售额订购日期，产品名称，销售额(订单明细订单明细.单价单价*数量数量*(1-折扣折扣)n光标停留在目标数据的
18、任意单元格，数据光标停留在目标数据的任意单元格，数据数据透视数据透视表和数据透视图表和数据透视图Microsoft Excel数据列表或数据库数据列表或数据库数据透视表，数据透视表，next，新建工作表，新建工作表，next。n订购日期拖至行域，销售额拖至列域，产品名称拖至页订购日期拖至行域，销售额拖至列域，产品名称拖至页域。域。n右击目标数据任意单元格，组及显示明细数据右击目标数据任意单元格，组及显示明细数据组合，组合，起始于起始于1996-7-1，终止于，终止于1998-5-31，依据为先月，后年。，依据为先月，后年。五、统计不同规模销量的发生次数及频率分布五、统计不同规模销量的发生次数及
19、频率分布n【例例3-6】利用数据透利用数据透视表，对视表，对Northwind公公司的销售数据按照司的销售数据按照不不同产品同产品、不同时间不同时间段，段，统计统计不同规模销售数不同规模销售数量量的发生次数，并计的发生次数，并计算其频率分布。算其频率分布。例例3-6操作方法操作方法n利用外部数据获得利用外部数据获得Northwind公司产品公司产品名称，订购日期，数量名称，订购日期，数量。n选定数据清单任意单元格，数据选定数据清单任意单元格，数据-数据透视表和数据透视图，默数据透视表和数据透视图，默认选项认选项next。n订购日期拖至行域，产品名称拖至页域，数量拖至数据域。订购日期拖至行域，产
20、品名称拖至页域，数量拖至数据域。n双击双击“求和项：数量求和项：数量”，汇总方式选，汇总方式选“计数计数”-确定。确定。n按例按例3-5方法对年月进行分组，然后将方法对年月进行分组，然后将“年年”和和“订购日期订购日期”拖到拖到页域，从数据透视表字段列表中将页域，从数据透视表字段列表中将“数量数量”拖至行域，在数量列任拖至行域，在数量列任意单元格右键，组及显示明细数据意单元格右键，组及显示明细数据-组合，起始于组合，起始于1，终止于，终止于130，确定。，确定。n右击右击“数量数量”数据任意单元格，表格选项，去掉行、列总计。数据任意单元格，表格选项，去掉行、列总计。nD6单元格输入单元格输入“
21、销量分组销量分组”，E6输入输入“频率频率”，D7输入输入“=A7”，E7输入输入“B7/SUM($B$7:$B$19)”，将，将E7数据格式设为百分数，数据格式设为百分数，小数点保留一位，并将小数点保留一位，并将D7和和E7复制到复制到D19和和E19单元格。单元格。n绘制频率分布图：选中绘制频率分布图：选中D6到到E19，单击图表向导，选柱状图，单击图表向导，选柱状图，next，next。五、统计不同规模销量的发生次数及频率分布五、统计不同规模销量的发生次数及频率分布NorthwindNorthwind公司不同规模销量的频率分布公司不同规模销量的频率分布0%0%5%5%10%10%15%1
22、5%20%20%25%25%30%30%35%35%1-101-1011-2011-2021-3021-3031-4031-4041-5041-5051-6051-6061-7061-7071-8071-8081-9081-9091-10091-100101-110101-110111-120111-120121-130121-130产品名称产品名称(全部全部)年年(全部全部)订购日期订购日期(全部全部)计数项计数项:数量数量数量数量汇总汇总在此处放置系列字段在此处放置系列字段第四节第四节 D函数和模拟运算表函数和模拟运算表nD函数函数n模拟运算表模拟运算表n生成时间序列生成时间序列n统计不同
23、规模销量的发生次数以及概率统计不同规模销量的发生次数以及概率分布分布一、一、D函数函数nD函数：对数据清单或数据库中的数函数：对数据清单或数据库中的数据进行分析有据进行分析有 12个数据库内建函数。个数据库内建函数。n常用的常用的D函数包括：函数包括：nDSUM()nDAVERAGE()nDCOUNT()nDCOUNTA()一、一、D函数（续）函数（续）nD函数名称函数名称(数据清单数据清单,统计字段统计字段,条件范围条件范围)n统计满足条件记录制定字段的总计值。统计满足条件记录制定字段的总计值。n数据清单数据清单：包含相关数据的工作表中的一：包含相关数据的工作表中的一系列数据行系列数据行n统
24、计字段统计字段：被汇总字段。可以使用字段名、：被汇总字段。可以使用字段名、字段名所在的单元格、或者字段在数据清字段名所在的单元格、或者字段在数据清单中所在列的序号。单中所在列的序号。n条件范围条件范围：规定函数的匹配条件的一个工：规定函数的匹配条件的一个工作表区域。作表区域。D函数只对满足该条件范围所函数只对满足该条件范围所规定条件的记录进行汇总。规定条件的记录进行汇总。一、一、D函数（续）函数（续）n【例例3-8】利用利用ABC公司的销售数据，应用公司的销售数据，应用DSUM()函数函数，生成一，生成一个与个与图图3-7相同的数据分类汇相同的数据分类汇总表，列出总表，列出ABC公司公司199
25、5年年不同不同省份省份不同不同类类别别净销售额净销售额总计值。总计值。例例3-8操作方法操作方法n1、获得数据清单。日期，省份，类别，净销售额，毛、获得数据清单。日期，省份，类别，净销售额，毛销售额。销售额。n2、命名一空白工作薄为、命名一空白工作薄为DSUM()函数用法，在单元格函数用法，在单元格B2:E2输入：日期、日期、省份、类别。在输入：日期、日期、省份、类别。在B3:E3输入：输入：=95/1/1、模拟运算表，将省份和类别依次模拟运算表，将省份和类别依次输入模拟运算表的引用行输入模拟运算表的引用行$D$3、列、列$E$3单元格，单元格，确定。确定。以下内容部分不作要求以下内容部分不作
26、要求二、模拟运算表（续）二、模拟运算表（续）n利用控件控制汇总条件利用控件控制汇总条件分类字段分类字段n【例例3-10】在在例例3-9的基础上，利用的基础上，利用DSUM()函数和函数和模拟运算表模拟运算表，计算，计算ABC公司不同省份、不同类别净公司不同省份、不同类别净销售额总计值，使用窗体中的组合框对省份进行控销售额总计值，使用窗体中的组合框对省份进行控制，当选择组合框中的不同省份时，模拟运算表计制，当选择组合框中的不同省份时，模拟运算表计算出该省份净销售额总计值，并以柱形图显示该结算出该省份净销售额总计值，并以柱形图显示该结果。果。二、模拟运算表（续）二、模拟运算表（续）各个省份不同类别
27、净销售总计值图各个省份不同类别净销售总计值图0 02000020000400004000060000600008000080000100000100000120000120000140000140000儿童用品儿童用品 服装服装食品食品体育用品体育用品 艺术品艺术品自行车自行车类别类别净净销销售售额额全部全部第五节第五节 利用利用DSUM函数和控件控制分类汇总结果函数和控件控制分类汇总结果n利用控件控制汇总条件利用控件控制汇总条件年份年份n n【例例例例3-113-11】在在在在 例例例例 3-103-10的的的的基基基基础础础础上上上上，使使使使用用用用组组组组合合合合框框框框控控控控制制制
28、制汇汇汇汇总总总总的的的的年年年年份份份份，并并并并以以以以柱柱柱柱形形形形图图图图形形形形式式式式显显显显示示示示各各各各个个个个年年年年份份份份各各各各个个个个类类类类别别别别的的的的净净净净销销销销售售售售额额额额总总总总计计计计值值值值，如如如如图图图图3-903-90所示。所示。所示。所示。各年各省份不同类别净销售总计值图各年各省份不同类别净销售总计值图0 0100001000020000200003000030000400004000050000500006000060000儿童用品儿童用品 服装服装食品食品体育用品体育用品 艺术品艺术品自行车自行车类别类别净销售额净销售额安徽安徽
29、19961996n利用控件控制汇总字段利用控件控制汇总字段第五节第五节 利用利用DSUM函数和控件控制分类汇总结果（续）函数和控件控制分类汇总结果（续）【例例例例3-123-12】在在在在例例例例3-113-11的的的的基基基基础础础础上上上上，使使使使用用用用列列列列表表表表框框框框控控控控制制制制汇汇汇汇总总总总字字字字段段段段为为为为“净净净净销销销销售售售售额额额额”或或或或“毛毛毛毛销销销销售售售售额额额额”，并并并并以以以以柱柱柱柱形形形形图图图图形形形形式式式式显显显显示示示示结结结结果果果果，如如如如图图图图3-3-9292所示所示所示所示 。各个省份不同类别净销售总计值图各个
30、省份不同类别净销售总计值图0 0100010002000200030003000400040005000500060006000儿童用品儿童用品 服装服装食品食品体育用品体育用品 艺术品艺术品自行车自行车类别类别净销售额净销售额江苏江苏19951995净销售额净销售额毛销售额毛销售额n【例例3-13】在例在例3-12的基础上，汇总特定省份和各省平均的净的基础上，汇总特定省份和各省平均的净销售额，并以图形显示结果，如图销售额，并以图形显示结果，如图3-94所示。所示。第五节第五节 利用利用DSUM函数和控件控制分类汇总结果（续）函数和控件控制分类汇总结果（续）各个省份不同类别净销售总计值图各个省
31、份不同类别净销售总计值图0 050005000100001000015000150002000020000250002500030000300003500035000400004000045000450005000050000瓷器瓷器儿童用品儿童用品服装服装食品食品体育用品体育用品艺术品艺术品类别类别净销售额净销售额江西江西各省平均各省平均江西江西净销售额毛销售额毛销售额19951995n【例【例3-14】使用】使用Northwind Trader 公司的销售数据，利用公司的销售数据，利用DSUM()和模拟运算表生成净销售额时间序列，创建可以调节的产品和模拟运算表生成净销售额时间序列，创建可以
32、调节的产品列表框，并绘制特定产品销售额的时间序列图形。列表框，并绘制特定产品销售额的时间序列图形。第五节第五节 利用利用DSUM函数和控件控制分类汇总结果（续）函数和控件控制分类汇总结果（续）NorthwindNorthwind公司产品的销售额时间序列公司产品的销售额时间序列y=39.401x-39.299y=39.401x-39.2990 0200200400400600600800800100010001200120014001400160016001996-7-11996-7-11996-8-11996-8-11996-9-11996-9-11996-10-11996-10-11996-
33、11-11996-11-11996-12-11996-12-11997-1-11997-1-11997-2-11997-2-11997-3-11997-3-11997-4-11997-4-11997-5-11997-5-11997-6-11997-6-11997-7-11997-7-11997-8-11997-8-11997-9-11997-9-11997-10-11997-10-11997-11-11997-11-11997-12-11997-12-11998-1-11998-1-11998-2-11998-2-11998-3-11998-3-11998-4-11998-4-1花生花生Nor
34、thwindNorthwind公司不同产品不同地区的销售额汇总公司不同产品不同地区的销售额汇总y=37.509x-211.79y=37.509x-211.79-500-5000 050050010001000150015001 1月月2 2月月3 3月月4 4月月5 5月月6 6月月7 7月月8 8月月9 9月月1010月月1111月月1212月月1 1月月2 2月月3 3月月4 4月月5 5月月6 6月月7 7月月8 8月月9 9月月1010月月1111月月1212月月1 1月月2 2月月3 3月月4 4月月5 5月月6 6月月7 7月月8 8月月9 9月月1010月月1111月月1212月
35、月产品名称产品名称 花生花生求和项求和项:销售额销售额19961996年年19971997年年19981998年年年年订购日期订购日期在此处放置系列字段在此处放置系列字段第六节第六节分类汇总数据的应用分类汇总数据的应用n企业经常在企业经常在“观测板观测板”上显示企业最关心的信息：销售上显示企业最关心的信息：销售额排在前十位产品的当月销售情况，最优秀员工目前的额排在前十位产品的当月销售情况，最优秀员工目前的工作进度，销售额增长最快地区的销售增长率，以及销工作进度，销售额增长最快地区的销售增长率，以及销路最差产品的销售情况等等。路最差产品的销售情况等等。n例例3-15，介绍为，介绍为Northwi
36、nd Trader公司发现连续公司发现连续2个季度销量个季度销量下滑产品的下滑产品的“观测版观测版”的方法。的方法。n零售超市需要对那些易变质食品打折，越接近保质期的零售超市需要对那些易变质食品打折，越接近保质期的产品打折比例越高。产品打折比例越高。n例例3-16帮助超市找到需要打折的产品，确定打折比例。帮助超市找到需要打折的产品，确定打折比例。第六节第六节分类汇总数据的应用（续）分类汇总数据的应用（续）n【例例3-15】Northwind Trader公司将根据产品的销售情公司将根据产品的销售情况来调整产品结构。他们决定找到那些连续两个季度销况来调整产品结构。他们决定找到那些连续两个季度销量
37、都在下降的产品，分析产品销售量下降原因，进而决量都在下降的产品，分析产品销售量下降原因，进而决定是否停止这些产品的进货和销售。试为定是否停止这些产品的进货和销售。试为Northwind Trader公司找到连续两个季度销量下降的产品，并以柱公司找到连续两个季度销量下降的产品，并以柱形图的形式显示这些产品最近三个季度的销量，如图形图的形式显示这些产品最近三个季度的销量，如图3-106所示。所示。第六节第六节分类汇总数据的应用（续）分类汇总数据的应用（续）第六节第六节分类汇总数据的应用（续）分类汇总数据的应用（续）n第一步，获得所需数据；第一步，获得所需数据；n第二步，增加新字段第二步，增加新字段
38、“订购季度订购季度”；n第三步，计算不同季度和各产品的总销量；第三步，计算不同季度和各产品的总销量；n第四步，利用模拟运算表，计算第四步，利用模拟运算表，计算97年第年第3季度、季度、97年第年第4季度和季度和98年第年第1季度各产品的销售总量；季度各产品的销售总量；n第五步，计算两个季度销量的下降数量、累计下降数量，第五步，计算两个季度销量的下降数量、累计下降数量，并按照从小到大的顺序排列；并按照从小到大的顺序排列；n第六步，找出连续两季度销量下降的产品以及其前第六步，找出连续两季度销量下降的产品以及其前3季度季度度、前度、前2季度、前季度、前1季度的销量；季度的销量；n第七步，绘制柱形图。
39、第七步，绘制柱形图。第六节第六节分类汇总数据的应用（续）分类汇总数据的应用（续）n【例例3-16】迅捷是一家便利店，销售日用品、饮料和食品迅捷是一家便利店，销售日用品、饮料和食品等产品。对于部分易变质的饮料和食品，因为保质期比较等产品。对于部分易变质的饮料和食品，因为保质期比较短，他们总是先销售先进货的产品。他们为每种产品制订短，他们总是先销售先进货的产品。他们为每种产品制订出了打折的开始时间（产品库存数据工作表中的打折提前出了打折的开始时间（产品库存数据工作表中的打折提前天数），不同时间对应的打折比例（即每天打折比例，例天数），不同时间对应的打折比例（即每天打折比例，例如产品如产品“康康原味
40、酸奶康康原味酸奶”在到期的前在到期的前3天给与天给与15%的折扣，的折扣，前前2天给与天给与30%的折扣，前的折扣，前1天给与天给与45%的折扣，当天给与的折扣，当天给与60%的折扣），如图的折扣），如图3-114所示。迅捷便利店希望利用产品所示。迅捷便利店希望利用产品库存数据和进货数据，如图库存数据和进货数据，如图3-114和图和图3-115所示。建立模型，所示。建立模型，发现需要打折的产品、打折比例。发现需要打折的产品、打折比例。第六节第六节分类汇总数据的应用（续）分类汇总数据的应用（续）产产品品库库存存数数据据进进货货原原始始数数据据第六节第六节分类汇总数据的应用（续）分类汇总数据的应用
41、（续）n第一步，在原有进货数据清单中增加新字段第一步，在原有进货数据清单中增加新字段“产品进货次数产品进货次数”和和“产品产品ID与订货次数与订货次数”；n第二步，计算某产品的累计进货次数；第二步，计算某产品的累计进货次数；n第三步，计算当前库存产品中倒数第第三步，计算当前库存产品中倒数第2次进货剩余的产品；次进货剩余的产品；n第四步，计算产品到保质期剩余天数；第四步，计算产品到保质期剩余天数；n第五步，找到该产品打折开始时间、计算打折比例、库存总第五步，找到该产品打折开始时间、计算打折比例、库存总数量、打折产品数量；数量、打折产品数量；n第六步，利用模拟运算表计算所有产品的打折开始时间、计第
42、六步，利用模拟运算表计算所有产品的打折开始时间、计算打折比例、库存总数量、打折产品数量；算打折比例、库存总数量、打折产品数量；n第七步，制作不同汇总方式的组合框；第七步，制作不同汇总方式的组合框；n第八步，准备绘图数据；第八步，准备绘图数据；n第九步，绘图。第九步，绘图。第七节第七节Excel中的多维数据分析中的多维数据分析OLAPn多维数据分析的定义多维数据分析的定义n通过关系数据库创建多维数据集通过关系数据库创建多维数据集 多维数据分析的定义多维数据分析的定义nOLAP定义定义n数据字段和维数据字段和维n确定数据字段确定数据字段n确定维确定维n指定源数据指定源数据多维数据分析的定义（续）多
43、维数据分析的定义（续）nOLAP定义定义n联机分析处理联机分析处理(OLAP，Online Analytical Processing)是是根据层次分类来预先计算汇总值的一种组织数据的方式，根据层次分类来预先计算汇总值的一种组织数据的方式，它符合用户检索和分析数据的习惯。它符合用户检索和分析数据的习惯。n按照按照OLAP 组织数据可以提高检索数据的速度。因为在组织数据可以提高检索数据的速度。因为在创建或更改报表时，创建或更改报表时，OLAP 服务器（而不是服务器（而不是 Microsoft Excel）会计算汇总值，并且只有汇总的数据（而非详会计算汇总值，并且只有汇总的数据（而非详细数据）送到
44、细数据）送到Excel中，减少了数据量，提高了中，减少了数据量，提高了Excel的的运算速度。运算速度。多维数据分析的定义（续）多维数据分析的定义（续）n数据字段和维数据字段和维nOLAP多维数据集中包含多维数据集中包含：n汇总值，被称为数据字段，即汇总字段汇总值，被称为数据字段，即汇总字段。n有关这些值的说明，即分类字段，有关这些值的说明，即分类字段，则被组织成则被组织成不同级别的明细数据，被称为维。不同级别的明细数据，被称为维。n有了数据字段和维，就可以很容易地在报表中显有了数据字段和维，就可以很容易地在报表中显示较高级别的汇总以及低级别的汇总。示较高级别的汇总以及低级别的汇总。多维数据分
45、析的定义（续）多维数据分析的定义（续）n确定数据字段确定数据字段n数据字段是用户希望在报表中出现的汇总字数据字段是用户希望在报表中出现的汇总字段段n销售额、销售量、库存量销售额、销售量、库存量n多维数据集可按四种方式汇总数据字段，即多维数据集可按四种方式汇总数据字段，即求总计、计数、最小和最大值求总计、计数、最小和最大值多维数据分析的定义（续）多维数据分析的定义（续）n确定维确定维n可以在分类层次上进行组织的说明字段构成了可以在分类层次上进行组织的说明字段构成了报表中的维，它包含数据某个方面的一组级别报表中的维，它包含数据某个方面的一组级别（如：地理位置）（如：地理位置）。多维数据分析的定义（
46、续）多维数据分析的定义（续）n指定源数据指定源数据n多维数据集中的所有字段都必须是数据字段或维。多维数据集中的所有字段都必须是数据字段或维。若要使查询或选定的源数据能更高效地运行，并且若要使查询或选定的源数据能更高效地运行，并且使采用这些数据的报表更快更新，则应该只包含那使采用这些数据的报表更快更新，则应该只包含那些必须在多维数据集中使用的字段。些必须在多维数据集中使用的字段。通过关系数据库创建多维数据集通过关系数据库创建多维数据集n利用利用Microsoft Query调用调用“OLAP多维数据集向导多维数据集向导”，创建多，创建多维数据集。维数据集。n【例例3-17】针对针对Northwi
47、nd Trader公司的销售数据，利用公司的销售数据，利用Microsoft Query创建多维数据集文件，并通过数据透视表浏创建多维数据集文件，并通过数据透视表浏览按照客户所在地理位置汇总的销售额。览按照客户所在地理位置汇总的销售额。n利用利用Excel调用调用“OLAP多维数据集向导多维数据集向导”，创建多维数据集。，创建多维数据集。n【例例3-18】为为Northwind Trader 公司创建脱机多维数据集文件，公司创建脱机多维数据集文件，并通过数据透视表显示销售额，该销售额需按照产品的类型并通过数据透视表显示销售额，该销售额需按照产品的类型维（包括类型和产品名称字段）、时间维、供应商
48、地理位置维（包括类型和产品名称字段）、时间维、供应商地理位置维（包括国家、地区、城市）来汇总。维（包括国家、地区、城市）来汇总。通过关系数据库创建多维数据集（续）通过关系数据库创建多维数据集（续）n【例例3-17】针对针对Northwind Trader公司的销售数据，利公司的销售数据，利用用Microsoft Query创建多维数据集文件，并通过数据透创建多维数据集文件，并通过数据透视表浏览按照客户所在地理位置汇总的销售额。视表浏览按照客户所在地理位置汇总的销售额。n第一步，利用第一步，利用Microsoft Query查询数据集中应包含的数查询数据集中应包含的数据；据；n第二步，创建第二步
49、，创建OLAP多维数据集；多维数据集；n第三步，利用数据透视表生成报表，浏览数据。第三步，利用数据透视表生成报表，浏览数据。通过关系数据库创建多维数据集（续）通过关系数据库创建多维数据集（续）通过关系数据库创建多维数据集（续）通过关系数据库创建多维数据集（续）求和求和 ：销售额销售额类别名称类别名称产品名称产品名称点心点心点心点心汇总汇总 调味品调味品谷类谷类/麦片麦片海鲜海鲜年年季度季度 月月饼干饼干蛋糕蛋糕199619964 4OctoberOctober24824897973453454124412424612461 58125812NovemberNovember73973973973
50、96290629025072507 10431043DecemberDecember1497149721942194 31433143199719974 4OctoberOctober698698518518 121612165688568830313031 68876887NovemberNovember3013012142145155153785378557875787 78827882DecemberDecember14831483285285 176817685824582452425242 59075907国家国家全部全部【例例3-18】为为Northwind Trader 公司创建