|
- Sub yy()
- Dim Arr, i&, hj, gs
- Dim d, k, t, d1, t1, x$, k1, k2, Brr, hy, jb, r1, r2
- Set d = CreateObject("Scripting.Dictionary")
- Set d1 = CreateObject("Scripting.Dictionary")
- Sheet4.Activate
- Cells.Clear
- Arr = Sheet2.[a1].CurrentRegion
- For i = 2 To UBound(Arr)
- x = Arr(i, 13) & "|" & Arr(i, 8)
- d(x) = d(x) + 1
- d1(x) = d1(x) + Arr(i, 7) / 10000
- Next
- k = d.keys
- t = d.items: t1 = d1.items
- d.RemoveAll: d1.RemoveAll
- For i = 2 To UBound(Arr)
- d1(Arr(i, 8)) = ""
- d(Arr(i, 13)) = ""
- Next
- k1 = d.keys: k2 = d1.keys
- [c1].Resize(1, d1.Count) = k2
- [a3].Resize(d.Count, 1) = Application.Transpose(k1)
- ReDim Brr(1 To d.Count, 1 To d1.Count)
- For i = 0 To UBound(k)
- hy = Split(k(i), "|")(0)
- jb = Split(k(i), "|")(1)
- Set r1 = [a:a].Find(hy)
- Set r2 = Rows(1).Find(jb, , , 1)
- Brr(r1.Row - 2, r2.Column - 2) = Format(t1(i) / t(i), "0.00")
- Next
- [c3].Resize(UBound(Brr), UBound(Brr, 2)) = Brr
- For i = 1 To UBound(Brr)
- hj = Application.Sum(Cells(i + 2, 3).Resize(1, UBound(Brr, 2)))
- gs = Application.CountA(Cells(i + 2, 3).Resize(1, UBound(Brr, 2)))
- Cells(i + 2, 2) = Format(hj / gs, "0.00")
- Next
- For i = 1 To UBound(Brr, 2)
- hj = Application.Sum(Cells(3, i + 2).Resize(UBound(Brr), 1))
- gs = Application.CountA(Cells(3, i + 2).Resize(UBound(Brr), 1))
- Cells(2, i + 2) = Format(hj / gs, "0.00")
- Next
- [b2] = "平均数"
- ActiveSheet.UsedRange.Borders.LineStyle = 1
- Set d = Nothing: Set d1 = Nothing
- End Sub
复制代码 |
|