效率计算

locker00700

请问，如何根据表2效率，实现表1黄色区域耗时？

表1

表2

效率.rar (8.92 KB, 下载次数: 4)

2018-8-18 16:31 上传

点击文件名下载附件

duquancai

1. Sub main()
   
2.     Dim a, b, i&, j&, r&
   
3.     Sheets("Sheet1").Select
   
4.     r = Cells(Rows.Count, "f").End(3).Row: b = Range("f2:g" & r)
   
5.     a = Range("a1:c" & Cells(Rows.Count, 1).End(3).Row)
   
6.     For i = 2 To UBound(a)
   
7.         For j = 1 To r - 1
   
8.             If InStr(a(i, 1), b(j, 1)) Then Exit For
   
9.         Next
   
10.         If j < r Then a(i, 3) = a(i, 2) / b(j, 2) Else a(i, 3) = ""
    
11.     Next
    
12.     Range("a1").Resize(UBound(a), 3) = a
    
13. End Sub

复制代码

chaohuahch

代码如下：

1. Sub fyExcelVBA()
   
2. Dim arr, brr, i%, j%
   
3. arr = Range("a1").CurrentRegion
   
4. brr = Range("f1").CurrentRegion
   
5. For i = 2 To UBound(arr)
   
6.     For j = 2 To UBound(brr)
   
7.       If InStr(arr(i, 1), brr(j, 1)) > 0 Then
   
8.          arr(i, 3) = arr(i, 2) / brr(j, 2)
   
9.       End If
   
10.     Next j
    
11. Next i
    
12. Range("a1").Resize(UBound(arr), UBound(arr, 2)).ClearContents
    
13. Range("a1").Resize(UBound(arr), UBound(arr, 2)) = arr
    
14. End Sub
    

复制代码