vba - Excel中的CountIfs非常慢

Question

场景:

我有一个场景，每个账户可以有很多客户，每个客户可以有很多账户(多对多)。

我正在创建一个报告来计算每个客户拥有多少个帐户。



为此，我使用以下公式:

=COUNTIF(CustAccount[Customer ID], [@[Customer ID]])

这工作正常。问题是 CustAccount 表包含 65K 行。报告表包含 45K 客户 ID。在相对较慢的 PC 上执行此计算需要 20 多分钟。

我正在使用明显的优化，例如:

 Application.ScreenUpdating = False
 Application.Calculation = xlCalculationManual 

它是否每次循环所有 65K 行以获取 45K 表中每一行的计数？
关于可以做些什么来显着提高性能的任何想法？

Answer 1

如何在 Excel 中对数据运行 SQL 查询:

Sub SQLTester()
'Add a reference to "Microsoft ActiveX data objects"
'Workbook must have been saved to run this

Dim oConn As New ADODB.Connection
Dim oRS As New ADODB.Recordset
Dim wb As Workbook

    Set wb = ThisWorkbook

    wb.Save

    oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & wb.FullName & _
               ";Extended Properties='Excel 8.0;HDR=Yes'"

    oRS.Open " select Customer, count(Account) " & _
             " from [Data test$A1:B2201] group by Customer", oConn

    wb.Sheets("Data test").Range("E2").CopyFromRecordset oRS

    oRS.Close

End Sub