|
针对汇总表的分析。。。。。- Sub 分析()
- With Sheet1
- arr = .[a1:L7] '汇总表
- .[a1:L7].Interior.ColorIndex = 0
- For i = 4 To UBound(arr)
- maxx = 0: minx = 10000 'x组最大最小值初始化
- maxy = 0: miny = 10000 'y组最大最小值初始化
- For j = 2 To UBound(arr, 2) - 1
- dh = arr(3, j): je = arr(i, j) '代号 、金额
- If Left(dh, 1) = 5 Then 'X组
- If maxx < je Then maxx = je: dh1 = dh 'x组最大值
- If minx > je Then minx = je: dh2 = dh 'x组最小值
- ElseIf Left(dh, 1) = 7 Then 'Y组
- If maxy < je Then maxy = je: dh3 = dh 'y组最大值
- If miny > je Then miny = je: dh4 = dh 'y组最小值
- End If
- Next
- Cells(1 + i, 2).Resize(1, 8) = Array(dh1, maxx, dh2, minx, dh3, maxy, dh4, miny)
- .Rows(i).Find(maxx, lookat:=xlWhole).Interior.Color = vbRed
- .Rows(i).Find(maxy, lookat:=xlWhole).Interior.Color = vbRed
- .Rows(i).Find(minx, lookat:=xlWhole).Interior.Color = vbYellow
- .Rows(i).Find(miny, lookat:=xlWhole).Interior.Color = vbYellow
- Next
- End With
- End Sub
复制代码 |
|