Excel 2007中自定義函數(shù)實(shí)例剖析

　　在介紹自定義函數(shù)的具體使用之前，不得不先介紹一下VBA，原因很簡單，自定義函數(shù)就是用它創(chuàng)建的。VBA的全稱是Visual Basic for Application，它是微軟最好的通用應(yīng)用程序腳本編程語言，它的特點(diǎn)是容易上手，而且功能非常強(qiáng)大。

　　在微軟所有的Office組件中，如Word、Access、Powerpoint等等都包含VBA，如果你能在一種Office組件中熟練使用VBA，那么在其它組件中使用VBA的原理是相通的。

　　Excel中VBA主要有兩個(gè)用途，一是使電子表格的任務(wù)自動(dòng)化；二是可以用它創(chuàng)建用于工作表公式的自定義函數(shù)。

　　由此可見，使用Excel自定義函數(shù)的一個(gè)前提條件是對(duì)VBA基礎(chǔ)知識(shí)有所了解，如果讀者朋友有使用Visual Basic編程語言的經(jīng)驗(yàn)，那么使用VBA時(shí)會(huì)感覺有很多相似之處。如果讀者朋友完全是一個(gè)新手，也不必太擔(dān)心，因?yàn)閷?shí)際的操作和運(yùn)用是很簡單的。

　　二、什么時(shí)候使用自定義函數(shù)？

　　有些初學(xué)Excel的朋友可能有這樣疑問：Excel已經(jīng)內(nèi)置了這么多函數(shù)，我還有必要?jiǎng)?chuàng)建自己的函數(shù)嗎？

　　回答是肯定的。原因有兩個(gè)，它們也正好可以解釋什么時(shí)候使用Excel自定義函數(shù)的問題。

　　第一，自定義函數(shù)可以簡化我們的工作。

　　有些工作，我們的確可以在公式中組合使用Excel內(nèi)置的函數(shù)來完成任務(wù)，但是這樣做的一個(gè)明顯缺點(diǎn)是，我們的公式可能太冗長、繁瑣，可讀性很差，不易于管理，除了自己之外別人可能很難理解。這時(shí)，我們可以通過使用自定義函數(shù)來簡化自己的工作。

　　第二，自定義函數(shù)可以滿足我們個(gè)性化的需要，可以使我們的公式具有更強(qiáng)大和靈活的功能。

　　實(shí)際工作的要求千變?nèi)f化，僅使用Excel內(nèi)置函數(shù)常常不能圓滿地解決問題，這時(shí)，我們就可以使用自定義函數(shù)來滿足實(shí)際工作中的個(gè)性化需求。

　　上面的講述比較抽象，我們還是把重點(diǎn)放在實(shí)際例子的剖析上，請(qǐng)大家在實(shí)際例子中進(jìn)一步體會(huì)，進(jìn)而學(xué)會(huì)在Excel中創(chuàng)建和使用自定義函數(shù)。

　　下面我們通過兩個(gè)典型實(shí)例，學(xué)習(xí)自定義函數(shù)使用的全過程。這里實(shí)際上假設(shè)讀者朋友都有一定的VBA基礎(chǔ)。

　　假如你完全沒有VBA基礎(chǔ)也不要緊，當(dāng)學(xué)習(xí)完實(shí)例后，若覺得自定義函數(shù)在自己以后的工作中可能用到，那么再去補(bǔ)充相應(yīng)的VBA基礎(chǔ)也不遲。

　　(一) 計(jì)算個(gè)人調(diào)節(jié)稅的自定義函數(shù)

　　任務(wù)

　　假設(shè)個(gè)人調(diào)節(jié)稅的收繳標(biāo)準(zhǔn)是：工資小于等于800元的免征調(diào)節(jié)稅，工資800元以上至1500元的超過部分按5％的稅率征收，1500元以上至2000元的超過部分按8％的稅率征收，高于2000元的超過部分按20％的稅率征收。

　　分析

　　假設(shè)Sheet1工作表的A、B、C、D列中分別存放“姓名”、“總工資”、“調(diào)節(jié)稅”、“稅后工資”字段數(shù)據(jù)，如圖1所示。 

　　　平時(shí)使用較多的方法是借助嵌套使用IF函數(shù)計(jì)算，比如在C2單元格輸入公式“=IF(B2<=800,0,IF(B2<=1500,(B2-800)*0.05,IF(B2<=2000,700*0.05+(B2-1500)*0.08,700*0.05+500*0.08+(B2-2000)*0.2)))”，然后通過填充柄復(fù)制公式到C列的其余單元格。

　　既然公式能夠解決問題，為什么還要使用自定義函數(shù)的方法呢？

　　正如前面提到的兩個(gè)方面的原因：一是公式看起來太繁瑣，不便于理解和管理；二是公式的處理能力在面對(duì)稍微復(fù)雜一些的問題時(shí)便失去效用，比如假設(shè)調(diào)節(jié)稅的稅率標(biāo)準(zhǔn)會(huì)根據(jù)年齡的不同而改變，那么公式可能就無能為力了。

　　使用自定義函數(shù)

　　下面就通過此例介紹使用自定義函數(shù)的全過程，即使是初學(xué)Excel的朋友，也會(huì)感覺其操作實(shí)際上是非常簡單的。

　　1. 為了便于測試自定義函數(shù)的計(jì)算效果，可以先把上面采用公式計(jì)算的結(jié)果刪去。然后選擇菜單“工具→宏→Visual Basic編輯器”命令(或按下鍵盤Alt+F11組合鍵)，打開Visual Basic窗口，我們將在這里自定義函數(shù)。

　　2. 進(jìn)入Visual Basic窗口后，選擇菜單“插入→模塊”命令，于是得到“模塊1”，在其中輸入如下自定義函數(shù)的代碼(圖2)：

　　Function TAX(salary)

　　Const r1 As Double = 0.05

　　Const r2 As Double = 0.08

　　Const r3 As Double = 0.2

　　Select Case salary

　　Case Is <= 800

　　TAX = 0

　　Case Is <= 1500

　　TAX = (salary - 800) * r1

　　Case Is <= 2000

　　TAX = (1500 - 800) * r1 + (salary - 1500) * r2

　　Case Is > 2000

　　TAX = (1500 - 800) * r1 + (2000 - 1500) * r2 + (salary - 2000) * r3

　　End Select

　　End Function 

　　　3. 函數(shù)自定義完成后，選擇菜單“文件→關(guān)閉并返回到Microsoft Excel”命令，返回到Excel工作表窗口，在C2單元格中輸入公式“=TAX(B2)”回車后就計(jì)算出了第一個(gè)員工應(yīng)付的個(gè)人調(diào)節(jié)稅，然后用公式填充柄復(fù)制公式到其它后面的單元格，這樣就利用自定義函數(shù)完成了個(gè)人調(diào)節(jié)稅的計(jì)算(圖3)。 

　　4. 從自定義函數(shù)的代碼中可以看出，用這種方式，自定義函數(shù)的功能非常易于理解，同時(shí)如果稅率改變，相應(yīng)地變化r1、r2、r3的值即可。

　　通常，自定義的函數(shù)只能在當(dāng)前工作薄使用，如果該函數(shù)需要在其它工作薄中使用，則選擇菜單“文件→另存為”命令，打開“另存為”對(duì)話框，選擇保存類型為“Mircosoft Excel加載宏”，然后輸入一個(gè)文件名，如“TAX”單擊“確定”后文件就被保存為加載宏(圖4)。然后選擇菜單“工具→加載宏”命令，打開“加載宏”對(duì)話框，勾選“可用加載宏”列表框中的“Tax”復(fù)選框即可，單擊“確定”按鈕后(圖5)，就可以在本機(jī)上的所有工作薄中使用該自定義函數(shù)了。 

　　　如果想要在其它機(jī)器上使用該自定義函數(shù)，只要把上面的加載宏文件復(fù)制到其它電腦上加載宏的默認(rèn)保存位置即可。

　　說明：Windows XP系統(tǒng)下加載宏文件的默認(rèn)保存位置為：C:Documents and Settingszunyue(用戶帳戶)Application DataMicrosoftAddIns文件夾。

　任務(wù)

　　為了促進(jìn)銷售人員的工作積極性，銷售部門經(jīng)理制定了銷售業(yè)績獎(jiǎng)金制度，獎(jiǎng)金發(fā)放的標(biāo)準(zhǔn)獎(jiǎng)金率如下：月銷售額小于等于2800元的獎(jiǎng)金率為4％，月銷售額為2800元至7900元的獎(jiǎng)金率為7％，月銷售額為7900元至15000元的獎(jiǎng)金率為10％，月銷售額為15000元至30000元的獎(jiǎng)金率為13％，月銷售額為30000元至50000元的獎(jiǎng)金率為16％，月銷售額大于50000元的獎(jiǎng)金率為19％。同時(shí)，為了鼓勵(lì)員工持續(xù)地為公司工作，工齡越長對(duì)獎(jiǎng)金越有利，具體規(guī)定為：參與計(jì)算的獎(jiǎng)金率等于標(biāo)準(zhǔn)獎(jiǎng)金率加上工齡一半的百分?jǐn)?shù)。比如一個(gè)工齡為5年的員工，標(biāo)準(zhǔn)獎(jiǎng)金率為7％時(shí)，參與計(jì)算的獎(jiǎng)金率則為9.5%=7%+(5/2)%。

　　分析

　　首先，我們?cè)贓xcel2003中制作好如圖6的Sheet1工作表，開始分析計(jì)算的方法。 

　 　如果不考慮工齡對(duì)獎(jiǎng)金率的影響，那么可以利用嵌套使用IF函數(shù)，在D2單元格輸入公式“=IF(B2<=2800,B2*4%,IF(B2<=7900,B2*7%,IF(B2<=15000,B2*10%,IF(B2<=30000,B2*13%,IF(B2<=50000,B2*16%,B2*19%)))))”可以進(jìn)行計(jì)算。

　　但是，該公式的一些弊端很明顯：一是公式看起來太繁瑣、不容易理解，而且IF函數(shù)最多只能嵌套7層，萬一獎(jiǎng)金率超過7個(gè)，那么這個(gè)方法就無能為力了。

　　另一方面，由于沒有考慮工齡，所以該方法不能算是解決問題了，如果我們把工齡融入到上述公式中，這樣公式就會(huì)顯得更加冗長繁瑣，以后的管理與調(diào)整都很不方便。

　　使用自定義函數(shù)

　　下面我們看看利用Excel自定義函數(shù)進(jìn)行計(jì)算的全過程，有了實(shí)例一的基礎(chǔ)，相信大家理解起來更容易了。不過這里與實(shí)例一有一個(gè)明顯的差別是，該自定義函數(shù)使用了2個(gè)參數(shù)，請(qǐng)大家注意體會(huì)。

　　1. 在上述Excel工作表中，選擇菜單“工具→宏→Visual Basic編輯器”命令，打開Visual Basic窗口，然后選擇菜單“插入→模塊”命令，插入一個(gè)名為“模塊1”的模塊。

　　2. 接著在模塊編輯窗口中輸入自定義函數(shù)的代碼如下(圖 7)：

　　Function REWARD(sales, years) As Double

　　Const r1 As Double = 0.04

　　Const r2 As Double = 0.07

　　Const r3 As Double = 0.1

　　Const r4 As Double = 0.13

　　Const r5 As Double = 0.16

　　Const r6 As Double = 0.19

　　Select Case sales

　　Case Is <= 2800

　　REWARD = sales * (r1 + years / 200)

　　Case Is <= 7900

　　REWARD = sales * (r2 + years / 200)

　　Case Is <= 15000

　　REWARD = sales * (r3 + years / 200)

　　Case Is <= 30000

　　REWARD = sales * (r4 + years / 200)

　　Case Is <= 50000

　　REWARD = sales * (r5 + years / 200)

　　Case Is > 50000

　　REWARD = sales * (r6 + years / 200)

　　End Select

　　End Function 

　 　3. 從代碼可以看出，我們自定義了一個(gè)名為REWARD的函數(shù)，它包含兩個(gè)參數(shù)：銷售額sales和工齡years。常量r1至r6分別存放著各個(gè)等級(jí)的獎(jiǎng)金率，這樣處理的好處是當(dāng)獎(jiǎng)金率調(diào)整時(shí)，修改非常方便。同時(shí)，函數(shù)的層次結(jié)構(gòu)比前面的公式清晰，讓人容易理解函數(shù)的功能。此外，當(dāng)獎(jiǎng)金率超過7個(gè)時(shí)，用自定義函數(shù)的方法仍然可以輕松處理。

　　4. 接下來用該自定義函數(shù)進(jìn)行具體的計(jì)算。選擇菜單“文件→關(guān)閉并返回到Microsoft Excel”命令，關(guān)閉Visual Basic窗口，返回Excel工作表。選中D2單元格，在其中輸入“=reward(B2,C2)”，回車后就算出了第一個(gè)員工的獎(jiǎng)金，然后利用公式填充柄復(fù)制該公式到后面的單元格，即可完成對(duì)其它員工獎(jiǎng)金的計(jì)算(圖 8)。 

　 　　如果該自定義函數(shù)需要在其它工作薄或其它機(jī)器上使用，仿照實(shí)例一的操作方法進(jìn)行即可。

　　四、總結(jié)

　　我們通過兩個(gè)典型的實(shí)例講述了Excel中自定義函數(shù)使用的全過程，相信大家都已經(jīng)會(huì)到，其操作過程還是相當(dāng)簡單的。

　　如果你覺得自己的工作可能需要自定義函數(shù)，想進(jìn)一步學(xué)好提高使用自定義函數(shù)的水平，筆者想給出如下幾點(diǎn)建議。

　　第一點(diǎn)、盡力全面熟練地掌握Excel內(nèi)置的函數(shù)。能用內(nèi)置函數(shù)妥善解決的問題，就不必使用自定義函數(shù)。實(shí)際上，自定義函數(shù)的執(zhí)行效率當(dāng)然是比Excel內(nèi)置函數(shù)的執(zhí)行效率慢的。

　　第二點(diǎn)、認(rèn)真掌握好VBA的基礎(chǔ)知識(shí)。這點(diǎn)很容易理解，如果連VBA的基本規(guī)則都不甚清楚，那么別說是寫出精致的自定義函數(shù)，就是寫出能解決問題的自定義函數(shù)也還大有疑問。

　　第三點(diǎn)、具體寫自定義函數(shù)代碼之前，應(yīng)該認(rèn)真分析自己要處理的實(shí)際問題，如果這個(gè)問題有實(shí)際的數(shù)學(xué)函數(shù)模型，那么最好列出這個(gè)函數(shù)的解析式。

　　以上只是筆者的一些淺薄認(rèn)識(shí)，希望能為大家使用好Excel自定義函數(shù)帶來幫助，也希望大家能夠通過使用自定義函數(shù)提高自己的工作效率。