Excel进阶培训数据分析篇

Excel高级培训：数据分析信息技术部办公软件技能培训系列2本课程您将学到什么？»这是一个面向进阶技巧的培训»掌握一些必要的excel进阶函数»理解并掌握数据立方体的概念»通过Excel的数据透视分析的练习，熟悉数据立方体的一般形式»提高数据分析工作的技能»做好准备，应用更高级的商业智能工具Excel进阶函数45一些有用的进阶函数»数据引用»Address»Indirect»offset»数据查找与关联»Lookup»Vlookup»Hlookup»统计»Count,Countif»Sum,Sumif»AVERAGE,AVERAGEA»Max,Min»Median,Mode»Frequency»Rank,PercentRank»逻辑函数»And»Or»NotIndirect»功能»按给定的文本形式的地址，来引用内容。»由于地址是文本形式的，这意味着它可以用公式来“合成”。»示例：»=Indirect(“1月数据!A1”)»引用了“1月数据”工作表里的A1单格内容»=SUM(INDIRECT(统计函数!a1:a10))»合计了“统计函数”表中的A1到A10的值。67lookup»功能»函数LOOKUP在单行区域或单列区域（向量）中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值。如果需要指定包含待查找数值的区域，则可以使用函数LOOKUP的这种形式。函数LOOKUP的另一种形式为自动在第一列或第一行中查找数值。8lookup»语法1»lookup(要找的值,在哪里找,结果在哪里)»参数»要找的内容指定函数LOOKUP要在哪里查找。必须按升序排序.»在哪里找指定要查找的内容，应该是一个单一值或是单一单元格。»结果在哪里指定结果位于的区域，其大小必须与在哪里找相同。»注意：»如果函数lookup找不到要找的内容，则查找在哪里找中小于或等于要找的内容的最大数值。9lookupAB1产品销售2桔子1.53苹果2.94西瓜2.15草莓0.7公式说明（结果）=LOOKUP(“苹果”,A2:A5,B2:B5)在A列中查找苹果，并返回同一行B列的销售值(2.9)10Vlookup»功能»在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值»语法»VLOOKUP(要找的值,在哪里找,找到后取第几列,是否模糊查找)11Vlookup»参数»要找的值需要在数组第一列中查找的数值。»在哪里找为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用，例如数据库或列表。»找到后取第几列为在哪里找中待返回的匹配值的列序号。从１开始。»是否模糊查找为一逻辑值，指明是精确匹配，还是近似匹配。输入“FALSE”将查找精确匹配值。12Vlookup(2)AB1产品销售量2桔子1.53苹果2.94西瓜2.15草莓0.7公式说明（结果）=VLOOKUP(“苹果”,A2:B6,2)在A2:B6区域中的第一列中查找苹果，并返回同一区域对应第2列的销售值(2.9)13Hlookup»说明»在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值»语法»HLOOKUP(要找的值,在哪里找,找到后取第几行,是否模糊查找)14Hlookup»参数»要找的值需要在数组第一列中查找的数值。»在哪里找为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用，例如数据库或列表。»找到后取第几行为在哪里找中待返回的匹配值的行序号。从１开始。»是否模糊查找为一逻辑值，指明是精确匹配，还是近似匹配。FALSE将查找精确匹配值。15HLookupABCD1桔子苹果西瓜草莓21.52.92.10.7公式说明（结果）=HLOOKUP(“苹果”,A1:D2,2)在A1:D2区域中的第一行中查找苹果，并返回同一区域对应第2行的销售值(2.9)16Countif»功能»计数符合条件的那些记录»语法»COUNTIF(条件所在区域,条件)»参数»条件所在区域为需要计算其中满足条件的单元格数目的单元格区域。»条件为确定哪些单元格将被计算在内的条件。例如，条件可以表示为32、32、32或apples。17Countif(2)AB1数据数据2apples323oranges544peaches755apples86公式说明（结果）=COUNTIF(A2:A5,apples)计算第一列中苹果所在单元格的个数(2)18sumif»功能»对符合条件的那些记录的指定列求和»语法»SUMIF(条件区域,条件[,求和区域])19sumif»参数»条件区域为用于条件判断的单元格区域。»条件为确定哪些单元格将被相加求和的条件。例如，条件可以表示为32、32、32或apples。»求和区域是需要求和的实际单元格。»说明»只有在区域中相应的单元格符合条件的情况下，求和区域中的单元格才求和。»如果忽略了求和区域，则对条件区域中的单元格求和。20sumif(2)AB1属性值佣金2100,0007,0003200,00014,0004300,00021,0005400,00028,000公式说明（结果）=SUMIF(A2:A5,“160000”,B2:B5)属性值超过160,000的佣金的和(63,000)2122逻辑函数»And：与»Or：或»Not：非条件1真真假假条件2真假真假and真假假假or真真真假ORAND23条件结果函数if»If(逻辑值,结果1,结果2)»如果逻辑值为真，则公式返回结果1»如果逻辑值为假，则公式返回结果224逻辑计算例子»判断消费是否在10到20之间，或者已经特别要求进行提醒，这些客户要求进行短信提醒：»=if(or(and(B2=10,B2=20),C2=“是“),“需要提醒”,“不要提醒”)ABCD1姓名消费要求提醒是否提醒2张三13否3李四5是25请善用Excel的帮助»自学是最好的老师.»Excel的帮助包括了所有的函数用法,示例等资料,是最好的Excel学习材料.数据分析部分27数据分析基础知识»数据挖掘»数据挖掘（DataMining，DM）是指从大量不完全的、有噪声的、模糊的、随机的数据中，提取隐含在其中的、有用的信息和知识的过程。»“数据立方体”是一个常用的数据挖掘分析概念.28数据分析基础知识»数据立方体概念»几何立方体»多维几何立方体»数据立方体»透视表29三维几何立方体»三个维度：»长»宽»高»一个被上述维度限定了位置的点O30多维几何立方体»如更多的维度呢？»人类生活在一个三维的空间里，无法感觉,也很难想象多于三维的空间»但是可以想象通过其他变量来表示更多维度»点的质量、颜色、粒子的类型……»还有什么？31数据立方体»这是一个二维数据集(立方体)的例子。部门产品业绩华北苹果10华北桔子5华南苹果3华南桔子1432数据立方体产品维部门维苹果桔子华北510华北143部门产品业绩华北苹果10华北桔子5华南苹果3华南桔子1433数据立方体»无限多的维度»无限多的指标34几何立方体VS数据立方体几何立方体数据立方体概念比较例子概念比较例子维度几何体维度长宽高分析维度产品、部门、…特定点几何点的量质量粒子类型数据指标集业绩、定单数35数据透视»透视，顾名思义，就是对一个多维数据立方体，在某个角度上进行“投影”观察。»投影时，一些维度被关注,其他维度被压缩(忽略)。36数据透视»上述二维数据集的例子。部门产品业绩华北苹果10华北桔子5华南苹果3华南桔子1437数据透视»当从部门的角度观察数据时，结果为：»当从产品的角度观察时，结果为：部门业绩华北15华南17产品业绩苹果13桔子1938透视表数据源»数据源范式»维度+统计指标»一个事实数据一行记录部门(维度)产品(维度)业绩(指标)华北苹果10华北桔子5华南苹果3华南桔子1440操作演示»基本的行列维度»添加分析指标»维度筛选»指标分析类型»求和»计数»平均数»方差»高级分析»百分比»同列百分比»同行百分比»环比增长»透视图答疑时间!谢谢！