时间戳

首页 > 解决方案 > 用字典键计算

excel - 用字典键计算

问题描述

我正在尝试计算两列中的百分比差异。我已将值存储在两个不同的字典中并计算百分比差异。结果存储在 value = pct_change 中。

然后我想把它添加到一个列表中，所以我计算有多少百分比增加了，有多少百分比减少了。是否可以创建一个新字典，将 pct_change 存储为键，并将该键附加到新字典中？在循环之后然后计算字典中的键数？
我将在下面发布我当前的代码。请注意，我知道我没有使用许多已定义的变量。
我希望有人可以帮助我

Sub benchmark()

Const NETSCONT_SHT3 = "D"
Const NETSCONT_SHT4 = "I"
Const NETSEXP_SHT4 = "H"


Dim wb As Workbook, wbNew As Workbook
Dim ws1 As Worksheet, ws3 As Worksheet, ws4 As Worksheet
Dim iRow As Long, iLastRow, iTargetRow As Long, iCopyRow As Long, NbCont_SHT3 As Long, AmCont_SHT3 As Double
Dim NbCont_SHT4 As Long, AmCont_SHT4 As Double, NbResults As Integer, AmResult As Double, pct_change As Double
Dim msg As String, i As Integer, j As Integer
Dim count As Long, countWB As Integer
Dim WkSht_Src   As Worksheet
Dim WkBk_Dest   As Workbook
Dim WkSht_Dest  As Worksheet
Dim Rng As Range
Dim r As Long

Set wb = ThisWorkbook
Set ws1 = wb.Sheets("BrokerSelect")
Set ws3 = wb.Sheets("ContributionSplitReport")
Set ws4 = wb.Sheets("ContributionExceptionReport")

Dim dict As Object, dictEXP As Object, sKey As Double, ar As Variant
Dim sEXP As Double, arEXP As Variant

Set dict = CreateObject("Scripting.Dictionary")
Set dictEXP = CreateObject("Scripting.Dictionary")

' pct change in expected and actual cont
iLastRow = ws4.Cells(Rows.count, NETSCONT_SHT4).End(xlUp).Row
For iRow = 18 To iLastRow
    sKey = ws4.Cells(iRow, NETSCONT_SHT4)
    sEXP = ws4.Cells(iRow, NETSEXP_SHT4)
    If sKey <> "0" Then
        pct_change = (sKey - sEXP) / sKey
        MsgBox (pct_Change)
    Else

    End If
Next


End Sub

标签： excelvba

解决方案

对于您的最后一个请求，请替换

MsgBox (pct_Change)

和

 Dim d As Long, dE as Long
    If pct_Change > 0 Then
      dict.Add d, pct_Change: d = d + 1
    ElseIf pct_Change < 0 Then
      dictEXP.Add dE, pct_Change: dE = dE + 1
    End If

  'finally:
  Debug.Print dict.Count, dictEXP.Count, dictEXP.Items(2)

推荐阅读