|
简易化处理,结果做了去重,如果不去重,可以直接用二维数组装载并显示
- Option Explicit
- Sub Test()
- Dim Sh As Worksheet, lngRows As Long
- Dim arrData As Variant, objDic As Object, objDicResult As Object
- Dim dblConstant As Double '常数
- Dim dblAccuracy As Double '精度
- Dim dblDeviation As Double '误差
- Dim dblI As Double, strKey As String
- Dim lngA As Long, lngB As Long, lngC As Long, lngD As Long
- Dim dblA As Double, dblB As Double, dblC As Double, dblD As Double
-
- Set objDic = CreateObject("Scripting.Dictionary")
- Set objDicResult = CreateObject("Scripting.Dictionary")
- Set Sh = Sheets("Sheet1")
- lngRows = Sh.Range("A" & Rows.Count).End(xlUp).Row
- arrData = Sh.Range("A1:A" & lngRows)
-
- dblConstant = Sh.Range("C2").Value
- dblAccuracy = Sh.Range("D2").Value
-
- For lngA = 1 To lngRows
- objDic.RemoveAll
- dblA = arrData(lngA, 1)
- objDic(dblA) = ""
- For lngB = 1 To lngRows
- dblB = arrData(lngB, 1)
- If objDic.Exists(dblB) = False Then
- objDic(dblB) = ""
- For lngC = 1 To lngRows
- dblC = arrData(lngC, 1)
- If objDic.Exists(dblC) = False Then
- objDic(dblC) = ""
- For lngD = 1 To lngRows
- dblD = arrData(lngD, 1)
- If objDic.Exists(dblD) = False Then
- objDic(dblD) = ""
- dblI = dblA / dblB * dblC / dblD
- dblDeviation = dblI - dblConstant
- If Abs(dblDeviation) < dblAccuracy Then
- strKey = dblA & "," & dblB & "," & dblC & "," & dblD & "," & dblI & "," & dblDeviation
- objDicResult(strKey) = ""
- End If
- objDic.Remove dblD
- End If
- Next
- objDic.Remove dblC
- End If
- Next
- objDic.Remove dblB
- End If
- Next
- Next
-
- arrData = Application.WorksheetFunction.Transpose(objDicResult.keys)
- lngRows = UBound(arrData)
-
- Sh.Range("F2:F" & Rows.Count).Clear
- Sh.Range("F2").Resize(lngRows, 1) = arrData
- Sh.Range("F2").Resize(lngRows, 1).TextToColumns Destination:=Range("F2"), DataType:=xlDelimited, Comma:=True
-
- MsgBox "OK"
- End Sub
复制代码 |
|