用字典实现多条件求和，并将某一条件将为行标题

tim159143

用字典实现多条件求和，并将某一条件将为行标题。

jiangxiaoyun

Sub 按钮1_Click()
Set d = CreateObject("scripting.dictionary")
arr = [a1].CurrentRegion
Dim brr(1 To 10000, 1 To 30)
k = 1: c = 2
For i = 2 To UBound(arr)
sr = arr(i, 1) & arr(i, 2)
If Not d.exists(sr) Then k = k + 1: d(sr) = k
If Not d.exists(arr(i, 3)) Then c = c + 1: d(arr(i, 3)) = c
irow = d(sr)
icol = d(arr(i, 3))
brr(irow, 1) = arr(i, 1)
brr(irow, 2) = arr(i, 2)
brr(1, icol) = arr(i, 3)
brr(irow, icol) = arr(i, 4) + brr(irow, icol)
Next
[g10].Resize(k, c) = brr
End Sub

tim159143

jiangxiaoyun 发表于 2020-10-10 12:42
Sub 按钮1_Click()
Set d = CreateObject("scripting.dictionary")
arr = [a1].CurrentRegion

很简单的算法，十分感谢

3190496160

Sub 按钮1_Click()
Dim d As Object
Dim ar As Variant
Dim br()
Set d = CreateObject("scripting.dictionary")
ar = Sheet1.[a1].CurrentRegion
ReDim br(1 To UBound(ar), 1 To UBound(ar))
y = 2: k = 1
For i = 2 To UBound(ar)
    If Trim(ar(i, 1)) <> "" And Trim(ar(i, 2)) <> "" Then
        zf = Trim(ar(i, 1)) & "|" & Trim(ar(i, 2))
        t = d(zf)
        If t = "" Then
            k = k + 1
            d(zf) = k
            t = k
            br(k, 1) = ar(i, 1)
            br(k, 2) = ar(i, 2)
        End If
        If Not d.exists(Trim(ar(i, 3))) Then
            y = y + 1
            br(1, y) = ar(i, 3)
            d(Trim(br(1, y))) = y
            br(t, y) = br(t, y) + ar(i, 4)
        ElseIf d.exists(Trim(ar(i, 3))) Then
            m = d(Trim(ar(i, 3)))
            br(t, m) = br(t, m) + ar(i, 4)
        End If
    End If
Next i
With Sheets("效果")
    .[a1].CurrentRegion = Empty
    .[a1].Resize(k, y) = br
End With
MsgBox "ok!"
End Sub

3190496160

多条件求和V3.0.rar (19.53 KB, 下载次数: 30)

2020-10-10 15:10 上传

点击文件名下载附件

tim159143

jiangxiaoyun 发表于 2020-10-10 12:42
Sub 按钮1_Click()
Set d = CreateObject("scripting.dictionary")
arr = [a1].CurrentRegion

老师，你好，现在行列的顺序是由系统默认排序，我所我将行列改为手动输入，怎么根据我输入的顺序来求和呢？还是上面的表格，我的顺序是“你、他、我”？麻烦你再帮忙看看

tim159143

3190496160 发表于 2020-10-10 15:09
Sub 按钮1_Click()
Dim d As Object
Dim ar As Variant

谢谢你的帮助，不过比上一个的代码复杂了点，能不能注释。我好理解两者区别及思路，另外，不符合条件的值为0，不要空值，要如何增加语句