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),"")
扫一扫
612
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
