Excel高级—数据处理及分析

1Excel2013高级教程数据统计与处理分析Excel是Office软件中的核心成员，是最优秀的电子表格软件之一，具有强大的数据处理和数据分析能力，是个人及办公事务中进行表格处理和数据分析的理想工具之一。如何利用Excel的函数、图表、高级分析工具、VBA程序等功能进行数据分析是本次学习的重点。内容提要2利用函数进行数据分析P291数据处理与分析基础P44利用透视表（图）进行数据分析P723利用图表进行数据分析P425构建动态数据分析报表P966宏与VBA在数据分析中的应用P1147Excel的数据分析工具简介P1461、数据处理与分析基础学习目标1认识EXCEL的功能与界面2学会利用数据条件格式进行数据处理3学会利用排序、筛选、分类汇总功能进行数据分析EXCEL在数据分析中的应用41、数据处理与分析基础1.1EXCEL的功能与界面1.2数据的输入、编辑与运算（案例：销售产品基本信息表、销售记录汇总表）1.3利用数据条件格式进行数据处理1.4利用排序、筛选、分类汇总功能进行数据分析1.1Excel的功能与界面1.1.1EXCEL新增的主要功能（1）取消了菜单方式，采用了面向结果的用户界面，易于找到。（2）更强大的数据管理能力和安全性。（如更多的行和列1048576×16384，1600万种颜色）（3）更强大的表功能，提供了全新的数据引用方式，称为结构化引用，可以方便地构造动态数据报表。（4）其他方面：提供了大量预定义主题和样式丰富的条件格式自动调整编辑栏函数记忆式输入1.1Excel的功能与界面1.1.1EXCEL新增的主要功能改进的筛选和排序功能（可按日期和颜色排序）图表外观更美观、更专业、布局和样式更多易于使用的数据透视表、数据透视图快速连接外部数据源1.1Excel的功能与界面1.1.2EXCEL的用户界面整个界面由功能区和工作表区组成；功能区有：OFFICE按钮、选项卡、组、快速访问工具栏、标题栏、状态栏。OFFICE按钮：相当于早期的“文件”菜单；选项卡：面向任务，包括功能控件。“开始”有日常操作功能，“页面布局”与打印有关·······（有书也称主菜单）组：每个组都与特定任务相关；（有书也称工具栏）快速访问工具栏：独立显示，默认有“保存”、“撤销”和“恢复“三按钮，可以自定义；标题栏：显示工作薄名称。状态栏：宏录制按钮、查看方式、缩放工具。1.2数据的输入、编辑与运算目标：建立某企业产品销售数据统计报表（两个工作表）1.2.1建立产品基本信息表任务1：在“产品营销数据处理与分析实例”工作薄建立形如下面的工作表，名称为“产品基本信息”企业所有商品基本信息列表知识点：表格内容了解、新建工作薄、新建或改名工作表；格式化工作表。包括对齐方式、设置单元格格式（下划线、列宽、边框、填充颜色）产品编号系列产品名称进货单价销售单价AP11001观音饼观音饼（花生）6.512.8AP11002观音饼观音饼（桂花）6.512.81.2数据的输入、编辑与运算1.2.2建立“销售记录汇总”表任务2：建立表框架（见实例）；区分数据来源（原始的、引用的、计算的）；输入公式（从信息表引用的、本表计算的）；数据复制（客户名称、发货日期、订单号、数量）。思考：什么是公式？其价值何在？公式是由“=”号或“+”号开头，由常数、函数、单元格引用以及运算符组成的式子。其价值不仅是计算，重要的是构建计算模型。1.2数据的输入、编辑与运算1.2.2建立“销售记录汇总”表思考：公式可分为几大类？可分三大类：普通公式：如=A1+B1,=sum(a1:b5)数组公式：如{=average(B1:B10-A1:A10)}（使用数组公式可减少存储空间，提高工作效率，详见“综合实例”工作薄中相关内容）命名公式：如：Data名称表示a1:a10=sum(data)1.2数据的输入、编辑与运算1.2.2建立“销售记录汇总”表知识点：跨表引用。根据“产品编号”自动返回产品基本信息表“系列”等字段内容（VLOOKUP函数）；如E3中公式：=VLOOKUP($D2,产品基本信息表!$B$3:$F$38,2,FALSE)公式计算字段“系列”、“产品名称”、“销售单价”、“成本单价”要找的值查找区域第2列精确比较相关知识：Vlookup函数的使用格式：VLOOKUP（查找的值，查找区域，返回的列号，选项）功能：在表格或数组的首列查找值，并返回表格或数组中其它列的值。选项：FALSE：精确匹配，若找不到返回#N/ATRUE或省略：近似匹配，若找不到返回一个小于要找参数的最大值。（EXCEL中的函数帮助可能有误，请在编辑栏输入公式时查看选项含义）VLOOKUP的稍高级应用见综合实例中相关练习1.2数据的输入、编辑与运算本任务中相关公式说明：销售额=数量*销售单价总成本=数量*成本单价毛利=销售额-总成本销售数据可从“销售原始”工作表复制而得。1.3利用数据条件格式进行数据处理概述：条件格式功能，指的是当单元格中的数据满足某种条件时就设置某种格式，否则不予设置。利用此功能，可将单元格中的满足条件的数据进行特殊标记，以便直观地查看。方法：选中数据区—开始—样式—条件格式任务3：对“销售记录汇总”表进行如下操作：（1）把“销售单价”以图标集形式显示（2）把“销售额”以数据条形式显示（3）把“毛利”低于平均值的数据设置为黄色。1.3利用数据条件格式进行数据处理知识点：条件格式有以下几种：（1）突出显示单元格规则：对选定区域内满足条件的单元格突出显示，默认的规则是用某种色彩填充单元格背景。条件包括：大于、小于、等于、文本包含等。（2）项目选取规则：对选定区域内小于或大于某个阈值的单元格实施条件格式。条件包括：值最大的10项、高于平均值等。（3）数据条。以色彩条形图直观地表示单元格数据。数据条长度代表数值的大小。1.3利用数据条件格式进行数据处理知识点：（4）色阶。用颜色的深浅表示数据的分布和变化，包括双色阶和三色阶。双色阶使用两种颜色的深浅程度比较某个区域的单元格，颜色的深浅表示值的高低。如在绿色和红色的双色刻度中，可指定越高越绿，越低越红。三色阶用三种颜色的深浅表示值的高、中、低。（5）图标集。按阈值把数据分成3-5个类别，每个图标代表一个值的范围，根据数据的大小比例设置图标的形状或颜色。1.4利用排序、筛选、分类汇总功能进行数据分析1.4.1排序概述：排序是对数据进行重新组织安排的一种方式，排序有助于直观地显示、组织和查找所需数据。任务4：对销售记录汇总表操作（1）为查看各“系列”产品的销售情况，对“系列”排序（升序或降序）（2）查看不同“发货日期”各“系列”产品的销售情况—按“发货日期”和“系列”两个字段排序1.4利用排序、筛选、分类汇总功能进行数据分析知识点：按一列排序：光标放于某列中，单击“升序”/“降序”按钮，可升序/降序排序；按多字段排序：光标放于表中任意单元格，单击“排序”按钮，可按多字段排序。注意：排序内容是否包含标题栏的选定；选择某列排序时的“排序提醒”；当有复杂表头时的选择区域再排序；自定义序列排序（如按职务高低排序，见“综合实例”工作薄）；1.4利用排序、筛选、分类汇总功能进行数据分析知识点：注意：EXCEL对排序字段不再局限于3个；不论升降序，空行总在排在最后；EXCEL可以按单元格颜色排序。思考：排序之后如何回到排序前的状态。（必要时引入辅助字段）姓名性别年龄工资辅助张三男2534561李四男2332152王五女2289031.4利用排序、筛选、分类汇总功能进行数据分析1.4.2自动筛选概述：筛选就是只把满足条件的数据行显示出来，而把不关注的数据隐藏。筛选有自动筛选和高级筛选两种。自动筛选易于使用，高级筛选条件可以更复杂。任务5：对销售记录汇总表操作（1）查看特定系列产品（如“观音酥”）的销售情况—（自动）筛选（2）查看特定用户（如“好又多”）、特定系列产品（如“旅游产品”）的销售情况—组合自动筛选1.4利用排序、筛选、分类汇总功能进行数据分析1.4.2自动筛选知识点：欲选择某列中一个项止，先取消本列中的“全选”复选框，然后再单选；注意状态栏提示（筛选出的个数）、列标右侧的图标显示；筛选是累加的，后一次筛选在前一次基础上进行；解除一列筛选：按“全选”；全部解除：按“筛选”按钮。EXCEL在数据分析中的应用221.4利用排序、筛选、分类汇总功能进行数据分析1.4.3高级筛选概述：可实现多字段间的“或”条件筛选，并能将结果复制到其他区域。任务6：查看一段时间内（如2007年8月上旬）特定用户（如“好又多”）的销售情况此任务可用高级筛选或自动筛选完成。EXCEL在数据分析中的应用231.4利用排序、筛选、分类汇总功能进行数据分析1.4.3高级筛选知识点：1、条件的写法（日期条件中用的是日期的数值）2、取消高级筛选单击“清除”思考：要筛选出销售数量不低于100，或者毛利不小于2000元的数据怎么做。EXCEL在数据分析中的应用24数量毛利=100=2000发货日期发货日期客户名称=39295=39304好又多1.4利用排序、筛选、分类汇总功能进行数据分析1.4.4分类汇总概述：分类汇总能够对工作表数据按不同的类别进行汇总，并通过分级显示方式展现数据。任务7：对销售记录汇总表操作按“系列”统计销售额、总成本、毛利等数据分类之和知识点：1、先按分类字段“系列”排序2、数据|分级显示|分类汇总(分类字段、汇总方式、汇总项)3、删除分类汇总：分类汇总|全部删除4、分级显示的使用与取消EXCEL在数据分析中的应用251.4利用排序、筛选、分类汇总功能进行数据分析任务8统计本期哪位客户购货数量最多知识点：1、先按“客户名称”字段排序2、数据|分级显示|分类汇总3、对分类汇总后的结果进行排序（按数量排序）EXCEL在数据分析中的应用261.4利用排序、筛选、分类汇总功能进行数据分析1.4.5高级分类汇总概述：高级分类汇总就是按同一类别进行不同字段或不同汇总方式的汇总。任务9：对销售记录汇总表操作按“客户名称”统计汇总销售额总和、总成本总和、毛利平均值知识点：1、先按分类字段“客户名称”排序2、数据|分级显示|分类汇总|选汇总项目和方式3、数据|分级显示|分类汇总|选另外的汇总项目和方式注意：不选择“替换当前分类汇总”EXCEL在数据分析中的应用271.4利用排序、筛选、分类汇总功能进行数据分析1.4.6嵌套分类汇总概述：嵌套分类汇总就是在一种分类汇总基础上再进行不同类别的分类汇总。任务10：对销售记录汇总表操作按“客户名称”统计汇总销售额总和、总成本总和、毛利总和，以及某客户不同“系列”的销售数量之和。知识点：1、先按多个分类字段排序（客户名称+系列）2、数据|分级显示|分类汇总|选分类字段“客户名称”，汇总方式求和，汇总项为销售额、总成本、毛利。3、数据|分级显示|分类汇总|选分类字段“系列”，选汇总方式求和，汇总项目为数量。注意：不选择“替换当前分类汇总”EXCEL在数据分析中的应用282利用函数进行数据分析学习目标1掌握函数的输入技巧和获取帮助的手段2掌握SUMIF、SUMIFS、SUMPRODUCT等函数的应用EXCEL在数据分析中的应用292利用函数进行数据分析2.1函数概述2.2SUMIF函数的应用2.3SUMIFS函数的应用2.4使用SUMPRODUCT函数分析数据EXCEL在数据分析中的应用302利用函数进行数据分析2.1函数概述函数就是预定义的公式。它使用一些参数进行运算并产生返回结果。EXCEL中有350多个内置函数，分数学与三角函数、财务函数、统计函数、工程函数等11大类，用户还可使用VBA自定义函数。函数可分为有参数函数和无参数函数两大类。在公式和自定义宏中可以调用函数。函数可以嵌套调用。如：=IF(AVERAGE(A2:A5)50,SUM(B2:B5),0)=IF(A2=10,“科技处”,IF(A2=20,“财务处”,“人事处”))EXCEL在数据分析中的应用31条件取