excel - Countif on sheet with 700k rows freezes program
问题描述
我目前有两个列表。A列中的“授予者”列表和B列中删除重复项的相同列表。我试图使用countif计算给定授予者在A列中的次数,但是我在A列中的列表超过700k行. 我使用的是 64 位 excel,但每次我运行代码来执行此操作时,excel 都会冻结和崩溃。
有没有办法在 excel 中做到这一点,还是我需要采取另一种方法,比如使用数据透视表或在访问中创建表?
我已经写了一些子程序,但这是最新的,来自这个论坛的另一个帖子。
Sub Countif()
Dim lastrow As Long
Dim rRange As Range
Dim B As Long '< dummy variable to represent column B
B = 2
With Application
.ScreenUpdating = False 'speed up processing by turning off screen updating
.DisplayAlerts = False
End With
'set up a range to have formulas applied
With Sheets(2)
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set rRange = .Range(.Cells(2, B), .Cells(lastrow, B))
End With
'apply the formula to the range
rRange.Formula = "=COUNTIF($A$2:$A$777363,C2)"
'write back just the value to the range
rRange.Value = rRange.Value
With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With
End Sub
解决方案
像这样的东西:
Sub Countif()
Dim allVals, uniqueVals, i As Long, dict, v, dOut(), r As Long
''creating dummy data
' With Sheet2.Range("A2:A700000")
' .Formula = "=""VAL_"" & round(RAND()*340000,0)"
' .Value = .Value
' End With
'
'get the raw data and unique values
With Sheet2
allVals = .Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value
uniqueVals = .Range("B2:B" & .Cells(.Rows.Count, "B").End(xlUp).Row).Value
End With
ReDim dOut(1 To UBound(uniqueVals, 1), 1 To 1) 'for counts...
Set dict = CreateObject("scripting.dictionary")
'map unique value to index
For i = 1 To UBound(uniqueVals, 1)
v = uniqueVals(i, 1)
If Len(v) > 0 Then dict(v) = i
Next i
'loop over the main list and count each unique value in colB
For i = 1 To UBound(allVals, 1)
v = allVals(i, 1)
If Len(v) > 0 Then
If dict.exists(v) Then
r = dict(v)
dOut(r, 1) = dOut(r, 1) + 1
End If
End If
Next i
'output the counts
Sheet2.Range("C2").Resize(UBound(dOut, 1), 1).Value = dOut
End Sub
在约 30 秒内运行,A 中有 700k 值,B 中有 300k 唯一值
推荐阅读
- jquery - jquery sortable connectWith到rails表单
- opencv - 文本文档中的块方案检测
- java - 只安排一次线程。
- mongodb - Mongodb 深度嵌套数组计数
- python - tempfile.NamedTemporaryFile 在任务计划程序中崩溃
- flurl - 反序列化问题GetJsonAsync
与 Flurl - php - Left Outer Join 的条件谓词评估较晚,导致性能问题。甲骨文 8i
- swift - Alamofire 缓存响应离线。斯威夫特 4
- excel - excel条件格式化多个函数
- delphi - Indy“对等连接重置”错误。10054 在特定网站上