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

林騰

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

關(guān)鍵詞：EXCEL 函數(shù)；匹配；問題

中圖分類號：G712文獻標(biāo)識碼：A文章編號：1005-1422（2016）06-0066-03

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

一、EXCEL的使用現(xiàn)狀

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

二、EXCEL的函數(shù)介紹

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

三、使用EXCEL函數(shù)解決實際問題

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

（一）VLOOKUP函數(shù)

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

函數(shù)格式：VLOOKUP（lookup_value，table_array，col_index_num，range_lookup）

格式解釋：VLOOKUP（要查找的內(nèi)容，搜索的區(qū)域，從查找區(qū)域首列開始到要找的內(nèi)容的列數(shù)，近似匹配還是精確匹配查找方式）

參數(shù)定義：

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

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

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

（二）實例演示

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

解決問題過程：

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

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

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

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

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

解決問題過程：

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

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

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

（三） 繼續(xù)探究

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

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

2.將例2的Sheet1中F2單元格改為=IF（ISERROR（VLOOKUP（B2，Sheet3“獎金”！B：D，3，F(xiàn)ALSE）），“0”，VLOOKUP（B2，Sheet3“獎金”！B：D，3，0）），其中VLOOKUP（B2，Sheet3“獎金”！B：D，3，0）是原來的內(nèi)容，運算中當(dāng)是找到匹配值時它輸出具體數(shù)值，找不到匹配值時則輸出錯誤值“#N/A”，ISERROR（VLOOKUP（B2，Sheet3“獎金”！B：D，3，F(xiàn)ALSE）則會在VLOOKUP函數(shù)運算找到匹配值時它輸出為“FALSE”，找不到匹配值時則輸出“TRUE”，

3.IF函數(shù)判斷ISERROR函數(shù)的值為“TRUE”時，F(xiàn)2單元格顯示“0”，值為“FALSE”時，F(xiàn)2單元格則顯示VLOOKUP函數(shù)運算找到匹配值。經(jīng)過運算，所有錯誤值“#N/A”均已消失，匯總結(jié)果正確，最終結(jié)果如圖七所示。

四、結(jié)語

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

參考文獻：

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

[2]馬軍.Excel數(shù)據(jù)處理與圖表應(yīng)用實例精講[J]北京：科學(xué)出版社，2006.

責(zé)任編輯朱守鋰