原始数据
1 使用数据透视表,最简单简洁的办法
- 插入 数据透视表
- 设置下字段,就很简单了
2 使用数组公式
2.1 感想,这些数组公式--特别是所谓万金油套路,都需要太多小技巧了,很麻烦
-
公式这么复杂, 用了很多奇怪技巧后对非专职研究EXCEL的人来说失去意义了
-
一般人能这么做,也就是写成一个工具表后,平时很少改了,
-
实用性不如用 透视表,或VBA
2.2 公式从内层逐层解析
- IFERROR(INDEX($A$2:$A$14,SMALL(IF($C$2:$C$14=$F$2,ROW($1:$13),99),ROW(A1))),"")
- 第1层 ----------IF
- IF($C$2:$C$14=$F$2,ROW($1:$13),99)----生成一个新的数组,数组元素内容就是其行号。
- 如果查到第1个元素符合,那么就取 往上-1的行号,如果没查到,就取99
- ROW($1:$13)为什么从1开始? 我认为必须从1开始的原因是excel里 range 或数组 序号必须从1开始?
- 为什么取99,因为现在列里就10来个元素,99够用,写999也没问题,据说也可以写成9^9 很大的这种数。
- 第2层--------small
- SMALL(IF($C$2:$C$14=$F$2,ROW($1:$13),99),ROW(A1))
- 所以 row(a1) row(a2) 就是起到1,2,3 自动化的作用,small取处理过的数组里的,第1,第2,第3小的值
- 第3层----index 取出数组里的前几位的元素,small()---变相成为了index
- INDEX($A$2:$A$14,small(99,1)) ---最小的index
- INDEX($A$2:$A$14,small(99,2)) ---第2小的index
ROW(A1) ROW(A2) 往右边拖动,就可以符合行数要求的其他列了!
- 第4 层
- iferror(,"")
- 因为数据的第1行,数组公式是自己写的 ROW(A1)
- 而数据的第2行,是往下拖的 ROW(A2) ROW(A3)等
- 避免错误值不好看
2.3 参考
2.4 利用F9看公式内的计算中间结果
4 使用VBA实现
- 代码的基本意思就是
- 取关键列的 关键字,如果是非重复的,写入数组内(之后把这个数组赋到表的列里去),写入字典其行号,
- 如果是重复的,则根据字典的行号,把内容加起来
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