vlookup,、countif,、substitute、match四個(gè)函數(shù)的使用方法(2007-08-16 17:21:56)
今天kelly說要在list里把已經(jīng)confirm refresh
的用戶刪除然后選出200個(gè)下批要做refresh 的用戶。對(duì)于在list里把已經(jīng)confirm refresh 的用戶找出來我一開始想用的函數(shù)是vlookup,，可是用了以后不清楚為什么值出不來,。請(qǐng)教Kevin時(shí)他給了我兩個(gè)函數(shù)，一個(gè)是countif,，一個(gè)是substitute,，結(jié)果我查了一下，這兩個(gè)函數(shù)都弄不出來我想要的結(jié)果（呵呵,，不是我不會(huì)用這兩個(gè)函數(shù),，就一定是我跟他溝通有問題，以后想想怎么溝通吧）,，是后只好請(qǐng)教資深工程師畢總,，他說讓用match函數(shù)。結(jié)果真的得到了我想要的結(jié)果,。
下面就把這四個(gè)函數(shù)都介紹一下：
vlookup（查找與引用函數(shù)）
作用是在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值,，并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值。
在 VLOOKUP 中的
V 代表垂直,。
格式：
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Lookup_value
為需要在數(shù)組第一列中查找的數(shù)值,。可以是數(shù)值,、引用或文本字符串,。
Table_array
為需要在其中查找數(shù)據(jù)的數(shù)據(jù)表?？梢允褂脤?duì)區(qū)域或區(qū)域名稱的引用,，例如數(shù)據(jù)庫或列表。第一列中的數(shù)值可以為文本,、數(shù)字或邏輯值,。
Col_index_num 為 table_array 中待返回的匹配值的列序號(hào)。
（Col_index_num 為 1 時(shí),，返回 table_array
第一列中的數(shù)值,；col_index_num 為 2，返回 table_array 第二列中的數(shù)值,，以此類推,。）
Range_lookup 為一邏輯值，指明函數(shù) VLOOKUP 返回時(shí)是精確匹配還是近似匹配,。
如果為 TRUE 或省略,，則返回近似匹配值，也就是說,，如果找不到精確匹配值,，則返回小于 lookup_value
的最大數(shù)值,；
如果 range_value 為 FALSE,，函數(shù) VLOOKUP 將返回精確匹配值,。如果找不到，則返回錯(cuò)誤值
#N/A,。
在這個(gè)函數(shù)里要注意的是對(duì)于查找數(shù)據(jù)的區(qū)域要用$進(jìn)行絕對(duì)限制,。
記得原來在DBY工作時(shí)是用這個(gè)函數(shù)進(jìn)行數(shù)據(jù)引用然后進(jìn)行訂單處理,（如果在表1里把商品的原始資料錄入后在每個(gè)商品前面加上商品編碼即貨號(hào)，然后在表2里用vlookup函數(shù)以貨號(hào)為索引引用商品的基礎(chǔ)信息如品名,、規(guī)格,、單位、單價(jià),，這樣在數(shù)量上輸入相應(yīng)數(shù)值,，就可以進(jìn)行訂單的價(jià)格匯總。)可是現(xiàn)在用的很少了,。
countif（統(tǒng)計(jì)函數(shù)）
作用是計(jì)算區(qū)域中滿足給定條件的單元格的個(gè)數(shù),。
格式：
COUNTIF(range,criteria)
Range
為需要計(jì)算其中滿足條件的單元格數(shù)目的單元格區(qū)域。
Criteria
為確定哪些單元格將被計(jì)算在內(nèi)的條件,，其形式可以為數(shù)字,、表達(dá)式或文本。文本和表達(dá)式需要用""括起來,。
例如,，條件可以表示為 32、"32",、">32" 或 "apples",。
在工作中可以用這個(gè)公式來計(jì)算需要做refresh 的每個(gè) PC model有多少臺(tái)，
例如：COUNTIF(B2:B6000,"LT1")返回的是LT1所在單元格的個(gè)數(shù),，也就能得出有多少臺(tái)LT1要做refresh了,，而這里"LT1"也可以用單元格位置來代替,不用加""號(hào)
substitute（替代函數(shù)）
作用是在文本字符串中用 new_text 替代
old_text。如果需要在某一文本字符串中替換指定的文本可以使用函數(shù) SUBSTITUTE,。
格式：
SUBSTITUTE(text,old_text,new_text,instance_num)
Text 為需要替換其中字符的文本,，或?qū)形谋镜膯卧竦囊谩?br>
Old_text 為需要替換的舊文本。
New_text 用于替換 old_text 的文本,。
Instance_num 為一數(shù)值,，用來指定以 new_text 替換第幾次出現(xiàn)的
old_text。
如果指定了 instance_num,，則只有滿足要求的 old_text 被替換,；
否則將用 new_text 替換 Text 中出現(xiàn)的所有 old_text。
例如：
A1單元格里的內(nèi)容是“NC6400-LT1”,，而我要把“NC6400-LT1”里第1次出現(xiàn)的0改成4,，
即A1單元格的內(nèi)容改成“NC6440-LT1”，
公式為：SUBSTITUTE(A1,0,4,1)
match（匹配函數(shù)）
作用是返回在指定方式下與指定數(shù)值匹配的數(shù)組中元素的相應(yīng)位置。查找文本值時(shí),，函數(shù) MATCH 不區(qū)分大小寫字母,。
格式：
MATCH(lookup_value,lookup_array,match_type)
Lookup_value
為需要在數(shù)據(jù)表中查找的數(shù)值?？梢詾閿?shù)值（數(shù)字,、文本或邏輯值）或?qū)?shù)字、文本或邏輯值的單元格引用,。
可以包含通配符,、星號(hào) (*) 和問號(hào) (?)。星號(hào)可以匹配任何字符序列,；問號(hào)可以匹配單個(gè)字符,。
Lookup_array 可能包含所要查找的數(shù)值的連續(xù)單元格區(qū)域。Lookup_array
應(yīng)為數(shù)組或數(shù)組引用,。
Match_type 為數(shù)字 -1,、0 或
1
為 1，函數(shù) MATCH 查找小于或等于 lookup_value
的最大數(shù)值
為 0,，函數(shù) MATCH 查找等于 lookup_value
的第一個(gè)數(shù)值
為 -1,，函數(shù) MATCH 查找大于或等于 lookup_value
的最小數(shù)值。
如果省略 match_type,，則假設(shè)為
1,。
例如：文章開頭需要做的工作用MATCH('Sheet5'!Z2,Confirmed!D:D,0)即可以完成。
表示的是找出在Confirmed!工作表中與含Sheet5工作表中Z2內(nèi)容的相匹配的單元格位置,。
如果不匹配會(huì)出現(xiàn)#N/A,，然后用篩選的方式把不等于#N/A的單元格找出來刪除就能得出不匹配的對(duì)象。
|
轉(zhuǎn)載
