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

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

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

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

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

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

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

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

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

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

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

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

13、,value_if_true結果為TRUEt,函數返回的值；value_if_false結果為FALSEt,函數返回的值；value_if_true和value_if_false都可以是其他的公式，也可以是IF函數的嵌套 進行多級判斷,嵌套不能超過7 層。當邏輯表達式包含有函數的時候，可以使用ISERROR®數來判斷函數返回的結果是否是錯誤值(包括#N/A、#VALUE! #REF! #DIV/0!、#NUM!、#NAME減#NULL!>ISERROR題數的格式為ISERROR(valu吸中參數value為要檢測的值。例4:在例1的基礎上，如果找到卡片號，則在BOOK1的SHE

14、ETS N列的相應 行處顯示 “相符 ”, 否則顯示 “未發現 ”。解決辦法:在BOOK1的SHEET的N2單元格處輸入如下的公式：=IF(ISERROR(MATCH(B2, “BOOK1.XL6 !NAME1,0)=FALSE未發現”在實際的工作中,可能會先判斷一下某個單元格中的數值(數字、文本或引用)在另外的一個工作簿中是否存在相應的匹配數值,如果找到匹配值則進行下一步的計算,如果沒有可能要以某些值代替。在這種情況下,我們就可以配合使用多個函數來實現這個目的。下面我們通過一個例子進行說明。例5:從BOOK2/SHEET件查找與BOOK1/SHEET的卡片號相匹配的行，并將 該行中 購置日期”列(列標為F)的數值顯示在BOOK1/SHEET的G列中相應的單 元格中，要求如果沒有找到匹配的數值，則在BOOK1/SHEET的G列的相應單元格 中填寫 “” 。我們可以進行如下的操作:(1)在G2xxxx輸入如下的公式：=IF(ISERROR(MATCH(B2,BOOK2.XLSSHEET1!$B$2:$B$1000,0)=FALSE,VLOOKU P(B2,BOOK2.XLSSHEET1!$B$2:$F$1000,5,FALSE),“”)(2)選中G2單元格，將鼠標指針移動至該單元格右下角的黑點處，指針變為小 黑十字形狀時，按住左鍵，拖動鼠標至G80單元格,對應的購置日期會