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

然后選擇菜單“工具→加載宏”命令，打開(kāi)“加載宏”對(duì)話(huà)框，勾選“可用加載宏”列表框中的“Tax”復(fù)選框即可，單擊“確定”按鈕后(圖5)，就可以在本機(jī)上的所有工作薄中使用該自定義函數(shù)了。 

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

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

　任務(wù)

　　為了促進(jìn)銷(xiāo)售人員的工作積極性，銷(xiāo)售部門(mén)經(jīng)理制定了銷(xiāo)售業(yè)績(jī)獎(jiǎng)金制度，獎(jiǎng)金發(fā)放的標(biāo)準(zhǔn)獎(jiǎng)金率如下：月銷(xiāo)售額小于等于2800元的獎(jiǎng)金率為4％，月銷(xiāo)售額為2800元至7900元的獎(jiǎng)金率為7％，月銷(xiāo)售額為7900元至15000元的獎(jiǎng)金率為10％，月銷(xiāo)售額為15000元至30000元的獎(jiǎng)金率為13％，月銷(xiāo)售額為30000元至50000元的獎(jiǎng)金率為16％，月銷(xiāo)售額大于50000元的獎(jiǎng)金率為19％。同時(shí)，為了鼓勵(lì)員工持續(xù)地為公司工作，工齡越長(zhǎng)對(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工作表，開(kāi)始分析計(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ì)算。

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

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

　　使用自定義函數(shù)

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

　　1. 在上述Excel工作表中，選擇菜單“工具→宏→Visual Basic編輯器”命令，打開(kāi)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ù)：銷(xiāo)售額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)金率超過(guò)7個(gè)時(shí)，用自定義函數(shù)的方法仍然可以輕松處理。

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

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

　　四、總結(jié)

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

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