Excel玩数据分析常用的43个函数!
Excel是我们工作中经常用到的工具。对于数据分析来说,也是最基本的数据处理工具。很多传统行业的数据分析师甚至可以掌握Excel和SQL。
对于初学者来说,有时候没必要急着去学习R语言等专业工具(当然学习是加分项)。
因为Excel涵盖了足够多的函数,也有很多统计、分析、可视化的插件,但是我们在处理数据的时候,很多函数通常是不知道怎么用的!
Excel的高级学习主要分为两个部分:
数据分析中常用的Excel函数
用Excel做一个简单完整的分析
本文主要介绍了数据分析中常用的43个Excel函数及其用法。
注:本文内容是一个目录,介绍每个函数是做什么的,哪个函数可以用来解决某个问题等。具体使用方法可以自己学习。
Excel的函数其实就是一些复杂的计算公式。这些函数将复杂的计算步骤留给程序来处理。只要按照函数格式输入相关参数,就可以得到结果。
如果想求一个区域的和(A1:C100),可以直接用SUM(A1:C100)的形式。
并且,对于函数,不用死记硬背,只要知道应该选择什么样的函数,需要什么参数,怎么用就行了!
例如,选择一个字段,并使用左/右/中功能& # 8230;…其他细节给万能的百度!
下面根据不同的应用场景,对这些常用的必备功能进行分类介绍。
一个
匹配类别
经常需要的数据不在同一个Excel表中或者在同一个Excel表的不同表中,数据太多,复制起来比较麻烦,容易出错。如何整合它们?
以下函数用于多表关联或行列比较,表越复杂越好用!
1.纵向查找函数
功能:用于查找满足第一列条件的元素。
语法:=VLOOKUP(要查找的值、要查找值的区域、包含返回值的区域中的列号、精确匹配或近似匹配-指定为0/FALSE或1/TRUE)。
例如,查询名称是单元格F5中雇员的职位。
2.HLOOKUP
函数:搜索表格的首行或值数组中的值,并返回表格或数组中指定行的同一列中的值。
语法:=VLOOKUP(要查找的值,要在其中查找值的区域,包含返回值的区域中的行号,精确匹配或近似匹配-指定为0/FALSE或1/TRUE)。
区别:HLOOKUP和VLOOKUP两个函数都是用来查找表中的数据的,但是HLOOKUP返回的值与要查找的值在同一列,而VLOOKUP返回的值与要查找的值在同一行。
3.指数
函数:返回或引用表格或区域中的值。
语法:= INDEX(单元格区域或数组,要返回值的行和列)
4.比赛
函数:用于返回指定区域(一行或一列)中指定内容的位置。
语法:= MATCH(要返回值的单元格区域或数组,搜索区域,搜索方法)
5.等级
功能:求某个区域的一组数值中某个数值的排名。
语法:=RANK(参与排名的数值,排名的数值范围,排名方式-0为降序-1为升序-默认为0)。
6.排
函数:返回单元格所在的行。
7.圆柱
函数:返回单元格所在的列。
8.抵消
函数:从指定的基准位置按行列偏移量返回指定的引用。
语法:=Offset(指定返回的点、行数、列数、行数和列数)
2
清洁处理类
在数据处理之前,需要对提取的数据进行初步的清理,比如清除字符串空格、合并单元格、替换、截取字符串、找出字符串出现的位置等。
清除字符串空格:使用Trim/Ltrim/Rtrim。
合并单元格:使用连接
截断字符串:使用左/右/中间
替换单元格中的内容:替换/替代
查找文本在单元格中的位置:查找/搜索
9.附加装饰
作用:清除绳子两边的空格。
10.Ltrim
功能:清除单元格右侧的空间。
11.Rtrim
功能:清除单元格左侧的空间。
12.连锁的
语法:=Concatenate(单元格1,单元格2 …)
还有另一种方法来合并单元格的内容&当需要合并太多内容时,Concatenate更有效。
13.左边的
函数:从左边截取字符串。
语法:=Left(值所在的单元格,截取长度)
14.对吧
函数:从右边截取字符串
语法:= Right(值所在的单元格,截取长度)
15.中间的
函数:从中间截取字符串
语法:= Mid(指定字符串,起始位置,截取长度)
例:根据身份证号提取年月。
16.替换
功能:更换电池串。
语法:=Replace(指定字符串、开始替换的位置、要替换的字符数、要替换的内容)
17.代替者
和replace类似,不同的是Replace是根据位置实现替换的,需要提供替换后的新文本,位置的个数,以及替换的位置。替换,根据文本内容,需要提供被替换的旧文本和新文本,以及替换哪个旧文本。因此,Replace实现了固定位置的文本替换,Substitute实现了固定位置的文本替换。
例如:替换一些电话号码。
18.发现
功能:查找文本位置
语法:=Find(要查找字符,请指定字符串和字符数)
19.搜索
函数:返回指定字符或文本字符串在字符串中的第一个出现位置,从左向右搜索。
语法:=search(要搜索的字符,字符所在的文本,从哪个字符开始搜索)
区别:Find和Search的功能差不多,都能找到字符的位置。不同的是,find函数是精确的,并且区分大小写;搜索函数模糊搜索,不区分大小写。
20.低输入联网(low-entry networking的缩写)
函数:文本字符串中的字符数
21.Lenb
函数:返回文本中包含的字符数。
例如:从a列中的姓名phone中提取姓名。
三
逻辑运算类
逻辑,顾名思义,直接进入功能而不涉及细节:
22.如果
函数:使用逻辑函数IF function时,如果条件为真,函数将返回值;如果条件为假,函数将返回另一个值。
语法:=IF(条件,如果为真则返回值,如果为假则返回值)
23.和
功能:逻辑判断,相当于“并集”。
语法:如果所有参数都为真,则返回True,常用于多条件判断。
24.运筹学
功能:逻辑判断,相当于“或”。
语法:只要参数有真值,就返回真,常用于多条件判断。
四
统计类
使用Excel表格统计数据时,经常需要用到Excel的各种公式,也是最常用的。(针对这些,Excel自带快捷功能)
25.部
功能:求一个区域的最小值。
26.最大函数
功能:求某一区域的最大值。
27.平均的
功能:计算某一区域的平均值。
28.数数
功能:统计包含数字的单元格的个数。
29.COUNTIF
函数:计算一个区域中满足给定条件的单元格的数量。
语法:=COUNTIF(单元格1:单元格2,条件)
例如:=COUNTIF(Table1!A1:表1!C100,“是”)计算表1中A1到C100中值为“是”的单元格的数量。
例如,统计商店中的商业交易数量。
30.COUNTIFS
函数:计算由一组给定条件指定的单元格的数量。
语法:=COUNTIFS(第一个条件区域,第一个对应条件,第二个条件区域,第二个对应条件,第n个条件区域,第n个对应条件)
例如:=COUNTIFS(Table1!A1:表1!A100,“是”,表1!C1:第一桌!C100,“否”)计算表1中A1到A100范围内单元格的个数,同时在C范围内取值“是”和“否”。
31.总和
函数:计算单元格区域中所有值的总和。
32.苏米夫
函数:查找符合条件的单元格
语法:=SUMIF(单元格1:单元格2,条件,单元格3:单元格4)
例:计算一班总成绩。
32.苏米夫斯
函数:对满足指定条件的一组单元格求和。
语法:=SUMIFS(实际求和区域,第一个条件区域,第一个对应求和条件,第二个条件区域,第二个对应求和条件,第n个条件区域,第n个对应求和条件)。
例如:=SUMIFS(表1!C1:第一桌!C100,表1!A1:表1!A100,“是”,表1!B1: Table1b100,“否”)计算表1中C1到C100区域的单元格之和,对应的行和列A值为“是”,对应的列B值为“否”。
33.求和乘积
函数:返回相应数组或面积乘积的和。
语法:=SUMPRODUCT(单元格1:单元格2,单元格3:单元格4)
示例:=SUMPRODUCT(Table1!A1:表1!A100,2号桌!B1表2!B10)计算表1中A1到A100和表2中B1到B100的乘积和,即A1*B1+A2*B2+A3*B3+…
34.标准差(standarddeviation)
统计函数,求标准差。
35.亚总数
语法:= Substotal(引用区域,参数)
汇总函数,参数化平均值、计数、最大值和最小值、乘法、标准差、求和、方差等。换句话说,只要你知道这个功能,以上都可以丢弃。
36.整数/整数
舍入函数,int向下舍入,舍入到小数位。
round(3.1415,2)= 3.14;
round(3.1415,1)=3.1
五
时间序列类
它专门用于处理时间格式和转换。
37.今天
函数:返回今天的日期,动态函数。
38.现在
函数:返回当前时间,动态函数。
39.年
函数:返回日期的年份。
40.月
函数:返回日期中的月份。
41.天
函数:返回某个日期的天数,用序列号表示。
42.工作日
函数:返回与某个日期对应的一周中的某一天。默认情况下,天数是1(星期日)到7(星期六)之间的整数。
语法:=Weekday(指定时间,参数)
43.Datedif
函数:计算两个日期之间的天数、月数或年数。
语法:=Datedif(开始日期,结束日期,参数)
以上是我经常整理和学习的Excel函数。希望能帮到你!