物流模拟Excel高级篇

第2章Excel高级篇Excel高级篇----收获价值目录1.数据表的汇总与分级2.数据透视表3.图形与图表的操作4.有效性高级应用下拉列表圈释无效数据5.假设分析(New!)6.宏及VBA简述7.窗体应用(New!)8.综合应用案例(New!)9.Excel插件(New!)Xlstate的应用ACSRTools数据表的汇总与分级分类汇总1．简单分类汇总（如图1-20所示）分类汇总计算均值可按如下步骤进行：（1）对需要分类汇总的关键字字段进行排序。（2）选定数据清单中的任一单元格。如图1-21所示。（3）单击“分类字段”框的下拉箭头，在弹出的下拉列表中选择所需字段作为分类汇总的依据，这里指定“组别”为分类字段。图1-20某药物代谢动力学研究数据图1-21“分类汇总”对话框（4）单击“汇总方式”框的下拉箭头，在弹出的下拉列表中有求和、计数等11个选项。（5）在“选定汇总项”列表框中，列出了数据清单中的所有字段（变量）名称，将所有需要汇总的字段前面的复选框选中。（6）根据需要，指定汇总结果显示的位置，选定相应的复选框。（7）单击“确定”按钮，获得如图1-22所示的结果。图1-22分类汇总结果2．删除分类汇总如果要删除分类汇总，可单击菜单中的“数据－分类汇总”命令，然后在分类汇总对话框中单击“全部删除”按钮。此时，数据清单恢复成原来的初始状态。3．多级分类汇总多级分类汇总的关键是正确的分类汇总次序，即先按主要关键字建立第一级分类汇总，再按次要关键字建立第二级分类汇总，依此类推。在分类汇总前必须按主要关键字、次要关键字等排序（如图1-24所示）。图1-24二级分类汇总结果返回本节使用数据透视表分析数据使用数据透视表分析数据数据透视表是Excel中最具技术性的复杂组件之一。数据透视表的本质是将表格数据库变为动态数据汇总报告。数据透视表的优势：人性化：方便的操作和界面灵活性：可以方便的得到任何类别的数据如何生成数据透视表下面我们以此数据来进行数据透视表的分析。为数据透视表选择合适的数据虽然Excel可以对任何数据生成数据透视表，但并不是所有的数据资料都可以从中受益。一般而言，数据资料应为数据库格式，包含下面两种类型的字段：数据（包括数字或文本）类别：对数据的描述数据的第一行要求为项目标题。当数据列不包含数字时数据透视表默认为以计数的方式使用数据；当数据列为数字时默认为使用求和方式。如何生成数据透视表Step1:选择工具栏中的：数据数据透视表和数据透视图。如何生成数据透视表Step2:一般情况按照默认的选项即可，单击下一步。上部分选项选择数据来自本Excel文件，或其它数据类型，或多个Excel数据文件。如何生成数据透视表Step3:弹出对话框，选择将要生成数据分析表的数据源。当活动单元格的位置在数据区域时，透视表会自动选择数据区域。如何生成数据透视表Step4:选择新建工作表，使数据透视表生成在一张新的工作表中，点击“完成”。选择“布局”和“选项”可以设置一些数据透视表的模式，但不是必需的，所以如果是新手入门就当作没有这些键好了，但是我们也分别解释一下。布局在“布局”对话框里，可以将透视表的各字段安排在不同的位置。但是这个步骤也可以在数据透视表建立后进行。选项数据透视表提供了丰富的“选项”。列总计：以列汇总；行总计：以行汇总；打开时刷新：打开文件时自动进行数据刷新；显示明细数据：可以显示每个数字对应的数据库中的数据；如何生成数据透视表Step5:生成新的一页工作表，并会弹出包含所有项目标题的对话框。如何生成数据透视表Step6:将项目标题拖到左侧表格的对应位置，一张数据透视表就完成了。如何读取数据透视表项目选项：选择不同组合下的数据。点击右键，对交叉表进行字段分析，如求和，计数，最大值等数据透视表的组成部分1.列字段。如除主详细特征；2.行字段。如time；3.数据区域。中间的数字；4.总数求和。如合计；5.元素。如个人；6.条件。如city;7.字段列表。移动在数据透视表中无法进行普通的操作，例如插入或删除一行或一列，也不能对数字进行硬性修改。如何移动数据透视表中的项目？将鼠标移到想要移动的项目，点右键，选择“顺序”，然后选择想要移动方向即可。注意只能移动项目，而不能仅仅移动数据。手工组合组合项目是数据透视表的一个有用特性。如何将选项进行组合？选择要组合的元素，点击右键，选择“组合显示明细数据”“组合”，出现新的项目名称“city2”，将生成的“数据组1”改为“重点城市”。这样就可以得到分“重点城市”和“其他城市”的数据。手工组合自动组合当被组合项为数字时，可以进行自动组合。例如日期可以自动选择按照月、季度、年等组合；收入，年龄等都可以按照自己指定的区间段进行组合；当进行手工组合时会生成新的元素，如对“收入”进行组合会生成“收入2”的元素；在进行自动组合时没有新的元素生成，组合数据替代了详细数据；因此建议在原数据库中复制一列数据。插入计算字段数据透视表有自我进行计算的功能，这种功能叫做“插入计算字段”。例如在数据透视表中有销售额和销量的数据，则可以通过计算来得到单价的数据。步骤：“数据透视表工具栏””公式””计算字段”在“名称”写入新建数据的名称；在“公式”内键入计算公式；确定。数据透视表的其他阅读详细数据对于数据透视表产生的数据可以查看详细的数据产生来源，具体的方式是双击此数。例如数据透视表计算出库中有3个样本符合某条件，那么通过双击此数可以得到具体是哪3个样本符合这个条件，并在新的工作表中显示。在双击前请保证“表格选项”中的“显示明细数据”已被选择。设置报告格式如果需要直接把数据透视表作为报告提交，那么一个正规的格式是必须的，请在“数据透视表工作栏”中选择“设置报告格式”，选择合适的个是显示数据透视表选择显示元素点击数据透视表中的任意列字段和行字段的右下方的小三角，弹出这个字段的所有元素，可以把你不需要的元素进行隐藏。例如您只对2005年的数据感兴趣，则点击数据透视表中年份的一栏，将其他年份去掉，则表中仅剩下2005年的数据。数据透视图除制作数据透视表外，我们还可以制作数据透视图.数据透视图兼有数据透视表和图表的功能。既可以随意选择数据组合，又可以随意改变图表类型。图形与图表的操作图形操作1．“绘图”工具栏图1-33“绘图”工具栏2．绘制图形图1-34自选图形－基本图形图1-35绘制的自选图形3．编辑图形图1-36添加图形图1-37“组合”命令图1-38默认图形叠放次序图1-39“叠放次序”命令图1-40调整叠放层次图1-41“阴影样式”选项图1-42“圆柱形”阴影效果图1-43“等腰三角形”三维效果图1-44“设置自选图形格式”对话框图1-45“艺术字库”对话框图1-46“编辑‘艺术字’文字”对话框图1-47艺术字效果返回本节1.5.2图表操作1．Excel图表类型简介（1）柱形、条形、圆柱、圆锥和锥图（如图1-48所示）。（2）折线图（如图1-49所示）。（3）面积图（如图1-50所示）。（4）饼图与圆环图（如图1-51所示）（5）XY散点图（如图1-52所示）。图1-48柱形图的7个子图表类型图1-49折线图的7个子图表类型图1-50面积图的6个子图表类型图1-51饼图的6个子图表类型图1-52散点图的5个子图表类型（6）雷达图（如图1-53所示）（7）曲面图（如图1-54所示）（8）气泡图。两个子图表类型：气泡图和三维气泡图（9）股价图（如图1-55所示）（10）自定义类型图1-53雷达图的3个子图表类型图1-54曲面图的4个子图表类型图1-55股价图子图表类型2．创建图表（1）确定制图的目的。（2）将统计资料输入到Excel中去（如图1-56所示）。（3）在想绘制图的数据中任选一个单元格，然后在“插入”菜单中选中“图表”命令。（4）决定图形。（5）确定要绘制图表的数据范围（如图1-57~1-59所示）（6）给图表命名。（7）图表位置的选中（如图1-60、1-61所示）。图1-56学生成绩数据图1-57“图表向导—4步骤之2”—“数据区域”选项卡图1-58“图表向导—4步骤之2”—“系列”选项卡图1-59“图表向导—4步骤之3”—“标题”选项卡图1-60“图表向导—4步骤之4－图表位置”对话框窗体应用应注意的方面仅当活动单元格处在数据透视表中的位置时，才能激活数据透视表。数据透视表有一个小缺点，当用户更改数据源时，他不能自动更新。但只需单击工具栏中“刷新”按钮或右键的“更新数据”，强制数据透视表使用最新的数据。当然你也可以在表格选项中选择每隔固定时间刷新数据。若在操作中将“字段列表”关掉，有两种方法可以再显示：点右键选择“数据透视表向导”在“数据透视表工作栏”种选择“显示字段列表”实验实验2,3数据表的汇总分级,透视表的应用实验4物流企业财务报表分析实验5库存分析有效性的高级应用创建下拉列表实现步骤打开“数据””有效性”；在“允许”中选择序列；选择序列来源或者手工输入列表(用“,”隔开)根据情况选择“忽略空值”，选择“提供下拉箭头”“确定”创建下拉列表可以增加数据录入的准确性和便捷性。圈释无效数据即使数据有效性起作用，先前在表格中已经存在的无效数据也不会去掉或被标注。“圈释无效数据”可以将表格中无效的数据标记出来，具体的步骤为：选择已经进行数据有效性处理的数据区域；选择“工具””公式审核””显示公式审核工具栏”；在工具栏内选择“圈释无效数据”注意：圈释无效数据仅是暂时的表现形式，当保存文件时，圈释标志即消失。窗体应用VBA与窗体EXCEL提供了一个在EXCEL工作簿中应用控件的方式。调用窗体工具栏。能够在工作簿中添加合适的控件。微调项按钮控件列表框组合框综合应用案例(New!)Excel插件(New!)Xlstate的应用ACSRTools使用xlstat软件我们经常会遇到下面的两种分析：Graveyard模型位于回归线左上方且距离较远的为强势品牌。位于回归线附近的为常规品牌。位于回归线下方且距离较远的为衰退品牌。位于回归线右上方且距离较远的为潜力品牌。各购物场所的提示提及与为提示提及图华联百盛百安居红星美凯龙超市发金海马金盛家居家乐福香江家居吉盛伟邦春申江美廉美美克美家好美家-5051015202530020406080100提示提及未提示提及品牌优劣势分析图OverallSWOTanalysisfactorychannelmanagementsalessupportTractor'safterserviceCombine'saftersaleservice0.20.250.30.350.40.450.53.63.73.83.944.14.24.3SATISFACTIONIMPACT急需改进区competitive优势区Advantage次要改进区inconsequential维持区maintain应用图形的三维图各电子品牌分析图SONYDELLSANSUNGPANOSONICLENOVOTOSHIBAZIGUANG0102030405060010203040506070创新的有亲和力使用xlstat软件Xlstat是著名的Excel的插件，它能够：使Excel的部分操作更加简洁。增加了Excel无法完成的分功能。本章介绍其中的两个分析命令。其余的命令因在Spss或Excel本身中也可实现，所以不进行讲解。绘制6D图（6Dplot）对应分析xlstat的安装与需要注意的几点xlstat的安装也可以如前面所述的“加载宏”方式进行。需要注意的方面：必须将Excel的宏的安全性调为中，具体操作是：“工具”---“宏”---“安全性”---“中”----“确定”。不能使用最高版本6.0，以避免失效后无法安装低的版本。如果操作过程中出现下面的对话框，选择“启用宏”。xlsta