时间戳

首页 > 解决方案 > vba-sum 和 group by 不使用 sql-adodb

excel - vba-sum 和 group by 不使用 sql-adodb

问题描述

我在excel中有以下数据：

+------+-------+-------+----+
| name | count | net   | CD |
+------+-------+-------+----+
| c1   | 125   | 12500 | D  |
| c2   | 55    | 3500  | C  |
| c3   | 80    | 2599  | C  |
| c4   | 30    | 1500  | D  |
| DGPS | 45    | 1000  | D  |
|      |       |       |    |
| PART | 51    | 1560  | C  |
| DGPS | 20    | 1990  | D  |
| c2   | 25    | 1325  | C  |
|      |       |       |    |
| c3   | 15    | 4500  | C  |
| c1   | 25    | 6300  | D  |
|      |       |       |    |
+------+-------+-------+----+

我不需要以 DGPS,PART 开头或为空的行。所以我必须删除它们。然后我需要执行 sum 和 group by。首先，如果 CD = D，我需要将 net 转换为 -net。然后尝试按名称获取 name、sum(count)、sum(net) 组。然后最后检查如果 sum(net) > 0 则 CD=C，如果 sum(net)<0 则 CD=D。

我可以在 sql 中使用以下查询：

select name,sum(count),to_char(ABS(ROUND(sum(net),2))),CASE when sum(net) > 0 then 'C' when sum(net) < 0 then 'D' when sum(net) = 0 then '0' END AS CD
FROM
(SELECT name,count,CASE WHEN CD = 'C' THEN to_char(ROUND(net,2)) ELSE to_char(ROUND(-net,2)) END AS net
FROM tb1 
)
group by name order by upper(name);

但是我不允许使用外部数据库或库，所以不能使用 sql-adodb。但我希望由于这是一个简单的求和和分组，我们可以仅通过 vba 实现它而不使用 sql。

编辑：样本最终输出格式

+------+-------+-------+----+
| name | count | net   | CD |
+------+-------+-------+----+
| c1   | 150   | 18800 | D  |
| c2   | 80    | 4825  | C  |
| c3   | 95    | 7099  | C  |
| c4   | 30    | 1500  | D  |
+------+-------+-------+----+

标签： excelvba

解决方案

大概是这样的：

Option Explicit

Public Sub SpecialSum()
    Dim wsData As Worksheet
    Set wsData = ThisWorkbook.Worksheets("data")

    Dim wsOutput As Worksheet
    Set wsOutput = ThisWorkbook.Worksheets("output")

    Dim AllNames As Variant
    AllNames = wsData.Range("A2", wsData.Cells(wsData.Rows.Count, "A").End(xlUp)).Value

    Dim UniqueNames As Object
    Set UniqueNames = CreateObject("Scripting.Dictionary")

    Dim iRow As Long
    For iRow = 1 To UBound(AllNames, 1)
        If AllNames(iRow, 1) <> "DGPS" And AllNames(iRow, 1) <> "PART" And AllNames(iRow, 1) <> "" Then
            If Not UniqueNames.Exists(AllNames(iRow, 1)) Then
                UniqueNames.Add AllNames(iRow, 1), 1
            End If
        End If
    Next iRow

    ReDim AllNames(1 To UniqueNames.Count, 1 To 1) As String
    iRow = 1
    Dim Key As Variant
    For Each Key In UniqueNames.Keys
        AllNames(iRow, 1) = Key
        iRow = iRow + 1
    Next Key

    wsOutput.Rows(1).Value = wsData.Rows(1).Value
    wsOutput.Range("A2").Resize(RowSize:=UniqueNames.Count).Value = AllNames
    wsOutput.Range("B2").Resize(RowSize:=UniqueNames.Count).Formula = "=SUMIF('" & wsData.Name & "'!A:A,'" & wsOutput.Name & "'!A:A,'" & wsData.Name & "'!B:B)"
    wsOutput.Range("C2").Resize(RowSize:=UniqueNames.Count).Formula = "=ABS(SUMIFS('" & wsData.Name & "'!C:C,'" & wsData.Name & "'!A:A,""=""&A2,'" & wsData.Name & "'!D:D,""=C"")-SUMIFS(data!C:C,'" & wsData.Name & "'!A:A,""=""&A2,'" & wsData.Name & "'!D:D,""=D""))"
    wsOutput.Range("D2").Resize(RowSize:=UniqueNames.Count).Formula = "=IF(SUMIFS('" & wsData.Name & "'!C:C,'" & wsData.Name & "'!A:A,""=""&A2,'" & wsData.Name & "'!D:D,""=C"")-SUMIFS(data!C:C,'" & wsData.Name & "'!A:A,""=""&A2,'" & wsData.Name & "'!D:D,""=D"")<0,""D"", ""C"")"
End Sub

推荐阅读