巧用EXCEL的If和IsError函數來消除VLOOKUP函數的錯誤值

本文轉自:星雲電腦教程www.gonet8.com----大家都知道EXCEL是微軟公司的OFFICE產品，EXCEL一經推出就得到很多經常處理大量數據的人士的喜愛，主要是因為它的快捷和自動計算的功能，特別是他提供了大量的函數，讓我們能夠十分方便的使用！----例如：VLOOKUP函數就是一個十分好的應用函數，它主要是用來計算如獎金分配等工作的，為我們減少了很多的麻煩和一些不必要的錯誤，只要您的條件值是正確的，他保證能夠讓您得到準確無誤的值，今後只要您的條件值有所改動，VLOOKUP函數馬上就會更新您的所有值。好了，言歸正傳！---- VLOOKUP函數---- 語法----VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)---- Lookup_value為需要在數據表第一列中查找的數值。---- Table_array為需要在其中查找數據的數據表。可以使用對區域或區域名稱的引用。---- Col_index_num 為table_array中待返回的匹配值的列序號。---- Range_lookup為一邏輯值，指明函數VLOOKUP返回時是精確匹配還是近似匹配。如果為 TRUE或省略，則返回近似匹配值。----首先，我們看看下面的這個表（表1）----這是一個編號和獎金分配的表，本例中獎金是隨著編號的固定數值的不同而改變，而且任何不在此編號內的數據都將視為不合格產品，不能給獎金！如20和25這兩個值，獎金分別為100和60，如果編號是21、22、23、24那麼就不能得到獎金！---- 表（1）編號 獎金5 5010 11015 12020 10025 60----第一步我做了一個VLOOKUP函數，讓獎金與編號掛鉤，首先，看看我們的工資表是如何使用VLOOKUP函數的，見表（2）這是一個EXCEL數據表，它VLOOKUP需要一個主表[表（2）] 和一個條件表 [表（1）]，將他們放在一張表內即可，例如SHEET1內的不同列中即可，我將主表放在A1:E7中，將條件表[表（1）]放在H和I列內，一切準備就緒後，我們就可以將VLOOKUP函數放在相應的單元格中了，即C列中從C2到C7，首先，選擇單元格C2，然後我們點擊工具條中的按鈕，在「查找與引用」里找到「VLOOKUP」函數，點擊確定即可，進入對話框後在：----lookup_value內輸入：B2----table_array內輸入：H:I----col_index_num內輸入：2----range_lookup內輸入：暫時不輸入（空值）即近似匹配值，將在以下詳細介紹。----確定後，單元格C2得到的公式為：「=VLOOKUP(B2,H:I,2)」，直接在單元格中輸入也是可以的！---- 表（2）ABCDE1姓名編號獎金基本工資合計2張一3#N/A100#N/A3李二211001302304王五101101302405大俠151201502706小蝦201001602607老闆2560250310----然後，使用EXCEL的「自動填充」功能來填入下面5個數據，填充的結果如[表（2）]，只要你改變「條件表」[表（1）]的值，[表（2）]數值將馬上進行改變。這樣就實現了表格的自動化，但是有一點你可以看到這個表格有兩個很大的缺陷，首先就是它出現了錯誤值#N/A，這個錯誤值代表的意思是：「除以了0」；其次「=VLOOKUP(B2,H:I,2)」這個公式是一個近似匹配值，即20和25之間的任意值獎金都為100，如本例的單元格B3它的值為：21，就得到獎金100（參看[表（1）]）。而本例的要求是：不在編號內的數據，都將視為不合格產品，且不能給獎金！即C3的值必需為「0」，不應該是「100」，否則將導致合計數據為230而不是130元，產生錯誤！怎樣才能改正這兩個錯誤的發生呢？---- 這就是我要做的第二步，選用另兩個函數----ISERROR和IF函數，ISERROR函數是一個測試錯誤的函數，它的語法是：---- ISERROR值為任意錯誤值（#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?或#NULL!）。如果您的測試值為錯誤的時候，當前得到的值為「TRUE」，否則將為「FALSE」。----舉例：如果有一個單元格「B9」是一個公式為：「＝2/0」回車後，它將成為一個錯誤值即「#DIV/0!」，用以告訴我們任何值不可以除零！在單元格「A9」內輸入公式「=ISERROR(B9)」回車後「A9」的值為：「TRUE」，表示測試結果是「真」，如果再次改變「B9」的公式為：「＝2/2」回車後給公式變為「1」，我們會發現同時「A9」的值也發生了變化，變為：「FALSE」。----在本例中公式「VLOOKUP(B2,H:I,2)」相當於上例中的「B9」單元格，現在我們看看如下兩個公式：----①「=ISERROR(VLOOKUP(B2,H:I,2))」←近似匹配值---- ②「=ISERROR(VLOOKUP(B2,H:I,2,FALSE))」← 精確匹配值----上述兩個公式，得到的值是不同的，即①得到的兩個值（20和25）之間的值如21得到的是FLASE，這就與我們的特定值[表（1）]規定的「任何不在此編號內的數據都將視為不合格產品，不能給獎金！」產生了衝突，所以只能強制讓公式得TRUE，即只能用②這個公式，讓VLOOKUP函數精確匹配。這樣C2和C3的值都為「TRUE」我們的目的就達到了！----最後一步就是使用IF函數，----它顯然是一個條件函數，語法----IF(logical_test,value_if_true,value_if_false)---- Logical_test 計算結果為 TRUE或FALSE 的任何數值或表達式。---- Value_if_true Logical_test 為TRUE時函數的返回值。---- Value_if_false Logical_test為FALSE時函數的返回值。----「Logical_test」的值就是在第二步中，說的②精確匹配公式 「----Value_if_true」這個值添入：「 "0"」，即值公式②的值等於TRUE時。---- 「Value_if_false」這個值添入：「VLOOKUP(B2,H:I,2) 」，即值公式①的值等於FALSE時。----OK單元格「C2」最終的公式得到了，如下：----「=IF(ISERROR(VLOOKUP(B2,H:I,2,FALSE)),"0",VLOOKUP(B2,H:I,2))」最後使用「自動填充」功能，向下拖動即可得到相應的數值，見[表（3）]---- 表（3）姓名編號正確獎金錯誤獎金基本工資錯誤合計正確合計張一30#N/A100#N/A100李二210100130230130王五10110110130240240大俠15120120150270270小蝦20100100160260260老闆256060250310310----通過這個公式我們能夠認識到EXCEL的強大數據處理能力，並由此讓您對EXCEL的函數有進一步的了解，在實際工作中充分利用它的內置函數方便自己的工作！
推薦閱讀：