excel常用函数大全及示例(二)
1.VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
语法:VLOOKUP(查找值,查找区域,返回查找区域第N列,查找模式)
示例:精确查找叫张华的学生的总成绩VLOOKUP(G2,A2:E4,5,0)
注意:
1.查找区域必须从查找值所在列开始
2.VLOOKUP的错误值处理。
如果找不到数据,函数总会传回一个这样的错误值#N/A,这错误值其实也很有用的。
例如,如果我们想这样来作处理:如果找到的话,就传回相应的值,如果找不到的话,就自动设定它的值等于0,则函数可以写成这样:
=if(iserror(vlookup(1,2,3,0)),0,vlookup(1,2,3,0))
在Excel 2007以上版本中,以上公式等价于
=IFERROR(vlookup(1,2,3,0),0)
2.vlookup多条件查询
1. 辅助列法
把要查询的条件用&符号连接到辅助列中,再使用vlookup函数查询,如下图:
2. 函数连接法
用if函数重组的方法,把多个条件列连接在一起如下:
VLOOKUP(F1&F2,IF({1,0},B2:B6&C2:C6,D2:D6),2,0)
注意:函数连接,输入公式后把光标放在公式最后,按ctrl+shif+enter完成输入,输入成功后公式两边自动添加大括号{ }
3.Upper(b5)小写转大写
4.Proper(b5)首字母大写
5.FREQUENCY函数,分段统计
FREQUENCY 函数计算值在某个范围内出现的频率,然后返回一个垂直的数字数组
示例:
6.MATCH
MATCH函数是一个很强大的辅助函数,
MATCH函数语法为:MATCH(lookup_value,lookuparray,match-type)
lookup_value:表示查询的指定内容;
lookuparray:表示查询的指定区域;
match-type:表示查询的指定方式,用数字-1、0或者1表示
示例:
选择B7单元格,输入“=MATCH(100,B2:B5,0)”,按回车,显示“3”。
公式解析:在“B2:B5”区域内查找等一个等于“100”的数值为第几个,按顺序找到B4单元格的数值为“100”,B4在“B2:B5”区域内排第3,所以显示“3”。
7.INDEX
数组形式——INDEX(array,row_num,column_num)和
引用形式——INDEX(reference,row_num,column_num,area_num)。
示例:
数组形式:获取D2:E6区域第2行第2列的值
引用形式:获取两个数组区域(A2:b6)和(D2:E6)的第1个区域的第2行、第2列的值。
8.Len函数
Len函数返回文本字符串的长度,语法格式=len(text)
LEN("hello world")=11空格也算
9.Substitute函数
SUBSTITUTE函数主要用来对指定字符串进行替换
SUBSTITUTE函数基本语法结构为:SUBSTITUTE(要替换的文本,旧文本,新文本,[替换第几个])
如把我么换成我们:SUBSTITUTE(“我么”,“么”,“们”)
示例:
把手机号中间四位替换成*,起到保护手机号隐私的作用
关注我不迷路,好了,这篇文章到这里就结束了.
每天学一点,薪资翻一番。对excel, vba,数据透视表,数据分析感兴趣的小伙伴欢迎关注公众号“学会数据分析”,
在公众号“学会数据分析”中回复wps可获取带vba版和其他高级版本的免费**版wps