基于VB的Excel分段统计插件的设计

夏芸

摘要：文中使用Visual Basic 6.0设计了一个Excel 2010的插件，该插件是依据用户所设定的分段条件，对用户在电子工作表中所选定的数据源区域进行条件统计，并将结果填写到用户所指定的单元格区域中。文中对插件的设计和实现进行了较为详细的描述。该插件具有一定的实用价值。

关键词：分段统计；Excel 2010；插件；VBA

中图分类号：TP317 文献标识码：A 文章编号：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

在日常的工作中，人们可能会使用Excel进行数据统计。例如，教师在每次测试结束后，需要统计班级的最高分、最低分、平均分及其每个分数段的人数。而这种类似于统计分数段人数的这种分段条件统计，对于一些Excel使用者而言，有一定的难度。为了解决这个分段统计的问题，文中设计了一个插件。

开发插件的主要好处在于插件结构具有良好的扩充和定制功能以及很容易将插件作为功能模块挂接到主程序上面[1]。文中采用Visual Basic 6.0为开发平台，开发了一个适用于Excel 2010版本的插件。该插件适用于对所选择的工作表单元格区域进行多段的条件统计，并且将统计结果显示在用户所指定的单元格区域中。这个插件的操作界面简单，操作过程容易掌握。本文主要介绍了这个插件的设计和实现。

1 插件的设计

文中所设计的插件是适用于Excel 2010版本的，因此，该插件在Excel 2010功能区中创建一个名为“统计”的选项卡，在该选项卡的名为“自定义组”选项组中，设置一个名为“分段统计”的命令按钮。当用户单击该命令按钮时，出现图1所示的窗口。

在该窗口中用户单击“计算区域”文本框时，可以在工作表中选择需要统计的单元格区域；单击“结果显示区域”文本框时，可以在工作表中选择存放统计结果的单元格区域。用户在该窗口中设置统计条件，至多可以设置10个条件，单击“确定”按钮后，就可以依据条件分段统计计算区域中符合条件的单元格个数，并将结果显示到指定的单元格区域中。

图1 分段统计的窗口

分段统计功能在实现时，考虑到用户所选择的需统计的单元格区域后，可能会在统计完成后有时会修改它们的值，那么相应的统计结果也会发生变化。因此，为了实现实时更新，统计方法是使用Excel中的函数来完成。

2 插件的实现

文中使用Visual Basic 6.0通过创建外接程序项目完成插件的设计和实现。

2.1创建外接程序项目

使用VB6.0企业版创建新的“外接程序”项目。在 “引用”对话框中添加如下的两个引用：“Microsoft Office 14.0 Object Library”和“Microsoft Excel 14.0 Object Library”，再按表1设置“Connect”设计器的属性。

载行为＼&Startup＼&]

2.2 声明全局变量、添加引用和实现接口

1） 声明全局变量

在模块中声明全局变量Excel_app，代码为：Public Excel_app As Excel.Application。

2） 添加引用

在“Connect”设计器的代码窗口中，添加应用程序接口的引用：IDTExtensibility2和IRibbonExtensibility，以便于VB调用Excel的资源，识别Excel VBA中的资源和对象。具体语句如下：

Implements IDTExtensibility2

Implements IRibbonExtensibility

3） 实现IDTExtensibility2接口

IDTExtensibility2接口中定义了5个成员方法：OnAddInsUpdate方法，OnConnection方法，OnDisconnection方法，OnStartupComplete方法，OnBeginShutdown方法。这些方法分别发生在COM加载项更改、加载、卸载，或者是Excel程序启动时，或者是Excel程序关闭而COM加载项仍运行时。文中这些方法在实现时因为没有特殊的要求，所以大部分方法是空方法。但是在OnConnection方法中需要完成全局变量的赋值，该变量在程序中表示Excel应用程序，其具体的代码如下：

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） 实现IRibbonExtensibility接口

因为该插件是面向Excel 2010版本的，因此，需要在Excel的功能区中添加选项组与命令按钮。而IRibbonExtensibility接口中所定义的方法GetCustomUI，就是依据XML代码创建功能区用户界面。所以，在“Connect”设计器必须实现IRibbonExtensibility接口的方法GetCustomUI，其具体代码如下：

Private Function IRibbonExtensibility_GetCustomUI（ByVal RibbonID As String） As String

IRibbonExtensibility_GetCustomUI = GetRibbonXML（）

End Function

上述代码中所调用的GetRibbonXML是用户自定义的函数，该函数要求返回一个字符串，这个字符串是使用XML代码在Excel的功能区中创建一个“统计”选项卡，并在该选项卡中创建名为“自定义组”的选项组，再在这个选项组中创建一个“分段统计”命令按钮。函数GetRibbonXML的具体代码如下：

Public Function GetRibbonXML（） As String

Dim sRibbonXML As String

sRibbonXML = "

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

"" & _

"" & _

"" & _

"" & _

"

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

"

" & _

"" & _

"" & _

"" & _

""

GetRibbonXML = sRibbonXML

End Function

在函数GetRibbonXML中指明，用户单击“分段统计”命令按钮时，则执行名为“submain”的过程，该过程主要是显示图1所示的“分段统计”窗口，其代码如下：

Public Sub submain（ByVal control As IRibbonControl）

UserForm1.Show （1）

End Sub

插件成功安装后Excel功能区中会添加图2所示的选项卡、选项组等。

图2 在Excel功能区中添加选项卡

2.3 窗体的设计

1） 外观设计

“分段统计”窗体的名称为UserForm1，在该窗体中添加了标签控件、文本框控件、选择框控件、组合框控件和按钮控件等，如图1所示。其中“计算区域”的文本框控件的名字为RefEdit1，“结果显示区域”的文本框控件的名字为RefEdit2。这两个文本框分别用于显示用户在Excel工作表中选定的数据源区域和结果区域的地址，该地址是绝对地址。

窗体中允许用户至多设置10个分段条件，每个分段条件是由一个选择框控件、两个组合框控件和两个文本框控件构成。选择框控件的名称是CheckBoxi，其中i=1，2，…，10；名称为ComboBoxi_1（i=1，2，…，10）的组合框只允许设置“>”或“>=”的关系运算，名称为ComboBoxi_2（i=1，2，…，10）的组合框只允许设置“<”或“<=”的关系运算，这两个组合框的内容只允许从下拉列表中选择，因此，它们的Style属性设置为Dropdown List；两个文本框分别用于输入对应关系运算的操作数值，该数值要求用户从键盘输入，它们的名称分别是TextBoxi_1和TextBoxi_2（i=1，2，…，10）。

“确定”按钮控件的名称是Command1，“取消”按钮控件的名称是Command2。

2） 窗口激活时设置默认值的功能实现

当“分段统计”窗口被激活时，所有ComboBoxi_1（i=1，2，…，10）的组合框的默认值为“>”，所有ComboBoxi_2（i=1，2，…，10）的组合框的默认值为“<”，同时，数据源的文本框RefEdit1获得焦点。因此，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单元格区域选择功能的实现

文本框RefEdit1和RefEdit2在窗体运行时，要求能显示用户在Excel工作表中所选择的单元格区域，因此，在用户单击文本框时，要求将当前的“分段统计”窗体隐藏，出现图3所示的输入框，允许用户在Excel工作表中用鼠标拖选单元格区域，选择完成后单击图3所示的“确定”按钮，隐藏的“分段统计”窗口显示到前台。

图3 输入框

RefEdit1文本框的单击事件和RefEdit2的单击事件处理方法非常类似，RefEdit1的Click事件代码如下：

Private Sub RefEdit1_Click（）

On Error Resume Next

Dim Rg As Range

Me.Hide

Set Rg = Excel_app.InputBox（"请选择需统计的数据源区域"， "数据来源"， 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） 设置分段条件的功能实现

在设置分段条件时，一但文本框TextBoxi_1或TextBoxi_2（i=1，2，…10）的内容发生改变时，则只要这两个文本框中的一个有内容，那么对应的选择框CheckBoxi（i=1，2，…10）的Value值设置为1，否则为0。因此，需要对这些文本框的文本改变事件进行对应的编程设计，它们的实现代码极为类似。以下是文本框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的功能实现

单击“确定”按钮时，要求能够按照窗口中所设定的条件，对指定的数据源区域进行条件统计，并将结果显示到指定的单元格区域中。

其算法基本思想是：首先获取数据源的非空的单元格区域和非空的结果区域，并分别保存为字符串形式，如果这两个区域有一个是空的，则出现出错提示对话框，再关闭窗口；如果区域选择正确，则使用循环语句对10个选择框进行依次检查，如果第i个选择框的Value值为1，即被选中，则读取它对应的两个组合框、两个文本框中的内容，并结合数据源区域的地址字符串，使用Excel中countif函数完成公式的编写，并将该公式保存为字符串，最后在指定的单元格内分别填写条件和计算的公式；最后，关闭窗口。

由于统计的方法使用的是公式，因此，如果数据源区域的值发生了改变，那么相应的运算结果就会自动调整。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 （"计算区域和结果显示区域均未设置！"）

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的功能实现

单击“取消”按钮时，关闭窗口。其Click事件的代码如下：

Private Sub Command2_Click（）

Unload Me

End Sub

2.4 生成dll文件并安装插件

在VB6.0企业版中生成扩展名为dll的动态链接库文件“fdtj.dll”。打开Excel 2010，在“开发工具”选项卡的“加载项”选项组中单击“COM加载项”命令按钮，在出现的“COM加载项”对话框中正确的添加文件“fdtj.dll”，即可成功的安装该插件。这时，Excel 2010中就会出现图2所示的选项卡，则用户就可以很方便地使用分段统计的功能。

3 结束语

本文详细介绍了使用VB开发Excel 2010插件的方法，以及具体的插件功能实现，文中所设计的插件可以很方便地实现分段的条件统计，具有一定的应用价值，并且也可以将之作为Excel VBA教学的一个良好用例。

参考文献：

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

[2] 陈文宇，胡英春，侯军燕. Word 2010插件的开发与实现[J].广西工学院学报，2010（6）.

[3] 解必华，奚玉梅. 基于VB的Excel打印插件设计及实现[J].电脑知识与技术，2011，7（6）： 1343-1345.

[4] 罗刚君，杨嘉恺.来吧！带你玩转Excel VBA[M].北京：电子工业出版社，2013.

[5] 裴纯礼.Word教育教学高级应用[M].北京：北京邮电大学出版社，2013.