EXCEL-数据分类汇总分析.ppt

Excel预测与决策分析第三章数据分类汇总分析基础篇数据分类汇总分析的意义和作用数据分类汇总的四种方法数据透视表D函数和模拟运算表提高篇利用DSUM函数和控件控制分类汇总结果分类汇总数据的应用Excel中的多维数据分析——OLAP内容简介：第一节数据分类汇总分析的意义和作用获得销售额分类统计值ABC公司1995年不同省份不同类别商品的销售额总计值01000020000300004000050000艺术品自行车服装食品儿童用品体育用品瓷器类别销售额江西安徽江苏广东福建浙江山东第一节数据分类汇总分析的意义和作用（续）获得各类销售额排行榜Northwind公司前十大客户销售额03000600090001200015000正人资源五洲信托嘉业艾德高科技幸义房屋迈多贸易百达电子悦海上河工业池春建设客户公司名称销售额Northwind公司前十大产品销售额03000600090001200015000猪肉猪肉干鸭肉绿茶白米苏澳奶酪运动饮料山渣片墨鱼牛肉干产品名称销售额第一节数据分类汇总分析的意义和作用（续）获得各类销售额的时间序列Northwind公司不同产品的销售额时间序列y=39.401x-39.299R2=0.4649020040060080010001200140016001996年7月1996年9月1996年11月1997年1月1997年3月1997年5月1997年7月1997年9月1997年11月1998年1月1998年3月时间销售额花生第一节数据分类汇总分析的意义和作用（续）获得各经济量之间的相关性Northwind公司分段平均运货费与平均销售金额的依赖关系y=3.0652x+292.37R2=0.846902004006008001000020406080100120140160180200平均运货费平均销售额第一节数据分类汇总分析的意义和作用（续）获得各种产品需求量的频率分布Northwind公司1997年白米月销量分布图012345203550658095110125140155170185200销量分组销售发生次数第二节数据分类汇总的四种方法数据查询的统计值功能数据清单功能数据透视表D函数与模拟运算表第二节数据分类汇总的四种方法汇总字段：待汇总的变量或字段。销售额、销售数量、工作时间等。分类字段：对数据进行汇总时，以某个变量或字段的不同值为参考来对汇总字段进行汇总。参考不同产品类别(分类字段)汇总销售额（汇总字段）参考不同的生产人员姓名（分类字段）汇总工作时间（汇总字段）第二节数据分类汇总的四种方法（续）ABC公司1995各省份、各类别商品净销售额总计值（图3-7）类别安徽广东江苏江西山东儿童用品3968652420444025524367服装1625547196食品3285547228561640419269体育用品1085010805113139703869艺术品42009289341194393224990自行车12522746421120383247281995年ABC公司各省各类别商品的销售额一、数据查询的统计值功能从Excel中启动MicrosoftQuery，利用Query的统计值功能，将数据汇总后返回Excel【例3-1】ABC公司销售数据.dbf中保存着公司从1994年到1997年三年的销售数据，利用Query软件的统计值功能汇总该公司的销售数据，并按照图3-7的形式，汇总出1995年不同省份、不同类别商品的净销售额。一、数据查询的统计值功能（续）123456789101112131415161718ABC省份类别总和净销售额安徽儿童用品39686安徽服装16255安徽食品32855安徽体育用品10850安徽艺术品42009安徽自行车12522广东儿童用品524广东食品472广东体育用品1080广东艺术品2893广东自行车746江苏儿童用品2044江苏食品2856江苏体育用品5113江苏艺术品4119江苏自行车4211江西儿童用品40255二、Excel数据清单功能数据清单数据清单是包含相关数据的一系列工作表数据行。数据清单的首行为字段名，首行下的各行是各个记录，数据清单中不能出现空行。数据清单的基本功能排序筛选分类总计二、Excel数据清单功能（续）利用Excel数据清单处理功能进行分类汇总的一般步骤：获得数据清单；设定筛选条件；筛选数据，将筛选结果复制到工作表其他位置；对经过筛选的数据按分类字段进行排序；对排序后的数据进行分类汇总。二、Excel数据清单功能（续）【例3-2】利用Excel的数据清单功能，将ABC公司的销售数据按照图3-7的形式，汇总出1995年不同省份、不同类别商品的净销售额总计值。将上述汇总表选中第3级汇总，然后选中所需数据区域，单击“选定可见单元格”（工具-自定义-编辑），再单击“复制”，将光标移到空白数据区域，单击粘贴，得到如图数据。根据右图数据再加以调整，便可得到如图3-7的数据。第三节数据透视表数据透视表的基本功能数据透视表结构的灵活性数据透视图的灵活性利用数据透视表生成时间序列统计不同规模销量的发生次数以及概率分布计算占同列数据总和的百分比一、数据透视表的基本功能数据透视表分类汇总的两种方法先将数据导入Excel成为数据清单，利用数据透视表汇总对数据清单进行汇总利用数据透视表直接从数据库中查询、并汇总数据一、数据透视表的基本功能（续）【例3-3】利用Excel获取外部数据功能，获取ABC公司1995年的销售数据，使用Excel数据透视表功能制作如图3-7所示的分类汇总表。提示：启动excel-数据－数据透视表和数据透视图－导入外部数据－新建数据库查询，在query获得汇总数据后返回excel，单击数据透视图的“图表向导”，生成数据透视图。一、数据透视表的基本功能（续）一、数据透视表的基本功能（续）05000100001500020000250003000035000400004500050000儿童用品服装食品体育用品艺术品自行车请将页字段拖至此处求和项:净销售额类别安徽广东江苏江西山东省份【例3-4】直接利用Excel数据透视表功能从数据库中查询数据，并根据查询结果汇总，制作出如图3-7所示的分类汇总表。提示：启动excel-数据－数据透视表和数据透视图－外部数据源二、数据透视表结构的灵活性改变数据透视表的行列结构利用页域对数据进行分页汇总新增、删除和修改行域、列域或页域字段（分类字段）新增、删除和修改汇总字段改变汇总字段的汇总方式同一个字段既作分类字段又作汇总字段二、数据透视表结构的灵活性（续）改变分类字段值的位置对分类字段的值加以合并隐藏行域、列域的字段值展开汇总值，显示详细数据三、数据透视图的灵活性变换分类字段的位置将行、列分类字段换成页分类字段新增、删除分类字段新增、删除汇总字段改变被汇总方式显示分类字段的部分值四、利用数据透视表生成时间序列时间序列:按照一定时间间隔汇总的数据序列【例3-5】利用数据透视表，对NorthwindTrader公司的销售数据按月汇总种各产品销售额。产品名称(全部)求和项:销售额年订购日期汇总1996年7月27861.898月25485.279月26381.4010月37515.7211月45600.0412月45239.631997年1月61258.072月38483.63例3-5操作方法数据－进行数据查询-query，获得Northwind公司的订购日期，产品名称，销售额(订单明细.单价*数量*(1-折扣))光标停留在目标数据的任意单元格，数据－数据透视表和数据透视图－MicrosoftExcel数据列表或数据库－数据透视表，next，新建工作表，next。订购日期拖至行域，销售额拖至列域，产品名称拖至页域。右击目标数据任意单元格，组及显示明细数据－组合，起始于1996-7-1，终止于1998-5-31，依据为先月，后年。五、统计不同规模销量的发生次数及频率分布【例3-6】利用数据透视表，对Northwind公司的销售数据按照不同产品、不同时间段，统计不同规模销售数量的发生次数，并计算其频率分布。产品名称(全部)年(全部)订购日期(全部)计数项:数量数量汇总1-1028.28%11-2029.49%21-3019.19%31-409.92%41-505.75%51-603.34%61-701.76%71-800.88%81-900.28%91-1000.51%101-1100.14%111-1200.37%121-1300.09%例3-6操作方法利用外部数据获得Northwind公司产品名称，订购日期，数量。选定数据清单任意单元格，数据-数据透视表和数据透视图，默认选项next。订购日期拖至行域，产品名称拖至页域，数量拖至数据域。双击“求和项：数量”，汇总方式选“计数”-确定。按例3-5方法对年月进行分组，然后将“年”和“订购日期”拖到页域，从数据透视表字段列表中将“数量”拖至行域，在数量列任意单元格右键，组及显示明细数据-组合，起始于1，终止于130，确定。右击“数量”数据任意单元格，表格选项，去掉行、列总计。D6单元格输入“销量分组”，E6输入“频率”，D7输入“=A7”，E7输入“＝B7/SUM($B$7:$B$19)”，将E7数据格式设为百分数，小数点保留一位，并将D7和E7复制到D19和E19单元格。绘制频率分布图：选中D6到E19，单击图表向导，选柱状图，next，next。五、统计不同规模销量的发生次数及频率分布Northwind公司不同规模销量的频率分布0%5%10%15%20%25%30%35%1-1011-2021-3031-4041-5051-6061-7071-8081-9091-100101-110111-120121-130产品名称(全部)年(全部)订购日期(全部)计数项:数量数量汇总在此处放置系列字段第四节D函数和模拟运算表D函数模拟运算表生成时间序列统计不同规模销量的发生次数以及概率分布一、D函数D函数：对数据清单或数据库中的数据进行分析有12个数据库内建函数。常用的D函数包括：DSUM()DAVERAGE()DCOUNT()DCOUNTA()一、D函数（续）D函数名称(数据清单,统计字段,条件范围)统计满足条件记录制定字段的总计值。数据清单：包含相关数据的工作表中的一系列数据行统计字段：被汇总字段。可以使用字段名、字段名所在的单元格、或者字段在数据清单中所在列的序号。条件范围：规定函数的匹配条件的一个工作表区域。D函数只对满足该条件范围所规定条件的记录进行汇总。一、D函数（续）【例3-8】利用ABC公司的销售数据，应用DSUM()函数，生成一个与图3-7相同的数据分类汇总表，列出ABC公司1995年不同省份不同类别净销售额总计值。例3-8操作方法1、获得数据清单。日期，省份，类别，净销售额，毛销售额。2、命名一空白工作薄为DSUM()函数用法，在单元格B2:E2输入：日期、日期、省份、类别。在B3:E3输入：=95/1/1、=95/12/31、安徽、儿童用品。在B5输入：净销售额的总计值，D5输入：=DSUM(数据清单,被汇总字段,条件范围)，数据清单为abc公司销售数据，被汇总字段为净销售额，条件范围为B2:E3。更方便方法：适用EXCEL的区域名。在数据清单选中A1:E3300，在区域名起新名字：原始数据。这时，DSUM()函数可以写成DSUM(原始数据,净销售额,B2:E3)。根据上面原理构造图3-7表。一、D函数（续）12345678910111213141516171819ABCDEFGHIJKLMN日期日期省份类别安徽广东江苏江西山东=95/1/1=95/12/31安徽儿童用品儿童用品3968652420444025524367服装16255