當前位置：首頁 → 文章首頁 IT學院 VC(VC++)

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

作者：東坡下載來源：uzzf 發(fā)布時間：2010-10-14 9:06:58 點擊：

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

圖 4

圖 5

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

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

　任務(wù)

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

　　分析

　　首先，我們在Excel2003中制作好如圖6的Sheet1工作表，開始分析計算的方法。

圖 6

　　如果不考慮工齡對獎金率的影響，那么可以利用嵌套使用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%)))))”可以進行計算。

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

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

　　使用自定義函數(shù)

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

　　1. 在上述Excel工作表中，選擇菜單“工具→宏→Visual Basic編輯器”命令，打開Visual Basic窗口，然后選擇菜單“插入→模塊”命令，插入一個名為“模塊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

圖 7

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

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