EXCEL中查找函數(shù)的妙用

1、EXCELx道我函數(shù)的妙用作為一個電子表格處理程序，EXCE除了具有強(qiáng)大的計算和匯總功能外，其內(nèi) 嵌的各類功能函數(shù)在進(jìn)行表格處理中給我們提供了極大的便利,可以極高的提高工作效率和準(zhǔn)確性。下面介紹幾個在實際工作中可能會經(jīng)常用到的查找函數(shù):MATCH。LOOKUP() HLOOKUP() VLOOKUP(這些查找函數(shù)不僅僅具有查 對的功能,同時還能根據(jù)查找的結(jié)果和參數(shù)的設(shè)定得到我們需要的數(shù)值。特別是這幾個函數(shù)的配合使用，并以兩個邏輯函數(shù)IF(邢ISERROR)助，我們就可以在兩 個或多個有一定關(guān)聯(lián)的工作簿中動態(tài)生成新的數(shù)據(jù)列。1.MATCH()格式 :MATCH(lookup_value,look

2、up_array,match_type)從英文含義我們可以看出,它是一個匹配的函數(shù),主要功能是按照 match_type參數(shù)所指定的查找方式,在 lookup_array 參數(shù)所代表的數(shù)據(jù)組中查找lookup_value 參數(shù)代表的數(shù)值,如果查找成功則返回lookup_value 在 lookup_array中的位置,不成功返回錯誤信息#N/A。EXCELS編輯”菜單中提供的 查找”功能只能進(jìn)行單一數(shù)值的查找，而 MATCH()1數(shù)可以實現(xiàn)批量數(shù)值的查找，所需要的只是在需要查找的首個單元格 中定義好該函數(shù),然后拖動該單元格直至到需要的范圍,所有需要查對單元格的查找結(jié)果會自動完成。參數(shù)解釋 :l

3、ookup_value 需要查找的值,可以是實際的數(shù)字、字符串或邏輯值,也可以是某一單元格的引用 ,通常使用單元格的引用 ;lookup_array 查找范圍 ,可以是定義的數(shù)組,也可以是自定義的多個連續(xù)單元格的引用,這個引用我們可以通過兩種方式實現(xiàn):直接輸入連續(xù)單元格的地址，通常使用絕對引用地址，如$B$2:$B$50;選定 一個連續(xù)的單元格區(qū)域后，通過 插入”菜單中 定義”項的名稱”給這段單元格區(qū)域 定義一個名稱，如NAME1這樣在以后所有使用該單元格區(qū)域的時候都可以用定 義的名稱代表。match_type,查找的方式，其值為-1、0、1,默認(rèn)為1。其中-1是在lookup_array查找

4、大于或等于lookup_value 的最小數(shù)值，要求lookup_array必須按降序排列；0是在lookup_array查找等于 lookup_value的第一個數(shù)值，不需要排序;1是在lookup_array查找小于或等于 lookup_value的最大數(shù)值，要求lookup_array必須按升序排列。函數(shù)舉例：例1:有兩個工作簿BOOK1和BOOK2它們的SHEET仲都具有名為 韋片號” 的列,列標(biāo)為B(假設(shè)BOOK1中該列包含數(shù)據(jù)的單元格為 B2:B80,BOOK2包含數(shù) 據(jù)的單元格為B2:B1000)想查對一下BOOK1中的卡片號是否能夠在 BOOK2中的 卡片號中全部查找到。按照以

5、上的需求我們可以進(jìn)行如下的操作 ：(1)定義引用的名稱，拖動鼠標(biāo)選中BOOK2中B2:B1000單元格，通過 插入”菜 單的定義”名稱”項，給其定義為NAME1;(2)在BOOK1中插入一空列，假定列標(biāo)為H,并在H2單元格中輸入如下的公式=MATCH(B2, “BOOK2.XL6 !NAME1,0);(3)選中H2單元格，將鼠標(biāo)指針移動至該單元格右下角的黑點處，此時指針變 為小黑十字形狀，按住左鍵，拖動鼠標(biāo)至H80單元格，此時H2到H80單元格會自動 顯示出B2至B80在BOOK2中定義的NAME1所代表的單元格區(qū)域的位置(如果 找到)，或是#N/A(未找到)；(4)通過 數(shù)據(jù)”菜單的 篩選“

6、自動篩選”功能項，可以查看所有匹配的項(H列 的數(shù)值大于0),或是所有不匹配的項(H列的數(shù)值為#N/A)。以上1、2步驟可以合并為直接在H2單元格中輸入如下的函數(shù)：=MATCH（B2,BOOK2.XLSSHEET1!$B$2:$B$1000,0）需要注意的是，一定要使用$B$2:$B$1000不能使用B2:B1000否則在拖動鼠標(biāo) 時 ,其單元格范圍會隨著發(fā)生變化,產(chǎn)生錯誤的結(jié)果。另外在引用外部工作簿時,如果定義了引用名稱,書寫格式為:工作簿名稱”引用名稱，如“BOOK2.XL6 !NAME果使用表格區(qū)域引用，格式 為:工作簿名稱工作表名稱!表格區(qū)域,如BOOK2.XLSSHEET1!$B$2

7、:$B$10O02.LOOKUP（、） HLOOKUP（、） VLOOKUP（）LOOKUP（） HLOOKUP（） VLOOKUP班數(shù)的功能都是在數(shù)組或表格中查找指 定的數(shù)值 ,并按照函數(shù)參數(shù)設(shè)定得值返回表格或數(shù)組當(dāng)前列（行）中指定行（列）處的數(shù)值。由于LOOKUP數(shù)在單行（列）區(qū)域查找數(shù)值，并返回第二個單行（列）區(qū)域中相 同位置的數(shù)值,或是在數(shù)組的第一行（列）中查找數(shù)值,返回最后一行（列）相同位置處的數(shù)值,其適用范圍具有比較大的局限性,在實際的應(yīng)用中,通常使用更加靈活的HLOOKUP©口 VLOOKUP班數(shù)。HLOOKUP（）口 VLOOKUP（）作用類似，其區(qū)別是HLOOKUP

8、庭表格或數(shù)組的首 行查找數(shù)值,返回表格或數(shù)組當(dāng)前列中指定行的數(shù)值，而VLOOKUP虐在表格或數(shù) 組的首列查找數(shù)值,并返回表格或數(shù)組當(dāng)前行中指定列的數(shù)值。這里所說的表格是按單元格地址設(shè)定的一個表格區(qū)域,如 A2:E8。HLOOKUP（加數(shù)的格式如下：HLOOKUP（lookup_value,table_array,row_index_num,range_looku滲數(shù)解釋:lookup_value需要在表格或數(shù)組第一行中查找的數(shù)值,可以是數(shù)值、字符串或引用 ;table_array 需要在其中查找數(shù)值的表格區(qū)域、數(shù)組或是表格區(qū)域的 引用 ;row_index_num 為 table_array

9、中待返回的匹配值的行序號;3/ 7range_lookup為一邏輯值，為TRUE省略t安值時，要求table_array第一行的數(shù)據(jù)必須升序排列 ,否則會得到錯誤的結(jié)果,同時表示待查找內(nèi)容與查找內(nèi)容近似匹配就可以了,如果不能精確匹配的話,則函數(shù)返回小于lookup_value 的最大數(shù)值;如果為FALS環(huán)需要table_array的數(shù)值進(jìn)行排序，并要求精確匹配，如果沒有 找到則函數(shù)返回#N/A。VLOOKUP陋數(shù)的格式如下：VLOOKUP（lookup_value,table_array,col_index_num,range_lookup）函數(shù)的參數(shù)中除了 col_index_num 表示

10、table_array 中待返回的匹配值的列 序號外，其他參數(shù)的意義和HLOOKUP值同。函數(shù)舉例 :例2:在BOOK2中，需要根據(jù)每一行中的資產(chǎn)類別（列標(biāo)為D）,自動的計算出該 資產(chǎn)的折舊年限。我們可以進(jìn)行如下的操作:（1）由于在現(xiàn)有的表格當(dāng)中不存在資產(chǎn)類別和折舊年限的對應(yīng)關(guān)系,因此首先要根據(jù)資產(chǎn)分類的情況構(gòu)造出一個數(shù)組,數(shù)組的第一行為資產(chǎn)的類別名稱,第二行為資產(chǎn)所對應(yīng)的折舊年限, 類“別 1” ,類別“ 2” ,類別“ 3” ,類別“ 4” ,類別“5” ;4,6,7,8,10;（2）在BOOK2的SHEET仲插入一空列，列標(biāo)為K,在K2單元格中輸入如下的 公式 :=HLOOKUP（D2,

11、 類別“ 1” 類別, “ 2” 類別, “ 3” 類別, “ 4” 類別, “5” ;4,6,7,8,10,2,FALSE）,（3）選中K2單元格，將鼠標(biāo)指針移動至該單元格右下角的黑點處，指針變?yōu)樾?黑十字形狀時，按住左鍵，拖動鼠標(biāo)至K80單元格，折舊年限會自動地顯示出來。例3:按照BOOK1中SHEET的卡片號（列標(biāo)為B）從BOOK2中SHEETH勺型號 列（列標(biāo)為E就取與該卡片號對應(yīng)的內(nèi)容，從而在BOOK1的SHEET仲自動的生成 一個新列（列標(biāo)為U）。解決方法(1)由于是在列中查找匹配的值,需要返回當(dāng)前行制定列的值, 因此適宜使用VLOOKUP新數(shù)，確定需要查找的數(shù)值為從 BOOK1/

12、SHEET的B2到B80,待查找的 范圍為BOOK2/SHEET的B2:E1000返回匹配值白列序號為4。(2)在BOOK1/SHEET的U2單元格，輸入如下的公式：=VLOOKUP(B2,BOOK2.XLSSHEET1!$B$2:$E$1000,4,FALSE)(3)選中U2單元格，將鼠標(biāo)指針移動至該單元格右下角的黑點處，指針變?yōu)樾?黑十字形狀時，按住左鍵，拖動鼠標(biāo)至U80單元格，對應(yīng)的型號會在U列中顯示出 來。3.IF()格式 :IF(logical_test,value_if_true,value_if_false)IF函數(shù)的作用是根據(jù)邏輯判斷的結(jié)果返回相應(yīng)的值參數(shù)解釋 :logical

13、_test 邏輯表達(dá)式,value_if_true結(jié)果為TRUEt,函數(shù)返回的值；value_if_false結(jié)果為FALSEt,函數(shù)返回的值；value_if_true和value_if_false都可以是其他的公式，也可以是IF函數(shù)的嵌套 進(jìn)行多級判斷,嵌套不能超過7 層。當(dāng)邏輯表達(dá)式包含有函數(shù)的時候，可以使用ISERROR®數(shù)來判斷函數(shù)返回的 結(jié)果是否是錯誤值(包括 #N/A、#VALUE! #REF! #DIV/0!、#NUM!、#NAME減#NULL!>ISERROR題數(shù)的格式為ISERROR(valu吸中參數(shù)value為要檢測的值。函數(shù)舉例 :例4:在例1的基礎(chǔ)上，

14、如果找到卡片號，則在BOOK1的SHEETS N列的相應(yīng)行處顯示 “相符 ”, 否則顯示 “未發(fā)現(xiàn) ”。解決辦法 :在BOOK1的SHEET的N2單元格處輸入如下的公式：=IF(ISERROR(MATCH(B2, “BOOK1.XL6 !NAME1,0)=FALSE未發(fā)現(xiàn)”在實際的工作中,可能會先判斷一下某個單元格中的數(shù)值(數(shù)字、文本或引用)在另外的一個工作簿中是否存在相應(yīng)的匹配數(shù)值,如果找到匹配值則進(jìn)行下一步的計算,如果沒有可能要以某些值代替。在這種情況下 ,我們就可以配合使用多個函數(shù)來實現(xiàn)這個目的。下面我們通過一個例子進(jìn)行說明。例5:從BOOK2/SHEET件查找與BOOK1/SHEET的

15、卡片號相匹配的行，并將該行中 購置日期”列(列標(biāo)為F)的數(shù)值顯示在BOOK1/SHEET的G列中相應(yīng)的單 元格中，要求如果沒有找到匹配的數(shù)值，則在BOOK1/SHEET的G列的相應(yīng)單元格 中填寫“” 。我們可以進(jìn)行如下的操作:(1)在G2xxxx輸入如下的公式：=IF(ISERROR(MATCH(B2,BOOK2.XLSSHEET1!$B$2:$B$1000,0)=FALSE,VLOOKUP(B2,BOOK2.XLSSHEET1!$B$2:$F$1000,5,FALSE), “”)(2)選中G2單元格，將鼠標(biāo)指針移動至該單元格右下角的黑點處，指針變?yōu)樾『谑中螤顣r，按住左鍵，拖動鼠標(biāo)至G80單元格,對應(yīng)的購置日期會