excel数据分析.pptx

《excel数据分析.pptx》由会员分享，可在线阅读，更多相关《excel数据分析.pptx（31页珍藏版）》请在优知文库上搜索。

1、Excel 数据分析 世界上的数据分析师分为两类，使用世界上的数据分析师分为两类，使用ExcelExcel的分析师，和其他分析师。的分析师，和其他分析师。每一个数据新人的入门工具都离不开每一个数据新人的入门工具都离不开ExcelExcel。因为。因为ExcelExcel涵盖的功能足够多。涵盖的功能足够多。很多传统行业的数据分析师只要求掌握很多传统行业的数据分析师只要求掌握ExcelExcel即可，会即可，会SPSS/SASSPSS/SAS是加分项。即使是加分项。即使在挖掘满街走，在挖掘满街走，PythonPython不如狗的互联网数据分析界，不如狗的互联网数据分析界，ExcelExcel也是不

2、可替代的。也是不可替代的。清洗处理类清洗处理类函数功能Trim清除掉字符串两边的空格Concatenate=Concatenate(单元格1，单元格2)合并单元格中的内容，当需要合并的内容过多时，concatenate的效率快也优雅Replace=Replace（指定字符串，哪个位置开始替换，替换几个字符，替换成什么）替换掉单元格的字符串，清洗使用较多Substitute和replace接近，区别是替换为全局替换，没有起始位置的概念LeftRightMid=Mid(指定字符串，开始位置，截取长度)截取字符串中的字符。Left/Right（指定字符串，截取长度），left为从左，right为从右

3、LenLenb返回字符串的长度，在len中，中文计算为一个，在lenb中，中文计算为两个Find=Find（要查找字符，指定字符串，第几个字符）查找某字符串出现的位置，可以指定为第几次出现，与LeftRightMid结合能完成简单的文本提取Search和Find类似，区别是Search大小写不敏感，但支持通配符Text将数值转化为指定的文本格式，可以和时间序列函数一起看 主要是文本、格式以及脏数据的清洗和转换。很多数据并不是直接拿来就能用的，需要经过数据分析人员的清理。数据越多，这个步骤花费的时间越长。关联匹配类关联匹配类函数功能Lookup=Lookup（查找的值，值所在的位置，返回相应位置

4、的值）最被忽略的函数，功能性和Vlookup一样，但是引申有数组匹配和二分法。Vlookup=Vlookup(查找的值，哪里找，找哪个位置的值，是否精准匹配)Excel第一大难关，因为涉及的逻辑对新手较复杂，通俗的理解是查找到某个值然后黏贴过来。IndexIndex（查找的区域，区域内第几行，区域内第几列）和Match组合，媲美Vlookup，但是功能更强大。MatchMatch（查找指定的值，查找所在区域，查找方式的参数）和Lookup类似，但是可以按照指定方式查找，比如大于、小于或等于。返回值所在的位置。Row返回单元格所在的行Column返回单元格所在的列OffsetOffset（指定点

5、，偏移多少行，偏移多少列，返回多少行，返回多少列）建立坐标系，以坐标系为原点，返回距离原点的值或者区域。正数代表向下或向右，负数则相反。 在进行多表关联或者行列比对时用到的函数，越复杂的表用得越多。良好的表习惯可以减少这类函数的使用。逻辑运算类逻辑运算类函数功能IF经典的如果但是，在后期的Python中，也会经常用到，当然会有许多更优雅的写法。也有ifs用法，取代if(and()的写法。And全部参数为True，则返回True，经常用于多条件判断Or只要参数有一个True，则返回Ture，经常用于多条件判断。IS系列常用判断检验，返回的都是布尔数值True和False。常用ISERR，ISER

6、ROR，ISNA，ISTEXT，可以和IF嵌套使用 数据分析中不得不用到逻辑运算，逻辑运算返回的均是布尔类型，True和False。很多复杂的数据分析会牵扯到较多的逻辑运算关联匹配类关联匹配类函数功能SumSumifSumifs统计满足条件的单元格总和Sumproduct统计总和相关，如果有两列数据销量和单价，现在要求卖出增加，用sumproduct是最方便的CountCountifCountifs统计满足条件的字符串个数Max返回数组或引用区域的最大值Min返回数组或引用区域的最小值Rank排序，返回指定值在引用区域的排名，重复值同一排名RandRandbetween常用随机抽样，前者返回0

7、1之间的随机值，后者可以指定范围Averagea求平均值，也有Averageaif，AverageaifsQuartile=Quartile（指定区域，分位参数）Stdev求标准差，统计型函数Substotal=Substotal（引用区域，参数）汇总型函数，将平均值、计数、最大最小、相乘、标准差、求和、方差等参数化，换言之，只要会了这个函数，上面的都可以抛弃掉了IntRound取整函数，int向下取整，round按小数位取数。round(3.1415,2) =3.14 ;round(3.1415,1)=3.1常用的基础计算、分析、统计函数，以描述性统计为准。时间序列类时间序列类函数功能Yea

8、r返回日期中的年Month返回日期中的月Weekday=Weekday(指定时间，参数)返回指定时间为一周中的第几天，参数为1代表从星期日开始算作第一天，参数为2代表从星期一开始算作第一天（中西方差异）。我们中国用2为参数即可Weeknum=Weeknum(指定时间，参数)返回一年中的第几个星期，后面的参数类同weekday，意思是从周日算还是周一Day返回日期中的日（第几号）Date=Date（年，月，日）时间转换函数，等于将year()，month()，day()合并Now返回当前时间戳，动态函数Today返回今天的日期，动态函数Datedif=Datedif（开始日期，结束日期，参数）日

9、期计算函数，计算两日期的差。参数决定返回的是年还是月等专门用于处理时间格式以及转换，时间序列在金融、财务等数据分析中占有较大比重。时机序列的处理函数比我列举了还要复杂，比如时区、分片、复杂计算等。这里只做一个简单概述快捷键快捷键快捷键功能Ctrl+方向键返回日期中的年Ctrl+Shift+方向键返回日期中的月Ctrl+空格键=Weekday(指定时间，参数)返回指定时间为一周中的第几天，参数为1代表从星期日开始算作第一天，参数为2代表从星期一开始算作第一天（中西方差异），我们中国用2为参数即可Shift+空格键=Weeknum(指定时间，参数)返回一年中的第几个星期，后面的参数类同weekda

10、y，意思是从周日算还是周一Ctrl+A返回日期中的日（第几号）Alt+Enter=Date（年，月，日）时间转换函数，等于将year()，month()，day()合并Ctrl+Enter返回当前时间戳，动态函数Ctrl+S返回今天的日期，动态函数Ctrl+Z=Datedif（开始日期，结束日期，参数）日期计算函数，计算两日期的差。参数决定返回的是年还是月等Excel的快捷键很多，以下主要是能提高效率格式转换格式转换Excel的格式及转换很容易忽略，但格式会如影随形伴随数据分析者的一切场景通常我们将Excel格式分为数值、文本、时间时间格式在Excel中可以和数值直接互换，也能用加减法进行天数

11、换算。时间格式有不同表达。例如2016年11月11日，2016/11/11，2016-11-11等。当数据源多就会变得混乱。我们可以用自定义格式规范时间。这里了解一下时间格式的概念，列举是一些较通用的范例。YYYY代表通配的四位数年格式MM代表通配的两位数月格式DD代表通配的两位数日格式HH代表通配的的两位数小时（24小时）格式hh代表通配的两位数小（12小时制）格式mm代表通配的两位数分格式ss代表通配的两位数秒格式例如2016/11/11可以写成：yyyy/MM/dd2016-11-11 23:59:59可以写成：yyyy-MM-dd HH:mm:ss数组数组数组很多人都不会用到，甚至不知

12、道有这个功能。依旧是数据分析越往后用到越多，它类似R语言的Array和Python的List。数组由多个元素组成。普通函数的计算结果是一个值，数组类函数的计算结果返回多个值。数组用大括号表示，当函数中使用到数组，应该用Ctrl+Shift+Enter输入，不然会报错。先看数组的最基础使用。选择A1:D1区域，输入=1,2,3,4。记住是大括号。然后Ctrl+Shift+Enter。我们发现数组里的四个值被分别传到四个单元格中，这是数组的独有用法。我们再来看一下数组和函数的应用。利用，我们能做到1匹配a，2匹配b，3匹配c。也就是一一对应。专业说法是Mapping。=lookup(查找值，1,2

13、,3，a,b,c)Excel的数组具体应用，大家可以搜索学习，可以提高一定的效率。数组数组数组很多人都不会用到，甚至不知道有这个功能。依旧是数据分析越往后用到越多，它类似R语言的Array和Python的List。数组由多个元素组成。普通函数的计算结果是一个值，数组类函数的计算结果返回多个值。数组用大括号表示，当函数中使用到数组，应该用Ctrl+Shift+Enter输入，不然会报错。先看数组的最基础使用。选择A1:D1区域，输入=1,2,3,4。记住是大括号。然后Ctrl+Shift+Enter。我们发现数组里的四个值被分别传到四个单元格中，这是数组的独有用法。我们再来看一下数组和函数的应用

14、。利用，我们能做到1匹配a，2匹配b，3匹配c。也就是一一对应。专业说法是Mapping。=lookup(查找值，1,2,3，a,b,c)Excel的数组具体应用，大家可以搜索学习，可以提高一定的效率。分列分列Excel可以将多个单元格的内容合并，但是不擅长拆分。分列功能可以将某一列按照特定规则拆分。常常用来进行数据清洗。有一列地区的数据，我想要将市和区分成两列。我们可以用mid和find函数查找市截取字符。但最快的做法就是用“市”分列。条件格式条件格式条件格式可以当作数据可视化的应用。如果我们要使用函数在大量数据中找出前三的值，可能会用到rank( )函数，排序，然后过滤出1，2，3。用条件

15、格式则是另外一种快速方法，直接用颜色标出，非常直观。自定义下拉菜单（数据有效性）自定义下拉菜单（数据有效性）数据有效性是一种约束，针对单元格限制其输入，也就是让其只能固定几个值。下拉菜单是一种高阶应用，通过允许下拉箭头即可。自定义名称自定义名称自定义名称是一个很好用的技巧，我们可以为一个区域，变量、或者数组定义一个名称。后续要经常使用的话，直接引用即可，无需再次定位。这是复用的概念。我们将A1:A3区域命名为NUM直接使用=sum(NUM) ，等价于sum(A1:A3)。自定义名称自定义名称自定义名称是一个很好用的技巧，我们可以为一个区域，变量、或者数组定义一个名称。后续要经常使用的话，直接引

16、用即可，无需再次定位。这是复用的概念。我们将A1:A3区域命名为NUM直接使用=sum(NUM) ，等价于sum(A1:A3)。查找公式错误查找公式错误公式报错也不知道错在哪里的时候可以使用，尤其是各类IF嵌套或者多表关联，逻辑复杂时。查找公式错误是逐步运算的，方便定位。分组和分级显示分组和分级显示分组和分级显示，常用在报表中，在报表行数多到一定程度时，通过分组达到快速切换和隐藏的目的。越是专业度的报表（咨询、财务等），越可以学习这块。在数据菜单下。分析工具库分析工具库分析工具库是高阶分析的利器，包含很多统计计算，检验功能等工具。Excel是默认不安装的，要安装需要加载项，在工具菜单下（不同版本安装方式会有一点小差异）。分析工具库是统计包，规划求解是计算最优解，类似决策树。这两者的分析方法以后详细论述。明确目的明确目的数据分析的大忌是不知道分析方向和目的，拿着一堆数据不知所措。一切数据分析都是以业务为核心目的，而不是以数据为目的。永远不要妄图在一堆数据中找出自己的结论，太难。目标在前，数据在后。哪怕给自己设立一个很简单的目标，例如计算业务的平均值，也比没有方向好。因为有了平均值可以想数