excel中自定義函式例項剖析
稍有excel使用經驗的朋友，都知道excel內建函式的快捷與方便，它大大增強了excel資料計算與分析的能力。不過內建的函式並不一定總是能滿足我們的需求，這時，就可以通過定義自己的函式來解決問題。文章末尾提供.
xls檔案供大家**參考。
一、認識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所示。
圖 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
圖 23. 函式自定義完成後，選擇選單「檔案→關閉並返回到microsoft excel」命令，返回到excel工作表視窗，在c2單元格中輸入公式「=tax(b2)」回車後就計算出了第乙個員工應付的個人調節稅，然後用公式填充柄複製公式到其它後面的單元格，這樣就利用自定義函式完成了個人調節稅的計算(圖3)。
圖 34. 從自定義函式的**中可以看出，用這種方式，自定義函式的功能非常易於理解，同時如果稅率改變，相應地變化r1、r2、r3的值即可。
通常，自定義的函式只能在當前工作薄使用，如果該函式需要在其它工作薄中使用，則選擇選單「檔案→另存為」命令，開啟「另存為」對話方塊，選擇儲存型別為「mircosoft excel載入巨集」，然後輸入乙個檔名，如「tax」單擊「確定」後檔案就被儲存為載入巨集(圖4)。然後選擇選單「工具→載入巨集」命令，開啟「載入巨集」對話方塊，勾選「可用載入巨集」列表框中的「tax」核取方塊即可，單擊「確定」按鈕後(圖5)，就可以在本機上的所有工作薄中使用該自定義函式了。
圖 4圖 5如果想要在其它機器上使用該自定義函式，只要把上面的載入巨集檔案複製到其它電腦上載入巨集的預設儲存位置即可。
說明：windows xp系統下載入巨集檔案的預設儲存位置為：c:
\documents and settings\zunyue(使用者帳戶)\application data\microsoft\addins資料夾。
(二) 計算獎金的自定義函式
任務為了促進銷售人員的工作積極性，銷售部門經理制定了銷售業績獎金制度，獎金發放的標準獎金率如下：月銷售額小於等於2800元的獎金率為4％，月銷售額為2800元至7900元的獎金率為7％，月銷售額為7900元至15000元的獎金率為10％，月銷售額為15000元至30000元的獎金率為13％，月銷售額為30000元至50000元的獎金率為16％，月銷售額大於50000元的獎金率為19％。同時，為了鼓勵員工持續地為公司工作，工齡越長對獎金越有利，具體規定為：
參與計算的獎金率等於標準獎金率加上工齡一半的百分數。比如乙個工齡為5年的員工，標準獎金率為7％時，參與計算的獎金率則為9.5%=7%+(5/2)%。
分析首先，我們在excel2003中製作好如圖6的sheet1工作表，開始分析計算的方法。
圖 6如果不考慮工齡對獎金率的影響，那麼可以利用巢狀使用if函式，在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函式最多只能巢狀7層，萬一獎金率超過7個，那麼這個方法就無能為力了。
另一方面，由於沒有考慮工齡，所以該方法不能算是解決問題了，如果我們把工齡融入到上述公式中，這樣公式就會顯得更加冗長繁瑣，以後的管理與調整都很不方便。
使用自定義函式
下面我們看看利用excel自定義函式進行計算的全過程，有了例項一的基礎，相信大家理解起來更容易了。不過這裡與例項一有乙個明顯的差別是，該自定義函式使用了2個引數，請大家注意體會。
1. 在上述excel工作表中，選擇選單「工具→巨集→visual basic編輯器」命令，開啟visual basic視窗，然後選擇選單「插入→模組」命令，插入乙個名為「模組1」的模組。
2. 接著在模組編輯視窗中輸入自定義函式的**如下(圖 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)
EXCEL圖表的應用技巧
12 圖表的應用技巧 excel提供了14種標準的圖表型別，每一種都具有多種組合和變換。在眾多的圖表型別中，選用那一種圖表更好呢？根據資料的不同和使用要求的不同，可以選擇不同型別的圖表。圖表的選擇主要同資料的形式有關，其次才考慮感覺效果和美觀性。下面給出了一些常見的規則。面積圖 顯示一段時間內變動的...
在EXCEL中計算員工入職年限
例項3 題目 計算日期為1973 4 1和當前日期的間隔天數.公式 datedif 1973 4 1 today d 結果 12273簡要說明當單位 為 d 時,計算結果是兩個日期間隔的天數.例項4 題目 計算日期為1973 4 1和當前日期的不計年數的間隔天數.公式 datedif 1973 4 ...
EXCEL中計算方位角距離公式
電子 中求方位角的公式 結果顯示為度格式的計算式 pi 1 sign b3 b 1 2 atan a3 a 1 b3 b 1 180 pi excel中求方位角公式 a1,b1放起始點座標a3,b3放終點座標。結果顯示為度分秒格式的計算式 int pi 1 sign b3 b 1 2 atan a3...