excel数据分析.pptx
Excel 数据分析,世界上的数据分析师分为两类，使用Excel的分析师，和其他分析师。每一个数据新人的入门工具都离不开Excel。因为Excel涵盖的功能足够多。很多传统行业的数据分析师只要求掌握Excel即可，会SPSS/SAS是加分项。即使在挖掘满街走，Python不如狗的互联网数据分析界，Excel也是不可替代的。,1.Excel常用函数,清洗处理类,主要是文本、格式以及脏数据的清洗和转换。很多数据并不是直接拿来就能用的，需要经过数据分析人员的清理。数据越多，这个步骤花费的时间越长。,关联匹配类,在进行多表关联或者行列比对时用到的函数，越复杂的表用得越多。良好的表习惯可以减少这类函数的使用。,逻辑运算类,数据分析中不得不用到逻辑运算，逻辑运算返回的均是布尔类型，True和False。很多复杂的数据分析会牵扯到较多的逻辑运算,关联匹配类,常用的基础计算、分析、统计函数，以描述性统计为准。,时间序列类,专门用于处理时间格式以及转换，时间序列在金融、财务等数据分析中占有较大比重。时机序列的处理函数比我列举了还要复杂，比如时区、分片、复杂计算等。这里只做一个简单概述,2.Excel数据处理技巧,快捷键,Excel的快捷键很多，以下主要是能提高效率,格式转换,Excel的格式及转换很容易忽略，但格式会如影随形伴随数据分析者的一切场景通常我们将Excel格式分为数值、文本、时间时间格式在Excel中可以和数值直接互换，也能用加减法进行天数换算。时间格式有不同表达。例如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,数组,数组很多人都不会用到，甚至不知道有这个功能。依旧是数据分析越往后用到越多，它类似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,3，a,b,c)Excel的数组具体应用，大家可以搜索学习，可以提高一定的效率。,数组,数组很多人都不会用到，甚至不知道有这个功能。依旧是数据分析越往后用到越多，它类似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,3，a,b,c)Excel的数组具体应用，大家可以搜索学习，可以提高一定的效率。,分列,Excel可以将多个单元格的内容合并，但是不擅长拆分。分列功能可以将某一列按照特定规则拆分。常常用来进行数据清洗。,有一列地区的数据，我想要将市和区分成两列。我们可以用mid和find函数查找市截取字符。但最快的做法就是用“市”分列。,条件格式,条件格式可以当作数据可视化的应用。如果我们要使用函数在大量数据中找出前三的值，可能会用到rank()函数，排序，然后过滤出1，2，3。用条件格式则是另外一种快速方法，直接用颜色标出，非常直观。,自定义下拉菜单（数据有效性）,数据有效性是一种约束，针对单元格限制其输入，也就是让其只能固定几个值。下拉菜单是一种高阶应用，通过允许下拉箭头即可。,自定义名称,自定义名称是一个很好用的技巧，我们可以为一个区域，变量、或者数组定义一个名称。后续要经常使用的话，直接引用即可，无需再次定位。这是复用的概念。,我们将A1:A3区域命名为NUM直接使用=sum(NUM)，等价于sum(A1:A3)。,自定义名称,自定义名称是一个很好用的技巧，我们可以为一个区域，变量、或者数组定义一个名称。后续要经常使用的话，直接引用即可，无需再次定位。这是复用的概念。,我们将A1:A3区域命名为NUM直接使用=sum(NUM)，等价于sum(A1:A3)。,查找公式错误,公式报错也不知道错在哪里的时候可以使用，尤其是各类IF嵌套或者多表关联，逻辑复杂时。查找公式错误是逐步运算的，方便定位。,分组和分级显示,分组和分级显示，常用在报表中，在报表行数多到一定程度时，通过分组达到快速切换和隐藏的目的。越是专业度的报表（咨询、财务等），越可以学习这块。在数据菜单下。,分析工具库,分析工具库是高阶分析的利器，包含很多统计计算，检验功能等工具。Excel是默认不安装的，要安装需要加载项，在工具菜单下（不同版本安装方式会有一点小差异）。,分析工具库是统计包，规划求解是计算最优解，类似决策树。这两者的分析方法以后详细论述。,3.Excel数据处理实例,明确目的,数据分析的大忌是不知道分析方向和目的，拿着一堆数据不知所措。一切数据分析都是以业务为核心目的，而不是以数据为目的。,永远不要妄图在一堆数据中找出自己的结论，太难。目标在前，数据在后。哪怕给自己设立一个很简单的目标，例如计算业务的平均值，也比没有方向好。因为有了平均值可以想数字比预期是高了还是低了，原因在哪里，数据靠谱吗？为了找出原因还需要哪些数据,在这个实例中，我们主要分析的目标是：数据分析师是一个什么样的岗位？哪些城市对数据分析师的需求较大？公司对数据分析师的缺口如何？数据分析师的薪水情况如何?,观察数据,拿出数据别急切计算，先观察数据。,字段名称都是英文，我是通过Json获取的数据，所以整体数据都较为规整。往后绝大部分的数据源的字段名都是英文。因为比起拼音和汉字，它更适合编程环境下。先看一下columns的含义。,city：城市companyFullName：公司全名companyId：公司IDcompanyLabelList：公司介绍标签companyShortName：公司简称companySize：公司大小businessZones：公司所在商区firstType：职位所属一级类目secondType：职业所属二级类目education：教育要求industryField：公司所属领域positionId：职位IDpositionAdvantage：职位福利positionName：职位名称positionLables：职位标签salary：薪水workYear：工作年限要求,首先看一下哪些字段数据可以去除。companyId和positionId是数据的唯一标示，类似该职位的身份证号，这次分析用不到关联vlookup，我们先隐藏。companyFullName和companyShortName则重复了，只需要留一个公司名称，companyFullName依旧隐藏,数据有无缺失值,数据的缺失值很大程度上影响分析结果。引起缺失的原因很多，例如技术原因，爬虫没有完全抓去，例如本身的缺失，该岗位的HR没有填写。如果某一字段缺失数据较多（超过50%），分析过程中要考虑是否删除该字段，因为缺失过多就没有业务意义了。,具体操作方法：“Ctrl+”移动到工作表最后一行查看总行数选取该列，在屏幕的右下角查看计数，以此判别有无缺失,数据是否一致化,一致化指的是数据是否有统一的标准或命名。例如上海市数据分析有限公司和上海数据分析有限公司，差别就在一个市字，主观上肯定会认为是同一家公司，但是对机器和程序依旧会把它们认成两家。会影响计数、数据透视的结果。,在positionName中有些职位明确为数据分析师，有些职位要求具备数据分析能力，但是又干其他活。招聘网站为了照顾这种需求，采用关联法，只要和数据分析相关职位，都会在数据分析师的搜索结果中出现。像大数据工程师是数据的另外发展方向，但不能归纳到数据分析岗位下，后续我们需要将数据分析强相关的职位挑选出来,数据是否有脏数据,脏数据是分析过程中很讨厌的环节。例如乱码，错位，重复值，未匹配数据，加密数据等。能影响到分析的都算脏数据，没有一致化也可以算。,在这个实例中主要是清除重复的职位，positioId作为职位唯一标示，如果重复了，就说明有重复的职位数据。在删除positionId列重复项时，我们也需要把其他对应positionId的同一行一起删除。,具体操作方法：数据-高级筛选-选择不重复记录1、选中A列2、数据-高级筛选-选择不重复记录3、粘贴到新的工作表中,数据清洗,数据清洗可以新建Sheet，方便和原始数据区分开来。先清洗Salary列，因为薪水是一个范围，我们不可能拿范围计算平均工资，我们只能取最高薪水和最低薪水的平均数作为该岗位薪资。这是数据来源的缺陷，因为我们并不能知道应聘者实际能拿多少。这是薪水计算的误差。,具体操作方法：利用文本查找的思想1、先用=SEARCH(k,O2,1)。查找第一个K出现的位置2、我们知道第一个k出现的位置，此时=LEFT(O2,SEARCH(k,O2,1)得到的结果就是 7K，要去除掉k，SEARCH(k,O2,1)再减去1即可3、最高薪水也是同样的思路，但不能使用k，因为第二个薪水位置不固定。需要利用SEARCH查找-位置,然后截取 从-到最后第二个位置的字符串。=MID(O2,SEARCH(-,O2,1)+1,LEN(O2)-SEARCH(-,O2,1)-1)4.算出平均数,数据清洗,companyLabelList是公司标签，诸如技能培训啊、五险一金啊等等。直接用分列即可。不过分列前我们要先去除一下无关符号如：“”、“”、“”，留下逗号作为分隔符即可。,具体操作方法：利用文本查找的思想1、用=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(U2,“”,“”),“”,“”),“”,“”)除去无关符号2、用“复制”、“选择性黏贴”将公式格式转化为文本3、分列,数据清洗,接下来清洗positionName，上文已经讲过有各种乱七八糟或非数据分析师职位，所以我们需要排除掉明显不是数据分析师的岗位。,具体操作方法：1、用find和数组函数结合=IF(COUNT(FIND(数据分析,数据运营,分析师,N2),1,0)，得到多条件查找后的结果2、用筛选将1过滤出来，得到需要分析的最终数据3、创建新的工作表将最终数据复制过去,分析过程,分析过程有很多玩法。因为主要数据均是文本格式，所以偏向汇总统计的计算。如果数值型的数据比较多，就会涉及到统计、比例等概念。如果有时间类数据，那么还会有趋势、变化的概念。整体分析使用数据透视表完成，先利用数据透视表获得汇总型统计。,分析过程不多做篇幅了，主要使用数据透视表进行多维度分析，没有其他复杂的技巧。我们的分析也属于多维度，城市、工作年限、企业大小、企业领域等，利用不同维度形成一个直观的二位表格，而维度则是通过早期的数据清洗统一化标准化。这是一种很常见的分析技巧。,
