巧用EXCEL函數神器，解決實際工作問題

林　騰

?

巧用EXCEL函數神器，解決實際工作問題

林騰

摘要：在日常工作中，計算機已成為一種常用工具，它通過使用各種軟件來完成相關工作，本文從實際使用的角度出發，探討使用EXCEL函數解決日常實際工作問題的方法與途徑。

關鍵詞：EXCEL 函數；匹配；問題

當今世界已進入信息高速發展的時代，在現代的學習、工作及生活中，計算機已經成為人類不可缺少的一種工具，它與各種軟件的完美結合能成為我們工作上的“好幫手”，解決現實工作的實際問題。在各類軟件中， Microsoft Office是目前普及最深、應用最廣的軟件，被廣泛使用于教學、工作、學習及生活各領域，其中EXCEL作為它的的組件之一，具有圖表制作、數據統計、分析及決策等強大功能，是目前最流行的電子表格系統，進行數據處理和分析的軟件工具。

一、EXCEL的使用現狀

EXCEL在使用上具有界面友好，容易學習，可操作性強等特點，然而，在現實的使用領域中，由于使用者對EXCEL的認知不夠深入，EXCEL大多只被用做一些簡單的事情，例如：用來制作表格(EXCEL的表格功能確實比WORD制表要強大，相對復雜的表格制作起來也十分容易)，數據處理(大都是如：輸入數據、設置數據的格式、SUM、AVERAGE等簡單計算)，對使用EXCEL函數解決一些綜合應用，了解甚少。

二、EXCEL的函數介紹

在日常辦公中，使用最多的是數據處理。EXCEL函數是EXCEL處理數據的一個重要手段，它是能夠完成特定功能的程序，是系統預定義的一些公式，它們使用一些稱為參數的特定數值按特定的順序或結構進行計算，然后把計算的結果存放在某個單元格中。EXCEL函數一共有11類，分別是數據庫函數、日期與時間函數、工程函數、財務函數、信息函數、邏輯函數、查詢和引用函數、數學和三角函數、統計函數、文本函數以及用戶自定義函數。通過熟悉和掌握EXCEL函數的使用，對解決實際工作問題將有很大的幫助，可大大提高工作效率。本文將用實例來展示EXCEL在實際工作中的綜合應用。

三、使用EXCEL函數解決實際問題

在管理工作中經常需要查找既定條件的數據，如果是在同一張表格內簡單的查找，用排序、篩選或IF函數就可解決問題，但如果在不同表格中查找數據，用上述的方法就會稍為困難。這時可使用查詢和引用函數中的VLOOKUP函數解決此問題。

(一)VLOOKUP函數

VLOOKUP函數定義：在表格或數值數組的首列匹配查找指定的數值，并由此返回表格或數組當前行中指定列處的數值。其含義可理解為指定條件在指定區域垂直方向查找數據。

函數格式：VLOOKUP(lookup_value，table_array，col_index_num，range_lookup)

格式解釋：VLOOKUP(要查找的內容，搜索的區域，從查找區域首列開始到要找的內容的列數，近似匹配還是精確匹配查找方式)

參數定義：

Lookup_value需要在數組第一列中查找的數值，可以為數值、引用或文本字符串；.Table_array為需要在其中查找數據的數據表，可以使用對區域或區域名稱的引用；

Col_index_num為table_array中待返回的匹配值的列序號；

Range_lookup為一邏輯值，指明函數VLOOKUP返回時是精確匹配還是近似匹配，如果為TRUE或省略，則返回近似匹配值；如果range_value為FALSE，函數VLOOKUP將返回精確匹配值，如果找不到，則返回錯誤值#N/A.。

(二)實例演示

例1:利用信息平臺給部分職工發送工作信息，需要在職工通訊錄文件找出符合的職工。

圖1　Sheet 1職工通訊錄

圖2　Sheet 2要查找的內容

圖3　Sheet 2使用VLOOKUP函數結果

解決問題過程：

1.全體職工聯系信息存放于“職工通訊錄.xls”文件的表1中(即Sheet1)，如圖1所示，要在Sheet2的B列、C列中查找相關人員的“移動電話”及“部門”信息，如圖2所示。

2.在Sheet2！B2單元格輸入函數：=VLOOKUP(A2， Sheet1!$B$2:$E$7，4，0)，確認后可獲相應數據，其中“Sheet1!”是引用的工作表名稱；“$B$2:$E$7”是在Sheet1數據的查找范圍；“4”查找到與A2(“陳一”)相同的數據后顯示第4列的內容；“0”表示使用精確匹配。余下的B3、B4單元格用填充方式完成。

3.在Sheet2！C2單元格輸入函數：=VLOOKUP(A2，Sheet1!$C$2:$E$7，2，0)，確認后可獲相應數據，C3、C4單元格用填充方式完成。最終結果如圖3所示。

需要注意的是，查找數據的范圍要定義好，最好使用絕對地址$B$2:$E$7，不能使用B2:E7，否則在拖動鼠標填充時，其單元格范圍會隨著發生變化，產生錯誤的結果。另外如果在引用外部工作簿或其他工作表時，必須定義好引用名稱，還有就是要注意數據源是否有重復的數據，例如姓名相同的情況，若有應提前處理，如可加入標識符以便區別。

例2：工資管理中，工資總表由Sheet1匯總表、Sheet2加班費、Sheet3獎金等表組成，需要將其他各分項表數據匯總統計，除了基本工資外，并不是每人在各分項表都有數據，例如：“Sheet2加班費”表中“趙二”、“李四”等數據為0(如圖4所示)，“Sheet3獎金”表中，只有部分員工有數據，如何將各分項表的內容加入到匯總表中。

圖4　Sheet 2加班費

圖5　Sheet 3獎金

圖6　Sheet1-初步匯總結果

圖7　Sheet1-最終匯總結果

解決問題過程：

1.各分項表的數據最終都集中到Sheet1即匯總表中匯總，因此應在Sheet1所需填入數據的單元格進行數據的提取，即尋找各分項表中的所需數據，仍然可使用VLOOKUP函數。

2.提取加班費：如圖6所示，在Sheet1中E2單元格輸入函數：=VLOOKUP(B2，Sheet2加班費!B:D，3，0)，確認后可獲相應數據，其中“Sheet2加班費!”是引用的工作表名稱；“B:D”是將數據的查找范圍固定在B至D列；“3”查找到與B2(“陳一”)相同的數據后顯示第3列的“加班費”內容；“0”表示使用精確匹配。余下的E3至E7單元格用填充方式完成。

3.提取獎金：在Sheet1中F2單元格輸入函數：=VLOOKUP(B2，Sheet3獎金!B:D，3，0)，確認后可獲相應的獎金數據，參數說明與上一點相同，余下的F3至F7單元格用填充方式完成后，匯總的合計數據就可自動計算出來，結果如圖6所示。

(三) 繼續探究

在例2中，函數計算后的結果圖6中，由于在F列獎金的運算中，有些數據在Sheet3獎金中找不到，函數只能返回錯誤值“#N/A”，由此對匯總合計也產生影響，造成自動計算的結果也是錯誤值“#N/A”。應想辦法對VLOOKUP函數的返回值“#N/A”作出相應處理，以免影響計算結果。在EXCEL函數中，通過使用ISERROR和IF函數，可解決此問題。

1.IF函數是常用的邏輯函數，這里不再累述。ISERROR函數是一個測試錯誤的函數，它的格式是：ISERROR(value)，語法是：ISERROR 值為任意錯誤值(#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?或#NULL!)。如果測試值為錯誤的時候，當前得到的值為“TRUE”，否則將為“FALSE”。

2.將例2的Sheet1中F2單元格改為=IF(ISERROR(VLOOKUP(B2，Sheet3“獎金”!B:D，3，FALSE))，“0”，VLOOKUP(B2，Sheet3“獎金”!B:D，3，0))，其中VLOOKUP(B2，Sheet3“獎金”!B:D，3，0)是原來的內容，運算中當是找到匹配值時它輸出具體數值，找不到匹配值時則輸出錯誤值“#N/A”，ISERROR(VLOOKUP(B2，Sheet3“獎金”!B:D，3，FALSE)則會在VLOOKUP函數運算找到匹配值時它輸出為“FALSE”，找不到匹配值時則輸出“TRUE”，

3.IF函數判斷ISERROR函數的值為“TRUE”時，F2單元格顯示“0”，值為“FALSE”時，F2單元格則顯示VLOOKUP函數運算找到匹配值。經過運算，所有錯誤值“#N/A”均已消失，匯總結果正確，最終結果如圖七所示。

四、結語

在使用VLOOKUP函數運算中，通過參數靈活的設置，可以實現簡單或復雜的數據匹配查找，再加上ISERROR和IF等函數的嵌套使用，更可以使輸出的結果“如你所愿”，本文只是通過幾個函數的運用，解決了一些實際問題，充分體現了EXCEL函數的強大。EXCEL函數的數量足足有幾百個之多，而我們平時常用的可能只有二三十個，其還有大量的潛能未能展現。因此，我們應該在平時的學習和工作中，不斷了解新的函數，嘗試和使用新的函數，使EXCEL函數成為我們工作上的“神器”，讓我們在工作中更加“得心應手”。

參考文獻：

[1]卓越文化.Excel2007電子表格[M].北京: 電子工業出版社，2010：190-191.

[2]馬軍.Excel數據處理與圖表應用實例精講[J]北京：科學出版社，2006.

責任編輯朱守鋰

收稿日期：2016-03-18

作者簡介：林騰(1969-)，男，廣東省湛江機電學校計算機講師，職業指導師。研究方向：計算機信息技術。(廣東 湛江/524018)

中圖分類號：G712

文獻標識碼：A

文章編號：1005-1422(2016)06-0066-03