Excel2013高级教程-数据处理及分析

1Excel2013高级教程数据统计与处理分析Excel是Office软件中的核心成员，是最优秀的电子表格软件之一，具有强大的数据处理和数据分析能力，是个人及办公事务中进行表格处理和数据分析的理想工具之一。如何利用Excel的函数、图表、高级分析工具、VBA程序等功能进行数据分析是本次学习的重点。内容提要2利用函数进行数据分析P291数据处理与分析基础P44利用透视表（图）进行数据分析P723利用图表进行数据分析P425构建动态数据分析报表P966宏与VBA在数据分析中的应用P1147Excel的数据分析工具简介P146成都家教，成都家教网、数据处理与分析基础41、数据处理与分析基础1.1Excel的功能与界面1.1.1Excel新增的主要功能（1）取消了菜单方式，采用了面向结果的用户界面，易于找到。（2）更强大的数据管理能力和安全性。（如更多的行和列1048576×16384，1600万种颜色）（3）更强大的表功能，提供了全新的数据引用方式，称为结构化引用，可以方便地构造动态数据报表。（4）其他方面：提供了大量预定义主题和样式丰富的条件格式自动调整编辑栏函数记忆式输入成都家教，成都家教网数据的输入、编辑与运算目标：建立某企业产品销售数据统计报表（两个工作表）1.2.1建立产品基本信息表任务1：在“产品营销数据处理与分析实例”工作薄建立形如下面的工作表，名称为“产品基本信息”企业所有商品基本信息列表知识点：表格内容了解、新建工作薄、新建或改名工作表；格式化工作表。包括对齐方式、设置单元格格式（下划线、列宽、边框、填充颜色）产品编号系列产品名称进货单价销售单价AP11001观音饼观音饼（花生）6.512.8AP11002观音饼观音饼（桂花）6.512.8成都家教，成都家教网数据的输入、编辑与运算公式是由“=”号或“+”号开头，由常数、函数、单元格引用以及运算符组成的式子。其价值不仅是计算，重要的是构建计算模型。1.2数据的输入、编辑与运算1.2.2建立“销售记录汇总”表思考：公式可分为几大类？可分三大类：普通公式：如=A1+B1,=sum(a1:b5)数组公式：如{=average(B1:B10-A1:A10)}（使用数组公式可减少存储空间，提高工作效率，详见“综合实例”工作薄中相关内容）命名公式：如：Data名称表示a1:a10=sum(data)成都家教，成都家教网数据的输入、编辑与运算要找的值查找区域第2列精确比较相关知识：Vlookup函数的使用格式：Vlookup（查找的值，查找区域，返回的列号，选项）功能：在表格或数组的首列查找值，并返回表格或数组中其它列的值。选项：FALSE：精确匹配，若找不到返回#N/ATRUE或省略：近似匹配，若找不到返回一个小于要找参数的最大值。（Excel中的函数帮助可能有误，请在编辑栏输入公式时查看选项含义）VLOOKUP的稍高级应用见综合实例中相关练习成都家教，成都家教网数据的输入、编辑与运算1.3利用数据条件格式进行数据处理1.3利用数据条件格式进行数据处理知识点：条件格式有以下几种：（1）突出显示单元格规则：对选定区域内满足条件的单元格突出显示，默认的规则是用某种色彩填充单元格背景。条件包括：大于、小于、等于、文本包含等。（2）项目选取规则：对选定区域内小于或大于某个阈值的单元格实施条件格式。条件包括：值最大的10项、高于平均值等。（3）数据条。以色彩条形图直观地表示单元格数据。数据条长度代表数值的大小。成都家教，成都家教网利用排序、筛选、分类汇总功能进行数据分析1.4利用排序、筛选、分类汇总功能进行数据分析1.4利用排序、筛选、分类汇总功能进行数据分析知识点：注意：Excel对排序字段不再局限于3个；不论升降序，空行总在排在最后；Excel可以按单元格颜色排序。思考：排序之后如何回到排序前的状态。（必要时引入辅助字段）姓名性别年龄工资辅助张三男2534561李四男2332152王五女228903成都家教，成都家教网利用排序、筛选、分类汇总功能进行数据分析1.4利用排序、筛选、分类汇总功能进行数据分析221.4利用排序、筛选、分类汇总功能进行数据分析231.4利用排序、筛选、分类汇总功能进行数据分析1.4.3高级筛选知识点：1、条件的写法（日期条件中用的是日期的数值）2、取消高级筛选单击“清除”思考：要筛选出销售数量不低于100，或者毛利不小于2000元的数据怎么做。24数量毛利=100=2000发货日期发货日期客户名称=39295=39304好又多成都家教，成都家教网利用排序、筛选、分类汇总功能进行数据分析251.4利用排序、筛选、分类汇总功能进行数据分析261.4利用排序、筛选、分类汇总功能进行数据分析271.4利用排序、筛选、分类汇总功能进行数据分析282利用函数进行数据分析292利用函数进行数据分析302利用函数进行数据分析31条件取真时取假时2利用函数进行数据分析32客户名称数量销售额总成本毛利毛利排名相关知识：Sumif函数SUMIF（条件判断区域，条件，求和区域）根据指定条件对若干单元格求和。条件可以是数字、表达式或文本，如：“北京”、“=50”等，要使用引号。也可以是单元格引用。案例中C5处（数量）的公式：=SUMIF(销售记录汇总!$A$2:$A$107,$B5,销售记录汇总!$G$2:$G$107)复制到右侧单元格，并修改最后一项中的列号，依次为IKL列。（sumif其他例子见“综合实例”工作薄）33求和区域条件区域条件值成都家教，成都家教网相关知识：RANK函数的使用342利用函数进行数据分析35客户名称数量销售额总成本毛利毛利排名求和区域条件区域条件值相关知识：sumifs函数的使用格式：sumifs（求和区域，条件范围1，条件1，条件范围2…）功能：对某一区域内满足多重条件的单元格求和，最多127个条件。条件可以是“18”,”学生”，B4等形式。SUMIFS详细使用见“综合实例”工作薄中的“sumifs用法举例”。类似的：Excel也提供了Averageif,Averageifs,countif等函数。36成都家教，成都家教网利用函数进行数据分析任务4：在“函数应用”工作表中，统计各客户本期购买各系列产品的销售额。使用SUMPRODUCT函数。知识点：（1）？处公式为：=SUMPRODUCT((销售记录汇总!$A$2:$A$107=$B41)*(销售记录汇总!$E$2:$E$107=C$40),销售记录汇总!$I$2:$I$107)（2）SUMPRODUCT详细使用见“综合实例”工作薄中的“sumproduct用法举例”。38观音饼观音酥金莎旅游产品蒙特莎珍珠王便民超市？好又多成都家教，成都家教网相关知识：sumproduct函数的使用39C3中公式=sumproduct(a1:a3,b1:b3)结果为14ABC112233142利用函数进行数据分析40观音饼观音酥金莎旅游产品蒙特莎珍珠王便民超市？好又多求和区域条件区域1条件值1条件值2条件区域22利用函数进行数据分析413利用图表进行数据分析423利用图表进行数据分析433.1图表概述443.1图表概述453.1图表概述463.2图表的建立473.2图表的建立483.2图表的建立归纳：建立图表的步骤（1）首先选择数据源。要选定图表数据所在的单元格，如希望数据的行、列标志也显示在图表中，则选定区还应包括行/列标题。（2）应用预定义的图表布局和样式设置图表。方法：图表工具—布局/样式。（3）创建图表工作表。选中图表，设计—位置—移动图表。（可将嵌入式图表变成图表工作表，并可移回）49成都家教，成都家教网月份城乡居民存款额（万元）一月二月三月四月62966448656066043.3复杂图表的建立在折线图上增加趋势线——效果图52成都家教，成都家教网复杂图表的建立组合图表（柱形-折线）图的制作——效果图55成都家教，成都家教网号总销售面积（m2）131200925001000202030502200008000067000750008000056000销售均价（元/m2）850010000930089009100940087008500830084003.3复杂图表的建立双轴图表的制作——效果图58成都家教，成都家教网复杂图表的建立复合条饼图的制作——效果图61成都家教，成都家教网某月各地区个人储蓄业务发展分析（定期存款）单位：万元地区余额本月净增额累计净增额本月增长率累计增长率北京1283648616.8191768.5-0.1312.292天津579627.36302.1717780.779.891河北271006.41734.638310.30.44811.522山西365395.8490.527660.5-0.2595.733内蒙233582.3501.532508.70.02811.319辽宁969297884.8153092.80.06313.1323.4动态图表的建立利用CHOOSE函数和组合框建立动态图表—效果图65成都家教，成都家教网动态图表的建立知识点：操作步骤（1）在原数据区外建立动态数据区域，作为数据源（2）复制标题行到动态数据区域（3）在“地区”下B15输入公式：=INDIRECT(ADDRESS(CELL(row),COLUMN(B4)))向右方各列复制（4）以动态数据区域建立饼图，单击任一地区名称，按F9键，即显示指定地区的饼图。70成都家教，成都家教网利用数据透视表（图）进行数据分析724利用数据透视表（图）进行数据分析734.1数据透视表简介744.