Excel 2007中自定義函數實例剖析

    一、認識VBA

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

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

　　Excel中VBA主要有兩個用途，一是使電子表格的任務自動化；二是可以用它創建用于工作表公式的自定義函數。

　　由此可見，使用Excel自定義函數的一個前提條件是對VBA基礎知識有所了解，如果讀者朋友有使用Visual Basic編程語言的經驗，那么使用VBA時會感覺有很多相似之處。如果讀者朋友完全是一個新手，也不必太擔心，因為實際的操作和運用是很簡單的。

　　二、什么時候使用自定義函數？

　　有些初學Excel的朋友可能有這樣疑問：Excel已經內置了這么多函數，我還有必要創建自己的函數嗎？

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

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

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

　　第二，自定義函數可以滿足我們個性化的需要，可以使我們的公式具有更強大和靈活的功能。

　　實際工作的要求千變萬化，僅使用Excel內置函數常常不能圓滿地解決問題，這時，我們就可以使用自定義函數來滿足實際工作中的個性化需求。

　　上面的講述比較抽象，我們還是把重點放在實際例子的剖析上，請大家在實際例子中進一步體會，進而學會在Excel中創建和使用自定義函數。

　　下面我們通過兩個典型實例，學習自定義函數使用的全過程。這里實際上假設讀者朋友都有一定的VBA基礎。

　　假如你完全沒有VBA基礎也不要緊，當學習完實例后，若覺得自定義函數在自己以后的工作中可能用到，那么再去補充相應的VBA基礎也不遲。

　　(一) 計算個人調節稅的自定義函數

　　任務

　　假設個人調節稅的收繳標準是：工資小于等于800元的免征調節稅，工資800元以上至1500元的超過部分按5％的稅率征收，1500元以上至2000元的超過部分按8％的稅率征收，高于2000元的超過部分按20％的稅率征收。

　　分析

　　假設Sheet1工作表的A、B、C、D列中分別存放“姓名”、“總工資”、“調節稅”、“稅后工資”字段數據，如圖1所示。 

　　　平時使用較多的方法是借助嵌套使用IF函數計算，比如在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)))”，然后通過填充柄復制公式到C列的其余單元格。

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

　　正如前面提到的兩個方面的原因：一是公式看起來太繁瑣，不便于理解和管理；二是公式的處理能力在面對稍微復雜一些的問題時便失去效用，比如假設調節稅的稅率標準會根據年齡的不同而改變，那么公式可能就無能為力了。

　　使用自定義函數

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

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

　　2. 進入Visual Basic窗口后，選擇菜單“插入→模塊”命令，于是得到“模塊1”，在其中輸入如下自定義函數的代碼(圖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. 函數自定義完成后，選擇菜單“文件→關閉并返回到Microsoft Excel”命令，返回到Excel工作表窗口，在C2單元格中輸入公式“=TAX(B2)”回車后就計算出了第一個員工應付的個人調節稅，然后用公式填充柄復制公式到其它后面的單元格，這樣就利用自定義函數完成了個人調節稅的計算(圖3)。 

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

　　通常，自定義的函數只能在當前工作薄使用，如果該函數需要在其它工作薄中使用，則選擇菜單“文件→另存為”命令，打開“另存為”對話框，選擇保存類型為“Mircosoft Excel加載宏”，然后輸入一個文件名，如“TAX”單擊“確定”后文件就被保存為加載宏(圖4)。