打开APP
userphoto
未登录

开通VIP,畅享免费电子书等14项超值服

开通VIP
vlookup、countif、substitute、match四个函数的使用方法

vlookup、countif、substitute、match四个函数的使用方法

(2007-08-16 17:21:56)
标签:

杂谈

分类: 偷来的知识
    今天kelly说要在list里把已经confirm refresh 的用户删除然后选出200个下批要做refresh 的用户。对于在list里把已经confirm refresh 的用户找出来我一开始想用的函数是vlookup,可是用了以后不清楚为什么值出不来。请教Kevin时他给了我两个函数,一个是countif,一个是substitute,结果我查了一下,这两个函数都弄不出来我想要的结果(呵呵,不是我不会用这两个函数,就一定是我跟他沟通有问题,以后想想怎么沟通吧),是后只好请教资深工程师毕总,他说让用match函数。结果真的得到了我想要的结果。
    下面就把这四个函数都介绍一下:
 
vlookup(查找与引用函数)
    作用是在表格或数值数组的首列查找指定的数值,并由此返回表格或数组当前行中指定列处的数值。
    在 VLOOKUP 中的 V 代表垂直。
格式:
    VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
    Lookup_value    为需要在数组第一列中查找的数值。可以是数值、引用或文本字符串。
    Table_array    为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用,例如数据库或列表。第一列中的数值可以为文本、数字或逻辑值。
    Col_index_num    为 table_array 中待返回的匹配值的列序号。
    (Col_index_num 为 1 时,返回 table_array 第一列中的数值;col_index_num 为 2,返回 table_array 第二列中的数值,以此类推。)
    Range_lookup    为一逻辑值,指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。
    如果为 TRUE 或省略,则返回近似匹配值,也就是说,如果找不到精确匹配值,则返回小于 lookup_value 的最大数值;
    如果 range_value 为 FALSE,函数 VLOOKUP 将返回精确匹配值。如果找不到,则返回错误值 #N/A。
    在这个函数里要注意的是对于查找数据的区域要用$进行绝对限制。 
    记得原来在DBY工作时是用这个函数进行数据引用然后进行订单处理,(如果在表1里把商品的原始资料录入后在每个商品前面加上商品编码即货号,然后在表2里用vlookup函数以货号为索引引用商品的基础信息如品名、规格、单位、单价,这样在数量上输入相应数值,就可以进行订单的价格汇总。)可是现在用的很少了。
 
countif(统计函数)
    作用是计算区域中满足给定条件的单元格的个数。
格式:
    COUNTIF(range,criteria)
    Range      为需要计算其中满足条件的单元格数目的单元格区域。
    Criteria    为确定哪些单元格将被计算在内的条件,其形式可以为数字、表达式或文本。文本和表达式需要用""括起来。
                  例如,条件可以表示为 32、"32"、">32" 或 "apples"。
   
   工作中可以用这个公式来计算需要做refresh 的每个 PC model有多少台,
    例如:COUNTIF(B2:B6000,"LT1")返回的是LT1所在单元格的个数,也就能得出有多少台LT1要做refresh了,而这里"LT1"也可以用单元格位置来代替,不用加""号
 
substitute(替代函数)
    作用是在文本字符串中用 new_text 替代 old_text。如果需要在某一文本字符串中替换指定的文本可以使用函数 SUBSTITUTE。
格式:
    SUBSTITUTE(text,old_text,new_text,instance_num)
    Text    为需要替换其中字符的文本,或对含有文本的单元格的引用。
    Old_text    为需要替换的旧文本。
    New_text    用于替换 old_text 的文本。
    Instance_num    为一数值,用来指定以 new_text 替换第几次出现的 old_text。
                           如果指定了 instance_num,则只有满足要求的 old_text 被替换;
                           否则将用 new_text 替换 Text 中出现的所有 old_text。
例如:
    A1单元格里的内容是“NC6400-LT1”,而我要把“NC6400-LT1”里第1次出现的0改成4,
    即A1单元格的内容改成“NC6440-LT1”,
    公式为:SUBSTITUTE(A1,0,4,1)
 
match(匹配函数)
    作用是返回在指定方式下与指定数值匹配的数组中元素的相应位置。查找文本值时,函数 MATCH 不区分大小写字母。
格式:
    MATCH(lookup_value,lookup_array,match_type)
    Lookup_value    为需要在数据表中查找的数值。可以为数值(数字、文本或逻辑值)或对数字、文本或逻辑值的单元格引用。
                          可以包含通配符、星号 (*) 和问号 (?)。星号可以匹配任何字符序列;问号可以匹配单个字符。
    Lookup_array    可能包含所要查找的数值的连续单元格区域。Lookup_array 应为数组或数组引用。
    Match_type    为数字 -1、0 或 1
                        为 1,函数 MATCH 查找小于或等于 lookup_value 的最大数值
                        为 0,函数 MATCH 查找等于 lookup_value 的第一个数值
                        为 -1,函数 MATCH 查找大于或等于 lookup_value 的最小数值。
                        如果省略 match_type,则假设为 1。
例如:文章开头需要做的工作用MATCH('Sheet5'!Z2,Confirmed!D:D,0)即可以完成。
        表示的是找出在Confirmed!工作表中与含Sheet5工作表中Z2内容的相匹配的单元格位置。
        如果不匹配会出现#N/A,然后用筛选的方式把不等于#N/A的单元格找出来删除就能得出不匹配的对象。
 
本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请点击举报
打开APP,阅读全文并永久保存 查看更多类似文章
猜你喜欢
类似文章
【热】打开小程序,算一算2024你的财运
Excel制表技巧(49)公式及函数B
总结篇--速速来看,函数高手们必备的技能
Excel常用函数公式及技巧(5)
Excel函数应用之查询与引用函数(下)
Excel常用功能大全(详解版)
Excel函数 | 数据分析最常用这16个!
更多类似文章 >>
生活服务
热点新闻
分享 收藏 导长图 关注 下载文章
绑定账号成功
后续可登录账号畅享VIP特权!
如果VIP功能使用有故障,
可点击这里联系客服!

联系客服