用vba中的其他数组内容过滤2D数组的最快方法 [英] fastest way to filter an 2D array with the content of other array in vba

问题描述

好吧，我的工作簿中有20万多行，所以我需要最快的方式来处理这些数据.

Well i have over 200.000 lines in a workbook, so i need the fastest way to handle this data.

我更简单的将数据过滤到临时表，进行一些计算和删除工作表的方法要花费大量时间，因此我认为，如果我使用数组，我可以提高效率.

My simpler approach of filter data to temp sheet, make some calculation and delete sheet is taking huge amount of time, so i think that if i worked with arrays i could boost things up.

我创建了一个具有动态范围的数组来保存所有数据，并且我在另一个数组(日期)中也具有唯一记录，但是我需要循环主数组并过滤日期，因此例如可以制作一个当天结果的简单总和.日期在第3列中，而value_to_sum在第6列中.我有一个在1d数组上工作的代码，但是如何在多字段数组中工作呢?

I created an array with dynamic range to hold all data and i have the unique records too in a different array (the days), but i needed to loop the main array and filter the day, so i can for example make a simple sum on results for that day. The day is in column 3 and value_to_sum in column 6. I have a code that works on 1d array but how can i get it to work in a multiple field array?

f_array = Filter(main_array, "smith")

我简单地想获取每天的值之和

I simple want to get sum of values for each day

推荐答案

请测试下面的代码.根据唯一的日期数组，将花费更少的时间.只是好奇处理您现有数据范围需要多少费用.现在，它在同一张纸中返回，从"M2"开始.细胞.它可以很容易地适应于返回到任何地方:

Please, test the next code. According to the unique date array it will take more on less time. Just curious how much it takes processing your existing data range. Now, it returns in the same sheet, starting from "M2" cell. It can be easily adapted to return anywhere:

Sub SummarizePerDate()
 Dim sh As Worksheet, lastR As Long
 Dim arr, arrD, arrFin, i As Long, j As Long
 
 Set sh = ActiveSheet 'use here the sheet with the data to be processed
 lastR = sh.Range("A" & sh.Rows.count).End(xlUp).row
 
 arr = sh.Range("A2:G" & lastR).value 'put the data to be processed in an array
 arrD = sh.Range("K2:K9").value       'use here your array of unique date values
                                      'I used this range when tried testing
 ReDim arrFin(1 To UBound(arrD), 1 To 4)

 For i = 1 To UBound(arrD)
    For j = 1 To UBound(arr)
        If arrD(i, 1) = arr(j, 3) Then
            arrFin(i, 1) = arrD(i, 1)
            arrFin(i, 2) = arrFin(i, 2) + arr(j, 5)
            arrFin(i, 3) = arrFin(i, 3) + arr(j, 6)
            arrFin(i, 4) = arrFin(i, 4) + arr(j, 7)
        End If
    Next j
 Next
 sh.Range("M2").Resize(UBound(arrFin), UBound(arrFin, 2)).value = arrFin
 MsgBox "Ready..."
End Sub

这篇关于用vba中的其他数组内容过滤2D数组的最快方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！