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

问题描述

我的工作簿中有超过 200.000 行，所以我需要最快的方法来处理这些数据.

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 列.我有一个适用于一维数组的代码，但如何让它在多字段数组中工作?

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中使用其他数组的内容过滤二维数组的最快方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！