数据分析中excel常用函数总结

时间:2024-07-10 08:07:23

1、相对引用,绝对引用和混合引用

相对引用:比如求和,当计算出一个求和值,其他的就可以向下填充,此时求和引用的行列会随着求和区域的行列变化而变化。

 

绝对引用:

对某一个引用区域进行锁定,让求和只随着另外一个引用区域变化而变化

混合引用:

对行列其中一个绝对引用,行变列不变,或者列变行不变

 

2、countifs(条件区域,条件值,条件区域,条件值...)

作用:多条件技术

例如:

条件区域:是一列,条件值所在的那列

3、求和函数:sumif、sumifs

sumif(条件区域,条件,求和区域)

例如下面:求布鞋的总销量,

 

当求和区域跟条件区域,大小形状不一样的时候,求和区域会以最左上角第一个单元格为起点,自动变为和条件区域大小形状一样的区域,

例如:求和区域变成了一个单元格,但是结果跟上面计算的是一样的,所以可以利用起来,

sumifs(求和区域,条件1区域,条件1,条件2区域,条件2...)

concat(字符串,字符串...):文本拼接函数,将多个文本拼接在一起

4、条件判断函数

(1) if(条件,条件成立值,条件不成立值)

if(3>4,1,0):3>4,条件成立返回1,条件不成立返回0

(2) iferror(表达式,错误处理):如果表达式错误,就返回错误处理,如果表达式正确,就返回表达式的值

iferror(3/0,“出错”):返回出错        iferror(3/1,“出错):返回3

使用环境:对错误值进行处理

(3)and、or、not

and(条件1,条件2,条件3....)多个参数:返回结果true/false 

or(判断条件1,条件2,条件3...)多个参数:返回结果true/false 

not(判断条件):只有一个参数,当条件成立,返回false,当条件不成立,返回true

5、取值函数

(1)left、right、mid

left函数:left(文本,截取数量)

例:left("你是个好人",2):返回“你是”,从左开始截取2个文本,

right函数:right(文本,截取数量)

例:right("你是个好人",2):从右往左截取2个文本,返回“好人”

mid函数:mid(文本,起始位置,截取数量)

例1:mid("你是个好人",2,6):从左往右数第二个文本,截取6个文本,返回“是个好人”

例2:mid函数,因为截取数量6超过了,从2开始截取到最后4个文本数,所以一直截取到文本的最后,常常被用作从某个字符开始截取到文本最后,搭配len函数使用:

mid("你是个好人",2,len("你是个好人")):len函数计算文本的长度,所以这个用会截取到文本的最后

6、其他函数

(1)lower、upper、substitute、replace、find、search、text、value

lower(文本):将大写变小写

upper(文本):将小写变大写

substitute(文本,被替换的文本,新的文本,替换第几个值)

例:substitute(“狐假狐威”,“狐”,“虎”,2):用“虎”替换文本里第二个的“狐”,返回值:狐假虎威,如果省略最后一个参数,就全部替换:

replace(文本,起始位置,替换数量,新的文本) 

例:​​​​​​​ replace(12345678932,8,4,“****”):从第8位开始,替换4位文本为“****”,就是把后四位替换为“****”,返回:1234567****

find(要查找文本,查找文本,从第几个值开始查找):返回查找的文本所在位置

search( 要查找文本,查找文本,从第几个值开始查找):返回查找的文本所在位置

例子:find和search两个函数的用法一样,但是区别在于:

1、find函数区别大小写,search函数不区别大小写

2、find函数不支持通配符,search函数支持通配符

text(数值,文本):将数值格式转换为特定的文本格式

例:第一个结果是85.5%

value(文本):一个参数,将文本格式转化为数值格式

 (2)column、row、index、match

column(单元格/单元格区域):返回单元格所在的列号/返回单元格区域最左上角单元格的列号

columns(单元格/单元格区域):返回所选区域的总列数

row跟column同理

index(数据范围,行号,列号,第几块数据范围):返回指定行指定列的交叉数据

例:查找陈六的英语成绩:

数据范围是橙色的范围,不包括字段名所在行列,因为图中就一块数据,所以数据范围可以默认省略,为1

 

match(查找的值,查找范围,查找方式):查找值所在位置

查找方式为:精确查找0,模糊查找1/-1

精确查找:0可以省略,但是0前面的逗号不能省略

 

模糊查找为1时,查找的数据区域必须按照升序的顺序排列,如果找不到查找对象,就返回小于查找对象的最大值,1可以省略,1前面的逗号也可以省略

模糊查找为-1时,查找的数据区域必须按照降序的顺序排列,如果找不到查找对象,就返回大于查找对象的最小值,-1不可以省略,

区别:精确查找支持通配符,模糊查找不支持通配符

7、日期函数

today():没有参数,获取系统当前的日期

now():没有参数,获取系统当前的日期和时间