巧用VBA拓展EXCEL的求和函數(shù)功能

張秀虎

（江蘇省海州高級(jí)中學(xué)，江蘇連云港222023）

巧用VBA拓展EXCEL的求和函數(shù)功能

張秀虎

（江蘇省海州高級(jí)中學(xué)，江蘇連云港222023）

在中小學(xué)的日常管理工作中，Excel軟件被大量應(yīng)用，Excel最大的特點(diǎn)是簡(jiǎn)單易用，操作靈活，可大大地彌補(bǔ)管理軟件的不足。采用Excel VBA編寫(xiě)自定義函數(shù)，可以拓展Excel的函數(shù)功能，使其更好地為大家服務(wù)。

VBA；求和函數(shù)；Excel

引言

隨著教育信息化的逐步推進(jìn)深入，學(xué)校的日常管理軟件已經(jīng)趨于普及，使得一般工作人員的工作效率大幅度提高，減輕了工作人員的工作強(qiáng)度。管理軟件從數(shù)據(jù)輸入、處理、輸出方面實(shí)現(xiàn)了一體化，但在實(shí)時(shí)需要的細(xì)節(jié)方面還是滿(mǎn)足不了管理工作的要求，導(dǎo)致這個(gè)現(xiàn)象的主要原因是軟件的開(kāi)發(fā)者不是教育管理工作一線(xiàn)人員，設(shè)計(jì)管理軟件時(shí)不能夠全面深入了解具體的工作需求，也有工作實(shí)時(shí)需求多變的原因。因此，在中小學(xué)的日常管理工作中，Excel軟件還是被大量應(yīng)用，Excel最大的特點(diǎn)是簡(jiǎn)單易用，操作靈活，可大大地彌補(bǔ)管理軟件的不足。可惜的是，Excel軟件也有自身的缺陷，如自帶的函數(shù)也有解決不了的實(shí)際問(wèn)題。那能否拓展Excel的函數(shù)功能，使其更好地為大家服務(wù)呢？采用Excel VBA編寫(xiě)自定義函數(shù)，就可以解決這個(gè)問(wèn)題。在本文中，筆者介紹使用VBA拓展求和函數(shù)功能。

一、Excel自帶的求和函數(shù)

在Excel中，為了統(tǒng)計(jì)一列或一行數(shù)據(jù)的總和，如圖1所示，統(tǒng)計(jì)所有財(cái)產(chǎn)的金額，一般采用SUM (E2:E12)函數(shù)、自動(dòng)求和∑命令以及手動(dòng)編寫(xiě)表達(dá)式的方式實(shí)現(xiàn)。

圖1

也可使用SUMIF函數(shù)統(tǒng)計(jì)滿(mǎn)足條件的數(shù)據(jù)和，如統(tǒng)計(jì)“方凳(板式或鋼式)”的金額總和，通過(guò)調(diào)用SUMIF(A2:A12,"方凳(板式或鋼式)",E2:E12)實(shí)現(xiàn)。

假設(shè)想查找出“方凳(板式或鋼式)”的存放地點(diǎn)或者保管使用人，SUMIF函數(shù)無(wú)法實(shí)現(xiàn)，原因是SUMIF只能統(tǒng)計(jì)數(shù)值型數(shù)據(jù)，無(wú)法統(tǒng)計(jì)文本型數(shù)據(jù)。根據(jù)這個(gè)想法，采用VBA編寫(xiě)函數(shù)SUMCIF拓展求和函數(shù)的功能。

二、VBA語(yǔ)言

VBA(Visual Basic For Applications)是微軟開(kāi)發(fā)出來(lái)在其桌面應(yīng)用程序中執(zhí)行通用的自動(dòng)化(OLE)任務(wù)的編程語(yǔ)言。VBA是Visual Basic的一個(gè)子集,VBA不同于VB，原因是VBA要求有一個(gè)宿主應(yīng)用程序才能運(yùn)行（需要在Excel等軟件的運(yùn)行下才能運(yùn)行），而且不能用于創(chuàng)建獨(dú)立應(yīng)用程序。而VB可用于創(chuàng)建獨(dú)立的應(yīng)用程序。VBA可使常用的過(guò)程或者進(jìn)程自動(dòng)化，可以創(chuàng)建自定義的解決方案，最適用于定制已有的桌面應(yīng)用程序。

在Excel主窗口中，選擇“工具—>宏—>Visual Basic編輯器”命令，彈出VBA編寫(xiě)窗口，如圖2所示，在VBA編寫(xiě)窗口中，選擇“插入—>模塊”命令，插入類(lèi)1，在右側(cè)的窗口中編寫(xiě)VBA代碼。

三、SUMCIF函數(shù)的代碼

Excel中的SUMIF(rang,criteria,sum_rang)函數(shù)有3個(gè)參數(shù)，其中criteria表示求和的條件，rang表示待比較的條件區(qū)域，sum_rang表示需要統(tǒng)計(jì)的數(shù)據(jù)區(qū)域。

SUMCIF函數(shù)模仿SUMIF函數(shù)，SUMCIF (col_head,col_end,sea_address,Ret_address)使用4個(gè)參數(shù)，col_head、col_end為待比較的條件區(qū)的行首、行尾，sea_address為求和的條件，Ret_address為需要統(tǒng)計(jì)的數(shù)據(jù)區(qū)的行首。

FunctionSUMCIF(col_head,col_end,sea_address, Ret_address As Range)

Dimd1,d2,d3,d4AsRange'聲明4個(gè)單元格對(duì)象

Set d1=col_header'給單元格對(duì)象d1關(guān)聯(lián)col_header

Set d2=col_end'給單元格對(duì)象d2關(guān)聯(lián)col_end

Set d3=sea_address'給單元格對(duì)象d3關(guān)聯(lián)sea_address

Set d4=Ret_address'給單元格對(duì)象d4關(guān)聯(lián)ret_address

Dim i,j,k As Integer'定義兩個(gè)循環(huán)變量，i表示比較數(shù)據(jù)區(qū)的行號(hào)，j表示比較數(shù)據(jù)區(qū)的列號(hào),k表示求和數(shù)據(jù)區(qū)的列號(hào)

Dim s1As String's1記錄滿(mǎn)足條件的文本

s1=""

j=d1.column'取比較數(shù)據(jù)區(qū)的列號(hào)

k=d4.column'取求和數(shù)據(jù)區(qū)的列號(hào)

For i=d1.Row To d2.Row'遍歷比較區(qū)域的數(shù)據(jù)與條件是否相符，相符則將對(duì)應(yīng)的統(tǒng)計(jì)區(qū)數(shù)據(jù)相連接

temp=ActiveWorkbook.Sheets(d1.Parent.Name).Cells (i,j).Value

If temp=d3.Value Then

s1=s1+ActiveWorkbook.Sheets(d4.Parent.Name). Cells(i,k).Value+","

End If

Next i

s1=Mid(s1,1,Len(s1)-1)

SUMCIF=s1'通過(guò)函數(shù)名稱(chēng)帶回結(jié)果

End Function

圖2

四、SUMIF函數(shù)的運(yùn)用

返回Excel窗口（圖1），統(tǒng)計(jì)出方凳(板式或鋼式)存放地點(diǎn)或者保管使用人。可以按下列步驟完成：第一步，在H2單元格中，輸入要查詢(xún)的條件——方凳(板式或鋼式)。第二步，在I2單元格中輸入=SUMCIF（）函數(shù)，（）內(nèi)的參數(shù)為A2,A12,H2,F2，其中A2、A12為比較區(qū)域的行首、行尾，H2為條件區(qū)域地址，F(xiàn)2為求和數(shù)據(jù)的地址。第三步按“回車(chē)”鍵，I2單元格中立即得出“生物解剖室，生物觀(guān)察室”信息。

五、SUMIF函數(shù)的移植

將SUMCIF移植至其他程序中，可以將其以“加載宏”類(lèi)型保存，再在其他程序中加載宏，就可以將SUMCIF函數(shù)移植，避免函數(shù)代碼的重復(fù)編寫(xiě)工作。具體步驟為：

保存宏，在Excel主窗口中，選擇“文件—>另存為”命令，彈出的另存為窗口，選擇保存類(lèi)型“Microsoft Office Excel加載宏”，并任意命名為SUMC，單擊“保存”按鈕，將其保存在“C:Documents and SettingsAdministratorApplication DataMicrosoftAddIns”目錄下。

加載宏，在需要使用SUMCIF函數(shù)的工作表中，選擇“工具—>加載宏”命令，彈出加載宏窗口，如果沒(méi)有出現(xiàn)SUMC宏選項(xiàng)，可以通過(guò)“瀏覽”命令瀏覽本地硬盤(pán)，找到保存的宏SUMC，將其添加至加載宏窗口中，然后選取SUMC，單擊“確定”按鈕就將SUMCIF函數(shù)加載到工作表中了。

要正常使用函數(shù)SUMCIF函數(shù)，必須在打開(kāi)工作表時(shí)啟用宏，并將宏的安全級(jí)別設(shè)置為“中”，函數(shù)SUNCIF才會(huì)起作用，不然SUMCIF不會(huì)生效。

結(jié)束語(yǔ)

以上代碼在Excel2000和Excel2003中都調(diào)試成功，上述函數(shù)代碼只要進(jìn)行適當(dāng)修改，可以繼續(xù)拓展SUNCIF函數(shù)功能。

（編輯：王天鵬）

book=63,ebook=59

TP312 文獻(xiàn)標(biāo)識(shí)碼：A 文章編號(hào)：1673-8454（2010）16-0063-02