VBA Excel:哪一个更好更快:对一个范围使用一个变体矩阵,还是对每一列使用多个数组?

发布于 01-10 08:19 字数 922 浏览 2 评论 0原文

我需要根据行值使用多列中的值。考虑到可能有几千行并且值会被多次使用,是创建变体矩阵数组并将所有列存储在该数组中,然后搜索并部署该数组中的值,还是为每列创建一个数组,是否更好更快?

示例:我们有以下数据:

在此处输入图片描述

如果此人在 2013 年 1 月 1 日之前加入,我想从股权中扣除应付金额。 是否更好?

Dim matrix() as Variant
Dim ws as Worksheet
Dim cols(4) as String: cols(0) = "A': cols(1) = "B": cols(2) = "C": cols(3) = "D"
Dim i as Integer
Dim b as Integer: b = 2 'beginning row
Dim j as Integer: j = 4 'number of lines

Set ws = Worksheets("Sheet1")

For i = 0 to UBound(cols)
  matrix(i) = Range(cols(i) & b & ":" & cols(i) & (b + j)).value2
End if

声明一个变体数组(例如)或

声明单独的四个数组(例如)

Dim arr1() as String
Dim arr2() as Date
Dim arr3() as Integer
Dim arr4() as Integer

当然,我也可以通过直接引用单元格来直接使用单元格中的数据,但是当我多次使用这个数千行数据时,更有意义将它们存储在数组中。

I need to use values from several columns based on a row value. Is it better and faster to create variant matrix array and store all columns in that array then search and deploy values from that array, or create an array for each column, considering that there can be several thousand rows and the values are used multiple times?

Example: We have the following data:

enter image description here

If the person joined before 01-Jan-13, I want to deduct due amount from equity. Is it better to declare a variant array like

Dim matrix() as Variant
Dim ws as Worksheet
Dim cols(4) as String: cols(0) = "A': cols(1) = "B": cols(2) = "C": cols(3) = "D"
Dim i as Integer
Dim b as Integer: b = 2 'beginning row
Dim j as Integer: j = 4 'number of lines

Set ws = Worksheets("Sheet1")

For i = 0 to UBound(cols)
  matrix(i) = Range(cols(i) & b & ":" & cols(i) & (b + j)).value2
End if

or

Declare separate four arrays, like

Dim arr1() as String
Dim arr2() as Date
Dim arr3() as Integer
Dim arr4() as Integer

Of course I can directly use data from cells by directly referencing cells as well, but as I use this multi-thousand-row data multiple times it makes more sense to store them in array.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

温柔戏命师2025-01-17 08:19:56

如果有很多列,将所有数据一次性读入单个矩阵可能会明显更快。 Excel 和 VBA 之间的每次数据传输都会产生大量开销。较大的数据传输并不比小数据传输慢多少,但许多数据传输比单个传输慢很多。

这是一个很好的详细信息来源:

工作表单元格和 VBA 变量之间的数据传输是一项昂贵的操作,应尽量减少。通过在单个操作中而不是一次一个单元格将数据数组传递到工作表(反之亦然),您可以显着提高 Excel 应用程序的性能。如果需要对 VBA 中的数据进行大量计算,则应将工作表中的所有值传输到数组,对数组进行计算,然后(可能的话)将数组写回工作表。这可以将工作表和 VBA 之间传输数据的次数降至最低。将包含 100 个值的数组传输到工作表比一次传输 100 个项目要高效得多。

http://www.cpearson.com/excel/ArraysAndRanges.aspx

If there are many columns, it may be noticeably faster to read all the data into a single matrix in one go. There is a large overhead with every transfer of data between Excel and VBA. A larger data transfer is not much slower than a small data transfer, but many data transfers is a lot slower than a single transfer.

Here is an excellent source of detail:

Data transfer between worksheet cells and VBA variables is an expensive operation that should be kept to a minimum. You can considerably increase the performance of your Excel application by passing arrays of data to the worksheet, and vice versa, in a single operation rather than one cell at a time. If you need to do extensive calculations on data in VBA, you should transfer all the values from the worksheet to an array, do the calculations on the array, and then, possibly, write the array back to the worksheet. This keeps the number of times data is transferred between the worksheet and VBA to a minimum. It is far more efficient to transfer one array of 100 values to the worksheet than to transfer 100 items at a time.

http://www.cpearson.com/excel/ArraysAndRanges.aspx

安静2025-01-17 08:19:56

将范围复制到数组,反之亦然

Option Explicit

Sub DeductDue()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets("Sheet1")
    
    ' Reference the data (no headers) range ('rg').
    Dim rg As Range
    With ws.Range("A1").CurrentRegion
        Set rg = .Resize(.Rows.Count - 1).Offset(1)
    End With
    
    ' Write the values from columns 2-4 ('Date' to 'Due') to an array ('sData').
    Dim sData As Variant: sData = rg.Columns(2).Resize(, 3).Value
    
    ' Wrtie the values from column 6 ('Equity') column to an array ('dData').
    Dim dData As Variant: dData = rg.Columns(6).Value
    
    ' Loop through the rows of the arrays and calculate...
    Dim r As Long
    For r = 1 To rg.Rows.Count
        If Year(sData(r, 1)) < 2013 Then
            dData(r, 1) = dData(r, 1) - sData(r, 3)
        End If
    Next r
    
    ' Write the result to a column range, e.g.:
    rg.Columns(6).Value = dData ' overwrite Equity with the deducted values
    
End Sub

Copy a Range to an Array and Vice Versa

Option Explicit

Sub DeductDue()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets("Sheet1")
    
    ' Reference the data (no headers) range ('rg').
    Dim rg As Range
    With ws.Range("A1").CurrentRegion
        Set rg = .Resize(.Rows.Count - 1).Offset(1)
    End With
    
    ' Write the values from columns 2-4 ('Date' to 'Due') to an array ('sData').
    Dim sData As Variant: sData = rg.Columns(2).Resize(, 3).Value
    
    ' Wrtie the values from column 6 ('Equity') column to an array ('dData').
    Dim dData As Variant: dData = rg.Columns(6).Value
    
    ' Loop through the rows of the arrays and calculate...
    Dim r As Long
    For r = 1 To rg.Rows.Count
        If Year(sData(r, 1)) < 2013 Then
            dData(r, 1) = dData(r, 1) - sData(r, 3)
        End If
    Next r
    
    ' Write the result to a column range, e.g.:
    rg.Columns(6).Value = dData ' overwrite Equity with the deducted values
    
End Sub
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文