基于VB的Excel分段統(tǒng)計(jì)插件的設(shè)計(jì)

夏蕓

摘要：文中使用Visual Basic 6.0設(shè)計(jì)了一個(gè)Excel 2010的插件，該插件是依據(jù)用戶(hù)所設(shè)定的分段條件，對(duì)用戶(hù)在電子工作表中所選定的數(shù)據(jù)源區(qū)域進(jìn)行條件統(tǒng)計(jì)，并將結(jié)果填寫(xiě)到用戶(hù)所指定的單元格區(qū)域中。文中對(duì)插件的設(shè)計(jì)和實(shí)現(xiàn)進(jìn)行了較為詳細(xì)的描述。該插件具有一定的實(shí)用價(jià)值。

關(guān)鍵詞：分段統(tǒng)計(jì)；Excel 2010；插件；VBA

中圖分類(lèi)號(hào)：TP317 文獻(xiàn)標(biāo)識(shí)碼：A 文章編號(hào)：1009-3044（2015）03-0110-04

The Design of An Excel Plug-in Used to Sectional Count Based on VB

XIA Yun

（College of Mathematics and Computer Science， Anhui Normal University， Wuhu 241000， China）

Abstract： An Excel 2010 plug-in is designed on Visual Basic 6.0 in this paper. And the plug-in is used to count the numbers of the data source range of cells that the user chooses in an Excel sheet according to the sectional condition that the user creates， then the result is recorded in the range of cells that the user chooses. It is described in this paper how to design and realize the plug-in. And the plug-in has some practical value.

Key words： sectional count； Excel 2010； plug-in； VBA

在日常的工作中，人們可能會(huì)使用Excel進(jìn)行數(shù)據(jù)統(tǒng)計(jì)。例如，教師在每次測(cè)試結(jié)束后，需要統(tǒng)計(jì)班級(jí)的最高分、最低分、平均分及其每個(gè)分?jǐn)?shù)段的人數(shù)。而這種類(lèi)似于統(tǒng)計(jì)分?jǐn)?shù)段人數(shù)的這種分段條件統(tǒng)計(jì)，對(duì)于一些Excel使用者而言，有一定的難度。為了解決這個(gè)分段統(tǒng)計(jì)的問(wèn)題，文中設(shè)計(jì)了一個(gè)插件。

開(kāi)發(fā)插件的主要好處在于插件結(jié)構(gòu)具有良好的擴(kuò)充和定制功能以及很容易將插件作為功能模塊掛接到主程序上面[1]。文中采用Visual Basic 6.0為開(kāi)發(fā)平臺(tái)，開(kāi)發(fā)了一個(gè)適用于Excel 2010版本的插件。該插件適用于對(duì)所選擇的工作表單元格區(qū)域進(jìn)行多段的條件統(tǒng)計(jì)，并且將統(tǒng)計(jì)結(jié)果顯示在用戶(hù)所指定的單元格區(qū)域中。這個(gè)插件的操作界面簡(jiǎn)單，操作過(guò)程容易掌握。本文主要介紹了這個(gè)插件的設(shè)計(jì)和實(shí)現(xiàn)。

1 插件的設(shè)計(jì)

文中所設(shè)計(jì)的插件是適用于Excel 2010版本的，因此，該插件在Excel 2010功能區(qū)中創(chuàng)建一個(gè)名為“統(tǒng)計(jì)”的選項(xiàng)卡，在該選項(xiàng)卡的名為“自定義組”選項(xiàng)組中，設(shè)置一個(gè)名為“分段統(tǒng)計(jì)”的命令按鈕。當(dāng)用戶(hù)單擊該命令按鈕時(shí)，出現(xiàn)圖1所示的窗口。

在該窗口中用戶(hù)單擊“計(jì)算區(qū)域”文本框時(shí)，可以在工作表中選擇需要統(tǒng)計(jì)的單元格區(qū)域；單擊“結(jié)果顯示區(qū)域”文本框時(shí)，可以在工作表中選擇存放統(tǒng)計(jì)結(jié)果的單元格區(qū)域。用戶(hù)在該窗口中設(shè)置統(tǒng)計(jì)條件，至多可以設(shè)置10個(gè)條件，單擊“確定”按鈕后，就可以依據(jù)條件分段統(tǒng)計(jì)計(jì)算區(qū)域中符合條件的單元格個(gè)數(shù)，并將結(jié)果顯示到指定的單元格區(qū)域中。

圖1 分段統(tǒng)計(jì)的窗口

分段統(tǒng)計(jì)功能在實(shí)現(xiàn)時(shí)，考慮到用戶(hù)所選擇的需統(tǒng)計(jì)的單元格區(qū)域后，可能會(huì)在統(tǒng)計(jì)完成后有時(shí)會(huì)修改它們的值，那么相應(yīng)的統(tǒng)計(jì)結(jié)果也會(huì)發(fā)生變化。因此，為了實(shí)現(xiàn)實(shí)時(shí)更新，統(tǒng)計(jì)方法是使用Excel中的函數(shù)來(lái)完成。

2 插件的實(shí)現(xiàn)

文中使用Visual Basic 6.0通過(guò)創(chuàng)建外接程序項(xiàng)目完成插件的設(shè)計(jì)和實(shí)現(xiàn)。

2.1創(chuàng)建外接程序項(xiàng)目

使用VB6.0企業(yè)版創(chuàng)建新的“外接程序”項(xiàng)目。在 “引用”對(duì)話(huà)框中添加如下的兩個(gè)引用：“Microsoft Office 14.0 Object Library”和“Microsoft Excel 14.0 Object Library”，再按表1設(shè)置“Connect”設(shè)計(jì)器的屬性。

載行為＼&Startup＼&]

2.2 聲明全局變量、添加引用和實(shí)現(xiàn)接口

1） 聲明全局變量

在模塊中聲明全局變量Excel_app，代碼為：Public Excel_app As Excel.Application。

2） 添加引用

在“Connect”設(shè)計(jì)器的代碼窗口中，添加應(yīng)用程序接口的引用：IDTExtensibility2和IRibbonExtensibility，以便于VB調(diào)用Excel的資源，識(shí)別Excel VBA中的資源和對(duì)象。具體語(yǔ)句如下：

Implements IDTExtensibility2

Implements IRibbonExtensibility

3） 實(shí)現(xiàn)IDTExtensibility2接口

IDTExtensibility2接口中定義了5個(gè)成員方法：OnAddInsUpdate方法，OnConnection方法，OnDisconnection方法，OnStartupComplete方法，OnBeginShutdown方法。這些方法分別發(fā)生在COM加載項(xiàng)更改、加載、卸載，或者是Excel程序啟動(dòng)時(shí)，或者是Excel程序關(guān)閉而COM加載項(xiàng)仍運(yùn)行時(shí)。文中這些方法在實(shí)現(xiàn)時(shí)因?yàn)闆](méi)有特殊的要求，所以大部分方法是空方法。但是在OnConnection方法中需要完成全局變量的賦值，該變量在程序中表示Excel應(yīng)用程序，其具體的代碼如下：

Private Sub IDTExtensibility2_OnConnection（ByVal Application As Object， ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode， ByVal AddInInst As Object， custom（） As Variant）

Set Excel_app = Application

End Sub

4） 實(shí)現(xiàn)IRibbonExtensibility接口

因?yàn)樵摬寮敲嫦駿xcel 2010版本的，因此，需要在Excel的功能區(qū)中添加選項(xiàng)組與命令按鈕。而IRibbonExtensibility接口中所定義的方法GetCustomUI，就是依據(jù)XML代碼創(chuàng)建功能區(qū)用戶(hù)界面。所以，在“Connect”設(shè)計(jì)器必須實(shí)現(xiàn)IRibbonExtensibility接口的方法GetCustomUI，其具體代碼如下：

Private Function IRibbonExtensibility_GetCustomUI（ByVal RibbonID As String） As String

IRibbonExtensibility_GetCustomUI = GetRibbonXML（）

End Function

上述代碼中所調(diào)用的GetRibbonXML是用戶(hù)自定義的函數(shù)，該函數(shù)要求返回一個(gè)字符串，這個(gè)字符串是使用XML代碼在Excel的功能區(qū)中創(chuàng)建一個(gè)“統(tǒng)計(jì)”選項(xiàng)卡，并在該選項(xiàng)卡中創(chuàng)建名為“自定義組”的選項(xiàng)組，再在這個(gè)選項(xiàng)組中創(chuàng)建一個(gè)“分段統(tǒng)計(jì)”命令按鈕。函數(shù)GetRibbonXML的具體代碼如下：

Public Function GetRibbonXML（） As String

Dim sRibbonXML As String

sRibbonXML = "

"office/2006/01/customui"" >" & _

"" & _

"" & _

"" & _

"" & _

"

" imageMso=""CatalogMergeEditList"" onAction=""submain"" />" & _

"

" & _

"" & _

"" & _

"" & _

""

GetRibbonXML = sRibbonXML

End Function

在函數(shù)GetRibbonXML中指明，用戶(hù)單擊“分段統(tǒng)計(jì)”命令按鈕時(shí)，則執(zhí)行名為“submain”的過(guò)程，該過(guò)程主要是顯示圖1所示的“分段統(tǒng)計(jì)”窗口，其代碼如下：

Public Sub submain（ByVal control As IRibbonControl）

UserForm1.Show （1）

End Sub

插件成功安裝后Excel功能區(qū)中會(huì)添加圖2所示的選項(xiàng)卡、選項(xiàng)組等。

圖2 在Excel功能區(qū)中添加選項(xiàng)卡

2.3 窗體的設(shè)計(jì)

1） 外觀(guān)設(shè)計(jì)

“分段統(tǒng)計(jì)”窗體的名稱(chēng)為UserForm1，在該窗體中添加了標(biāo)簽控件、文本框控件、選擇框控件、組合框控件和按鈕控件等，如圖1所示。其中“計(jì)算區(qū)域”的文本框控件的名字為RefEdit1，“結(jié)果顯示區(qū)域”的文本框控件的名字為RefEdit2。這兩個(gè)文本框分別用于顯示用戶(hù)在Excel工作表中選定的數(shù)據(jù)源區(qū)域和結(jié)果區(qū)域的地址，該地址是絕對(duì)地址。

窗體中允許用戶(hù)至多設(shè)置10個(gè)分段條件，每個(gè)分段條件是由一個(gè)選擇框控件、兩個(gè)組合框控件和兩個(gè)文本框控件構(gòu)成。選擇框控件的名稱(chēng)是CheckBoxi，其中i=1，2，…，10；名稱(chēng)為ComboBoxi_1（i=1，2，…，10）的組合框只允許設(shè)置“>”或“>=”的關(guān)系運(yùn)算，名稱(chēng)為ComboBoxi_2（i=1，2，…，10）的組合框只允許設(shè)置“<”或“<=”的關(guān)系運(yùn)算，這兩個(gè)組合框的內(nèi)容只允許從下拉列表中選擇，因此，它們的Style屬性設(shè)置為Dropdown List；兩個(gè)文本框分別用于輸入對(duì)應(yīng)關(guān)系運(yùn)算的操作數(shù)值，該數(shù)值要求用戶(hù)從鍵盤(pán)輸入，它們的名稱(chēng)分別是TextBoxi_1和TextBoxi_2（i=1，2，…，10）。

“確定”按鈕控件的名稱(chēng)是Command1，“取消”按鈕控件的名稱(chēng)是Command2。

2） 窗口激活時(shí)設(shè)置默認(rèn)值的功能實(shí)現(xiàn)

當(dāng)“分段統(tǒng)計(jì)”窗口被激活時(shí)，所有ComboBoxi_1（i=1，2，…，10）的組合框的默認(rèn)值為“>”，所有ComboBoxi_2（i=1，2，…，10）的組合框的默認(rèn)值為“<”，同時(shí)，數(shù)據(jù)源的文本框RefEdit1獲得焦點(diǎn)。因此，UserForm1的Activate事件代碼如下：

Private Sub Form_Activate（）

Dim i As Integer

RefEdit1.SetFocus

For i = 1 To 10

Me.Controls（"ComboBox" & i & "_1"）.ListIndex = 0

Me.Controls（"ComboBox" & i & "_2"）.ListIndex = 0

Next

End Sub

3） RefEdit1和RefEdit2單元格區(qū)域選擇功能的實(shí)現(xiàn)

文本框RefEdit1和RefEdit2在窗體運(yùn)行時(shí)，要求能顯示用戶(hù)在Excel工作表中所選擇的單元格區(qū)域，因此，在用戶(hù)單擊文本框時(shí)，要求將當(dāng)前的“分段統(tǒng)計(jì)”窗體隱藏，出現(xiàn)圖3所示的輸入框，允許用戶(hù)在Excel工作表中用鼠標(biāo)拖選單元格區(qū)域，選擇完成后單擊圖3所示的“確定”按鈕，隱藏的“分段統(tǒng)計(jì)”窗口顯示到前臺(tái)。

圖3 輸入框

RefEdit1文本框的單擊事件和RefEdit2的單擊事件處理方法非常類(lèi)似，RefEdit1的Click事件代碼如下：

Private Sub RefEdit1_Click（）

On Error Resume Next

Dim Rg As Range

Me.Hide

Set Rg = Excel_app.InputBox（"請(qǐng)選擇需統(tǒng)計(jì)的數(shù)據(jù)源區(qū)域"， "數(shù)據(jù)來(lái)源"， Type：=8）

If Not Rg Is Nothing Then

RefEdit1.Text = Rg.Address

Else

RefEdit1.Text = ""

End If

On Error GoTo 0

Me.Show

End Sub

4） 設(shè)置分段條件的功能實(shí)現(xiàn)

在設(shè)置分段條件時(shí)，一但文本框TextBoxi_1或TextBoxi_2（i=1，2，…10）的內(nèi)容發(fā)生改變時(shí)，則只要這兩個(gè)文本框中的一個(gè)有內(nèi)容，那么對(duì)應(yīng)的選擇框CheckBoxi（i=1，2，…10）的Value值設(shè)置為1，否則為0。因此，需要對(duì)這些文本框的文本改變事件進(jìn)行對(duì)應(yīng)的編程設(shè)計(jì)，它們的實(shí)現(xiàn)代碼極為類(lèi)似。以下是文本框TextBox1_1的Change事件的代碼：

Private Sub TextBox1_1_Change（）

If Trim（TextBox1_1.Text） <> "" Or Trim（TextBox1_2.Text） <> "" Then

CheckBox1.Value = 1

Else

CheckBox1.Value = 0

End If

End Sub

5） “確定”按鈕Command1的功能實(shí)現(xiàn)

單擊“確定”按鈕時(shí)，要求能夠按照窗口中所設(shè)定的條件，對(duì)指定的數(shù)據(jù)源區(qū)域進(jìn)行條件統(tǒng)計(jì)，并將結(jié)果顯示到指定的單元格區(qū)域中。

其算法基本思想是：首先獲取數(shù)據(jù)源的非空的單元格區(qū)域和非空的結(jié)果區(qū)域，并分別保存為字符串形式，如果這兩個(gè)區(qū)域有一個(gè)是空的，則出現(xiàn)出錯(cuò)提示對(duì)話(huà)框，再關(guān)閉窗口；如果區(qū)域選擇正確，則使用循環(huán)語(yǔ)句對(duì)10個(gè)選擇框進(jìn)行依次檢查，如果第i個(gè)選擇框的Value值為1，即被選中，則讀取它對(duì)應(yīng)的兩個(gè)組合框、兩個(gè)文本框中的內(nèi)容，并結(jié)合數(shù)據(jù)源區(qū)域的地址字符串，使用Excel中countif函數(shù)完成公式的編寫(xiě)，并將該公式保存為字符串，最后在指定的單元格內(nèi)分別填寫(xiě)條件和計(jì)算的公式；最后，關(guān)閉窗口。

由于統(tǒng)計(jì)的方法使用的是公式，因此，如果數(shù)據(jù)源區(qū)域的值發(fā)生了改變，那么相應(yīng)的運(yùn)算結(jié)果就會(huì)自動(dòng)調(diào)整。Command1按鈕的Click事件的代碼如下：

Private Sub Command1_Click（）

On Error Resume Next

Dim i， j As Integer

i = 1

j = 0

Source = Trim（RefEdit1.Text）

Target = Trim（RefEdit2.Text）

If Target = "" Or Source = "" Then

MsgBox （"計(jì)算區(qū)域和結(jié)果顯示區(qū)域均未設(shè)置！"）

Else

pos = InStr（Target， "："）

If pos > 0 Then

Target = Left（Target， pos - 1）

End If

For i = 1 To 10

If Me.Controls（"CheckBox" & i）.Value = 1 Then

Data1 = Trim（Me.Controls（"TextBox" & i & "_1"）.Text）

Data2 = Trim（Me.Controls（"TextBox" & i & "_2"）.Text）

Oper1 = Me.Controls（"ComboBox" & i & "_1"）.Text

Oper2 = Me.Controls（"ComboBox" & i & "_2"）.Text

If Data1 <> "" And Data2 <> "" Then

formu = "=countif（" + Source + "，" & """" + Oper1 + Data1 & """" + "） - countif（ " + Source + "，" & """"

If Oper2 = "<" Then

formu = formu + ">="

Else

formu = formu + ">"

End If

formu = formu + Data2 & """" + "）"

conx = Oper1 + Data1 + "且" + Oper2 + Data2

Else

If Data1 <> "" Then

formu = "=countif（" + Source + "，" & """" + Oper1 + Data1 & """" + "）"

conx = Oper1 + Data1

Else

formu = "=countif（" + Source + "，" & """" + Oper2 + Data2 & """" + "）"

conx = Oper2 + Data2

End If

End If

Excel_app.Range（Target）.Offset（j， 0） = conx

Excel_app.Range（Target）.Offset（j， 1）.Formula = formu

j = j + 1

End If

Next

End If

Unload Me

End Sub

6） “取消”按鈕Command2的功能實(shí)現(xiàn)

單擊“取消”按鈕時(shí)，關(guān)閉窗口。其Click事件的代碼如下：

Private Sub Command2_Click（）

Unload Me

End Sub

2.4 生成dll文件并安裝插件

在VB6.0企業(yè)版中生成擴(kuò)展名為dll的動(dòng)態(tài)鏈接庫(kù)文件“fdtj.dll”。打開(kāi)Excel 2010，在“開(kāi)發(fā)工具”選項(xiàng)卡的“加載項(xiàng)”選項(xiàng)組中單擊“COM加載項(xiàng)”命令按鈕，在出現(xiàn)的“COM加載項(xiàng)”對(duì)話(huà)框中正確的添加文件“fdtj.dll”，即可成功的安裝該插件。這時(shí)，Excel 2010中就會(huì)出現(xiàn)圖2所示的選項(xiàng)卡，則用戶(hù)就可以很方便地使用分段統(tǒng)計(jì)的功能。

3 結(jié)束語(yǔ)

本文詳細(xì)介紹了使用VB開(kāi)發(fā)Excel 2010插件的方法，以及具體的插件功能實(shí)現(xiàn)，文中所設(shè)計(jì)的插件可以很方便地實(shí)現(xiàn)分段的條件統(tǒng)計(jì)，具有一定的應(yīng)用價(jià)值，并且也可以將之作為Excel VBA教學(xué)的一個(gè)良好用例。

參考文獻(xiàn)：

[1] 周威.VB插件的原理和應(yīng)用[J].中文信息，2003（10）： 44-45.

[2] 陳文宇，胡英春，侯軍燕. Word 2010插件的開(kāi)發(fā)與實(shí)現(xiàn)[J].廣西工學(xué)院學(xué)報(bào)，2010（6）.

[3] 解必華，奚玉梅. 基于VB的Excel打印插件設(shè)計(jì)及實(shí)現(xiàn)[J].電腦知識(shí)與技術(shù)，2011，7（6）： 1343-1345.

[4] 羅剛君，楊嘉愷.來(lái)吧！帶你玩轉(zhuǎn)Excel VBA[M].北京：電子工業(yè)出版社，2013.

[5] 裴純禮.Word教育教學(xué)高級(jí)應(yīng)用[M].北京：北京郵電大學(xué)出版社，2013.