excel统计小程序开发

Excel 是一款非常流行的电子表格软件，广泛应用于企业、教育等领域。在 Excel 中，我们可以通过各种公式和函数来进行数据计算和分析。但是，对于一些复杂的计算和分析需求，Excel 自带的功能就有些不够用了。这时，我们可以开发一些小程序，借助 VBA （Visual Basic for Applications）语言来实现自定义功能。

下面，我将介绍一个实用的 Excel 统计小程序的开发原理，以及如何实现。

原理：

我们将要开发一个 Excel 数据库统计小程序，主要功能是将 Excel 表格中的数据存储到 Access 数据库中，并提供一些查询和分析的功能。实现这个小程序的主要步骤如下：

1. 建立 Access 数据库：在 Access 中建立一个与 Excel 表格对应的数据库，将表格中的每个字段对应到数据库中的每个表项。

2. 编写 VBA 代码：在 Excel 中编写 VBA 代码来实现数据导入、查询和分析的功能。

3. 运行 VBA 代码：将 VBA 代码保存到 Excel 文件中，在 Excel 菜单中添加一个按钮，点击按钮即可运行 VBA 代码。

实现：

1. 创建 Access 数据库

首先，在 Access 中创建一个数据库，可以使用默认的模板，也可以根据实际需求进行设置。在本例中，我们采用默认的模板。

在 Access 中打开“新建”选项，输入数据库名称，选择一个存储位置，然后点击“创建”按钮，即可创建一个新的 Access 数据库。

接下来，在“创建”选项卡中选择“表格设计”，在设计视图中依次输入 Excel 表格中的每个字段名称和类型，并将其保存为一个新的表格。如果 Excel 表格中有多个 sheet，需要为每个 sheet 建立一个对应的表格。注意：在保存表格的时候，要保证表格名称与 VBA 代码中的表格名称一致。

2. 编写 VBA 代码

（1）数据导入

在 Excel 中按下 ALT + F11 打开 VBA 窗口，然后在“插入”选项卡中选择“模块”，在模块窗口中输入以下 VBA 代码：

Sub import()

Dim rs As Object

Dim cn As Object

Set cn = CreateObject("ADODB.Connection")

cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Administrator\Desktop\data.accdb"

cn.Open

Set rs = CreateObject("ADODB.Recordset")

rs.Open "select * from Sheet1", cn, 2, 3

Sheets("Sheet1").Activate

Range("A2").Select

Do Until IsEmpty(ActiveCell)

rs.AddNew

rs.Fields("ID") = ActiveCell.Value

rs.Fields("Name") = ActiveCell.Offset(0, 1).Value

rs.Fields("Age") = ActiveCell.Offset(0, 2).Value

rs.Fields("Score") = ActiveCell.Offset(0, 3).Value

rs.Update

ActiveCell.Offset(1, 0).Activate

Loop

rs.Close

Set rs = Nothing

cn.Close

Set cn = Nothing

End Sub

上述代码实现了将 Excel 表格中的数据导入到 Access 数据库中，具体实现方法如下：

（1） 创建一个 ADODB.Connection 对象进行数据库连接；

（2） 创建一个 ADODB.Recordset 对象，用于存储表格中的数据；

（3） 使用 SELECT 语句查询数据库中的表格数据；

（4） 使用 AddNew 方法将表格中数据添加到 Recordset 对象中；

（5） 更新 Recordset 对象，写入数据库中。

注意：代码中的第 5 行是数据库文件路径，需要根据实际情况进行修改。

（2）数据查询

在 VBA 模块中添加以下代码：

Sub search()

Dim rs As Object

Dim cn As Object

Dim strSQL As String

Set cn = CreateObject("ADODB.Connection")

cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Administrator\Desktop\data.accdb"

cn.Open

strSQL = "select * from Sheet1 where Age>=20"

Set rs = cn.Execute(strSQL)

Sheets("Sheet2").Activate

Range("A2").Select

Do While Not rs.EOF

ActiveCell.Value = rs.Fields("ID")

ActiveCell.Offset(0, 1).Value = rs.Fields("Name")

ActiveCell.Offset(0, 2).Value = rs.Fields("Age")

ActiveCell.Offset(0, 3).Value = rs.Fields("Score")

rs.MoveNext

ActiveCell.Offset(1, 0).Activate

Loop

rs.Close

Set rs = Nothing

cn.Close

Set cn = Nothing

End Sub

上述代码实现了根据年龄进行查询，查询结果存储在 Sheet2 中。具体实现方法如下：

（1） 创建一个 ADODB.Connection 对象进行数据库连接；

（2） 构造一个 SQL 查询语句，这里使用了 WHERE 子句和 >= 操作符进行查询；

（3） 创建一个 ADODB.Recordset 对象，调用 Execute 方法进行查询；

（4） 将查询结果写入到 Sheet2 中。

（3）数据分析

在 VBA 模块中添加以下代码：

Sub statistics()

Dim rs As Object

Dim cn As Object

Dim strSQL As String

Set cn = CreateObject("ADODB.Connection")

cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Administrator\Desktop\data.accdb"

cn.Open

strSQL = "select avg(Score) as avgScore,max(Score) as maxScore,min(Score) as minScore,count(Score) as total from Sheet1"

Set rs = cn.Execute(strSQL)

Sheets("Sheet3").Activate

Range("A1").Select

ActiveCell.Value = "平均分"

ActiveCell.Offset(0, 1).Value = rs.Fields("avgScore")

ActiveCell.Offset(1, 0).Select

ActiveCell.Value = "最高分"

ActiveCell.Offset(0, 1).Value = rs.Fields("maxScore")

ActiveCell.Offset(1, 0).Select

ActiveCell.Value = "最低分"

ActiveCell.Offset(0, 1).Value = rs.Fields("minScore")

ActiveCell.Offset(1, 0).Select

ActiveCell.Value = "总人数"

ActiveCell.Offset(0, 1).Value = rs.Fields("total")

rs.Close

Set rs = Nothing

cn.Close

Set cn = Nothing

End Sub

上述代码实现了计算平均分、最高分、最低分和总人数，并将结果写入到 Sheet3 中。具体实现方法如下：

（1） 创建一个 ADODB.Connection 对象进行数据库连接；

（2） 构造一个 SQL 查询语句，使用 AVG、MAX、MIN 和 COUNT 函数进行统计；

（3） 创建一个 ADODB.Recordset 对象，调用 Execute 方法进行查询；

（4） 将查询结果写入到 Sheet3 中。

3. 运行 VBA 代码

在 Excel 中打开“开发工具”选项卡，在工具栏中选择“插入”按钮，在下拉菜单中选择“按钮”并添加到 Excel 中。然后在“属性”面板中设置按钮名称以及执行的宏代码，这里选择“import”、“search”和“statistics”三个子程序。保存 Excel 文件后，就可以点击按钮，运行 VBA 代码了。

总结：

通过上述的方法，我们可以开发出一个实用的 Excel 统计小程序，帮助我们更方便地进行数据处理和分析。需要注意的是，在实际应用中，需要根据具体需求进行代码的优化和修改，以实现更全面和高效的功能。