Excel是我们工作中经常使用的一种工具,对于数据分析来说,这也是处理数据最基础的工具。很多传统行业的数据分析师甚至只要掌握Excel和SQL即可。
对于初学者而言,有时候并不需要急于苦学R语言等专业工具(当然,学会了就是加分项),因为Excel涵盖的功能足够多,也有很多统计、分析、可视化的插件等,只不过我们平时处理数据的时候对于许多函数都不知道怎么用!
对于Excel的进阶学习,主要分为两块——一个是数据分析常用的Excel函数,另一个是用Excel做一个简单完整的分析。
这篇文章主要介绍数据分析常用的43个Excel函数及用途,实战分析将在下一篇讲解。
(本文内容为目录式的,介绍每个函数是做什么的、遇到某个问题可以用哪个函数解决等,具体使用方法各位可以自行百度学习。)
关于函数:
Excel的函数实际上就是一些复杂的计算公式,函数把复杂的计算步骤交由程序处理,只要按照函数格式录入相关参数,就可以得出结果。如,求一个区域(A1:C100)的和,可以直接用SUM(A1:C100)的形式。 并且,对于函数,不用死记硬背,只需要知道应该选取什么类别的函数,以及需要哪些参数怎么用就行了!比如选取字段,用Left/Right/Mid函数......其他细节神马的就交给万能的百度吧!01 关联匹配类 经常性的,需要的数据不在同一个Excel表或同一个Excel表不同sheet中,数据太多,copy起来麻烦还容易出错,如何整合呢? 下面这些函数就是用于多表关联或者行列比对时的场景,而且表格越复杂,用起来越爽! 1. VLOOKUP 功能:用于查找首列满足条件的元素。 语法:=VLOOKUP(要查找的值,要在其中查找值的区域,区域中包含返回值的列号,精确匹配或近似匹配 – 指定为 0/FALSE 或 1/TRUE)。 (举例:查询姓名是F5单元格中的员工是什么职务) 2. HLOOKUP 功能:搜索表的顶行或值的数组中的值,并在表格或数组中指定的行的同一列中返回一个值。 语法:=HLOOKUP(要查找的值,要在其中查找值的区域,区域中包含返回值的行号,精确匹配或近似匹配 – 指定为 0/FALSE 或 1/TRUE)。 区别:函数HLOOKUP和VLOOKUP都是用来在表格中查找数据,但是,HLOOKUP返回的值与需要查找的值在同一列上,而VLOOKUP返回的值与需要查找的值在同一行上。 3. INDEX 功能:返回表格或区域中的值或引用该值。 语法:= INDEX(要返回值的单元格区域或数组,所在行,所在列) 4. MATCH 功能:用于返回指定内容在指定区域(某行或者某列)的位置。 语法:= MATCH (要返回值的单元格区域或数组,查找的区域,查找方式) 5. RANK 功能:求某一个数值在某一区域内一组数值中的排名。 语法:=RANK(参与排名的数值, 排名的数值区域, 排名方式-0是降序-1是升序-默认为0)。 6. Row 功能:返回单元格所在的行 7. Column 功能:返回单元格所在的列 8. Offset 功能:从指定的基准位置按行列偏移量返回指定的引用 语法:=Offset(指定点,偏移多少行,偏移多少列,返回多少行,返回多少列) 02 清洗处理类 数据处理之前,需要对提取的数据进行初步清洗,如清除字符串空格,合并单元格、替换、截取字符串、查找字符串出现的位置等。函数分类介绍:
下面根据不同的运用场景,对这些常用的必备函数进行分类介绍。
- 清除字符串空格:使用Trim/Ltrim/Rtrim
- 合并单元格:使用concatenate
- 截取字符串:使用Left/Right/Mid
- 替换单元格中内容:Replace/Substitute
- 查找文本在单元格中的位置:Find/Search
- MIN函数:找到某区域中的最小值
- MAX函数:找到某区域中的最大值
- AVERAGE函数:计算某区域中的平均值
- COUNT函数:计算某区域中包含数字的单元格的数目
- COUNTIF函数:计算某个区域中满足给定条件的单元格数目
- COUNTIFS函数:统计一组给定条件所指定的单元格数
- SUM函数:计算单元格区域中所有数值的和
- SUMIF函数:对满足条件的单元格求和
- SUMIFS函数:对一组满足条件指定的单元格求和
- SUMPRODUCT函数:返回相应的数组或区域乘积的和
- 从0到1:Excel数据透视表10招轻松入门!
- 7个Python特殊技巧,助力数据分析工作
- 美观实用!用 Python 做交互式图形!