<em id="pj4oa"><acronym id="pj4oa"></acronym></em><button id="pj4oa"><object id="pj4oa"></object></button>
<progress id="pj4oa"><track id="pj4oa"><rt id="pj4oa"></rt></track></progress>

    <dd id="pj4oa"></dd>
    <th id="pj4oa"></th>

    返回首頁
    當前位置: 主頁 > Excel教程 > Excel2007教程 >

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

    時間:2012-02-22 14:31來源:Office教程學習網 www.tin22.com編輯:麥田守望者

    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,如表(1)

    編號
    獎金
    5
    50
    10
    110
    15
    120
    20
    100
    25
    60
    表(1)

    如果編號是21、22、23、24那么就不能得到獎金!  

      第一步我做了一個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)",直接在單元格中輸入也是可以的!  
        

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

       然后,使用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
    0
    #N/A
    100
    #N/A
    100
    李二
    21
    0
    100
    130
    230
    130
    王五
    10
    110
    110
    130
    240
    240
    大俠
    15
    120
    120
    150
    270
    270
    小蝦
    20
    100
    100
    160
    260
    260
    老板
    25
    60
    60
    250
    310
    310
    表(3)


      通過這個公式我們能夠認識到EXCEL的強大數據處理能力,并由此讓您對EXCEL的函數有進一步的了解,在實際工作中充分利用它的內置函數方便自己的工作!

    ------分隔線----------------------------
    標簽(Tag):excel excel2007 excel2010 excel2003 excel技巧 excel教程 excel實例教程 excel2010技巧
    ------分隔線----------------------------
    推薦內容
    猜你感興趣
    五月婷婷福利