林　騰

?

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

林騰

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

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

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

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

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

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

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

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

在管理工作中經(jīng)常需要查找既定條件的數(shù)據(jù)，如果是在同一張表格內(nèi)簡單的查找，用排序、篩選或IF函數(shù)就可解決問題，但如果在不同表格中查找數(shù)據(jù)，用上述的方法就會(huì)稍為困難。這時(shí)可使用查詢和引用函數(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ù)表，可以使用對(duì)區(qū)域或區(qū)域名稱的引用；

Col_index_num為table_array中待返回的匹配值的列序號(hào)；

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

(二)實(shí)例演示

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

圖1　Sheet 1職工通訊錄

圖2　Sheet 2要查找的內(nèi)容

圖3　Sheet 2使用VLOOKUP函數(shù)結(jié)果

解決問題過程：

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

2.在Sheet2！B2單元格輸入函數(shù)：=VLOOKUP(A2， Sheet1!$B$2:$E$7，4，0)，確認(rèn)后可獲相應(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)，確認(rèn)后可獲相應(yīng)數(shù)據(jù)，C3、C4單元格用填充方式完成。最終結(jié)果如圖3所示。

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

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

圖4　Sheet 2加班費(fèi)

圖5　Sheet 3獎(jiǎng)金

圖6　Sheet1-初步匯總結(jié)果

圖7　Sheet1-最終匯總結(jié)果

解決問題過程：

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

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

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

(三) 繼續(xù)探究

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

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

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

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

四、結(jié)語

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

參考文獻(xiàn)：

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

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

責(zé)任編輯朱守鋰

收稿日期：2016-03-18

作者簡介：林騰(1969-)，男，廣東省湛江機(jī)電學(xué)校計(jì)算機(jī)講師，職業(yè)指導(dǎo)師。研究方向：計(jì)算機(jī)信息技術(shù)。(廣東 湛江/524018)

中圖分類號(hào)：G712

文獻(xiàn)標(biāo)識(shí)碼：A

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