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
旧编号 | 新编号 |
---|---|
1003 | A0001 |
1001 | A0002 |
1008 | A0003 |
1006 | A0004 |
1007 | A0005 |
1002 | A0006 |
1009 | A0007 |
1011 | A0008 |
1004 | A0009 |
1010 | A0010 |
1005 | A0011 |
更换旧编号为新编号
=VLOOKUP(A:A,E:F,2,FALSE)
在E:F列中查找A:A列中的值是否存在,存在则赋值为E:Fd第二列的值。
旧编号 | 总价值 | 数量 | 新编号 | 旧编号 | 新编号 |
---|---|---|---|---|---|
1001 | 58452 | 56 | A0002 | 1003 | A0001 |
1002 | 5454 | 4 | A0006 | 1001 | A0002 |
1003 | 24577 | 44 | A0001 | 1008 | A0003 |
1004 | 45454 | 14 | A0009 | 1006 | A0004 |
1005 | 4252 | 5 | A0011 | 1007 | A0005 |
1006 | 4441 | 96 | A0004 | 1002 | A0006 |
1007 | 42747 | 63 | A0005 | 1009 | A0007 |
1008 | 2477 | 50 | A0003 | 1011 | A0008 |
1009 | 277274 | 252 | A0007 | 1004 | A0009 |
1010 | 2424 | 41 | A0010 | 1010 | A0010 |
1011 | 24242 | 44 | A0008 | 1005 | A0011 |
以上表为自动完成。
注意以下方法是错误的,在求新编号时,第一行使用=VLOOKUP(A2:A12,E2:F12,2,FALSE)可以得出正确结果,但拖动时会自动增长,导致下面的数据会出现错误,以下是错误的结果。
旧编号 | 总价值 | 数量 | 新编号 | 旧编号 | 新编号 |
---|---|---|---|---|---|
1001 | 58452 | 56 | A0002 | 1003 | A0001 |
1002 | 5454 | 4 | A0006 | 1001 | A0002 |
1003 | 24577 | 44 | #N/A | 1008 | A0003 |
1004 | 45454 | 14 | A0009 | 1006 | A0004 |
1005 | 4252 | 5 | A0011 | 1007 | A0005 |
1006 | 4441 | 96 | #N/A | 1002 | A0006 |
1007 | 42747 | 63 | #N/A | 1009 | A0007 |
1008 | 2477 | 50 | #N/A | 1011 | A0008 |
1009 | 277274 | 252 | #N/A | 1004 | A0009 |
1010 | 2424 | 41 | A0010 | 1010 | A0010 |
1011 | 24242 | 44 | #N/A | 1005 | A0011 |
原因是在拖动时,指向自动增长,下图比较清楚:
###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(判断区域,条件)
例:
编号 | 年收入 |
---|---|
1 | 11220 |
2 | 9500 |
3 | 8400 |
4 | 25000 |
5 | 18000 |
6 | 12000 |
7 | 8666 |
8 | 9999 |
9 | 13500 |
计数年收入大于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…)
例:
编号 | 年收入 |
---|---|
1 | 11220 |
2 | 9500 |
3 | 8400 |
4 | 25000 |
5 | 18000 |
6 | 12000 |
7 | 8666 |
8 | 9999 |
9 | 13500 |
计数年收入大于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.1415926 | num_digits>0时,表示取小数点后对应位数的四舍五入数值。 |
---|
|3.140
|num_digits=0时,表示则将数字四舍五入到最接近的整数。
|3.0000000
|num_digits< 0时,表示对小数点左侧前几位进行四舍五入。
|0.000
=ROUND(H1,2)
取整 =INT(数值)
=INT(H1)
###6、Left、Right和Mid函数
用途:字符串的截取
用法:
=Left(字符串,从左边截取的位数)
gansutianshui | left |
---|
|=LEFT(H8,5)
=Right(字符串,从右边截取的位数)
gansutianshui | left |
---|---|
gansu | |
right | |
=RIGHT(H8,5) |
=Mid(字符串,从第几位开始截,截多少个字符)
gansutianshui | left |
---|
|gansu
|right
|nshui
|Mid
|=MID(H8,5,5)
结果:
gansutianshui | left |
---|---|
gansu | |
right | |
nshui | |
mid | |
utian |
###7、Datedif函数
用途:日期的间隔计算。
用法:
=Datedif(开始日期,结束日期.“y”) 间隔的年数
=Datedif(开始日期,结束日期.“M”) 间隔的月份
=Datedif(开始日期,结束日期.“D”) 间隔的天数
例:
2015/12/11 | 2017/8/28 |
---|---|
计算年数差 | 1 |
计算月数差 | 20 |
计算天数差 | 626 |
=DATEDIF(F16,G16,“y”)
=DATEDIF(F16,G16,“m”)
=DATEDIF(F16,G16,“d”)
###8、IFERROR函数
用途:把公式返回的错误值转换为提定的值。如果没有返回错误值则正常返回结果
用法:
=IFERROR(公式表达式,错误值转换后的值)
例:以第一个为例,删除新旧编号对应的一部分,结果如下
旧编号 | 总价值 | 数量 | 新编号 |
---|---|---|---|
1001 | 58452 | 56 | A0002 |
1002 | 5454 | 4 | #N/A |
1003 | 24577 | 44 | A0001 |
1004 | 45454 14 | #N/A | |
1005 | 4252 | 5 | A0008 |
1006 | 4441 | 96 | A0004 |
1007 | 42747 | 63 | A0005 |
1008 | 2477 | 50 | A0003 |
1009 | 277274 | 252 | #N/A |
1010 | 2424 | 41 | A0007 |
1011 | 24242 | 44 | A0006 |
旧编号 | 新编号 |
---|---|
1003 | A0001 |
1001 | A0002 |
1008 | A0003 |
1006 | A0004 |
1007 | A0005 |
1011 | A0006 |
1010 | A0007 |
1005 | A0008 |
直接使“#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),"")
更多推荐
Excel最常用的函数
发布评论