当前位置：首页 → 问答吧 → 跨两表处理数据，求得数据比值

跨两表处理数据，求得数据比值

时间：2011-08-09

来源：互联网

在自己试做程序，其中部分功能无法实现，求高手帮助！

附件

Book2.rar(45.03 KB)

2011-8-9 11:40, 下载次数: 23

作者: purpledragoon 发布时间: 2011-08-09

有办法实现吗？

作者: purpledragoon 发布时间: 2011-08-09

有没有朋友能帮忙解决的，谢谢！

作者: purpledragoon 发布时间: 2011-08-10

有没有朋友能帮忙解决的，谢谢！对我来说有困难！论坛里也没找到类似的案例！

作者: purpledragoon 发布时间: 2011-08-11

数据分析表中A、B列是已知的？
只需要求这几个的？

作者: 蓝桥玄霜发布时间: 2011-08-11

复制内容到剪贴板

代码:

Sub yy()
Dim hou5, hou10, hou20, x$, j&, y&
Dim dic As Object, Brr, k1, t1
Dim i&, r$, Arrjc, k, t, Arr, Myc%
Sheet4.Range("c2:h1500").Clear
Set dic = CreateObject("Scripting.Dictionary")
Arrjc = Sheet5.[a1].CurrentRegion
For i = 2 To UBound(Arrjc)
      dic(Arrjc(i, 1) & "|" & Arrjc(i, 2)) = Arrjc(i, 4)
Next
k = dic.keys
t = dic.items
dic.RemoveAll
Arr = Sheet7.[a1].CurrentRegion
Myc = UBound(Arr, 2)
For i = 2 To UBound(Arr)
      dic(Arr(i, 1) & "|" & Arr(i, 2)) = i
Next
k1 = dic.keys
t1 = dic.items
Sheet4.Activate
r = Range("a65536").End(xlUp).Row
Brr = Range("a2:h" & r)
For i = 1 To UBound(Brr)
      hou5 = 0: hou10 = 0: hou20 = 0
      x = Brr(i, 1) & "|" & Brr(i, 2)
      For j = 0 To UBound(k1)
         If k1(j) = x Then
            For y = 1 To 5
                  hou5 = hou5 + Arr(t1(j), Myc - y + 1)
            Next
            For y = 1 To 10
                  hou10 = hou10 + Arr(t1(j), Myc - y + 1)
            Next
            For y = 1 To 20
                  hou20 = hou20 + Arr(t1(j), Myc - y + 1)
            Next
            Exit For
         End If
      Next
      For j = 0 To UBound(k)
         If k(j) = x Then
            Brr(i, 4) = hou5 / t(i)
            Brr(i, 6) = hou10 / t(i)
            Brr(i, 8) = hou20 / t(i)
         End If
      Next
Next
Range("d:h").NumberFormatLocal = "0.00%"
Range("d2").Resize(r - 1, 1) = Application.Index(Brr, 0, 4)
Range("f2").Resize(r - 1, 1) = Application.Index(Brr, 0, 6)
Range("h2").Resize(r - 1, 1) = Application.Index(Brr, 0, 8)
Set dic = Nothing
End Sub

作者: 蓝桥玄霜发布时间: 2011-08-11

请见附件。