第187期(106年2月)│行政院人事行政總處公務人力發展中心發行
│發行人:城忠志

各期電子報    訂閱/取消電子報

習藝增能(09)-善用VLOOKUP和ISERROR函數進行人事資料處理....蕭丞舜

尋找並顯示特定內容的查找函數VLOOKUP
 
   搜尋是資訊科技中一個很重要的課題,尤其是在網際網路發展出來之後,它可以說是最重要且關鍵的技術之一。尤其是在龐大的數位資料庫內找出某一特定資料時,若不利用搜尋功能而採人工檢視,將徒費許多寶貴的時間。目前多數的應用軟體都會提供搜尋的功能,以便快速找到使用者所需的特定資料。MS Excel也不例外,此一功能稱為「尋找」,適合用來僅僅要找出特定資料並檢視之。

   操作方法很簡單,切換到常用面版,依序點選「尋找與選取」>「尋找」,隨即會開啟「尋找與取代」的視窗。在「搜尋目標」的欄位內輸入與目標資料相關的關鍵字之後,按一下「找下一個」鈕,當發現有相關資料,作用儲存格就會切換到該處;如無,就會顯示找不到的對話視窗。(另一個開啟「尋找與取代」的方法,就是按快速鍵組合:Ctrl + F。此組快速鍵在多數的應用軟體都可使用,大家不妨多加利用。)

  只是,如果希望找到某一筆資料並在目標儲存格內顯示該資料的特定部分,此法就不合用,此時就是VLOOKUP上場的機會到了。VLOOKUP的原理就是擇定某一目標資料(例:身分證統一編號)為搜尋的依據,並根據需求而顯示該筆資料的特定欄位。以範例檔案為例,想知道「鳳丑」的薪資總額並顯示在I2儲存格內,作法就是在I2輸入:

  =VLOOKUP("鳳丑",A:G,7,0)

  第一個引數「"鳳丑"」就是搜尋的基準資料,除了文字之外也可以是數字、參照儲存格等;第二個引數「A:G」就是搜尋的範圍,要注意的是起始的欄位必須包含搜尋的基準資料(也就是鳳丑所在的那一欄必須是欄位範圍的起始欄),終止欄位至少要到所欲顯示的目標資料欄位為止。第三個引數的意義是當找到鳳丑時,要顯示鳳丑這一筆資料內的第7個儲存格內容;最後一個引數「0」的意義是搜尋資料時,必須找到一模一樣的資料才算數,任何的差異都會視為不同(包括空白)。反之,如果引數用的是「1」,那麼會在找到最接近的資料時就會認定已找到目標並顯示指定的內容。

  要注意的是,VLOOKUP的尋找方式是由上而下,顯示資料則是由左至右。而最後一個引數也可改用TRUE和FALSE,其意義如下:

  TRUE或省略(與上述的1同義):表示找出與首欄中最接近的內容。
  FALSE(與上述的0同義):表示僅尋找完全符合的內容。

  若有一以MS Excel建立的管制名冊或常用簡易資料庫,就可利用VLOOKUP設計一簡易搜尋機制,只要輸入預設欄位的內容(例如:名字),就會顯示所需的資料,如管制名冊或資料庫內無相關內容,也會顯示找不到的訊息(#N/A),在極短的時間內就可知道有無所需資料。

  此外,如果有兩份資料不知內容是否一致,此時亦可使用VLOOKUP來檢查。例如:檢核人事單位產製的待遇校對清冊和出納所提供的薪資清冊,所列的人名是否完全一致。只要在兩份清冊的姓名旁新增一欄位並以VLOOKUP來尋找並顯示另一清冊內的名字即可。顯示為#N/A的就表示目標(來源)清冊沒有來源(目標)清冊的資料。

  另外一種常用的狀況是,建立一特定的管制名冊時需要已建檔資料中的某些資料,例如要建立因公出國管制名冊,需要職名冊內的人員身分證統一編號、官職等、職稱等資料時,就可利用VLOOKUP將資料擷取至新的清冊內,省去建構重複資料的麻煩。

判斷運算結果是否錯誤的ISERROR函數

  VLOOKUP的顯示結果#N/A,固然可讓使用者知道這是尋找不到目標資料的結果,惟,此一顯示結果卻不能更改成使用者想要的文字或數值,亦不能進行任何的運算、統計,更不能透過設定格式化條件,使之在找不到目標時,自動變更顏色,在實務應用上常有不便之處。針對這種情形,可搭配ISERROR和IF函數,以巢狀結構來解決。

  ISERROR,從字面上看,它就是用來判斷運算結果是否為錯的函數。如果運算結果是錯的(例如VLOOKUP找不到目標物),經ISERROR確認後真的是錯誤,就會顯示為TRUE(意思是「VLOOKUP找不到目標物」這件事是真的),若顯示為FALSE,表示運算的結果是對的(「VLOOKUP找不到目標物」這件事是錯的,也就是VLOOKUP已找到目標物)。

  ISERROR的引數只有一個,就是將整個計算式或函數,以上面的例子來說,撰寫的結果如下:

  =ISERROR(VLOOKUP("鳳丑",A:G,7,0))

  顯示的結果是FALSE,因為在範例內確實有鳳丑的資料。如果將「丑」改為「翔」,得到的結果將是TRUE,因為VLOOKUP尋找的範圍內根本就沒有鳳翔,這個錯誤結果,已由ISERROR加以確認無誤。

  ISERROR適用的對象不僅僅是VLOOKUP,其他無法運算的狀況也可使用,例如分母(除數)為0、文字除以數字這種不存在的數值或者其他函數運算結果不存在、不正確等,均可透過ISERROR來確認。

VLOOKUP、ISERROE與IF

  ISERROR固然能夠協助判斷VLOOKUP執行後的結果為何,且這個結果可以透過設定格式化條件讓所在的儲存格變更顏色,惟仍無法解決任隨己意顯示出特定結果的問題。此時當然要透過IF來幫忙。

  將ISERROR(VLOOKUP("鳳翔",A:G,7,0))當作IF的第一個引數(邏輯判斷式),意思就是「VLOOKUP找不到鳳翔是真的」,當這個邏輯判斷是成立時,就顯示為「找不到」(第二個引數)。要是「VLOOKUP找得到鳳翔(找不到鳳翔的條件不成立)」,就顯示鳳翔那一筆資料的第7個儲存格資料(邏輯判斷式不成立時要顯示的結果,就是第三個引數),所以撰寫的內容就如下(紅色和紫色的部份各表示IF的第一個和第三個引數):

  =IF(ISERROR(VLOOKUP("鳳翔",A:G,7,0)),"找不到",VLOOKUP("鳳翔",A:G,7,0))

  透過這樣的方式就可讓VLOOKUP在找不到資料時,顯示為使用者需要的內容。這個內容也可以是數值,便於統計、排序或計算。這樣的判斷式適合用在兩份資料互相比對內容是否一致時使用,再配合設定格式化條件的功能、等號運算子(=),就能建構出一個數位檢核機制,省去人工比對的麻煩。

 

(作者為臺北市立南湖高級中學人事室組員)  

 

 

 

 

 

 

 
民國86年1月創刊,95年2月改版為電子版  
10660臺北市大安區新生南路3段30號 電話:(02) 8369-1399 轉 8308 研究組