VBA｜按需要动态地进行数值列的数据汇总

我们可以通过VBA代码，动态地对数据进行汇总：

1 对特定的一列或某些列进行动态汇总：

1.1 写一个以列标字母为参数的子过程

Sub dynamicSumFun(col As String) Dim lastR As Integer Dim lastC As Integer Dim flag As Boolean

lastR = Cells(Rows.Count, 1).End(xlUp).Row

lastC = Cells(1, Columns.Count).End(xlToLeft).Column Range('B' & lastR + 1).Value = '汇总：'

Range(col & lastR + 1).Formula = '=sum(' & col & '2:' & col & lastR & ')'

End Sub 1.2 以特定的列标字母为参数调用子过程

Sub dynamicSum() Call dynamicSumFun('E') End Sub

用不同的参数调用多次：

Sub dynamicSum2() Call dynamicSumFun('E') Call dynamicSumFun('G') End Sub

2 对全部的数值列动态汇总

以第二行为判断依据，对第二行内的每一个单元格进行判断，IsNumeric()函数判断为True及非空的单元格进行动态汇总。

Sub dynamicSumFun2()

Dim lastR As Integer Dim lastC As Integer Dim flag As Boolean Dim i As Integer

lastR = Cells(Rows.Count, 1).End(xlUp).Row

lastC = Cells(1, Columns.Count).End(xlToLeft).Column Range('B' & lastR + 1).Value = '汇总：' For i = 3 To lastC

flag = VBA.IsNumeric(Cells(2, i)) And Cells(2, i) <> '' Debug.Print (flag) If flag = True Then col = colsChar(i)

Cells(lastR + 1, i).Formula = '=sum(' & col & '2:' & col & lastR & ')' End If Next i End Sub

'数字转换为列标的函数，用于上面过程的调用

Function colsChar(i As Integer) Dim j As Integer

Dim s As String s = Cells(1, i).Address j = InStrRev(s, '$') s = Left(s, j - 1)

colsChar = Right(s, Len(s) - 1) End Function

_End_

Powered by TCPDF (www.tcpdf.org)