目录
VLOOKUP:数据查询
函数解释
vlookup(lookup_value, table_array, col_index_num,range_lookup)
lookup_value:需要查找的数值
table_array:需要在其中查找数据的单元格区域
col_index_num:在table_array区域中待返回的匹配值的列序号
range_lookup:如果为TUER或1或省略,则返回近似匹配值,如果为FALSE或0,则返回精确匹配值,如果找不到,则返回错误值#N/A
说明:函数写完后使用快捷键ctrl+shift+enter组合键结束。
用法一:查找数据
目的是通过NAME列查找AGE
=VLOOKUP(E2,B2:C5,2,FALSE)
说明:会将E2和B列(table_array中第一列)进行精准查找,查找到后将查找区域中第2列(也就是C列)显示在单元格中
如果B列中有多个参数和E2中相同,只返回匹配到的第一条数据
用法二:反向查找
目的是通过NAME列反向查找到前面列的CD
=VLOOKUP(E2,IF({1,0},B2:B5,A2:A5),2,FALSE)
说明:正常情况下如果需要用NAME查找CD,需要NAME列在第一列,所以需要IF函数辅助,IF({1,0},B2:B5,A2:A5)返回的结果为{"张三",001; "李四",002; "王五",003; "张三",004},相当于使用if制造了一个NAME为第一列的区域值
如果B列中有多个参数和E2中相同,只返回匹配到的第一条数据
用法三:跳过空格查找
目的是无视table_array区域或lookup_value中空格开头的数据
=VLOOKUP(TRIM(F3),IF({1,0},TRIM(B:B),TRIM(C:C)),2,0)
说明:由于字符串中含有空格,而导致返回结果为错误值#N/A,0在这里等同于FALSE,其中B:B代表查询B列所有数据,其他同理
用法四:双条件查找
目的是通过多个条件去查找
=VLOOKUP(E2&F2,IF({1,0},A:A&B:B,C:C),2,FALSE)
说明:如果有需要使用多个条件进行查询,可以使用&把多个条件连接起来,其中A:A代表查询A列所有数据,其他同理
用法五:判断一列数据是否在另一列中出现过
=VLOOKUP(F2,$B$2:$C$5,2,FALSE)
说明:如果数据不存在,会显示#N/A,如果存在,显示公式中要求的数据,其中$符号的作用是在下拉自动填充单元格时,不会改变数据
用法六:整行查找
用于根据第一列内容,匹配整行的数据
=VLOOKUP($E$2,$A:$C,COLUMN(B1),FALSE)
说明:COLUMN(B1)相当于查询B1单元格所在列,会返回2,但是COLUMN(B1)在向右自动填充单元格时,B1会变成C1、D1,这样,返回的数字就会自动变成2,3,4,方便自动填充整行数据
用法七:通配符查找
类似于模糊查询,使用通配符代替某些不确定的内容
=VLOOKUP("*"&E2&"*",A2:C5,2,FALSE)
说明:通过&符号,把"*"符号和E2单元格结合,"*"可以匹配任意文字内容
用法八:区间查询
=VLOOKUP(B2,$F$2:$G$5,2,TRUE)
说明,TRUE代表近似匹配,会自动判断B2中的数字和F列大小的比较,然后根据2返回对应G列的内容
SUBSTITUTE:文本替换
函数解释
用于在文本字符串中用新文本替换旧文本。
SUBSTITUTE(text,old_text,new_text,instance_num)
text:需要替换其中字符的文本,或对含有文本的单元格的引用
old_text:要替换的旧文本
new_text:替换old_text的新文本
instance_num:数值,用来指定以new_text替换第几次出现的old_text,为空代表全部替换
用法:
=SUBSTITUTE(C30," ","")
说明:函数会把第二个参数里的文本替换成第三个参数里的文本,我的案例是把空格替换成了无文本,相当于去掉空格
数据截取:MID、LEFT、RIGHT
函数解释
mid、left、right分别用于从中间、左侧、右侧提取文本中的指定长度内容。
MID(text,start_num,num_chars)
LEFT(text, [num_chars])
RIGHT(text, [num_chars])
text:待提取的文本字符串
start_num:开始提取的字符位置数
num_chars:从文本中提取的字符数
[num_chars]:不填默认为1,代表提取的字符数。
MID用法
=MID(B29,2,1)
说明:从第二个字开始,取一个字
LEFT用法
=LEFT(B29,2)
说明:从左侧开始,取两个字
RIGHT用法
=RIGHT(B29,2)
说明:从右侧开始,取两个字
FLOOR:向下取整
函数解释
“向下取整”,或者说“向下舍入”、“向零取舍”,即取不大于significance的最大数
FLOOR(number, significance)
number:需要计算的数值
significance:要舍入到的倍数
用法
=FLOOR(1.4,0.5)
说明:因为1<1.4<1.5,所以会被向下舍去
=FLOOR(1.9,0.5)
说明:因为1.5<1.9<2.0,所以向下取舍为1.5