1 群里有人提问求助
1.1 原始问题和报错
1.2 拿到他的原始代码
Sub 多列汇总()
Dim l(1 To 1000, 1 To 4)
Dim arr, 行数
Dim x, k As Integer
Dim d As New Dictionary
arr = Range("q1:t" & Range("t65536").End(xlUp).Row)
For x = 1 To UBound(arr)
If d.Exists(arr(x, 1)) Then
行数 = d(arr(x, 1)) '字典的items赋给行数?
l(行数, 2) = l(行数, 2) + arr(x, 2)
l(行数, 3) = l(行数, 3) + arr(x, 3)
l(行数, 4) = l(行数, 4) + arr(x, 4)
Else
k = k + 1 'k代表行数??
d(arr(x, 1)) = k '把行数放进字典?
l(k, 1) = arr(x, 1)
l(k, 2) = arr(x, 2)
l(k, 3) = arr(x, 3)
l(k, 4) = arr(x, 4)
End If
Next x
Range("v2").Resize(k, 4) = l
Range("w" & Range("w65536").End(xlUp).Row + 1) = Application.WorksheetFunction.Sum("w3:w" & Range("w65536").End(xlUp).Row)
Range("x" & Range("x65536").End(xlUp).Row + 1) = Application.WorksheetFunction.Sum("x3:x" & Range("x65536").End(xlUp).Row)
Range("y" & Range("y65536").End(xlUp).Row + 1) = Application.WorksheetFunction.Sum("y3:y" & Range("y65536").End(xlUp).Row)
Range("z" & Range("z65536").End(xlUp).Row + 1) = Application.WorksheetFunction.Sum("z3:z" & Range("z65536").End(xlUp).Row)
Range(Range("w65536").End(xlUp).Row + 1) = "总计"
End Sub
2 这份代码的意思和常见问题的排除
2.1 这份代码的意思和我这2天搞的数组公式和透视表基本是一个目的
- 根据数据的分类,进行数据汇总
2.2 报错:不能取得类 worksheetfunction的sum属性
- 这种问题一般都是因为用了工作表函数,遇到有错误数据时,健壮性不够
- worksheetfunction.sum()
- worksheetfunction.match()
- 一般来说
- 用application类下的函数就行
- application.sum()
- application.match()
2.3 报错 方法 range作用于对象_global时失败
- Range(Range("w65536").End(xlUp).Row + 1) = "总计"
- 这种range(行号+1) 必然是语法错误,应该写成 range("v" & ()行号+1)) 才行
3 修正后的代码和运行情况
- 代码的基本意思就是
- 取关键列的 关键字,如果是非重复的,写入数组内(之后把这个数组赋到表的列里去),写入字典其行号,
- 如果是重复的,则根据字典的行号,把内容加起来
Sub 多列汇总()
Dim l(1 To 1000, 1 To 4)
Dim arr, 行数
Dim x, k As Integer
Dim d As New Dictionary
arr = Range("q1:t" & Range("t65536").End(xlUp).Row)
For x = 1 To UBound(arr)
If d.Exists(arr(x, 1)) Then
行数 = d(arr(x, 1)) '字典的items赋给行数?
l(行数, 2) = l(行数, 2) + arr(x, 2)
l(行数, 3) = l(行数, 3) + arr(x, 3)
l(行数, 4) = l(行数, 4) + arr(x, 4)
Else
k = k + 1 'k代表行数??
d(arr(x, 1)) = k '把行数放进字典?
l(k, 1) = arr(x, 1)
l(k, 2) = arr(x, 2)
l(k, 3) = arr(x, 3)
l(k, 4) = arr(x, 4)
End If
Next x
Range("v2").Resize(k, 4) = l
Range("w" & Range("w65536").End(xlUp).Row + 1) = Application.Sum(Range("w3:w" & Range("w65536").End(xlUp).Row))
Range("x" & Range("x65536").End(xlUp).Row + 1) = Application.Sum(Range("x3:x" & Range("x65536").End(xlUp).Row))
Range("y" & Range("y65536").End(xlUp).Row + 1) = Application.Sum(Range("y3:y" & Range("y65536").End(xlUp).Row))
Range("z" & Range("z65536").End(xlUp).Row + 1) = Application.Sum(Range("z3:z" & Range("z65536").End(xlUp).Row))
Range("v" & Range("w65536").End(xlUp).Row) = "总计"
End Sub