想成为数据分析师，看这里，数据分析必备的43个Excel函数

目录

• 前言

• 函数分类
  • 关联匹配类
  • 清洗处理类
  • 逻辑运算类
  • 计算统计类
  • 时间序列类

前言

Excel是我们工作中经常使用的一种工具对于数据分析来说这也是处理数据最基础的工具。

很多传统行业的数据分析师甚至只要掌握Excel和SQL即可。
对于初学者有的时候并不需要急于苦学R语言等专业工具当然会也是加分项因为Excel涵盖的功能足够多也有很多统计、分析、可视化的插件。只不过我们平时处理数据的时候很多函数都不知道怎么用。
关于 Excel的进阶学习主要分为两块一个是数据分析常用的Excel函数另一个分享用Excel 做一个简单完整的分析。
这篇文章主要介绍数据分析常用的43个Excel函数及用途实战分析将在下一篇讲解。
关于函数
Excel的函数实际上就是一些复杂的计算公式函数把复杂的计算步骤交由程序处理只要按照函数格式录入相关参数就可以得出结果。如求一个区域的和可以直接用 SUM(A1:C100)的形式。
所以对于函数不用刻意记刻意背只要知道比如“选取字段Left/Right/Mid”函数并且需要哪些参数怎么用就行了复杂的就交给万能的百度吧。

函数分类

关联匹配类
清洗处理类
逻辑运算类
计算统计类
时间序列类

关联匹配类

经常性的需要的数据不在同一个excel表或同一个 excel表不同sheet中数据太多copy麻烦也不准确如何整合呢这类函数就是用于多表关联或者行列比对时的场景而且表越复杂用得越多。

函数HLOOKUP和 VLOOKUP 都是用来在格中查找数据。
1、VLOOKUP
功能用于查找首列满足条件的元素。

语法=VLOOKUP要查找的值要在其中查找值的区域区域中包含返回值的列号精确匹配或近似匹配 - 指定为 0/FALSE 或 1/TRUE。
2. HLOOKUP
功能搜索表的顶行或值的数组中的值并在表格或数组中指定的行的同一列中返回一个值。语法=VLOOKUP要查找的值要在其中查找值的区域区域中包含返回值的行号精确匹配或近似匹配 -指定为 0/FALSE 或 1/TRUE。
区别HLOOKUP返回的值与需要查找的值在同一列上而VLOOKUP返回的值与需要童找的值在同一行上。
3、INDEX
功能返回表格或区域中的值或引用该值。
语法=INDEX要返回值的单元格区域或数组所在行所在列)
4、 MATCH
功能用于返回指定内容在指定区域(某行或者某列的位置。
语法=MATCH要返回值的单元格区域或数组查找的区域查找方式)
5、RANK
功能求某一个数值在某一区域内一组数值中的排名.
语法=RANK参与排名的数值排名的数值区域排名方式-0是降序-1是升序-默认为0。
6、Row
功能返回单元格所在的行
7、Column
功能返回单元格所在的列
8、Offset
功能从指定的基准位置按行列偏移量返回指定的引用
语法=Offset指定点偏移多少行偏移多少列返回多少行返回多少列

清洗处理类

数据处理之前需要对提取的数据进行初步清汁。如清除字符串空格合并单元格、替换、截取字符、查找字符串出现的位置等。

• 清除字符串空格使用Trim/Ltrim/Rtrim

• 合并单元格使用concatenate

• 截取字符串使用Left/Right/Mid

• 替换单元格中内容Replace/Substitute

• 查找文本在单元格中的位置Find/Search

9、Trim
功能清除掉字符串两边的空格
10、Rtrim
功能清除单元格右边的空格
11、 Ltrim
功能清除单元格左边的空格
12、concatenate
语法=Concatenate(单元格1单元格2…)
合并单元格中的内容还有另一种合并方式是&需要合并的内容过多时concatenate 效率更快。
13、Left
功能从左截取字符串
语法=Left值所在单元格截取长度)
14.Right
功能从右截取字符串
语法=Right值所在单元格截取长度)
15、Mid
功能从中间截取字符串
语法=Mid指定字符串开始位置截取长度
16.Replace
功能替换掉单元格的字符串
语法=Replace指定字符串哪个位置开始替换替换几个字符,替换成什么
17.Substitute

18、和replace 接近不同在于Replace根据位置实现替换需要提供从第几位开始替换替换几位替换后的新的文本而Substitute根据文本内容替换需要提供替换的旧文本和新文本以及替换第几个旧文本等。因此Replace 实现固定位置的文本替换Substitute 实现固定文本替换。
18、 Find
功能·查找文本位置
语法=Find要查我字符指定字符里第几个字符
19、Search
功能返回一个指定字符或文本字符串在字符串中第一次出现的位置从左到右查找
语法=search要查找的字符字符所在的文本从第几个字符开始查找
Find 和Search 这两个函数功能几乎相同实现直找字符所在的位置区别在于Find函数精
确查找区分大小写Search函数模糊查找不区分大小写。
20、Len
功能文本字符串的字符个数
21、 Lenb
功能返回文本中所包含的字符数

逻辑运算类

22、1F
功能使用逻辑函数IF函数时如果条件为真该函数将返回一个值如果条件为假函数将返回另一个值。
语法=IF条件true时返回值false返回值

23、AND
功能逻辑判断相当于“并”。
语法全部参数为True则返回True经常用于多条件判断。
24、OR
功能逻辑判断相当于“或”。
语法只要参数有一个True则返回Ture经常用于多条件判断。

计算统计类

在利用excel表格统计数据时常常需要使用各种excel自带的公式也是最常使用的一类。
重要性不言而喻。不过excel都自带快捷功能。

• MIN函数找到某区域中的最小值

• MAX函数找到某区域中的最大值

• AVERAGE函数计算某区域中的平均值

• COUNT函数计算某区域中包含数字的单元格的数目

• COUNTIF 函数计算某个区域中满足给定条件的单元格数目

• COUNTIFS函数统计一组给定条件所指定的单元格数

• SUM函数计算单元格区域中所有数值的和

• SUMIF函数对满足条件的单元格求和

• SUMIFS函数对一组满足条件指定的单元格求和

• SUMPRODUCT函数返回相应的数组或区域乘积的和

25、MIN
功能到某区域中的最小值
26、MAX函数
功能找到某区域中的最大值
27、AVERAGE
功能计算某区域中的平均值

28.COUNT
功能计算含有数字的单元格的个数。
29.COUNTIF
功能计算某个区域中满足给定条件的单元格数目
语法=COUNTIF(单元格1单元格2条件)
比如=COUNTIF(TableTIA1:TableT!C100“YES*计算 Table1中 A1 到C100区域单元格中值为”YES"的单元格个数
30、 COUNTIFS
功能统计一组给定条件所指定的单元格数
语法=COUNTIFS(第一个条件区域第一个对应的条件第二个条件区域第二个对应的条件第N个条件区域第N个对应的条件)
比如:=COUNTIFS(Table1!A1: Table1IA100“YES".Table1!C1:Table1!C100“NO”)计算Table1中A1到A100区域单元格中值为"YES".而且同时C区域值为"NO"的单元格个数
31、SUM
计算单元格区域中所有数值的和
32、SUMIF
功能求满足条件的单元格和
语法=SUMIF(单元格1单元格2条件单元格3单元格4)

32、SUMIFS
功能对一组满足条件指定的单元格求和

语法=SUMIFS实际求和区域第一个条件区域第一个对应的求和条件第二个条件区域第二个对应的求和条件第N个条件区域第N个对应的求和条件
比如=SUMIFS(Table1!C1:Table1!C100Table1IA1:Table11A100,“YES”,Table1!B1:Table1B100.“NO")计算Table1中C1到C100区域同时相应行A列值为“YES”而且对应B列值为NO"的单元格的和。
33、SUMPRODUCT
功能返回相应的数组或区域乘积的和
语法=SUMPRODUCT(单元格1单元格2单元格3单元格4)
比如=SUMPRODUCT(Table1!A1Table1!A100Table2!B1Table2!B100 计算表格1的A1到A100与表格2的B1到B100的乘积和即 A1* B1+A2B2+A3B3+.
34、 Stdev
统计型函数求标准差。
35. Substotal
语法=Substotal引用区域参数
汇总型函数将平均值、计数、最大最小、相乘、标准差、求和方差等参数化换言之只要会了这个函数上面的都可以抛弃掉了。
36、 Int / Round
取整函数int 向下取整round按小数位取数。
round(3.1415,2)=3.14:
round(3.1415,1)=3.1

时间序列类

专门用于处理时间格式以及转换。
37、TODAY
返回今天的日期动态函数。
38、NOW
返回当前的时间动态函数。
39、YEAR
功能返回日期的年份。

40.MONTH
功能返回日期的月份。
41.DAY
功能返回以序列数表示的某日期的天数。
42、WEEKDAY
功能返回对应于某个日期的一周中的第几天。 默认情况下天数是1星期日到7星期六范围内的整数。
语法=Weekday(指定时间参数)
43、Datedif
功能计算两个日期之间相隔的天数、月数或年数。
语法=Datedif开始日期结束日期参数
以上就是数据分析必备的43个Excel函数下一篇博主准备讲解Excel数据分析其他数据分析资料在博主的专栏可查询。