上一篇發布的數字統計，采用的是常規方法，運行速度比較慢，這一篇采用了數組的方法進行統計。運行速度會快很多。但是這篇文章把表格進行了簡化。

數據如圖：

數組的代碼如下:

Sub test()

Dim arr, brr, crr

Dim rng, i, k

Dim j As Integer

'獲取最後一個動态行

rng = Range("a" & Rows.Count).End(xlUp).Row

'數據源

arr = Range("A3:D" & rng)

'數據區

Range("E3:N10000") = ""

brr = Range("E3:N" & rng)

'運算過程

For k = 1 To UBound(arr, 1)

For i = 1 To UBound(arr, 2)

j = arr(k, i)

brr(k, j) = brr(k, j) 1

Next i

Next k

'寫入數據

Range("E3:N" & rng) = brr

'對所的行進行求和

Range("E" & rng 3 & ":N" & rng 3) = ""

crr = Range("E" & rng 3 & ":N" & rng 3)

For i = 1 To UBound(brr, 2)

crr(1, i) = Application.WorksheetFunction.Sum(Application.WorksheetFunction.Index(brr, 0, i))

Next

Range("E" & rng 3 & ":N" & rng 3) = crr

'對最後5行進行求和

rng = rng - 4

Range("E" & rng 9 & ":N" & rng 9) = ""

crr = Range("E" & rng 9 & ":N" & rng 9)

brr = Range("E" & rng & ":N" & rng 4)

For i = 1 To UBound(brr, 2)

crr(1, i) = Application.WorksheetFunction.Sum(Application.WorksheetFunction.Index(brr, 0, i))

Next

Range("E" & rng 9 & ":N" & rng 9) = crr

End Sub

代碼運行結果如下：

由于有幾行代碼太長，避免誤解。貼上源代碼的截圖

源代碼截圖：

,

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!

查看全部