|
如何用VBA字典和数组将表1的数据计算得到表2数据
取不重复值计算相应的平均数量,平均周期,标准差。
请各位老师帮忙看看。谢谢
数据透视表效果就能达到了
- Sub test()
- Dim arrSrc, arrSplit, arrRst()
- Dim objdic As Object
- Dim irow%, iCnt%, iCol%
- Dim stDveNum1#, stDveNum2#
- arrSrc = Sheet1.Range("a2:c14").Value
- Set objdic = CreateObject("scripting.dictionary")
- For irow = 1 To UBound(arrSrc)
- If Not objdic.exists(arrSrc(irow, 1)) Then
- objdic(arrSrc(irow, 1)) = objdic(arrSrc(irow, 1)) + arrSrc(irow, 2) & "/" & arrSrc(irow, 3) & "/" & 1
- Else
- objdic(arrSrc(irow, 1)) = Split(objdic(arrSrc(irow, 1)), "/")(0) + arrSrc(irow, 2) & "/" & Split(objdic(arrSrc(irow, 1)), "/")(1) + arrSrc(irow, 3) & "/" & Split(objdic(arrSrc(irow, 1)), "/")(2) + 1
- End If
- Next
- arrSplit = Application.Transpose(Array(objdic.keys, objdic.items))
- ReDim arrRst(1 To objdic.Count, 1 To 5)
- '计算平均数
- For iCnt = 1 To UBound(arrRst)
- arrRst(iCnt, 1) = arrSplit(iCnt, 1)
- arrRst(iCnt, 2) = Split(arrSplit(iCnt, 2), "/")(0) / Split(arrSplit(iCnt, 2), "/")(2)
- arrRst(iCnt, 4) = Split(arrSplit(iCnt, 2), "/")(1) / Split(arrSplit(iCnt, 2), "/")(2)
- Next
- '计算标准偏差
- For iCnt = 1 To UBound(arrRst)
- For irow = 1 To UBound(arrSrc)
- If arrRst(iCnt, 1) = arrSrc(irow, 1) Then
- stDveNum1 = stDveNum1 + (arrSrc(irow, 2) - arrRst(iCnt, 2)) ^ 2
- stDveNum2 = stDveNum2 + (arrSrc(irow, 3) - arrRst(iCnt, 4)) ^ 2
- End If
- Next
- arrRst(iCnt, 3) = Sqr(stDveNum1 / (Split(arrSplit(iCnt, 2), "/")(2) - 1))
- arrRst(iCnt, 5) = Sqr(stDveNum2 / (Split(arrSplit(iCnt, 2), "/")(2) - 1))
- stDveNum1 = 0: stDveNum2 = 0
- Next
- Sheet2.Range("a2").Resize(UBound(arrRst), 5) = arrRst
- End Sub
复制代码
|
|