使用SQL在EXCEL中操作数据 [英] Manipulating data WITHIN excel using SQL
问题描述
我正在编写一份使用VBA操作数据的Excel报告。
虽然我没有理由不能使用VBA来实现我的目的，但我更愿意使用SQL，因为我对该语言更加精通。为了做到这一点，我面临着几个挑战，并已将我的问题简化为一个更容易解决的问题。
假设我有一个Excel电子表格，单元格A1中的数字为1，单元格A2中的数字为2，单元格A3中的数字为3。在单元格A4中，我尝试对这些求和，结果是数字6。sum()函数会得到我想要的答案，但不是我想要的方式。
相反，这是我正在尝试做的事情...
1) read cells A1->A3 into a table object (call this x) using VBA
2) declare a string variable and set it to 'SELECT SUM(column1) FROM x'
3) execute that sql string
4) store the results in cell A4
这是可行的做事方式吗？如果是，是否可以发布一个示例？
推荐答案
可以做到。不，不应该这样做。如果您需要3个单元格的总和，计算3个单元格的总和-Excel具有专门为此设计的内置函数。
=SUM(A1:A3)
在[A4]中键入它，您将以最有效的方式得到您的总和，而无需编写任何代码，并且不会让任何人看到您所做的事情感到惊讶。
还不信服吗？好吧。坐下来，拿点爆米花，享受这段旅程。
使用VBA将单元格A1->A3读入表格对象(称为x)
Excel不是数据库，它没有表--不是您所说的"表"。但这并不是什么精彩的表演。
假设您的工作簿有3个工作表，代号为Sheet1、Sheet2和Sheet3(这无论如何都是默认的)。因此，您已经使用sql填充了一些您希望SUM向上填充的数字，因为...不管为什么，只要因为。
由于我们希望将总和写入Sheet1!A4，因此我们将不使用Sheet1作为"表"，而是将其视为输出。
因此我们将Sheet1!A1:A3复制到Sheet2：
Sheet2.Range("A1").Value = "Values" 'our column header
Sheet1.Range("A1:A3").Copy Sheet2.Range("A2") 'our values
Sheet2视为"表"并对其执行SQL查询的对象。因此，我们将建立到Sheet2的ADODB/OLEDB连接，执行SQL查询，获取包含结果的Recordset对象，然后将值转储到Sheet1!A4。
草率的后期绑定代码执行此操作将如下所示：
Public Sub OverkillSum()
Dim connection As Object
Set connection = CreateObject("ADODB.Connection")
connection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.FullName & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";"
Dim recordset As Object
Set recordset = connection.Execute("SELECT SUM(Values) As Total FROM [Sheet2$]")
Sheet1.Range("A4").Value = recordset.Fields("Total").Value
recordset.Close
connection.Close
End Sub
请注意，连接字符串需要ThisWorkbook.FullName，因此在您尚未保存的一次性工作簿中不起作用。
在上面的内容和单元格A4中的=SUM(A1:A3)之间，设计决策应该是不需要动脑筋的。
- 后期绑定调用在运行时解析。这是通过引用ADODB类型库并使用
ADODB.Connection和ADODB.Recordset类型而不是使用Object接口来避免的开销。 - 与工作簿的连接也是无关的运行时开销。
- 通过OLEDB查询工作表绝对不适合计算3个值的总和。
- 您需要记住清理您的连接和记录集！
- 不要那样做。
- 就是不要。
Sheet1.Range("A4").Value = Application.WorksheetFunction.Sum(Sheet1.Range("A1:A3"))是本机Excel工作表函数解决方案的一行等价物--这仍然有些夸张，但至少它仍然属于Excel的范畴，不涉及往返月球的飞行。- 我说了不要那样做吗？
这种类型的解决方案对于其他用途很有用，例如，当您的工作簿布局为表格时，包含本应位于数据库中但以某种方式存在于Excel工作表中的信息，并且具有如此多的数据，因此通过Workbooks.Open打开它并计算复杂的聚合(可能涉及WHERE和GROUP BY子句)使用SUMIFS或其他非SQL方法将效率低下。
这篇关于使用SQL在EXCEL中操作数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
