Excel最常用的函数

Excel函数有很多,最常用的有以下几个,做个总结:

1、Vlookup函数

用途:数据查找、表格核对、表格合并
用法:
=VLOOKUP(lookuo_value,table_array,col_index_num,[range_lookup])
=vlookup(查找的值,查找区域,返回值所在列数,精确还是模糊查找)
例:
旧编号 | 总价值 | 数量 | 新编号
----|------|----|
1001 |58452 |56
1002 |5454 |4
1003 |24577 |44
1004 |45454 |14
1005 |4252 |5
1006 |4441 |96
1007 |42747 |63
1008 |2477 |50
1009 |277274 |252
1010 |2424 |41
1011 |24242 |44

旧编号新编号
1003A0001
1001A0002
1008A0003
1006A0004
1007A0005
1002A0006
1009A0007
1011A0008
1004A0009
1010A0010
1005A0011

更换旧编号为新编号
=VLOOKUP(A:A,E:F,2,FALSE)
在E:F列中查找A:A列中的值是否存在,存在则赋值为E:Fd第二列的值。

旧编号总价值数量新编号旧编号新编号
10015845256A00021003A0001
100254544A00061001A0002
10032457744A00011008A0003
10044545414A00091006A0004
100542525A00111007A0005
1006444196A00041002A0006
10074274763A00051009A0007
1008247750A00031011A0008
1009277274252A00071004A0009
1010242441A00101010A0010
10112424244A00081005A0011

以上表为自动完成。
注意以下方法是错误的,在求新编号时,第一行使用=VLOOKUP(A2:A12,E2:F12,2,FALSE)可以得出正确结果,但拖动时会自动增长,导致下面的数据会出现错误,以下是错误的结果。

旧编号总价值数量新编号旧编号新编号
10015845256A00021003A0001
100254544A00061001A0002
10032457744#N/A1008A0003
10044545414A00091006A0004
100542525A00111007A0005
1006444196#N/A1002A0006
10074274763#N/A1009A0007
1008247750#N/A1011A0008
1009277274252#N/A1004A0009
1010242441A00101010A0010
10112424244#N/A1005A0011

原因是在拖动时,指向自动增长,下图比较清楚:
这里写图片描述
这里写图片描述
###2 、Sumif函数和Countif函数
用途:按条件求和,按条件计数,很多复杂的数据核对也需要用到这2个函数。
用法:
=sumif(range,criteria,[sum_range])
=Sumif(判断区域,条件,求和区域)
例:
考号 |语文| 数学 |英语| 总分
----|------|----|------|
1 |65 |88 |67 |220
2 |56 |95 |83 |234
3 |96 |86 |86 |268
4 |86 |66 |85 |237
5 |77 |87 |66 |230
6 |86 |57 |75 |218
7 |99 |88 |55 |242
8 |63 |88 |75 |226
求语文大于60分的学生的总分之和;
=SUMIF(B2:B9,">60",E2:E9)
B2:B9所有语文成绩
">60"条件
E2:E9求和列

=countif(range,criteria)
=Countif(判断区域,条件)
例:

编号年收入
111220
29500
38400
425000
518000
612000
78666
89999
913500

计数年收入大于12000;
=COUNTIF(K2:K10,">12000")
K2:K10—>所有收入
“>12000"条件
###3、IF函数
用途:根据条件进行判断
用法:
=if(logical_test,[value_if_ture],[value_if_false])
=IF(判断条件,条件成立返回的值,条件不成立返回的值)
例:
考号 |语文 |数学 |英语 |总分 |语文>60且总分大于230分的人
----|------
1 |65 |88 |67 |220 |0
2 |56 |95 |83 |234 |0
3 |96 |86 |86 |268 |1
4 |86 |66 |85 |237 |1
5 |77 |87 |66 |230 |0
6 |86 |57 |75 |218 |0
7 |99 |88 |55 |242 |1
8 |63 |88 |75 |226 |0
求语文>60且总分大于230分的人
=IF(AND(B:B>60,E:E>230),1,0)
B:B,E:E条件列,>60、>230条件
AND函数,多条件与运算
1,0 满足条件则赋值1,不满足则赋值0
###4、Sumifs函数和Countifs函数
用途:多条件求和、多条件计数,数据分类汇总利器
用法:
=sumifs(sum_range,criteria_range1,criteria1,…)
=Sumifs(求和区域,判断区域1,条件1,判断区域2,条件2…)
例:
考号 |语文 |数学 |英语 |总分
----|------
1 |65 |88 |67 |220
2 |56 |95 |83 |234
3 |96 |86 |86 |268
4 |86 |66 |85 |237
5 |77 |87 |66 |230
6 |86 |57 |75 |218
7 |99 |88 |55 |242
8 |63 |88 |75 |226
求各项成绩都大于60分的总成绩
=SUMIFS(E2:E9,B2:B9,”>60",C2:C9,">60",D2:D9,">60")
B2:B9所有语文成绩
">60"条件
。。。数学、英语成绩以及条件
E2:E9求和列

=countifs(criteria_range1,criteria1,…)
=Countifs(判断区域1,条件1,判断区域2,条件2…)
例:

编号年收入
111220
29500
38400
425000
518000
612000
78666
89999
913500

计数年收入大于12000且编号大于5;
=COUNTIFS(J2:J10,">5",K2:K10,">12000")
K2:K10—>所有收入
">12000"条件收入大于12000
J2:J10编号
">5"条件编号大于5
###5、Round函数、INT函数
用途:数值四舍五入和取整函数
用法:
四舍五入 =Round(number, num_digits)
number表示需要进行四舍五入的数值或单元格内容。
num_digits表示需要取多少位的参数。
例:

3.1415926num_digits>0时,表示取小数点后对应位数的四舍五入数值。
  |3.140 
 |num_digits=0时,表示则将数字四舍五入到最接近的整数。
 |3.0000000 
&nbsp;|num_digits< 0时,表示对小数点左侧前几位进行四舍五入。
&nbsp;|0.000 

=ROUND(H1,2)

取整 =INT(数值)
=INT(H1)
###6、Left、Right和Mid函数
用途:字符串的截取
用法:
=Left(字符串,从左边截取的位数)

gansutianshuileft
&nbsp;|=LEFT(H8,5)

=Right(字符串,从右边截取的位数)

gansutianshuileft
 gansu
 right
 =RIGHT(H8,5)

=Mid(字符串,从第几位开始截,截多少个字符)

gansutianshuileft
&nbsp;|gansu
&nbsp;|right
&nbsp;|nshui
&nbsp;|Mid

 |=MID(H8,5,5)

结果:

gansutianshuileft
 gansu
 right
 nshui
 mid
 utian

###7、Datedif函数
用途:日期的间隔计算。
用法:
=Datedif(开始日期,结束日期.“y”) 间隔的年数
=Datedif(开始日期,结束日期.“M”) 间隔的月份
=Datedif(开始日期,结束日期.“D”) 间隔的天数
例:

2015/12/112017/8/28
计算年数差1
计算月数差20
计算天数差626

=DATEDIF(F16,G16,“y”)
=DATEDIF(F16,G16,“m”)
=DATEDIF(F16,G16,“d”)
###8、IFERROR函数
用途:把公式返回的错误值转换为提定的值。如果没有返回错误值则正常返回结果
用法:
=IFERROR(公式表达式,错误值转换后的值)
例:以第一个为例,删除新旧编号对应的一部分,结果如下

旧编号总价值数量新编号
10015845256A0002
100254544#N/A
10032457744A0001
100445454 14#N/A
100542525A0008
1006444196A0004
10074274763A0005
1008247750A0003
1009277274252#N/A
1010242441A0007
10112424244A0006
旧编号新编号
1003A0001
1001A0002
1008A0003
1006A0004
1007A0005
1011A0006
1010A0007
1005A0008

直接使“#N/A”变为空
旧编号 |总价值 |数量| 新编号
—|---
1001 |58452 |56 |A0002
1002 |5454 |4 |
1003 |24577 |44 |A0001
1004 |45454 |14 |
1005 |4252 |5 |A0008
1006 |4441 |96 |A0004
1007 |42747 |63 |A0005
1008| 2477 |50 |A0003
1009 |277274 |252 |
1010 |2424 |41 |A0007
1011 |24242 |44 |A0006
函数:
=IFERROR(VLOOKUP(M:M,R:S,2,FALSE),"")

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值